- - - - - - - - - - - - - - -' ); dbms_output.put_line('Over the Change_Rate 10%:'); for v_topinsert in overchangerate loop dbms_output.put_line(v_topinsert.table_owner || '.' || v_topinsert.table_name || ' once has ' || v_topinsert.num_rows || ' rows, ' || 'till now inserted ' || v_topinsert.inserts || ' rows, updated ' || v_topinsert.updates || ' rows, deleted ' || v_topinsert.deletes || ' rows. consider gathering statistics'); end loop; dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -'); end; / 下面的是分区表
set serveroutput on declare -----select OVER THE Change RATE TABLES--------------- cursor overchangerate is select a.table_owner,a.table_name,a.partition_name,sum(a.inserts) inserts,sum(a.updates) updates,sum(a.deletes) deletes,sum(b.num_rows) num_rows from dba_tab_modifications a,dba_tab_partitions b where a.table_owner =b.table_owner and a.table_name=b.table_name and a.partition_name=b.partition_name and a.table_owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS', 'XDB', 'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP', 'ORDSYS','OUTLN', 'TSMSYS', 'MDSYS') group by a.table_owner,a.table_name,a.partition_name having (sum(a.inserts)/decode(sum(b.num_rows),0,1,sum(b.num_rows)))>=0.1 or (sum(a.updates)/decode(sum(b.num_rows),0,1,sum(b.num_rows)))>=0.1 or (sum(a.deletes)/decode(sum(b.num_rows),0,1,sum(b.num_rows)))>=0.1 order by a.table_name; begin dbms_output.enable(1000000); ----flush the monitorring information into the dba_tab_modifications DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; ----display the top_n_insert information------------------- dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -' ); dbms_output.put_line('Over the Change_Rate 10%:'); for v_topinsert in overchangerate loop dbms_output.put_line(v_topinsert.table_owner || '.' || v_topinsert.table_name || ' partition ' || v_topinsert.partition_name || ' once has ' || v_topinsert.num_rows || ' rows, ' || 'till now inserted ' || v_topinsert.inserts || ' rows, updated ' || v_topinsert.updates || ' rows, deleted ' || v_topinsert.deletes || ' rows. consider gathering statistics'); end loop; dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -' ); end; / 在此特别声明一点，在oracle11.2版本中有一个相关的BUG Bug 9272549 - User statistics are ignored when dynamic sampling occurs 9272549.8 该BUG会导致严重的性能问题。 oracle官方申明，只有在12.1版本才解决这个问题，临时解决方案是手动关闭动态采样。 顺便贴上10个level的动态采样介绍
Level 0: Do not use dynamic sampling.
Level 1: Sample all tables that have not been analyzed if the following criteria are met: (1) there is at least 1 unanalyzed table in the query; (2) this unanalyzed table is joined to another table or appears in a subquery or non-mergeable view; (3) this unanalyzed table has no indexes; (4) this unanalyzed table has more blocks than the number of blocks that would be used for dynamic sampling of this table. The number of blocks sampled is the default number of dynamic sampling blocks (32).
Level 2: Apply dynamic sampling to all unanalyzed tables. The number of blocks sampled is two times the default number of dynamic sampling blocks.
Level 3: Apply dynamic sampling to all tables that meet Level 2 criteria, plus all tables for which standard selectivity estima |