收集统计信息的目的:为了让优化器选择最佳执行计划,以最少的代价(成本)查询出表中的数据。
统计信息主要分为:
- 表的统计信息
- 列的统计信息
- 索引的统计信息
- 系统的统计信息
- 数据字典的统计信息
- 动态性能视图基表的统计信息
表的统计信息
- 包含表的总行数(num_rows)、表的块数(blocks)以及平均长度(avg_row_len)
- 通过数据字典DBA_TABLES 获取表的统计信息。
查看表的统计信息
create table t_stats as select * from dba_objects;
select owner, table_name, num_rows, blocks, avg_row_len
from dba_tables
where owner = 'SCOTT'
and table_name = 'T_STATS'
scott@orclpdb1:orclcdb> select owner, index_name, clustering_factor
2 from dba_indexes
where owner = 'SCOTT'
4 and index_name = 'IDX_ID';
OWNER INDEX_NAME CLUSTERING_FACTOR
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -----------------
SCOTT IDX_ID 1628
1 row selected.
scott@orclpdb1:orclcdb> select count(*) from dba_objects;
COUNT(*)
----------
73522
1 row selected.
scott@orclpdb1:orclcdb> create table t_stats as select * from dba_objects;
Table created.
scott@orclpdb1:orclcdb>
scott@orclpdb1:orclcdb>
scott@orclpdb1:orclcdb>
scott@orclpdb1:orclcdb>
scott@orclpdb1:orclcdb> /*收集表T_STATS统计信息*/
scott@orclpdb1:orclcdb>
BEGIN
2 DBMS_STATS.gather_table_stats(ownname => 'SCOTT',
3 tabname => 'T_STATS',
4 estimate_percent => 100,
method_opt => 'for all columns size auto',
6 no_invalidate => FALSE,
7 degree => 1,
8 cascade => TRUE);
9 END;
10 /
PL/SQL procedure successfully completed.
scott@orclpdb1:orclcdb> select owner, table_name, num_rows, blocks, avg_row_len
2 from dba_tables
where owner = 'SCOTT'
4 and table_name = 'T_STATS';
OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- -----------
SCOTT T_STATS 73522 1458 132
1 row selected.
scott@orclpdb1:orclcdb>
列的统计信息
- 列的统计信息主要包含列的基数、列中的空值数量以及列的数据分布情况(直方图)
- 通过数据字典DBA_TAB_COL_STATISTICS查看列的统计信息。
/*列的统计信息*/
select column_name, num_distinct, num_nulls, num_buckets, histogram
from dba_tab_col_statistics
where owner = 'SCOTT'
and table_name = 'T_STATS';
scott@orclpdb1:orclcdb> set pagesize 200
scott@orclpdb1:orclcdb> select column_name, num_distinct, num_nulls, num_buckets, histogram
2 from dba_tab_col_statistics
where owner = 'SCOTT'
4 and table_name = 'T_STATS';
COLUMN_NAME NUM_DISTINCT NUM_NULLS NUM_BUCKETS HISTOGRAM
-------------------------------------------------------------------------------------------------------------------------------- ------------ ---------- ----------- ---------------
EDITIONABLE 2 48191 1 NONE
ORACLE_MAINTAINED 2 0 1 NONE
APPLICATION 1 0 1 NONE
DEFAULT_COLLATION 1 58943 1 NONE
DUPLICATED 1 0 1 NONE
SHARDED 1 0 1 NONE
CREATED_APPID 0 73522 0 NONE
CREATED_VSNID 0 73522 0 NONE
MODIFIED_APPID 0 73522 0 NONE
MODIFIED_VSNID 0 73522 0 NONE
OWNER 38 0 1 NONE
OBJECT_NAME 61423 0 1 NONE
SUBOBJECT_NAME 420 72490 1 NONE
OBJECT_ID 73520 2 1 NONE
DATA_OBJECT_ID 6591 66894 1 NONE
OBJECT_TYPE 47 0 1 NONE
CREATED 994 0 1 NONE
LAST_DDL_TIME 1263 2 1 NONE
TIMESTAMP 1182 2 1 NONE
STATUS 2 0 1 NONE
TEMPORARY 2 0 1 NONE
GENERATED 2 0 1 NONE
SECONDARY 2 0 1 NONE
NAMESPACE 23 2 1 NONE
EDITION_NAME 0 73522 0 NONE
SHARING 4 0 1 NONE
26 rows selected.
scott@orclpdb1:orclcdb>
工作中经常使用下面的脚本查看表和列的统计信息。
/*查看表和列的统计信息*/
select a.column_name,
b.num_rows,
a.num_nulls,
a.num_distinct Cardinality,
round(a.num_distinct / b.num_rows * 100, 2) selectivity,
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 = 'SCOTT'
and a.table_name = 'T_STATS';
索引的统计信息
- 索引的统计信息主要包含索引(blevel(索引高度-1))、叶子块的个数(leaf_blocks)以及集群因子(cluster_factor)
- 通过数据字典DBA_INDEXES查看索引的统计信息
/*查看索引的统计信息*/
select blevel,leaf_blocks,clustering_factor,status
from dba_indexes
where owner='SCOTT'
and index_name = 'IDX_T_STATS_ID';
统计信息重要参数设置
使用下面的脚本收集表和索引的统计信息。
/*收集表和索引的统计信息。*/
BEGIN
DBMS_STATS.gather_table_stats(ownname => 'TAB_OWNER',
tabname => 'TAB_NAME',
estimate_percent => 根据表大小设置,
method_opt => 'for all columns size repeat',
no_invalidate => FALSE,
degree => 根据表大小,CPU资源和负载设置,
granularity => 'AUTO',
cascade => TRUE);
END;
/
- ownername: 表的拥有者,不区分大小写
- tabname 表名字,不区分大小写
- granularity 标售收集统计信息的粒度,该选项只对分区表生效,默认为AUTO,表示让Oracle根据表的分区类型自己判断如何收集分区表的统计信息。
- estimate_percent 表示采样率,范围是0.000001 ~ 100.
- 一般对表大小为1G-5GB的表采样50%,对于大于5GB的表采样30%。特别大的表几十G甚至上百G,建议应该先对表进行分区,然后分别对每个分区收集统计信息。
- 为了确保统计信息比较准确,建议采样率不要低于30%。
/*查看表的采样率*/
select owner,
table_name,
num_rows,
sample_size,
round(sample_size / num_rows * 100) estimate_percent
from DBA_TAB_STATISTICS
WHERE owner = 'SCOTT'
AND table_name = 'T_STATS';
scott@orclpdb1:orclcdb>
scott@orclpdb1:orclcdb> /*查看表的采样率*/
scott@orclpdb1:orclcdb> select owner,
table_name,
3 num_rows,
4 sample_size,
5 round(sample_size / num_rows * 100) estimate_percent
6 from DBA_TAB_STATISTICS
7 WHERE owner = 'SCOTT'
8 AND table_name = 'T_STATS';
OWNER TABLE_NAME NUM_ROWS SAMPLE_SIZE ESTIMATE_PERCENT
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------- ----------- ----------------
SCOTT T_STATS 73522 73522 100
1 row selected.
scott@orclpdb1:orclcdb>
将采样率设置为30%
/*收集表T_STATS统计信息,将采样率设置为30%*/
BEGIN
DBMS_STATS.gather_table_stats(ownname => 'SCOTT',
tabname => 'T_STATS',
estimate_percent => 30,
method_opt => 'for all columns size auto',
no_invalidate => FALSE,
degree => 1,
cascade => TRUE);
END;
/
scott@orclpdb1:orclcdb>
scott@orclpdb1:orclcdb> /*收集表T_STATS统计信息,将采样率设置为30%*/
scott@orclpdb1:orclcdb>
BEGIN
2 DBMS_STATS.gather_table_stats(ownname => 'SCOTT',
3 tabname => 'T_STATS',
4 estimate_percent => 30,
method_opt => 'for all columns size auto',
6 no_invalidate => FALSE,
7 degree => 1,
8 cascade => TRUE);
9 END;
10 /
PL/SQL procedure successfully completed.
scott@orclpdb1:orclcdb>
scott@orclpdb1:orclcdb> /*查看表的采样率*/
scott@orclpdb1:orclcdb> select owner,
table_name,
3 num_rows,
4 sample_size,
round(sample_size / num_rows * 100) estimate_percent
6 from DBA_TAB_STATISTICS
7 WHERE owner = 'SCOTT'
8 AND table_name = 'T_STATS';
OWNER TABLE_NAME NUM_ROWS SAMPLE_SIZE ESTIMATE_PERCENT
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------- ----------- ----------------
SCOTT T_STATS 73527 22058 30
1 row selected.
scott@orclpdb1:orclcdb>
- method_opt 用于控制收集直方图策略。
- method_opt => 'for all columns size 1' 表示所有列都不收集直方图。
/*收集统计信息*/
BEGIN
DBMS_STATS.gather_table_stats(ownname => 'SCOTT',
tabname => 'T_STATS',
estimate_percent => 100,
method_opt => 'for all columns size 1',
no_invalidate => FALSE,
degree => 1,
cascade => TRUE);
END;
/
查看直方图信息:
/*查看表和列的统计信息*/
select a.column_name,
b.num_rows,
a.num_nulls,
a.num_distinct Cardinality,
round(a.num_distinct / b.num_rows * 100, 2) selectivity,
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 = 'SCOTT'
and a.table_name = 'T_STATS';
scott@orclpdb1:orclcdb>
scott@orclpdb1:orclcdb> /*查看表和列的统计信息*/
scott@orclpdb1:orclcdb> select a.column_name,
b.num_rows,
3 a.num_nulls,
4 a.num_distinct Cardinality,
round(a.num_distinct / b.num_rows * 100, 2) selectivity,
6 a.histogram,
a.num_buckets
8 from dba_tab_col_statistics a, dba_tables b
9 where a.owner = b.owner
10 and a.table_name = b.table_name
and a.owner = 'SCOTT'
12 and a.table_name = 'T_STATS';
COLUMN_NAME NUM_ROWS NUM_NULLS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
-------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- ----------- ----------- --------------- -----------
EDITIONABLE 73527 48324 2 0 NONE 1
ORACLE_MAINTAINED 73527 0 2 0 NONE 1
APPLICATION 73527 0 1 0 NONE 1
DEFAULT_COLLATION 73527 59100 1 0 NONE 1
DUPLICATED 73527 0 1 0 NONE 1
SHARDED 73527 0 1 0 NONE 1
CREATED_APPID 73527 73527 0 0 NONE 0
CREATED_VSNID 73527 73527 0 0 NONE 0
MODIFIED_APPID 73527 73527 0 0 NONE 0
MODIFIED_VSNID 73527 73527 0 0 NONE 0
OWNER 73527 0 38 .05 NONE 1
OBJECT_NAME 73527 0 54851 74.6 NONE 1
SUBOBJECT_NAME 73527 72560 215 .29 NONE 1
OBJECT_ID 73527 0 73527 100 NONE 1
DATA_OBJECT_ID 73527 66974 6533 8.89 NONE 1
OBJECT_TYPE 73527 0 41 .06 NONE 1
CREATED 73527 0 853 1.16 NONE 1
LAST_DDL_TIME 73527 0 1031 1.4 NONE 1
TIMESTAMP 73527 0 951 1.29 NONE 1
STATUS 73527 0 2 0 NONE 1
TEMPORARY 73527 0 2 0 NONE 1
GENERATED 73527 0 2 0 NONE 1
SECONDARY 73527 0 2 0 NONE 1
NAMESPACE 73527 0 19 .03 NONE 1
EDITION_NAME 73527 73527 0 0 NONE 0
SHARING 73527 0 4 .01 NONE 1
26 rows selected.
scott@orclpdb1:orclcdb> /*收集表T_STATS统计信息*/
scott@orclpdb1:orclcdb>
BEGIN
2 DBMS_STATS.gather_table_stats(ownname => 'SCOTT',
3 tabname => 'T_STATS',
estimate_percent => 100,
5 method_opt => 'for all columns size skewonly',
6 no_invalidate => FALSE,
degree => 1,
8 cascade => TRUE);
9 END;
10 /
PL/SQL procedure successfully completed.
scott@orclpdb1:orclcdb>
scott@orclpdb1:orclcdb> /*查看表和列的统计信息*/
scott@orclpdb1:orclcdb> select a.column_name,
b.num_rows,
3 a.num_nulls,
4 a.num_distinct Cardinality,
5 round(a.num_distinct / b.num_rows * 100, 2) selectivity,
a.histogram,
7 a.num_buckets
8 from dba_tab_col_statistics a, dba_tables b
9 where a.owner = b.owner
and a.table_name = b.table_name
11 and a.owner = 'SCOTT'
12 and a.table_name = 'T_STATS';
COLUMN_NAME NUM_ROWS NUM_NULLS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
-------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- ----------- ----------- --------------- -----------
EDITIONABLE 73522 48191 2 0 FREQUENCY 2
ORACLE_MAINTAINED 73522 0 2 0 FREQUENCY 2
APPLICATION 73522 0 1 0 FREQUENCY 1
DEFAULT_COLLATION 73522 58943 1 0 FREQUENCY 1
DUPLICATED 73522 0 1 0 FREQUENCY 1
SHARDED 73522 0 1 0 FREQUENCY 1
CREATED_APPID 73522 73522 0 0 NONE 0
CREATED_VSNID 73522 73522 0 0 NONE 0
MODIFIED_APPID 73522 73522 0 0 NONE 0
MODIFIED_VSNID 73522 73522 0 0 NONE 0
OWNER 73522 0 38 .05 FREQUENCY 38
OBJECT_NAME 73522 0 61423 83.54 HEIGHT BALANCED 254
SUBOBJECT_NAME 73522 72490 420 .57 HEIGHT BALANCED 254
OBJECT_ID 73522 2 73520 100 NONE 1
DATA_OBJECT_ID 73522 66894 6591 8.96 HEIGHT BALANCED 254
OBJECT_TYPE 73522 0 47 .06 FREQUENCY 47
CREATED 73522 0 994 1.35 HEIGHT BALANCED 254
LAST_DDL_TIME 73522 2 1263 1.72 HEIGHT BALANCED 254
TIMESTAMP 73522 2 1182 1.61 HEIGHT BALANCED 254
STATUS 73522 0 2 0 FREQUENCY 2
TEMPORARY 73522 0 2 0 FREQUENCY 2
GENERATED 73522 0 2 0 FREQUENCY 2
SECONDARY 73522 0 2 0 FREQUENCY 2
NAMESPACE 73522 2 23 .03 FREQUENCY 23
EDITION_NAME 73522 73522 0 0 NONE 0
SHARING 73522 0 4 .01 FREQUENCY 4
26 rows selected.
scott@orclpdb1:orclcdb>
切记:在实际工作中千万不要使用method_opt => 'for all columns size skewonly' (表示对出现在where条件中的列自动判断是否收集直方图)收集直方图信息,主要原因是并不是所有列都被包含在where条件列中。对未被包含的列进行直方图信息收集没有任何意义。
使用method_opt => 'for all columns size auto' 方式对表收集统计信息。
结论: 以下例子可以说明,Oracle自动地对owner 列收集了直方图。
案例实践:
scott@orclpdb1:orclcdb>
scott@orclpdb1:orclcdb> /*收集表T_STATS统计信息,删除表中所有列的直方图(所有列都不收集直方图)*/
scott@orclpdb1:orclcdb>
BEGIN
2 DBMS_STATS.gather_table_stats(ownname => 'SCOTT',
3 tabname => 'T_STATS',
4 estimate_percent => 100,
method_opt => 'for all columns size 1',
6 no_invalidate => FALSE,
7 degree => 1,
8 cascade => TRUE);
9 END;
10 /
PL/SQL procedure successfully completed.
scott@orclpdb1:orclcdb> select count(*) from t_stats where owner='SYS';
COUNT(*)
----------
52433
1 row selected.
scott@orclpdb1:orclcdb> /*刷新数据库监控信息*/
scott@orclpdb1:orclcdb> begin
dbms_stats.flush_database_monitoring_info;
3 end;
4 /
PL/SQL procedure successfully completed.
scott@orclpdb1:orclcdb>
scott@orclpdb1:orclcdb> /*收集表T_STATS统计信息*/
scott@orclpdb1:orclcdb>
BEGIN
2 DBMS_STATS.gather_table_stats(ownname => 'SCOTT',
3 tabname => 'T_STATS',
4 estimate_percent => 100,
method_opt => 'for all columns size auto',
6 no_invalidate => FALSE,
7 degree => 1,
8 cascade => TRUE);
9 END;
10 /
PL/SQL procedure successfully completed.
scott@orclpdb1:orclcdb>
scott@orclpdb1:orclcdb>
scott@orclpdb1:orclcdb> /*查看直方图信息*/
scott@orclpdb1:orclcdb> select a.column_name,
b.num_rows,
3 a.num_nulls,
4 a.num_distinct Cardinality,
round(a.num_distinct / b.num_rows * 100, 2) selectivity,
6 a.histogram,
7 a.num_buckets
8 from dba_tab_col_statistics a, dba_tables b
9 where a.owner = b.owner
10 and a.table_name = b.table_name
11 and a.owner = 'SCOTT'
12 and a.table_name = 'T_STATS';
COLUMN_NAME NUM_ROWS NUM_NULLS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
-------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- ----------- ----------- --------------- -----------
EDITIONABLE 73522 48191 2 0 NONE 1
ORACLE_MAINTAINED 73522 0 2 0 NONE 1
APPLICATION 73522 0 1 0 NONE 1
DEFAULT_COLLATION 73522 58943 1 0 NONE 1
DUPLICATED 73522 0 1 0 NONE 1
SHARDED 73522 0 1 0 NONE 1
CREATED_APPID 73522 73522 0 0 NONE 0
CREATED_VSNID 73522 73522 0 0 NONE 0
MODIFIED_APPID 73522 73522 0 0 NONE 0
MODIFIED_VSNID 73522 73522 0 0 NONE 0
OWNER 73522 0 38 .05 FREQUENCY 38
OBJECT_NAME 73522 0 61423 83.54 NONE 1
SUBOBJECT_NAME 73522 72490 420 .57 NONE 1
OBJECT_ID 73522 2 73520 100 NONE 1
DATA_OBJECT_ID 73522 66894 6591 8.96 NONE 1
OBJECT_TYPE 73522 0 47 .06 NONE 1
CREATED 73522 0 994 1.35 NONE 1
LAST_DDL_TIME 73522 2 1263 1.72 NONE 1
TIMESTAMP 73522 2 1182 1.61 NONE 1
STATUS 73522 0 2 0 NONE 1
TEMPORARY 73522 0 2 0 NONE 1
GENERATED 73522 0 2 0 NONE 1
SECONDARY 73522 0 2 0 NONE 1
NAMESPACE 73522 2 23 .03 NONE 1
EDITION_NAME 73522 73522 0 0 NONE 0
SHARING 73522 0 4 .01 NONE 1
26 rows selected.
scott@orclpdb1:orclcdb> /
选择性比较高的列放入where 条件中是否会自动收集直方图?
实践结论: Object_name列没有收集直方图。由此说明AUTO方式收集直方图很智能。
默认方式:
method_opt => 'for all columns size auto
案例实践:
scott@orclpdb1:orclcdb>
scott@orclpdb1:orclcdb> select count(*) from t_stats where object_name='EMP';
COUNT(*)
----------
1
1 row selected.
scott@orclpdb1:orclcdb> /*刷新数据库监控信息*/
scott@orclpdb1:orclcdb> begin
dbms_stats.flush_database_monitoring_info;
3 end;
4 /
PL/SQL procedure successfully completed.
scott@orclpdb1:orclcdb> /*收集表T_STATS统计信息*/
scott@orclpdb1:orclcdb>
BEGIN
2 DBMS_STATS.gather_table_stats(ownname => 'SCOTT',
3 tabname => 'T_STATS',
4 estimate_percent => 100,
5 method_opt => 'for all columns size auto',
6 no_invalidate => FALSE,
7 degree => 1,
8 cascade => TRUE);
9 END;
10 /
PL/SQL procedure successfully completed.
scott@orclpdb1:orclcdb> /*查看object_name列是否收集了直方图信息*/
scott@orclpdb1:orclcdb> select a.column_name,
b.num_rows,
3 a.num_nulls,
4 a.num_distinct Cardinality,
5 round(a.num_distinct / b.num_rows * 100, 2) selectivity,
a.histogram,
7 a.num_buckets
8 from dba_tab_col_statistics a, dba_tables b
9 where a.owner = b.owner
10 and a.table_name = b.table_name
11 and a.owner = 'SCOTT'
12 and a.table_name = 'T_STATS';
COLUMN_NAME NUM_ROWS NUM_NULLS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
-------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- ----------- ----------- --------------- -----------
EDITIONABLE 73522 48191 2 0 NONE 1
ORACLE_MAINTAINED 73522 0 2 0 NONE 1
APPLICATION 73522 0 1 0 NONE 1
DEFAULT_COLLATION 73522 58943 1 0 NONE 1
DUPLICATED 73522 0 1 0 NONE 1
SHARDED 73522 0 1 0 NONE 1
CREATED_APPID 73522 73522 0 0 NONE 0
CREATED_VSNID 73522 73522 0 0 NONE 0
MODIFIED_APPID 73522 73522 0 0 NONE 0
MODIFIED_VSNID 73522 73522 0 0 NONE 0
OWNER 73522 0 38 .05 FREQUENCY 38
OBJECT_NAME 73522 0 61423 83.54 NONE 1
SUBOBJECT_NAME 73522 72490 420 .57 NONE 1
OBJECT_ID 73522 2 73520 100 NONE 1
DATA_OBJECT_ID 73522 66894 6591 8.96 NONE 1
OBJECT_TYPE 73522 0 47 .06 NONE 1
CREATED 73522 0 994 1.35 NONE 1
LAST_DDL_TIME 73522 2 1263 1.72 NONE 1
TIMESTAMP 73522 2 1182 1.61 NONE 1
STATUS 73522 0 2 0 NONE 1
TEMPORARY 73522 0 2 0 NONE 1
GENERATED 73522 0 2 0 NONE 1
SECONDARY 73522 0 2 0 NONE 1
NAMESPACE 73522 2 23 .03 NONE 1
EDITION_NAME 73522 73522 0 0 NONE 0
SHARING 73522 0 4 .01 NONE 1
26 rows selected.
scott@orclpdb1:orclcdb> /
method_opt => 'for all columns size repeat' 表示当前有哪些列收集了直方图,就重复对这些列收集直方图。
method_opt => 'for columns object_type size skewonly' 表示单独对 object_type收集直方图。
检查统计信息是否过期
scott@orclpdb1:orclcdb>
scott@orclpdb1:orclcdb> create index idx_stats_owner on t_stats(owner);
Index created.
scott@orclpdb1:orclcdb> BEGIN
2 DBMS_STATS.gather_table_stats(ownname => 'SCOTT',
3 tabname => 'T_STATS',
4 estimate_percent => 30,
5 method_opt => 'for all columns size skewonly',
6 no_invalidate => FALSE,
7 degree => 1,
8 cascade => TRUE);
9 END;
10 /
PL/SQL procedure successfully completed.
scott@orclpdb1:orclcdb> set autot trace
scott@orclpdb1:orclcdb>
scott@orclpdb1:orclcdb> select * from t_stats where owner='SCOTT';
33 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1288907714
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40 | 5280 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T_STATS | 40 | 5280 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_STATS_OWNER | 40 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SCOTT')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
6759 bytes sent via SQL*Net to client
646 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
33 rows processed
scott@orclpdb1:orclcdb>
stale_stats 显示为YES表示表的统计信息过期了,如果stale_stats显示为no表示表的统计信息没有过期。
select owner, table_name, object_type, stale_stats, last_analyzed
from DBA_TAB_STATISTICS
WHERE owner = 'SCOTT'
AND table_name = 'T_STATS';
scott@orclpdb1:orclcdb> set linesize 300
scott@orclpdb1:orclcdb> set pagesize 300
scott@orclpdb1:orclcdb>
scott@orclpdb1:orclcdb> select owner, table_name, object_type, stale_stats, last_analyzed
2 from DBA_TAB_STATISTICS
3 WHERE owner = 'SCOTT'
4 AND table_name = 'T_STATS';
OWNER TABLE_NAME OBJECT_TYPE STALE_S
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------ -------
LAST_ANALYZED
-----------------------------
SCOTT T_STATS TABLE YES
18-OCT-2022 20:06:39
1 row selected.
scott@orclpdb1:orclcdb>