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

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

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

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

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