ITPub博客

首页 > Linux操作系统 > Linux操作系统 > About direct path reads ,Alex

About direct path reads ,Alex

原创 Linux操作系统 作者:flying_warrior 时间:2011-04-23 00:30:31 0 删除 编辑

做不同的Unix OS 一次最多可以读多少个block的测试。 结果发现得到的常常是direct read path wait event.我无语了……于是就尝试着找出如何关闭direct path read 的办法。

其中倒是找到一些好玩的东西。

·  http://afatkulin.blogspot.com/2009/01/11g-adaptive-direct-path-reads-what-is.html

Oracle 11g has introduced 10949 event to control this.

 

1

UKJA@ukja116> @oerr 10949

2

10949

 

3

 "Disable autotune direct path read for full table scan"

4

// *Cause:

 

5

// *Action:  Disable autotune direct path read for serial full table scan.

 

01

-- case#1

02

alter system flush buffer_cache;

 

03

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

04

select count(*) from t1;

 

05

 

06

-- case#2

 

07

alter system flush buffer_cache;

08

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

 

09

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

10

select count(*) from t1;

 

 

01

SQL ID : 5bc0v4my7dvr5

02

select count(*)

 

03

from

04

 t1

 

05

 

06

-- Case #1

 

07

Elapsed times include waiting on following events:

08

  Event waited on                             Times   Max. Wait  Total Waited

 

09

  ----------------------------------------   Waited  ----------  ------------

10

  SQL*Net message to client                       2        0.00          0.00




 

11

  db file sequential read                         3        0.02          0.04

12

  db file scattered read                          1        0.02          0.02

 

13

  direct path read                              231        0.29          1.67

14

  SQL*Net message from client                     2        0.03          0.03

 

15

 

 

16

-- Case #2

 

17

Elapsed times include waiting on following events:

18

  Event waited on                             Times   Max. Wait  Total Waited

 

19

  ----------------------------------------   Waited  ----------  ------------

20

  SQL*Net message to client                       2        0.00          0.00




 

21

  db file scattered read                        213        0.26          1.77

22

  SQL*Net message from client                     2        0.00          0.00


 

Enable 10949 event 就关闭了 direct path  read.

 

 

 

算了 还是来说点10G的东西吧

_small_table_threshold 这个参数在10g中就有

[oracle@testdb2 ~]$ ora params small

 

NAME                                          VALUE                DESCRIPTION

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

_olap_parallel_update_small_group             400                  OLAP parallel update pagespace by group

_olap_parallel_update_small_threshold         1000                 OLAP parallel update threshold for number of small pagespaces

_parallel_broadcast_enabled                   TRUE                 enable broadcasting of small inputs to hash and sort merge joins

_small_table_threshold                        7243                 threshold level of table size for direct reads

 

[oracle@testdb2 ~]$ ora params _serial_direct_read

 

NAME                                          VALUE                DESCRIPTION

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

_serial_direct_read                           FALSE                enable direct read in serial

 

“_serial_direct_read” parameter which forces the full scan on the big table to be a direct read – The opposite of 10949 event.

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

请登录后发表评论 登录
全部评论

注册时间:2009-06-21

  • 博文量
    49
  • 访问量
    78982