Oracle 查看统计信息
SCRIPT - Select to show Optimizer Statistics for CBO (Doc ID 31412.1)
[oracle@my2950 admin]$ cd /u01/app/oracle/product/11.2.4/db_1/rdbms/admin
[oracle@my2950 admin]$ vi sosi.sql
内容:
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
运行的时候报错
SQL> @?/rdbms/admin/sosi.sql
SQL> set echo off
select table_name from dba_tables where owner=upper('SCOTT') order by 1
*
ERROR at line 1:
ORA-00942: table or view does not exist
Please enter Name of Table Owner (Null = SCOTT):
Please enter Table Name to show Statistics for: t2
***********
Table Level
***********
from dba_tables t
*
ERROR at line 13:
ORA-00942: table or view does not exist
from dba_tab_columns t
*
ERROR at line 26:
ORA-00942: table or view does not exist
dba_indexes t
*
ERROR at line 16:
ORA-00942: table or view does not exist
.........
SQL> show user;
USER is "SYS"
grant select on dba_tables to scott;
grant select on dba_tab_columns to scott;
grant select on dba_indexes to scott;
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL> grant select on dba_ind_columns to scott;
Grant succeeded.
SQL>
SQL>
再次执行
SQL> show user;
USER is "SCOTT"
SQL> @?/rdbms/admin/sosi.sql
SQL> set echo off
Tables owned by SCOTT
------------------------------
BONUS
DEPT
EMP
SALGRADE
T2
Please enter Name of Table Owner (Null = SCOTT):
Please enter Table Name to show Statistics for: t2
***********
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
--------------- ------------------ -------- ------------ ------- -------- ------- ------ ------ ------------------ ----------
T2 87,092 1,272 0 0 0 98 YES NO 87,092 09-10-2014
Column Column Distinct Number Number Global User Sample Date
Name Details Values Density Buckets Nulls Stats Stats Size MM-DD-YYYY
------------------------- ------------------------ ------------ ------- ------- ---------- ------ ------ ------------------ ----------
OWNER VARCHAR2(30) 25 0 1 0 YES NO 87,092 09-10-2014
OBJECT_NAME VARCHAR2(128) 52,637 0 1 0 YES NO 87,092 09-10-2014
SUBOBJECT_NAME VARCHAR2(30) 238 0 1 86,656 YES NO 436 09-10-2014
OBJECT_ID NUMBER(22) 87,092 0 1 0 YES NO 87,092 09-10-2014
DATA_OBJECT_ID NUMBER(22) 9,280 0 1 77,772 YES NO 9,320 09-10-2014
OBJECT_TYPE VARCHAR2(19) 44 0 1 0 YES NO 87,092 09-10-2014
CREATED DATE 1,571 0 1 0 YES NO 87,092 09-10-2014
LAST_DDL_TIME DATE 2,603 0 1 0 YES NO 87,092 09-10-2014
TIMESTAMP VARCHAR2(19) 2,583 0 1 0 YES NO 87,092 09-10-2014
STATUS VARCHAR2(7) 1 1 1 0 YES NO 87,092 09-10-2014
TEMPORARY VARCHAR2(1) 2 1 1 0 YES NO 87,092 09-10-2014
GENERATED VARCHAR2(1) 2 1 1 0 YES NO 87,092 09-10-2014
SECONDARY VARCHAR2(1) 2 1 1 0 YES NO 87,092 09-10-2014
NAMESPACE NUMBER(22) 20 0 1 0 YES NO 87,092 09-10-2014
EDITION_NAME VARCHAR2(30) 0 0 0 87,092 YES NO 09-10-2014
B Average Average
Index Tree Leaf Distinct Number Leaf Blocks Data Blocks Cluster Global User Sample
Name Unique Level Blks Keys of Rows Per Key Per Key Factor Stats Stats Size
--------------- --------- ----- ---- -------------- ------------------ ----------- ----------- ------------ ------ ------ ------------------
Date
MM-DD-YYYY
----------
IDX_T2 NONUNIQUE 1 193 87,092 87,092 1 1 1,936 YES NO 87,092
09-10-2014
Index Column Col Column
Name Name Pos Details
--------------- ------------------------- ---- ------------------------
IDX_T2 OBJECT_ID 1 NUMBER(22)
***************
Partition Level
***************
dba_tab_partitions t
*
ERROR at line 14:
ORA-00942: table or view does not exist
dba_PART_COL_STATISTICS t
*
ERROR at line 13:
ORA-00942: table or view does not exist
dba_ind_partitions t,
*
ERROR at line 16:
ORA-00942: table or view does not exist
***************
SubPartition Level
***************
dba_tab_subpartitions t
*
ERROR at line 15:
ORA-00942: table or view does not exist
dba_tab_subpartitions p
*
ERROR at line 15:
ORA-00942: table or view does not exist
dba_ind_subpartitions t,
*
ERROR at line 17:
ORA-00942: table or view does not exist
SQL>
如果要查看分区表的统计信息还有授权相关的表
SQL>
grant select on dba_tab_partitions to scott;
grant select on dba_PART_COL_STATISTICS to scott;
grant select on dba_ind_partitions to scott;
grant select on dba_tab_subpartitions to scott;
grant select on dba_ind_subpartitions to scott;
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
SQL> grant select on dba_SUBPART_COL_STATISTICS to scott;
Grant succeeded.
再次看看
SQL> show user;
USER is "SCOTT"
SQL> @?/rdbms/admin/sosi.sql
SQL> set echo off
Tables owned by SCOTT
------------------------------
BONUS
DEPT
EMP
SALGRADE
T2
Please enter Name of Table Owner (Null = SCOTT):
Please enter Table Name to show Statistics for: t2
***********
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
--------------- ------------------ -------- ------------ ------- -------- ------- ------ ------ ------------------ ----------
T2 87,092 1,272 0 0 0 98 YES NO 87,092 09-10-2014
Column Column Distinct Number Number Global User Sample Date
Name Details Values Density Buckets Nulls Stats Stats Size MM-DD-YYYY
------------------------- ------------------------ ------------ ------- ------- ---------- ------ ------ ------------------ ----------
OWNER VARCHAR2(30) 25 0 1 0 YES NO 87,092 09-10-2014
OBJECT_NAME VARCHAR2(128) 52,637 0 1 0 YES NO 87,092 09-10-2014
SUBOBJECT_NAME VARCHAR2(30) 238 0 1 86,656 YES NO 436 09-10-2014
OBJECT_ID NUMBER(22) 87,092 0 1 0 YES NO 87,092 09-10-2014
DATA_OBJECT_ID NUMBER(22) 9,280 0 1 77,772 YES NO 9,320 09-10-2014
OBJECT_TYPE VARCHAR2(19) 44 0 1 0 YES NO 87,092 09-10-2014
CREATED DATE 1,571 0 1 0 YES NO 87,092 09-10-2014
LAST_DDL_TIME DATE 2,603 0 1 0 YES NO 87,092 09-10-2014
TIMESTAMP VARCHAR2(19) 2,583 0 1 0 YES NO 87,092 09-10-2014
STATUS VARCHAR2(7) 1 1 1 0 YES NO 87,092 09-10-2014
TEMPORARY VARCHAR2(1) 2 1 1 0 YES NO 87,092 09-10-2014
GENERATED VARCHAR2(1) 2 1 1 0 YES NO 87,092 09-10-2014
SECONDARY VARCHAR2(1) 2 1 1 0 YES NO 87,092 09-10-2014
NAMESPACE NUMBER(22) 20 0 1 0 YES NO 87,092 09-10-2014
EDITION_NAME VARCHAR2(30) 0 0 0 87,092 YES NO 09-10-2014
B Average Average
Index Tree Leaf Distinct Number Leaf Blocks Data Blocks Cluster Global User Sample
Name Unique Level Blks Keys of Rows Per Key Per Key Factor Stats Stats Size
--------------- --------- ----- ---- -------------- ------------------ ----------- ----------- ------------ ------ ------ ------------------
Date
MM-DD-YYYY
----------
IDX_T2 NONUNIQUE 1 193 87,092 87,092 1 1 1,936 YES NO 87,092
09-10-2014
Index Column Col Column
Name Name Pos Details
--------------- ------------------------- ---- ------------------------
IDX_T2 OBJECT_ID 1 NUMBER(22)
***************
Partition Level
***************
***************
SubPartition Level
***************
SQL>
非常方便.
