ITPub博客

首页 > 数据库 > Oracle > 统计信息收集

统计信息收集

原创 Oracle 作者:roc_phoenix 时间:2016-03-21 13:51:34 0 删除 编辑
创建表,创建索引,添加数据,此时表跟索引的统计信息都没有。

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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 字符集查询
请登录后发表评论 登录
全部评论

注册时间:2015-09-18

  • 博文量
    48
  • 访问量
    126617