RULE lives on and on and on...

I’ve often said that Oracle will have an easier time getting rid of the IO costing model then Rule based optimizing.  Rule lives on in 12.1.0.1.  Maybe in my life time I’ll see the day when RULE is gone, but not this day.   

 

C:\OP>sqlplus op/op

SQL*Plus: Release 12.1.0.1.0 Production on Thu Dec 19 13:48:39 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Last Successful login time: Thu Dec 19 2013 13:44:55 -05:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter session set OPTIMIZER_MODE = rule;

SQL> @hxplan

Enter .sql file name (without extension): emp

Enter the display level (TYPICAL, ALL, BASIC, SERIAL) [TYPICAL]  :

Plan hash value: 1445457117

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

| Id  | Operation         | Name      |

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

|   0 | SELECT STATEMENT  |           |

|*  1 |  TABLE ACCESS FULL| EMPLOYEES |

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

Predicate Information (identified by operation id):

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

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

Note

-----

   - rule based optimizer used (consider using cbo)

SQL>

TABLE ACCESS BY INDEX ROWID BATCHED

This looks like a pretty cool new way to use an index.  From the documentation it say that this will access the index and get a “few rowids from the index, and then attempts to access rows in block order to improve the clustering and reduce the number of times that the database must access a block.”

That sure seems like a good idea and from my testing it looks like it does just that.  I have a table called APHYS1 and it has an index on a column that has a really great clustering factor (these stats are identical on the 11.2 and the 12.1 database):

Clust. Factor : 152
Table Rows    : 10000
Table Blocks  : 164

You can’t get much better then that the clustering factor is just less than the number of blocks in the table.  When I run a query on this table in 11.2 that stats of the run looks like this for LIOs and Pins:

buffer is pinned count : 738
consistent gets: 63

In 12.1 the stats are:

buffer is pinned count: 784
consistent gets: 17

It appears that for about 6% more pins we cut the LIOs (consistent gets) by 73%, not bad.  I’m liking this. 

There is one thing about this that is still a bit of a mystery for me.  There is a parameter to turn this feature off:

_optimizer_batch_table_access_by_rowid

However I don’t think it does anything.  My test case (which is very simple) has the same stats with this set to TRUE or FALSE.  What does change is the word BATCHED is dropped from the plan when this is set to FALSE but otherwise it’s exactly the same plan, cost and everything else.  Even the 10053 Trace looks the same again except for the word BATCHED appearing in the plan.  The part of the 10053 trace where it costs out the step is identical. The word BATCHED doesn’t appear in that section at all. 

Here is what is in the 10053 trace for costing the access path, note nothing about BATCHED:

SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for APHYS1[APHYS1]
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
  Column (#1): OBJECT_ID(NUMBER)
    AvgLen: 3 NDV: 100 Nulls: 0 Density: 0.010000 Min: 0.000000 Max: 99.000000
  Table: APHYS1  Alias: APHYS1
    Card: Original: 10000.000000  Rounded: 503  Computed: 503.03  Non Adjusted: 503.03
 ***** Logdef predicate Adjustment ******
 Final IO cst 0.00 , CPU cst 50.00
 ***** End Logdef Adjustment ******
 ***** Logdef predicate Adjustment ******
 Final IO cst 0.00 , CPU cst 52.52
 ***** End Logdef Adjustment ******
  Access Path: TableScan
    Cost:  46.08  Resp: 46.08  Degree: 0
      Cost_io: 46.00  Cost_cpu: 3203178
      Resp_io: 46.00  Resp_cpu: 3203178
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
  Access Path: index (RangeScan)
    Index: APHYS1_N1
    resc_io: 11.00  resc_cpu: 264816
    ix_sel: 0.050303  ix_sel_with_filters: 0.050303
    Cost: 11.01  Resp: 11.01  Degree: 1
  Best:: AccessPath: IndexRange
  Index: APHYS1_N1
         Cost: 11.01  Degree: 1  Resp: 11.01  Card: 503.03  Bytes: 0

Here is the plan in the 10053 trace when the parameter is set to TURE, the only difference when set to FALSE is the work BATCHED disappears (underlined here for emphasis, it’s not underlined in the trace file). 

============
Plan Table
============
--------------------------------------------------------+-----------------------------------+
| Id  | Operation                            | Name     | Rows  | Bytes | Cost  | Time      |
--------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT                     |          |       |       |    11 |           |
| 1   |  TABLE ACCESS BY INDEX ROWID BATCHED | APHYS1   |   503 |   51K |    11 |  00:00:01 |
| 2   |   INDEX RANGE SCAN                   | APHYS1_N1|   503 |       |     3 |  00:00:01 |
--------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("OBJECT_ID">=1 AND "OBJECT_ID"<=4)

So in 12.1 is there really a difference between these two?  I’m not seeing it. 

If you’d like to try this, please do and let me know if you get the same results.  I’ve run this test many times and get the same each time.  I’ve been doing this testing on two Amazon Cloud Linux based systems. This is the code to create the table:

create table aphys1 as
select
trunc((rownum-1)/100) object_id,
rpad(rownum,100) col2
from dba_source
where rownum < 10001;

create index aphys1_n1 on aphys1(object_id);

exec dbms_stats.gather_table_stats(user,'aphys1',method_opt=>'FOR ALL COLUMNS SIZE 1',cascade=>TRUE);

This is the query used:

select * from aphys1 where object_id between 1 and 4;

Getting the SQL_ID for a statement

A while back while I was teaching a class in Dallas, I was chatting with Carlos Sierra about the fact that I was disappointed there wasn’t a function in Oracle to return a SQL_ID for a statement.  Yes, I can get it AFTER I run a statement, but that is kind of closing the door after the horse has escaped. 

I wanted to know the statements SQL_ ID before I ran it so I could easily monitor it.  Carlos wrote up a nifty little routine to do it and you can find it here.

Apparently I was just a little ahead of the curve.  Now in 12c there is a new package called DBMS_SQL_TRANSLATOR.   In it are two routines of interest.

FUNCTION SQL_HASH RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_TEXT                       CLOB                    IN

FUNCTION SQL_ID RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_TEXT                       CLOB                    IN

These do pretty much what they say, the return the SQL_ID and the HASH_VALUE for a given statement.  Just a little test to show this:

SQL> DECLARE
  2    sqltext CLOB;
  3    sqlid   VARCHAR2(13);
  4    sqlhash number;
  5  BEGIN
  6    sqltext :=  'SELECT * FROM DUAL';
  7    sqlid   :=  DBMS_SQL_TRANSLATOR.SQL_ID (sqltext);
  8    sqlhash :=  DBMS_SQL_TRANSLATOR.SQL_HASH (sqltext);
  9    dbms_output.put_line ('SQL ID is '||sqlid);
 10    dbms_output.put_line ('SQL HASH is '||to_char(sqlhash));
 11  END;
 12  /
SQL ID is 9g6pyx7qz035v
SQL HASH is 3991932091
SQL>
SQL> SELECT * FROM DUAL;

D
-
X

SQL>
SQL> select SQL_ID, HASH_VALUE from v$sql where sql_text like 'SELECT * FROM DUAL';
SQL_ID             HASH_VALUE
------------- ---------------
9g6pyx7qz035v      3991932091

And I want to say a big thanks to Gary Propeck for pointing these out to me!