不可不知的统计信息

于 2022-10-18 20:48:14 发布
阅读量386 收藏
点赞数
分类专栏： SQL优化 文章标签： 数据库 dba sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u011868279/article/details/127385675
版权

收集统计信息的目的：为了让优化器选择最佳执行计划，以最少的代价（成本）查询出表中的数据。

统计信息主要分为：

  • 表的统计信息
  • 列的统计信息
  • 索引的统计信息
  • 系统的统计信息
  • 数据字典的统计信息
  • 动态性能视图基表的统计信息

表的统计信息

  • 包含表的总行数（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>
DB架构
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
不可不知的电子工程常用的6大电子元器件，了解一下！
07-07
一、电阻器 电阻可以说是电路工程中最常用的电子元器件，用R表示，表征导体对电流的阻碍作用。在电路中的作用主要是分流、限流、分压、偏置等。 电阻的参数识别：常用的是色标法、值标法和数标法。 常用的是色标法，带有四个色环的其中第一、二环分别代表阻值的前两位数；第三环代表倍率；第四环代表误差。比如说，当四个色环依次是黄、橙、红、金色时，因第三环为红色、阻值范围是几点几kΩ的，按照黄、橙两色分别代表的数”4″和”3″代入,，则其读数为4.3 kΩ。第环是金色表示误差为5%。   二、电容器 电容在电路中用来储存电荷和电能，用C表示，电容的主要特性是通交流隔直流，电容对交流电的阻碍叫做容抗，是电抗的一种（还有一种叫做感抗），容抗的大小与交流电的频率和自身容量有关。电容器在电路中的作用主要是：耦合、滤波、谐振、旁路、补偿、分频等。 电容器的参数表示也有直标法、文字和符号组合法、以及色标法。 其型号由四部分组成，当然这不适用于不适用于压敏、可变、真空电容器，依次分别代表名称、材料、分类和序号。 电容器的分类较为复杂，据目前的统计分析，有10种分类方法，具体的可参见器件手册。 目前有研究超级电容器，超级电容器是一种电容量可达数千法拉的极大容量电容器，采用双电层原理和活性炭多孔化电极。   三、晶体二极管 是一种半导体器件，具有非线性的伏安特性，用D表示，主要作用是单向导电性。核心部分是一块PN结，广泛应用于各种电子电路中。其类型很多，按照材料有硅管和锗管之分；按照功能，有整流、发光、检波、稳压、开关、续流、旋转、肖特基二极管和硅功率二极管等。按照结构点接触型和平面型之分。前者可以通过小电流、后者通过大电流。 二极管的主要参数有最大整流电流IF、最高反向工作电压、反向电流、动态电阻、最高工作频率、电压温度系数等。 二极管的正负二个端子，正端A称为阳极，负端K ；称为阴极，电流只能从阳极向阴极方向移动。很多初学者讲二极管和半导体混为一谈，其实二极管和半导体是完全不同的，只能说二极管是一种半导体器件。 二极管的识别问题：小功率二极管的N极（负极），在二极管外表大多采用一种色圈标出来，有些二极管也用二极管专用符号来表示P极（正极）或N极（负极），也有采用符号标志为“P”、“N”来确定二极管极性的。发光二极管的正负极可从引脚长短来识别，长脚为正，短脚为负。用数字式万用表去测二极管时，红表笔接二极管的正极，黑表笔接二极管的负极，此时测得的阻值才是二极管的正向导通阻值，这与指针式万用表的表笔接法刚好相反。
oracle中sys用户下all_tables表个字段说明
右手星辰
04-21 4876
1.获得当前用户有权限的表的信息（ALL_TABLES） （只要对某个表有任何权限，即可在此视图中看到表的相关信息） 表中各字段说明如下: 字段 含义 owner oracle用户名 table_name 表名 tabpespace_name 表空间名称 cluster_name 集群名称 iot(Index Organized ...
Oracle 数据泵详解
weixin_34389926的博客
06-01 1216
一、EXPDP和IMPDP使用说明 Oracle Database 10g引入了最新的数据泵(Data Dump)技术，数据泵导出导入(EXPDP和IMPDP)的作用 1）实现逻辑备份和逻辑恢复. 2）在数据库用户之间移动对象. 3）在数据库之间移动对象 4）实现表空间搬移.   二、数据泵导出导入与传统导出导入的区别 在10g之前,传统的导出和导入分别使用EXP工具和IMP工...
table statistics lock
ct827519的博客
04-18 158
今天在检查刚接手的DB的AWR报告时发现一个SQL Gets很大，于是进行tunning，看了一眼user_tables，发现这个表的last_analyzed居然是2007年的，很奇怪，这个表应该是在不断的进行修改，并且，数据...
oracle统计总行数(sample_size)和占用空间 各个表的条数
enthan809882的博客
02-27 4109
老板突然让统计oracle数据库总行数，和占用空间。 统计总行数： SELECT sum(sample_size) FROM user_tables; 统计占用空间： select sum(bytes)/1024/1024 from dba_segments;
mysql statistics 多_Mysql5.6 innodb如何 统计table statistics
weixin_27697385的博客
01-19 399
Table statistics 包含两个方面：1) table stat 比如表内一共有多少条记录(n_rows)。此类信息保存在mysql.innodb_table_stats表中。2) index stat 比如index中包含多少个page, 多少个leaf page，共有多少条完全不同的索引此信息保存在mysql.innodb_index_stats表中。在innodb中，一切都是基于i...
数据泵导入中table_statistics长时间等待问题
smh821025的专栏
12-27 7162
用impdp 导入，检查 table_statistics 时等待了N长时间。      网上说这是oracle 10G impdp 的一个bug，命令中若定义了 remap_schema 项，也就是原用户名和导入的用户名不一致，则会有这个问题。      网上提供的解决办法是，让导入和导出的用户名一致，或者在导入时通过 EXCLUDE=TABLE_STATISTICS 选项将 table
Oracle 系列 统计信息详解（Statistic）
我家狗不咬人的博客
06-02 5136
1、概述 统计信息对基于CBO优化器的Oracle版本尤为重要，一个统计信息的好坏，很大程度将直接影响原本有可能几秒执行成功的一段SQL脚本，因为错误的统计信息而选择一条错误的执行计划，导致几分钟才能出来结果。 2、优化器 Oracle中的优化器是SQL分析和执行的优化工具，它负责生成、制定SQL的执行计划。 Oracle 中主要的优化器有两种： 2.1、RBO（Rule-Based Optimization） 基于规则的...
Oracle 10g 统计信息自动收集功能
07-18 157
1、什么是STATISTICS：优化统计信息是为了更详细的描述数据库及数据库对象而收集的数据，这些信息被用于为sql语句选择最优的执行计划。优化统计信息包括的项有： Table statistics（表统计信息）：Numbe...
ORA-39126: 在 KUPW$WORKER.PUT_DDLS [TABLE_STATISTICS] 错误解决
Lixora's DB Home
10-22 3346
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production PL
收获不知Oracle
08-07
5.2.1.8 不可不说的主外键设计265 5.2.1.9 组合索引高效设计要领272 5.2.1.10变换角度看索引的危害289 5.2.1.11如何合理控制索引数量295 5.2.2 位图索引的玫瑰花之刺 297 5.2.2.1 统计条数奋勇夺冠297 5.2.2.2 即席...
管理信息系统课程设计前言.doc
05-31
经 过处理转换为对各部门不可缺少的数据，经过对这些数据的分析使其变为各级管理人员 做出决策具有重要意义的信息。总之，管理信息系统用于管理中可以大大减轻管理人员 的工作负担，提高工作效率，使管理更加程序化...
【Word精品简历】产品运营-数据分析-统计图
最新发布
02-18
简历是个人形象，包括资历与能力的书面表述，对于求职者而言，是必不可少的一种应用文。它的功能概括说来，有如下几点: 1、可根据传媒所载招聘信息，单独寄出或与求职信配套寄出，应聘自己感兴趣的职位。 2、对不知...
让你的分析报告更具说服力——Excel高效数据统计分析.txt
12-24
创建表达多种统计信息的综合统计分析报表（统计不同地区、商品的订单数、平均订单额、订单总额等等） ? 快速创建地区销售统计分析报表，分析地区的分布情况 ? 快速创建商品销售统计分析报表，分析产品的市场分布...
Oracle-sql优化之统计信息
sinat_36757755的博客
04-19 2716
本文章主要讲述生产运维中常见的统计信息导致执行计划异常的情形以及如何确认统计信息是否存在问题，Oracle的执行计划生成是基于cost，而cost则是根据表的统计信息以及服务器性能指标去计算的，所以一个正确的统计信息对于执行计划的生成非常重要。
oracle 删除大量数据后整理表（analyze table xxx compute statistics）
热门推荐
写博客只为学习
04-11 2万+
1. DELETE 后 TRUNCATE TABLE ; 然后重新分析一下 analyze table tablename compute statistics 查看表信息 select NUM_ROWS,BLOCKS,AVG_SPACE,AVG_ROW_LEN from user_tables where table_name=table_name; 2.Oracle 表删除大量数据后，
information_schema.TABLE_STATISTICS
weixin_30488313的博客
07-11 171
version: 5.7.21 Percona Server >CREATE TABLE `t1` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uni...
ORA-39126: 在 KUPW$WORKER.PUT_DDLS [TABLE_STATISTICS] 中 Worker 发生意外致命错误
竹林晚风
09-01 4180
本文转自：http://www.xifenfei.com/2012/06/ora-39126-%E5%9C%A8-kupwworker-put_ddls-table_statistics-%E4%B8%AD-worker-%E5%8F%91%E7%94%9F%E6%84%8F%E5%A4%96%E8%87%B4%E5%91%BD%E9%94%99%E8%AF%AF.html
expdp/impdp 详细参数解释
白飞飞_Alan
02-19 3163
数据泵导出实用程序提供了一种用于在 Oracle 数据库之间传输数据对象的机制。该实用程序可以使用以下命令进行调用:    示例: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp 您可以控制导出的运行方式。具体方法是: 在 'expdp' 命令后输入各种参数。要指定各参数, 请使用关键字:    格式:  expdp K
Oracle如何进行统计信息
03-31
Oracle可以通过以下方式进行统计信息： 1. 自动统计信息：Oracle可以自动收集统计信息，以优化查询性能。默认情况下，Oracle将在每个对象的空闲时间收集统计信息。可以使用以下命令查看自动统计信息的状态： ```sql SELECT client_name, status FROM dba_autotask_client WHERE client_name LIKE '%auto optimizer%'; ``` 2. 手动统计信息：可以使用以下命令手动收集统计信息： ```sql EXEC DBMS_STATS.GATHER_SCHEMA_STATS('schema_name'); ``` ```sql EXEC DBMS_STATS.GATHER_TABLE_STATS('schema_name', 'table_name'); ``` ```sql EXEC DBMS_STATS.GATHER_INDEX_STATS('schema_name', 'index_name'); ``` 3. 使用分析管理器：分析管理器是Oracle的一个可选组件，它可以收集和分析统计信息，并在查询优化时使用它们。分析管理器使用Oracle的自动统计信息机制来收集和维护这些信息。 总之，Oracle提供了多种方法来收集和维护统计信息，以优化查询性能。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值