ITPub博客

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

GATHER_STATS_JOB收集统计信息

原创 Oracle 作者:gholay 时间:2014-03-02 20:48:47 0 删除 编辑

GATHER_STATS_JOB收集统计信息

    Oracle10g的自动信息收集功能跟参数STATISTICS_LEVEL有着直接的关系,当该参数的值设置为basic的时候,自动信息收集功能将关闭,大部分的功能将受到影响:
_ ADDM
_ Automatic optimizer statistics collection
_ Automatic SGA memory management
_ AWR snapshots
_ Buffer cache advisory
_ Database time distribution statistics
_ End-to-end application tracing
_ Monitoring of statistics
_ MTTR advisory
_ Object-level statistics
_ PGA target advisory
_ Segment-level statistics
_ Server-generated alerts
_ Service-level statistics
_ Shared Pool sizing advisory
_ Timed statistics
    而oracle的自动信息收集功能是通过一个叫GATHER_STATS_JOB的任务来完成的,该任务调用过程dbms_stats.gather_database_stats_job_proc执行信息收集,它会检测哪些对象没有统计信息或者统计信息比较陈旧,将优先对这些对象进行分析。而我们平时手工用的dbms_stats.gather_database_stats,是没有分优先级的,也就是它将对数据里的所有对象进行分析,不管该对象有没有被分析过。GATHER_STATS_JOB任务一般是安排在晚上10pm-6am,周未全天启动。
SQL> SELECT OWNER,JOB_NAME,TO_CHAR(LAST_START_DATE,'YYYY-MM-DD
HH24:MI:SS'),TO_CHAR(LAST_RUN_DURATION,'YYYY-MM-DD HH24:MI:SS')
  2  FROM DBA_SCHEDULER_JOBS
  3  WHERE JOB_NAME = 'GATHER_STATS_JOB';

OWNER                          JOB_NAME                       TO_CHAR(LAST_START_DATE,'YYYY- TO_CHAR(LAST_RUN_DURATION,'YYY
------------------------------ ------------------------------ ------------------------------ ------------------------------
SYS                            GATHER_STATS_JOB               2007-05-22 22:00:03            +000000000 00:01:55.186000
--我们也可以将这个任务停止:
SQL> BEGIN
  2  DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
  3  END;
  4  /
PL/SQL procedure successfully completed
SQL> SELECT OWNER,JOB_NAME,ENABLED
  2  FROM DBA_SCHEDULER_JOBS
  3  WHERE JOB_NAME = 'GATHER_STATS_JOB';

OWNER                          JOB_NAME                       ENABLED
------------------------------ ------------------------------ -------
SYS                            GATHER_STATS_JOB               FALSE
--启动该任务
SQL> BEGIN
  2  DBMS_SCHEDULER.ENABLE('GATHER_STATS_JOB');
  3  END;
  4  /
PL/SQL procedure successfully completed
SQL> SELECT OWNER,JOB_NAME,ENABLED
  2  FROM DBA_SCHEDULER_JOBS
  3  WHERE JOB_NAME = 'GATHER_STATS_JOB';

OWNER                          JOB_NAME                       ENABLED
------------------------------ ------------------------------ -------
SYS                            GATHER_STATS_JOB               TRUE


--手工执行exec  dbms_stats.gather_database_stats_job_proc

SQL> exec  dbms_stats.gather_database_stats_job_proc


--收集没有分析过的表的统计信息
begin
dbms_stats.gather_schema_stats(ownname=>scott,options=>"gather empty");
end;
--重新分析修改量超过10%的表(这些修改包括插入、更新和删除)
begin
dbms_stats.gather_schema_stats(ownname=>scott,options=>"gather stale");
end;

SQL> create table aa as select * from dba_tables;
Table created
SQL> select table_name,NUM_ROWS from user_tables where table_name='AA';
TABLE_NAME                       NUM_ROWS
------------------------------ ----------
AA                            
SQL> DESC dbms_stats.gather_schema_stats;
Parameter        Type                     Mode Default?
---------------- ------------------------ ---- --------
OWNNAME          VARCHAR2                 IN           
ESTIMATE_PERCENT NUMBER                   IN   Y       
BLOCK_SAMPLE     BOOLEAN                  IN   Y       
METHOD_OPT       VARCHAR2                 IN   Y       
DEGREE           NUMBER                   IN   Y       
GRANULARITY      VARCHAR2                 IN   Y       
CASCADE          BOOLEAN                  IN   Y       
STATTAB          VARCHAR2                 IN   Y       
STATID           VARCHAR2                 IN   Y       
OPTIONS          VARCHAR2                 IN   Y       
OBJLIST          SYS.DBMS_STATS.OBJECTTAB OUT          
STATOWN          VARCHAR2                 IN   Y       
NO_INVALIDATE    BOOLEAN                  IN   Y       
GATHER_TEMP      BOOLEAN                  IN   Y       
GATHER_FIXED     BOOLEAN                  IN   Y       
STATTYPE         VARCHAR2                 IN   Y       
FORCE            BOOLEAN                  IN   Y       
OWNNAME          VARCHAR2                 IN           
ESTIMATE_PERCENT NUMBER                   IN   Y       
BLOCK_SAMPLE     BOOLEAN                  IN   Y       
METHOD_OPT       VARCHAR2                 IN   Y       
DEGREE           NUMBER                   IN   Y       
GRANULARITY      VARCHAR2                 IN   Y       
CASCADE          BOOLEAN                  IN   Y       
STATTAB          VARCHAR2                 IN   Y       
STATID           VARCHAR2                 IN   Y       
OPTIONS          VARCHAR2                 IN   Y       
STATOWN          VARCHAR2                 IN   Y       
NO_INVALIDATE    BOOLEAN                  IN   Y       
GATHER_TEMP      BOOLEAN                  IN   Y       
GATHER_FIXED     BOOLEAN                  IN   Y       
STATTYPE         VARCHAR2                 IN   Y       
FORCE            BOOLEAN                  IN   Y       
SQL> begin
  2  dbms_stats.gather_schema_stats(ownname=>'SCOTT',options=>'gather empty');
  3  end;
  4  /
PL/SQL procedure successfully completed
SQL> select table_name,NUM_ROWS from user_tables where table_name='AA';
TABLE_NAME                       NUM_ROWS
------------------------------ ----------
AA                                   1539
SQL> INSERT INTO AA SELECT * FROM DBA_TABLES WHERE ROWNUM<160;
159 rows inserted
SQL> COMMIT;
SQL> select table_name,NUM_ROWS from user_tables where table_name='AA';
TABLE_NAME                       NUM_ROWS
------------------------------ ----------
AA                                   1539
SQL> begin
  2  dbms_stats.gather_schema_stats(ownname=>'SCOTT',options=>'gather stale');
  3  end;
  4  /

PL/SQL procedure successfully completed

SQL> select table_name,NUM_ROWS from user_tables where table_name='AA';

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
AA                                   1698

SQL> INSERT INTO AA SELECT * FROM DBA_TABLES WHERE ROWNUM<150;
149 rows inserted
SQL> COMMIT;
Commit complete
SQL> begin
  2  dbms_stats.gather_schema_stats(ownname=>'SCOTT',options=>'gather stale');
  3  end;
  4  /
PL/SQL procedure successfully completed
SQL> select table_name,NUM_ROWS from user_tables where table_name='AA';

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
AA                                   1698

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

上一篇: invisible index
请登录后发表评论 登录
全部评论

注册时间:2013-12-28

  • 博文量
    82
  • 访问量
    651564