oracle 11g 擴展統(tǒng)計信息extended_stats

發(fā)布時間：2020-08-09 06:15:20 來源：ITPUB博客閱讀：339 作者：kunlunzhiying 欄目：關(guān)系型數(shù)據(jù)庫

oracle 11g在統(tǒng)計信息收集方面增加了擴展統(tǒng)計信息的特性,它可以收集一個表中相關(guān)列上的統(tǒng)計信息,也可以收集函數(shù)表達式上的 統(tǒng)計信息.使選擇率,成本的估計更加準確,也更容易走正確的執(zhí)行計劃.在相關(guān)列上收集統(tǒng)計信息,好處還是很明顯的.例如兩列在邏輯 上有一定的關(guān)系,但如果只是對這兩個列單獨做統(tǒng)計信息的收集,根據(jù)多條件的選擇率計算{(A AND B的選擇率為:OPSEL[a]*OPSEL[b]); (A OR B 的選擇率為:OPSEL[a]+OPSEL[b]-OPSEL[a]OPSEL[b]);(NOT A的選擇率為:1-OPSEL[a])},估算出來的選擇率就可能偏差很大. 以下測試: DB Version:11.2.0.4 ----產(chǎn)生測試數(shù)據(jù) drop table scott.test01 purge; create table scott.test01 as select * from dba_objects; --把object_name 更新為和object_type一樣,用于測試. update scott.test01 set object_name=object_type; commit; 1.收集單列統(tǒng)計信息,查看執(zhí)行計劃 --收集單列統(tǒng)計信息 begin dbms_stats.gather_table_stats('scott','test01'); end; --查看表的行數(shù) select  table_name,num_rows from dba_tables where owner = 'SCOTT' and table_name = 'TEST01'; /* TABLE_NAME  NUM_ROWS TEST01  87212 */ --產(chǎn)生語句的執(zhí)行計劃 explain plan for select * from scott.test01 where object_name='INDEX' and object_type='INDEX'; SELECT lpad(' ', 2 * (LEVEL - 1)) || operation operation,        options,        object_name,        cardinality,        bytes,        io_cost,        cpu_cost,        cost,        time   FROM plan_table  START WITH id = 0 CONNECT BY PRIOR id = parent_id; /* OPERATION  OPTIONS  OBJECT_NAME  CARDINALITY  BYTES  IO_COST  CPU_COST  COST  TIME SELECT STATEMENT      41  3362  347  35338490  348  5   TABLE ACCESS  FULL  TEST01  41  3362  347  35338490  348  5 */ 這里可以看到,估算的返回行數(shù)是41,顯然和實際相差很遠 rollback; --行數(shù)估算   select rpad(column_name, 30, ' ') column_name,        rpad(num_distinct, 8, ' ') num_distinct,        rpad(utl_raw.cast_to_varchar2(low_value), 15, ' ') low_value,        rpad(utl_raw.cast_to_varchar2(high_value), 10, ' ') high_value,        rpad(num_nulls, 8, ' ') num_nulls,        rpad(avg_col_len, 6, ' ') avg_col_len,        rpad(density, 20, ' ') density,        histogram   from dba_tab_col_statistics  where owner = 'SCOTT'    and table_name = 'TEST01'    and column_name  in ('OBJECT_NAME','OBJECT_TYPE'); /*    COLUMN_NAME  NUM_DISTINCT  LOW_VALUE  HIGH_VALUE  NULLABLE  NUM_NULLS  AVG_COL_LEN  DENSITY  HISTOGRAM OBJECT_NAME   46        CLUSTER          XML SCHEMA  Y         0         9       .0217391304347826     NONE OBJECT_TYPE   46        CLUSTER          XML SCHEMA  Y         0         9       .0217391304347826     NONE */ 估算的返回行數(shù)是41,是由兩個列的density相乘再乘以表的行數(shù)得到,.0217391304347826*.0217391304347826*87212=41.2155009451796=41     2.收集多列擴展統(tǒng)計信息,查看執(zhí)行計劃 --收集多列擴展統(tǒng)計信息   begin   dbms_stats.gather_table_stats('scott','test01',method_opt =>'for columns (object_name,object_type)');   end; --產(chǎn)生語句的執(zhí)行計劃     explain plan for select * from scott.test01 where object_name='INDEX' and object_type='INDEX'; SELECT lpad(' ', 2 * (LEVEL - 1)) || operation operation,        options,        object_name,        cardinality,        bytes,        io_cost,        cpu_cost,        cost,        time   FROM plan_table  START WITH id = 0 CONNECT BY PRIOR id = parent_id; /* OPERATION  OPTIONS  OBJECT_NAME  CARDINALITY  BYTES  IO_COST  CPU_COST  COST  TIME SELECT STATEMENT            5303    498482    347    36285951    348    5   TABLE ACCESS    FULL    TEST01    5303    498482    347    36285951    348    5 */ 這里可以看到,估算的返回行數(shù)是5303,已經(jīng)基本上和實際返回行數(shù)相近. PS: 1.擴展統(tǒng)計信息的收集,可以用select dbms_stats.create_extended_stats('scott','test01','(object_name,object_type)')from dual 創(chuàng)建擴展統(tǒng)計列,然后dbms_stats.gather_table_stats('scott','test01')收集統(tǒng)計信息,也可以直接在 dbms_stats.gather_table_stats中的method_opt屬性同時建立擴展統(tǒng)計又收集統(tǒng)計數(shù)據(jù). 2.oracle 11g不僅可以收集多列擴展統(tǒng)計信息,還可以收集函數(shù)和表達式的擴展統(tǒng)計信息.

向AI問一下細節(jié)

oracle 11g 擴展統(tǒng)計信息extended_stats

猜你喜歡

最新資訊

相關(guān)推薦

相關(guān)標簽