oracle的统计信息的查看与收集

最新推荐文章于 2023-11-21 15:25:23 发布

张玉龙

最新推荐文章于 2023-11-21 15:25:23 发布

阅读量4.1w

点赞数 6

分类专栏： oracle优化学习

本文链接：https://blog.csdn.net/u010692693/article/details/75206206

版权

oracle优化学习专栏收录该内容

62 篇文章 4 订阅

订阅专栏

查看某个表的统计信息

SQL> alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> select t.TABLE_NAME,t.NUM_ROWS,t.BLOCKS,t.LAST_ANALYZED from user_tables t where table_name in ('T1','T2');

TABLE_NAME                       NUM_ROWS     BLOCKS LAST_ANALYZED
------------------------------ ---------- ---------- -------------------
T1                                   2000         30 2017-07-16 14:02:23
T2                                   2000         30 2017-07-16 14:02:23

查看某个表上索引的统计信息

SQL> select table_name,index_name,t.blevel,t.num_rows,t.leaf_blocks,t.last_analyzed from user_indexes t where table_name in ('T1','T2');

TABLE_NAME     INDEX_NAME                 BLEVEL   NUM_ROWS LEAF_BLOCKS LAST_ANALYZED
-------------- ---------------------- ---------- ---------- ----------- -------------------
T1             IDX_T1_OBJ_ID                   1       2000           5 2017-07-16 12:06:33
T2             IDX_T2_OBJ_ID                   1       2000           5 2017-07-16 14:02:23
T2             IDX_T2_OBJ_TYPE                 1       2000           5 2017-07-16 14:02:23
T2             IDX_T2_OBJ_NAME                 1       2000           8 2017-07-16 14:02:23
T2             IDX_T2_DATA_OBJ_ID              1       1198           3 2017-07-16 14:02:23
T2             IDX_T2_STATUS                   1       2000           5 2017-07-16 14:02:23
T2             IDX_T2_CREATED                  1       2000           6 2017-07-16 14:02:23
T2             IDX_T2_LAST_DDL_TIME            1       2000           6 2017-07-16 14:02:23

8 rows selected.

查看统计信息的脚本，来源于MOS：SCRIPT - Select to show Optimizer Statistics for CBO (Doc ID 31412.1)

脚本下载地址参考文章：https://blog.csdn.net/u010692693/article/details/103120646

下面是脚本执行输出结果示例：

***********
Table Level
***********

Table               Number             Empty Average Chain Average Global User       Sample Date
Name               of Rows   Blocks   Blocks   Space Count Row Len Stats  Stats        Size MM-DD-YYYY
--------------- ---------- -------- -------- ------- ----- ------- ------ ------ ---------- ----------
T1                   5,000       20        0       0     0       9 YES    NO          5,000 06-30-2003

Column                    Column                   Distinct          Number            Global User    Sample Date
Name                      Details                    Values Density Buckets  NUM_NULLS Stats  Stats     Size MM-DD-YYYY
------------------------- ------------------------ -------- ------- ------- ---------- ------ ------ ---------- ----------
T1C1                      NUMBER(22)                  5,000       0       1          0 YES    NO          5,000 06-30-2003
T1C2                      NUMBER(22)                      7       0       1          0 YES    NO          5,000 06-30-2003
T1C3                      NUMBER(22)                      8       0       1          0 YES    NO          5,000 06-30-2003

                              B                                Average     Average
Index                      Tree Leaf   Distinct     Number Leaf Blocks Data Blocks  Cluster Global User       Sample Date
Name            Unique    Level Blks       Keys    of Rows     Per Key     Per Key   Factor Stats  Stats        Size MM-DD-YYYY
--------------- --------- ----- ---- ---------- ---------- ----------- ----------- -------- ------ ------ ---------- ----------
UN_T1           NONUNIQUE     1   12      5,000      5,000           1           1       12 YES    NO          5,000 06-30-2003

Index           Column                     Col Column
Name            Name                       Pos Details
--------------- ------------------------- ---- ------------------------
UN_T1           T1C1                         1 NUMBER(22)


***************
Partition Level
***************

Partition           Number             Empty Average Chain Average Global User       Sample Date
Name               of Rows   Blocks   Blocks   Space Count Row Len Stats  Stats        Size MM-DD-YYYY
--------------- ---------- -------- -------- ------- ----- ------- ------ ------ ---------- ----------
Q1                   2,144       10        0       0     0       9 YES    NO          2,144 06-30-2003
Q2                   2,856       10        0       0     0       9 YES    NO          2,856 06-30-2003

Partition       Column                    Distinct          Number            Global User       Sample Date
Name            Name                        Values Density Buckets  NUM_NULLS Stats  Stats        Size MM-DD-YYYY
--------------- ------------------------- -------- ------- ------- ---------- ------ ------ ---------- ----------
Q1              T1C1                         2,144       0       1          0 YES    NO          2,144 06-30-2003
                T1C2                             3       0       1          0 YES    NO          2,144 06-30-2003
                T1C3                             8       0       1          0 YES    NO          2,144 06-30-2003
Q2              T1C1                         2,856       0       1          0 YES    NO          2,856 06-30-2003
                T1C2                             4       0       1          0 YES    NO          2,856 06-30-2003
                T1C3                             8       0       1          0 YES    NO          2,856 06-30-2003

                                    B                                Average     Average
Index           Partition        Tree Leaf   Distinct     Number Leaf Blocks Data Blocks  Cluster Global User       Sample Date
Name            Name            Level Blks       Keys    of Rows     Per Key     Per Key   Factor Stats  Stats        Size MM-DD-YYYY
--------------- --------------- ----- ---- ---------- ---------- ----------- ----------- -------- ------ ------ ---------- ----------
UN_T1           Q1                  1   12      5,000      5,000           1           1       12 YES    NO          5,000 06-30-2003
UN_T1           Q2                  1   12      5,000      5,000           1           1       12 YES    NO          5,000 06-30-2003


***************
SubPartition Level
***************

Partition       SubPartition        Number             Empty Average Chain Average Global User       Sample Date
Name            Name               of Rows   Blocks   Blocks   Space Count Row Len Stats  Stats        Size MM-DD-YYYY
--------------- --------------- ---------- -------- -------- ------- ----- ------- ------ ------ ---------- ----------
Q1              SYS_SUBP497            803        5        0       0     0       9 YES    NO            803 06-30-2003
Q2              SYS_SUBP499          1,072        5        0       0     0       9 YES    NO          1,072 06-30-2003
Q1              SYS_SUBP498          1,341        5        0       0     0       9 YES    NO          1,341 06-30-2003
Q2              SYS_SUBP500          1,784        5        0       0     0       9 YES    NO          1,784 06-30-2003

Partition       SubPartition    Column                    Distinct          Number            Global User       Sample Date
Name            Name            Name                        Values Density Buckets  NUM_NULLS Stats  Stats        Size MM-DD-YYYY
--------------- --------------- ------------------------- -------- ------- ------- ---------- ------ ------ ---------- ----------
Q1              SYS_SUBP497     T1C1                           803       0       1          0 YES    NO            803 06-30-2003
                SYS_SUBP498     T1C1                         1,341       0       1          0 YES    NO          1,341 06-30-2003
                SYS_SUBP497     T1C2                             3       0       1          0 YES    NO            803 06-30-2003
                SYS_SUBP498     T1C2                             3       0       1          0 YES    NO          1,341 06-30-2003
                SYS_SUBP497     T1C3                             3       0       1          0 YES    NO            803 06-30-2003
                SYS_SUBP498     T1C3                             5       0       1          0 YES    NO          1,341 06-30-2003
Q2              SYS_SUBP499     T1C1                         1,072       0       1          0 YES    NO          1,072 06-30-2003
                SYS_SUBP500     T1C1                         1,784       0       1          0 YES    NO          1,784 06-30-2003
                SYS_SUBP499     T1C2                             4       0       1          0 YES    NO          1,072 06-30-2003
                SYS_SUBP500     T1C2                             4       0       1          0 YES    NO          1,784 06-30-2003
                SYS_SUBP499     T1C3                             3       0       1          0 YES    NO          1,072 06-30-2003
                SYS_SUBP500     T1C3                             5       0       1          0 YES    NO          1,784 06-30-2003

                                                    B                                Average     Average
Index           Partition       SubPartition     Tree Leaf   Distinct     Number Leaf Blocks Data Blocks  Cluster Global User       Sample Date
Name            Name            Name            Level Blks       Keys    of Rows     Per Key     Per Key   Factor Stats  Stats        Size MM-DD-YYY
--------------- --------------- --------------- ----- ---- ---------- ---------- ----------- ----------- -------- ------ ------ ---------- ---------
UN_T1           Q1              SYS_SUBP497         1    2        803        803           1           1        2 YES    NO            803 06-30-200
UN_T1                           SYS_SUBP498         1    3      1,341      1,341           1           1        3 YES    NO          1,341 06-30-200
UN_T1           Q2              SYS_SUBP499         1    3      1,072      1,072           1           1        3 YES    NO          1,072 06-30-200
UN_T1                           SYS_SUBP500         1    4      1,784      1,784           1           1        4 YES    NO          1,784 06-30-200

oracle会在一个固定的时间将数据库里的表和索引的相关统计信息进行收集，默认选择周一到周五晚上10点，持续收集4小时，和周六周日早上6点，持续收集20小时。

oracle可以专门对表的记录变化量进行管理，当某表一天记录变化量没有超过指定的阀值时，oracle就不会对该表进行统计信息收集。

修改统计信息自动收集时间

SQL> set linesize 200
SQL> col REPEAT_INTERVAL for a60
SQL> col DURATION for a30
SQL> select t1.window_name,t1.repeat_interval,t1.duration from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2
  2  where t1.window_name=t2.window_name and t2.window_group_name in ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED');

WINDOW_NAME        REPEAT_INTERVAL                                              DURATION
------------------ ------------------------------------------------------------ ---------------
MONDAY_WINDOW      freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0        +000 04:00:00
TUESDAY_WINDOW     freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0        +000 04:00:00
WEDNESDAY_WINDOW   freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0        +000 04:00:00
THURSDAY_WINDOW    freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0        +000 04:00:00
FRIDAY_WINDOW      freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0        +000 04:00:00
SATURDAY_WINDOW    freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0         +000 20:00:00
SUNDAY_WINDOW      freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0         +000 20:00:00

7 rows selected.


关闭自动统计信息收集
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;
/


SQL> set linesize 200
SQL> col REPEAT_INTERVAL for a60
SQL> col DURATION for a30
SQL> select t1.window_name,t1.repeat_interval,t1.duration from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2
  2  where t1.window_name=t2.window_name and t2.window_group_name in ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED');

WINDOW_NAME       REPEAT_INTERVAL                                              DURATION
----------------- ------------------------------------------------------------ --------------
MONDAY_WINDOW     freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0        +000 04:00:00
TUESDAY_WINDOW    freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0        +000 04:00:00
WEDNESDAY_WINDOW  freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0        +000 04:00:00
THURSDAY_WINDOW   freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0        +000 04:00:00
FRIDAY_WINDOW     freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0        +000 04:00:00
SATURDAY_WINDOW   freq=daily;byday=SAT;byhour=22;byminute=0; bysecond=0        +000 04:00:00
SUNDAY_WINDOW     freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0         +000 20:00:00

7 rows selected.

手动收集统计信息

收集表统计信息

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);

收集分区表的某个分区统计信息

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_schema_stats(ownname=>'CS',estimate_percent=>10,degree=>8,cascade=>true,granularity=>'ALL');

收集整个数据库的统计信息

exec dbms_stats.gather_database_stats(estimate_percent=>10,degree=>8,cascade=>true,granularity=>'ALL');

关于dbms_stats中参数的解释，具体参考官方文档：

http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_stats.htm#ARPLS68582

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

动态采集统计信息

对于新创建的表，当访问此表时，oracle会动态的收集这个表的相关信息，等到晚上10点，再将其收集到数据字典中。

SQL> set autotrace off
SQL> set linesize 1000
SQL> drop table t_sample purge;
drop table t_sample purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create table t_sample as select * from dba_objects;

Table created.

SQL> create index idx_t_sample_objid on t_sample(object_id);

Index created.

新建的表，查不到统计信息

SQL> select num_rows, blocks, last_analyzed from user_tables where table_name = 'T_SAMPLE';

  NUM_ROWS     BLOCKS LAST_ANAL
---------- ---------- ---------

SQL> set autotrace traceonly
SQL> set linesize 1000
SQL> select  * from t_sample where object_id=20;


Execution Plan
----------------------------------------------------------
Plan hash value: 1453182238

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |   207 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_SAMPLE           |     1 |   207 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T_SAMPLE_OBJID |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=20)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
         24  recursive calls
          0  db block gets
         93  consistent gets
          1  physical reads
          0  redo size
       1608  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

- dynamic sampling used for this statement (level=2) 表示动态采样，但是不记录数据字典，除非手动收集表的统计信息。

SQL> select num_rows, blocks, last_analyzed from user_tables where table_name = 'T_SAMPLE';

  NUM_ROWS     BLOCKS LAST_ANAL
---------- ---------- ---------


SQL>

张玉龙

关注

6
点赞
踩
56

收藏

觉得还不错? 一键收藏
1
评论
oracle的统计信息的查看与收集

查看某个表的统计信息SQL> alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';Session altered.SQL> select t.TABLE_NAME,t.NUM_ROWS,t.BLOCKS,t.LAST_ANALYZED from user_tables t where table_name i...
复制链接

扫一扫