Dion Cho – Oracle Performance Storyteller

We are natural born scientists

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

with 11 comments

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

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.

About these ads

Written by Dion Cho

July 21, 2009 at 3:38 am

11 Responses

Subscribe to comments with RSS.

  1. Thank you for this great posts! It fits some gaps in my knowledge I couldn’t fill myselve.
    Martin

    Martin Berger

    July 23, 2009 at 6:29 am

  2. Nice finding. I’ve set _small_table_threshold to a large value in specific sessions to disable this feature in a case where it hurt the performance.

    Tanel Poder

    July 23, 2009 at 5:52 pm

  3. [...] Christian Antognini-Impact of Direct Reads on Delayed Block Cleanouts Dion Cho-Disabling direct path read for the serial full table scan – 11g [...]

  4. [...] direct path read for the serial full table scan – 11g http://dioncho.wordpress.com/2009/07/21/disabling-direct-path-read-for-the-serial-full-table-scan-11… 韩国的Dion Cho写的关于如何关闭Adaptive direct path read的文章, 比较全面. But I [...]

  5. Hi Dion,

    FYI, This event works fine on our normal 11.2 databases but it seems to be resolutely ignored on our 11.2 Exadata V2 boxes.
    I raised an SR to ask for clarification and the reply simply said the 10949 event is for Oracle development use only.
    It’d be interesting to hear if anyone else can confirm the behaviour of the event on Exadata. Needless to say, it’s expensive kit and I’ve only got access to one box.

    Cheers,
    Tim

    Tim Hopkins

    May 28, 2010 at 11:02 am

    • Nice post Dion. The follow up on the RAC issue caused by the direct path reads was very interesting as well.

      In reply to Tim’s question about the event not shutting off serial direct path reads on Exadata, I tested this and it worked fine for me. (I’ll paste a little output (hopefully it won’t be too ugly):

      SYS@LABRAT1> select /* normal */ count(*) from kso.skew3 where col1 is null;
      
        COUNT(*)
      ----------
              12
      
      Elapsed: 00:00:00.12
      SYS@LABRAT1> alter session set events '10949 trace name context forever, level 1';
      
      Session altered.
      
      Elapsed: 00:00:00.00
      SYS@LABRAT1> select /* dpr off */ count(*) from kso.skew3 where col1 is null;
      
        COUNT(*)
      ----------
              12
      
      Elapsed: 00:03:23.36
      SYS@LABRAT1> set echo off
      SYS@LABRAT1> @fsx
      Enter value for sql_text: %col1 is null%
      Enter value for sql_id: 
      Enter value for inst_id: 
      Elapsed: 00:00:00.00
      Elapsed: 00:00:00.00
      Elapsed: 00:00:00.00
      
       INST SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO    AVG_PIO AVG_PX OFFLOADABLE IO_SAVED_% SQL_TEXT
      ----- ------------- ------ ---------- ---------- ------------- ------------ ---------- ------ ----------- ---------- ----------------------------------------
          1 c5f593c3xsadr      0 2684249835          1        203.36    1,956,247    903,973      0 No                 .00 select /* dpr off */ count(*) from kso.s
          1 g9s5h3pa8r975      0 2684249835          1           .11    1,956,226  1,956,219      0 Yes             100.00 select /* normal */ count(*) from kso.sk
      
      2 rows selected.
      
      Elapsed: 00:00:00.10
      SYS@LABRAT1> @we
      Enter value for event: 
      Enter value for sql_id: g9s5h3pa8r975
      
      no rows selected
      
      Elapsed: 00:00:00.05
      SYS@LABRAT1> /
      Enter value for event: 
      Enter value for sql_id: c5f593c3xsadr
      
      EVENT                                                            SQL_ID          COUNT(*)
      ---------------------------------------------------------------- ------------- ----------
      cell multiblock physical read                                    c5f593c3xsadr        115
      cell single block physical read                                  c5f593c3xsadr         57
      
      2 rows selected.
      
      Elapsed: 00:00:00.03
      

      Kerry Osborne

      June 16, 2010 at 1:46 pm

      • Kerry. Thank you for the info.

        And I envy you for having such a luck to run Exadata databases.

        Dion Cho

        June 17, 2010 at 4:29 am

  6. [...] Disabling Direct path read for the serial full table scan-11g [...]

  7. [...] I must say that I think the changes to the heuristics in 11g may be a little on the aggressive side for non-Exadata platforms (the changes may well be driven by Exadata). And by the way, serial direct path reads are not always faster than the normal reads that go through the buffer cache. Dion Cho has a good post on a performance problem due to serial direct path reads kicking in on one node of an 11g RAC environment (not Exadata). The node doing the direct path reads was running the query much slower than the node using the normal buffer cache reads. He also has a post on turning off serial direct path reads. [...]

  8. I raised an SR to oracle about serial path direct reads. After much investigation and some interesting test cases. I have reached the conclusion that for direct path serial read you need a big PGA and Fast IO system. The performance gain you will see is with hash joining and sort merge joins. after looking at performance gains, i would not suggest someone disables this features.

    Amir Riaz

    October 30, 2010 at 11:33 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 58 other followers

%d bloggers like this: