oracledbms_stats统计信息管理(六)
中，
出现这种情形的机率相称小。使用 CBO时，最罕见的过失之一就是在CBO统计中不用要地引入直方图。根据经验，
只需在列值请求必需修改施行计划时，才应使用直方图。
为了智能地生成直方图，Oracle为dbms_stats准备了method_opt参数。在method_opt子句中，还有一些首要的
新选项，包含skewonly，repeat和auto：
method_opt=>'for all columns size skewonly'
method_opt=>'for all columns size repeat'
method_opt=>'for all columns size auto'
----案例1
begin
dbms_stats.gather_schema_stats(
ownname => 'JINRILOG',
estimate_percent => 100,
method_opt => 'for all indexed columns'
);
end;
含义解释 ownname:填写需要分析的用户（该用户下所有表都将被分析）
estimate_percent:分析抽样的力度
cascade:是否对索引进行分析
---案例2
Exec dbms_stats.gather_schema_stats
(
ownname => 'JINRILOG',
options => 'GATHER AUTO',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all indexed columns '
) ;
------------3.GATHER_INDEX_STATS
/*******************
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);
*********/
begin
dbms_stats.gather_index_stats (
ownname => 'SCOTT',
indname =>'INDEX_01',
estimate_percent => 100,
degree => 2
);
end;
/
@sosi.txt
***********
Table Level
***********
Table Number Empty Average Chain Average Global User Sample Date
Name of Rows Blocks Blocks Space Count Row Len Stats Stats Size MM-DD-YYYY
--------------- ------------------ -------- ------------ ------- -------- ------- ------ ------ ------------------ ----------
TT NO NO
Column Column Distinct Number Number Global User Sample Date
Name Details Values Density Buckets Nulls Stats Stats Size MM-DD-YYYY
------------------------- ------------------------ ------------ ------- ------- ---------- ------ ------ ------------------ ----------
OWNER VARCHAR2(30) NO NO
OBJECT_NAME VARCHAR2(128) NO NO
SUBOBJECT_NAME VARCHAR2(30) NO NO
OBJECT_ID NUMBER(22) NO NO
DATA_OBJECT_ID NUMBER(22) NO NO
OBJECT_TYPE VARCHAR2(19) NO NO
CREATED DATE NO NO
LAST_DDL_TIME DATE NO NO
TIMESTAMP VARCHAR2(19) NO NO
STATUS VARCHAR2(7) NO NO
TEMPORARY VARCHAR2(1) NO NO
GENERATED VARCHAR2(1) NO NO
SECONDARY VARCHAR2(1) NO NO
NAMESPACE NUMBER(22) NO NO
EDITION_NAME VARCHAR2(30) NO NO
B Average Average
Index Tree Leaf Distinct Number Leaf Blocks Data Blocks Cluster Global User Sample
Name Unique Level Blks Keys of Rows Per Key Per Key Factor Stats Stats Size
--------------- --------- ----- ---- -------------- ------------------ ----------- ----------- ------------ ------ ------ ------------------
Date
MM-DD-YYYY
----------
INDEX_01 NONUNIQUE 1 166 74,908 74,908 1 1 1,177 YES NO 74,908
12-25-2013
Index Column Col Column
Name Name Pos Details
--------------- ------------------------- ---- ------------------------
INDEX_01 OBJE