Oracle-统计信息收集&&分析表和索引_oracle手动收集数据库统计信息-CSDN博客

本文链接：https://blog.csdn.net/FuTian0715/article/details/133772429

统计信息-DBMS_STATS

analyze始于Oracle7，但自从Oracle8.1.5引入dbms_stats包后，Oracle便推荐使用dbms_stats取代analyze

官网地址
https://docs.oracle.com/cd/E11882_01/server.112/e41573/stats.htm#PFGRF003
https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_stats.htm#ARPLS059

1. 收集统计信息

gather_system_stats

-- 系统信息的统计信息收集
gather_system_stats (
  gathering_mode  varchar2 default 'NOWORKLOAD', --收集模式（START、STOP、INTERVAL、NOWORKLOAD）
  interval  integer  default 60, --gathering_mode => 'INTERVAL', interval => 10 以未来10分钟的系统负载，收集系统信息
  stattab   varchar2 default null, --用来存放统计信息的表名称 如果使用此参数，不输入则会报错，需要使用dbms_stats.create_stat_table来创建
  statid    varchar2 default null, --STATTAB表中的第一列，用来区分不同的统计信息
  statown   varchar2 default null); --STATTAB表的用户 如果使用此参数，不输入则会报错

--常用语句
BEGIN
  dbms_stats.create_stat_table(ownname          => 'HXAPP',
                               stattab          => 'UNTIFA_STATTAB',
                               tblspace         => 'TEST_DATA',
                               global_temporary => FALSE);
END;

BEGIN
  dbms_stats.gather_system_stats(stattab => 'UNTIFA_STATTAB',
                                 statid  => 'UNTIFA_STATID',
                                 statown => 'HXAPP');
END;

BEGIN
  dbms_stats.gather_system_stats();
END;

Oracle提供了dbms_stats.gather_system_stats来收集系统统计信息。系统统计信息让优化器考虑服务器的IO与CPU性能及其利用率，作为计算成本的依据；为每一个可选的执行计划估算IO与CPU成本。因而对于CBO来说，获得准确的系统统计信息对于正确估计成本是非常重要的。Oracle收集的系统统计信息主要内容说明如下：
–cpuspeedNW 表示非负载情况下的cpu速度,在系统启动时自动搜集
–ioseektim IO查找时间，以毫秒表示；缺省为10ms,非负载模式或可以手动设置。
–iotfrspeed IO传输速度，表示Oracle数据库单次读数据的传输速率，单位为bytes/ms，在系统启动时自动收集；默认为4096 bytes/ms
–cpuspeed 表示负载情况下的cpu速度,以平均每秒可提供的cpu周期表示
–maxthr 最大IO吞吐量,单位为bytes/s
–slavethr 从属IO吞吐量，表示并行进程时，从属进程的IO吞吐量，单位为bytes/s
–sreadtim 单块读时间(如索引读取)，表示随机读一个Oracle数据块的时间，以ms计算
–mreadtim 多块读时间(主要是指全表扫描),表示连续读取多个Oracle数据库的平均时间，以ms计算　
–mbrc 多块读计数，表示一次多块读的读取的Oracle数据块数量
以上系统统计信息存储在sys.aux_stats$表中：SELECT * FROM sys.aux_stats$ t;
系统统计信息有工作负载与无工作负载两种类型; ioseektim、iotrfspeed、cpuspeednw是无负载的统计信息，也就是说不需要系统有工作负载，可以系统空闲时进行收集。Oracle为在系统启动时间重新设置，或重置为默认值。要手动收集非工作负载统计信息，使用dbms_stats.gather_system_stats(gathering_mode => ‘NOWORKLOAD’)。当使用dbms_stats.delete_system_stats()删除系统统计信息时间，将只保留非负载时的统计信息。不同压力与不同类型的应用，甚至同一系统的不同时间，cpu与io的能力都是不一样的，理想的情况是，收集不同系统负载下的系统统计信息，存放到特定的统计信息表中，然后在负载发生变化的时候导入到Oracle中，但在要求高可用的系统中，频繁的变更系统统计信息不太现实。大多数情况下，只需要采集系统高峰时段或典型时段的系统统计信息即可。
收集负载情况下的统计信息有两种方式，一种是手工指定收集时段的开始与结束：
gathering_mode => ‘START’ 开始收集
gathering_mode => ‘STOP’ 停止收集
gathering_mode => ‘INTERVAL’, interval => 10 以未来10分钟的系统负载，收集系统信息
需要说明的是收集系统统计信息，并不影响已经缓存的sql语句，只会影响新解析的SQL语句，如果要已经缓存的SQL语句也按新的系统统计信息生成执行计划，只有清空共享池，但这在生产系统上是比较危险的操作。另外需要注意的就是，如果在收集时段内没有相应操作，将不会收集对应的系统统计信息；例如，如果收集时段内没有产生全表扫描的多块读，mbrc(多块读计数)将不会收集。
系统统计信息对CBO成本计算的影响
虽然CBO计算的成本只是对生成何种执行计划有关，并不对真正执行SQL语句的真实代价产生什么影响，但作为CBO估算成本的基础要素，系统统计信息要尽量保证准确。

案例：

-- 创建测试表及数据
CREATE TABLE untifa_test AS SELECT * FROM dba_objects;
INSERT INTO untifa_test SELECT * FROM dba_objects;

-- 测试表统计信息的收集
BEGIN
  dbms_stats.gather_table_stats(ownname => 'HXAPP',
                                tabname => 'UNTIFA_TEST',
                                cascade => TRUE);
END;

-- 然后删除工作负载的统计信息，只保留非工作负载的统计信息
BEGIN
  dbms_stats.delete_system_stats();
END;

-- 查看全表扫描的成本
SQL> EXPLAIN PLAN FOR SELECT COUNT(1) FROM untifa_test;

Explained


SQL> SELECT * FROM table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2407157032

--------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     1 |  1036   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |             |     1 |            |          |
|   2 |   TABLE ACCESS FULL| UNTIFA_TEST |   196K|  1036   (1)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version

13 rows selected
/*可以看到Oracle在没有负载情况下的系统统计信息时，估算的成本为1036。
接下来，我们使用导入一些系统统计信息。由于测试环境，没有什么负载，
我们使用dbms_stats.set_system_stats过程来手工修改统计信息
*/

-- 创建统计信息表
BEGIN
  dbms_stats.create_stat_table(ownname => 'HXAPP', stattab => 'SYSTEM_STATS');
END;

-- 设置相关的统计信息值
BEGIN
  dbms_stats.set_system_stats(pname   => 'SREADTIM',
                              pvalue  => '6',
                              stattab => 'SYSTEM_STATS');

  dbms_stats.set_system_stats(pname   => 'MREADTIM',
                              pvalue  => '12',
                              stattab => 'SYSTEM_STATS');

  dbms_stats.set_system_stats(pname   => 'CPUSPEED',
                              pvalue  => '1800',
                              stattab => 'SYSTEM_STATS');

  dbms_stats.set_system_stats(pname   => 'MBRC',
                              pvalue  => '16',
                              stattab => 'SYSTEM_STATS');
END;

-- 导入相应统计信息
BEGIN
  dbms_stats.import_system_stats(stattab => 'SYSTEM_STATS',
                                 statown => 'HXAPP');
END;

-- 重新查看全表扫描的成本
SQL> EXPLAIN PLAN FOR SELECT COUNT(1) FROM untifa_test;

Explained


SQL> SELECT * FROM table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2407157032

--------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     1 |   483   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE    |             |     1 |            |          |
|   2 |   TABLE ACCESS FULL| UNTIFA_TEST |   196K|   483   (2)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version

13 rows selected
/*
这次估算的成本(cost)为483,在收集了系统信息后，CBO估算的成本发生了变化。
我们知道Oracle提供了db_file_multiblock_read_count参数，
来控制Oracle一次多块读的Oracle数据块数量，也将影响Oracle对全表扫描成本的估算。
Oracle既然收集了多块读IO速度(mreadtim)、多块读计数(mbrc)等信息，
那db_file_multiblock_read_count的设置与这些统计信息是什么关系呢？
答案是：如果存在负载情况下的多块读的相关统计信息，
将会忽略db_file_multiblock_read_count的设置，
如果不存在相应的系统统计信息，
将使用db_file_multiblock_read_count的值对全表扫描成本进行估算。
备注：（初始化参数db_file_multiblock_read_count
是用来约束Oracle进行多数据块读取时的行为，
所谓多数据块读取，就是Oracle在一次I/O时，可以读取多个数据块，
从而用最小的I/O完成数据的读取）。
*/

-- 删除工作负载的统计信息，只保留非工作负载的统计信息
BEGIN
  dbms_stats.delete_system_stats();
END;

-- 重新查看全表扫描的成本
SQL> EXPLAIN PLAN FOR SELECT COUNT(1) FROM untifa_test;

Explained


SQL> SELECT * FROM table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2407157032

--------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     1 |  1036   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |             |     1 |            |          |
|   2 |   TABLE ACCESS FULL| UNTIFA_TEST |   196K|  1036   (1)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version

13 rows selected
/*
可以看成本(cost)又回到了未收集系统统计信息时的1036，而不是收集后的483，
这个时候，我们修改db_file_multiblock_read_count参数，
来看看相应的成本cost是否会起变化。
*/

-- 设置db_file_multiblock_read_count
SQL>  show parameter db_file_multiblock_read_count;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     128

SQL> alter session set db_file_multiblock_read_count=256;

Session altered

-- 重新查看全表扫描的成本
SQL> EXPLAIN PLAN FOR SELECT COUNT(1) FROM untifa_test;

Explained


SQL> SELECT * FROM table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2407157032

--------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     1 |   651   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |             |     1 |            |          |
|   2 |   TABLE ACCESS FULL| UNTIFA_TEST |   196K|   651   (1)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version

13 rows selected
/*
可以看到在没有系统统计信息的情况下，设置db_file_multiblock_read_count，
可以影响SQL的成本估算，现在变成了651。
现在重新导入系统统计信息，看设置db_file_multiblock_read_count能否影响执行计划
*/

-- 重新导入统计信息
BEGIN
  dbms_stats.import_system_stats(stattab => 'SYSTEM_STATS',
                                 statown => 'HXAPP');
END;

-- 还原db_file_multiblock_read_count
SQL> alter session set db_file_multiblock_read_count=128;

Session altered


SQL> show parameter db_file_multiblock_read_count;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     128

-- 重新查看全表扫描的成本
SQL> EXPLAIN PLAN FOR SELECT COUNT(1) FROM untifa_test;

Explained


SQL> SELECT * FROM table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2407157032

--------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     1 |   483   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE    |             |     1 |            |          |
|   2 |   TABLE ACCESS FULL| UNTIFA_TEST |   196K|   483   (2)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version

13 rows selected
/*可以看到，导入系统统计信息后，成本又变成了483，
我们再设置db_file_multiblock_read_count，再重新解析sql语句
*/

-- 设置db_file_multiblock_read_count
SQL> alter session set db_file_multiblock_read_count=256;

Session altered


SQL> show parameter db_file_multiblock_read_count;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     256

-- 重新查看全表扫描的成本
SQL> EXPLAIN PLAN FOR SELECT COUNT(1) FROM untifa_test;

Explained


SQL> SELECT * FROM table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2407157032

--------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     1 |   483   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE    |             |     1 |            |          |
|   2 |   TABLE ACCESS FULL| UNTIFA_TEST |   196K|   483   (2)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version

13 rows selected

gather_dictionary_stats

-- 字典对象的统计信息收集
gather_dictionary_stats
    (comp_id varchar2 default null, -- 组件ID，不确认是不是dba_registry这张表里的comp_id
     estimate_percent number default DEFAULT_ESTIMATE_PERCENT, -- 采样百分比，有效范围为[0.000001,100]（默认Oracle自动确定适当的样本量以获得良好的统计数据）
     block_sample boolean default FALSE, -- 使用随机块采样代替随机行采样
     method_opt varchar2 default DEFAULT_METHOD_OPT, -- 抽样方法for table:只统计表 
     												 -- for all indexed columns:只统计有索引的表列
     												 -- for all indexes:只分析统计相关索引
     												 -- for all columns：分析所有的列
     												 -- for all hidden columns：分析所有隐藏列（函数索引等）
     degree number default DEFAULT_DEGREE_VALUE, -- 并行处理的cpu数量
     granularity varchar2 default DEFAULT_GRANULARITY, -- 要收集的统计信息的粒度（仅在表已分区时才相关）
     												   -- 'AUTO':根据分区类型确定粒度，默认值
     												   -- 'ALL':收集所有（子分区，分区和全局）统计信息
     												   -- 'GLOBAL':收集全球统计数据
     												   -- 'GLOBAL AND PARTITION':收集全局和分区级别的统计信息。即使它是一个复合分区对象，也不会收集任何子分区级别统计信息。
     												   -- 'PARTITION ':收集分区级别的统计信息
     												   -- 'SUBPARTITION' -收集子分区级别的统计信息
     												   -- 'DEFAULT':收集全局和分区级别的统计信息。该选项已过时，并且当前受支持，但仅出于遗留原因才包含在文档中。您应该使用"GLOBAL AND PARTITION"来实现此功能。请注意，默认值现在是'AUTO'
     cascade boolean default DEFAULT_CASCADE, -- 是否收集此表索引的统计信息
     stattab varchar2 default null, -- 用户统计信息表标识符，用于描述将当前统计信息保存在何处
     statid varchar2 default null, -- 标识符（可选），用于与这些统计信息关联 stattab
     options varchar2 default 'GATHER AUTO', -- GATHER default 收集方案下(schema)所有对象的统计信息。
											 -- GATHER AUTO 由Oracle自动决定收集哪些必要的统计数据。当指定参数为GATHER AUTO时，附加有效参数只有stattab, statid, objlist和statown;所有其他参数设置将被忽略。返回已处理对象的列表。
											 -- GATHER STALE 收集通过查看* _tab_modify视图确定的陈旧对象的统计信息。返回值为已过期对象的列表。
											 -- GATHER EMPTY 收集当前没有统计信息的对象的统计信息。返回没有统计信息对象的列表。
											 -- LIST AUTO 返回要用 GATHER AUTO 处理对象的列表。
											 -- LIST STALE 返回要用 GATHER STALE 处理对象的列表。
											 -- LIST EMPTY 返回要用 GATHER EMPTY 处理对象的列表。
     objlist out ObjectTab, -- 对于options 选项 除了GATHER外，都会返回统计对象的列表
     statown varchar2 default null, -- 包含的架构stattab（如果不同于ownname）
     no_invalidate boolean default to_no_invalidate_type(get_param('NO_INVALIDATE')), -- 如果设置为TRUE，则不使从属游标无效。如果设置为FALSE，则该过程将立即使从属游标无效。默认Oracle自己决定何时使依赖的游标无效
     stattype varchar2 default 'DATA', -- 统计信息类型。允许的唯一值是DATA
     obj_filter_list ObjectTab default null);

-- 常用语句
BEGIN
  dbms_stats.gather_dictionary_stats(degree => 8, cascade => TRUE);
END;

在网上查询到关于此命令的案例

https://developer.aliyun.com/article/603851

obj_filter_list 的使用具体参考
http://t.csdnimg.cn/eJVyD

gather_database_stats

-- 数据库信息的统计信息收集
gather_database_stats
    (estimate_percent number default DEFAULT_ESTIMATE_PERCENT,
     block_sample boolean default FALSE,
     method_opt varchar2 default DEFAULT_METHOD_OPT,
     degree number default DEFAULT_DEGREE_VALUE,
     granularity varchar2 default DEFAULT_GRANULARITY,
     cascade boolean default DEFAULT_CASCADE,
     stattab varchar2 default null, 
     statid varchar2 default null,
     options varchar2 default 'GATHER', 
     objlist out NOCOPY ObjectTab, 
     statown varchar2 default null,
     gather_sys boolean default TRUE,
     no_invalidate boolean default to_no_invalidate_type(get_param('NO_INVALIDATE')), 
     gather_temp boolean default FALSE,
     gather_fixed boolean default FALSE,
     stattype varchar2 default 'DATA',
     obj_filter_list ObjectTab default null);

-- 常用语句
BEGIN
  dbms_stats.gather_database_stats(degree           => 4,
                                   block_sample     => TRUE,
                                   estimate_percent => '10',
                                   cascade          => TRUE,
                                   granularity      => 'ALL');
END;

gather_schema_stats

-- schema的统计信息收集
gather_schema_stats
    (ownname varchar2, -- SCHEMA的信息
     estimate_percent number default DEFAULT_ESTIMATE_PERCENT,
     block_sample boolean default FALSE,
     method_opt varchar2 default  DEFAULT_METHOD_OPT,
     degree number default DEFAULT_DEGREE_VALUE,
     granularity varchar2 default DEFAULT_GRANULARITY,
     cascade boolean default DEFAULT_CASCADE,
     stattab varchar2 default null, 
     statid varchar2 default null,
     options varchar2 default 'GATHER', 
     objlist out NOCOPY ObjectTab,
     statown varchar2 default null,
     no_invalidate boolean default to_no_invalidate_type(get_param('NO_INVALIDATE')),
     gather_temp boolean default FALSE,
     gather_fixed boolean default FALSE,
     stattype varchar2 default 'DATA',
     force boolean default FALSE, -- 当这个参数的值为TRUE时，即使锁表也会强制收集索引统计信息。
     obj_filter_list ObjectTab default null);

-- 常用语句
BEGIN
  dbms_stats.gather_schema_stats(ownname     => 'HXAPP',
                                 degree      => 8,
                                 cascade     => TRUE,
                                 granularity => 'ALL');
END;

gather_table_stats

-- 表的统计信息收集
gather_table_stats
    (ownname varchar2, 
     tabname varchar2, -- 表名
     partname varchar2 default null, -- 分区表的某个分区名
     estimate_percent number default DEFAULT_ESTIMATE_PERCENT,
     block_sample boolean default FALSE,
     method_opt varchar2 default DEFAULT_METHOD_OPT,
     degree number default DEFAULT_DEGREE_VALUE,
     granularity varchar2 default  DEFAULT_GRANULARITY,
     cascade boolean default DEFAULT_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,
     -- the context is intended for internal use only.
     context dbms_stats.CContext default null,
     options varchar2 default DEFAULT_OPTIONS);

--常用语句
BEGIN
  dbms_stats.gather_table_stats(ownname          => 'HXAPP',
                                tabname          => 'KCGB_SFDJBU',
                                method_opt       => 'for all columns',
                                estimate_percent => '100',
                                degree           => '8',
                                granularity      => 'all',
                                cascade          => TRUE);
END;

gather_index_stats

-- index的统计信息收集
gather_index_stats
    (ownname varchar2, 
     indname varchar2, -- 索引名
     partname varchar2 default null,
     estimate_percent number default DEFAULT_ESTIMATE_PERCENT,
     stattab varchar2 default null, 
     statid varchar2 default null,
     statown varchar2 default null,
     degree number default DEFAULT_DEGREE_VALUE,
     granularity varchar2 default DEFAULT_GRANULARITY,
     no_invalidate boolean default to_no_invalidate_type(get_param('NO_INVALIDATE')),
     stattype varchar2 default 'DATA',
     force boolean default FALSE);
-- 常用语句
BEGIN
  dbms_stats.gather_index_stats(ownname          => 'HXAPP',
                                indname          => 'KCGB_SFDJBU_IDX1',
                                estimate_percent => '100',
                                degree           => '4');
END;

2. 删除统计信息

delete_system_stats

-- 删除系统信息的统计信息收集
delete_system_stats (
   stattab         varchar2  default nulL,
   statid          varchar2  default nulL,
   statown         varchar2  default null);

delete_dictionary_stats

-- 删除字典对象的统计信息收集
delete_dictionary_stats(
        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 'ALL',
        force boolean default FALSE,
        stat_category varchar2 default DEFAULT_DEL_STAT_CATEGORY);

delete_database_stats

-- 删除数据库信息的统计信息收集
delete_database_stats(
        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 'ALL',
        force boolean default FALSE,
        stat_category varchar2 default DEFAULT_DEL_STAT_CATEGORY);

delete_schema_stats

-- 删除schema的统计信息收集
delete_schema_stats(
        ownname varchar2,
        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 'ALL',
        force boolean default FALSE,
        stat_category varchar2 default DEFAULT_DEL_STAT_CATEGORY);

delete_table_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')),
        stattype varchar2 default 'ALL',
        force boolean default FALSE,
        stat_category varchar2 default DEFAULT_DEL_STAT_CATEGORY);

delete_index_stats

-- 删除index的统计信息收集
delete_index_stats(
        ownname varchar2, 
        indname varchar2,
        partname varchar2 default null,
        stattab varchar2 default null, 
        statid varchar2 default null,
        cascade_parts boolean default true,
        statown varchar2 default null,
        no_invalidate boolean default to_no_invalidate_type(get_param('NO_INVALIDATE')),
        stattype varchar2 default 'ALL',
        force boolean default FALSE);

delete_column_stats

-- 删除列的统计信息收集
delete_column_stats(
        ownname varchar2, 
        tabname varchar2, 
        colname varchar2, -- 列名
        partname varchar2 default null,
        stattab varchar2 default null, 
        statid varchar2 default null,
        cascade_parts boolean default true,
        statown varchar2 default null,
        no_invalidate boolean default to_no_invalidate_type(get_param('NO_INVALIDATE')),
        force boolean default FALSE,
        col_stat_type varchar2 default 'ALL');

3. 创建备份收集信息表

create_stat_table

create_stat_table(
        ownname varchar2, 
        stattab varchar2,
        tblspace varchar2 default null,
        global_temporary boolean default false); -- 指定了表空间，此参数必须为false

-- 常用语句
BEGIN
  dbms_stats.create_stat_table(ownname          => 'HXAPP',
                               stattab          => 'UNTIFA_STATTAB',
                               tblspace         => 'TEST_DATA',
                               global_temporary => FALSE);
END;

4. 备份收集信息

export_system_stats

-- 
export_system_stats (
   stattab  varchar2,
   statid   varchar2 default null,
   statown  varchar2 default null);

export_dictionary_stats

--
export_dictionary_stats(
        stattab varchar2, 
        statid varchar2 default null,
        statown varchar2 default null,
        stat_category varchar2 default DEFAULT_STAT_CATEGORY);

export_database_stats

-- 
export_database_stats(
        stattab varchar2, 
        statid varchar2 default null,
        statown varchar2 default null,
        stat_category varchar2 default DEFAULT_STAT_CATEGORY);

export_schema_stats

--
 export_schema_stats(
        ownname varchar2,
        stattab varchar2, 
        statid varchar2 default null,
        statown varchar2 default null,
        stat_category varchar2 default DEFAULT_STAT_CATEGORY);

export_table_stats

--
export_table_stats(
        ownname varchar2, 
        tabname varchar2,
        partname varchar2 default null,
        stattab varchar2, 
        statid varchar2 default null,
        cascade boolean default true,
        statown varchar2 default null,
        stat_category varchar2 default DEFAULT_STAT_CATEGORY
);

export_index_stats

--
export_index_stats(
        ownname varchar2, 
        indname varchar2,
        partname varchar2 default null,
        stattab varchar2, 
        statid varchar2 default null,
        statown varchar2 default null);

export_column_stats

--
export_column_stats(
        ownname varchar2, 
        tabname varchar2, 
        colname varchar2,
        partname varchar2 default null,
        stattab varchar2, 
        statid varchar2 default null,
        statown varchar2 default null);

5. 导入收集信息

import_system_stats

--
import_system_stats (
   stattab  varchar2,
   statid   varchar2 default null,
   statown  varchar2 default null);

import_dictionary_stats

--
import_dictionary_stats(
        stattab varchar2, 
        statid varchar2 default null,
        statown varchar2 default null,
        no_invalidate boolean default to_no_invalidate_type(get_param('NO_INVALIDATE')),
        force boolean default FALSE,
        stat_category varchar2 default DEFAULT_STAT_CATEGORY);

import_database_stats

-- 
import_database_stats(
        stattab varchar2, 
        statid varchar2 default null,
        statown varchar2 default null,
        no_invalidate boolean default to_no_invalidate_type(get_param('NO_INVALIDATE')),
        force boolean default FALSE,
        stat_category varchar2 default DEFAULT_STAT_CATEGORY
        );

import_schema_stats

-- 
import_schema_stats(
        ownname varchar2,
        stattab varchar2, 
        statid varchar2 default null,
        statown varchar2 default null,
        no_invalidate boolean default to_no_invalidate_type(get_param('NO_INVALIDATE')),
        force boolean default FALSE,
        stat_category varchar2 default DEFAULT_STAT_CATEGORY);

import_table_stats

--
import_table_stats(
        ownname varchar2, 
        tabname varchar2, 
        partname varchar2 default null,
        stattab varchar2, statid varchar2 default null,
        cascade boolean default true,
        statown varchar2 default null,
        no_invalidate boolean default
          to_no_invalidate_type(get_param('NO_INVALIDATE')),
        force boolean default FALSE,
        stat_category varchar2 default DEFAULT_STAT_CATEGORY);

import_index_stats

--
import_index_stats(
        ownname varchar2, indname varchar2,
        partname varchar2 default null,
        stattab varchar2, statid varchar2 default null,
        statown varchar2 default null,
        no_invalidate boolean default
          to_no_invalidate_type(get_param('NO_INVALIDATE')),
        force boolean default FALSE);

import_column_stats

--
import_column_stats(
        ownname varchar2, 
        tabname varchar2, 
        colname varchar2,
        partname varchar2 default null,
        stattab varchar2, 
        statid varchar2 default null,
        statown varchar2 default null,
        no_invalidate boolean default to_no_invalidate_type(get_param('NO_INVALIDATE')),
        force boolean default FALSE);

6. 可以通过DBA_TABLES来查看表是否与被分析过

SELECT TABLE_NAME, LAST_ANALYZED FROM DBA_TABLES

分析表和索引-ANALYZE

例子
在测试收取费用时，发现收费登记簿按照index_odb1索引查询和更新特别慢，并且对表进行统计信息收集后依然无法解决，在网上查到了analyze工具，在执行了
analyze table xxxx_sfdjbu compute statistics for table for all indexes for all indexed columns;
命令后，之前耗时40秒左右的慢SQL基本不会出现了。

首先查询长期锁表SQL的sid

SELECT b.sid,
       b.serial#,
       b.logon_time,
       b.username,
       b.osuser,
       a.object_name,
       'alter system kill session ''' || b.sid || ',' || b.serial# || ''';' AS kill
  FROM (SELECT s.*, t.object_name
          FROM v$locked_object s, dba_objects t
         WHERE s.object_id = t.object_id) a
  LEFT OUTER JOIN v$session b
    ON a.session_id = b.sid
 ORDER BY b.logon_time, b.sid;

然后根据sid查询SQL计划等信息

SELECT * FROM v$session t WHERE t.SID = '10407';
SELECT * FROM v$sql t WHERE t.SQL_ID = '9tpmx87nz4jzh';
SELECT * FROM v$sql_plan t WHERE t.SQL_ID = '9tpmx87nz4jzh';
SELECT * FROM  v$sqlarea t WHERE t.SQL_ID = '9tpmx87nz4jzh';

SELECT * FROM v$session t WHERE t.SID = '10407';
SELECT * FROM v$sql t WHERE t.SQL_ID = '56a5h016rxdx8';
SELECT * FROM v$sql_plan t WHERE t.SQL_ID = '56a5h016rxdx8';
SELECT * FROM  v$sqlarea t WHERE t.SQL_ID = '56a5h016rxdx8';

发现对应的SQL没有按照索引执行，执行对应表的分析表和索引命令进行优化。

以下为网上查询到的信息

分析表

analyze table tablename compute statistics;

分析索引

analyze index indexname compute statistics;

该语句生成的统计信息会更新user_tables这个视图的统计信息,分析的结果被Oracle用于基于成本的优化生成更好的查询计划

对于使用CBO(Cost-Base Optimization)很有好处，可以使用更可靠的table信息，从而执行计划也可以更准确一些，在10g会自动analyze，之前的版本需要手动定期

analyze table 一般可以指定分析：表，所有字段，所有索引字段，所有索引。若不指定则全部都分析。

analyze table my_table compute statistics;  

analyze table my_table compute statistics for table for all indexes for all columns;   

analyze table my_table compute statistics for table for all indexes for all indexed columns;

analyze table my_table compute statistics;  
等价于：
analyze table my_table compute statistics for table for all indexes for all columns;

例子：

analyze table t1 compute statistics for table;
analyze table t2 compute statistics for all columns;
analyze table t3 compute statistics for all indexed columns;
analyze table t5 compute statistics for all indexes; 
analyze table t4 compute statistics;

另外，可以删除分析数据：

analyze table my_table delete statistics;

analyze table my_table delete statistics for table for all indexes for all indexed columns;