爱悠闲 > 禁用Direct Path read等待事件实验分析

禁用Direct Path read等待事件实验分析

分类: oracle db  |  标签: 11g,direct path read  |  作者: cn_mos 相关  |  发布日期 : 2014-12-11  |  热度 : 22°
禁用direct path read的方式有两种:
(1)使用10949事件禁用,系统级需要重启数据库
(2)使用 _serial_direct_read隐藏参数,系统级设置不需要重启数据库
direct path read等待事件通过10949事件关闭是否起作用,不只是与大表阀值、buffer cache有关, 还与sga有关系
经测试后将测试结果总结如下表:
情况 大于大表
阀值500M
大于5倍
buffer_cache
大于2倍
(sga-redo buffer)
有无DPR
事件
10949是否
能禁用DPR
_serial_direct_read
是否能禁用DPR
1
2
3
4



情况1:表大于大表阀值,大于5倍buffer cache,大于2倍sga 100949无法关闭dpr--_serial_direct_read可以关闭dpr

SQL> select bytes/1024/1024 from dba_segments where segment_name='DPR2';

BYTES/1024/1024
---------------
           1985
           
SQL> select (622206976-7581696)/1024/1024*2 "2*SGA Mb" from dual;

  2*SGA Mb
----------
1172.30469

SQL> select 150994944/1024/1024*5 "5*Buffercache" from dual;

5*Buffercache
-------------
          720      
          

SQL> alter system flush buffer_cache;

System altered.

SQL> alter session set "_serial_direct_read" = never;

Session altered.

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

Session altered.

SQL> select count(*) from dpr2;

  COUNT(*)
----------
  17132033

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

Session altered.

END OF STMT
PARSE #47473721844488:c=34995,e=93321,p=202,cr=107,cu=0,mis=1,r=0,dep=0,og=1,plh=284904835,tim=1413175980762855
EXEC #47473721844488:c=0,e=23,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=284904835,tim=1413175980762941
WAIT #47473721844488: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=89319 tim=1413175980762964
WAIT #47473721844488: nam='db file scattered read' ela= 566 file#=1 block#=244897 blocks=7 obj#=89319 tim=1413175980763648
WAIT #47473721844488: nam='db file scattered read' ela= 484 file#=1 block#=244904 blocks=8 obj#=89319 tim=1413175980764313
WAIT #47473721844488: nam='db file scattered read' ela= 625 file#=1 block#=244912 blocks=8 obj#=89319 tim=1413175980765157
WAIT #47473721844488: nam='db file scattered read' ela= 500 file#=1 block#=244920 blocks=8 obj#=89319 tim=1413175980765884
WAIT #47473721844488: nam='db file scattered read' ela= 519 file#=1 block#=244928 blocks=8 obj#=89319 tim=1413175980766567
WAIT #47473721844488: nam='db file scattered read' ela= 310 file#=1 block#=244936 blocks=8 obj#=89319 tim=1413175980767068
WAIT #47473721844488: nam='db file scattered read' ela= 322 file#=1 block#=244944 blocks=8 obj#=89319 tim=1413175980767579
WAIT #47473721844488: nam='db file scattered read' ela= 305 file#=1 block#=244952 blocks=8 obj#=89319 tim=1413175980768080
WAIT #47473721844488: nam='db file scattered read' ela= 347 file#=1 block#=244960 blocks=8 obj#=89319 tim=1413175980768659
WAIT #47473721844488: nam='db file scattered read' ela= 307 file#=1 block#=244968 blocks=8 obj#=89319 tim=1413175980769135
WAIT #47473721844488: nam='db file scattered read' ela= 693 file#=1 block#=244976 blocks=8 obj#=89319 tim=1413175980772024
WAIT #47473721844488: nam='db file scattered read' ela= 421 file#=1 block#=244984 blocks=8 obj#=89319 tim=1413175980772638
WAIT #47473721844488: nam='db file scattered read' ela= 451 file#=1 block#=244736 blocks=8 obj#=89319 tim=1413175980773281
WAIT #47473721844488: nam='db file scattered read' ela= 407 file#=1 block#=244744 blocks=8 obj#=89319 tim=1413175980773820


SQL> show sga

Total System Global Area  622206976 bytes
Fixed Size                  2255672 bytes
Variable Size             461374664 bytes
Database Buffers          150994944 bytes
Redo Buffers                7581696 bytes
SQL> 
SQL> 
SQL> alter session set "_serial_direct_read" = auto;

Session altered.

SQL> alter system flush buffer_cache;

System altered.

SQL> select bytes/1024/1024 from dba_segments where segment_name='DPR2';

BYTES/1024/1024
---------------
           1985

SQL> alter session set events '10949 trace name context forever,level 1';

Session altered.

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

Session altered.

SQL> select count(*) from dpr2;

  COUNT(*)
----------
  17132033

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

Session altered.

select count(*) from dpr2

*** 2014-10-13 14:21:20.976
WAIT #47473721516304: nam='SQL*Net message from client' ela= 25254710 driver id=1650815232 #bytes=1 p3=0 obj#=9 tim=1413181280976463
CLOSE #47473721516304:c=0,e=7,dep=0,type=1,tim=1413181280976643
=====================
PARSING IN CURSOR #47473721680032 len=25 dep=0 uid=0 oct=3 lid=0 tim=1413181280977486 hv=1816038448 ad='7afc33f8' sqlid='8sfq87pq3x31h'
select count(*) from dpr2
END OF STMT
PARSE #47473721680032:c=1000,e=803,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=284904835,tim=1413181280977484
EXEC #47473721680032:c=1000,e=2301,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=284904835,tim=1413181280979874
WAIT #47473721680032: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=9 tim=1413181280979932
WAIT #47473721680032: nam='db file sequential read' ela= 574 file#=1 block#=244896 blocks=1 obj#=89319 tim=1413181280980632
WAIT #47473721680032: nam='direct path read' ela= 1609 file number=1 first dba=244897 block cnt=31 obj#=89319 tim=1413181280983170
WAIT #47473721680032: nam='direct path read' ela= 3083 file number=1 first dba=244736 block cnt=32 obj#=89319 tim=1413181280987154
WAIT #47473721680032: nam='direct path read' ela= 3172 file number=1 first dba=261440 block cnt=64 obj#=89319 tim=1413181280991104
WAIT #47473721680032: nam='direct path read' ela= 3392 file number=1 first dba=261568 block cnt=64 obj#=89319 tim=1413181280996207
WAIT #47473721680032: nam='direct path read' ela= 3422 file number=1 first dba=261696 block cnt=64 obj#=89319 tim=1413181281000467
WAIT #47473721680032: nam='direct path read' ela= 2837 file number=1 first dba=261824 block cnt=64 obj#=89319 tim=1413181281004869
WAIT #47473721680032: nam='direct path read' ela= 3023 file number=1 first dba=261952 block cnt=64 obj#=89319 tim=1413181281008771
WAIT #47473721680032: nam='direct path read' ela= 2360 file number=1 first dba=262080 block cnt=64 obj#=89319 tim=1413181281013500
WAIT #47473721680032: nam='direct path read' ela= 565 file number=1 first dba=262144 block cnt=64 obj#=89319 tim=1413181281014533
WAIT #47473721680032: nam='direct path read' ela= 1406 file number=1 first dba=262272 block cnt=64 obj#=89319 tim=1413181281016876
WAIT #47473721680032: nam='direct path read' ela= 1361 file number=1 first dba=262400 block cnt=64 obj#=89319 tim=1413181281019316
WAIT #47473721680032: nam='direct path read' ela= 3019 file number=1 first dba=262528 block cnt=64 obj#=89319 tim=1413181281023355
WAIT #47473721680032: nam='direct path read' ela= 1007 file number=1 first dba=262656 block cnt=64 obj#=89319 tim=1413181281024893
WAIT #47473721680032: nam='direct path read' ela= 413 file number=1 first dba=262720 block cnt=64 obj#=89319 tim=1413181281025557
WAIT #47473721680032: nam='direct path read' ela= 659 file number=1 first dba=262784 block cnt=64 obj#=89319 tim=1413181281026491  

情况2:表大于大表阀值,大于5倍buffer cache,小于2倍sga 100949无法关闭dpr:
SQL> show sga

Total System Global Area  622206976 bytes
Fixed Size                  2255672 bytes
Variable Size             461374664 bytes
Database Buffers          150994944 bytes
Redo Buffers                7581696 bytes
SQL> 

SQL> select bytes/1024/1024 from dba_segments where segment_name='DPR3';

BYTES/1024/1024
---------------
            728

SQL> select (622206976-7581696)/1024/1024*2 "2*SGA Mb" from dual;

  2*SGA Mb
----------
1172.30469

SQL> select 150994944/1024/1024*5 "5*Buffercache" from dual;

5*Buffercache
-------------
          720   

SQL> alter system flush buffer_cache;

System altered.

SQL> alter session set events '10949 trace name context forever, level 1';

Session altered.

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

Session altered.

SQL> select count(*) from dpr3 order by object_id;

  COUNT(*)
----------
   6408263

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

Session altered.

select count(*) from dpr3 order by object_id
select count(*) from dpr3 order by object_id
END OF STMT
PARSE #47473724288976:c=37994,e=70607,p=239,cr=111,cu=0,mis=1,r=0,dep=0,og=1,plh=2136408865,tim=1413175549472358
EXEC #47473724288976:c=0,e=20,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2136408865,tim=1413175549472438
WAIT #47473724288976: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=89336 tim=1413175549472473
WAIT #47473724288976: nam='direct path read' ela= 1261 file number=1 first dba=97305 block cnt=39 obj#=89336 tim=1413175549474655
WAIT #47473724288976: nam='direct path read' ela= 291 file number=1 first dba=97344 block cnt=64 obj#=89336 tim=1413175549475233
WAIT #47473724288976: nam='direct path read' ela= 212 file number=1 first dba=244768 block cnt=24 obj#=89336 tim=1413175549475693
WAIT #47473724288976: nam='direct path read' ela= 1354 file number=1 first dba=162944 block cnt=64 obj#=89336 tim=1413175549477189
WAIT #47473724288976: nam='direct path read' ela= 1574 file number=1 first dba=163072 block cnt=64 obj#=89336 tim=1413175549479706
WAIT #47473724288976: nam='direct path read' ela= 492 file number=1 first dba=163136 block cnt=64 obj#=89336 tim=1413175549480708
WAIT #47473724288976: nam='direct path read' ela= 251 file number=1 first dba=163200 block cnt=64 obj#=89336 tim=1413175549481320
WAIT #47473724288976: nam='direct path read' ela= 434 file number=1 first dba=163264 block cnt=64 obj#=89336 tim=1413175549482100
WAIT #47473724288976: nam='direct path read' ela= 900 file number=1 first dba=163328 block cnt=64 obj#=89336 tim=1413175549483266
WAIT #47473724288976: nam='direct path read' ela= 508 file number=1 first dba=163392 block cnt=64 obj#=89336 tim=1413175549484146
WAIT #47473724288976: nam='direct path read' ela= 332 file number=1 first dba=163456 block cnt=64 obj#=89336 tim=1413175549484816
WAIT #47473724288976: nam='direct path read' ela= 263 file number=1 first dba=163520 block cnt=64 obj#=89336 tim=1413175549485376
WAIT #47473724288976: nam='direct path read' ela= 737 file number=1 first dba=163584 block cnt=64 obj#=89336 tim=1413175549486373
WAIT #47473724288976: nam='direct path read' ela= 316 file number=1 first dba=163648 block cnt=64 obj#=89336 tim=1413175549487028
WAIT #47473724288976: nam='direct path read' ela= 379 file number=1 first dba=163712 block cnt=64 obj#=89336 tim=1413175549487656

表大于大表阀值,大于5倍buffer cache,小于2倍sga 100949无法关闭dpr--_serial_direct_read可以关闭dpr


SQL> alter system flush buffer_cache;

System altered.

SQL> alter session set "_serial_direct_read" = never;

Session altered.

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

Session altered.

SQL> select count(*) from dpr3 order by object_id desc;

  COUNT(*)
----------
   6408263

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

Session altered.

PARSING IN CURSOR #47950832525864 len=49 dep=0 uid=0 oct=3 lid=0 tim=1413174983317469 hv=3288583592 ad='6b8fcf18' sqlid='bsqfkjg207md8'
select count(*) from dpr3 order by object_id desc
END OF STMT
PARSE #47950832525864:c=26995,e=56678,p=225,cr=89,cu=0,mis=1,r=0,dep=0,og=1,plh=2136408865,tim=1413174983317467
EXEC #47950832525864:c=0,e=32,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2136408865,tim=1413174983317601
WAIT #47950832525864: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=89336 tim=1413174983317639
WAIT #47950832525864: nam='db file scattered read' ela= 865 file#=1 block#=97305 blocks=7 obj#=89336 tim=1413174983318755
WAIT #47950832525864: nam='db file scattered read' ela= 1009 file#=1 block#=97312 blocks=8 obj#=89336 tim=1413174983320229
WAIT #47950832525864: nam='db file scattered read' ela= 886 file#=1 block#=97320 blocks=8 obj#=89336 tim=1413174983321487
WAIT #47950832525864: nam='db file scattered read' ela= 855 file#=1 block#=97328 blocks=8 obj#=89336 tim=1413174983322782
WAIT #47950832525864: nam='db file scattered read' ela= 894 file#=1 block#=97336 blocks=8 obj#=89336 tim=1413174983324191
WAIT #47950832525864: nam='db file scattered read' ela= 471 file#=1 block#=97344 blocks=8 obj#=89336 tim=1413174983325037
WAIT #47950832525864: nam='db file scattered read' ela= 350 file#=1 block#=97352 blocks=8 obj#=89336 tim=1413174983325675
WAIT #47950832525864: nam='db file scattered read' ela= 332 file#=1 block#=97360 blocks=8 obj#=89336 tim=1413174983326216
WAIT #47950832525864: nam='db file scattered read' ela= 383 file#=1 block#=97368 blocks=8 obj#=89336 tim=1413174983326761
WAIT #47950832525864: nam='db file scattered read' ela= 379 file#=1 block#=97376 blocks=8 obj#=89336 tim=1413174983327422
WAIT #47950832525864: nam='db file scattered read' ela= 1118 file#=1 block#=97384 blocks=8 obj#=89336 tim=1413174983329268
WAIT #47950832525864: nam='db file scattered read' ela= 297 file#=1 block#=97392 blocks=8 obj#=89336 tim=1413174983329828
WAIT #47950832525864: nam='db file scattered read' ela= 364 file#=1 block#=97400 blocks=8 obj#=89336 tim=1413174983330428 

情况3:表大于大表阀值,小于5倍buffer cache,大于2倍sga 100949无法关闭dpr--_serial_direct_read可以关闭dpr----
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1043886080 bytes
Fixed Size                  2259840 bytes
Variable Size             599786624 bytes
Database Buffers          436207616 bytes
Redo Buffers                5632000 bytes
Database mounted.
Database opened.
SQL> select (1043886080-5632000)/1024/1024*2 "2*(SGA-redo buffer)" from dual;

2*(SGA-redo buffer)
-------------------
          1980.3125

SQL> select 436207616/1024/1024*5 "5*Buffer_cache" from dual;

5*Buffer_cache
--------------
          2080

SQL> select bytes/1024/1024 from dba_segments where segment_name='DPR2';

BYTES/1024/1024
---------------
           1985

SQL> alter session set "_serial_direct_read" = auto;

Session altered.

SQL> alter session set events '10949 trace name context forever,level 1';

Session altered.

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

Session altered.

SQL> select count(*) from dpr2 order by object_type;

  COUNT(*)
----------
  17132033

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

Session altered.

SQL> SELECT    a.VALUE || b.symbol || c.instance_name || '_ora_' || d.spid || '.trc' trace_file
  2    FROM (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') a,
  3                 (SELECT SUBSTR (VALUE, -6, 1) symbol FROM v$parameter
  4           WHERE NAME = 'user_dump_dest') b,
  5         (SELECT instance_name FROM v$instance) c,
  6         (SELECT spid FROM v$session s, v$process p, v$mystat m
  7           WHERE s.paddr = p.addr AND s.SID = m.SID AND m.statistic# = 0) d
  8  /

TRACE_FILE
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/sdcnc/sdcnc1/trace/sdcnc1_ora_12214.trc

SQL> 



*** 2014-10-13 15:03:37.814
WAIT #47882322876544: nam='SQL*Net message from client' ela= 23878799 driver id=1650815232 #bytes=1 p3=0 obj#=35 tim=1413183817814277
CLOSE #47882322876544:c=0,e=21,dep=0,type=1,tim=1413183817814387
WAIT #47882322872008: nam='row cache lock' ela= 1227 cache id=8 mode=0 request=3 obj#=35 tim=1413183817816481
=====================
PARSING IN CURSOR #47882321730976 len=202 dep=1 uid=0 oct=3 lid=0 tim=1413183817816724 hv=3819099649 ad='941feee0' sqlid='3nkd3g3ju5ph1'
select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
/object_type
STAT #47882322973584 id=2 cnt=4246 pid=1 pos=1 obj=89319 op='TABLE ACCESS SAMPLE DPR2 (cr=86 pr=188 pw=0 time=1962 us cost=19 size=61752 card=5146)'
CLOSE #47882322973584:c=0,e=7,dep=1,type=0,tim=1413183817876067
WAIT #47882322872008: nam='row cache lock' ela= 435 cache id=16 mode=0 request=3 obj#=89319 tim=1413183817876650
BINDS #47882322998704:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0
  kxsbbbfp=2b8c78d16880  bln=22  avl=04  flg=05
  value=89319
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=2b8c78d16898  bln=22  avl=02  flg=01
  value=6
EXEC #47882322998704:c=0,e=118,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,plh=2239883476,tim=1413183817876828
FETCH #47882322998704:c=0,e=13,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=3,plh=2239883476,tim=1413183817876860
CLOSE #47882322998704:c=0,e=2,dep=1,type=3,tim=1413183817876884
=====================
PARSING IN CURSOR #47882322872008 len=46 dep=0 uid=0 oct=3 lid=0 tim=1413183817877243 hv=3204789967 ad='934471b0' sqlid='37yp31fzhadqg'
select count(*) from dpr2 order by object_type
END OF STMT
PARSE #47882322872008:c=32995,e=62828,p=190,cr=109,cu=0,mis=1,r=0,dep=0,og=1,plh=284904835,tim=1413183817877242
EXEC #47882322872008:c=1000,e=91,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=284904835,tim=1413183817877373
WAIT #47882322872008: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=89319 tim=1413183817877419
WAIT #47882322872008: nam='reliable message' ela= 1182 channel context=2635675096 channel handle=2497253272 broadcast message=2636738920 obj#=89319 tim=1413183817878851
WAIT #47882322872008: nam='enq: KO - fast object checkpoint' ela= 5568 name|mode=1263468550 2=65569 0=1 obj#=89319 tim=1413183817884465
WAIT #47882322872008: nam='direct path read' ela= 3263 file number=1 first dba=244897 block cnt=95 obj#=89319 tim=1413183817888481
WAIT #47882322872008: nam='direct path read' ela= 3831 file number=1 first dba=261376 block cnt=128 obj#=89319 tim=1413183817893744
WAIT #47882322872008: nam='direct path read' ela= 4242 file number=1 first dba=261632 block cnt=128 obj#=89319 tim=1413183817899427
WAIT #47882322872008: nam='direct path read' ela= 3404 file number=1 first dba=261888 block cnt=128 obj#=89319 tim=1413183817904356
WAIT #47882322872008: nam='direct path read' ela= 4149 file number=1 first dba=262144 block cnt=128 obj#=89319 tim=1413183817910057
WAIT #47882322872008: nam='direct path read' ela= 3886 file number=1 first dba=262400 block cnt=128 obj#=89319 tim=1413183817915198
WAIT #47882322872008: nam='direct path read' ela= 3368 file number=1 first dba=262656 block cnt=128 obj#=89319 tim=1413183817920091
WAIT #47882322872008: nam='direct path read' ela= 3840 file number=1 first dba=262912 block cnt=128 obj#=89319 tim=1413183817925341
WAIT #47882322872008: nam='direct path read' ela= 3353 file number=1 first dba=263168 block cnt=128 obj#=89319 tim=1413183817929913
WAIT #47882322872008: nam='direct path read' ela= 3595 file number=1 first dba=263424 block cnt=128 obj#=89319 tim=1413183817935020
WAIT #47882322872008: nam='direct path read' ela= 3327 file number=1 first dba=263680 block cnt=128 obj#=89319 tim=1413183817939872
WAIT #47882322872008: nam='direct path read' ela= 3589 file number=1 first dba=263936 block cnt=128 obj#=89319 tim=1413183817945086
WAIT #47882322872008: nam='direct path read' ela= 3265 file number=1 first dba=264192 block cnt=128 obj#=89319 tim=1413183817950014
WAIT #47882322872008: nam='direct path read' ela= 855 file number=1 first dba=264448 block cnt=128 obj#=89319 tim=1413183817953062   


SQL> alter session set "_serial_direct_read" = never
  2  ;

Session altered.

SQL> alter system flush buffer_cache;

System altered.

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

Session altered.

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

Session altered.

SQL> select count(*) from dpr2 order by object_id;

  COUNT(*)
----------
  17132033

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

Session altered.

SQL>

*** 2014-10-13 15:03:37.814
WAIT #47882322876544: nam='SQL*Net message from client' ela= 23878799 driver id=1650815232 #bytes=1 p3=0 obj#=35 tim=1413183817814277
CLOSE #47882322876544:c=0,e=21,dep=0,type=1,tim=1413183817814387
WAIT #47882322872008: nam='row cache lock' ela= 1227 cache id=8 mode=0 request=3 obj#=35 tim=1413183817816481
=====================
PARSING IN CURSOR #47882321730976 len=202 dep=1 uid=0 oct=3 lid=0 tim=1413183817816724 hv=3819099649 ad='941feee0' sqlid='3nkd3g3ju5ph1'
select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
/object_id
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0
  kxsbbbfp=2b8c78c75fd0  bln=22  avl=04  flg=05
  value=89319
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=2b8c78c75fe8  bln=22  avl=02  flg=01
  value=4
EXEC #47882322014304:c=2000,e=1555,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=3,plh=2239883476,tim=1413185630139873
WAIT #47882322014304: nam='db file sequential read' ela= 475 file#=1 block#=3025 blocks=1 obj#=450 tim=1413185630140429
WAIT #47882322014304: nam='db file sequential read' ela= 425 file#=1 block#=96385 blocks=1 obj#=450 tim=1413185630140962
FETCH #47882322014304:c=0,e=1141,p=2,cr=2,cu=0,mis=0,r=0,dep=1,og=3,plh=2239883476,tim=1413185630141026
STAT #47882322014304 id=1 cnt=0 pid=0 pos=1 obj=448 op='TABLE ACCESS BY INDEX ROWID HIST_HEAD$ (cr=2 pr=2 pw=0 time=1143 us)'
STAT #47882322014304 id=2 cnt=0 pid=1 pos=1 obj=450 op='INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=2 pr=2 pw=0 time=1140 us)'
CLOSE #47882322014304:c=0,e=1,dep=1,type=3,tim=1413185630141091
=====================
PARSING IN CURSOR #47882322876544 len=44 dep=0 uid=0 oct=3 lid=0 tim=1413185630141432 hv=6765473 ad='968448e8' sqlid='14x1gdc06ffx1'
select count(*) from dpr2 order by object_id
END OF STMT
PARSE #47882322876544:c=28995,e=62132,p=191,cr=88,cu=0,mis=1,r=0,dep=0,og=1,plh=284904835,tim=1413185630141431
EXEC #47882322876544:c=0,e=21,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=284904835,tim=1413185630141495
WAIT #47882322876544: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=89319 tim=1413185630141519
WAIT #47882322876544: nam='db file scattered read' ela= 683 file#=1 block#=244897 blocks=7 obj#=89319 tim=1413185630142371
WAIT #47882322876544: nam='db file scattered read' ela= 658 file#=1 block#=244904 blocks=8 obj#=89319 tim=1413185630143370
WAIT #47882322876544: nam='db file scattered read' ela= 793 file#=1 block#=244912 blocks=8 obj#=89319 tim=1413185630144556
WAIT #47882322876544: nam='db file scattered read' ela= 705 file#=1 block#=244920 blocks=8 obj#=89319 tim=1413185630145761
WAIT #47882322876544: nam='db file scattered read' ela= 544 file#=1 block#=244928 blocks=8 obj#=89319 tim=1413185630146645
WAIT #47882322876544: nam='db file scattered read' ela= 426 file#=1 block#=244936 blocks=8 obj#=89319 tim=1413185630147484
WAIT #47882322876544: nam='db file scattered read' ela= 442 file#=1 block#=244944 blocks=8 obj#=89319 tim=1413185630148220
WAIT #47882322876544: nam='db file scattered read' ela= 427 file#=1 block#=244952 blocks=8 obj#=89319 tim=1413185630148948
WAIT #47882322876544: nam='db file scattered read' ela= 371 file#=1 block#=244960 blocks=8 obj#=89319 tim=1413185630149608
WAIT #47882322876544: nam='db file scattered read' ela= 842 file#=1 block#=244968 blocks=8 obj#=89319 tim=1413185630150850
WAIT #47882322876544: nam='db file scattered read' ela= 464 file#=1 block#=244976 blocks=8 obj#=89319 tim=1413185630151638
WAIT #47882322876544: nam='db file scattered read' ela= 397 file#=1 block#=244984 blocks=8 obj#=89319 tim=1413185630152267
WAIT #47882322876544: nam='db file scattered read' ela= 467 file#=1 block#=244736 blocks=8 obj#=89319 tim=1413185630153004
WAIT #47882322876544: nam='db file scattered read' ela= 687 file#=1 block#=244744 blocks=8 obj#=89319 tim=1413185630154115
WAIT #47882322876544: nam='db file scattered read' ela= 695 file#=1 block#=244752 blocks=8 obj#=89319 tim=1413185630155251    


情况4:表大于大表阀值,但是小于5倍buffer cache且小于2倍(sga-redo buffer) 100949可以关闭dpr-:
SQL> select bytes/1024/1024 from dba_segments where segment_name='DPR3';

BYTES/1024/1024
---------------
            648

SQL> show sga

Total System Global Area  375828480 bytes
Fixed Size                  2253344 bytes
Variable Size             218107360 bytes
Database Buffers          150994944 bytes
Redo Buffers                4472832 bytes
SQL> select 150994944/1024/1024*5 "5倍buffer_cache" from dual;

5倍buffer_cache
---------------------
                  720

SQL> select (375828480-4472832)/1024/1024*2 "2*(SGA-redo buffer)" from dual;

2*(SGA-redo buffer)
-------------------
         708.304688
         
SQL> alter system flush buffer_cache;

System altered.

SQL> alter session set events '10949 trace name context forever, level 1';

Session altered.

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

Session altered.

SQL> select count(*) from dpr3;

  COUNT(*)
----------
   5710423

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

Session altered.

 SELECT    a.VALUE || b.symbol || c.instance_name || '_ora_' || d.spid || '.trc' trace_file
   FROM (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') a,
        (SELECT SUBSTR (VALUE, -6, 1) symbol FROM v$parameter
          WHERE NAME = 'user_dump_dest') b,
        (SELECT instance_name FROM v$instance) c,
        (SELECT spid FROM v$session s, v$process p, v$mystat m
          WHERE s.paddr = p.addr AND s.SID = m.SID AND m.statistic# = 0) d
 /

TRACE_FILE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/sdcnc/sdcnc1/trace/sdcnc1_ora_18276.trc    

select count(*) from dpr3
END OF STMT
PARSE #47950832525864:c=31996,e=50653,p=199,cr=83,cu=0,mis=1,r=0,dep=0,og=1,plh=2136408865,tim=1413173404392500
EXEC #47950832525864:c=0,e=68,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2136408865,tim=1413173404392606
WAIT #47950832525864: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=89336 tim=1413173404392636
WAIT #47950832525864: nam='db file scattered read' ela= 601 file#=1 block#=97305 blocks=7 obj#=89336 tim=1413173404393369
WAIT #47950832525864: nam='db file scattered read' ela= 690 file#=1 block#=97312 blocks=8 obj#=89336 tim=1413173404394268
WAIT #47950832525864: nam='db file scattered read' ela= 763 file#=1 block#=97320 blocks=8 obj#=89336 tim=1413173404395236
WAIT #47950832525864: nam='db file scattered read' ela= 642 file#=1 block#=97328 blocks=8 obj#=89336 tim=1413173404396034
WAIT #47950832525864: nam='db file scattered read' ela= 567 file#=1 block#=97336 blocks=8 obj#=89336 tim=1413173404396794
WAIT #47950832525864: nam='db file scattered read' ela= 232 file#=1 block#=97344 blocks=8 obj#=89336 tim=1413173404397166
WAIT #47950832525864: nam='db file scattered read' ela= 253 file#=1 block#=97352 blocks=8 obj#=89336 tim=1413173404397599
WAIT #47950832525864: nam='db file scattered read' ela= 257 file#=1 block#=97360 blocks=8 obj#=89336 tim=1413173404398043
WAIT #47950832525864: nam='db file scattered read' ela= 244 file#=1 block#=97368 blocks=8 obj#=89336 tim=1413173404398452
WAIT #47950832525864: nam='db file scattered read' ela= 267 file#=1 block#=97376 blocks=8 obj#=89336 tim=1413173404398887