About direct path reads ,Alex-优快云博客

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

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

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:

// *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

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

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21818314/viewspace-693216/