微课sql优化(8)、统计信息收集(6)-统计信息查询
1、关于查询统计信息
- dba_tables
- prompt | ----------------1 dba_tables--------------------------------------------+^M
- column owner format a10 heading 'Owner' print entmap off
- column table_name format a15 heading 'Table_Name' print entmap off
- column NUM_ROWS format 999 ,999 ,999 ,999 heading 'Num_Rows' print entmap off
- column blocks format 999 ,999 ,999 heading 'Blocks' print entmap off
- column avg_row_len format 999 ,999 heading 'Avg_Row_len' print entmap off
- column LAST_ANALYZED format a20 heading 'Last_Analyzed' print entmap off
- column PARTITIONED format a5 heading 'Par' print entmap off
- column par_key format a10 heading 'Par_Key' print entmap off
- column subpar_key format a10 heading 'Subpar_Key' print entmap off
- column "ESTIMATE_PERCENT%" format a4 heading 'ESTIMATE_PERCENT%' print entmap off
- select t .OWNER ,
- t .TABLE_NAME ,
- t .NUM_ROWS ,
- blocks ,
- avg_row_len ,
- t .LAST_ANALYZED ,
- round ( nvl (t .SAMPLE_SIZE ,1 ) / nvl (t .NUM_ROWS ,1 ) ,2 ) *100 | | '%' "ESTIMATE_PERCENT%" ,
- t .PARTITIONED ,
- ( select nvl (m .column_name , 'null' )
- from dba_part_key_columns m
- where m .owner = t .OWNER
- and m .name = t .TABLE_NAME ) "par_key" ,
- ( select nvl (sm .column_name , 'null' )
- from dba_subpart_key_columns sm
- where sm .owner = t .OWNER
- and sm .name = t .TABLE_NAME ) "subpar_key"
- from dba_tables t
- where t .OWNER = upper ( '&TABLE_OWNER' )
- and t .TABLE_NAME = upper ( '&TABLE_NAME' )
- /
|----------------1 dba_tables--------------------------------------------+^M
Owner
prompt
|
----------------2 dba_tab_partitoins------------------------------------+^M
column p_name
format a10 heading
'p_NAME'
print entmap
off
select tp
.table_owner owner
,
tp
.table_name table_name
,
tp
.partition_name p_name
,
tp
.subpartition_count sp_count
,
tp
.num_rows NUM_ROWS
,
blocks
,
avg_row_len
,
tp
.last_analyzed
from dba_tab_partitions tp
where tp
.table_owner
=
upper
(
'&TABLE_OWNER'
)
and tp
.TABLE_NAME
=
upper
(
'&TABLE_NAME'
)
/
|----------------2 dba_tab_partitoins------------------------------------+^M
Owner
prompt
|
----------------3 dba_tab_subpartitions---------------------------------+
column sp_name
format a20 heading
'sp_NAME'
print entmap
off
select sp
.table_owner owner
,
sp
.table_name table_name
,
sp
.partition_name p_name
,
sp
.subpartition_name sp_name
,
sp
.num_rows NUM_ROWS
,
blocks
,
avg_row_len
,
sp
.last_analyzed
from dba_tab_subpartitions sp
where sp
.table_owner
=
upper
(
'&TABLE_OWNER'
)
and sp
.TABLE_NAME
=
upper
(
'&TABLE_NAME'
)
/
|----------------3 dba_tab_subpartitions---------------------------------+
- dba_tab_columns
- prompt | ----------------4 dba_tab_columns---------------------------------+
- column COLUMN_NAME format a20 heading 'COLUMN_NAME' print entmap off
- column HISTOGRAM format a10 heading 'HISTOGRAM' print entmap off
- select m .OWNER ,
- m .TABLE_NAME ,
- m .COLUMN_NAME ,
- m .NUM_DISTINCT ,
- m .HISTOGRAM ,
- m .NUM_NULLS ,
- m .LAST_ANALYZED
- from dba_tab_columns m
- where m .OWNER = upper ( '&TABLE_OWNER' )
- and m .TABLE_NAME = upper ( '&TABLE_NAME' )
- ORDER BY NUM_DISTINCT DESC ;
|----------------4 dba_tab_columns---------------------------------+
Owner
prompt
|
----------------5 dba_indexes---------------------------------+
column BL
format 99 heading
'BL'
print entmap
off
column cr
format a4 heading
'cr'
print entmap
off
column IDX_KEY
format a20 heading
'IDX_KEY'
print entmap
off
column uniq
format a4 heading
'uniq'
print entmap
off
column INDEX_NAME
format a20 heading
'INDEX_NAME'
print entmap
off
column par
format a3 heading
'par'
print entmap
off
select d
.OWNER
,
d
.INDEX_NAME
,
substr
(d
.uniqueness
,1
,4
) uniq
,
d
.blevel bl
,
d
.leaf_blocks
,
d
.clustering_factor c_factor
,
d
.num_rows
,
round
(
nvl
(d
.clustering_factor
,1
)
/
nvl
(d
.num_rows
,1
)
,2
)
*100
|
|
'%' cr
,
d
.distinct_keys d_keys
,
(
select m
.COLUMN_NAME
from dba_ind_columns m
where m
.INDEX_OWNER
= d
.OWNER
and m
.INDEX_NAME
= d
.INDEX_NAME
and m
.COLUMN_POSITION
= 1
)
|
|
(
select
','
|
| m
.COLUMN_NAME
from dba_ind_columns m
where m
.INDEX_OWNER
= d
.OWNER
and m
.INDEX_NAME
= d
.INDEX_NAME
and m
.COLUMN_POSITION
= 2
)
|
|
(
select
','
|
| m
.COLUMN_NAME
from dba_ind_columns m
where m
.INDEX_OWNER
= d
.OWNER
and m
.INDEX_NAME
= d
.INDEX_NAME
and m
.COLUMN_POSITION
= 3
)
|
|
(
select
','
|
| m
.COLUMN_NAME
from dba_ind_columns m
where m
.INDEX_OWNER
= d
.OWNER
and m
.INDEX_NAME
= d
.INDEX_NAME
and m
.COLUMN_POSITION
= 4
) idx_key
,
d
.partitioned par
from dba_indexes d
where d
.table_owner
=
upper
(
'&TABLE_OWNER'
)
and d
.TABLE_NAME
=
upper
(
'&TABLE_NAME'
)
order
by 1
, 2
desc
/