SAMPLE語句在統計資訊收集中應用

前幾天簡單研究了一下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的方式進行。