recyclebin未清引起的查詢表空間使用率慢

darren__chan發表於2016-01-05
今天客戶反應說跑sql一條sql非常慢,看了一下發現發現是一條查詢表空間相關資訊的sql。詢問是否recyclebin資料太多引起?
為什麼recyclebin太多垃圾會引起sql執行慢呢?針對此我研究了一下。

透過檢視sql的執行計劃發現問題應該來自於sql會對recyclebin$這個表進行訪問,而該sql 查詢的檢視是db_tablespaces,dba_data_files和dba_free_space三個檢視,sql對recyclebin$訪問正是來源於查詢 dba_free_space。

  1. SQL> set linesize 200 pagesize 200
  2. SQL> explain plan for select bytes from dba_free_space;

  3. Explained.

  4. SQL> select * from table(dbms_xplan.display());

  5. PLAN_TABLE_OUTPUT
  6. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  7. Plan hash value: 2345329605

  8. -----------------------------------------------------------------------------------------------------
  9. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  10. -----------------------------------------------------------------------------------------------------
  11. | 0 | SELECT STATEMENT | | 10559 | 134K| 20 (10)| 00:00:01 |
  12. | 1 | VIEW | DBA_FREE_SPACE | 10559 | 134K| 20 (10)| 00:00:01 |
  13. | 2 | UNION-ALL | | | | | |
  14. | 3 | NESTED LOOPS | | 1 | 56 | 1 (0)| 00:00:01 |
  15. | 4 | NESTED LOOPS | | 1 | 45 | 1 (0)| 00:00:01 |
  16. | 5 | INDEX FULL SCAN | I_FILE2 | 9 | 54 | 1 (0)| 00:00:01 |
  17. |* 6 | TABLE ACCESS CLUSTER | FET$ | 1 | 39 | 0 (0)| 00:00:01 |
  18. |* 7 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
  19. |* 8 | TABLE ACCESS CLUSTER | TS$ | 1 | 11 | 0 (0)| 00:00:01 |
  20. |* 9 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
  21. | 10 | NESTED LOOPS | | 65 | 4030 | 6 (0)| 00:00:01 |
  22. | 11 | NESTED LOOPS | | 65 | 3640 | 6 (0)| 00:00:01 |
  23. |* 12 | TABLE ACCESS FULL | TS$ | 8 | 136 | 6 (0)| 00:00:01 |
  24. |* 13 | FIXED TABLE FIXED INDEX | X$KTFBFE (ind:1) | 8 | 312 | 0 (0)| 00:00:01 |
  25. |* 14 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 6 | 0 (0)| 00:00:01 |
  26. | 15 | NESTED LOOPS | | 10492 | 1014K| 9 (23)| 00:00:01 |
  27. | 16 | NESTED LOOPS | | 5 | 170 | 7 (0)| 00:00:01 |
  28. | 17 | NESTED LOOPS | | 5 | 85 | 2 (0)| 00:00:01 |
  29. | 18 | INDEX FULL SCAN | I_FILE2 | 9 | 54 | 1 (0)| 00:00:01 |
  30. | 19 | TABLE ACCESS BY INDEX ROWID| RECYCLEBIN$ | 1 | 11 | 1 (0)| 00:00:01 |
  31. |* 20 | INDEX RANGE SCAN | RECYCLEBIN$_TS | 5 | | 0 (0)| 00:00:01 |
  32. |* 21 | TABLE ACCESS CLUSTER | TS$ | 1 | 17 | 1 (0)| 00:00:01 |
  33. |* 22 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
  34. |* 23 | FIXED TABLE FIXED INDEX | X$KTFBUE (ind:1) | 2222 | 141K| 0 (0)| 00:00:01 |
  35. | 24 | NESTED LOOPS | | 1 | 80 | 4 (0)| 00:00:01 |
  36. | 25 | NESTED LOOPS | | 1 | 74 | 4 (0)| 00:00:01 |
  37. | 26 | NESTED LOOPS | | 1 | 63 | 4 (0)| 00:00:01 |
  38. | 27 | TABLE ACCESS FULL | RECYCLEBIN$ | 5 | 55 | 4 (0)| 00:00:01 |
  39. | 28 | TABLE ACCESS CLUSTER | UET$ | 1 | 52 | 0 (0)| 00:00:01 |
  40. |* 29 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 0 (0)| 00:00:01 |
  41. |* 30 | TABLE ACCESS CLUSTER | TS$ | 1 | 11 | 0 (0)| 00:00:01 |
  42. |* 31 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
  43. |* 32 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 6 | 0 (0)| 00:00:01 |
  44. -----------------------------------------------------------------------------------------------------

  45. Predicate Information (identified by operation id):
  46. ---------------------------------------------------

  47.    6 - filter("F"."FILE#"="FI"."RELFILE#")
  48.    7 - access("F"."TS#"="FI"."TS#")
  49.    8 - filter("TS"."BITMAPPED"=0)
  50.    9 - access("TS"."TS#"="F"."TS#")
  51.   12 - filter("TS"."BITMAPPED"<>0 AND ("TS"."ONLINE$"=1 OR "TS"."ONLINE$"=4) AND
  52.               "TS"."CONTENTS$"=0)
  53.   13 - filter("TS"."TS#"="F"."KTFBFETSN")
  54.   14 - access("F"."KTFBFETSN"="FI"."TS#" AND "F"."KTFBFEFNO"="FI"."RELFILE#")
  55.   20 - access("RB"."TS#"="FI"."TS#")
  56.   21 - filter("TS"."BITMAPPED"<>0 AND ("TS"."ONLINE$"=1 OR "TS"."ONLINE$"=4) AND
  57.               "TS"."CONTENTS$"=0)
  58.   22 - access("TS"."TS#"="RB"."TS#")
  59.   23 - filter("U"."KTFBUEFNO"="FI"."RELFILE#" AND "U"."KTFBUESEGTSN"="RB"."TS#" AND
  60.               "U"."KTFBUESEGFNO"="RB"."FILE#" AND "U"."KTFBUESEGBNO"="RB"."BLOCK#")
  61.   29 - access("U"."TS#"="RB"."TS#" AND "U"."SEGFILE#"="RB"."FILE#" AND
  62.               "U"."SEGBLOCK#"="RB"."BLOCK#")
  63.   30 - filter("TS"."BITMAPPED"=0)
  64.   31 - access("TS"."TS#"="U"."TS#")
  65.   32 - access("U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#")

  66. 62 rows selected.

從執行計劃中可以看出,RECYCLEBIN$兩次被訪問,
第一次是作為被驅動表與驅動表 FILE$進行nested loops。此時 RECYCLEBIN$ 因   FILE$返回的行數可能被掃描多次索引以及多次回表;
第二次是 RECYCLEBIN$作為驅動表與被驅動表UET$進行   nested loops,此時  RECYCLEBIN$進行一次全表掃描。 

因此當回收站的堆積了太多的資料的時候,有時在跑一些查詢表空間的指令碼會越來越慢。
                                                                                                        

 
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               

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

相關文章