managed oracle statistics

收集表的统计信息 收集索引的统计信息 删除表的统计信息 删除索引的统计信息 导出导入统计信息 不同的数据库间统计信息的复制 dbms_stats 和 analyze 的优点和缺点 对柱状图的理解和常用的统计信息，柱状图的视图 常用的 analyze ,dbms_stats 包的使用 dbms_stats.gather_table_stats 的使用 1 统计表名是 wrj 的统计信息，不统计索引的信息，同时，是自动确定histograms size 的最优值，也是默认值 SQL> exec dbms_stats.gather_table_stats(ownname=>'test',tabname=>'wrj',cascade=>true,method_opt=>dbms_stats.auto_sample_size); PL/SQL procedure successfully completed. 2. 统计表名是 wrj 的统计信息，不统计索引的信息，同时确定并行度是4 SQL> exec dbms_stats.gather_table_stats(ownname=>'test',tabname=>'wrj',cascade=>true,degree=>4); PL/SQL procedure successfully completed. 3. SQL>exec dbms_stats.gather_table_stats(ownname=>'test',tabname=>'wrj',method_opt=>'for columns size 5 object_id'); exec dbms_stats.gather_table_stats('WRJ','AGENT_INFO',method_opt => 'FOR ALL INDEXES FOR ALL INDEXED COLUMNS',cascade => true); 4. SQL> exec dbms_stats.gather_table_stats(ownname=>'test',tabname=>'wrj',estimate_percent=>30); PL/SQL procedure successfully completed. 5. 分析索引信息 SQL> execute dbms_stats.gather_index_stats(ownname=>'test',indname=>'idx_wrj'); PL/SQL procedure successfully completed. 6 单独删除索引信息 SQL> execute dbms_stats.delete_index_stats(ownname=>'test',indname=>'idx_wrj'); PL/SQL procedure successfully completed. 7. 删除表的统计信息(把索引的统计信息一块删除掉) exec dbms_stats.delete_table_stats(ownname=>'test',tabname=>'wrj'); 8. lock unlock 表的信息 SQL> execute dbms_stats.lock_table_stats(ownname=>'test',tabname=>'wrj'); PL/SQL procedure successfully completed. SQL> SQL> execute dbms_stats.unlock_table_stats(ownname=>'test',tabname=>'wrj'); PL/SQL procedure successfully completed. 9. analzye 常用语句 analyze table wrj compute statistics for table for all indexes for all indexed columns analyze table wrj delete statistics analyze table wrj compute statistics for table for columns record_sn size 50; 复制表的信息： 1. 在产品库上 exec dbms_stats.export_table_stats(ownname=>'test1',tabname=>'wrj',stattab=>'STAT_WRJ',cascade=>true); PL/SQL procedure successfully completed. exp test1/test1 file=test1.dmp tables=wrj,stat_wrj 第二 SQL> exec dbms_stats.create_stat_table(ownname=>'test1',stattab=>'stat_wrj'); PL/SQL procedure successfully completed. SQL> SQL> exec dbms_stats.export_table_stats(ownname=>'test1',tabname=>'wrj',stattab=>'stat_wrj'); PL/SQL procedure successfully completed. SQL> SQL> exec dbms_stats.delete_table_stats(ownname=>'test1',tabname=>'wrj'); PL/SQL procedure successfully completed. SQL> exec dbms_stats.import_table_stats(ownname=>'test1',tabname=>'wrj',stattab=>'stat_wrj'); PL/SQL procedure successfully completed. SQL> select num_rows,blocks 2 , empty_blocks 3 , avg_space 4 , avg_row_len 5 , sample_size 6 from user_tables 7 where table_name = upper('wrj'); NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN SAMPLE_SIZE ---------- ---------- ------------ ---------- ----------- ----------- 29441 822 0 0 92 29441 不同schema 的统计信息的复制 在test2 用户内 imp test2/test2 file=test1.dmp tables=wrj,stat_wrj commit=y ignore=y sqlplus test2/test2 sql>update STAT_WRJ set c5='TEST2'; sql>execute dbms_stats.import_schema_stats(ownname=>'test2',stattab=>'stat_wrj'); SQL> select num_rows,blocks 2 , empty_blocks 3 , avg_space 4 , avg_row_len 5 , sample_size 6 from user_tables 7 where table_name = upper('wrj'); NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN SAMPLE_SIZE ---------- ---------- ------------ ---------- ----------- ----------- 29441 822 0 0 96 29441 统计信息需要的查询语句 select num_rows,blocks , empty_blocks , avg_space , avg_row_len , sample_size from dba_tables where table_name = upper('CDR_WRJ_1021') and owner = 'CRBT' select uniqueness , blevel , leaf_blocks , distinct_keys , clustering_factor from user_indexes where index_name = upper('IDX_WRJ') select column_name, num_distinct, num_buckets, low_value, high_value, num_nulls, histogram from user_tab_col_statistics where table_name = 'WRJ' and column_name = 'OBJECT_ID' select column_name, num_distinct, num_buckets, histogram from user_tab_col_statistics where table_name = 'WRJ' and column_name = 'OBJECT_ID' select endpoint_number -- demo08_09.sql , endpoint_value from user_histograms where table_name = 'WRJ' and column_name = 'RECORD_SN' order by endpoint_number select * from user_tab_histograms where table_name = 'WRJ' and column_name = 'RECORD_SN' order by endpoint_number analyze 和dbms_stats 的优缺点 ： 1. dbms_stats 可以并行 2. dbms_stats 可以保存统计信息 (export ,import,lock ) 3. 可以在不同级别上Compute Statistics：单个分区，子分区，全表，所有分区 4. 分区表建议用 dbms_stats 5. dbms_stats 可以一次执行一个用户的统计信息 dbms_stats.gather_schema_stats(User,estimate_percent=>100,cascade=> TRUE); dbms_stats.gather_table_stats(User,TableName,degree => 4,cascade => true); 2、DBMS_STATS的缺点 a) 不能Validate Structure b) 不能收集CHAINED ROWS, 不能收集CLUSTER TABLE的信息，这两个仍旧需要使用Analyze语句。 c) DBMS_STATS 默认不对索引进行Analyze，因为默认Cascade是False，需要手工指定为True 3、对于oracle 9里面的External Table，Analyze不能使用，只能使用DBMS_STATS来收集信息。 histograms Height-Balanced Histograms和Frequency Histograms 的区别 distinct columns 值的大小大于 num_buckets 的大小的时候，就是 Height-Balanced Histograms， 反之，则是 Frequency Histograms num_buckets大小在0-254中间 select column_name, num_distinct, -------------- num_buckets, -------------- histogram from user_tab_col_statistics where table_name = 'WRJ' and column_name = 'OBJECT_ID'