What are Query Block Names and how to find them

I got a lot of follow-up questions on what Query Block names are and how to find them, after my recent post about using SQL Patches to influence execution plans. Rather than burying my responses in the comment section under that post, I thought it would be more useful to do a quick post on it.

What are query blocks?

query block is a basic unit of SQL. For example, any inline view or subquery of a SQL statement are considered separate query blocks to the outer query.

The  simple query below has just one sub-query, but it has two Query Blocks. One for the outer SELECT and one for the subquery SELECT.

Oracle automatically names each query block in a SQL statement based on the keyword using the following sort of name; sel$1, ins$2, upd$3, del$4, cri$5, mrg$6, set$7, misc$8, etc.

Given there are two SELECT statements in our query, the query block names will begin with SEL. The outer query will be SEL$1 and the inner query SEL$2.

How do I find the name of a query block?

To find the Query Block name, you can set the FORMAT parameter to ‘+alias’ in the DBMS_XPLAN.DISPLAY_CURSOR command. This will display the contents of the OBJECT_ALIAS column in the PLAN_TABLE, as a new section under the execution plan.

The new section will list the Query Block name for each of the lines in the plan.

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=>'+alias'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
SQL_ID 4c8bfsduxhyht, child NUMBER 0
-------------------------------------
SELECT e.ename, e.deptno FROM emp e WHERE e.deptno IN (SELECT d.deptno 
FROM dept d WHERE d.loc='DALLAS')
Plan hash VALUE: 2484013818
---------------------------------------------------------------------------
| Id  | Operation	   | Name | ROWS  | Bytes | Cost (%CPU)| TIME	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	  |	  |	5 (100)|	  |
|*  1 |  HASH JOIN SEMI    |	  |	5 |   205 |	5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   280 |	2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| DEPT |	1 |    21 |	2   (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (IDENTIFIED BY operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
2 - SEL$5DA710D3 / E@SEL$1
3 - SEL$5DA710D3 / D@SEL$2
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO")
3 - FILTER("D"."LOC"='DALLAS')

As you can see @SEL1 is the Query Block name for the outer query, where the EMP table is used and @SEL2 is the Query Block name for the sub-query, where the DEPT tables is used.

So, if I wanted to use a SQL Patch to change the access method for the DEPT table, I would specify it as follows.

DECLARE
   patch_name varchar2(100);
BEGIN
   patch_name := sys.dbms_sqldiag.create_sql_patch(
                 sql_id=>'4c8bfsduxhyht',
                 hint_text=>'INDEX(@"SEL$2" "D" "DEPT_LOC_IDX")',
                 name=>'TEST_QB_PATCH');
END;
/

Let’s check the plan to be sure the SQL Patch worked.

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=>'+alias'));
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
SQL_ID	4c8bfsduxhyht, child NUMBER 0
-------------------------------------
SELECT e.ename, e.deptno FROM	emp e WHERE e.deptno IN (SELECTd.deptno
FROM   dept d	WHEREd.loc='DALLAS')
Plan hash VALUE: 3547841569
-----------------------------------------------------------------------------------------------------
| Id  | Operation			     | Name	    | ROWS  | Bytes | Cost (%CPU)| TIME     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		     |		    |	    |	    |	  5 (100)|	    |
|*  1 |  HASH JOIN SEMI 		     |		    |	  5 |	205 |	  5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL		     | EMP	    |	 14 |	280 |	  2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID BATCHED| DEPT	    |	  1 |	 21 |	  2   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN		     | DEPT_LOC_IDX |	  1 |	    |	  1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (IDENTIFIED BY operation id):
-------------------------------------------------------------
   1 - SEL$5DA710D3
   2 - SEL$5DA710D3 / E@SEL$1
   3 - SEL$5DA710D3 / D@SEL$2
   4 - SEL$5DA710D3 / D@SEL$2
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
   1 - access("E"."DEPTNO"="D"."DEPTNO")
   4 - access("D"."LOC"='DALLAS')
 
Note
-----
   - SQL patch "TEST_QB_PATCH" used FOR this statement

Remember, even if it’s a long running query, once it starts executing the execution plan will be available in the cursor cache and therefore can be viewed by supplying the SQL_ID and CHILD NUMBER to the DBMS_XPLAIN.DIPLAY function.

If you have a more complex SQL statements, you may want to use the QB_NAME hint to explicitly name each query block. This is a good way to document your code and it will make it a lot easier to use a SQL PATCH to add additional hints, to a particular query block.

Let’s add a QB_NAME hint to the subquery in our simple SQL statement above and then use that Query Block name in our SQL Patch.

SELECT e.ename, e.deptno 
FROM   emp e 
WHERE  e.deptno IN (SELECT /*+QB_NAME(MY_DEPT_SQ) */ d.deptno 
                    FROM   dept d 
                    WHERE  d.loc='DALLAS');
 
DECLARE
   patch_name varchar2(100);
BEGIN
   patch_name := sys.dbms_sqldiag.create_sql_patch(
                 sql_id=>'bah0gcbrvm1gk',
                 hint_text=>'INDEX(@"MY_DEPT_SQ" "D" "DEPT_LOC_IDX")',
                 name=>'TEST_MYQB_PATCH2');
END;
/
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>'bah0gcbrvm1gk',- 
                                              format=>'+alias'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
SQL_ID bah0gcbrvm1gk, child NUMBER 0
-------------------------------------
SELECT e.ename, e.deptno  FROM   emp e  
WHERE  e.deptno IN (SELECT /*+QB_NAME(MY_DEPT_SQ) */ d.deptno 
FROM   dept d  WHERE  d.loc='DALLAS')
 
Plan hash VALUE: 2006641677
-----------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | ROWS  | Bytes | Cost (%CPU)| TIME     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              |       |       |     4 (100)|          |
|*  1 |  HASH JOIN SEMI                      |              |    10 |   200 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL                  | EMP          |    30 |   270 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID BATCHED| DEPT         |     1 |    11 |     2   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN                  | DEPT_LOC_IDX |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (IDENTIFIED BY operation id):
-------------------------------------------------------------
   1 - SEL$CA735286
   2 - SEL$CA735286 / E@SEL$1
   3 - SEL$CA735286 / D@MY_DEPT_SQ
   4 - SEL$CA735286 / D@MY_DEPT_SQ
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
   1 - access("E"."DEPTNO"="D"."DEPTNO")
   4 - access("D"."LOC"='DALLAS')
 
Note
-----
   - SQL patch "TEST_MYQB_PATCH2" used FOR this statement

For more complex queries it might be easier to use the Explain Plan or Auto Trace functionality in SQL Developer to view the Query Block names. Jeff Smith the product manager for SQL Developer recently showed me how to do this, as the OBJECT_ALIAS column isn’t displayed there by default.

In SQL Developer, go to the main menu under the Oracle SQL Developer tab in the top left-hand corner of the screen. From there, select preferences. This will open a new window that will allow you to change the default preferences. Under Database, select the Autotrace/Explain Plan option, which allows you to select which columns you want to have displayed in an execution plan. You want to select OBJECT_ALIAS from the list and click OK.

Once you have set the preference, simply select the EXPLAIN or AUTO TRACE button for the statement you are interested in and the Query Block Name will appear in the plan.

Unfortunately, SQL Monitor doesn’t provide a mechanism to display the OBJECT_ALIAS column right now. But I have filed an Enhancement Request to get it added.

SQL Tuning Workshop

Last week I had the pleasure of delivering a five-part SQL Tuning Workshop for my local Oracle User Group –  Northern California Oracle User Group. The workshop explains the fundamentals of the cost-based optimizer, the statistics that feed it, the hints that influence it and key tools you need to exam executions plans.

The workshop also provides a methodology for diagnosing and resolving the most common SQL execution performance problems. Given the volume of interest in this content, I want to share all of the material from the workshop here and give you links to additional material on each of the 5 topics.

Part 1 Understanding the Optimizer
The first part of the workshop covers the history of the Oracle Optimizer and explains the first thing the Optimizer does when it begins to optimize a query – query transformation.

Query transformations or the rewriting of the SQL statement into a semantically equivalent statement allows the Optimizer to consider alternative methods of processing or executing that query, which are often more efficient than the original SQL statement would allow. the majority of Oracle’s query transactions are now cost based, which means the Optimizer will cost the plan with and with the query transformation and pick the plan with the lowest cost. With the help of the Optimizer development team, I’ve already blogged about a number of these transformations including:

 

Part 2 Best Practices for Managing Optimizer Statistics
Part 2 of the workshop focuses on Optimizer Statistics and the best practices for managing them, including when and how to gather statistics, including fixed object statistics.
Continue reading “SQL Tuning Workshop”

Can I use an Autonomous Database to develop new applications?

Yes, Oracle Autonomous Database (ADB) is the ideal platform for new application development.

With this family of cloud services, developers no longer have to wait on others to provision hardware, install software, and create a database for them. With ADB, developers can easily and instantly deploy an Oracle database without worrying about having to manual tune it or capacity planning. This allows developers to start developing in minutes and concentrate on solving business problems without all of the usual distractions.

ADB has the most advanced SQL and PL/SQL support accelerating developer productivity by minimizing the amount of application code required to implement complex business logic. It also has a complete set of integrated Machine Learning algorithms, simplifying the development of applications that perform real-time predictions such as personalized shopping recommendations, customer churn rates, and fraud detection.

What Development Tools should I use with ATP?

Continue reading “Can I use an Autonomous Database to develop new applications?”

Getting started with Oracle Autonomous Transaction Processing

Getting started with Oracle Autonomous Transaction Processing is actually much easier than you might think. In fact, with Oracle’s $300 in free cloud credits you can probably get your first 30 days on the service for free. Please note, you will require an active email address and credit card in order to sign up for a trial account. Of course, if you have existing cloud credits you can skip this step.

Once you sign up for trail account you’ll get an email with your tenancy, username and password. Armed with this information, head on over to https://cloud.oracle.com to sign in. The video below explains in detailed the simple steps needed to provision a new Autonomous Transaction Processing database. I’ve also listed these steps below the video, for  easy reference.

Continue reading “Getting started with Oracle Autonomous Transaction Processing”

What you can expect from Oracle Autonomous Transaction Processing

Today Larry Ellison announced the general availability of Oracle Autonomous Transaction Processing (ATP), the newest member of the Oracle Autonomous Database family, combining the flexibility of cloud with the power of machine learning to deliver data management as a service.

Traditionally, creating a database management system required a team of experts to custom build and manually maintain a complex hardware and software stack. With each system being unique, this approach led to poor economies of scale and a lack of the agility typically needed to give the business a competitive edge.

ATP enables businesses to safely run a complex mix of high-performance transactions, reporting, and batch processing using the most secure, available, performant, and proven platform – Oracle Database on Exadata in the cloud. Unlike manually managed transaction processing databases, ATP provides instant, elastic compute and storage, so only the required resources are provisioned at any given time, decreasing runtime costs.

But what does the Autonomous in Autonomous Transaction Processing really mean?

Self-Driving

ATP is a self-driving database, meaning it eliminates the human labor needed to provision, secure, update, monitor, backup, and troubleshooting a database.  This reduction in database maintenance tasks, reducing costs and freeing scarce administrator resources to work on higher value tasks.

Continue reading “What you can expect from Oracle Autonomous Transaction Processing”

How does Autonomous Transaction Processing differ from the Autonomous Data Warehouse?

In my previous post, I explained that  Oracle Autonomous Transaction Processing has three main attribute: Self-Driving, Self-Securing and Self-Repairing. All of the functionality I described in that post is shared between both the Autonomous Data Warehouse (ADW) and ATP.

Where the two services differ is actually inside the database itself. Although both services use Oracle Database 18c, they have been optimized differently to support two very different but complimentary workloads. The primary goal of ADW is to achieve fast complex analytics, while ATP has been designed to efficiently execute a high volume of simple transactions.

Configuration

The differences in the two services begins with how we configure them. Continue reading “How does Autonomous Transaction Processing differ from the Autonomous Data Warehouse?”

Oracle Database 18c is now available for Download!

Today Oracle officially released Oracle Database 18c for download on Linux x86-64.

As you may recall, we originally released 18c on the Oracle Public Cloud and Oracle Engineered Systems back in February.

So, when will you be able to get your hands on 18c on-premises for other platforms?

You can check the Oracle Support document 742060.1 for more details!

18c is the first version of the database to follow the new yearly release model and you can find more details on the release model change in the Oracle Support Document 2285040.1.

Before you freak out about the fact you haven’t even upgraded to 12.2, so how on earth are you ever going to get to 18c – Don’t Panic!

Oracle Database 18c is in fact “Oracle Database 12c Release 2 12.2.0.2”, the name has simply been changed to reflect the year in which the product is released.

So, what can you expect?

As you’d imagine a patchset doesn’t contain any seismic changes in functionality but there are lots of small but extremely useful incremental improvements, most of which focus on the three key marquee features in Oracle Database 12c Release2:

More details on what has changed in each of these areas and other improvements can be found in the Oracle Database blog post published by Dominic Giles or in the video below with Penny Avril, VP of Database Product Management.

You can also read all about the new features in the 18c documentation and you can try out Oracle Database 18c on LiveSQL.

 

Oracle Database 18c Released

Today Oracle officially released Oracle Database 18c on the Oracle Public Cloud and Oracle Engineered Systems. This is the first version of the database to follow the new yearly release model and you can find more details on the release model change in the Oracle Support Document 2285040.1 .

Before you freak out about the fact you haven’t even upgraded 12.2, so how on earth are you ever going to get to 18c – Don’t Panic!

Oracle Database 18c is in fact “Oracle Database 12c Release 2 12.2.0.2”, the name has simply been changed to reflect the year in which the product is released.

So, what can you expect?

As you’d imagine a patchset doesn’t contain any seismic changes in functionality but there are lots of small but extremely useful incremental improvements, most of which focus on the three key marquee features in Oracle Database 12c Release2:

More details on what has changed in each of these areas and other improvements can be found in the Oracle Database blog post published by Dominic Giles this morning or in the video below with Penny Avril.

You can also read all about the new features in the 18c documentation and you can try out Oracle Database 18c on LiveSQL.

So, when will you be able to get your hands on 18c on-premises for non-engineered systems?

It will be some time later this calendar year. You can check the Oracle Support document 742060.1 for more details!