一、统计信息查看
官方文档:
Optimizer Statistics Concepts (oracle.com)
1.1 表统计信息查看
SELECT OWNER,
TABLE_NAME,
PARTITION_NAME,
OBJECT_TYPE,
NUM_ROWS,
BLOCKS,
EMPTY_BLOCKS,
AVG_SPACE,
CHAIN_CNT,
AVG_ROW_LEN,
to_char(LAST_ANALYZED, 'yyyy-mm-dd hh24:mi:ss') LAST_ANALYZED,
USER_STATS
FROM DBA_TAB_STATISTICS
WHERE OWNER='CMXBUSI'
AND TABLE_NAME = 'T02';
1.2 索引统计信息查看
SELECT INDEX_NAME AS NAME,
BLEVEL,
LEAF_BLOCKS AS LEAF_BLKS,
DISTINCT_KEYS AS DST_KEYS,
NUM_ROWS,
CLUSTERING_FACTOR AS CLUST_FACT,
AVG_LEAF_BLOCKS_PER_KEY AS LEAF_PER_KEY,
AVG_DATA_BLOCKS_PER_KEY AS DATA_PER_KEY,
LAST_ANALYZED
FROM DBA_IND_STATISTICS where
TABLE_OWNER='CMXBUSI' AND
TABLE_NAME='T02';
select table_name,index_name,leaf_blocks,blevel,distinct_keys,avg_leaf_blocks_per_key,avg_data_blocks_per_key,clustering_factor,num_rows from dba_indexes where owner = 'SCOTT' and table_name = 'T1';
1.3 列统计信息查看
方法一:
SELECT COLUMN_NAME,
NUM_DISTINCT,
LOW_VALUE,
HIGH_VALUE,
DENSITY ,
NUM_NULLS ,
AVG_COL_LEN ,
HISTOGRAM,
NUM_BUCKETS
FROM DBA_TAB_COL_STATISTICS
WHERE OWNER='CMXBUSI'
AND TABLE_NAME = 'T02';
方法二:
a.owner ||'.'||a.table_name name ,
a.column_name,
b.num_rows,
a.num_distinct Cardinality,
a.num_distinct/b.num_rows selectivity,
num_nulls,density,
a.histogram,
a.num_buckets
from dba_tab_col_statistics a,dba_tables b
where a.owner=b.owner
and a.table_name=b.table_name
and a.owner=upper('CMXBUSI')
and a.table_name=upper('T02')
and a.column_name=upper('ID');
方法三:
select table_name,column_name,num_distinct,low_value,high_value,histogram,density from dba_tab_columns where owner = 'SCOTT' and table_name = 'T1';
二、DBMS_STATS收集统计信息
dbms_stas 包不仅能够对表进行分析,它还可以对数据库分析进行管理。 按照功能可以分一下几类:
(1) 性能数据的收集
(2) 性能数据的设置
(3) 性能数据的删除
(4) 性能数据的备份和恢复
1.性能数据的收集
这个包的下面四个存储过程分别收集 index、table、schema、database 的统计信息:
dbms_stats.gather_table_stats 收集表、列和索引的统计信息(当 cascade 为true 时,分析表、列(索引)信息);
dbms_stats.gather_schema_stats 收集 schema 下所有对象的统计信息;
dbms_stats.gather_index_stats 收集索引的统计信息;
dbms_stats.gather_system_stats 收集系统统计信息
dbms_stats.gather_dictionary_stats: 所有字典对象的统计;
dbms_stats.GATHER_DATABASE_STATS:分析数据库信息
dbms_stats.create_stat_table 创建存放统计信息表
dbms_stats.auto_sample_size 采样值
2.性能数据的设置
设置表统计信息:dbms_stats.set_table_stats
设置索引统计信息:dbms_stats.set_index_stats
设置列统计信息:dbms_stats.set_column_stats
3.性能数据的删除
删除数据库统计信息:dbms_stats.delete_database_stats
删除用户方案统计信息:dbms_stats.delete_schema_stats
删除表统计信息:dbms_stats.delete_table_stats
删除索引统计信息:dbms_stats.delete_index_stats
删除列统计信息:dbms_stats.delete_column_stats
4.性能数据的备份和恢复
dbms_stats.EXPORT_COLUMN_STATS:导出列的分析信息
dbms_stats.EXPORT_INDEX_STATS:导出索引分析信息
dbms_stats.EXPORT_SYSTEM_STATS:导出系统分析信息
dbms_stats.EXPORT_TABLE_STATS:导出表分析信息
dbms_stats.EXPORT_SCHEMA_STATS:导出方案分析信息
dbms_stats.EXPORT_DATABASE_STATS:导出数据库分析信息
dbms_stats.IMPORT_COLUMN_STATS:导入列分析信息
dbms_stats.IMPORT_INDEX_STATS:导入索引分析信息
dbms_stats.IMPORT_SYSTEM_STATS:导入系统分析信息
dbms_stats.IMPORT_TABLE_STATS:导入表分析信息
dbms_stats.IMPORT_SCHEMA_STATS:导入方案分析信息
dbms_stats.IMPORT_DATABASE_STATS:导入数据库分析信息
2.1 收集统计信息步骤
1.检查统计信息
set linesize 300;
SELECT OWNER,TABLE_NAME,NUM_ROWS,LAST_ANALYZED FROM dba_tables where TABLE_NAME in (
'T_S_TYPE',
'T_EDF_TASK'
);
2.针对运行效率慢的SQL情况,按表收集统计信息:
sqlplus / as sysdba <<EOF
exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '用户名',TABNAME => '表名' ,ESTIMATE_PERCENT => 0.1 ,METHOD_OPT => 'FOR ALL INDEXED COLUMNS' ,CASCADE => TRUE,DEGREE => 30);
EOF
ps:ESTIMATE_PERCENT => 0.1 为采样比例
3.针对运行效率慢的SQL情况,按用户收集统计信息:
sqlplus / as sysdba <<EOF
exec DBMS_STATS.GATHER_schema_STATS(OWNNAME => 'CCICJY' ,ESTIMATE_PERCENT => 0.1 ,METHOD_OPT => 'FOR ALL INDEXED COLUMNS' ,CASCADE => TRUE,DEGREE => 30);
EOF
4.查看统计信息是否执行
select * from dba_scheduler_jobs;
select * from dba_scheduler_jobs WHERE JOB_NAME='GATHER_STATS_JOB';
2.2 DBMS_STATS.GATHER_TABLE_STATS使用详解
DBMS_STATS.GATHER_TABLE_STATS的语法如下:
DBMS_STATS.GATHER_TABLE_STATS (ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2, estimate_percent NUMBER, block_sample BOOLEAN, method_opt VARCHAR2, degree NUMBER, granularity VARCHAR2, cascade BOOLEAN, stattab VARCHAR2, statid VARCHAR2, statown VARCHAR2, no_invalidate BOOLEAN, force BOOLEAN);
参数说明:
1.ownname:要分析表的拥有者
2.tabname:要分析的表名.
3.partname:分区的名字,只对分区表或分区索引有用.
4.estimate_percent:采样行的百分比,取值范围[0.000001,100],null为全部分析,不采样.
常量:DBMS_STATS.AUTO_SAMPLE_SIZE是默认值,由oracle决定最佳取采样值.
5.block_sapmple:是否用块采样代替行采样.
6.method_opt:决定histograms信息是怎样被统计的(抽样方法)
method_opt的取值如下(默认值为FOR ALL COLUMNS SIZE AUTO):
for table:只统计表
for all columns:分析所有的列
for all indexes:只分析统计相关索引
for all indexed columns:只统计有索引的表列
for all hidden columns:分析所有隐藏列(函数索引等
for columns <list> SIZE <N> | REPEAT | AUTO | SKEWONLY:统计指定列的histograms.
N的取值范围[1,254];
REPEAT:上次统计过的histograms;
AUTO:由oracle决定N的大小;
skewonly:Oracle 确定需要收集检查每个索引中每列值的分布
例如:
method_opt=>'for all columns size skewonly'
method_opt=>'for all columns size repeat'
method_opt=>'for all columns size auto'
7.degree:决定并行度.默认值为null.
分析索引速度加快,根据 cpu 数量来设置,一般在业务空闲的时候 degree 可设为 cpu 数量-1,繁忙的时候就再小点。
8.granularity:要收集的统计信息的粒度(仅在表已分区时才相关)
根据将优化的 SQL语句,优化器可以选择使用分区统计或全局统计,对于大多数系统这两种统计都是很
重要的,Oracle 推荐将 GRANULARITY 设置为 AUTO 同时收集全部信息。
①'AUTO'-根据分区类型确定粒度。这是默认值。
②'ALL' -收集所有(子分区,分区和全局)统计信息
③'GLOBAL' -收集全球统计数据
④'GLOBAL AND PARTITION'-收集全局和分区级别的统计信息。即使它是一个复合分区对象,也不会收集任何子分区级别统计信息。
⑤'PARTITION '-收集分区级别的统计信息
⑥'SUBPARTITION' -收集子分区级别的统计信息
⑦'DEFAULT'-收集全局和分区级别的统计信息。该选项已过时,并且当前受支持,但仅出于遗留原因才包含在文档中。您应该使用“ GLOBAL AND PARTITION”来实现此功能。请注意,默认值现在是'AUTAUTO'。
9.cascade:是收集索引的信息.默认为FALSE.
10.stattab:用户统计信息表标识符,用于描述将当前统计信息保存在何处
statid如果多个表的统计信息存储在同一个stattab中用于进行区分,标识符
statown存储统计信息表的拥有者.包含的架构stattab(如果不同于ownname)
以上三个参数若不指定,统计信息会直接更新到数据字典.
11.no_invalidate: 通过不同的参数配置,可以实现对 Oracle 失效共享游标行为的控制TRUE,FALSE
如果取值为 true,表示不进行游标失效动作,原有的 shared cursor 保持原有状态。
如果取值为 false,表示将统计量对象相关的所有 cursor 全部失效。
如果设置为auto_invalidate,根据官方文档,Oracle 自己决定 shared cursor 失效动作。
从 10G 开始,Oracle 就将 auto_invalidate 作为默认的统计量收集行为。
select dbms_stats.get_param(pname => 'no_invalidate') from dual;
12.force:即使表锁住了也收集统计信息.
13.options 分析模式
使用 4 个预设的法子之一,这个选项能把握 Oracle 统计的刷新方法:
gather——重新分析整个(Schema)。
gather empty——只分析目前还没有统计的表。
gather stale——只重新分析修改量超过 10%的表(这些修改包含插入、更新和删除)。
gather auto——重新分析以前没有统计的对象,以及统计数据过期(变脏)的对象。
注意,使用 gather auto 相似于组合使用 gather stale 和 gather empty。注意,不论 gather stale 仍是 gather auto,都请求进行监视。假如你施行一个alter table xxx monitoring 命令,Oracle 会用 dba_tab_modifications 视图来跟踪发生变动的表。这样一来,你就确实地知道,自从上一次剖析统计数据以来,发生了多少次插入、更新和删除操作。
14.objlist: 指定对象列表
15.obj_filter_list:A list of object filters
16.gather_sys:只收集 sys 的对象。
17.stattype:统计信息类型。允许的唯一值是DATA。
2.3 收集直方图
直方图收集方法
1. 语法格式
对于dbms_stats包,是通过指定method_opt参数实现的,该参数可接受值如下:
1.1统计所有列的histograms(直方图).
for all [indexed | hidden] columns [size_clause]
1.2统计指定列的histograms(直方图).
for columns [size_clause] column | attribute [size_clause] [,column | attribute [size_clause]...]
例如:
for columns column size 1
for columns column size auto
其中size_clause必须符合以下格式:size [整数值 | repeat | auto | skewonly]
整数值:直方图的Bucket数,范围为[1,254],为1表示删除列上直方图信息
repeat:只对已有直方图的列收集直方图信息
auto:由oracle决定是否对列收集直方图,以及使用哪类直方图。oracle默认只对用过的列(where条件中出现过的列)自动收集直方图统计信息,oracle会在sys.col_usage$基表中记录各列的使用情况。在自动收集直方图统计信息时先查该表,如果列未被使用过,则不会收集。
skewonly:只对倾斜列收集直方图
2.距离
2.1 对表所有列以auto方式收集直方图
exec dbms_stats.gather_table_stats(ownname => 'USER01',tabname => 'TAB01',method_opt=> 'for all columns size auto');
2.2 对表所有有索引的列以auto方式收集直方图
exec dbms_stats.gather_table_stats(ownname => 'USER01',tabname => 'TAB01',method_opt=> 'for all indexed columns size auto');
2.3 对表的empno和deptno列以auto方式收集直方图
exec dbms_stats.gather_table_stats(ownname => 'USER01',tabname => 'TAB01',method_opt=> 'for columns size auto empno deptno');
2.4 对表的empno和deptno列收集直方图,并指定Bucket数为10
exec dbms_stats.gather_table_stats(ownname => 'USER01',tabname => 'TAB01',method_opt=> 'for columns size 10 empno deptno');
2.5 对表的empno和deptno列收集直方图,指定empno列Bucket数为10,deptno列Bucket数为5
exec dbms_stats.gather_table_stats(ownname => 'USER01',tabname => 'TAB01',method_opt=> 'for columns empno size 10 deptno size 5')
3.查询直方图
SELECT COLUMN_NAME,NOTES,HISTOGRAM FROM DBA_TAB_COL_STATISTICS WHERE TABLE_NAME = 'SALES2';
select * from user_histograms;
2.4 收集数据库统计信息
gather_database_stats 收集数据库级别的统计信息
GATHER_DATABASE_STATS 语法与参数
DBMS_STATS.GATHER_DATABASE_STATS (
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 DEFAULTto_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,
gather_sys BOOLEAN DEFAULT TRUE,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')),
obj_filter_list ObjectTab DEFAULT NULL);
gather_database_stats 测试:
SQL>exec DBMS_STATS.gather_database_stats;
SQL>exec DBMS_STATS.gather_database_stats(estimate_percent => 15);
SQL>exec DBMS_STATS.GATHER_DATABASE_STATS(estimate_percent=>10,Degree=>8, Cascade=>TRUE, Granularity=>'ALL');
过程:
SQL> exec DBMS_STATS.gather_database_stats;
PL/SQL procedure successfully completed
查看进程
SQL> select distinct sid, serial# from v$session_longops where opname like'%Gather%';
SID SERIAL#
---------- ----------
459 20726
select distinct sid, serial# from v$session_longops where opname like'%gather_database_stats%';
如果要终止:
SQL> alter system kill session '459,20726';
System altered
2.5 收集用户统计信息
1)、GATHER_SCHEMA_STATS 语法与参数
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 DEFAULTto_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN DEFAULTto_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);
GATHER_SCHEMA_STATS
2.5.2 重新收集用户所有的统计信息
begin
dbms_stats.gather_schema_stats(ownname => 'CMXBUSI',
options => 'gather',
estimate_percent => 100,
method_opt => 'for all indexed columns',
degree => 2);
end;
/
2.5.3 重新收集用户以前没有统计的对象
begin
dbms_stats.gather_schema_stats(ownname => 'CMXBUSI',
options => 'gather auto',
estimate_percent =>dbms_stats.AUTO_SAMPLE_SIZE,
method_opt => 'for all columns size auto',
cascade => true,
degree => 2);
end;
/
2.5.4 收集用户分区表和索引信息
begin
dbms_stats.gather_schema_stats(ownname => 'CMXBUSI',
estimate_percent => 10,
cascade => true,
granularity => 'all',
degree => 2);
end;
/
2.6 收集表的统计信息
收集统计信息的方法之 gather_table_stats
1)统计信息的函数 dbms_stats.gather_table_stats 介绍:
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 defaultto_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);
2)gather_table_stats 测试:
–查看表的统计信息
SQL>select table_name, blocks, empty_blocks, num_rows from SQL>dba_tables where owner = 'SCOTT' and table_name = 'T1';
SQL>select table_name, blocks, empty_blocks, num_rows,LAST_ANALYZED fromdba_tables where owner = 'SCOTT' and table_name = 'T1';
SQL>select table_name,sample_size,num_rows,round(sample_size/num_rows*100,1)"%" from dba_tables where owner = 'SCOTT' and table_name = 'T1';
SQL>select table_name,sample_size,num_rows,round(sample_size/num_rows*100,1)"%" from dba_tables where owner = 'SCOTT' order by table_name;
–查看表列的统计信息
SQL>select table_name,column_name,last_analyzed,num_distinct,low_value,high_value,histogram,density from dba_tab_columns where owner = 'SCOTT' and table_name ='T1';
–查看表索引的统计信息
SQL>select table_name,index_name,last_analyzed,leaf_blocks,blevel,distinct_keys,avg_leaf_blocks_per_key,avg_data_blocks_per_key,clustering_factor,num_rows fromdba_indexes where owner = 'SCOTT' and table_name = 'T1';
–情形1:收集表,包括表的所有列和索引的统计信息
SQL>analyze table ITPUX_M10 delete statistics;
SQL>begin
>dbms_stats.gather_table_stats(ownname => 'SCOTT',
>tabname => 'T1',
>estimate_percent =>dbms_stats.AUTO_SAMPLE_SIZE,
>method_opt => 'for all indexed columns',
>cascade => true,
>degree => 2);
>end;
>/
–情形2:只收集表的统计信息,采样比例为 15%
SQL>analyze table ITPUX_M10 delete statistics;
SQL>begin
>dbms_stats.gather_table_stats(ownname => 'SCOTT',
>tabname => 'T1',
>estimate_percent => 15,
>method_opt => 'for table',
>cascade => false,
>degree => 2);
>end;
>/
–情形3:只收集表的统计信息,其中两列的信息不收集直方图
SQL>analyze table T1 delete statistics;
SQL>begin
>dbms_stats.gather_table_stats(ownname => 'SCOTT',
>tabname => 'T1',
>estimate_percent => 100,
>method_opt => 'for columns size 1 object_name status',
>cascade => false,
>degree => 2);
>end;
>/
–情形4:只收集表的信息,表所有列以及表所有索引的统计信息
SQL>begin
>dbms_stats.gather_table_stats(ownname => 'SCOTT',
>tabname => 'T1',
>estimate_percent => 100,
>cascade => true,
>degree => 2);
>end;
>/
–情形5:(包括表,分区表,子分区)
SQL>begin
>dbms_stats.gather_table_stats(ownname => 'SCOTT',
>tabname => 'T1',
>estimate_percent => 18,
>cascade => true,
>granularity => 'ALL', --全局的表,分区表,子分区表
>degree => 2);
>end;
>/
–情形6:分区级的统计信息收集
SQL>begin
>dbms_stats.gather_table_stats(ownname => 'SCOTT',
>tabname => 'T1',
>partname => '分区名',
>estimate_percent => 5,
>cascade => true,
>granularity => 'PARTITION',
>method_opt => 'for all indexes columns',
>degree => 2);
>end;
>/
–情形7:删除表的统计信息
SQL>begin
>dbms_stats.delete_table_stats (ownname => 'SCOTT',tabname >=>'T1');
>end;
>/
2.7 收集index 的统计信息
1)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);
2.8 数据字典表收集
GATHER_DICTIONARY_STATS:
DBMS_STATS.GATHER_DICTIONARY_STATS (
comp_id 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,
options VARCHAR2 DEFAULT 'GATHER AUTO',
objlist OUT ObjectTab,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (get_param('NO_INVALIDATE')),
obj_filter_list ObjectTab DEFAULT NULL);
SQL> exec DBMS_STATS.GATHER_DICTIONARY_STATS
(estimate_percent=>100, Degree=>8, Cascade=>TRUE, Granularity=>'ALL');
2.9 动态性能表收集
动态性能表统计信息采集GATHER_FIXED_OBJECTS_STATS
DBMS_STATS.GATHER_FIXED_OBJECTS_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')));
SQL> exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
测试:
v$lock 视图访问慢解决方法
V$ 视图访问慢–解决方法
分析:可能是有数据字典统计信息过久,造成。
SQL>exec dbms_stats.gather_fixed_objects_stats;
2.10 收集所有数据字典的fixed table 的统计信息
请勿在业务高峰期间执行。
如果需要单独对这张fixed_table 进行统计分析也可以用:
SQL>exec DBMS_STATS.GATHER_TABLE_STATS ('SYS', 'X$KSUSE');
SQL>exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'RECYCLEBIN$');
然后确认统计信息的正确性:
SQL>select count(*) from sys.tab_stats$;
SQL>select num_rows, last_analyzed from user_tab_statistics where table_name ='X$KSUSE';
关于基表的查询:
SQL>select VIEW_DEFINITION from v$fixed_view_definition where view_name='V$LOCK';
or:
SQL>select dbms_metadata.get_ddl('VIEW', 'DBA_EXTENTS') from dual;
三、analyze收集统计信息
三大功能
① 搜集和删除索引、表和簇的统计信息
② 验证表、索引和簇的结构
③ 鉴定表和簇和行迁移和行链接
3.1 表情况查询
查看表的统计信息
select table_name, blocks, empty_blocks, num_rows
from dba_tables
where owner = 'CMXBUSI'
and table_name = 'T02';
查看表列的统计信息
select table_name,
column_name,
num_distinct,
low_value,
high_value,
histogram,
density
from dba_tab_columns
where owner = 'CMXBUSI'
and table_name = 'T02';
查看表索引的统计信息
select * from dba_indexes
leaf_blocks, -- 索引中叶子块数据
blevel, -- B 树索引等级
distinct_keys, -- 不同索引值的数量
avg_leaf_blocks_per_key, -- 索引中的每个值平均在多少个叶子块中,如果是主键/唯一就是 1.
avg_data_blocks_per_key, -- 通过索引中的一个值指向表中的数据块,应数据块数量的平均值。
clustering_factor, -- 聚集因子,越小越好,越大越不好。num_rows --索引的行数量。
SELECT TABLE_NAME,INDEX_NAME,LEAF_BLOCKS,blevel,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY,AVG_DATA_BLOCKS_PER_KEY,CLUSTERING_FACTOR
from dba_indexes
WHERE TABLE_OWNER='CMXBUSI'
and table_name = 'T02';
3.2 表收集
analyze table t1 compute statistics for table;
3.3 表字段收集
analyze table T1 compute statistics for all columns;
3.4 索引字段收集
analyze table T1 compute statistics for all indexed columns;
3.5 同时收集表,表字段,索引字段
analyze table T1 compute statistics;
3.6 索引收集
analyze table T1 compute statistics for all indexes;
3.7 同时收集表,表字段,索引
analyze table T1 compute statistics for table for all indexes for all columns;
3.8 删统计信息
analyze table t1 delete statistics;
3.9 验证表,索引,分区的结构
analyze table T1 validate structure;
analyze table CMXBUSI.T01 validate structure;
四、知识总结
这是对命令与工具包的总结:
1、对于分区表,建议使用DBMS_STAT,而不是使用analyze语句。
a)可以并行进行,对多个用户,多个table
b)可以得到整个分区表的数据和单个分区的数据。
从。
c)可以在不同级别上compute statics:单个分区、子分区、全表、所有分区。
d)可以导出统计信息
e)可以用户自动收集统计信息
DBMS_STATS的缺点
a)不能validate strtucture
b)不能收集CHAINED ROWS,不能收集CLUSTER TABLE的信息,这两个仍旧需要使用analyze语句。
c)DBMS_STATS默认不对索引进行analyze,因为默认cascade是false,需要手工指定为True。