dbms_stats 包常用方法介绍

来源：互联网发布：二维码制作软件下载编辑：程序博客网时间：2024/05/17 12:06

dbms_stats 包是 oracle 9i 之后用来收集统计 oracle 相关信息的包, 虽然 analyze 命令也可以实现类似功能, 但 oracle 还是推荐使用 dbms_stats 包, 最主要的原因 dbms_stats 包能够收集正确的分区表的统计信息, 因为 analyze 命令只能在最低级上收集统计信息, 而高级的统计信息是通过低级的统计信息汇总以及推导出来的. 此外, dbms_stats 包收集的统计信息可以被 CBO 优化器使用, 对于能否制定准确高效的执行计划至关重要.

下面介绍 Oracle 11g 中常用的几个方法

1. 收集 schema 统计信息

DBMS_STATS.GATHER_SCHEMA_STATS (    ownname          VARCHAR2,    estimate_percent NUMBER   DEFAULT to_estimate_percent_type                                                 (get_param('ESTIMATE_PERCENT')),    block_sample     BOOLEAN  DEFAULT FALSE,    method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),   degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),    granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),    cascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')),    stattab          VARCHAR2 DEFAULT NULL,    statid           VARCHAR2 DEFAULT NULL,    options          VARCHAR2 DEFAULT 'GATHER',    objlist          OUT      ObjectTab,   statown          VARCHAR2 DEFAULT NULL,    no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type (                                     get_param('NO_INVALIDATE')),  force             BOOLEAN DEFAULT FALSE,  obj_filter_list  ObjectTab DEFAULT NULL);

method_opt(收集直方图的策略):

for all [indexed | hidden] columns size [integer | repeat | auto | skewonly]

默认值为：for all columns size auto

integer : 直方图桶数, 取消范围 1 – 254, 如果是 1, 则不收集直方图
repeat : 只在已经有直方图的列上收集直方图
auto : oracle 根据列上的数据的分布情况和与对应表相关的工作负载决定哪些列需要收集统计信息
skewonly: oracle 根据列上的数据的分布情况决定哪些列需要收集统计信息(收集数据非均匀分布的列的统计信息)

granularity(收集粒度), 取值如下:

默认值为: AUTO

ALL ：对所有包括子分区, 分区, 全局级别的数据进行收集（Sub Partition + Partition + Table）
AUTO ：根据分区类型由 oracle 决定做哪一种粒度的收集
DEFAULT ：同 GLOBAL AND PARTITION（Table + Partition）
GLOBAL ：收集全局级别的统计信息（Table）
GLOBAL AND PARTITION ：收集全局级别和分区级别的统计信息（Table + Partition）
PARTITION ：收集分区级别的统计信息（Partition）
SUBPARTITION ：收集子分区级别的统计信息（Sub Partition）

options(收集对象的策略), 取值如下：

默认值：GATHER

GATHER ：收集 schema 上所有对象的统计信息
GATHER ATUO ：等于 gather stale 和 gather empty
GATHER STALE ：收集和返回更新超过10%的表(更新包括插入,修改,删除)
GATHER EMPTY：收集和返回没有统计信息的对象
LIST AUTO ：返回 GATHER Auto 处理的对象
LIST STALE ：返回失效的对象(根据 view *_tab_modifications )
LIST EMPTY ：返回当前没有统计信息的对象

Exec dbms_stats.gather_schema_stats( ownname => 'scott', options => 'GATHER AUTO', estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all indexed columns ', degree => 4 )

2. 收集表统计信息

DBMS_STATS.GATHER_TABLE_STATS (    ownname          VARCHAR2,     tabname          VARCHAR2,     partname         VARCHAR2 DEFAULT NULL,    estimate_percent NUMBER   DEFAULT to_estimate_percent_type (get_param    ('ESTIMATE_PERCENT')),     block_sample     BOOLEAN  DEFAULT FALSE,    method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),    degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),    granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),     cascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')),    stattab          VARCHAR2 DEFAULT NULL,     statid           VARCHAR2 DEFAULT NULL,    statown          VARCHAR2 DEFAULT NULL,    no_invalidate    BOOLEAN  DEFAULT  to_no_invalidate_type (get_param('NO_INVALIDATE')),    stattype         VARCHAR2 DEFAULT 'DATA',    force            BOOLEAN  DEFAULT FALSE);

ownname -------- table 所在的schema = 当前用户名
tablename -------- table name
partname -------- 分区名
estimate_percent -------- 动态采集行数比例, 取值范围为 [0.00001, 100], 如果为空代表全表收集
block_sample -------- 是否使用块采样代替行采样
method_opt -------- for all [indexed | hidden] columns size [integer | repeat | auto | skewonly]
degree -------- 并行度

对 emp表中所有有索引的列进行自动收集直方图统计信息

exec dbms_stats.gather_table_stats(ownname => 'scott',tabname => 'emp',estimate_percent => 20,method_opt=> 'for all indexed columns') ;

收集 emp 表中 empno 列的直方图统计信息，同时指定 bucket(桶) 的数量为 5

exec dbms_stats.gather_table_stats(ownname => 'scott',tabname => 'emp',estimate_percent => 20,method_opt=> 'for columns size 5 EMPNO') ;

3. 收集索引统计信息

DBMS_STATS.GATHER_INDEX_STATS (   ownname          VARCHAR2,    indname          VARCHAR2,    partname         VARCHAR2 DEFAULT NULL,   estimate_percent NUMBER   DEFAULT to_estimate_percent_type                                                 (GET_PARAM('ESTIMATE_PERCENT')),   stattab          VARCHAR2 DEFAULT NULL,    statid           VARCHAR2 DEFAULT NULL,   statown          VARCHAR2 DEFAULT NULL,   degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),   granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),   no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type                                                (GET_PARAM('NO_INVALIDATE')),   force            BOOLEAN DEFAULT FALSE);

ownname ------- 索引所在的 schema
indname ------ 索引名
partname ------- 分区名
estimate_percent -------- 动态采集行数比例, 取值范围为 [0.00001, 100], 如果为空代表全表收集
stattab ------- 保存当前统计信息的名字

exec dbms_stats.gather_index_stats(ownname => 'scott',indname => 'IDX_EMPNO',estimate_percent => '20',degree => '4') ;

4. 删除表统计信息

DBMS_STATS.DELETE_TABLE_STATS (   ownname          VARCHAR2,    tabname          VARCHAR2,    partname         VARCHAR2 DEFAULT NULL,   stattab          VARCHAR2 DEFAULT NULL,    statid           VARCHAR2 DEFAULT NULL,   cascade_parts    BOOLEAN  DEFAULT TRUE,    cascade_columns  BOOLEAN  DEFAULT TRUE,   cascade_indexes  BOOLEAN  DEFAULT TRUE,   statown          VARCHAR2 DEFAULT NULL,   no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type (                                     get_param('NO_INVALIDATE')),   force            BOOLEAN DEFAULT FALSE);

exec dbms_stats.delete_table_stats(ownname => 'scott', tabname => ‘EMP’);

5. 查看收集的统计信息

可以参考 sosi.sql 方便的查看

PS: oracle 官网 dbms_stats 介绍

http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_stats.htm#ARPLS059

0 0