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.   

Tuesday, June 19, 2018

Unique index scans in the cloud or not

Every once and a while it’s good to refresh yourself on basic assumptions you have about how things work in Oracle.   Like how unique index scan work.   Somewhere along the line I had picked up the “false-truth” that as long as there was a unique constraint on the column(s) the optimizer would be able to do a unique scan, even if the index wasn’t unique.  Now this is NOT the norm, which might be why I thought this.  However a test is worth, – you know the rest of the saying.

Here we go.  First I create a non-unique index then put on a Primary Key constraint on the ID column, fill it up with 1000 rows and run a select.  (The column “SOMEDATA” is a random 9 char string.)  The full code for the example is at the bottom, I’m just highlighting some important bits here.

If I try to insert a value that is already in the table I get (as expected) an error:

Error starting at line : 33 in command -
INSERT INTO drop_me_now VALUES (1,'ABCDEFGHI')
Error report -
ORA-00001: unique constraint (OP.DMN_PK) violated


And when I select that row here is the plan:

SELECT * FROM drop_me_now WHERE ID=1;
        ID SOMEDATA
---------- ---------
         1 lVxoEQSTS

SELECT * FROM TABLE(dbms_xplan.display_cursor (FORMAT=>'typical'));
---------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |             |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DROP_ME_NOW |     1 |    14 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | DMN_ID      |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=1)

Notice it is a RANGE SCAN, but the optimizer does know it’s only going to get one row because of the primary key constraint.   

Coolness.

Now I recreate the table, put in a unique index but do not have any constraints, no primary key or unique constraint.  Again fill it with 1000 rows.  And do the same routine.  When inserting a row that already exists I again get an error.   Notice I get a “unique constraint” violation, but there isn’t a constraint on the column.  The constraint name used is the index name.

Error starting at line : 65 in command -
INSERT INTO drop_me_now VALUES (1,'ABCDEFGHI')
Error report -
ORA-00001: unique constraint (OP.DMN_ID) violated

And here is the plan for the select getting ID=1 on this table with just the unique index, but without any constraint on the column.

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| DROP_ME_NOW |     1 |    14 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | DMN_ID      |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=1)

OK, I got that “false-truth” out of my head now!  Here is the full script used if you want to have a go of it yourself.

rem file pk_index_example1.sql
rem RVD JUN2018
rem create a table with a nonunique index to support a
rem primary key constraint
rem then recreate with a unique index without a
rem primary key constraint
set serveroutput off

DROP TABLE drop_me_now;

CREATE TABLE drop_me_now ( ID NUMBER, somedata VARCHAR2(9));

CREATE INDEX dmn_id ON drop_me_now(ID);

ALTER TABLE drop_me_now ADD CONSTRAINT dmn_pk PRIMARY KEY (ID);

BEGIN
 FOR X IN 1..1000 LOOP
   INSERT INTO drop_me_now VALUES (X, (dbms_random.STRING('A',9)));
 END LOOP;
END;
/
COMMIT;

BEGIN
  dbms_stats.gather_table_stats (
    ownname => 'OP',
    tabname => 'DROP_ME_NOW',
    estimate_percent => dbms_stats.auto_sample_size);
END;
/

INSERT INTO drop_me_now VALUES (1,'ABCDEFGHI');

SELECT * FROM drop_me_now WHERE ID=1;

SELECT * FROM TABLE(dbms_xplan.display_cursor (FORMAT=>'typical'));

rem ***************************
rem Recreating the table
rem ***************************

DROP TABLE drop_me_now;

CREATE TABLE drop_me_now ( ID NUMBER, somedata VARCHAR2(9));

CREATE UNIQUE INDEX dmn_id ON drop_me_now(ID);

BEGIN
 FOR X IN 1..1000 LOOP
   INSERT INTO drop_me_now VALUES (X, (dbms_random.STRING('A',9)));
 END LOOP;
END;
/
COMMIT;

BEGIN
  dbms_stats.gather_table_stats (
    ownname => 'OP',
    tabname => 'DROP_ME_NOW',
    estimate_percent => dbms_stats.auto_sample_size);
END;
/

INSERT INTO drop_me_now VALUES (1,'ABCDEFGHI');

SELECT * FROM drop_me_now WHERE ID=1;

SELECT * FROM TABLE(dbms_xplan.display_cursor (FORMAT=>'typical'));

rem DROP TABLE drop_me_now;