• ORACLE-统计信息收集&&分析表和索引

    统计信息-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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25

    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


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101
    • 102
    • 103
    • 104
    • 105
    • 106
    • 107
    • 108
    • 109
    • 110
    • 111
    • 112
    • 113
    • 114
    • 115
    • 116
    • 117
    • 118
    • 119
    • 120
    • 121
    • 122
    • 123
    • 124
    • 125
    • 126
    • 127
    • 128
    • 129
    • 130
    • 131
    • 132
    • 133
    • 134
    • 135
    • 136
    • 137
    • 138
    • 139
    • 140
    • 141
    • 142
    • 143
    • 144
    • 145
    • 146
    • 147
    • 148
    • 149
    • 150
    • 151
    • 152
    • 153
    • 154
    • 155
    • 156
    • 157
    • 158
    • 159
    • 160
    • 161
    • 162
    • 163
    • 164
    • 165
    • 166
    • 167
    • 168
    • 169
    • 170
    • 171
    • 172
    • 173
    • 174
    • 175
    • 176
    • 177
    • 178
    • 179
    • 180
    • 181
    • 182
    • 183
    • 184
    • 185
    • 186
    • 187
    • 188
    • 189
    • 190
    • 191
    • 192
    • 193
    • 194
    • 195
    • 196
    • 197
    • 198
    • 199
    • 200
    • 201
    • 202
    • 203
    • 204
    • 205
    • 206
    • 207
    • 208
    • 209
    • 210
    • 211
    • 212
    • 213
    • 214
    • 215
    • 216
    • 217
    • 218
    • 219
    • 220
    • 221
    • 222
    • 223
    • 224
    • 225
    • 226
    • 227
    • 228
    • 229
    • 230
    • 231
    • 232
    • 233
    • 234
    • 235
    • 236
    • 237
    • 238
    • 239
    • 240
    • 241
    • 242
    • 243
    • 244
    • 245
    • 246
    • 247
    • 248
    • 249
    • 250
    • 251
    • 252
    • 253
    • 254
    • 255
    • 256
    • 257
    • 258
    • 259
    • 260
    • 261
    • 262
    • 263
    • 264
    • 265
    • 266
    • 267
    • 268
    • 269
    • 270
    • 271
    • 272
    • 273
    • 274
    • 275
    • 276
    • 277

    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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39

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


    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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28

    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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28

    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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31

    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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21

    2. 删除统计信息

    delete_system_stats 

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

    • 1
    • 2
    • 3
    • 4
    • 5

    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);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    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);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    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);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    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);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    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);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    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');


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    4. 备份收集信息

    export_system_stats

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

    • 1
    • 2
    • 3
    • 4
    • 5

    export_dictionary_stats

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    export_database_stats

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    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);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    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
);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    export_index_stats

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    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);


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    5. 导入收集信息

    import_system_stats

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

    • 1
    • 2
    • 3
    • 4
    • 5

    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);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    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
        );

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    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);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    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);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    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);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    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);


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

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

    SELECT TABLE_NAME, LAST_ANALYZED FROM DBA_TABLES

    • 1

    分析表和索引-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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 然后根据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';

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

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

    以下为网上查询到的信息

    • 分析表
    analyze table tablename compute statistics;

    • 1
    • 分析索引
    analyze index indexname compute statistics;

    • 1

    该语句生成的统计信息会更新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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    例子：

    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;

    • 1
    • 2
    • 3
    • 4
    • 5

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

    analyze table my_table delete statistics;

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

    • 1
    • 2
    • 3
  • 相关阅读:
    OSCP系列靶场-Esay-Moneybox保姆级
    Golang中的GC原理（介于三个不同版本）
    Oracle连接工具PLSQL登录时提示初始化失败，无法锁定OCI.dll错误解决
    【无标题】
    【Spring boot 拦截器 HandlerInterceptor】
    【算法训练-排序算法 三】【排序应用】合并区间
    使用hibernate，报出ORA-00933_ SQL 命令未正确结束
    C/C++空格分开输出 2019年12月电子学会青少年软件编程（C/C++）等级考试一级真题答案解析
    【OpenCV】仿 IOS 锁屏时钟
    使用Rust 实现文件批量下载
  • 原文地址：https://blog.csdn.net/FuTian0715/article/details/133772429