Serial Direct Reads are a subject that has been getting a reasonable amount of attention since 11g was released. Doug Burns, Dion Cho (and here), Kerry Osborne and Miladin Modrakovic have all written on the subject. Tanel Põder also covered it in his presentation at the UKOUG Exadata Special Event.
Kerry and Tanel both cover the importance of direct reads in the context Smart Scan when running Exadata. I won’t reproduce their material here, you can read it direct from them, but to summarise: seeing “storage” an execution plan simply means that Smart Scan is possible, but does not mean that you’ll get it. You must have direct reads.
There are a number of ways to ensure your statement executes using direct reads and the one I’ll cover here is the (hidden) parameter _serial_direct_read.
In the process of using _serial_direct_read to ensure a serial delete used Smart Scan I ran into something that I find both interesting and slightly concerning. Let me demonstrate.
Create an environment to test in:
SQL> create tablespace sdr_test datafile '+DATA01' size 1G autoextend on maxsize 5G 2 / Tablespace created. Elapsed: 00:00:01.41 SQL> create user sdr_test identified by martin 2 default tablespace sdr_test 3 quota unlimited on sdr_test 4 / User created. Elapsed: 00:00:00.05 SQL> grant create session 2 , create table 3 , alter session 4 , alter system 5 to sdr_test 6 / Grant succeeded. Elapsed: 00:00:00.01
Connect as the test user and create a test table:
SQL> connect sdr_test/martin Connected. SDR_TEST - SESSION 1> create table a as 2 ( 3 select rownum id 4 , rpad(rownum,200,'X') c1 5 from dual 6 connect by rownum <= 10000000 7 ) 8 / Table created. Elapsed: 00:00:25.43
I’m sure it’s obvious, but I’ll state it for clarity: with no index on the table all access will be via full table scan.
Flush the buffer cache, set a trace file identifier, start tracing, run DELETE statement and rollback:
SDR_TEST - SESSION 1> alter system flush buffer_cache 2 / System altered. Elapsed: 00:00:02.65 SDR_TEST - SESSION 1> alter session set tracefile_identifier='sdr_1a' 2 / Session altered. Elapsed: 00:00:00.00 SDR_TEST - SESSION 1> exec dbms_session.session_trace_enable(true,true,'ALL_EXECUTIONS') PL/SQL procedure successfully completed. Elapsed: 00:00:00.02 SDR_TEST - SESSION 1> var id number SDR_TEST - SESSION 1> exec :id := 1 PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 SDR_TEST - SESSION 1> delete from a where id = :id 2 / 1 row deleted. Elapsed: 00:00:19.93 SDR_TEST - SESSION 1> rollback 2 / Rollback complete. Elapsed: 00:00:00.01
Check the trace file to see what we get:
WAIT #47706004018352: nam='gc cr multi block request' ela= 324 file#=5382 block#=2584 class#=1 obj#=761740 tim=1308902615410001 WAIT #47706004018352: nam='gc cr multi block request' ela= 806 file#=5382 block#=2680 class#=1 obj#=761740 tim=1308902615411418 WAIT #47706004018352: nam='cell multiblock physical read' ela= 12689 cellhash#=764630511 diskhash#=3671767124 bytes=1032192 obj#=761740 tim=1308902615424291 WAIT #47706004018352: nam='gc cr multi block request' ela= 408 file#=5382 block#=2744 class#=1 obj#=761740 tim=1308902615426480 WAIT #47706004018352: nam='gc cr multi block request' ela= 1037 file#=5382 block#=2808 class#=1 obj#=761740 tim=1308902615428066 WAIT #47706004018352: nam='cell multiblock physical read' ela= 5475 cellhash#=764630511 diskhash#=3671767124 bytes=1032192 obj#=761740 tim=1308902615433706 WAIT #47706004018352: nam='gc cr multi block request' ela= 696 file#=5382 block#=2881 class#=1 obj#=761740 tim=1308902615436139 WAIT #47706004018352: nam='gc cr multi block request' ela= 895 file#=5382 block#=2943 class#=1 obj#=761740 tim=1308902615437558 WAIT #47706004018352: nam='cell multiblock physical read' ela= 5300 cellhash#=764630511 diskhash#=3671767124 bytes=1032192 obj#=761740 tim=1308902615443082 WAIT #47706004018352: nam='gc cr multi block request' ela= 916 file#=5382 block#=3009 class#=1 obj#=761740 tim=1308902615445733
… That’s not a Smart Scan.
Set _serial_direct_read to TRUE, set new trace file identifier, flush buffer cache, run DELETE and rollback:
SDR_TEST - SESSION 1> alter session set "_serial_direct_read"=true 2 / Session altered. Elapsed: 00:00:00.00 SDR_TEST - SESSION 1> alter session set tracefile_identifier='sdr_1b' 2 / Session altered. Elapsed: 00:00:00.00 SDR_TEST - SESSION 1> alter system flush buffer_cache 2 / System altered. Elapsed: 00:00:00.45 SDR_TEST - SESSION 1> delete from a where id = :id 2 / 1 row deleted. Elapsed: 00:00:14.54 SDR_TEST - SESSION 1> rollback 2 / Rollback complete. Elapsed: 00:00:00.00
Check the trace file to see what we get:
WAIT #47706540316360: nam='cell multiblock physical read' ela= 8168 cellhash#=764630511 diskhash#=1422992472 bytes=1048576 obj#=761740 tim=1308902638029103 WAIT #47706540316360: nam='cell multiblock physical read' ela= 5468 cellhash#=764630511 diskhash#=1422992472 bytes=1048576 obj#=761740 tim=1308902638035744 WAIT #47706540316360: nam='cell multiblock physical read' ela= 5190 cellhash#=764630511 diskhash#=1422992472 bytes=1015808 obj#=761740 tim=1308902638042035 WAIT #47706540316360: nam='cell multiblock physical read' ela= 13723 cellhash#=2714602255 diskhash#=266393524 bytes=1048576 obj#=761740 tim=1308902638056851 WAIT #47706540316360: nam='cell multiblock physical read' ela= 7911 cellhash#=2714602255 diskhash#=266393524 bytes=1048576 obj#=761740 tim=1308902638066297 WAIT #47706540316360: nam='cell multiblock physical read' ela= 5505 cellhash#=2714602255 diskhash#=266393524 bytes=1048576 obj#=761740 tim=1308902638072991 WAIT #47706540316360: nam='cell multiblock physical read' ela= 5218 cellhash#=2714602255 diskhash#=266393524 bytes=32768 obj#=761740 tim=1308902638079404 WAIT #47706540316360: nam='cell multiblock physical read' ela= 1424 cellhash#=1730663586 diskhash#=755155853 bytes=1048576 obj#=761740 tim=1308902638082024 WAIT #47706540316360: nam='cell multiblock physical read' ela= 1493 cellhash#=1730663586 diskhash#=755155853 bytes=1048576 obj#=761740 tim=1308902638084697 WAIT #47706540316360: nam='cell multiblock physical read' ela= 1314 cellhash#=1730663586 diskhash#=755155853 bytes=1048576 obj#=761740 tim=1308902638087102
… That’s still not a Smart Scan 😦
Set new trace file identifier, flush buffer cache, run DELETE (with an embedded comment to force hard parse) and rollback:
SDR_TEST - SESSION 1> alter session set tracefile_identifier='sdr_1c' 2 / Session altered. Elapsed: 00:00:00.00 SDR_TEST - SESSION 1> alter system flush buffer_cache 2 / System altered. Elapsed: 00:00:00.23 SDR_TEST - SESSION 1> delete /* SESSION 1 */ from a where id = :id 2 / 1 row deleted. Elapsed: 00:00:00.29 SDR_TEST - SESSION 1> rollback 2 / Rollback complete. Elapsed: 00:00:00.01
Check the trace file to see what we get:
WAIT #47706540290640: nam='cell smart table scan' ela= 26 cellhash#=3018218633 p2=0 p3=0 obj#=761740 tim=1308902650538344 WAIT #47706540290640: nam='cell smart table scan' ela= 8 cellhash#=3018218633 p2=0 p3=0 obj#=761740 tim=1308902650538400 WAIT #47706540290640: nam='cell smart table scan' ela= 310 cellhash#=3018218633 p2=0 p3=0 obj#=761740 tim=1308902650538741 WAIT #47706540290640: nam='cell smart table scan' ela= 9 cellhash#=3018218633 p2=0 p3=0 obj#=761740 tim=1308902650538821 WAIT #47706540290640: nam='cell smart table scan' ela= 26 cellhash#=3018218633 p2=0 p3=0 obj#=761740 tim=1308902650538887 WAIT #47706540290640: nam='cell smart table scan' ela= 25 cellhash#=3018218633 p2=0 p3=0 obj#=761740 tim=1308902650538961 WAIT #47706540290640: nam='cell smart table scan' ela= 25 cellhash#=1078241440 p2=0 p3=0 obj#=761740 tim=1308902650539712 WAIT #47706540290640: nam='cell smart table scan' ela= 35 cellhash#=1078241440 p2=0 p3=0 obj#=761740 tim=1308902650539801 WAIT #47706540290640: nam='cell smart table scan' ela= 7 cellhash#=1078241440 p2=0 p3=0 obj#=761740 tim=1308902650539856 WAIT #47706540290640: nam='cell smart table scan' ela= 564 cellhash#=1078241440 p2=0 p3=0 obj#=761740 tim=1308902650540456
… Smart Scan, Smart Scan! 🙂
Start a new session, set variable, set new trace file identifier, start tracing, run DELETE and rollback:
SQL> connect sdr_test/martin Connected. SDR_TEST - SESSION 2> var id number SDR_TEST - SESSION 2> exec :id := 1 PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 SDR_TEST - SESSION 2> alter session set tracefile_identifier='sdr_2a' 2 / Session altered. Elapsed: 00:00:00.00 SDR_TEST - SESSION 2> exec dbms_session.session_trace_enable(true,true,'ALL_EXECUTIONS') PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 SDR_TEST - SESSION 2> alter system flush buffer_cache 2 / System altered. Elapsed: 00:00:00.12 SDR_TEST - SESSION 2> delete /* SESSION 2 */ from a where id = :id 2 / 1 row deleted. Elapsed: 00:00:14.07 SDR_TEST - SESSION 2> SDR_TEST - SESSION 2> rollback 2 / Rollback complete. Elapsed: 00:00:00.01
Check the trace file to see what we get:
WAIT #47830984391856: nam='cell multiblock physical read' ela= 5305 cellhash#=2714602255 diskhash#=1846921898 bytes=1032192 obj#=761740 tim=1308902651164153 WAIT #47830984391856: nam='cell multiblock physical read' ela= 1356 cellhash#=2714602255 diskhash#=1846921898 bytes=1032192 obj#=761740 tim=1308902651168319 WAIT #47830984391856: nam='cell multiblock physical read' ela= 1411 cellhash#=1857444352 diskhash#=3010233083 bytes=1032192 obj#=761740 tim=1308902651174085 WAIT #47830984391856: nam='cell multiblock physical read' ela= 1346 cellhash#=1857444352 diskhash#=3010233083 bytes=1032192 obj#=761740 tim=1308902651178202 WAIT #47830984391856: nam='cell multiblock physical read' ela= 1286 cellhash#=1857444352 diskhash#=3010233083 bytes=1032192 obj#=761740 tim=1308902651182195 WAIT #47830984391856: nam='cell multiblock physical read' ela= 1259 cellhash#=1857444352 diskhash#=3010233083 bytes=1032192 obj#=761740 tim=1308902651186208 WAIT #47830984391856: nam='cell multiblock physical read' ela= 15622 cellhash#=281363246 diskhash#=3630261201 bytes=1032192 obj#=761740 tim=1308902651206140 WAIT #47830984391856: nam='cell multiblock physical read' ela= 5431 cellhash#=281363246 diskhash#=3630261201 bytes=1032192 obj#=761740 tim=1308902651214612 WAIT #47830984391856: nam='cell multiblock physical read' ela= 5315 cellhash#=281363246 diskhash#=3630261201 bytes=1032192 obj#=761740 tim=1308902651222641 WAIT #47830984391856: nam='cell multiblock physical read' ela= 5303 cellhash#=281363246 diskhash#=3630261201 bytes=1032192 obj#=761740 tim=1308902651230702
… Not a Smart Scan, but that’s expected.
Set a new trace file identifier, flush buffer cache and run DELETE statement from session 1 in session 2:
SDR_TEST - SESSION 2> alter session set tracefile_identifier='sdr_2b' 2 / Session altered. Elapsed: 00:00:00.00 SDR_TEST - SESSION 2> alter system flush buffer_cache 2 / System altered. Elapsed: 00:00:00.21 SDR_TEST - SESSION 2> delete /* SESSION 1 */ from a where id = :id 2 / 1 row deleted. Elapsed: 00:00:00.25 SDR_TEST - SESSION 2> rollback 2 / Rollback complete. Elapsed: 00:00:00.01
Check the trace file to see what we get:
WAIT #47830986253416: nam='cell smart table scan' ela= 48 cellhash#=281363246 p2=0 p3=0 obj#=761740 tim=1308902665561263 WAIT #47830986253416: nam='cell smart table scan' ela= 8 cellhash#=281363246 p2=0 p3=0 obj#=761740 tim=1308902665561338 WAIT #47830986253416: nam='cell smart table scan' ela= 3944 cellhash#=281363246 p2=0 p3=0 obj#=761740 tim=1308902665565316 WAIT #47830986253416: nam='cell smart table scan' ela= 10 cellhash#=764630511 p2=0 p3=0 obj#=761740 tim=1308902665565394 WAIT #47830986253416: nam='cell smart table scan' ela= 3869 cellhash#=764630511 p2=0 p3=0 obj#=761740 tim=1308902665569302 WAIT #47830986253416: nam='cell smart table scan' ela= 14 cellhash#=764630511 p2=0 p3=0 obj#=761740 tim=1308902665569389 WAIT #47830986253416: nam='cell smart table scan' ela= 6876 cellhash#=764630511 p2=0 p3=0 obj#=761740 tim=1308902665576298 WAIT #47830986253416: nam='cell smart table scan' ela= 26 cellhash#=3970173858 p2=0 p3=0 obj#=761740 tim=1308902665576745 WAIT #47830986253416: nam='cell smart table scan' ela= 25 cellhash#=3970173858 p2=0 p3=0 obj#=761740 tim=1308902665576816 WAIT #47830986253416: nam='cell smart table scan' ela= 25 cellhash#=281363246 p2=0 p3=0 obj#=761740 tim=1308902665576988
… Smart Scan! But, hold on a minute, that means it’s using direct reads.
Set _serial_direct_read to NEVER, set new trace file identifier, flush buffer cache, run delete from session 1 in session 2 and rollback:
SDR_TEST - SESSION 2> alter session set "_serial_direct_read"=never 2 / Session altered. Elapsed: 00:00:00.00 SDR_TEST - SESSION 2> alter session set tracefile_identifier='sdr_2c' 2 / Session altered. Elapsed: 00:00:00.00 SDR_TEST - SESSION 2> alter system flush buffer_cache 2 / System altered. Elapsed: 00:00:00.13 SDR_TEST - SESSION 2> delete /* SESSION 1 */ from a where id = :id 2 / 1 row deleted. Elapsed: 00:00:00.20 SDR_TEST - SESSION 2> rollback 2 / Rollback complete. Elapsed: 00:00:00.00
Check the trace file to see what we get:
WAIT #47830984369624: nam='cell smart table scan' ela= 201 cellhash#=3108293528 p2=0 p3=0 obj#=761740 tim=1308902665858678 WAIT #47830984369624: nam='cell smart table scan' ela= 8 cellhash#=3108293528 p2=0 p3=0 obj#=761740 tim=1308902665858742 WAIT #47830984369624: nam='cell smart table scan' ela= 201 cellhash#=3108293528 p2=0 p3=0 obj#=761740 tim=1308902665858973 WAIT #47830984369624: nam='cell smart table scan' ela= 8 cellhash#=3108293528 p2=0 p3=0 obj#=761740 tim=1308902665859048 WAIT #47830984369624: nam='cell smart table scan' ela= 288 cellhash#=3108293528 p2=0 p3=0 obj#=761740 tim=1308902665859367 WAIT #47830984369624: nam='cell smart table scan' ela= 7 cellhash#=3108293528 p2=0 p3=0 obj#=761740 tim=1308902665859415 WAIT #47830984369624: nam='cell smart table scan' ela= 23 cellhash#=3108293528 p2=0 p3=0 obj#=761740 tim=1308902665859471 WAIT #47830984369624: nam='cell smart table scan' ela= 7 cellhash#=3108293528 p2=0 p3=0 obj#=761740 tim=1308902665859521 WAIT #47830984369624: nam='cell smart table scan' ela= 868 cellhash#=3108293528 p2=0 p3=0 obj#=761740 tim=1308902665860420 WAIT #47830984369624: nam='cell smart table scan' ela= 7 cellhash#=3108293528 p2=0 p3=0 obj#=761740 tim=1308902665860485
… Still doing Smart Scan!
During his UKOUG presentation Tanel emphasised that the CBO knows nothing about Smart Scan, which is confirmed by execution plans and CBO traces, but it seems that there must be information about how the execution plan will actually be executed stored in memory, and associated with the execution plan… I don’t know where that is yet, but I’m guessing Tanel does.
Anyway, the key point is that the value of _serial_direct_read is not considered by the CBO to be part of the “optimizer environment”, but it appears (to me) that it should be. After all, wouldn’t you expect ALTER SESSION to do just that, rather than having the ability to affect other sessions? I appreciate that this is a hidden parameter, which shouldn’t be used without agreement from Oracle Support, but it seems like something has been overlooked. In Kerry’s PDF Tuning Exadata he mentions that _serial_direct_read is not compatible with the OPT_PARAM hint, which is a more succinct way of saying all the above, but the implications weren’t clear to me when I first read that.
Maybe at some point the parameter will lose its underscore and become part of the the optimizer environment… Time will tell.
ORAganism 
Nice post. _serial_direct_path_read is definitely a weird parameter. Changing it’s value does not appear to invalidate existing cursors – although is definitely changes the behavior. I haven’t spoken with Tanel directly about whether he has investigated where this information might be stored, but it definitely appears that it is stored some where. I agree that at some point the optimizer should become aware of whether it’s running on Exadata or not (costing should probably be different when the option of Smart Scans is available). And the decision of whether to do direct reads or nots should probably be part of the optimizer environment as well.
Kerry