前几天简单研究了一下SAMPLE语法,当时就猜测SAMPLE语句的主要应用实在收集统计信息时,今天找机会验证了一下。
SAMPLE语句(一):http://yangtingkun.itpub.net/post/468/506602
SAMPLE语句(二):http://yangtingkun.itpub.net/post/468/506627
SAMPLE语句(三):http://yangtingkun.itpub.net/post/468/506665
SAMPLE语句(四):http://yangtingkun.itpub.net/post/468/506748
通过TRACE的方式根据一下以ESTIMATE方式收集统计是否利用SAMPLE语句:
SQL> create table t as
2 select *
3 from dba_objects;
Table created.
SQL> create index ind_t_object_id on t (object_id);
Index created.
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> begin
2 dbms_stats.gather_table_stats(
3 user,
4 'T',
5 estimate_percent => 5,
6 cascade => true);
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_stats.gather_table_stats(
3 user,
4 'T',
5 estimate_percent => 10,
6 block_sample => true,
7 cascade => true);
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> alter session set events '10046 trace name context off';
Session altered.
SQL> select spid
2 from v$process
3 where addr in
4 (select paddr
5 from v$session
6 where sid in
7 (select sid
8 from v$mystat
9 where rownum = 1));
SPID
------------
8551
下面检查对应的trace文件:
/opt/ora10g/admin/test08/udump/test08_ora_8551.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /opt/ora10g/product/10.2.0/db_1
System name: Linux
Node name: yans1
Release: 2.6.9-42.0.0.0.1.ELsmp
Version: #1 SMP Sun Oct 15 15:13:57 PDT 2006
Machine: x86_64
Instance name: test08
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 8551, image: oracle@yans1 (TNS V1-V3)
*** 2010-11-18 20:19:45.209
*** ACTION NAME:() 2010-11-18 20:19:45.185
*** MODULE NAME:(SQL*Plus) 2010-11-18 20:19:45.185
*** SERVICE NAME:(SYS$USERS) 2010-11-18 20:19:45.185
*** SESSION ID:(307.575) 2010-11-18 20:19:45.185
WAIT #7: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=563 tim=1259846469907404
*** 2010-11-18 20:21:13.338
WAIT #7: nam='SQL*Net message from client' ela= 86063472 driver id=1650815232 #bytes=1 p3=0 obj#=563 tim=1259846555994418
XCTEND rlbk=0, rd_only=1
=====================
PARSING IN CURSOR #4 len=93 dep=0 uid=74 ct=47 lid=74 tim=1259846627073589 hv=785043992 ad='fadef010'
begin
dbms_stats.gather_table_stats(
user,
'T',
estimate_percent => 5,
cascade => true);
end;
END OF STMT
PARSE #4:c=18997,e=166264,p=4,cr=69,cu=0,mis=1,r=0,dep=0,og=1,tim=1259846627073586
BINDS #4:
=====================
.
.
.
=====================
PARSING IN CURSOR #17 len=2427 dep=1 uid=74 ct=3 lid=74 tim=1259846627322518 hv=2198259475 ad='fb8660b0'
select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */ count(*),count("OWNER"),count(distinct "OWNER"),sum(sys_op_opnsize("OWNER")),substrb(dump(min("OWNER"),16,0,32),1,120),substrb(dump(max("OWNER"),16,0,32),1,120),count("OBJECT_NAME"),count(distinct "OBJECT_NAME"),sum(sys_op_opnsize("OBJECT_NAME")),substrb(dump(min(substrb("OBJECT_NAME",1,32)),16,0,32),1,120),substrb(dump(max(substrb("OBJECT_NAME",1,32)),16,0,32),1,120),count("SUBOBJECT_NAME"),count(distinct "SUBOBJECT_NAME"),sum(sys_op_opnsize("SUBOBJECT_NAME")),substrb(dump(min("SUBOBJECT_NAME"),16,0,32),1,120),substrb(dump(max("SUBOBJECT_NAME"),16,0,32),1,120),count("OBJECT_ID"),count(distinct "OBJECT_ID"),sum(sys_op_opnsize("OBJECT_ID")),substrb(dump(min("OBJECT_ID"),16,0,32),1,120),substrb(dump(max("OBJECT_ID"),16,0,32),1,120),count("DATA_OBJECT_ID"),count(distinct "DATA_OBJECT_ID"),sum(sys_op_opnsize("DATA_OBJECT_ID")),substrb(dump(min("DATA_OBJECT_ID"),16,0,32),1,120),substrb(dump(max("DATA_OBJECT_ID"),16,0,32),1,120),count("OBJECT_TYPE"),count(distinct "OBJECT_TYPE"),sum(sys_op_opnsize("OBJECT_TYPE")),substrb(dump(min("OBJECT_TYPE"),16,0,32),1,120),substrb(dump(max("OBJECT_TYPE"),16,0,32),1,120),count("CREATED"),count(distinct "CREATED"),substrb(dump(min("CREATED"),16,0,32),1,120),substrb(dump(max("CREATED"),16,0,32),1,120),count("LAST_DDL_TIME"),count(distinct "LAST_DDL_TIME"),substrb(dump(min("LAST_DDL_TIME"),16,0,32),1,120),substrb(dump(max("LAST_DDL_TIME"),16,0,32),1,120),count("TIMESTAMP"),count(distinct "TIMESTAMP"),sum(sys_op_opnsize("TIMESTAMP")),substrb(dump(min("TIMESTAMP"),16,0,32),1,120),substrb(dump(max("TIMESTAMP"),16,0,32),1,120),count("STATUS"),count(distinct "STATUS"),sum(sys_op_opnsize("STATUS")),substrb(dump(min("STATUS"),16,0,32),1,120),substrb(dump(max("STATUS"),16,0,32),1,120),count("TEMPORARY"),count(distinct "TEMPORARY"),sum(sys_op_opnsize("TEMPORARY")),substrb(dump(min("TEMPORARY"),16,0,32),1,120),substrb(dump(max("TEMPORARY"),16,0,32),1,120),count("GENERATED"),count(distinct "GENERATED"),sum(sys_op_opnsize("GENERATED")),substrb(dump(min("GENERATED"),16,0,32),1,120),substrb(dump(max("GENERATED"),16,0,32),1,120),count("SECONDARY"),count(distinct "SECONDARY"),sum(sys_op_opnsize("SECONDARY")),substrb(dump(min("SECONDARY"),16,0,32),1,120),substrb(dump(max("SECONDARY"),16,0,32),1,120) from "TEST"."T" sample ( 5.0000000000) t
END OF STMT
PARSE #17:c=7998,e=9649,p=0,cr=26,cu=0,mis=1,r=0,dep=1,og=1,tim=1259846627322515
BINDS #17:
EXEC #17:c=0,e=156,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1259846627322958
FETCH #17:c=51993,e=50039,p=0,cr=527,cu=0,mis=0,r=1,dep=1,og=1,tim=1259846627373019
FETCH #17:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,tim=1259846627373113
STAT #17 id=1 cnt=1 pid=0 pos=1 bj=0 p='SORT GROUP BY (cr=527 pr=0 pw=0 time=49973 us)'
STAT #17 id=2 cnt=3903 pid=1 pos=1 bj=155957 p='TABLE ACCESS SAMPLE T (cr=527 pr=0 pw=0 time=3971 us)'
=====================
.
.
.
=====================
PARSING IN CURSOR #27 len=6 dep=1 uid=74 ct=44 lid=74 tim=1259846627964793 hv=255718823 ad='0'
COMMIT
END OF STMT
PARSE #27:c=0,e=8,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,tim=1259846627964791
XCTEND rlbk=0, rd_only=1
EXEC #27:c=0,e=20,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,tim=1259846627964850
EXEC #4:c=429936,e=891224,p=90,cr=1693,cu=157,mis=0,r=1,dep=0,og=1,tim=1259846627964878
WAIT #4: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=155958 tim=1259846627965500
*** 2010-11-18 20:23:27.099
WAIT #4: nam='SQL*Net message from client' ela= 58655541 driver id=1650815232 #bytes=1 p3=0 obj#=155958 tim=1259846686621070
=====================
PARSING IN CURSOR #28 len=116 dep=0 uid=74 ct=47 lid=74 tim=1259846686632559 hv=45483779 ad='fb5a7ce8'
begin
dbms_stats.gather_table_stats(
user,
'T',
estimate_percent => 10,
block_sample => true,
cascade => true);
end;
END OF STMT
PARSE #28:c=1999,e=11344,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1259846686632555
BINDS #28:
BINDS #8:
EXEC #8:c=0,e=32,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1259846686632745
FETCH #8:c=0,e=11,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,tim=1259846686632771
BINDS #7:
kkscoacd
Bind#0
acdty=01 mxl=32(30) mxlc=00 mal=00 scl=00 pre=00
acflg=03 fl2=1206001 frm=01 csi=852 siz=32 ff=0
.
.
.
=====================
PARSING IN CURSOR #26 len=2433 dep=1 uid=74 ct=3 lid=74 tim=1259846686660613 hv=34594733 ad='fa2a2b08'
select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */ count(*),count("OWNER"),count(distinct "OWNER"),sum(sys_op_opnsize("OWNER")),substrb(dump(min("OWNER"),16,0,32),1,120),substrb(dump(max("OWNER"),16,0,32),1,120),count("OBJECT_NAME"),count(distinct "OBJECT_NAME"),sum(sys_op_opnsize("OBJECT_NAME")),substrb(dump(min(substrb("OBJECT_NAME",1,32)),16,0,32),1,120),substrb(dump(max(substrb("OBJECT_NAME",1,32)),16,0,32),1,120),count("SUBOBJECT_NAME"),count(distinct "SUBOBJECT_NAME"),sum(sys_op_opnsize("SUBOBJECT_NAME")),substrb(dump(min("SUBOBJECT_NAME"),16,0,32),1,120),substrb(dump(max("SUBOBJECT_NAME"),16,0,32),1,120),count("OBJECT_ID"),count(distinct "OBJECT_ID"),sum(sys_op_opnsize("OBJECT_ID")),substrb(dump(min("OBJECT_ID"),16,0,32),1,120),substrb(dump(max("OBJECT_ID"),16,0,32),1,120),count("DATA_OBJECT_ID"),count(distinct "DATA_OBJECT_ID"),sum(sys_op_opnsize("DATA_OBJECT_ID")),substrb(dump(min("DATA_OBJECT_ID"),16,0,32),1,120),substrb(dump(max("DATA_OBJECT_ID"),16,0,32),1,120),count("OBJECT_TYPE"),count(distinct "OBJECT_TYPE"),sum(sys_op_opnsize("OBJECT_TYPE")),substrb(dump(min("OBJECT_TYPE"),16,0,32),1,120),substrb(dump(max("OBJECT_TYPE"),16,0,32),1,120),count("CREATED"),count(distinct "CREATED"),substrb(dump(min("CREATED"),16,0,32),1,120),substrb(dump(max("CREATED"),16,0,32),1,120),count("LAST_DDL_TIME"),count(distinct "LAST_DDL_TIME"),substrb(dump(min("LAST_DDL_TIME"),16,0,32),1,120),substrb(dump(max("LAST_DDL_TIME"),16,0,32),1,120),count("TIMESTAMP"),count(distinct "TIMESTAMP"),sum(sys_op_opnsize("TIMESTAMP")),substrb(dump(min("TIMESTAMP"),16,0,32),1,120),substrb(dump(max("TIMESTAMP"),16,0,32),1,120),count("STATUS"),count(distinct "STATUS"),sum(sys_op_opnsize("STATUS")),substrb(dump(min("STATUS"),16,0,32),1,120),substrb(dump(max("STATUS"),16,0,32),1,120),count("TEMPORARY"),count(distinct "TEMPORARY"),sum(sys_op_opnsize("TEMPORARY")),substrb(dump(min("TEMPORARY"),16,0,32),1,120),substrb(dump(max("TEMPORARY"),16,0,32),1,120),count("GENERATED"),count(distinct "GENERATED"),sum(sys_op_opnsize("GENERATED")),substrb(dump(min("GENERATED"),16,0,32),1,120),substrb(dump(max("GENERATED"),16,0,32),1,120),count("SECONDARY"),count(distinct "SECONDARY"),sum(sys_op_opnsize("SECONDARY")),substrb(dump(min("SECONDARY"),16,0,32),1,120),substrb(dump(max("SECONDARY"),16,0,32),1,120) from "TEST"."T" sample block ( 10.0000000000) t
END OF STMT
PARSE #26:c=5000,e=15105,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=1259846686660608
BINDS #26:
EXEC #26:c=0,e=80,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1259846686660975
FETCH #26:c=75988,e=74605,p=0,cr=48,cu=0,mis=0,r=1,dep=1,og=1,tim=1259846686735603
FETCH #26:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,tim=1259846686735679
STAT #26 id=1 cnt=1 pid=0 pos=1 bj=0 p='SORT GROUP BY (cr=48 pr=0 pw=0 time=74547 us)'
STAT #26 id=2 cnt=6336 pid=1 pos=1 bj=155957 p='TABLE ACCESS SAMPLE T (cr=48 pr=0 pw=0 time=47 us)'
=====================
.
.
.
=====================
PARSING IN CURSOR #4 len=38 dep=1 uid=0 ct=7 lid=0 tim=1259846686962894 hv=624276838 ad='6457a8'
delete from ind_online$ where obj#= :1
END OF STMT
PARSE #4:c=0,e=23,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1259846686962892
BINDS #4:
kkscoacd
Bind#0
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=2a9734bf78 bln=22 avl=04 flg=05
value=155958
EXEC #4:c=0,e=86,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=1,tim=1259846686963026
STAT #4 id=1 cnt=0 pid=0 pos=1 bj=0 p='DELETE IND_ONLINE$ (cr=3 pr=0 pw=0 time=28 us)'
STAT #4 id=2 cnt=0 pid=1 pos=1 bj=731 p='TABLE ACCESS FULL IND_ONLINE$ (cr=3 pr=0 pw=0 time=28 us)'
XCTEND rlbk=0, rd_only=1
EXEC #27:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,tim=1259846686963462
EXEC #28:c=308952,e=330852,p=0,cr=984,cu=103,mis=0,r=1,dep=0,og=1,tim=1259846686963489
WAIT #28: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=155958 tim=1259846686963967
WAIT #28: nam='SQL*Net message from client' ela= 8634980 driver id=1650815232 #bytes=1 p3=0 obj#=155958 tim=1259846695598976
=====================
PARSING IN CURSOR #29 len=55 dep=0 uid=74 ct=42 lid=74 tim=1259846695606322 hv=2217940283 ad='0'
alter session set events '10046 trace name context off'
END OF STMT
PARSE #29:c=0,e=7230,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1259846695606319
EXEC #29:c=0,e=55,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1259846695606415
根据trace结果可以看到,gather_table_stats包输入的estimate_percent的值,就用在SAMPLE语句中指定值,而如果block_sample参数输入为TRUE,那么表扫描的时候也会采用SAMPLE BLOCK的方式进行。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-678472/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/4227/viewspace-678472/