Fun with _SERIAL_DIRECT_READ

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.

About these ads

One thought on “Fun with _SERIAL_DIRECT_READ

  1. 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

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