檢視oracle資料庫表空間使用情況 非常慢!

huangdazhu發表於2017-10-31
1.現象
檢視資料庫表空間使用情況 非常慢!

2.診斷過程
SQL> set timing on
SQL> set autotrace traceonly
SQL> Select a.Tablespace_Name, a.Total || 'M' Total_Space, (a.Total - b.Free) || 'M' Used_Space, To_Char((a.Total - b.Free) / a.Total * 100, '99.99') || '%' Pct_Free
  2  From
  3  (Select Tablespace_Name, Sum(Bytes) / 1024 / 1024 Total From Dba_Data_Files Group By tablespace_Name) a,
  4  (Select Tablespace_Name, Sum(Bytes) / 1024 / 1024 Free From Dba_Free_Space Group By Tablespace_Name) b where a.Tablespace_Name = b.Tablespace_Name;


已選擇21行。


已用時間:  00: 23: 59.93


執行計劃
----------------------------------------------------------                      
Plan hash value: 341960732                                                      
                                                                                
--------------------------------------------------------------------------------
------------------------                                                        
                                                                                
| Id  | Operation                           | Name             | Rows  | Bytes |
 Cost (%CPU)| Time     |                                                        
                                                                                
--------------------------------------------------------------------------------
------------------------                                                        
                                                                                
|   0 | SELECT STATEMENT                    |                  |     2 |   120 |
   568  (51)| 00:00:07 |                                                        
                                                                                
|*  1 |  HASH JOIN                          |                  |     2 |   120 |
   568  (51)| 00:00:07 |                                                        
                                                                                
|   2 |   VIEW                              |                  |     2 |    60 |
     5  (20)| 00:00:01 |                                                        
                                                                                
|   3 |    HASH GROUP BY                    |                  |     2 |    40 |
     5  (20)| 00:00:01 |                                                        
                                                                                
|   4 |     VIEW                            | DBA_DATA_FILES   |     2 |    40 |
     4   (0)| 00:00:01 |                                                        
                                                                                
|   5 |      UNION-ALL                      |                  |       |       |
            |          |                                                        
                                                                                
|   6 |       NESTED LOOPS                  |                  |     1 |   356 |
     2   (0)| 00:00:01 |                                                        
                                                                                
|   7 |        NESTED LOOPS                 |                  |     1 |   342 |
     1   (0)| 00:00:01 |                                                        
                                                                                
|   8 |         NESTED LOOPS                |                  |     1 |   329 |
     1   (0)| 00:00:01 |                                                        
                                                                                
|*  9 |          FIXED TABLE FULL           | X$KCCFN          |     1 |   310 |
     0   (0)| 00:00:01 |                                                        
                                                                                
|* 10 |          TABLE ACCESS BY INDEX ROWID| FILE$            |     1 |    19 |
     1   (0)| 00:00:01 |                                                        
                                                                                
|* 11 |           INDEX UNIQUE SCAN         | I_FILE1          |     1 |       |
     0   (0)| 00:00:01 |                                                        
                                                                                
|* 12 |         FIXED TABLE FIXED INDEX     | X$KCCFE (ind:1)  |     3 |    39 |
     0   (0)| 00:00:01 |                                                        
                                                                                
|  13 |        TABLE ACCESS CLUSTER         | TS$              |     1 |    14 |
     1   (0)| 00:00:01 |                                                        
                                                                                
|* 14 |         INDEX UNIQUE SCAN           | I_TS#            |     1 |       |
     0   (0)| 00:00:01 |                                                        
                                                                                
|  15 |       NESTED LOOPS                  |                  |     1 |   399 |
     2   (0)| 00:00:01 |                                                        
                                                                                
|  16 |        NESTED LOOPS                 |                  |     1 |   385 |
     1   (0)| 00:00:01 |                                                        
                                                                                
|  17 |         NESTED LOOPS                |                  |     1 |   372 |
     1   (0)| 00:00:01 |                                                        
                                                                                
|  18 |          NESTED LOOPS               |                  |     1 |   362 |
     0   (0)| 00:00:01 |                                                        
                                                                                
|* 19 |           FIXED TABLE FULL          | X$KCCFN          |     1 |   310 |
     0   (0)| 00:00:01 |                                                        
                                                                                
|* 20 |           FIXED TABLE FIXED INDEX   | X$KTFBHC (ind:1) |     1 |    52 |
     0   (0)| 00:00:01 |                                                        
                                                                                
|* 21 |          TABLE ACCESS BY INDEX ROWID| FILE$            |     1 |    10 |
     1   (0)| 00:00:01 |                                                        
                                                                                
|* 22 |           INDEX UNIQUE SCAN         | I_FILE1          |     1 |       |
     0   (0)| 00:00:01 |                                                        
                                                                                
|* 23 |         FIXED TABLE FIXED INDEX     | X$KCCFE (ind:1)  |     3 |    39 |
     0   (0)| 00:00:01 |                                                        
                                                                                
|  24 |        TABLE ACCESS CLUSTER         | TS$              |     1 |    14 |
     1   (0)| 00:00:01 |                                                        
                                                                                
|* 25 |         INDEX UNIQUE SCAN           | I_TS#            |     1 |       |
     0   (0)| 00:00:01 |                                                        
                                                                                
|  26 |   VIEW                              |                  |     6 |   180 |
   563  (51)| 00:00:07 |                                                        
                                                                                
|  27 |    HASH GROUP BY                    |                  |     6 |   120 |
   563  (51)| 00:00:07 |                                                        
                                                                                
|  28 |     VIEW                            | DBA_FREE_SPACE   |  2437K|    46M|
   352  (21)| 00:00:05 |                                                        
                                                                                
|  29 |      UNION-ALL                      |                  |       |       |
            |          |                                                        
                                                                                
|  30 |       NESTED LOOPS                  |                  |     1 |    63 |
     3   (0)| 00:00:01 |                                                        
                                                                                
|  31 |        NESTED LOOPS                 |                  |     1 |    57 |
     3   (0)| 00:00:01 |                                                        
                                                                                
|  32 |         TABLE ACCESS FULL           | FET$             |     1 |    39 |
     3   (0)| 00:00:01 |                                                        
                                                                                
|* 33 |         TABLE ACCESS CLUSTER        | TS$              |     1 |    18 |
     0   (0)| 00:00:01 |                                                        
                                                                                
|* 34 |          INDEX UNIQUE SCAN          | I_TS#            |     1 |       |
     0   (0)| 00:00:01 |                                                        
                                                                                
|* 35 |        INDEX UNIQUE SCAN            | I_FILE2          |     1 |     6 |
     0   (0)| 00:00:01 |                                                        
                                                                                
|  36 |       NESTED LOOPS                  |                  |    80 |  5520 |
     4   (0)| 00:00:01 |                                                        
                                                                                
|  37 |        NESTED LOOPS                 |                  |    80 |  5040 |
     4   (0)| 00:00:01 |                                                        
                                                                                
|* 38 |         TABLE ACCESS FULL           | TS$              |     6 |   144 |
     4   (0)| 00:00:01 |                                                        
                                                                                
|* 39 |         FIXED TABLE FIXED INDEX     | X$KTFBFE (ind:1) |    14 |   546 |
     0   (0)| 00:00:01 |                                                        
                                                                                
|* 40 |        INDEX UNIQUE SCAN            | I_FILE2          |     1 |     6 |
     0   (0)| 00:00:01 |                                                        
                                                                                
|* 41 |       HASH JOIN                     |                  |  2437K|   244M|
   300  (25)| 00:00:04 |                                                        
                                                                                
|  42 |        TABLE ACCESS FULL            | RECYCLEBIN$      | 17654 |   172K|
   221   (1)| 00:00:03 |                                                        
                                                                                
|* 43 |        HASH JOIN                    |                  |   557K|    50M|
    57  (88)| 00:00:01 |                                                        
                                                                                
|  44 |         MERGE JOIN CARTESIAN        |                  |   217 |  6510 |
     7   (0)| 00:00:01 |                                                        
                                                                                
|* 45 |          TABLE ACCESS FULL          | TS$              |     6 |   144 |
     4   (0)| 00:00:01 |                                                        
                                                                                
|  46 |          BUFFER SORT                |                  |    39 |   234 |
     3   (0)| 00:00:01 |                                                        
                                                                                
|  47 |           INDEX FAST FULL SCAN      | I_FILE2          |    39 |   234 |
     1   (0)| 00:00:01 |                                                        
                                                                                
|  48 |         FIXED TABLE FULL            | X$KTFBUE         |   100K|  6347K|
    45 (100)| 00:00:01 |                                                        
                                                                                
|  49 |       NESTED LOOPS                  |                  |     1 |    86 |
    45   (0)| 00:00:01 |                                                        
                                                                                
|  50 |        NESTED LOOPS                 |                  |  1358 |    86 |
    45   (0)| 00:00:01 |                                                        
                                                                                
|  51 |         NESTED LOOPS                |                  |     1 |    76 |
     5   (0)| 00:00:01 |                                                        
                                                                                
|  52 |          NESTED LOOPS               |                  |     1 |    70 |
     5   (0)| 00:00:01 |                                                        
                                                                                
|* 53 |           TABLE ACCESS FULL         | TS$              |     1 |    18 |
     4   (0)| 00:00:01 |                                                        
                                                                                
|  54 |           TABLE ACCESS CLUSTER      | UET$             |     1 |    52 |
     1   (0)| 00:00:01 |                                                        
                                                                                
|* 55 |            INDEX RANGE SCAN         | I_FILE#_BLOCK#   |     1 |       |
     1   (0)| 00:00:01 |                                                        
                                                                                
|* 56 |          INDEX UNIQUE SCAN          | I_FILE2          |     1 |     6 |
     0   (0)| 00:00:01 |                                                        
                                                                                
|* 57 |         INDEX RANGE SCAN            | RECYCLEBIN$_TS   |  1358 |       |
     8   (0)| 00:00:01 |                                                        
                                                                                
|* 58 |        TABLE ACCESS BY INDEX ROWID  | RECYCLEBIN$      |     1 |    10 |
    40   (0)| 00:00:01 |                                                        
                                                                                
--------------------------------------------------------------------------------
------------------------                                                        
                                                                                
                                                                                
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   1 - access("A"."TABLESPACE_NAME"="B"."TABLESPACE_NAME")                      
   9 - filter("FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE'
) AND                                                                           
                                                                                
              BITAND("FNFLG",4)<>4)                                             
  10 - filter("F"."SPARE1" IS NULL)                                             
  11 - access("FNFNO"="F"."FILE#")                                              
  12 - filter("FE"."FENUM"="F"."FILE#")                                         
  14 - access("F"."TS#"="TS"."TS#")                                             
  19 - filter("FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE'
) AND                                                                           
                                                                                
              BITAND("FNFLG",4)<>4)                                             
  20 - filter("FNFNO"="HC"."KTFBHCAFNO")                                        
  21 - filter("F"."SPARE1" IS NOT NULL)                                         
  22 - access("FNFNO"="F"."FILE#")                                              
  23 - filter("FE"."FENUM"="F"."FILE#")                                         
  25 - access("HC"."KTFBHCTSN"="TS"."TS#")                                      
  33 - filter("TS"."BITMAPPED"=0)                                               
  34 - access("TS"."TS#"="F"."TS#")                                             
  35 - access("F"."TS#"="FI"."TS#" AND "F"."FILE#"="FI"."RELFILE#")             
  38 - filter("TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0 AND ("TS"."ONLINE$"=1 O
R                                                                               
                                                                                
              "TS"."ONLINE$"=4))                                                
  39 - filter("TS"."TS#"="F"."KTFBFETSN")                                       
  40 - access("F"."KTFBFETSN"="FI"."TS#" AND "F"."KTFBFEFNO"="FI"."RELFILE#")   
  41 - access("TS"."TS#"="RB"."TS#" AND "RB"."TS#"="FI"."TS#" AND "U"."KTFBUESEG
TSN"="RB"."TS#"                                                                 
                                                                                
              AND "U"."KTFBUESEGFNO"="RB"."FILE#" AND "U"."KTFBUESEGBNO"="RB"."B
LOCK#")                                                                         
                                                                                
  43 - access("U"."KTFBUEFNO"="FI"."RELFILE#")                                  
  45 - filter("TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0 AND ("TS"."ONLINE$"=1 O
R                                                                               
                                                                                
              "TS"."ONLINE$"=4))                                                
  53 - filter("TS"."BITMAPPED"=0)                                               
  55 - access("TS"."TS#"="U"."TS#")                                             
  56 - access("U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#")          
  57 - access("U"."TS#"="RB"."TS#")                                             
  58 - filter("U"."SEGFILE#"="RB"."FILE#" AND "U"."SEGBLOCK#"="RB"."BLOCK#")    




統計資訊
----------------------------------------------------------                      
     176659  recursive calls                                                    
       5802  db block gets                                                      
     561510  consistent gets                                                    
     180309  physical reads                                                     
        132  redo size                                                          
       1477  bytes sent via SQL*Net to client                                   
        483  bytes received via SQL*Net from client                             
          3  SQL*Net roundtrips to/from client                                  
          1  sorts (memory)                                                     
          0  sorts (disk)                                                       
         21  rows processed                                                     


SQL> set autotrace off
SQL> spool off;

2.2 檢視回收站物件
SQL> select count(*) from dba_recyclebin;

  COUNT(*)
----------
   143421
      
3.解決辦法
3.1清空回收站所有物件
SQL> purge dba_recyclebin;


3.2 重新收集統計資訊
exec dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'RECYCLEBIN$', estimate_percent => 100, method_opt=> 'for all indexed columns',degree=>8);
查詢很快,只需要1S。


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

相關文章