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.
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
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
[…] Christian Antognini-Impact of Direct Reads on Delayed Block Cleanouts Dion Cho-Disabling direct path read for the serial full table scan – 11g […]
Blogroll Report 17/07/2009 – 24/07/2009 « Coskan’s Approach to Oracle
August 1, 2009 at 1:55 am
[…] direct path read for the serial full table scan – 11g https://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 […]
Oracle 11g Adaptive direct path read的几篇链接. « a db thinker's home
April 19, 2010 at 10:00 am
[…] https://dioncho.wordpress.com/2009/07/21/disabling-direct-path-read-for-the-serial-full-table-scan-11… (dated July 21, 2009) […]
True or False – Direct Path Reads « Charles Hooper's Oracle Notes
April 21, 2010 at 6:03 am
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):
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
[…] Disabling Direct path read for the serial full table scan-11g […]
Interesting combination of RAC and serial direct path read « Dion Cho – Oracle Performance Storyteller
June 9, 2010 at 4:31 am
[…] 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. […]
Kerry Osborne’s Oracle Blog » Blog Archive Exadata Offload - The Secret Sauce - Kerry Osborne’s Oracle Blog
June 16, 2010 at 4:12 am
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