CBO时代常用知识和命令【不断完善】

一.先看一个例子

exec dbms_stats.gather_table_stats(user,'SYS_KS_LOG',cascade=>true,degree=>28,method_opt=>'for all indexed columns');

意思是:收集表+相关索引信息，并发度为28，方法是收集索引列的直方图信息，其中直方图对于索引列数据的倾斜是很有用的，

打个比方：列col1

a: 20条

b:200000条

当使用where col=**时候，ORACLE很可能会选择错误的执行计划，即使刚刚分析了表和相关索引，而直方图可以解决此类问题；

二.下面完善下理论知识

包dbms_stat

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')),  --CPU的并行度
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')),
force BOOLEAN DEFAULT FALSE); --强制对LOCK表进行搜集

除了owner,tabname外其它的都有默认值，想查默认值可使用以下SQL

select dbms_stats.get_param('method_opt') from dual;

三.参数

*estimate_percent

范围valid range is [0.000001,100]

默认值

select dbms_stats.get_param('estimate_percent') from dual;

DBMS_STATS.AUTO_SAMPLE_SIZE

* method _option

先看下官档说明

method_opt Accepts:
■ FOR ALL [INDEXED | HIDDEN] COLUMNS [size_
clause]

比如.FOR ALL INDEXED COLUMNS AUTO(其中auto可不写，默认)

比如.FOR ALL HIDDEN COLUMNS

■ FOR COLUMNS [size clause] column|attribute
[size_clause] [,column|attribute [size_
clause]...]

比如.FOR COLUMNS IDX_COL1 ;
size_clause is defined as size_clause := SIZE
{integer | REPEAT | AUTO | SKEWONLY}
- integer : Number of histogram buckets. Must be in the
range [1,254].
- REPEAT : Collects histograms only on the columns that
already have histograms.
- AUTO : Oracle determines the columns to collect histograms
based on data distribution and the workload of the columns.
- SKEWONLY : Oracle determines the columns to collect
histograms based on the data distribution of the columns.
The default is FOR ALL COLUMNS SIZE AUTO.The default
value can be changed using the SET_PARAM Procedure.

翻译下:

所有列:for all columns --也是默认的 auto是默认值

索引列:for all indexed columns

隐藏列:for all hidden columns

指定列:for columns column_name

*degree

SQL> select dbms_stats.get_param('degree') from dual;

DBMS_STATS.GET_PARAM('DEGREE')
------------------------------------------------------------------------------------------------------------------------
NULL

1.如果是NULL值，那么它将按表的DEGREE属性值进行

2.可以批定数值，show parameter cpu,根据CPU数来指，可以加快速度，但耗资源

3.default_degree，ORACLE将根据系统默认参进行设定

 * granularity  --这个参数对OLAP系统会比较有用

1.all,将会对分区表的全局，分区，子分区都进行分析；

2.auto，oracle将会根据分区类型自动决定哪种粒度分析；

3.GLOBAL,只做全局分析

4.GLOBAL AND PARTITION,对全局和分区做分析但不包含子分区；

5.PARTITION,只对分区

6.SUBPARTITION,只对子分区

*cascade

我们经常看到这个参数设为cascade=>true

这个表示表和索引一起分析

四.gather_schema_stats

先看下官档说明

GATHER_SCHEMA_STATS Procedures
This procedure gathers statistics for all objects in a schema.
Syntax
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,----List of objects found to be stale or empty
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);

看下这个option参数

options Further specification of which objects to gather statistics for:
GATHER: Gathers statistics on all objects in the schema.

收集所有的对象
GATHER AUTO: Gathers all necessary statistics automatically.
Oracle implicitly determines which objects need new statistics,
and determines how to gather those statistics. When GATHER
AUTO is specified, the only additional valid parameters are
ownname, stattab, statid, objlist and statown; all
other parameter settings are ignored. Returns a list of
processed objects.
GATHER STALE: Gathers statistics on stale objects as
determined by looking at the *_tab_modifications views.
Also, return a list of objects found to be stale.
GATHER EMPTY: Gathers statistics on objects which currently
have no statistics. also, return a list of objects found to have no
statistics.
LIST AUTO: Returns a list of objects to be processed with
GATHER AUTO.
LIST STALE: Returns list of stale objects as determined by
looking at the *_tab_modifications views.
LIST EMPTY: Returns list of objects which currently have no
statistics.

五。DBMS_STATS.GATHER_INDEX_STATS

GATHER_INDEX_STATS Procedure
This procedure gathers index statistics. It attempts to parallelize as much of the work
as possible. Restrictions are described in the individual parameters. This operation will
not parallelize with certain types of indexes, including cluster indexes, domain
indexes, and bitmap join indexes. The granularity and no_invalidate
arguments are not relevant to these types of indexes.
Syntax
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);

六.获取分析数据

dbms_stats.get_column_stats

dbms_stats.get_index_stats

dbms_stats.get_table_stats

dbms_stats.get_system_stats

七。设置分析数据

dbms_stats.set_column_stats

dbms_stats.set_index_stats

dbms_stats.set_system_stats

dbms_stats.set_table_stats

八。删除分析数据

DBMS_STATS.DELETE_COLUMN_STATS

DBMS_STATS.DELETE_DATABASE_STATS

DBMS_STATS.DELETE_DICTIONARY_STATS

DBMS_STATS.DELETE_FIXED_OBJECTS_STATS

DBMS_STATS.DELETE_INDEX_STATS

DBMS_STATS.DELETE_SCHEMA_STATS

DBMS_STATS.DELETE_SYSTEM_STATS

DBMS_STATS.DELETE_TABLE_STATS

九。保存分析数据

DBMS_STATS.CREATE_STAT_TABLE

DBMS_STATS.DROP_STAT_TABLE

十。导入和导出分析数据

EXPORT_COLUMN_STATS Procedure

EXPORT_DATABASE_STATS Procedure

EXPORT_DICTIONARY_STATS Procedure

EXPORT_FIXED_OBJECTS_STATS Procedure

EXPORT_INDEX_STATS Procedure

EXPORT_SCHEMA_STATS Procedure

EXPORT_SYSTEM_STATS Procedure

EXPORT_TABLE_STATS Procedure

IMPORT_COLUMN_STATS Procedure

IMPORT_DATABASE_STATS Procedure

IMPORT_DICTIONARY_STATS Procedure

IMPORT_FIXED_OBJECTS_STATS Procedure

IMPORT_INDEX_STATS Procedure

IMPORT_SCHEMA_STATS Procedure

IMPORT_SYSTEM_STATS Procedure

IMPORT_TABLE_STATS Procedure

十一。锁定分析数据

LOCK_SCHEMA_STATS Procedure

LOCK_TABLE_STATS Procedure

UNLOCK_SCHEMA_STATS Procedure

UNLOCK_TABLE_STATS Procedure

十二。分析数据的恢复

RESET_PARAM_DEFAULTS Procedure

RESTORE_DATABASE_STATS Procedure

RESTORE_DICTIONARY_STATS Procedure

RESTORE_FIXED_OBJECTS_STATS Procedure

RESTORE_SCHEMA_STATS Procedure

RESTORE_SYSTEM_STATS Procedure

RESTORE_TABLE_STATS Procedure

十三。动态采样