GATHER_STATS_JOB收集统计信息

最新推荐文章于 2021-06-23 00:36:22 发布

congshengao4046

最新推荐文章于 2021-06-23 00:36:22 发布

阅读量500

点赞数

文章标签：数据库

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

转载于:http://blog.itpub.net/29196873/viewspace-1098207/