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.
