数据库中如何查看统计信息

小编给大家分享一下数据库中如何查看统计信息，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

set echo offset scan onset lines 150set pages 66set verify offset feedback offset termout offcolumn uservar new_value Table_Owner noprintselect user uservar from dual;set termout oncolumn TABLE_NAME heading "Tables owned by &Table_Owner" format a30select table_name from dba_tables where owner=upper('&Table_Owner') order by 1/undefine table_nameundefine ownerpromptaccept 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 a15column PARTITION_NAME heading "Partition|Name" format a15column SUBPARTITION_NAME heading "SubPartition|Name" format a15column NUM_ROWS heading "Number|of Rows" format 9,999,999,999,990column BLOCKS heading "Blocks" format 999,990column EMPTY_BLOCKS heading "Empty|Blocks" format 999,999,990column AVG_SPACE heading "Average|Space" format 9,990column CHAIN_CNT heading "Chain|Count" format 999,990column AVG_ROW_LEN heading "Average|Row Len" format 990column COLUMN_NAME  heading "Column|Name" format a25column NULLABLE heading Null|able format a4column NUM_DISTINCT heading "Distinct|Values" format 999,999,990column NUM_NULLS heading "Number|Nulls" format 9,999,990column NUM_BUCKETS heading "Number|Buckets" format 990column DENSITY heading "Density" format 990column INDEX_NAME heading "Index|Name" format a15column UNIQUENESS heading "Unique" format a9column BLEV heading "B|Tree|Level" format 90column LEAF_BLOCKS heading "Leaf|Blks" format 990column DISTINCT_KEYS heading "Distinct|Keys" format 9,999,999,990column AVG_LEAF_BLOCKS_PER_KEY heading "Average|Leaf Blocks|Per Key" format 99,990column AVG_DATA_BLOCKS_PER_KEY heading "Average|Data Blocks|Per Key" format 99,990column CLUSTERING_FACTOR heading "Cluster|Factor" format 999,999,990column COLUMN_POSITION heading "Col|Pos" format 990column col heading "Column|Details" format a24column COLUMN_LENGTH heading "Col|Len" format 9,990column GLOBAL_STATS heading "Global|Stats" format a6column USER_STATS heading "User|Stats" format a6column SAMPLE_SIZE heading "Sample|Size" format 9,999,999,999,990column to_char(t.last_analyzed,'MM-DD-YYYY') heading "Date|MM-DD-YYYY" format a10promptprompt ***********prompt Table Levelprompt ***********promptselect     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 twhere     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 twhere     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 twhere     table_name = upper('&Table_name')and table_owner = upper(nvl('&Owner',user))/break on index_nameselect    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) colfrom     dba_ind_columns i,    dba_tab_columns twhere     i.table_name = upper('&Table_name')and owner = upper(nvl('&Owner',user))and i.table_name = t.table_nameand i.column_name = t.column_nameorder by index_name,column_position/promptprompt ***************prompt Partition Levelprompt ***************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 twhere     table_owner = upper(nvl('&&Owner',user))and table_name = upper('&&Table_name')order by partition_position/break on partition_nameselect    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 twhere     table_name = upper('&Table_name')and owner = upper(nvl('&Owner',user))/break on partition_nameselect     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 iwhere     i.table_name = upper('&Table_name')and i.table_owner = upper(nvl('&Owner',user))and i.owner = t.index_ownerand i.index_name=t.index_name/promptprompt ***************prompt SubPartition Levelprompt ***************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 twhere     table_owner = upper(nvl('&&Owner',user))and table_name = upper('&&Table_name')order by SUBPARTITION_POSITION/break on partition_nameselect     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 pwhere     t.table_name = upper('&Table_name')and t.owner = upper(nvl('&Owner',user))and t.subpartition_name = p.subpartition_nameand t.owner = p.table_ownerand t.table_name=p.table_name/break on partition_nameselect     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 iwhere     i.table_name = upper('&Table_name')and i.table_owner = upper(nvl('&Owner',user))and i.owner = t.index_ownerand i.index_name=t.index_name/clear breaksset echo on

***********Table Level***********Table               Number             Empty Average Chain Average Global User       Sample DateName               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-2003Column                    Column                   Distinct          Number            Global User    Sample DateName                      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-2003T1C2                      NUMBER(22)                      7       0       1          0 YES    NO          5,000 06-30-2003T1C3                      NUMBER(22)                      8       0       1          0 YES    NO          5,000 06-30-2003                              B                                Average     AverageIndex                      Tree Leaf   Distinct     Number Leaf Blocks Data Blocks  Cluster Global User       Sample DateName            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-2003Index           Column                     Col ColumnName            Name                       Pos Details--------------- ------------------------- ---- ------------------------UN_T1           T1C1                         1 NUMBER(22)***************Partition Level***************Partition           Number             Empty Average Chain Average Global User       Sample DateName               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-2003Q2                   2,856       10        0       0     0       9 YES    NO          2,856 06-30-2003Partition       Column                    Distinct          Number            Global User       Sample DateName            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-2003Q2              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     AverageIndex           Partition        Tree Leaf   Distinct     Number Leaf Blocks Data Blocks  Cluster Global User       Sample DateName            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-2003UN_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 DateName            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-2003Q2              SYS_SUBP499          1,072        5        0       0     0       9 YES    NO          1,072 06-30-2003Q1              SYS_SUBP498          1,341        5        0       0     0       9 YES    NO          1,341 06-30-2003Q2              SYS_SUBP500          1,784        5        0       0     0       9 YES    NO          1,784 06-30-2003Partition       SubPartition    Column                    Distinct          Number            Global User       Sample DateName            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-2003Q2              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     AverageIndex           Partition       SubPartition     Tree Leaf   Distinct     Number Leaf Blocks Data Blocks  Cluster Global User       Sample DateName            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-200UN_T1                           SYS_SUBP498         1    3      1,341      1,341           1           1        3 YES    NO          1,341 06-30-200UN_T1           Q2              SYS_SUBP499         1    3      1,072      1,072           1           1        3 YES    NO          1,072 06-30-200UN_T1                           SYS_SUBP500         1    4      1,784      1,784           1           1        4 YES    NO          1,784 06-30-200

以上是"数据库中如何查看统计信息"这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注行业资讯频道！