dbms_stats

最新推荐文章于 2020-05-02 08:41:21 发布

congnen9588

最新推荐文章于 2020-05-02 08:41:21 发布

阅读量493

点赞数

1.base
1) 为了让查询优化器(query optimizer)产生最优的执行计划，对象上的statistics一定是要有效的。
oracle数据库收集statistics的功能在每个版本都在得到不断的提高

2) 8i中，引入了dbms_stats包，dba可以利用它来决定何时以何种方式来收集statistics，9i中引入了监控功能，数据库决定如何收集statistics，但监控功能必须在收集统计信息时手工打开，即使用gather auto子句来使数据库发现statistics无效时重新收集statistics。

3)10g中，收集statistics完全自动，而且table monitoring功能默认是打开的，不过前提是statistics_level等于typical(default)或者all。在create table或alter table时，[no] monitoring子句已经被obsolete,但如果你使用了，也不会报错，10g会ignore该子句.

2.How Statistics Are Maintained Current
1) 当创建1个10g database或者升级为10g database时，会自动创建1个Job，gather_stats_job,
该job用来收集statistics.
The job is managed by the scheduler and runs when the MAINTENANCE_WINDOW_GROUP window group is opened. The MAINTENANCE_WINDOW_GROUP window group, which has the WEEKEND_WINDOW window and the WEEKNIGHT_WINDOW window, are also created at the database creation time. By default WEEKNIGHT_WINDOW opens Monday through Friday at 10 p.m. for 8 hours. By default WEEKEND_WINDOW opens Saturday at 0000 hours and continues for 48 hours.

2) 该job实际调用了dbms_stats.gather_database_stats_job_proc过程，这是属于10g的一个内部过程，类似于dbms_stats.gather_database_stats. 该任务收集那些缺失statistics，或者statistics太陈旧(比如超过10%的行变化)的对象的stats。

3) 可以用dbms_scheduler.disable('GATHER_STATS_JOB')来禁用自动收集statistics的功能，但不建议这样做。

3.以下情况需要手工收集statistics
1)When a table is loaded using bulk operation

2)When using external tables

3)To collect system statistics

4)To collect statistics on fixed objects (dynamic performance dictionary tables)

4.收集dictionary的stats
1)10g以前版本不关注dictionary的stats，但10g为了更好的性能，需要收集dictionary、OS的stats.
2)DBMS_STATS包具有收集dictionary和fixed table的stats的功能，收集时，最好使用gather auto选项。
3)收集dictionary 的stats方法有以下几种：

    a. DBMS_STATS.GATHER_DATABASE_STATS (GATHER_SYS=>TRUE,   PTIONS=>'GATHER AUTO');     The default for GATHER_SYS parameter is FALSE. If you set it to TRUE, the
statistics on the objects owned by the SYS schema are analyzed along with the other objects of the database.

    b. DBMS_STATS.GATHER_SCHEMA_STATS ('SYS', PTIONS=>'GATHER AUTO');
    Use this option to gather the schema statistics by specifying the SYS schema name.

    c. DBMS_STATS.GATHER_DICTIONARY_STATS (OPTIONS=>'GATHER AUTO');
    This option collects statistics on the SYS, SYSTEM, and any other schema that owns the server components。

    d.需要有analyze any dictionary的权限
4)收集fixed view的stats的方法
    使用DBMS_STATS.GATHER_FIXED_OBJECTS_STATS

5.收集system stats
1)用dbms_stats.gather_system_stats收集system stats,例子：
     exec dbms_stats.gather_system_stats('NOWORKLOAD');
     exec dbms_stats.gather_system_stats('START');
     exec dbms_stats.gather_system_stats('STOP');

注:（AUTOMATIC Optimizer statistics collection可以自动收集dictionary stats，但不会自动收集fixed table stats和system stats）

SQL> select pname,pval1 from sys.aux_stats$ where sname='SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEED
CPUSPEEDNW                     484.974958
IOSEEKTIM                              10
IOTFRSPEED                           4096
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM

已选择9行。

SQL> exec dbms_stats.gather_system_stats('NOWORKLOAD');

PL/SQL 过程已成功完成。

SQL> select pname,pval1 from sys.aux_stats$ where sname='SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEED
CPUSPEEDNW                       1725.294
IOSEEKTIM                           12.68
IOTFRSPEED                           4096
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM

已选择9行。

6.管理statistics
1)10g具有锁定stats,维护历史stats,还原stats的功能

2)lock stats：
     一旦lock，不能更新其stats。
     利用dbms_stats.lock_table_stats来锁定表的stats,利用dbms_stats.unlock_table_stats来解锁。同理，还有dbms_stats.lock_schema_stats。
     可以使用dba_tab_statistics的stattype_locked=ALL列来查看其是否被锁住。

7.statistics history
1) 10g可以自动保存stats历史，dbms_stats包里的gather_*,import_*,set_*过程，用来在更改stats之前自动保存stats信息。
2) dba_optstat_operations字典显示了所有的database-level、schema-level的update stats操作的star time和end time。
     SQL> SELECT stats_update_time FROM dba_tab_stats_history
          WHERE wner = 'TRAING' AND TABLE_NAME = 'ENROLLMENT';
     STATS_UPDATE_TIME
     -----------------------------------
     20-APR-04 11.45.49.898795 AM -05:00
     14-MAY-04 06.20.41.034775 AM -05:00
     SQL>
3) old stats每一interval就会被废弃，默认31天，可以用dbms_stats.get_stats_history_retention函数可以查看当前设置
    SQL> select dbms_stats.get_stats_history_retention from dual;

    GET_STATS_HISTORY_RETENTION
    ---------------------------
                           31
4) dbms_stats.get_stats_history_availabity函数可以查找the oldest time stamp of stats history.
    SQL> select dbms_stats.get_stats_history_availability from dual;

    GET_STATS_HISTORY_AVAILABILITY
    ---------------------------------------------------
    10-9月 -07 10.58.28.000000000 下午 +08:00

5)dbms_stat.alter_stats_history_retention过程可以修改interval

    SQL> exec dbms_stats.alter_stats_history_retention(15);

    PL/SQL procedure successfully completed

    SQL> select dbms_stats.get_stats_history_retention from dual;

    GET_STATS_HISTORY_RETENTION
    ---------------------------
                           15

6)可以利用dbms_stats.purge_stats过程，来手工废弃stats history
    SQL> exec dbms_stats.purge_stats(to_timestamp('20071001','YYYYMMDD'));

    PL/SQL procedure successfully completed

    SQL> select dbms_stats.get_stats_history_availability from dual;

    GET_STATS_HISTORY_AVAILABILITY
    -------------------------------------------------------------
    01-10月-07 12.00.00.000000000 上午 +08:00

7) dbms_stats中的以下过程用于restore stats history
    restore_database_stats
    restore_dictionary_stats
    restore_fixed_objects_stats
    restore_schema_stats
    restore_system_stats
    restore_tables_stats

备注：如果用analyze命令收集stats,old stats不会保留到AWR中，因此也不能用于还原。

8.other
10g中，create index或 rebuild index时会自动collect stats，而compute statistics 子句现在已经被obsolete.