微课sql优化(8)、统计信息收集(6)-统计信息查询

发布时间：2020-08-16 02:46:05 作者：orastar
来源：ITPUB博客阅读：161

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

|----------------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' )

|----------------6 dba_tab_modifications----------------------------------+^M

dba_tab_statistics

prompt | ----------------7 dba_tab_statistics------------------------------------+^M
column object_type format a15 heading 'object_type' print entmap off
select owner , table_name , object_type , stale_stats ,
num_rows ,
sample_size ,
trunc (sample_size / num_rows * 100 ) estimate_percent ,
last_analyzed
from dba_tab_statistics
where OWNER = upper ( '&TABLE_OWNER' )
and TABLE_NAME = upper ( '&TABLE_NAME' )
/

|----------------7 dba_tab_statistics------------------------------------+^M

Owner

* * * * * * * Oracle 10G parallel 8 HP - UX nopartitioned * * * * * * * * * * * * * * * * * * * * * *

table_name table_size (M ) index_size (M ) total (M ) time

--------- ------------ ------------ ------- --------------------

tab1 488 0 488 Elapsed : 00 :00 :27 .99

tab2 1 ,115 331 1 ,446 Elapsed : 00 :00 :06 .28

tab3 2 ,019 243 2 ,262 Elapsed : 00 :00 :44 .30

tab4 3 ,171 1 ,221 4 ,392 Elapsed : 00 :02 :17 .08

tab5 4 ,756 0 4 ,756 Elapsed : 00 :05 :42 .85

tab6 15 ,146 16 ,059 31 ,205 Elapsed : 00 :29 :59 .14

tab7 8 ,105 4 ,820 12 ,925 Elapsed : 00 :26 :12 .52

微课sql优化(8)、统计信息收集(6)-统计信息查询

1、关于查询统计信息

相关阅读