Author: Maria Colgan

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?

A 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.

What is a Converged Database?

At the recent OOW European conference there was a lot talk about Converged Databases and how they can greatly simplify data-driven app development.

But if you missed the conference, you might find yourself wondering what exactly is a Converged Database and what is the difference between a Converged Database and an Autonomous Database?

So, I thought it would be a good idea to write a short blog post explaining what a Converged Database is and how it relates to the Oracle Autonomous Database.

A Converged Database is a database that has native support for all modern data types (JSON, Spatial, Graph, etc. as well as relational), multiple workloads (IoT, Blockchain, Machine Learning, etc.) and the latest development paradigms (Microservice, Events, REST, SaaS, CI/CD, etc.) built into one product.

By having support for each of these datatype, workloads, and paradigms as features within a converged database, you can support mixed workloads and data types in a much simpler way. You don’t need to manage and maintain multiple systems or worry about having to provide unified security across them.

You also get synergy across these capabilities. For example, by having support for Machine Learning algorithms and Spatial data in the same database, you can easily do predictive analytics on Spatial data.  The Oracle Database is a great example of a Converged Database, as it provides support for Machine Learning, Blockchain, Graph, Spatial, JSON, REST, Events, Editions, and IoT Streaming as part of the core database at no additional cost.

A good analogy for a Converged Database is a smartphone. In the past, if you wanted to take a picture or video you would need a camera. If you wanted to navigate somewhere you would need a map or a navigation system. If you wanted to listen to music, you needed an iPod and if you wanted to make phone calls, you would also need a phone.

But with a smartphone, all of these products have been converged into one. Each of the original products is now a feature of the smartphone. Having all of these features converged into a single product inherently makes your life easier, as you can stream music over the phone’s data plan or upload pictures or videos directly to social media sites.
Continue reading “What is a Converged Database?”

How to use a SQL Plan Baseline or a SQL Patch to add Optimizer hints

In a recent chat with Connor McDonald, we discussed if it is realistic to have applications that don’t contain Optimizer hints. Ideally, the answer to this question is “yes”, you don’t need hints if you have a well-written application and you have supplied a representative set of statistics and all the possible constraint information (NOT NULL, Primary keys, Foreign Keys, etc.) to the Optimizer.

But in reality even with all of this in place, there can be cases where something goes wrong with the execution plan for a critical SQL statement and you get called in to fix.

During our chat, Connor used a very apt analogy to describe this situation. He said it was like having a patient arrive in the emergency room, who is bleeding profusely. Your first priority is to stop the patient from bleeding by slapping a band-aid on the wound.

The same is true for our poorly performing SQL statement. Our initial response is to add an optimizer hint to get the SQL statement’s execution plan back to a reasonable response time or acceptable performance.

But once a patient has been stabilized in the emergency room, medical professionals normally take that patient into surgery to make a permanent fix or at the very least stitch up the wound properly.

We need to make sure we do the same thing for our SQL statements.

Rather than leaving a band-aid in the application code in the form of an optimizer hint, we should either fix the root cause or at the very least, make a permanent fix that can be easily traced and ideally can evolve over time.

That’s why you often hear me say, “if you can hint it, you can baseline or patch it”.

What do I mean by that?

I mean we should capture the hinted plan as a SQL plan baseline or at the very least insert the hints via a SQL Patch so that we know that this statement is patched (the use of a SQL patch is visible in the note section of the plan).

Continue reading “How to use a SQL Plan Baseline or a SQL Patch to add Optimizer hints”

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”

How do I scale an Autonomous Database?

Traditionally database deployments have been designed and provision for the peak possible workload. And in reality, a substantial margin of safety was also provisioned on top of that in order to make sure the system could cope with any unforeseen demands.
But peak workloads tend to occur infrequently, leaving most of this costly capacity idle the majority of the time.

In order to enable customers to pay for only the resources they need, Oracle Autonomous Database allow customers to elastically adjust their compute and storage resources when necessary.

An Autonomous Database can be scaled through the UI as shown in the video below or via our cloud APIs or CLI commands. In the video you see how I can scale an 2 CPU  configuration to an 8 CPU configuration in under a minute  to accommodate 48 concurrent users running a JSON workload.

Continue reading “How do I scale an Autonomous Database?”

OpenWorld 2019 Wrap Up

I can’t believe Oracle Open World is done for another year.

But what a great conference it was for those interested in the Oracle Database. We saw the introduction of Oracle Database 20c and of course the continuing evolution of the Oracle Autonomous Database with details on new deployment option (Dedicated) and new flavors (Document Store).

If you weren’t able to make it to OOW in person, don’t panic as a lot of the session presentations are now available for download in the OOW session catalog. You can download the slides by clicking the down arrow on the right-hand side of each session title as shown below.

Continue reading “OpenWorld 2019 Wrap Up”

FREE Oracle Autonomous Database

Today was the first day of Oracle Open World and during Larry Ellison’s afternoon keynote he made a number of extremely exciting announcements around Oracle Autonomous Database including the launch of an Always Free Tier for Developers and students in the Oracle Cloud.  Larry said, “as long as you use the service, it will be there, free forever.”

So what’s included?

Along with compute and storage services, you also get access to 2 Autonomous Database with 20GB of storage each absolutely free. So you can try out both an Autonomous Data Warehouse and an Autonomous Transaction Processing system.

Continue reading “FREE Oracle Autonomous Database”

Are you ready for Oracle OpenWorld 2019?

With less than a week to go until Oracle Open World kicks off, I thought I would share with you what you can expect.

Of course the whole Database team will be there and you will have multiple opportunities to meet up with us, in one of our technical sessions, our hands-on-labs or at the Oracle demogrounds.

Below are just some of the session I thought might be interesting to help get you started:

Continue reading “Are you ready for Oracle OpenWorld 2019?”

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?”

Cloning an Oracle Autonomous Database

Often times development and testing teams need a copy of a production database in order to develop or test using a representative dataset. Up until now, creating these cloned  environments has been a challenging and time consuming process for DBAs.

Oracle Autonomous Database makes it extremely easy for a DBA to clone a database in just a few mouse clicks, as I demonstrate in the video below.

When it comes to cloning an Autonomous Database  you have two options:

You can create a new database that is a complete copy of the original database, which includes all metadata (table and view definitions etc.) and all of the actual data. This is referred to as a FULL CLONE.

Or you can create a new database that only contains the metadata from the original database.  This is referred to as METADATA CLONE.

Either way the “cloned” database will have a completely new database name, admin password. It can also have different infrastructure criteria (CPU count and storage) from the original database.

The clone will also not have any of the AWR data  or any of the ML Worksheets from original database.

Of course you don’t need to use the UI every time you want to create a clone. All of the tasks that can be done via the UI can be done using REST APIs or CLI commands.

Continue reading “Cloning an Oracle Autonomous Database”