Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE 查看隐含参数 _small_table_threshold
SELECT A.KSPPINM PARAMETER, A.KSPPDESC DESCRIPTION, B.KSPPSTVL SESSION_VALUE,
C.KSPPSTVL INSTANCE_VALUE
FROM X$KSPPI A, X$KSPPCV B, X$KSPPSV C
WHERE A.INDX = B.INDX AND A.INDX = C.INDX AND
--SUBSTR(A.KSPPINM, 1, 1) = '_' and
a.KSPPINM ='_small_table_threshold'
ORDER BY A.KSPPINM ;
小表的定义值为734个block大小。
PARAMETER DESCRIPTION SESSION_VALUE INST
------------------------------ ------------------------------------------------------------ --------------- ----
_small_table_threshold lower threshold level of table size for direct reads 734 734
当表的规模大于5倍小表阀值时候
SQL> create table TT(c1 number, c2 char(2000), c3 char(2000), c4 char(2000));
SQL> insert into TT
2 select level, 'x', 'x', 'x'
3 from dual connect by level <= 10 + 5*&sth;
Enter value for sth: 734
old 3: from dual connect by level <= 10 + 5*&sth
new 3: from dual connect by level <= 10 + 5*734
3680 rows created.
SQL> select bytes/1024/1024 MB ,SEGMENT_TYPE from dba_segments where segment_name='TT' and wner='SYS';
MB SEGMENT_TYPE
---------- ------------------------------------------------------
29 TABLE
SQL> alter system flush buffer_cache;
alter session set events '10046 trace name context forever, level 8';
select count(*) from TT;
可以看到跟踪事件中,以直接路径读进行全表扫描
WAIT #7: nam='db file sequential read' ela= 32539 file#=1 block#=33064 blocks=1 obj#=14517 tim=18808750986
WAIT #7: nam='direct path read' ela= 15931 file number=1 first dba=33065 block cnt=127 obj#=14517 tim=18808769895
WAIT #7: nam='direct path read' ela= 24746 file number=1 first dba=247040 block cnt=128 obj#=14517 tim=18808795968
WAIT #7: nam='direct path read' ela= 17911 file number=1 first dba=247168 block cnt=128 obj#=14517 tim=18808815059
…
而执行
alter system flush buffer_cache;
alter session set events '10046 trace name context forever, level 8';
alter session set events '10949 trace name context forever, level 1';
select count(*) from TT;
见到的就是另一番景象,数据库采用离散读来进行全表扫描。
WAIT #7: nam='SQL*Net message to client' ela= 7 driver id=1111838976 #bytes=1 p3=0 obj#=14517 tim=18746048764
WAIT #7: nam='db file scattered read' ela= 832 file#=1 block#=33065 blocks=7 obj#=14517 tim=18746049844
WAIT #7: nam='db file scattered read' ela= 712 file#=1 block#=33072 blocks=8 obj#=14517 tim=18746051116
WAIT #7: nam='db file scattered read' ela= 577 file#=1 block#=33080 blocks=6 obj#=14517 tim=18746052075
WAIT #7: nam='db file sequential read' ela= 289 file#=1 block#=33087 blocks=1 obj#=14517 tim=18746052647
…
现在开始验证表大小与_very_large_object_threshold的比较,对10949事件的影响。
查看隐含参数_very_large_object_threshold,这里的单位为MB
SELECT A.KSPPINM PARAMETER, A.KSPPDESC DESCRIPTION, B.KSPPSTVL SESSION_VALUE,
C.KSPPSTVL INSTANCE_VALUE
FROM X$KSPPI A, X$KSPPCV B, X$KSPPSV C
WHERE A.INDX = B.INDX AND A.INDX = C.INDX AND
--SUBSTR(A.KSPPINM, 1, 1) = '_' and
a.KSPPINM =' _very_large_object_threshold'
ORDER BY A.KSPPINM ;
PARAMETER
--------------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
SESSION_VALUE
--------------------------------------------------------------------------------
INSTANCE_VALUE
--------------------------------------------------------------------------------
_very_large_object_threshold
upper threshold level of object size for direct reads
500
500
理论上表的大小小于500M还是会自动使用直接路径读,10949事件也可以禁止直接路径读。因此直接增加表的size到超过500MB:
SQL> insert into TT
2 select level, 'x', 'x', 'x'
3 from dual connect by level <= 10 + 5*&sth;
Enter value for sth: 10000
old 3: from dual connect by level <= 10 + 5*&sth
new 3: from dual connect by level <= 10 + 5*10000
50010 rows created.
SQL> select bytes/1024/1024 MB ,SEGMENT_TYPE from dba_segments where segment_name='TT' and wner='SYS'
MB SEGMENT_TYPE
---------- ------------------------------------------------------
856 TABLE
此时验证10949事件能否阻止直接路径读:
SQL> alter system flush buffer_cache;
SQL> alter session set events '10949 trace name context forever, level 1';
SQL> select count(*) from TT; 10:21执行
COUNT(*)
----------
109115
*** 2011-07-08 10:21:02.115
WAIT #2: nam='SQL*Net message from client' ela= 40831353 driver id=1111838976 #bytes=1 p3=0 obj#=14509 tim=9243629861
CLOSE #2:c=0,e=15,dep=0,type=1,tim=9243630106
=====================
PARSING IN CURSOR #1 len=23 dep=0 uid=0 ct=3 lid=0 tim=9243630258 hv=3499053100 ad='27ef3938' sqlid='9330tqb88yn1c'
select count(*) from TT
END OF STMT
PARSE #1:c=0,e=50,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3133740314,tim=9243630254
EXEC #1:c=0,e=136,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3133740314,tim=9243630577
WAIT #1: nam='SQL*Net message to client' ela= 6 driver id=1111838976 #bytes=1 p3=0 obj#=14509 tim=9243630667
WAIT #1: nam='db file scattered read' ela= 32813 file#=1 block#=33225 blocks=7 obj#=14509 tim=9243663820
WAIT #1: nam='db file scattered read' ela= 854 file#=1 block#=33232 blocks=8 obj#=14509 tim=9243665083
WAIT #1: nam='db file scattered read' ela= 692 file#=1 block#=33240 blocks=8 obj#=14509 tim=9243666078
…
显然并没有令10949事件失效。
现在看表的大小超过5倍buffer cache的试验:
算出5倍buffer大小为1480M。
增加表size
SQL> select bytes/1024/1024 MB ,SEGMENT_TYPE from dba_segments where segment_name='TT' and wner='SYS';
MB SEGMENT_TYPE
---------- ------------------------------------------------------
1670 TABLE
SQL> alter system flush buffer_cache;
SQL> alter session set events '10949 trace name context off';
SQL> select count(*) from TT; 10:26
COUNT(*)
----------
209125
SQL> alter system flush buffer_cache;
SQL> alter session set events '10949 trace name context forever, level 1';
SQL> select count(*) from TT; 10:31
COUNT(*)
----------
209125
*** 2011-07-08 10:31:02.503
WAIT #2: nam='SQL*Net message from client' ela= 68444968 driver id=1111838976 #bytes=1 p3=0 obj#=14509 tim=9844017130
CLOSE #2:c=0,e=15,dep=0,type=3,tim=9844017436
=====================
PARSING IN CURSOR #1 len=23 dep=0 uid=0 ct=3 lid=0 tim=9844017611 hv=3499053100 ad='27ef3938' sqlid='9330tqb88yn1c'
select count(*) from TT
END OF STMT
PARSE #1:c=0,e=58,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3133740314,tim=9844017607
EXEC #1:c=0,e=130,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3133740314,tim=9844017928
WAIT #1: nam='SQL*Net message to client' ela= 8 driver id=1111838976 #bytes=1 p3=0 obj#=14509 tim=9844018021
WAIT #1: nam='db file sequential read' ela= 30665 file#=1 block#=33224 blocks=1 obj#=14509 tim=9844048839
WAIT #1: nam='direct path read' ela= 50296 file number=1 first dba=33225 block cnt=55 obj#=14509 tim=9844100911
WAIT #1: nam='direct path read' ela= 29366 file number=1 first dba=92672 block cnt=128 obj#=14509 tim=9844132883
。。。
无论10949事件有没有开启;天王老子都不能阻止全表扫描采用直接路径读的方式了。
看来 10949 事件还是有其局限性,但是话说回来,对一个如此大的表采用直接路径读,也是无奈的办法,而对这么个大表,最根本的还是建立索引,不走全表扫描还是主要的方法。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24582392/viewspace-705133/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24582392/viewspace-705133/