Wednesday, September 5, 2018

SQL Developer: Renaming Columns in the cloud or not


Hey did you miss this in the latest drop of SQL Developer?  You can now rename the columns in the AUTOTRACE output! Way cool!! (Oh and Explain Plan too.)

Since I do a lot of screen shots this is great.  A lot of the columns in AUTOTRACE start with the work LAST, so when shrinking columns to make the screen shot a nice size the names are less than useful.  For example:







Notice the thee columns with just “LAST…” in them, um, which is which? Well now I can right click on the column heading and rename it!  YES!  This is very nice.









The SQL Developer team keeps hitting home runs.  Go Team!!





Jeff Smith (yes, thatjeffsmith)  asked me to add what I'd suggest you should rename columns in AUTOTRACE to.  So here are some of them that I think are important to be viewing in your plans that should be renamed.   Some are fine they way they are like OPTIONS, ID and COST.  Even these could be renamed using initcap to just make it a bit easier to read.



Current Name
Suggested Name
CARDINALITY
E-Rows
DISTRIBUTION
PQ Dist
LAST_CR_BUFFER_GETS
CR_Buffers or CR_LIOs
LAST_CU_BUFFER_GETS
CU_Buffers or CU_LIOs
LAST_DISK_READS
PIOs
LAST_ELAPSED_TIME
A-Time
LAST_MEMORY_USED
Used-Mem or Memory
LAST_OUTPUT_ROWS
A-Rows
LAST_STARTS
Starts
OBJECT_NAME
Name
PARENT_ID
PID
QBLOCK_NAME
QB_Name


Monday, July 30, 2018

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.