驗證Oracle收集統計資訊參數granularity資料分析的力度

標籤：drop   ora   方式   and   資料   test   var   sub   glob   

 

   最近在學習Oracle的統計資訊這一塊，收集統計資訊的方法如下：   

DBMS_STATS.GATHER_TABLE_STATS (ownname VARCHAR2, ---所有者名字tabname VARCHAR2, ---表名partname VARCHAR2 DEFAULT NULL, ---要分析的分區名estimate_percent NUMBER DEFAULT NULL, ---採樣的比例block_sample BOOLEAN DEFAULT FALSE, ---是否塊分析method_opt VARCHAR2 DEFAULT ‘FOR ALL COLUMNS SIZE 1’,---分析的方式degree NUMBER DEFAULT NULL, ---分析的並行度granularity VARCHAR2 DEFAULT ‘DEFAULT’, ---分析的粒度cascade BOOLEAN DEFAULT FALSE, ---是否分析索引stattab VARCHAR2 DEFAULT NULL, ---使用的效能表名statid VARCHAR2 DEFAULT NULL, ---效能表標識statown VARCHAR2 DEFAULT NULL, ---效能表所有者no_invalidate BOOLEAN DEFAULT FALSE, ---是否驗證遊標依存關係force BOOLEAN DEFAULT FALSE); ---強制分析，即使鎖表

  本文主要對參數granularity進行了一下驗證，

  granularity：資料分析的力度

  --global  ---全域

  --partition ---只在分區層級做分析

  --subpartition  --只在子分區層級做分析

驗證步驟如下：

一、建立一個分區表並插入兩條資料，同時在欄位ID上建立索引drop table test purge;create table test(id number) partition by range(id)(partition p1 values less than (5),partition p2 values less than (10)) ;insert into test values(1);insert into test values(6);commit; create index ind_id on test(id); 二、收集表的統計資訊exec dbms_stats.gather_table_stats(user,‘TEST‘,cascade=>true); 三、查詢表的統計資訊select num_rows,blocks,last_analyzed from user_tables where table_name = ‘TEST‘;結果如下：num_rows:表資料行數blocks:資料區塊數last_analyzed：最近分析時間 四、查詢表分區資訊select partition_name,num_rows,blocks,last_analyzed from dba_tab_partitions where table_name =‘TEST‘;PARTITION_NAME:分區名稱NUM_ROWS:資料行數BLOCKS:資料區塊數last_analyzed：最近分析時間 五、查詢索引統計資訊select num_rows,blevel,last_analyzed from user_indexes where index_name = ‘IND_ID‘; num_rows:索引資料行數blevel:索引高度last_analyzed:分析時間 六、新增一個分區alter table test add partition pmax values less than(maxvalue); 七、往新的分區中插入10000條資料begin for i in 1..10000 loop ---插入10000條資料insert into test values(100);end loop;commit;end; 八、建立一個傾斜度非常大的分區update test set id=10000 where id=100 and rownum=1; ---創造一個非常傾斜的Pmax分區Commit; 九、查詢分區資料select id,count(*) from test partition(pmax) group by id; 十、不做分析，重新查詢表的統計資訊select num_rows,blocks,last_analyzed from user_tables where table_name = ‘TEST‘;探索資料行數量和資料區塊數量沒有發現變化  十一、查詢id=100時執行計畫set autotrace traceonlyset linesize 1000select * from test where id=100;發現走了索引，正常情況下，因為id=100的資料在一個傾斜度非常高的分區pmax中，id為100的資料有9999條，走索引的代價會比走全表的代價還要高（因為走索引需要回表），如果統計資訊正確，最佳化器應該會選擇走全表，但是這裡沒走全表而是走了索引，這裡懷疑是統計資訊不正確導致，後面驗證 十二、收集分區統計資訊exec dbms_stats.gather_table_stats(user,‘TEST‘,partname => ‘PMAX‘,granularity => ‘PARTITION‘); 十三、重新查詢表的統計資訊和分區統計資訊select partition_name,num_rows,blocks,last_analyzed from dba_tab_partitions where table_name =‘TEST‘;發現和步驟四比較，分區資訊有了變化，說明對分區進行統計資訊收集後，分區資訊進行了更新 select num_rows,blocks,last_analyzed from user_tables where table_name = ‘TEST‘;發現和步驟三想比，表的統計資訊並沒有發生變化，說明統計了分區資訊後，表的統計資訊麼有更新

 

十四、重新查詢id=100的資料仍然走索引，說明在評估查詢的時候，表的統計資訊依然陳舊 十五、查詢索引的統計資訊select num_rows,blevel,last_analyzed from user_indexes where index_name = ‘IND_ID‘;發現索引統計資訊較步驟五沒有變化，說明收集了分區的統計資訊後，表的索引資訊沒有更新

 

 

十六、重新再次收集表的統計資訊exec dbms_stats.gather_table_stats(user,‘TEST‘,cascade =>true); 十七、查詢表的統計資訊以及索引的統計資訊select num_rows,blocks,last_analyzed from user_tables where table_name = ‘TEST‘;表的統計資訊已經更新 select num_rows,blevel,last_analyzed from user_indexes where index_name = ‘IND_ID‘;索引的統計資訊也已經更新 十八、重新查詢id=100的執行計畫這次發現走了全表，說明收集了全域的統計資訊後，表的統計資訊準確了，評估也就準確了。

驗證Oracle收集統計資訊參數granularity資料分析的力度