Oracle实验--查看表、索引统计信息是否收集成功

概述

统计信息对于表的执行效率不言而喻,那么我们怎么去看这个表统计信息是否收集了,最后收集的时间又是什么时候呢?

查看表的统计信息是否收集其实可通过dba_ind_statistics和dba_tab_statistics中的LAST_ANALYZED字段,观察是否完成了统计信息的收集,只有完成的收集工作才会记录在这两个视图中。

下面用实验来测试一下。

测试过程如下(测试环境为Oracle11.2.0.1):


1 、 创建一个200w行左右的测试表,数据为dba_objects。

SQL> drop table t;
Table dropped.
SQL> create table t as select * from dba_objects;
Table created.
SQL> insert into t select * from t;
87392 rows created.
SQL> /
174784 rows created.
SQL> /
349568 rows created.
SQL> /
699136 rows created.
SQL> /
1398272 rows created.
SQL> select count(*) from t;
 COUNT(*)
----------
 2796544

Oracle实验--查看表、索引统计信息是否收集成功


2、 查看视图中统计信息:

select a.OWNER,a.INDEX_NAME,a.TABLE_NAME,a.LAST_ANALYZED from dba_ind_statistics a where table_name='T' and table_owner='SCOTT';
select b.TABLE_NAME,b.OWNER,b.LAST_ANALYZED from dba_tab_statistics b where table_name='T' and owner in ('SCOTT');

Oracle实验--查看表、索引统计信息是否收集成功

通过观察得到,索引的统计信息为索引创建时自动收集,表的统计信息为空。


3 、手工收集统计信息,在执行约38秒时手工中断(已通过测试,此表收集统计信息约需要60秒)

begin
dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname => 'T' ,estimate_percent => 100 ,cascade => true);
end;
/

Oracle实验--查看表、索引统计信息是否收集成功


4 、查看视图中统计信息:

select a.OWNER,a.INDEX_NAME,a.TABLE_NAME,a.LAST_ANALYZED from dba_ind_statistics a where table_name='T' and table_owner='SCOTT';
select b.TABLE_NAME,b.OWNER,b.LAST_ANALYZED from dba_tab_statistics b where table_name='T' and owner in ('SCOTT');

Oracle实验--查看表、索引统计信息是否收集成功

因为统计信息并未收集完全,所以此二表中的数据并无变化。


5、再次收集统计信息

begin
dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname => 'T' ,estimate_percent => 100 ,cascade => true);
end;
/

Oracle实验--查看表、索引统计信息是否收集成功

可以发现分析结果出来了。


6、创建索引

SQL> create index idx_id on scott.T(object_id);
SQL> select a.OWNER,a.INDEX_NAME,a.TABLE_NAME,a.LAST_ANALYZED from dba_ind_statistics a where table_name='T' and table_owner='SCOTT';
SQL> select b.TABLE_NAME,b.OWNER,b.LAST_ANALYZED from dba_tab_statistics b where table_name='T' and owner in ('SCOTT');

Oracle实验--查看表、索引统计信息是否收集成功

可以看到一创建索引就自动收集统计信息了。

那么,为什么索引一创建会自动收集统计信息呢?


7、隐含参数_optimizer_compute_index_stats

SQL> col KSPPDESC for a80
SQL> col KSPPINM for a30
SQL> col KSPPSTVL for a20
SQL> SELECT ksppinm, ksppstvl, ksppdesc FROM x$ksppi x, x$ksppcv y WHERE x.indx = y.indx AND ksppinm='_optimizer_compute_index_stats';

Oracle实验--查看表、索引统计信息是否收集成功

可以看到隐含参数_optimizer_compute_index_stats会强制创建过引时自动收集统计信息,默认为true。


后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注一下~

Oracle实验--查看表、索引统计信息是否收集成功

相关推荐