[统计信息系列1] 统计信息概述-搜云库技术团队

IDEA2023.1.3破解,IDEA破解,IDEA 2023.1破解,最新IDEA激活码

（一）统计信息分类

Oracle的统计信息可以分为如下6类：

统计信息分类	作用
表的统计信息	包含记录数、表块的数量、平均行长度等
索引的统计信息	包含索引的高度、叶子块的数量、聚簇因子等
列的统计信息	包含列的distinct值、列的null值的数量、列的最小值、列的最大值以及直方图等
系统统计信息	系统统计信息用于描述Oracle所在服务器的系统处理能力，它包含了CPU和I/O两个维度，借助于系统统计信息，Oracle可以更清楚的指导服务器的实际处理能力
数据字典统计信息	数据字典统计信息用于描述数据库字典基表（如tab$,ind$等）、基表的索引、以及对应列的详细信息
内部对象统计信息	内部对象统计信息用于描述Oracle的一些内部表（如x$系列表）的详细信息，他的维度和普通表的统计信息维度类似，只不过其表块的数量为0，因为x$系列表实际上是Oracle自定义的内存结构，并不占用实际空间

（二）统计信息收集与删除

（1）使用analyze来收集/删除统计信息


analyze index pk_id compute statistics;


analyze index pk_id delete statistics;


analyze table test01 estimate statistics sample 20 percent for table;


analyze table test01 compute statistics for columns col1,col2;


analyze table test01 compute statistics;


analyze table test01 delete statistics;

（2）使用DBMS_STATS来收集/删除统计信息

收集统计信息：

GATHER_TABLE_STATS：用来收集目标表、表上的列、表上的索引的统计信息；
GATHER_INDEX_STATS：用于收集索引的统计信息；
GATHER_SCHEMA_STATS：用于收集schema下所有对象的统计信息
GATHER_DATABASE_STATS：用于收集全库的统计信息

删除统计信息：

DELETE_COLUMN_STATS ：删除列的统计信息
DELETE_INDEX_STATS ：删除索引的统计信息
DELETE_TABLE_STATS ：删除表的统计信息
DELETE_SCHEMA_STATS ：删除schema的统计信息
DELETE_DATABASE_STATS ：删除数据库的统计信息

（三）统计信息收集方法比较

analyze不能正确收集分区表的统计信息。analyze只会收集最底层对象的统计信息，然后推导和汇总出高一级的统计信息。例如对于分区表，analyze会统计出分区的统计信息，然后汇总出表的统计信息，对于表的行数，可以由分区汇总得到，但是对于列的distinct值，无法得到正确值。
analyze命令不能并行收集统计信息，而dbms_stats可以。
analyze命令可以收集行迁移/行链接、索引的结构等信息，而dbms_stats不行。

（四）查看统计信息

可以使用sosi.txt脚本收集统计信息，该脚本记录了表、分区、子分区级别的统计信息，sosi.txt脚本如下：

 set echo off
 set scan on
 set lines 150
 set pages 66
 set verify off
 set feedback off
 set termout off
 column uservar new_value Table_Owner noprint
 select user uservar from dual;
 set termout on
 column TABLE_NAME heading "Tables owned by &Table_Owner" format a30
 select table_name from dba_tables where owner=upper('&Table_Owner') order by 1
 /
 undefine table_name
 undefine owner
 prompt
 accept owner prompt 'Please enter Name of Table Owner (Null = &Table_Owner): '
 accept table_name  prompt 'Please enter Table Name to show Statistics for: '
 column TABLE_NAME heading "Table|Name" format a15
 column PARTITION_NAME heading "Partition|Name" format a15
 column SUBPARTITION_NAME heading "SubPartition|Name" format a15
 column NUM_ROWS heading "Number|of Rows" format 9,999,999,990
 column BLOCKS heading "Blocks" format 999,990
 column EMPTY_BLOCKS heading "Empty|Blocks" format 999,999,990
 column AVG_SPACE heading "Average|Space" format 9,990
 column CHAIN_CNT heading "Chain|Count" format 999,990
 column AVG_ROW_LEN heading "Average|Row Len" format 990
 column COLUMN_NAME  heading "Column|Name" format a25
 column NULLABLE heading Null|able format a4
 column NUM_DISTINCT heading "Distinct|Values" format 999,999,990
 column NUM_NULLS heading "Number|Nulls" format 9,999,990
 column NUM_BUCKETS heading "Number|Buckets" format 990
 column DENSITY heading "Density" format 990
 column INDEX_NAME heading "Index|Name" format a15
 column UNIQUENESS heading "Unique" format a9
 column BLEV heading "B|Tree|Level" format 90
 column LEAF_BLOCKS heading "Leaf|Blks" format 990
 column DISTINCT_KEYS heading "Distinct|Keys" format 9,999,999,990
 column AVG_LEAF_BLOCKS_PER_KEY heading "Average|Leaf Blocks|Per Key" format 99,990
 column AVG_DATA_BLOCKS_PER_KEY heading "Average|Data Blocks|Per Key" format 99,990
 column CLUSTERING_FACTOR heading "Cluster|Factor" format 999,999,990
 column COLUMN_POSITION heading "Col|Pos" format 990
 column col heading "Column|Details" format a24
 column COLUMN_LENGTH heading "Col|Len" format 9,990
 column GLOBAL_STATS heading "Global|Stats" format a6
 column USER_STATS heading "User|Stats" format a6
 column SAMPLE_SIZE heading "Sample|Size" format 9,999,999,990
 column to_char(t.last_analyzed,'MM-DD-YYYY') heading "Date|MM-DD-YYYY" format a10
 prompt
 prompt ***********
 prompt Table Level
 prompt ***********
 prompt
 select 
     TABLE_NAME,
     NUM_ROWS,
     BLOCKS,
     EMPTY_BLOCKS,
     AVG_SPACE,
     CHAIN_CNT,
     AVG_ROW_LEN,
     GLOBAL_STATS,
     USER_STATS,
     SAMPLE_SIZE,
     to_char(t.last_analyzed,'MM-DD-YYYY')
 from dba_tables t
 where 
     owner = upper(nvl('&&Owner',user))
 and table_name = upper('&&Table_name')
 /
 select
     COLUMN_NAME,
     decode(t.DATA_TYPE,
            'NUMBER',t.DATA_TYPE||'('||
            decode(t.DATA_PRECISION,
                   null,t.DATA_LENGTH||')',
                   t.DATA_PRECISION||','||t.DATA_SCALE||')'),
                   'DATE',t.DATA_TYPE,
                   'LONG',t.DATA_TYPE,
                   'LONG RAW',t.DATA_TYPE,
                   'ROWID',t.DATA_TYPE,
                   'MLSLABEL',t.DATA_TYPE,
                   t.DATA_TYPE||'('||t.DATA_LENGTH||')') ||' '||
     decode(t.nullable,
               'N','NOT NULL',
               'n','NOT NULL',
               NULL) col,
     NUM_DISTINCT,
     DENSITY,
     NUM_BUCKETS,
     NUM_NULLS,
     GLOBAL_STATS,
     USER_STATS,
     SAMPLE_SIZE,
     to_char(t.last_analyzed,'MM-DD-YYYY')
 from dba_tab_columns t
 where 
     table_name = upper('&Table_name')
 and owner = upper(nvl('&Owner',user))
 /
 select 
     INDEX_NAME,
     UNIQUENESS,
     BLEVEL BLev,
     LEAF_BLOCKS,
     DISTINCT_KEYS,
     NUM_ROWS,
     AVG_LEAF_BLOCKS_PER_KEY,
     AVG_DATA_BLOCKS_PER_KEY,
     CLUSTERING_FACTOR,
     GLOBAL_STATS,
     USER_STATS,
     SAMPLE_SIZE,
     to_char(t.last_analyzed,'MM-DD-YYYY')
 from 
     dba_indexes t
 where 
     table_name = upper('&Table_name')
 and table_owner = upper(nvl('&Owner',user))
 /
 break on index_name
 select
     i.INDEX_NAME,
     i.COLUMN_NAME,
     i.COLUMN_POSITION,
     decode(t.DATA_TYPE,
            'NUMBER',t.DATA_TYPE||'('||
            decode(t.DATA_PRECISION,
                   null,t.DATA_LENGTH||')',
                   t.DATA_PRECISION||','||t.DATA_SCALE||')'),
                   'DATE',t.DATA_TYPE,
                   'LONG',t.DATA_TYPE,
                   'LONG RAW',t.DATA_TYPE,
                   'ROWID',t.DATA_TYPE,
                   'MLSLABEL',t.DATA_TYPE,
                   t.DATA_TYPE||'('||t.DATA_LENGTH||')') ||' '||
            decode(t.nullable,
                   'N','NOT NULL',
                   'n','NOT NULL',
                   NULL) col
 from 
     dba_ind_columns i,
     dba_tab_columns t
 where 
     i.table_name = upper('&Table_name')
 and owner = upper(nvl('&Owner',user))
 and i.table_name = t.table_name
 and i.column_name = t.column_name
 order by index_name,column_position
 /
 prompt
 prompt ***************
 prompt Partition Level
 prompt ***************
 select
     PARTITION_NAME,
     NUM_ROWS,
     BLOCKS,
     EMPTY_BLOCKS,
     AVG_SPACE,
     CHAIN_CNT,
     AVG_ROW_LEN,
     GLOBAL_STATS,
     USER_STATS,
     SAMPLE_SIZE,
     to_char(t.last_analyzed,'MM-DD-YYYY')
 from 
     dba_tab_partitions t
 where 
     table_owner = upper(nvl('&&Owner',user))
 and table_name = upper('&&Table_name')
 order by partition_position
 /
 break on partition_name
 select
     PARTITION_NAME,
     COLUMN_NAME,
     NUM_DISTINCT,
     DENSITY,
     NUM_BUCKETS,
     NUM_NULLS,
     GLOBAL_STATS,
     USER_STATS,
     SAMPLE_SIZE,
     to_char(t.last_analyzed,'MM-DD-YYYY')
 from 
     dba_PART_COL_STATISTICS t
 where 
     table_name = upper('&Table_name')
 and owner = upper(nvl('&Owner',user))
 /
 break on partition_name
 select 
     t.INDEX_NAME,
     t.PARTITION_NAME,
     t.BLEVEL BLev,
     t.LEAF_BLOCKS,
     t.DISTINCT_KEYS,
     t.NUM_ROWS,
     t.AVG_LEAF_BLOCKS_PER_KEY,
     t.AVG_DATA_BLOCKS_PER_KEY,
     t.CLUSTERING_FACTOR,
     t.GLOBAL_STATS,
     t.USER_STATS,
     t.SAMPLE_SIZE,
     to_char(t.last_analyzed,'MM-DD-YYYY')
 from 
     dba_ind_partitions t, 
     dba_indexes i
 where 
     i.table_name = upper('&Table_name')
 and i.table_owner = upper(nvl('&Owner',user))
 and i.owner = t.index_owner
 and i.index_name=t.index_name
 /
 prompt
 prompt ***************
 prompt SubPartition Level
 prompt ***************
 select 
     PARTITION_NAME,
     SUBPARTITION_NAME,
     NUM_ROWS,
     BLOCKS,
     EMPTY_BLOCKS,
     AVG_SPACE,
     CHAIN_CNT,
     AVG_ROW_LEN,
     GLOBAL_STATS,
     USER_STATS,
     SAMPLE_SIZE,
     to_char(t.last_analyzed,'MM-DD-YYYY')
 from 
     dba_tab_subpartitions t
 where 
     table_owner = upper(nvl('&&Owner',user))
 and table_name = upper('&&Table_name')
 order by SUBPARTITION_POSITION
 /
 break on partition_name
 select 
     p.PARTITION_NAME,
     t.SUBPARTITION_NAME,
     t.COLUMN_NAME,
     t.NUM_DISTINCT,
     t.DENSITY,
     t.NUM_BUCKETS,
     t.NUM_NULLS,
     t.GLOBAL_STATS,
     t.USER_STATS,
     t.SAMPLE_SIZE,
     to_char(t.last_analyzed,'MM-DD-YYYY')
 from 
     dba_SUBPART_COL_STATISTICS t, 
     dba_tab_subpartitions p
 where 
     t.table_name = upper('&Table_name')
 and t.owner = upper(nvl('&Owner',user))
 and t.subpartition_name = p.subpartition_name
 and t.owner = p.table_owner
 and t.table_name=p.table_name
 /
 break on partition_name
 select 
     t.INDEX_NAME,
     t.PARTITION_NAME,
     t.SUBPARTITION_NAME,
     t.BLEVEL BLev,
     t.LEAF_BLOCKS,
     t.DISTINCT_KEYS,
     t.NUM_ROWS,
     t.AVG_LEAF_BLOCKS_PER_KEY,
     t.AVG_DATA_BLOCKS_PER_KEY,
     t.CLUSTERING_FACTOR,
     t.GLOBAL_STATS,
     t.USER_STATS,
     t.SAMPLE_SIZE,
     to_char(t.last_analyzed,'MM-DD-YYYY')
 from 
     dba_ind_subpartitions t, 
     dba_indexes i
 where 
     i.table_name = upper('&Table_name')
 and i.table_owner = upper(nvl('&Owner',user))
 and i.owner = t.index_owner
 and i.index_name=t.index_name
 /
 clear breaks
 set echo on

View Code

执行过程如下：

SQL> @sosi.txt
SQL> set echo off


Tables owned by LIJIAMAN

INTERVAL_MONTH_TABLE01
TEST01


Please enter Name of Table Owner (Null = LIJIAMAN):            
Please enter Table Name to show Statistics for: TEST01        


***********
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

TEST01            23,732        496       16   1,589        0      133 NO     NO          2,905 05-29-2020


Column              Column               Distinct      Number     Number Global User         Sample Date
Name              Details             Values Density Buckets      Nulls Stats  Stats        Size MM-DD-YYYY

COL1              NUMBER(22) NOT NULL         20,000       0       1      0 YES      NO         20,000 05-29-2020
COL2              NUMBER(22)             20,000       0       1      0 YES      NO         20,000 05-29-2020
COL3              DATE                      9       0       1      0 YES      NO         20,000 05-29-2020
COL4              VARCHAR2(30)             19,908       0       1      0 YES      NO         20,000 05-29-2020
COL5              VARCHAR2(100)          19,918       0       1      0 YES      NO         20,000 05-29-2020


                  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

PK_COL1     UNIQUE          1   41         20,000        20,000         1         1        378 YES    NO          20,000 05-29-2020
IDX_COL2    NONUNIQUE                                        YES    NO


Index        Column               Col Column
Name        Name               Pos Details

IDX_COL2    COL2                 1 NUMBER(22)
PK_COL1     COL1                 1 NUMBER(22) NOT NULL


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


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

【完】

文章永久链接：https://tech.souyunku.com/?p=35200

[统计信息系列1] 统计信息概述

相关推荐

评论抢沙发

学习必备：精选优质专栏

大前端WP主题更专业更方便

觉得文章有用就打赏一下文章作者

微信扫一扫打赏

微信扫一扫打赏

QQ咨询

回顶部

相关推荐

评论 抢沙发

学习必备：精选优质专栏

大前端WP主题 更专业 更方便

觉得文章有用就打赏一下文章作者

微信扫一扫打赏

微信扫一扫打赏

QQ咨询

回顶部

评论抢沙发

大前端WP主题更专业更方便