The Final Query in the cloud or not

When the Oracle Optimizer receives a query, one key thing it does is transforms the query.  This is a literally rewrite of the query text.  The optimizer has been doing this to some degree always, but it’s getting much more aggressive as we move into 12 and 18.  When I teach about this in my classes, I’m often asked “How can I see the query after the transformations are done?” 

The only place I know of is the 10053 trace file.  In the trace you’ll find a section with the title:

Final query after transformations:******* UNPARSED QUERY IS *******

After this line will be the query after all the transformations have happened.  Here are a couple of points about the statement.  First it is one long string.  There are no line breaks so you may want to copy it off to a text editor to make it a bit more readable.  Also everything has been fully qualified. 

This means that even a very simple select like this one:

select * from emp

Becomes this:

SELECT "EMP"."EMPNO" "EMPNO","EMP"."ENAME" "ENAME","EMP"."JOB" "JOB","EMP"."GENDER" "GENDER","EMP"."MGR" "MGR","EMP"."HIREDATE" "HIREDATE","EMP"."SAL" "SAL","EMP"."COMM" "COMM","EMP"."DEPTNO" "DEPTNO" FROM "OP"."EMP" "EMP"

This is the query text that will actually be optimized.   Notice that the entire statement has be converted into uppercase, of course any quoted strings wouldn’t be.

So how do I get this? You might be asking yourself. 

Getting the 10053 trace used to be rather cumbersome.  The problem is that the 10053 only traces a hard parse, and the only way to turn it one was the alter session command.  More recently Oracle has added the dbms_sqldiag.dump_trace call that creates a trace file from an existing cursor that is in the library cache.

The call looks like this:

execute dbms_sqldiag.dump_trace(p_sql_id=>'&hsqlid',  p_child_number=>'&hchild', p_component=>'Optimizer', p_file_id=>'MY_TRACE'));

The first two parameters are what you really have to enter, the SQL_ID and the CHILD_NUMBER.  The component has two values optimizer or compiler.  I’d recommend that you stick with the optimizer value (it’s the default).  Compiler adds more to the trace but the additional stuff is internal code calls which are likely great for the folks out is Redwood Shores who are developing the optimizer, but not to useful for us. 

The last parameter is pretty cool; this lets you add a string that will be appended to the end of the trace file name to help you find the trace file you just created.  This has the same effect as setting TRACEFILE_IDENTIFIER.  I hope this gets added to creating 10046 trace files someday.
 

Here is a great blog post by Greg Rahn about dbms_sqldiag.dump_trace if you’d like to see more about it, http://structureddata.org/2011/08/18/creating-optimizer-trace-files/

So now you can create a 10053 trace of a sql statement and see the statement that was actually optimized. 

A little example:

SQL> select /*+ qb_name (main1) */

  2  d.department_name, e.last_name as manager_name

  3  from dept1700 d, employees e

  4  where e.department_id = d.department_id;

DEPARTMENT_NAME                MANAGER_NAME

------------------------------ -------------------------

Purchasing                     Baida

Finance                        Chen

...

SQL> select sql_id from v$sql where sql_text like 'select /*+ qb_name (main1) */%';

SQL_ID

-------------

5sshry022tdrc

SQL>

SQL> execute dbms_sqldiag.dump_trace(p_sql_id=>'5sshry022tdrc',  p_child_number=>'0', -

>    p_component=>'Optimizer', p_file_id=>'HOTSOS_SQL_10053_'||to_char(sysdate, 'hh24miss'));

SQL>

SQL> SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Default Trace File';

VALUE

----------------------------------------------------------------------

C:\APP\ORACLE\diag\rdbms\orcl\orcl\trace\orcl_ora_11880_HOTSOS_SQL_10053_082115.trc

In the trace here is the final query:

Final query after transformations:******* UNPARSED QUERY IS *******

SELECT /*+ QB_NAME ("MAIN1") */ "DEPARTMENTS"."DEPARTMENT_NAME" "DEPARTMENT_NAME","E"."LAST_NAME" "MANAGER_NAME" FROM "OP"."DEPARTMENTS" "DEPARTMENTS","OP"."EMPLOYEES" "E" WHERE "E"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID" AND "DEPARTMENTS"."LOCATION_ID"=1700

 

If you compare this to what was run a couple things stand out, the query was written to select from a table called dept1700, which isn’t here in the final query.  The table DEPARTMENTS is instead, clearly dept1700 is really a view. And a new predicate has been added to the file query about the LOCATION_ID, which comes from the view.  This is what is transformation called view merging happening.

There is a lot more in the 10053 trace and some of it is even useful to us, a lot however is more suitable for the developers out in Redwood Shores.