Disabling direct path read for the serial full table scan – 11g

It’s a good news – Oracle 11g has implemented direct path read for the serial full table scan!

• http://oraclue.com/2009/07/17/direct-path-reads-and-serial-table-scans-in-11g/
  

• http://afatkulin.blogspot.com/2009/01/11g-adaptive-direct-path-reads-what-is.html

But I don’t like this kind of one-way optimization. What I want is to enable/disable it whenever I want it – putting Oracle under my control.

So I had a research for a couple of hours and found how to disable it. Oracle 11g has introduced 10949 event to control this.

UKJA@ukja116> @oerr 10949
10949                                                                           
 "Disable autotune direct path read for full table scan"                        
// *Cause:                                                                      
// *Action:  Disable autotune direct path read for serial full table scan.      
//                                                                              

Here is a simple and dirty example how to use this event.

1. Create a table big enough to meet the threshold which seems to be 5 * _small_table_threshold.

UKJA@ukja116> col value new_value sth
UKJA@ukja116> @para small_table
old   9: and i.ksppinm like '%&1%'
new   9: and i.ksppinm like '%small_table%'

NAME                           VALUE                IS_DEFAUL SES_MODIFI        
------------------------------ -------------------- --------- ----------        
SYS_MODIFI                                                                      
----------                                                                      
DESCRIPTION                                                                     
--------------------------------------------------------------------------------
_small_table_threshold         637                  TRUE      true              
deferred                                                                        
threshold level of table size for direct reads                                  
                                                                                
Elapsed: 00:00:00.04
UKJA@ukja116> 
UKJA@ukja116> create table t1(c1 number, c2 char(2000), c3 char(2000), c4 char(2000));

Table created.

Elapsed: 00:00:00.21
UKJA@ukja116> insert into t1
  2  select level, 'x', 'x', 'x'
  3  from dual connect by level <= 10 + 5*&sth;
old   3: from dual connect by level <= 10 + 5*&sth
new   3: from dual connect by level <= 10 + 5*637

3195 rows created.

Elapsed: 00:00:01.14
UKJA@ukja116> commit;

Commit complete.

Elapsed: 00:00:00.00
UKJA@ukja116> @gather t1
UKJA@ukja116> exec dbms_stats.gather_table_stats(user, '&1', no_invalidate=>false);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.68

2. Now I compare the wait event(direct path read vs. db file scattered read) of the two queries – one with 10949 disabled(default) and the other with 10949 enabled.

-- case#1
alter system flush buffer_cache;
alter session set events '10046 trace name context forever, level 8';
select count(*) from t1;

-- case#2
alter system flush buffer_cache;
alter session set events '10046 trace name context forever, level 8';
alter session set events '10949 trace name context forever, level 1';
select count(*) from t1;

3. Do you see that 10949 enabled one behaves just like the previous version? The famous db file scattered read event!

SQL ID : 5bc0v4my7dvr5
select count(*) 
from
 t1

-- Case #1
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  db file sequential read                         3        0.02          0.04
  db file scattered read                          1        0.02          0.02
  direct path read                              231        0.29          1.67
  SQL*Net message from client                     2        0.03          0.03


-- Case #2
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  db file scattered read                        213        0.26          1.77
  SQL*Net message from client                     2        0.00          0.00

Don’t forget 10949 event when you want to disable this great feature for any reason!

Footnote1:What you should keep in mind is that this is not a CBO feature but a runtime execution engine feature. It would always show the same execution plan – TABLE ACCESS FULL. When the execution engine runs this operation, it determines how to read the table(direct path read or conventional path read) considering various factors.

Footnote2:There is “_serial_direct_read” parameter which forces the full scan on the big table to be a direct read – The opposite of 10949 event.