r all columns size interger' 我们自己指定一个bucket值
例子2对某一个schma收集统计信息
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SCOTT',
estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,
ptions => 'gather auto',
degree => DBMS_STATS.AUTO_DEGREE,
method_opt => 'for all columns size repeat',
cascade => TRUE
);
END;
/ 上面的例子收集SCOTT模式下所有对象的统计信息。里面值得注意的一个参数就是options。前面已经讲到过，他与表监控有关。它有四个选项
Options =>’gather’ 收集所有对象的统计信息
Options =>’gather empty’ 只收集还没被统计的表
Options =>’gather stale’ 只收集修改量超过10%的表
Options =>’gather auto’ 相当于empty+stale ，所以我们一般设置为AUTO。
例子3 对一个分区表收集统计信息
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ROBINSON',
tabname => 'P_TEST',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'for all columns size repeat',
degree => DBMS_STATS.AUTO_DEGREE, granularity => 'ALL',
cascade=>TRUE
);
END;
/ 上面的例子收集ROBINSON.P_TEST表的统计信息。里面值得注意的一个参数就是granularity，他有7个选项。
granularity => 'ALL' 收集分区，子分区，全局的统计信息
granularity => 'AUTO' 这个是默认的设置，ORACLE会根据分区类型来决定用ALL,GLOBAL AND PARTITION ,还是其他的
granularity => 'DEFAULT' 这个是过期了的
granularity => 'GLOBAL' 收集全局统计信息
granularity => 'GLOBAL AND PARTITION' 收集全局，分区统计信息，但是不收集子分区统计信息
granularity => 'PARTITION' 收集分区统计信息
granularity => 'SUBPARTITION' 收集子分区统计信息 当然我们可以指定partname，自己控制对哪个分区收集统计信息
9. 列出表需要收集统计信息的脚本
普通表
set serveroutput on declare -----select OVER THE Change RATE TABLES--------------- cursor overchangerate is select a.table_owner, a.table_name, a.inserts,a.updates,a.deletes ,b.num_rows from dba_tab_modifications a, dba_tables b where a.table_name = b.table_name and table_owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS', 'XDB', 'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP', 'ORDSYS', 'OUTLN', 'TSMSYS', 'MDSYS') and inserts > 0 and partitioned='NO' and a.inserts/decode(b.num_rows,0,1,b.num_rows)>=0.1 or a.table_name = b.table_name and table_owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS', 'XDB', 'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP', 'ORDSYS', 'OUTLN', 'TSMSYS', 'MDSYS') and updates > 0 and partitioned='NO' and a.updates/decode(b.num_rows,0,1,b.num_rows)>=0.1 or a.table_name = b.table_name and table_owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS', 'XDB', 'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP', 'ORDSYS', 'OUTLN', 'TSMSYS', 'MDSYS') and deletes > 0 and partitioned='NO' and a.deletes/decode(b.num_rows,0,1,b.num_rows)>=0.1 ; ----select the unanalyzed table--------------- cursor nullmonitor is select owner, table_name from dba_tables where owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS', 'XDB', 'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP', 'ORDSYS', 'OUTLN', 'TSMSYS', 'MDSYS') and last_analyzed is null; begin dbms_output.enable(1000000); ----flush the monitorring information into the dba_tab_modifications DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; ----display the unanalyzed table-------------- dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -'); dbms_output.put_line('Unalalyzed tables:'); for v_null in nullmonitor loop dbms_output.put_line(v_null.owner || '.' || v_null.table_name || ' has not been analyzed, consider gathering statistics'); end loop; ----display the information------------------- dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - |