<span style="font-size:16px;"><strong>oracle 11g在統(tǒng)計信息收集方面增加了擴展統(tǒng)計信息的特性,它可以收集一個表中相關列上的統(tǒng)計信息,也可以收集函數(shù)表達式上的</strong></span><br />
<span style="font-size:16px;"><strong>統(tǒng)計信息.使選擇率,成本的估計更加準確,也更容易走正確的執(zhí)行計劃.在相關列上收集統(tǒng)計信息,好處還是很明顯的.例如兩列在邏輯</strong></span><br />
<span style="font-size:16px;"><strong>上有一定的關系,但如果只是對這兩個列單獨做統(tǒng)計信息的收集,根據(jù)多條件的選擇率計算{(A AND B的選擇率為:OPSEL[a]*OPSEL[b]);</strong></span><br />
<span style="font-size:16px;"><strong>(A OR B 的選擇率為:OPSEL[a]+OPSEL[b]-OPSEL[a]OPSEL[b]);(NOT A的選擇率為:1-OPSEL[a])},估算出來的選擇率就可能偏差很大.</strong></span><br />
<br />
<span style="font-size:16px;"><strong>以下測試:</strong></span><br />
<strong>DB Version:11.2.0.4</strong><br />
<span style="font-size:16px;"><strong>----產(chǎn)生測試數(shù)據(jù)</strong></span><br />
<span style="font-size:16px;">drop table scott.test01 purge;</span><br />
<span style="font-size:16px;">create table scott.test01</span><br />
<span style="font-size:16px;">as select * from dba_objects;</span><br />
<br />
<span style="font-size:16px;"><strong>--把object_name 更新為和object_type一樣,用于測試.</strong></span><br />
<span style="font-size:16px;">update scott.test01</span><br />
<span style="font-size:16px;">set object_name=object_type;</span><br />
<span style="font-size:16px;">commit;</span><br />
<br />
<span style="font-size:16px;"><strong>1.收集單列統(tǒng)計信息,查看執(zhí)行計劃</strong></span><br />
<span style="font-size:16px;"><strong>--收集單列統(tǒng)計信息</strong></span><br />
<span style="font-size:16px;">begin</span><br />
<span style="font-size:16px;">dbms_stats.gather_table_stats('scott','test01');</span><br />
<span style="font-size:16px;">end;</span><br />
<span style="font-size:16px;"><strong>--查看表的行數(shù)</strong></span><br />
<span style="font-size:16px;">select table_name,num_rows from dba_tables</span><br />
<span style="font-size:16px;">where owner = 'SCOTT' and table_name = 'TEST01';</span><br />
<span style="font-size:16px;">/*</span><br />
<span style="font-size:16px;">TABLE_NAME NUM_ROWS</span><br />
<span style="font-size:16px;">TEST01 87212</span><br />
<span style="font-size:16px;">*/</span><br />
<span style="font-size:16px;"><strong>--產(chǎn)生語句的執(zhí)行計劃</strong></span><br />
<span style="font-size:16px;">explain plan for select * from scott.test01 where object_name='INDEX' and object_type='INDEX';</span><br />
<br />
<span style="font-size:16px;">SELECT lpad(' ', 2 * (LEVEL - 1)) || operation operation,</span><br />
<span style="font-size:16px;"> options,</span><br />
<span style="font-size:16px;"> object_name,</span><br />
<span style="font-size:16px;"> cardinality,</span><br />
<span style="font-size:16px;"> bytes,</span><br />
<span style="font-size:16px;"> io_cost,</span><br />
<span style="font-size:16px;"> cpu_cost,</span><br />
<span style="font-size:16px;"> cost,</span><br />
<span style="font-size:16px;"> time</span><br />
<span style="font-size:16px;"> FROM plan_table</span><br />
<span style="font-size:16px;"> START WITH id = 0</span><br />
<span style="font-size:16px;">CONNECT BY PRIOR id = parent_id;</span><br />
<span style="font-size:16px;">/*</span><br />
<span style="font-size:16px;">OPERATION OPTIONS OBJECT_NAME CARDINALITY BYTES IO_COST CPU_COST COST TIME</span><br />
<span style="font-size:16px;">SELECT STATEMENT 41 3362 347 35338490 348 5</span><br />
<span style="font-size:16px;"> TABLE ACCESS FULL TEST01 41 3362 347 35338490 348 5</span><br />
<span style="font-size:16px;">*/</span><br />
<span style="font-size:16px;"><strong>這里可以看到,估算的返回行數(shù)是41,顯然和實際相差很遠</strong></span><br />
<span style="font-size:16px;">rollback;</span><br />
<br />
<span style="font-size:16px;"><strong>--行數(shù)估算</strong></span><br />
<span style="font-size:16px;"> select rpad(column_name, 30, ' ') column_name,</span><br />
<span style="font-size:16px;"> rpad(num_distinct, 8, ' ') num_distinct,</span><br />
<span style="font-size:16px;"> rpad(utl_raw.cast_to_varchar2(low_value), 15, ' ') low_value,</span><br />
<span style="font-size:16px;"> rpad(utl_raw.cast_to_varchar2(high_value), 10, ' ') high_value,</span><br />
<span style="font-size:16px;"> rpad(num_nulls, 8, ' ') num_nulls,</span><br />
<span style="font-size:16px;"> rpad(avg_col_len, 6, ' ') avg_col_len,</span><br />
<span style="font-size:16px;"> rpad(density, 20, ' ') density,</span><br />
<span style="font-size:16px;"> histogram</span><br />
<span style="font-size:16px;"> from dba_tab_col_statistics</span><br />
<span style="font-size:16px;"> where owner = 'SCOTT'</span><br />
<span style="font-size:16px;"> and table_name = 'TEST01'</span><br />
<span style="font-size:16px;"> and column_name in ('OBJECT_NAME','OBJECT_TYPE');</span><br />
<span style="font-size:16px;">/* </span><br />
<span style="font-size:16px;">COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE NULLABLE NUM_NULLS AVG_COL_LEN DENSITY HISTOGRAM</span><br />
<span style="font-size:16px;">OBJECT_NAME 46 CLUSTER XML SCHEMA Y 0 9 .0217391304347826 NONE</span><br />
<span style="font-size:16px;">OBJECT_TYPE 46 CLUSTER XML SCHEMA Y 0 9 .0217391304347826 NONE</span><br />
<span style="font-size:16px;">*/</span><br />
<span style="font-size:16px;"><strong>估算的返回行數(shù)是41,是由兩個列的density相乘再乘以表的行數(shù)得到,.0217391304347826*.0217391304347826*87212=41.2155009451796=41</strong></span><br />
<span style="font-size:16px;"><strong> </strong> </span><br />
<span style="font-size:16px;"><strong>2.收集多列擴展統(tǒng)計信息,查看執(zhí)行計劃</strong></span><br />
<span style="font-size:16px;"><strong>--收集多列擴展統(tǒng)計信息</strong></span><br />
<span style="font-size:16px;"> begin</span><br />
<span style="font-size:16px;"> dbms_stats.gather_table_stats('scott','test01',method_opt =>'for columns (object_name,object_type)');</span><br />
<span style="font-size:16px;"> end;</span><br />
<br />
<span style="font-size:16px;"><strong>--產(chǎn)生語句的執(zhí)行計劃 </strong> </span><br />
<span style="font-size:16px;"> explain plan for select * from scott.test01 where object_name='INDEX' and object_type='INDEX';</span><br />
<br />
<span style="font-size:16px;">SELECT lpad(' ', 2 * (LEVEL - 1)) || operation operation,</span><br />
<span style="font-size:16px;"> options,</span><br />
<span style="font-size:16px;"> object_name,</span><br />
<span style="font-size:16px;"> cardinality,</span><br />
<span style="font-size:16px;"> bytes,</span><br />
<span style="font-size:16px;"> io_cost,</span><br />
<span style="font-size:16px;"> cpu_cost,</span><br />
<span style="font-size:16px;"> cost,</span><br />
<span style="font-size:16px;"> time</span><br />
<span style="font-size:16px;"> FROM plan_table</span><br />
<span style="font-size:16px;"> START WITH id = 0</span><br />
<span style="font-size:16px;">CONNECT BY PRIOR id = parent_id;</span><br />
<span style="font-size:16px;">/*</span><br />
<span style="font-size:16px;">OPERATION OPTIONS OBJECT_NAME CARDINALITY BYTES IO_COST CPU_COST COST TIME</span><br />
<span style="font-size:16px;">SELECT STATEMENT 5303 498482 347 36285951 348 5</span><br />
<span style="font-size:16px;"> TABLE ACCESS FULL TEST01 5303 498482 347 36285951 348 5</span><br />
<span style="font-size:16px;">*/</span><br />
<span style="font-size:16px;"><strong>這里可以看到,估算的返回行數(shù)是5303,已經(jīng)基本上和實際返回行數(shù)相近.</strong></span><br />
<br />
<span style="font-size:16px;"><strong>PS:</strong></span><br />
<span style="font-size:16px;"><strong>1.擴展統(tǒng)計信息的收集,可以用select dbms_stats.create_extended_stats('scott','test01','(object_name,object_type)')from dual</strong></span><br />
<span style="font-size:16px;"><strong>創(chuàng)建擴展統(tǒng)計列,然后dbms_stats.gather_table_stats('scott','test01')收集統(tǒng)計信息,也可以直接在</strong></span><br />
<span style="font-size:16px;"><strong>dbms_stats.gather_table_stats中的method_opt屬性同時建立擴展統(tǒng)計又收集統(tǒng)計數(shù)據(jù).</strong></span><br />
<span style="font-size:16px;"><strong>2.oracle 11g不僅可以收集多列擴展統(tǒng)計信息,還可以收集函數(shù)和表達式的擴展統(tǒng)計信息.</strong></span><br />
<br />
網(wǎng)站欄目:oracle11g擴展統(tǒng)計信息extended_stats
文章分享:http://muchs.cn/article10/ijoddo.html
成都網(wǎng)站建設公司_創(chuàng)新互聯(lián),為您提供外貿(mào)網(wǎng)站建設、服務器托管、網(wǎng)站導航、定制網(wǎng)站、關鍵詞優(yōu)化、微信公眾號
廣告
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請盡快告知,我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場,如需處理請聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時需注明來源:
創(chuàng)新互聯(lián)