表的统计信息

 

 

col table_name for a30
col owner for a20
col column_name for a30
col index_name for a30

##查询表的统计信息
select t.owner,t.TABLE_NAME,t.NUM_ROWS,t.BLOCKS,t.LAST_ANALYZED from dba_tables t where table_name='&1';

 

##表的大小
select SEGMENT_NAME,BYTES/1024/1024/1024 from dba_segments where segment_name='&1';

##查询某一个列的统计信息
select TABLE_NAME,COLUMN_NAME,NULLABLE,NUM_DISTINCT,NUM_NULLS from dba_tab_columns where table_name='&1' and column_name ='&2';

##查询表的索引信息
select INDEX_NAME,TABLE_NAME,COLUMN_NAME,COLUMN_POSITION from dba_ind_columns where table_name='&1';
select table_name,index_name,t.blevel,t.num_rows,t.leaf_blocks,t.last_analyzed,t.STATUS from dba_indexes t where table_name='&1';

##收集表的统计信息
exec dbms_stats.gather_table_stats('STUDENT','COURSE');

##查询表上的DML操作
select INSERTS,UPDATES,DELETES,TIMESTAMP,TRUNCATED from dba_tab_modifications where table_name='&1';

select dbms_metadata.get_ddl('TABLE','CCT_USERDATAUSE_INFO','DBACCADM') from dual;

##查询统计信息的收集时间（默认周一到周五每天22点，持续4小时。周末06点开始，持续20小时）
set linesize 200
col WINDOW_NAME for a20
col REPEAT_INTERVAL for a60
col DURATION for a30
select t1.window_name,t1.repeat_interval,t1.duration from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2
where t1.window_name=t2.window_name and t2.window_group_name in ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED');

##关闭自动统计信息收集
BEGIN
DBMS_SCHEDULER.DISABLE(
name => '"SYS"."SATURDAY_WINDOW"',
force => TRUE);
END;
/

##修改自动统计信息持续时间
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"SYS"."SATURDAY_WINDOW"',
attribute => 'DURATION',
value => numtodsinterval(240,'minute'));
END;
/

##修改自动统计信息开始时间
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"SYS"."SATURDAY_WINDOW"',
attribute => 'REPEAT_INTERVAL',
value => 'freq=daily;byday=SAT;byhour=22;byminute=0; bysecond=0 ');
END;
/

##开启自动统计信息收集
BEGIN
DBMS_SCHEDULER.ENABLE(
name => '"SYS"."SATURDAY_WINDOW"');
END;
/

##手动收集统计信息
exec dbms_stats.gather_table_stats(ownname => 'USER',tabname => 'TEST',estimate_percent => 10,method_opt=> 'for all indexed columns');

exec dbms_stats.gather_table_stats(ownname => 'USER',tabname => 'TAB_NAME',CASCADE=>TURE);
##强制收集统计信息
force=>'TRUE'

##收集分区表的某个分区统计信息
exec dbms_stats.gather_table_stats(ownname => 'USER',tabname => 'RANGE_PART_TAB',partname => 'p_201312',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE);

##收集索引统计信息
exec dbms_stats.gather_index_stats(ownname => 'USER',indname => 'IDX_OBJECT_ID',estimate_percent => '10',degree => '4');

##收集表和索引统计信息
exec dbms_stats.gather_table_stats(ownname => 'USER',tabname => 'TEST',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE);

##收集分区表的统计信息
exec dbms_stats.gather_table_stats(ownname => 'USER',tabname => 'TEST',estimate_percent => 10,method_opt=> 'for all indexed columns',granularity => 'ALL',cascade=>TRUE);

对一个分区表收集统计信息
BEGIN
???DBMS_STATS.GATHER_TABLE_STATS(ownname =>?'ROBINSON',
?????????????????????????????????tabname =>?'P_TEST',
?????????????????????????????????estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
?????????????????????????????????method_opt =>?'for all columns size repeat',
?????????????????????????????????degree?=> DBMS_STATS.AUTO_DEGREE,
?????????????????????????????????granularity =>?'ALL',
?????????????????????????????????cascade=>TRUE
?????????????????????????????????);
END;
/
上面的例子收集ROBINSON.P_TEST表的统计信息。里面值得注意的一个参数就是granularity，他有7个选项。
granularity => 'ALL'??收集分区，子分区，全局的统计信息
granularity => 'AUTO'?这个是默认的设置，ORACLE会根据分区类型来决定用ALL,GLOBAL AND PARTITION ,还是其他的
granularity => 'DEFAULT'?这个是过期了的
granularity => 'GLOBAL'?收集全局统计信息
granularity => 'GLOBAL AND PARTITION'?收集全局，分区统计信息，但是不收集子分区统计信息
granularity => 'PARTITION'?收集分区统计信息
granularity => 'SUBPARTITION'?收集子分区统计信息
当然我们可以指定partname，自己控制对哪个分区收集统计信息

 

##收集整个数据库的统计信息
exec dbms_stats.gather_database_stats(estimate_percent=>10,degree=>8,cascade=>true,granularity=>'ALL');

ownname： USER_NAME
tabname： TABLE_NAME
partname: 分区表的某个分区名
estimate_percent: 采样百分比，有效范围为[0.000001,100]
block_sample：使用随机块采样代替随机行采样
method_opt：
cascade:是否收集此表索引的统计信息
degree:并行处理的cpu数量
granularity： 统计数据的收集，'ALL' - 收集所有（子分区，分区和全局）统计信息

##统计信息解锁
##查询表的统计信息是否加锁
select table_name,stattype_locked from dba_tab_statistics where stattype_locked is not null and table_name='&1';

##解锁表的统计信息
execute dbms_statS.unlock_TABLE_stats(ownname=>'&1',TABNAME=>'&2');

##表统计信息手动加锁
execute dbms_stats.lock_TABLE_stats(ownname=>'&1',TABNAME=>'&2');

##查看统计信息是否过期
select owner,table_name ,object_type,stale_stats,partition_name,last_analyzed from dba_tab_statistics where owner='&owner' and table_name='&table_name' and (stale_stats='YES' or last_analyzed is null);

昨天我从9i R2里导出了几张表，然后导入到11g R2中，在导入成功后我要收集下这些表的信息，结果发现好几张表都没法收集，
用DBMS_STATS包显示ORA-20005：object statistics are locked (stattype = ALL)，用Analyze命令显示ORA-38029： 对象统计信息已锁定。

解决办法很明确，就是解锁。
可以从两个层面去处理：
A、解锁Schema
DBMS_STATS.UNLOCK_schema_STATS(user);

B、解锁单个对象
1)先查出被锁定的表select table_name from user_tab_statistics where stattype_locked is not null;
然后再解锁对象
exec dbms_stats.unlock_table_stats(user,'表名');
2)也可直接生成sql脚本
select 'exec dbms_stats.unlock_table_stats('''||user||''','''||table_name||''');' from user_tab_statistics where stattype_locked is not null;
这里不在生成的sql中用动态的user是为了让执行者明确知道到底是解锁哪个schema下的表，防止误操作。

不过，你要特别注意，Oracle为什么会要锁定住统计信息？
一般而言，这是为了稳定执行计划，因为在Oracle 10g以上，Oracle默认会自动收集统计信息，要想锁住统计信息，请使用LOCK_SCHEMA_STATS、LOCK_TABLE_STATS包。