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

    image

    （一）统计信息分类

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

    统计信息分类 作用
    表的统计信息 包含记录数、表块的数量、平均行长度等
    索引的统计信息 包含索引的高度、叶子块的数量、聚簇因子等
    列的统计信息 包含列的distinct值、列的null值的数量、列的最小值、列的最大值以及直方图等
    系统统计信息

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

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

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

    --收集索引的统计信息
analyze index pk_id compute statistics;

-- 删除索引pk_id的统计信息
analyze index pk_id delete statistics;

-- 只对表收集统计信息，不对列和索引收集，以采样20%的方式收集
analyze table test01 estimate statistics sample 20 percent for table;

-- 收集test01的col1和col2的统计信息
analyze table test01 compute statistics for columns col1,col2;

-- 一次性收集test01 表、列、索引的统计信息
analyze table test01 compute statistics;

-- 删除test01 表、列、索引的统计信息
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脚本如下：

      1 set echo off
  2 set scan on
  3 set lines 150
  4 set pages 66
  5 set verify off
  6 set feedback off
  7 set termout off
  8 column uservar new_value Table_Owner noprint
  9 select user uservar from dual;
 10 set termout on
 11 column TABLE_NAME heading "Tables owned by &Table_Owner" format a30
 12 select table_name from dba_tables where owner=upper('&Table_Owner') order by 1
 13 /
 14 undefine table_name
 15 undefine owner
 16 prompt
 17 accept owner prompt 'Please enter Name of Table Owner (Null = &Table_Owner): '
 18 accept table_name  prompt 'Please enter Table Name to show Statistics for: '
 19 column TABLE_NAME heading "Table|Name" format a15
 20 column PARTITION_NAME heading "Partition|Name" format a15
 21 column SUBPARTITION_NAME heading "SubPartition|Name" format a15
 22 column NUM_ROWS heading "Number|of Rows" format 9,999,999,990
 23 column BLOCKS heading "Blocks" format 999,990
 24 column EMPTY_BLOCKS heading "Empty|Blocks" format 999,999,990
 25 column AVG_SPACE heading "Average|Space" format 9,990
 26 column CHAIN_CNT heading "Chain|Count" format 999,990
 27 column AVG_ROW_LEN heading "Average|Row Len" format 990
 28 column COLUMN_NAME  heading "Column|Name" format a25
 29 column NULLABLE heading Null|able format a4
 30 column NUM_DISTINCT heading "Distinct|Values" format 999,999,990
 31 column NUM_NULLS heading "Number|Nulls" format 9,999,990
 32 column NUM_BUCKETS heading "Number|Buckets" format 990
 33 column DENSITY heading "Density" format 990
 34 column INDEX_NAME heading "Index|Name" format a15
 35 column UNIQUENESS heading "Unique" format a9
 36 column BLEV heading "B|Tree|Level" format 90
 37 column LEAF_BLOCKS heading "Leaf|Blks" format 990
 38 column DISTINCT_KEYS heading "Distinct|Keys" format 9,999,999,990
 39 column AVG_LEAF_BLOCKS_PER_KEY heading "Average|Leaf Blocks|Per Key" format 99,990
 40 column AVG_DATA_BLOCKS_PER_KEY heading "Average|Data Blocks|Per Key" format 99,990
 41 column CLUSTERING_FACTOR heading "Cluster|Factor" format 999,999,990
 42 column COLUMN_POSITION heading "Col|Pos" format 990
 43 column col heading "Column|Details" format a24
 44 column COLUMN_LENGTH heading "Col|Len" format 9,990
 45 column GLOBAL_STATS heading "Global|Stats" format a6
 46 column USER_STATS heading "User|Stats" format a6
 47 column SAMPLE_SIZE heading "Sample|Size" format 9,999,999,990
 48 column to_char(t.last_analyzed,'MM-DD-YYYY') heading "Date|MM-DD-YYYY" format a10
 49 prompt
 50 prompt ***********
 51 prompt Table Level
 52 prompt ***********
 53 prompt
 54 select 
 55     TABLE_NAME,
 56     NUM_ROWS,
 57     BLOCKS,
 58     EMPTY_BLOCKS,
 59     AVG_SPACE,
 60     CHAIN_CNT,
 61     AVG_ROW_LEN,
 62     GLOBAL_STATS,
 63     USER_STATS,
 64     SAMPLE_SIZE,
 65     to_char(t.last_analyzed,'MM-DD-YYYY')
 66 from dba_tables t
 67 where 
 68     owner = upper(nvl('&&Owner',user))
 69 and table_name = upper('&&Table_name')
 70 /
 71 select
 72     COLUMN_NAME,
 73     decode(t.DATA_TYPE,
 74            'NUMBER',t.DATA_TYPE||'('||
 75            decode(t.DATA_PRECISION,
 76                   null,t.DATA_LENGTH||')',
 77                   t.DATA_PRECISION||','||t.DATA_SCALE||')'),
 78                   'DATE',t.DATA_TYPE,
 79                   'LONG',t.DATA_TYPE,
 80                   'LONG RAW',t.DATA_TYPE,
 81                   'ROWID',t.DATA_TYPE,
 82                   'MLSLABEL',t.DATA_TYPE,
 83                   t.DATA_TYPE||'('||t.DATA_LENGTH||')') ||' '||
 84     decode(t.nullable,
 85               'N','NOT NULL',
 86               'n','NOT NULL',
 87               NULL) col,
 88     NUM_DISTINCT,
 89     DENSITY,
 90     NUM_BUCKETS,
 91     NUM_NULLS,
 92     GLOBAL_STATS,
 93     USER_STATS,
 94     SAMPLE_SIZE,
 95     to_char(t.last_analyzed,'MM-DD-YYYY')
 96 from dba_tab_columns t
 97 where 
 98     table_name = upper('&Table_name')
 99 and owner = upper(nvl('&Owner',user))
100 /
101 select 
102     INDEX_NAME,
103     UNIQUENESS,
104     BLEVEL BLev,
105     LEAF_BLOCKS,
106     DISTINCT_KEYS,
107     NUM_ROWS,
108     AVG_LEAF_BLOCKS_PER_KEY,
109     AVG_DATA_BLOCKS_PER_KEY,
110     CLUSTERING_FACTOR,
111     GLOBAL_STATS,
112     USER_STATS,
113     SAMPLE_SIZE,
114     to_char(t.last_analyzed,'MM-DD-YYYY')
115 from 
116     dba_indexes t
117 where 
118     table_name = upper('&Table_name')
119 and table_owner = upper(nvl('&Owner',user))
120 /
121 break on index_name
122 select
123     i.INDEX_NAME,
124     i.COLUMN_NAME,
125     i.COLUMN_POSITION,
126     decode(t.DATA_TYPE,
127            'NUMBER',t.DATA_TYPE||'('||
128            decode(t.DATA_PRECISION,
129                   null,t.DATA_LENGTH||')',
130                   t.DATA_PRECISION||','||t.DATA_SCALE||')'),
131                   'DATE',t.DATA_TYPE,
132                   'LONG',t.DATA_TYPE,
133                   'LONG RAW',t.DATA_TYPE,
134                   'ROWID',t.DATA_TYPE,
135                   'MLSLABEL',t.DATA_TYPE,
136                   t.DATA_TYPE||'('||t.DATA_LENGTH||')') ||' '||
137            decode(t.nullable,
138                   'N','NOT NULL',
139                   'n','NOT NULL',
140                   NULL) col
141 from 
142     dba_ind_columns i,
143     dba_tab_columns t
144 where 
145     i.table_name = upper('&Table_name')
146 and owner = upper(nvl('&Owner',user))
147 and i.table_name = t.table_name
148 and i.column_name = t.column_name
149 order by index_name,column_position
150 /
151 prompt
152 prompt ***************
153 prompt Partition Level
154 prompt ***************
155 select
156     PARTITION_NAME,
157     NUM_ROWS,
158     BLOCKS,
159     EMPTY_BLOCKS,
160     AVG_SPACE,
161     CHAIN_CNT,
162     AVG_ROW_LEN,
163     GLOBAL_STATS,
164     USER_STATS,
165     SAMPLE_SIZE,
166     to_char(t.last_analyzed,'MM-DD-YYYY')
167 from 
168     dba_tab_partitions t
169 where 
170     table_owner = upper(nvl('&&Owner',user))
171 and table_name = upper('&&Table_name')
172 order by partition_position
173 /
174 break on partition_name
175 select
176     PARTITION_NAME,
177     COLUMN_NAME,
178     NUM_DISTINCT,
179     DENSITY,
180     NUM_BUCKETS,
181     NUM_NULLS,
182     GLOBAL_STATS,
183     USER_STATS,
184     SAMPLE_SIZE,
185     to_char(t.last_analyzed,'MM-DD-YYYY')
186 from 
187     dba_PART_COL_STATISTICS t
188 where 
189     table_name = upper('&Table_name')
190 and owner = upper(nvl('&Owner',user))
191 /
192 break on partition_name
193 select 
194     t.INDEX_NAME,
195     t.PARTITION_NAME,
196     t.BLEVEL BLev,
197     t.LEAF_BLOCKS,
198     t.DISTINCT_KEYS,
199     t.NUM_ROWS,
200     t.AVG_LEAF_BLOCKS_PER_KEY,
201     t.AVG_DATA_BLOCKS_PER_KEY,
202     t.CLUSTERING_FACTOR,
203     t.GLOBAL_STATS,
204     t.USER_STATS,
205     t.SAMPLE_SIZE,
206     to_char(t.last_analyzed,'MM-DD-YYYY')
207 from 
208     dba_ind_partitions t, 
209     dba_indexes i
210 where 
211     i.table_name = upper('&Table_name')
212 and i.table_owner = upper(nvl('&Owner',user))
213 and i.owner = t.index_owner
214 and i.index_name=t.index_name
215 /
216 prompt
217 prompt ***************
218 prompt SubPartition Level
219 prompt ***************
220 select 
221     PARTITION_NAME,
222     SUBPARTITION_NAME,
223     NUM_ROWS,
224     BLOCKS,
225     EMPTY_BLOCKS,
226     AVG_SPACE,
227     CHAIN_CNT,
228     AVG_ROW_LEN,
229     GLOBAL_STATS,
230     USER_STATS,
231     SAMPLE_SIZE,
232     to_char(t.last_analyzed,'MM-DD-YYYY')
233 from 
234     dba_tab_subpartitions t
235 where 
236     table_owner = upper(nvl('&&Owner',user))
237 and table_name = upper('&&Table_name')
238 order by SUBPARTITION_POSITION
239 /
240 break on partition_name
241 select 
242     p.PARTITION_NAME,
243     t.SUBPARTITION_NAME,
244     t.COLUMN_NAME,
245     t.NUM_DISTINCT,
246     t.DENSITY,
247     t.NUM_BUCKETS,
248     t.NUM_NULLS,
249     t.GLOBAL_STATS,
250     t.USER_STATS,
251     t.SAMPLE_SIZE,
252     to_char(t.last_analyzed,'MM-DD-YYYY')
253 from 
254     dba_SUBPART_COL_STATISTICS t, 
255     dba_tab_subpartitions p
256 where 
257     t.table_name = upper('&Table_name')
258 and t.owner = upper(nvl('&Owner',user))
259 and t.subpartition_name = p.subpartition_name
260 and t.owner = p.table_owner
261 and t.table_name=p.table_name
262 /
263 break on partition_name
264 select 
265     t.INDEX_NAME,
266     t.PARTITION_NAME,
267     t.SUBPARTITION_NAME,
268     t.BLEVEL BLev,
269     t.LEAF_BLOCKS,
270     t.DISTINCT_KEYS,
271     t.NUM_ROWS,
272     t.AVG_LEAF_BLOCKS_PER_KEY,
273     t.AVG_DATA_BLOCKS_PER_KEY,
274     t.CLUSTERING_FACTOR,
275     t.GLOBAL_STATS,
276     t.USER_STATS,
277     t.SAMPLE_SIZE,
278     to_char(t.last_analyzed,'MM-DD-YYYY')
279 from 
280     dba_ind_subpartitions t, 
281     dba_indexes i
282 where 
283     i.table_name = upper('&Table_name')
284 and i.table_owner = upper(nvl('&Owner',user))
285 and i.owner = t.index_owner
286 and i.index_name=t.index_name
287 /
288 clear breaks
289 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):            --这里输入scheme
Please enter Table Name to show Statistics for: TEST01        --这里输入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
***************
     

    【完】
  • 相关阅读:
    CSRF攻击与防御
    別惹我
    单系统登录机制SSO
    jdk8- list操作
    mybatis--Mapper 常见报错总结(持续总结)
    死锁排查的小窍门 --使用jdk自带管理工具jstack
    oracle笔记--查询10条之后记录的数据
    zookeeper集群操作【这里只说明简单的操作步骤,zk的相关参数、说明请参考官方文档】
    利用jquery操作隐藏table某一列
    关于python3.X 报"import urllib.request ImportError: No module named request"错误,解决办法
  • 原文地址：https://www.cnblogs.com/lijiaman/p/12994409.html
Copyright © 2020-2023  润新知