收集表的统计信息

收集索引的统计信息

删除表的统计信息

删除索引的统计信息

导出导入统计信息

不同的数据库间统计信息的复制

dbms_stats 和 analyze 的优点和缺点

对柱状图的理解和常用的统计信息，柱状图的视图

[@more@]

常用的 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'