Automatic Statistics Gathering

on

在Oracle10g中引入的优化器统计信息(Optimizer Statistics)自动收集,是一个看上去很不错的功能,但是在实际应用中却往往没有起到相应的效果,甚至在某些系统中我们会建议禁用这个功能。

阐述一些该功能的相关知识点。

1. Automatic Statistics Gathering是由Scheduler调度GATHER_STATS_JOB作业来完成的,在GATHER_STATS_JOB作业中则调用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC存储过程。

2. 该作业在创建数据库的自动创建,并且设置为每天晚上10点到第二天早上6点和周六周日的全天为运行窗口期。在运行窗口期内,该作业都会运行,根据stop_on_window_close属性来决定,如在窗口期结束以后,该作业如果还没有运行完毕,是继续运行还是结束运行。

3. GATHER_DATABASE_STATS_JOB_PROC是内部的存储过程,基本上跟DBMS_STATS.GATHER_DATABASE_STATS的功能一样,但是有内部的优先顺序考虑,更新越多的表将会越优先收集统计信息。

4. 收集统计信息的表对象是,之前从来没有收集过的或者是更新的(包括insert,update,delete,truncate)记录数超过当前总记录数10%的表。记录数的更改量由Oracle数据库自动监控,在初始化参数statistics_level设置为TYPICAL或者ALL时,自动监控即会生效。

5. 在USER_TAB_MODIFICATIONS表中记录了所有被监控的表的数据量更改信息。该信息的更新将会稍微滞后于真实的修改,可以通过DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO存储过程来立刻将更改的信息更新到USER_TAB_MODIFICATIONS表中。对于更新之后再rollback的记录,仍然算为已经受影响的记录,Oracle不会在rollback之后再去更新USER_TAB_MODIFICATIONS表。

SQL> select * from user_tab_modifications where table_name=’EMP’;

no rows selected

SQL> select count(*) from emp;

COUNT(*)
———-
14

SQL> update emp set sal=sal+100;

14 rows updated.

SQL> select * from user_tab_modifications where table_name=’EMP’;

no rows selected

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();

PL/SQL procedure successfully completed.

SQL> select inserts,updates,deletes from user_tab_modifications where table_name
=’EMP’;

INSERTS UPDATES DELETES
———- ———- ———-
0 14 0

SQL> rollback;

Rollback complete.

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();

PL/SQL procedure successfully completed.

SQL> select inserts,updates,deletes from user_tab_modifications where table_name
=’EMP’;

INSERTS UPDATES DELETES
———- ———- ———-
0 14 0

SQL>

6. 在Oracle10g版本(包括最新的10.2.0.4)中没有已知的修改10%这个阀值的方法。但是在Oracle11g中则提供了SET_TABLE_PREFS等函数。

以下命令将指定表的STALE默认值从10%改为5%,该值可以从新的dba_tab_stat_prefs数据字典中查询获得。

–仅限于Oracle11g版本
BEGIN
DBMS_STATS.SET_TABLE_PREFS ( ownname =>’KAMUS’, tabname =>’T1′, pname =>’STALE_PERCENT’, pvalue =>’5′);
END;
/

SQL> select * from dba_tab_stat_prefs;

OWNER TABLE_NAME PREFERENCE_NAME PREFE
———- ———- ——————– —–
KAMUS T1 STALE_PERCENT 5

7. 在Oracle10g中运行以下命令,可以禁用统计信息自动收集功能。
BEGIN
DBMS_SCHEDULER.DISABLE(‘GATHER_STATS_JOB’);
END;
/

5 Comments Add yours

  1. jacklee says:

    刚上10g(10.2.0.3+windows 2003 64bit),问题不断。不知与此有无关系。
    什么情况下,建议不用ASG,谢谢了

  2. kamus says:

    具体情况具体分析,你说的问题不断都是哪些问题?

  3. jacklee says:

    数据库挂了几次,
    Oracle 给的action plan
    ACTION PLAN
    ============
    1. If possible please consider applying the latest patches i.e. 10.2.0.4.0 this
    will ensure fix of bug 5476873. It may also fix any bugs due to MMAN process.
    2. Incase the problem still occurs , you may consider unsetting SGA_TARGET

    这与AMM有关,另外在AWR TOP SQL中有大量系统SQL,例如
    select privilege#, level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0

    Top SQL of Parse Call 几乎全部来自系统,所以有些担心ASG对系统有影响。

    这个系统是从32BIT 9I升上来的,硬件大大增强了,正常是确实比之前快很多,却没想到问题不断。

  4. kamus says:

    收集一次数据字典的统计信息看看

  5. ora-600 says:

    amm是有很大问题的,我也有客户的10g库由于amm造成连接经常连不上,连系统都登陆不进去,或者操作挂起不动等问题了.基本上我已经把它们主要库的SGA_TARGET都禁掉了.

Leave a Reply

Your email address will not be published. Required fields are marked *