Thursday, December 10, 2015

New version of the Hotsos SQL Test Harness: V5

Dateline – December 2015, Grapevine TX, Hotsos World Headquarters

The latest and greatest version of the Harness is now available for download from the Hotsos Education Downloads page.  

This version is functionally quite different from the old one.  The use of it remains the same, you provide a SQL script file name, a workspace and scenario name and the test harness will run the SQL file and provide key statistics on the run, including the Extended SQL Trace File (10046).

How it works is quite different and it does show some different and I believe more useful information then the old one.  In version past the output included the explain plan.  However this is becoming less useful with the new features like Cardinality Feedback in 11 and Adaptive Query Optimization in 12.  The new version of the Harness now shows the execution plan using DBMS_XPLAN.DISPLAY_CURSOR.  The format options are set to TYPICAL ALLSTATS LAST ALIAS which I believe give you key information about the run. 

You still get the PARSE line and all the STAT lines from the 10046 trace as part of the output.  Also it still takes a snap shot of the statistics before and after the run to show you the total work done by the query. 

But a really big change is that the Harness no longer requires the use of a username and password.  In the old version the tool had to log off and back on a few times as trace files were created.   The new version uses the DBMS_MONITOR.SESSION_TRACE_ENABLE and DISABLE to create the 10046 trace file so logging off and on is no longer required to get the full trace file.  The problem really was in the old style of creating trace file you might not get “everything” if you just turned off trace when you were done with the statement.  And a key part was the STAT lines didn’t always get written to the trace file when you just “turned it off”.  Now they do.

Also, the new Harness doesn’t create the 10053 trace anymore (CBO Enable optimizer trace).  This is the trace of a hard parse and for the most part it’s not used for performance optimization.  Yes it can be interesting to look at to see what the optimizer was “thinking” as it created a plan.  There will be a separate utility to create and load the 10053 trace added to the tool pack soon.   

Please let me know of any issue you have while using the Harness.   

A simple example of a run:

SQL> @dosql emp emp emp
+-+-+-+-+-+-+
Starting Test
+-+-+-+-+-+-+
Snapshot 1 collection OK.
Snapshot 2 collection OK.

+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
| Actual PARSE and STAT line data for emp:emp
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

PARSE #383679648:c=0,e=51,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1445457117,tim=343535899973

STAT #383679648 id=1 cnt=27 pid=0 pos=1 obj=116599 op='TABLE ACCESS FULL EMPLOYEES (cr=363 pr=0 pw=0 time=1331 us cost=73 size=83 card=1)'

+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
| Plan using DBMS_XPLAN.DISPLAY_CURSOR
| Format options: TYPICAL ALLSTATS LAST ALIAS
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  g76m74pycbct4, child number 0
-------------------------------------
select * from employees where first_name = 'David'

Plan hash value: 1445457117

----------------------------------------------------------------------------------------------------------
|Id |Operation         |Name      |Starts |E-Rows|E-Bytes|Cost (%CPU)|E-Time  |A-Rows|   A-Time  |Buffers|
----------------------------------------------------------------------------------------------------------
|  0|SELECT STATEMENT  |          |     1 |      |       |   73 (100)|        |    27|00:00:00.01|    363|
|* 1| TABLE ACCESS FULL|EMPLOYEES |     1 |     1|    83 |   73   (0)|00:00:01|    27|00:00:00.01|    363|
----------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / EMPLOYEES@SEL$1

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

   1 - filter("FIRST_NAME"='David')


+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
| Statistics Snapshot for emp:emp
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

Type  Statistic Name                                    Value
----- ---------------------------------------- --------------
Latch cache buffers chains                              1,040
      row cache objects                                    55
      shared pool                                          33

Stats buffer is pinned count                                0
      consistent gets                                     363
      consistent gets direct                                0
      db block changes                                      0
      db block gets                                         0
      execute count                                         6
      index fast full scans (full)                          0
      parse count (hard)                                    0
      parse count (total)                                   6
      physical reads                                        0
      physical writes                                       0
      redo size                                             0
      session logical reads                               363
      session pga memory                                    0
      session pga memory max                                0
      session uga memory                                    0
      session uga memory max                                0
      sorts (disk)                                          0
      sorts (memory)                                        0
      sorts (rows)                                          0
      table fetch by rowid                                  0
      table scan blocks gotten                            355
      table scans (long tables)                             0
      table scans (short tables)                            1

Time  elapsed time (centiseconds)                           3

+-+-+-+-+-+-+
Test Complete
+-+-+-+-+-+-+
SQL>

Happy Optimizing!

Monday, November 16, 2015

You only get one

I had an interesting discussion in a class recently.  There was a belief by several folks in the class that the Oracle’s optimizer could use more than one B-Tree index for retrieving data from a table.  Other than converting them into bit-maps, no it can’t.  (Yes there is a CONCATENATION plan that can do this, but it’s rather inefficient and unlikely to be used by the optimizer, it’s more or less deprecated since 10.2.)

For each access into a table in a plan it’s either going to use one index, or a full table scan.   If a table is access multiple times in a plan each time it could be using a different access method.  A query with a subquery doing a correlated self-join for example.

This is a simple query to illustrate the point.  This is a select from the employees table.  To make it slightly more interesting the employees table has over 27,000 rows in it.  It’s just multiple copies of the standard employees table (which has 107 rows).   If you want the code to create this larger version of employees drop me a line and I’ll send it to you.  

First I’m telling the optimizer to use both these indexes on this one table.  The order of the hints doesn’t matter by the way; it picks the same index even if you switch the order.  The optimizer did evaluate both indexes and pick the one that had the lower cost. The cost for EMP_JOB was 214, for EMP_DEPT was 218 in this case.  For the plan I’m just showing the barest of details just for space.   I used DBMS_XPLAN.DISPLAY_CURSOR to show the plan, these are execution plans not explain plans.

select /*+  index(emp emp_dept) index(emp emp_job) */
manager_id
from employees emp
where department_id = 50
and job_id = 'ST_MAN'
/

Plan hash value: 3308778001

---------------------------------------------------------
| Id  | Operation                           | Name      |
---------------------------------------------------------
|   0 | SELECT STATEMENT                    |           |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES |
|*  2 |   INDEX RANGE SCAN                  | EMP_JOB   |
---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DEPARTMENT_ID"=50)
   2 - access("JOB_ID"='ST_MAN')

Now if I have one index on both columns (a concatenated index), it can use that one to scan for the two predicates in the index  (BTW the cost of this was 200 for those that might be curious.):

select /*+  index(emp emp_dept_job) */
manager_id
from employees emp
where department_id = 50
and job_id = 'ST_MAN'
/

Plan hash value: 2068448379

------------------------------------------------------------
| Id  | Operation                           | Name         |
------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES    |
|*  2 |   INDEX RANGE SCAN                  | EMP_DEPT_JOB |
------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPARTMENT_ID"=50 AND "JOB_ID"='ST_MAN')

It’s a Highlander thing, there can only be one.  The optimizer can’t use more than one B-Tree index at a time to access a given table.  This is why the right multi-column index can be so important.  For a normal B-Tree index, getting the correct set of columns in the right order is a critical aspect of performance. 

Some notes about this test.  I did turn off bit map conversions with:

alter session set "_b_tree_bitmap_plans" = false;

With this set to the default of true it did do the bitmap conversion of the two single column indexes into a bit map index for each column.   Then did an “AND” operation between the two bit-map indexes. Lastly it converted the results back to rowids to find the rows in the table.  This can appear to be a good plan; however it can be problematic with the memory used and the time it takes to do all the conversions.

Also with no hints the optimizer went with a full table scan each time and really for this plan it was the best, it did the fewest over all LIOs and Buffer Pins.   I used the hints to illustrate that the optimizer can only use one index to access a given table at a time. 

Database used for this test:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Wednesday, November 4, 2015

Jokes....

Many of you who have taken a class with me know of my exceptionally bad jokes during class.  The material is rather dry so adding in a pun and or "joke" along the way does help keep things more entertaining for all.

Last week I was teaching and we were taking a about dimension of data and I made the "that means your data is demented" joke.  The problem was I had said nearly the same thing the day before in a different (but related) discussion.  Most folks groaned a bit but one guy was as quick as a whip and said:

"You have to use select distinct joke from jokes, you told that one yesterday."

We all roared laughing and class was on hold for several minutes.  I still chuckle about it now.

Good one mate!