11g direct path read 和 10046 10949 event

最新推荐文章于 2021-12-16 14:08:41 发布

cqhiabc50405

最新推荐文章于 2021-12-16 14:08:41 发布

阅读量127

点赞数

11g 相比较10G出现了很多的新功能，其中很多还是很有用的。但是新的事物刚出来，就是不太稳定，虽然到现在11G已经是11.2.0.3。

其中一个就是Oracle 11g has implemented direct path read for the serial full table scan，摘录自《Disabling direct path read for the serial full table scan – 11g》，http://afatkulin.blogspot.com/2009/01/11g-adaptive-direct-path-reads-what-is.html 等：
There have been changes in 11g in the heuristics to choose between direct path reads or reads through buffer cache for serial table scans.In 10g, serial table scans for “large” tables used to go through cache (by default) which is not the case anymore. In 11g, this decision to read via direct path or through cache is based on the size of the table, buffer cache size and various other stats.

When direct path reads starts to happen?

It is known that somewhat reliable figure is your _small_table_threshold multiplied by 5 (mentioned by Tanel Poder on oracle-l recently). You can discover it using quick and dirty test case similar to this:

关于 5 * _small_table_threshold 这一点我没有验证过，但是是有这么一个值的存在：

1 先确定版本和small_table_threshold的大小

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

[oracle@RHEL55_node1 ~]$

_small_table_threshold 119 lower threshold level of table size for direct reads

2 “small table” test ：

SQL> create table t1(c1 number, c2 char(2000), c3 char(2000), c4 char(2000)) tablespace data01;

Table created.

SQL> insert into t1

2 select level, 'x', 'x', 'x'

3 from dual connect by level <= 10 + 2*&sth;

Enter value for sth: 20

old 3: from dual connect by level <= 10 + 2*&sth

new 3: from dual connect by level <= 10 + 2*20

50 rows created.

SQL> commit;

Commit complete.

SQL> alter system flush buffer_cache;

SQL> set linesize 300

SQL> select vm.sid, vs.name, vm.value

2 from v$mystat vm, v$sysstat vs

3 where vm.statistic# = vs.statistic#

4 and vs.name in ('cleanouts only - consistent read gets',

5 'session logical reads',

6 'physical reads',

7 'physical reads direct','redo size');

SID NAME VALUE

---------- ---------------------------------------------------------------- ----------

1 session logical reads 3

1 physical reads 2

1 physical reads direct 0

1 redo size 0

1 cleanouts only - consistent read gets 0

SQL> alter session set events '10046 trace name context forever, level 8';

Session altered.

SQL> select count(*) from t1;

COUNT(*)

----------

SQL> alter session set events '10046 trace name context off';

Session altered.

SQL> select vm.sid, vs.name, vm.value

2 from v$mystat vm, v$sysstat vs

3 where vm.statistic# = vs.statistic#

4 and vs.name in ('cleanouts only - consistent read gets',

5 'session logical reads',

6 'physical reads',

7 'physical reads direct','redo size');

SID NAME VALUE

---------- ---------------------------------------------------------------- ----------

1 session logical reads 55

1 physical reads 53

1 physical reads direct 0

1 redo size 0

1 cleanouts only - consistent read gets 0

select count(*)

from

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.00 0.00 0 0 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 2 0.03 0.07 51 52 0 1

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 4 0.03 0.07 51 52 0 1

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: SYS

Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation

---------- ---------- ---------- ---------------------------------------------------

1 1 1 SORT AGGREGATE (cr=52 pr=51 pw=0 time=71573 us)

50 50 50 TABLE ACCESS FULL T1 (cr=52 pr=51 pw=0 time=60736 us cost=15 size=0 card=50)

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

Disk file operations I/O 1 0.00 0.00

db file sequential read 4 0.00 0.00

db file scattered read 17 0.00 0.00

SQL*Net message from client 2 8.14 8.15

3 “big table” test ：

SQL> set linesize 300

SQL> select vm.sid, vs.name, vm.value

2 from v$mystat vm, v$sysstat vs

3 where vm.statistic# = vs.statistic#

4 and vs.name in ('cleanouts only - consistent read gets',

5 'session logical reads',

6 'physical reads',

7 'physical reads direct','redo size');

SID NAME VALUE

---------- ------------------------------------------------------------ ----------

26 session logical reads 46

26 physical reads 11

26 physical reads direct 0

26 redo size 0

26 cleanouts only - consistent read gets 0

SQL> select count(*) from t1;

COUNT(*)

----------

605

SQL> select vm.sid, vs.name, vm.value

2 from v$mystat vm, v$sysstat vs

3 where vm.statistic# = vs.statistic#

4 and vs.name in ('cleanouts only - consistent read gets',

5 'session logical reads',

6 'physical reads',

7 'physical reads direct','redo size');

SID NAME VALUE

---------- ------------------------------------------------------------ ----------

26 session logical reads 773

26 physical reads 790

26 physical reads direct 622

26 redo size 0

26 cleanouts only - consistent read gets 0

select count(*)

from

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.00 0.00 0 0 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 2 0.16 0.85 632 637 1 1

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 4 0.16 0.85 632 637 1 1

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: SYS

Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation

---------- ---------- ---------- ---------------------------------------------------

1 1 1 SORT AGGREGATE (cr=637 pr=632 pw=0 time=851875 us)

605 605 605 TABLE ACCESS FULL T1 (cr=637 pr=632 pw=0 time=2631762 us cost=171 size=0 card=671)

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

Disk file operations I/O 1 0.00 0.00

db file sequential read 3 0.04 0.05

db file parallel read 1 0.01 0.01

direct path read 315 0.45 0.61

SQL*Net message from client 2 4.65 4.65

4 对于10046 trace 里query：

physical reads direct是包含在session logical reads里面的。即，逻辑读的统计信息包含物理读的统计信息，这个也是我一开始挺疑惑的地方：

下面这个是工具tkprof输出的，我们绝大部分人都是理解为通过buffer get（在一致性检索方式获得块时，执行逻辑I/O次数；）：

********************************************************************************

count = number of times OCI procedure was executed

cpu = cpu time in seconds executing

elapsed = elapsed time in seconds executing

disk = number of physical reads of buffers from disk

query = number of buffers gotten for consistent read

current = number of buffers gotten in current mode (usually for update)

rows = number of rows processed by the fetch or execute call

********************************************************************************

其实这个从trace文件里可以看出绝大部分都是direct path read：

PARSING IN CURSOR #10742416 len=23 dep=0 uid=0 ct=3 lid=0 tim=1331353130977474 hv=4235652837 ad='265585c8' sqlid='5bc0v4my7dvr5'

select count(*) from t1

END OF STMT

PARSE #10742416:c=0,e=1601,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3724264953,tim=1331353130977453

EXEC #10742416:c=0,e=1456,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3724264953,tim=1331353130979196

WAIT #10742416: nam='SQL*Net message to client' ela= 29 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1331353130979564

WAIT #10742416: nam='Disk file operations I/O' ela= 1360 FileOperation=2 fileno=5 filetype=2 obj#=13562 tim=1331353130984136

WAIT #10742416: nam='db file sequential read' ela= 360 file#=5 block#=266 blocks=1 obj#=13562 tim=1331353130987120

WAIT #10742416: nam='db file sequential read' ela= 41206 file#=5 block#=1024 blocks=1 obj#=13562 tim=1331353131154473

WAIT #10742416: nam='db file sequential read' ela= 12111 file#=5 block#=265 blocks=1 obj#=13562 tim=1331353131166898

WAIT #10742416: nam='db file parallel read' ela= 19615 files=1 blocks=7 requests=7 obj#=13562 tim=1331353131187899

WAIT #10742416: nam='direct path read' ela= 827 file number=5 first dba=267 block cnt=1 obj#=13562 tim=1331353131190685

WAIT #10742416: nam='direct path read' ela= 349 file number=5 first dba=268 block cnt=2 obj#=13562 tim=1331353131191225

.........................省去几百行.....................................................................................

WAIT #10742416: nam='direct path read' ela= 369 file number=5 first dba=1532 block cnt=2 obj#=13562 tim=1331353131829358

WAIT #10742416: nam='direct path read' ela= 76 file number=5 first dba=1534 block cnt=2 obj#=13562 tim=1331353131829539

FETCH #10742416:c=168010,e=851525,p=632,cr=637,cu=1,mis=0,r=1,dep=0,og=1,plh=3724264953,tim=1331353131831374

STAT #10742416 id=1 cnt=1 pid=0 pos=1 bj=0 p='SORT AGGREGATE (cr=637 pr=632 pw=0 time=851875 us)'

STAT #10742416 id=2 cnt=605 pid=1 pos=1 bj=13562 p='TABLE ACCESS FULL T1 (cr=637 pr=632 pw=0 time=2631762 us cost=171 size=0 card=671)'

WAIT #10742416: nam='SQL*Net message from client' ela= 2629 driver id=1650815232 #bytes=1 p3=0 obj#=13562 tim=1331353131836316

FETCH #10742416:c=0,e=23,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=3724264953,tim=1331353131836483

WAIT #10742416: nam='SQL*Net message to client' ela= 17 driver id=1650815232 #bytes=1 p3=0 obj#=13562 tim=1331353131836638

*** 2012-03-10 12:18:56.488

WAIT #10742416: nam='SQL*Net message from client' ela= 4650605 driver id=1650815232 #bytes=1 p3=0 obj#=13562 tim=1331353136488110

CLOSE #10742416:c=0,e=38,dep=0,type=0,tim=1331353136488429

5 ORACLE在读一张表之间如何能预知表的大小

这个是可以计算出来的，也就是统计信息：

SQL> exec dbms_stats.gather_table_stats(user, 'T1',no_invalidate=>false);

PL/SQL procedure successfully completed.

SQL> exec show_space('T1','auto','T','Y');

Total Blocks............................640

Total Bytes.............................5242880

Unused Blocks...........................0

Unused Bytes............................0

Last Used Ext FileId....................5

Last Used Ext BlockId...................768

Last Used Block.........................128

*************************************************

The segment is analyzed

0% -- 25% free space blocks.............0

0% -- 25% free space bytes..............0

25% -- 50% free space blocks............1

25% -- 50% free space bytes.............8192

50% -- 75% free space blocks............0

50% -- 75% free space bytes.............0

75% -- 100% free space blocks...........17

75% -- 100% free space bytes............139264

Unused Blocks...........................0

Unused Bytes............................0

Total Blocks............................604

Total bytes.............................4947968

PL/SQL procedure successfully completed.

SQL> select TABLE_NAME,BLOCKS,NUM_ROWS from user_tables where table_name='T1';

TABLE_NAME BLOCKS NUM_ROWS

------------------------------ ---------- ----------

T1 622 605

手工设置了统计信息以后，可以骗过cost based analysis of direct path i/o

6 how to disable this feature

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.
Don’t forget 10949 event when you want to disable this great feature for any reason!

alter session set events '10949 trace name context forever, level 1';
alter system set event= '10949 trace name context forever, level 1' scope=spfile;

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.
--摘录自《Disabling direct path read for the serial full table scan–11g》

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/758322/viewspace-718274/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/758322/viewspace-718274/