微课sql优化(8)、统计信息收集(6)-统计信息查询
1、关于查询统计信息
-
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---------------------------------+
-
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
/ |----------------5 dba_indexes---------------------------------+
Owner
prompt
|
----------------6 dba_tab_modifications----------------------------------+^M
select table_owner
, table_name
, partition_name p_name
, subpartition_name sp_name
, inserts
, updates
, deletes
from dba_tab_modifications
where table_owner
=
upper
(
'&TABLE_OWNER'
)
and TABLE_NAME
=
upper
(
'&TABLE_NAME'
)
/ 
