微課sql最佳化(8)、統計資訊收集(6)-統計資訊查詢

orastar發表於2020-03-03
SQL

1、關於查詢統計資訊

  • dba_tables
  1. prompt   | ----------------1 dba_tables--------------------------------------------+^M
  2. column owner  format a10 heading  'Owner'  print entmap  off
  3. column table_name  format a15 heading  'Table_Name'  print entmap  off
  4. column NUM_ROWS  format 999 ,999 ,999 ,999 heading  'Num_Rows'  print entmap  off
  5. column blocks  format 999 ,999 ,999 heading  'Blocks'  print entmap  off
  6. column avg_row_len  format 999 ,999 heading  'Avg_Row_len'  print entmap  off
  7. column LAST_ANALYZED  format a20 heading  'Last_Analyzed'  print entmap  off
  8. column PARTITIONED  format a5 heading  'Par'  print entmap  off
  9. column par_key  format a10 heading  'Par_Key'  print entmap  off
  10. column subpar_key  format a10 heading  'Subpar_Key'  print entmap  off
  11. column  "ESTIMATE_PERCENT%"  format a4 heading  'ESTIMATE_PERCENT%'  print entmap  off 
  12. select t .OWNER ,
  13.        t .TABLE_NAME ,
  14.        t .NUM_ROWS ,
  15.        blocks ,
  16.        avg_row_len ,
  17.        t .LAST_ANALYZED ,
  18.         round ( nvl (t .SAMPLE_SIZE ,1 ) / nvl (t .NUM_ROWS ,1 ) ,2 ) *100 | | '%'  "ESTIMATE_PERCENT%" ,
  19.        t .PARTITIONED ,
  20.         ( select  nvl (m .column_name ,  'null' )
  21.            from dba_part_key_columns m
  22.           where m .owner  = t .OWNER
  23.             and m .name  = t .TABLE_NAME )  "par_key" ,
  24.         ( select  nvl (sm .column_name ,  'null' )
  25.            from dba_subpart_key_columns sm
  26.           where sm .owner  = t .OWNER
  27.             and sm .name  = t .TABLE_NAME )  "subpar_key"
  28.    from dba_tables t
  29.   where t .OWNER  =  upper ( '&TABLE_OWNER' )
  30.     and t .TABLE_NAME  =  upper ( '&TABLE_NAME' )
  31. /
|----------------1 dba_tables--------------------------------------------+^M
Owner       Table_Name       Num_Rows      Blocks Avg_Row_len Last_Analyzed      ESTI Par   Par_Key   Subpar_Key
---------- --------------- ---------------- ------------ ----------- -------------------- ---- ----- ---------- ----------
HT       A_AMT_P1         59,968         276      22 2017-08-19 18:33:51  100% YES   AMT_YM
  • dba_tab_partitoins
  1. prompt   | ----------------2 dba_tab_partitoins------------------------------------+^M
  2. column p_name  format a10 heading  'p_NAME'  print entmap  off
  3. select tp .table_owner owner ,
  4.        tp .table_name table_name ,
  5.        tp .partition_name p_name ,
  6.        tp .subpartition_count sp_count ,
  7.        tp .num_rows NUM_ROWS ,
  8.        blocks ,
  9.        avg_row_len ,
  10.        tp .last_analyzed
  11.    from dba_tab_partitions tp
  12. where tp .table_owner  =  upper ( '&TABLE_OWNER' )
  13.     and tp .TABLE_NAME  =  upper ( '&TABLE_NAME' )
  14. /
|----------------2 dba_tab_partitoins------------------------------------+^M
Owner       Table_Name       p_NAME   SP_COUNT     Num_Rows   Blocks Avg_Row_len Last_Analyzed
---------- --------------- ---------- ---------- ---------------- ------------ ----------- --------------------
HT       A_AMT_P1       P01           0        9,998        46   21 2017-08-19 18:33:51
HT       A_AMT_P1       P02           0        9,987        46   22 2017-08-19 18:33:51
HT       A_AMT_P1       P03           0        9,994        46   22 2017-08-19 18:33:51
HT       A_AMT_P1       P04           0        9,993        46   22 2017-08-19 18:33:51
HT       A_AMT_P1       P05           0        9,998        46   22 2017-08-19 18:33:51
HT       A_AMT_P1       P06           0        9,998        46   22 2017-08-19 18:33:51
  • dba_tab_subpartitions
  1. prompt   | ----------------3 dba_tab_subpartitions---------------------------------+
  2. column sp_name  format a20 heading  'sp_NAME'  print entmap  off
  3. select sp .table_owner owner ,
  4.        sp .table_name table_name ,
  5.        sp .partition_name p_name ,
  6.        sp .subpartition_name sp_name ,
  7.        sp .num_rows NUM_ROWS ,
  8.        blocks ,
  9.        avg_row_len ,
  10.        sp .last_analyzed
  11.     from dba_tab_subpartitions sp
  12. where sp .table_owner  =  upper ( '&TABLE_OWNER' )
  13.     and sp .TABLE_NAME  =  upper ( '&TABLE_NAME' )
  14. /
|----------------3 dba_tab_subpartitions---------------------------------+
  • dba_tab_columns
  1. prompt   | ----------------4 dba_tab_columns---------------------------------+
  2. column COLUMN_NAME  format a20 heading  'COLUMN_NAME'  print entmap  off
  3. column HISTOGRAM  format a10 heading  'HISTOGRAM'  print entmap  off
  4. select m .OWNER ,
  5.        m .TABLE_NAME ,
  6.        m .COLUMN_NAME ,
  7.        m .NUM_DISTINCT ,
  8.        m .HISTOGRAM ,
  9.        m .NUM_NULLS ,
  10.        m .LAST_ANALYZED
  11.    from dba_tab_columns m
  12. where m .OWNER  =  upper ( '&TABLE_OWNER' )
  13.     and m .TABLE_NAME  =  upper ( '&TABLE_NAME' )
  14.     ORDER  BY NUM_DISTINCT  DESC ;
|----------------4 dba_tab_columns---------------------------------+
Owner       Table_Name       COLUMN_NAME   NUM_DISTINCT HISTOGRAM     NUM_NULLS Last_Analyzed
---------- --------------- -------------------- ------------ ---------- ---------- --------------------
HT       A_AMT_P1       AMT_ID           59968 NONE     0 2017-08-19 18:33:51
HT       A_AMT_P1       AMT           59968 NONE     0 2017-08-19 18:33:51
HT       A_AMT_P1       CONS_NO           10000 NONE     0 2017-08-19 18:33:51
HT       A_AMT_P1       AMT_YM       6 NONE     0 2017-08-19 18:33:51
  • dba_indexes
  1. prompt   | ----------------5 dba_indexes---------------------------------+
  2. column BL  format 99 heading  'BL'  print entmap  off
  3. column cr  format a4 heading  'cr'  print entmap  off
  4. column IDX_KEY  format a20 heading  'IDX_KEY'  print entmap  off
  5. column uniq  format a4 heading  'uniq'  print entmap  off
  6. column INDEX_NAME  format a20 heading  'INDEX_NAME'  print entmap  off
  7. column par  format a3 heading  'par'  print entmap  off
  8. select d .OWNER ,
  9.        d .INDEX_NAME ,
  10.         substr (d .uniqueness ,1 ,4 ) uniq ,
  11.        d .blevel bl ,
  12.        d .leaf_blocks ,
  13.        d .clustering_factor c_factor ,
  14.        d .num_rows ,
  15.         round ( nvl (d .clustering_factor ,1 ) / nvl (d .num_rows ,1 ) ,2 ) *100 | | '%' cr ,
  16.        d .distinct_keys d_keys ,
  17.         ( select m .COLUMN_NAME
  18.            from dba_ind_columns m
  19.           where m .INDEX_OWNER  = d .OWNER
  20.             and m .INDEX_NAME  = d .INDEX_NAME
  21.             and m .COLUMN_POSITION  = 1 )  | |
  22.         ( select  ','  | | m .COLUMN_NAME
  23.            from dba_ind_columns m
  24.           where m .INDEX_OWNER  = d .OWNER
  25.             and m .INDEX_NAME  = d .INDEX_NAME
  26.             and m .COLUMN_POSITION  = 2 )  | |
  27.         ( select  ','  | | m .COLUMN_NAME
  28.            from dba_ind_columns m
  29.           where m .INDEX_OWNER  = d .OWNER
  30.             and m .INDEX_NAME  = d .INDEX_NAME
  31.             and m .COLUMN_POSITION  = 3 )  | |
  32.         ( select  ','  | | m .COLUMN_NAME
  33.            from dba_ind_columns m
  34.           where m .INDEX_OWNER  = d .OWNER
  35.             and m .INDEX_NAME  = d .INDEX_NAME
  36.             and m .COLUMN_POSITION  = 4 ) idx_key ,
  37.        d .partitioned par
  38.    from dba_indexes d
  39. where d .table_owner  =  upper ( '&TABLE_OWNER' )
  40.     and d .TABLE_NAME  =  upper ( '&TABLE_NAME' )
  41.   order  by 1 , 2  desc
  42. /
|----------------5 dba_indexes---------------------------------+
Owner       INDEX_NAME   uniq  BL LEAF_BLOCKS   C_FACTOR     Num_Rows cr      D_KEYS IDX_KEY          par
---------- -------------------- ---- --- ----------- ---------- ---------------- ---- ---------- -------------------- ---
HT       SYS_C0011127     UNIQ   1     262        357        59,968 1%       59968 AMT_ID            NO
  • dba_tab_modifications
  1. prompt   | ----------------6 dba_tab_modifications----------------------------------+^M
  2. select table_owner ,
  3.        table_name ,
  4.        partition_name p_name ,
  5.        subpartition_name sp_name ,
  6.        inserts ,
  7.        updates ,
  8.        deletes
  9.    from dba_tab_modifications
  10.   where table_owner  =  upper ( '&TABLE_OWNER' )
  11.     and TABLE_NAME  =  upper ( '&TABLE_NAME' )
  12. /
|----------------6 dba_tab_modifications----------------------------------+^M
  • dba_tab_statistics
  1. prompt   | ----------------7 dba_tab_statistics------------------------------------+^M  
  2. column object_type  format a15 heading  'object_type'  print entmap  off
  3. select owner , table_name , object_type , stale_stats , 
  4. num_rows ,
  5. sample_size ,
  6. trunc (sample_size  / num_rows  * 100 ) estimate_percent ,
  7. last_analyzed
  8.    from dba_tab_statistics
  9.   where OWNER  =  upper ( '&TABLE_OWNER' )
  10.     and TABLE_NAME  =  upper ( '&TABLE_NAME' )
  11. /
|----------------7 dba_tab_statistics------------------------------------+^M
Owner       Table_Name       object_type       STALE_STA         Num_Rows SAMPLE_SIZE ESTIMATE_PERCENT Last_Analyzed
---------- --------------- --------------- --------- ---------------- ----------- ---------------- --------------------
HT       A_AMT_P1       TABLE       NO           59,968        59968           100 2017-08-19 18:33:51
HT       A_AMT_P1       PARTITION       NO   9,998         9998           100 2017-08-19 18:33:51
HT       A_AMT_P1       PARTITION       NO   9,987         9987           100 2017-08-19 18:33:51
HT       A_AMT_P1       PARTITION       NO   9,994         9994           100 2017-08-19 18:33:51
HT       A_AMT_P1       PARTITION       NO   9,993         9993           100 2017-08-19 18:33:51
HT       A_AMT_P1       PARTITION       NO   9,998         9998           100 2017-08-19 18:33:51
HT       A_AMT_P1       PARTITION       NO   9,998         9998           100 2017-08-19 18:33:51

2、收集統計資訊耗時


  1. * * * * * * * Oracle 10G parallel 8 HP - UX nopartitioned * * * * * * * * * * * * * * * * * * * * * *                 
  2. table_name    table_size (M )    index_size (M )    total (M )    time
  3. --------- ------------        ------------      ------- --------------------
  4. tab1         488                0                 488       Elapsed : 00 :00 :27 .99
  5. tab2         1 ,115              331               1 ,446     Elapsed : 00 :00 :06 .28
  6. tab3         2 ,019              243               2 ,262     Elapsed : 00 :00 :44 .30
  7. tab4         3 ,171              1 ,221             4 ,392     Elapsed : 00 :02 :17 .08
  8. tab5         4 ,756              0                 4 ,756     Elapsed : 00 :05 :42 .85
  9. tab6         15 ,146             16 ,059            31 ,205    Elapsed : 00 :29 :59 .14
  10. tab7         8 ,105              4 ,820             12 ,925    Elapsed : 00 :26 :12 .52


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31442014/viewspace-2678259/，如需轉載，請註明出處，否則將追究法律責任。

相關文章