统计信息收集

最新推荐文章于 2024-09-17 13:42:29 发布

congzhirou8448

最新推荐文章于 2024-09-17 13:42:29 发布

阅读量81

点赞数

文章标签：数据库

创建表，创建索引，添加数据，此时表跟索引的统计信息都没有。

12:40:18 rodman@RODMAN>create table t as select object_id,object_name from dba_objects where 1=0;

Table created.

12:40:47 rodman@RODMAN>create index index_t on t(object_id);

Index created.

12:40:56 rodman@RODMAN>insert into t select object_id,object_name from dba_objects;

80784 rows created.

12:41:06 rodman@RODMAN>select num_rows,avg_row_len,blocks,last_analyzed from user_tables where table_name='T';

NUM_ROWS AVG_ROW_LEN BLOCKS LAST_ANALYZED
---------- ----------- ---------- -------------------

12:41:13 rodman@RODMAN>select blevel,leaf_blocks,distinct_keys,last_analyzed from user_indexes where table_name='T';

BLEVEL LEAF_BLOCKS DISTINCT_KEYS LAST_ANALYZED
---------- ----------- ------------- -------------------
0 0 0 2016-03-21 12:40:55

执行表的统计信息收集，并且连带索引信息也收集完成。

12:43:25 rodman@RODMAN>exec dbms_stats.gather_table_stats('RODMAN','T');

PL/SQL procedure successfully completed.

12:43:59 rodman@RODMAN>
12:44:00 rodman@RODMAN>select num_rows,avg_row_len,blocks,last_analyzed from user_tables where table_name='T';

NUM_ROWS AVG_ROW_LEN BLOCKS LAST_ANALYZED
---------- ----------- ---------- -------------------
80784 30 496 2016-03-21 12:43:58

12:44:06 rodman@RODMAN>select blevel,leaf_blocks,distinct_keys,last_analyzed from user_indexes where table_name='T';

BLEVEL LEAF_BLOCKS DISTINCT_KEYS LAST_ANALYZED
---------- ----------- ------------- -------------------
1 167 80784 2016-03-21 12:43:59

或者用：
analyze table t compute statistics;
analyze table t compute statistics for all indexes;

begin
dbms_stats.gather_table_stats(RODMAN,'EMP',cascade=>true);
end;
/

select /*+ gather_plan_statistics */ * from t;

用户级别
$ sqlplus / as sysdba
Sql> BEGIN
SYS.DBMS_STATS.GATHER_SCHEMA_STATS (
OwnName => 'ADMIN'
,Estimate_Percent => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE
,Degree => 4
,Cascade => TRUE
,No_Invalidate => TRUE);
END;
/

数据库级别
$ sqlplus / as sysdba
Sql> BEGIN
SYS.DBMS_STATS.GATHER_DATABASE_STATS (
Estimate_Percent => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE
,Degree => 4
,Cascade => TRUE
,No_Invalidate => TRUE);
END;
/

查看统计信息级别：

13:26:45 rodman@RODMAN>select STATISTICS_NAME,ACTIVATION_LEVEL from V$STATISTICS_LEVEL;

STATISTICS_NAME ACTIVAT
---------------------------------------------------------------- -------
Buffer Cache Advice TYPICAL
MTTR Advice TYPICAL
Timed Statistics TYPICAL
Timed OS Statistics ALL
Segment Level Statistics TYPICAL
PGA Advice TYPICAL
Plan Execution Statistics ALL
Shared Pool Advice TYPICAL
Modification Monitoring TYPICAL
Longops Statistics TYPICAL
Bind Data Capture TYPICAL
Ultrafast Latch Statistics TYPICAL
Threshold-based Alerts TYPICAL
Global Cache Statistics TYPICAL
Global Cache CPU Statistics ALL
Active Session History TYPICAL
Undo Advisor, Alerts and Fast Ramp up TYPICAL
Streams Pool Advice TYPICAL
Time Model Events TYPICAL
Plan Execution Sampling TYPICAL
Automated Maintenance Tasks TYPICAL
SQL Monitoring TYPICAL
Adaptive Thresholds Enabled TYPICAL
V$IOSTAT_* statistics TYPICAL

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30491527/viewspace-2060911/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30491527/viewspace-2060911/