ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 一次DBA_FREE_SPACE的优化

一次DBA_FREE_SPACE的优化

原创 Oracle 作者：ora_benben 时间：2016-06-29 14:14:13 0 删除 编辑

sql文本：


  1. SELECT TABLESPACE_NAME,
  2.          FILE_ID,
  3.          COUNT (*) EXTENT_COUNT,
  4.          MIN (BYTES) BYTES_MIN,
  5.          MAX (BYTES) BYTES_MAX,
  6.          ROUND (AVG (BYTES)) BYTES_AVG,
  7.          SUM (BYTES) BYTES
  8.     FROM DBA_FREE_SPACE
  9. GROUP BY TABLESPACE_NAME, FILE_ID;

执行计划：

| Id  | Operation                         | Name             | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                  |      1 |        |       |   319M(100)|   5404 |00:29:22.37 |      81M|
|   1 |  HASH GROUP BY                    |                  |      1 |   5404 |   121K|   319M  (1)|   5404 |00:29:22.37 |      81M|
|   2 |   VIEW                            | DBA_FREE_SPACE   |      1 |    294M|  6457M|   319M  (1)|   1985K|00:29:16.96 |      81M|
|   3 |    UNION-ALL                      |                  |      1 |        |       |            |   1985K|00:29:15.81 |      81M|
|   4 |     NESTED LOOPS                  |                  |      1 |      1 |    66 |     4   (0)|      0 |00:00:00.01 |      30 |
|   5 |      NESTED LOOPS                 |                  |      1 |      1 |    48 |     3   (0)|      0 |00:00:00.01 |      30 |
|   6 |       TABLE ACCESS FULL           | FET$             |      1 |      1 |    39 |     3   (0)|      0 |00:00:00.01 |      30 |
|   7 |       TABLE ACCESS BY INDEX ROWID | FILE$            |      0 |      1 |     9 |     0   (0)|      0 |00:00:00.01 |       0 |
|*  8 |        INDEX UNIQUE SCAN          | I_FILE2          |      0 |      1 |       |     0   (0)|      0 |00:00:00.01 |       0 |
|*  9 |      TABLE ACCESS CLUSTER         | TS$              |      0 |      1 |    18 |     1   (0)|      0 |00:00:00.01 |       0 |
|  10 |     NESTED LOOPS                  |                  |      1 |    520 | 37440 |   145   (0)|    677K|00:09:47.75 |      27M|
|  11 |      NESTED LOOPS                 |                  |      1 |    520 | 32760 |     3   (0)|    677K|00:00:07.85 |     110K|
|* 12 |       TABLE ACCESS FULL           | TS$              |      1 |     26 |   624 |     3   (0)|     26 |00:00:00.01 |      30 |
|* 13 |       FIXED TABLE FIXED INDEX     | X$KTFBFE (ind:1) |     26 |     20 |   780 |     0   (0)|    677K|00:00:07.55 |     110K|
|* 14 |      TABLE ACCESS FULL            | FILE$            |    677K|      1 |     9 |     0   (0)|    677K|00:09:38.88 |      27M|
|  15 |     NESTED LOOPS                  |                  |      1 |    294M|    37G|   319M  (1)|   1307K|00:19:24.13 |      53M|
|  16 |      NESTED LOOPS                 |                  |      1 |   1177M|   140G|   147K(100)|   1307K|00:00:55.39 |   84980 |
|  17 |       MERGE JOIN                  |                  |      1 |  11777 |   724K|     4  (25)|  13085 |00:00:00.20 |     639 |
|  18 |        TABLE ACCESS BY INDEX ROWID| RECYCLEBIN$      |      1 |  13085 |   498K|     0   (0)|  13085 |00:00:00.08 |     609 |
|  19 |         INDEX FULL SCAN           | RECYCLEBIN$_TS   |      1 |      1 |       |     0   (0)|  13085 |00:00:00.03 |     157 |
|* 20 |        SORT JOIN                  |                  |  13085 |      4 |    96 |     4  (25)|  13085 |00:00:00.09 |      30 |
|* 21 |         TABLE ACCESS FULL         | TS$              |      1 |      4 |    96 |     3   (0)|     26 |00:00:00.01 |      30 |
|* 22 |       FIXED TABLE FIXED INDEX     | X$KTFBUE (ind:1) |  13085 |    100K|  6347K|    13 (100)|   1307K|00:00:54.54 |   84341 |
|* 23 |      TABLE ACCESS FULL            | FILE$            |   1307K|      1 |     9 |     0   (0)|   1307K|00:18:26.77 |      53M|
|  24 |     NESTED LOOPS                  |                  |      1 |      1 |   118 |     7   (0)|      0 |00:00:01.09 |     343K|
|  25 |      NESTED LOOPS                 |                  |      1 |      1 |   100 |     6   (0)|      0 |00:00:01.09 |     343K|
|  26 |       NESTED LOOPS                |                  |      1 |      1 |    61 |     6   (0)|      0 |00:00:01.09 |     343K|
|  27 |        TABLE ACCESS FULL          | FILE$            |      1 |      4 |    36 |     2   (0)|   5717 |00:00:00.01 |      41 |
|  28 |        TABLE ACCESS CLUSTER       | UET$             |   5717 |      1 |    52 |     1   (0)|      0 |00:00:01.08 |     343K|
|* 29 |         INDEX RANGE SCAN          | I_FILE#_BLOCK#   |   5717 |      1 |       |     1   (0)|    334K|00:00:00.22 |    9633 |
|* 30 |       TABLE ACCESS BY INDEX ROWID | RECYCLEBIN$      |      0 |  13085 |   498K|     0   (0)|      0 |00:00:00.01 |       0 |
|* 31 |        INDEX RANGE SCAN           | RECYCLEBIN$_TS   |      0 |      1 |       |     0   (0)|      0 |00:00:00.01 |       0 |
|* 32 |      TABLE ACCESS CLUSTER         | TS$              |      0 |      1 |    18 |     1   (0)|      0 |00:00:00.01 |       0 |
|* 33 |       INDEX UNIQUE SCAN           | I_TS#            |      0 |      1 |       |     0   (0)|      0 |00:00:00.01 |       0 |
-------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SET$1 / DBA_FREE_SPACE@SEL$1
   3 - SET$1
   4 - SEL$2
   6 - SEL$2 / F@SEL$2
   7 - SEL$2 / FI@SEL$2
   8 - SEL$2 / FI@SEL$2
   9 - SEL$2 / TS@SEL$2
  10 - SEL$3
  12 - SEL$3 / TS@SEL$3
  13 - SEL$3 / F@SEL$3
  14 - SEL$3 / FI@SEL$3
  15 - SEL$4
  18 - SEL$4 / RB@SEL$4
  19 - SEL$4 / RB@SEL$4
  21 - SEL$4 / TS@SEL$4
  22 - SEL$4 / U@SEL$4
  23 - SEL$4 / FI@SEL$4
  24 - SEL$5
  27 - SEL$5 / FI@SEL$5
  28 - SEL$5 / U@SEL$5
  29 - SEL$5 / U@SEL$5
  30 - SEL$5 / RB@SEL$5
  31 - SEL$5 / RB@SEL$5
  32 - SEL$5 / TS@SEL$5
  33 - SEL$5 / TS@SEL$5
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$4")
      OUTLINE_LEAF(@"SEL$5")
      OUTLINE_LEAF(@"SET$1")
      OUTLINE_LEAF(@"SEL$1")
      NO_ACCESS(@"SEL$1" "DBA_FREE_SPACE"@"SEL$1")
      USE_HASH_AGGREGATION(@"SEL$1")
      FULL(@"SEL$5" "FI"@"SEL$5")
      INDEX_RS_ASC(@"SEL$5" "U"@"SEL$5" "I_FILE#_BLOCK#")
      INDEX_RS_ASC(@"SEL$5" "RB"@"SEL$5" ("RECYCLEBIN$"."TS#"))
      INDEX(@"SEL$5" "TS"@"SEL$5" "I_TS#")
      LEADING(@"SEL$5" "FI"@"SEL$5" "U"@"SEL$5" "RB"@"SEL$5" "TS"@"SEL$5")
      USE_NL(@"SEL$5" "U"@"SEL$5")
      USE_NL(@"SEL$5" "RB"@"SEL$5")
      USE_NL(@"SEL$5" "TS"@"SEL$5")
      INDEX(@"SEL$4" "RB"@"SEL$4" ("RECYCLEBIN$"."TS#"))
      FULL(@"SEL$4" "TS"@"SEL$4")
      FULL(@"SEL$4" "U"@"SEL$4")
      FULL(@"SEL$4" "FI"@"SEL$4")
      LEADING(@"SEL$4" "RB"@"SEL$4" "TS"@"SEL$4" "U"@"SEL$4" "FI"@"SEL$4")
      USE_MERGE(@"SEL$4" "TS"@"SEL$4")
      USE_NL(@"SEL$4" "U"@"SEL$4")
      USE_NL(@"SEL$4" "FI"@"SEL$4")
      FULL(@"SEL$3" "TS"@"SEL$3")
      FULL(@"SEL$3" "F"@"SEL$3")
      FULL(@"SEL$3" "FI"@"SEL$3")
      LEADING(@"SEL$3" "TS"@"SEL$3" "F"@"SEL$3" "FI"@"SEL$3")
      USE_NL(@"SEL$3" "F"@"SEL$3")
      USE_NL(@"SEL$3" "FI"@"SEL$3")
      FULL(@"SEL$2" "F"@"SEL$2")
      INDEX_RS_ASC(@"SEL$2" "FI"@"SEL$2" ("FILE$"."TS#" "FILE$"."RELFILE#"))
      CLUSTER(@"SEL$2" "TS"@"SEL$2")
      LEADING(@"SEL$2" "F"@"SEL$2" "FI"@"SEL$2" "TS"@"SEL$2")
      USE_NL(@"SEL$2" "FI"@"SEL$2")
      USE_NL(@"SEL$2" "TS"@"SEL$2")
      END_OUTLINE_DATA
  */
Predicate Information (identified by operation id):
---------------------------------------------------
   8 - access("F"."TS#"="FI"."TS#" AND "F"."FILE#"="FI"."RELFILE#")
   9 - filter(("TS"."BITMAPPED"=0 AND "TS"."TS#"="F"."TS#"))
  12 - filter(("TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0 AND INTERNAL_FUNCTION("TS"."ONLINE$")))
  13 - filter("TS"."TS#"="F"."KTFBFETSN")
  14 - filter(("F"."KTFBFETSN"="FI"."TS#" AND "F"."KTFBFEFNO"="FI"."RELFILE#"))
  20 - access("TS"."TS#"="RB"."TS#")
       filter("TS"."TS#"="RB"."TS#")
  21 - filter(("TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0 AND INTERNAL_FUNCTION("TS"."ONLINE$")))
  22 - filter(("U"."KTFBUESEGTSN"="RB"."TS#" AND "U"."KTFBUESEGFNO"="RB"."FILE#" AND "U"."KTFBUESEGBNO"="RB"."BLOCK#"))
  23 - filter(("RB"."TS#"="FI"."TS#" AND "U"."KTFBUEFNO"="FI"."RELFILE#"))
  29 - access("U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#")
  30 - filter(("U"."SEGFILE#"="RB"."FILE#" AND "U"."SEGBLOCK#"="RB"."BLOCK#"))
  31 - access("U"."TS#"="RB"."TS#")
  32 - filter("TS"."BITMAPPED"=0)
  33 - access("TS"."TS#"="U"."TS#")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "TABLESPACE_NAME"[VARCHAR2,30], "FILE_ID"[NUMBER,22], COUNT("BYTES")[22], COUNT(*)[22], SUM("BYTES")[22], MAX("BYTES")[22], MIN("BYTES")[22]
   2 - "TABLESPACE_NAME"[VARCHAR2,30], "FILE_ID"[NUMBER,22], "BYTES"[NUMBER,22]
   3 - STRDEF[30], STRDEF[22], STRDEF[22]
   4 - "F"."LENGTH"[NUMBER,22], "FI"."FILE#"[NUMBER,22], "TS"."NAME"[VARCHAR2,30], "TS"."BLOCKSIZE"[NUMBER,22]
   5 - "F".ROWID[ROWID,10], "F"."TS#"[NUMBER,22], "F"."LENGTH"[NUMBER,22], "FI"."FILE#"[NUMBER,22]
   6 - "F".ROWID[ROWID,10], "F"."TS#"[NUMBER,22], "F"."FILE#"[NUMBER,22], "F"."LENGTH"[NUMBER,22]
   7 - "FI"."FILE#"[NUMBER,22]
   8 - "FI".ROWID[ROWID,10]
   9 - "TS"."TS#"[NUMBER,22], "TS"."NAME"[VARCHAR2,30], "TS"."BLOCKSIZE"[NUMBER,22], "TS"."BITMAPPED"[NUMBER,22]
  10 - "TS"."NAME"[VARCHAR2,30], "TS"."BLOCKSIZE"[NUMBER,22], "F"."KTFBFEBLKS"[NUMBER,22], "FI"."FILE#"[NUMBER,22]
  11 - "TS"."NAME"[VARCHAR2,30], "TS"."BLOCKSIZE"[NUMBER,22], "F"."KTFBFETSN"[NUMBER,22], "F"."KTFBFEFNO"[NUMBER,22], "F"."KTFBFEBLKS"[NUMBER,22]
  12 - "TS"."TS#"[NUMBER,22], "TS"."NAME"[VARCHAR2,30], "TS"."BLOCKSIZE"[NUMBER,22]
  13 - "F"."KTFBFETSN"[NUMBER,22], "F"."KTFBFEFNO"[NUMBER,22], "F"."KTFBFEBLKS"[NUMBER,22]
  14 - "FI"."FILE#"[NUMBER,22]
  15 - "TS"."NAME"[VARCHAR2,30], "TS"."BLOCKSIZE"[NUMBER,22], "U"."KTFBUEBLKS"[NUMBER,22], "FI"."FILE#"[NUMBER,22]
  16 - "RB"."TS#"[NUMBER,22], "TS"."NAME"[VARCHAR2,30], "TS"."BLOCKSIZE"[NUMBER,22], "U"."KTFBUEFNO"[NUMBER,22], "U"."KTFBUEBLKS"[NUMBER,22]
  17 - "RB"."TS#"[NUMBER,22], "RB"."BLOCK#"[NUMBER,22], "RB"."FILE#"[NUMBER,22], "TS"."NAME"[VARCHAR2,30], "TS"."BLOCKSIZE"[NUMBER,22]
  18 - "RB"."TS#"[NUMBER,22], "RB"."FILE#"[NUMBER,22], "RB"."BLOCK#"[NUMBER,22]
  19 - "RB".ROWID[ROWID,10], "RB"."TS#"[NUMBER,22]
  20 - (#keys=1) "TS"."TS#"[NUMBER,22], "TS"."NAME"[VARCHAR2,30], "TS"."BLOCKSIZE"[NUMBER,22]
  21 - "TS"."TS#"[NUMBER,22], "TS"."NAME"[VARCHAR2,30], "TS"."BLOCKSIZE"[NUMBER,22]
  22 - "U"."KTFBUESEGTSN"[NUMBER,22], "U"."KTFBUESEGFNO"[NUMBER,22], "U"."KTFBUESEGBNO"[NUMBER,22], "U"."KTFBUEFNO"[NUMBER,22], "U"."KTFBUEBLKS"[NUMBER,22]
  23 - "FI"."FILE#"[NUMBER,22]
  24 - "FI"."FILE#"[NUMBER,22], "U"."LENGTH"[NUMBER,22], "TS"."NAME"[VARCHAR2,30], "TS"."BLOCKSIZE"[NUMBER,22]
  25 - "FI"."FILE#"[NUMBER,22], "U"."TS#"[NUMBER,22], "U"."LENGTH"[NUMBER,22]
  26 - "FI"."FILE#"[NUMBER,22], "U"."TS#"[NUMBER,22], "U"."SEGFILE#"[NUMBER,22], "U"."SEGBLOCK#"[NUMBER,22], "U"."LENGTH"[NUMBER,22]
  27 - "FI"."FILE#"[NUMBER,22], "FI"."TS#"[NUMBER,22], "FI"."RELFILE#"[NUMBER,22]
  28 - "U".ROWID[ROWID,10], "U"."TS#"[NUMBER,22], "U"."SEGFILE#"[NUMBER,22], "U"."SEGBLOCK#"[NUMBER,22], "U"."LENGTH"[NUMBER,22]
  29 - "U".ROWID[ROWID,10]
  31 - "RB".ROWID[ROWID,10]
  32 - "TS".ROWID[ROWID,10], "TS"."TS#"[NUMBER,22], "TS"."NAME"[VARCHAR2,30], "TS"."BLOCKSIZE"[NUMBER,22], "TS"."BITMAPPED"[NUMBER,22]
  33 - "TS".ROWID[ROWID,10]
Note
-----
   - cardinality feedback used for this statement

cardinality feedback特性作用在了该sql上，但是这个对此sql影响并不大。


问题分析：

通过A-TIME可以发现时间消耗主要在以下步骤：
| Id  | Operation                         | Name             | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------
|  10 |     NESTED LOOPS                  |                  |      1 |    520 | 37440 |   145   (0)|    677K|00:09:47.75 |      27M|

|  11 |      NESTED LOOPS                 |                  |      1 |    520 | 32760 |     3   (0)|    677K|00:00:07.85 |     110K|
|* 12 |       TABLE ACCESS FULL           | TS$              |      1 |     26 |   624 |     3   (0)|     26 |00:00:00.01 |      30 |
|* 13 |       FIXED TABLE FIXED INDEX     | X$KTFBFE (ind:1) |     26 |     20 |   780 |     0   (0)|    677K|00:00:07.55 |     110K|
|* 14 |      TABLE ACCESS FULL            | FILE$            |    677K|      1 |     9 |     0   (0)|    677K|00:09:38.88 |      27M|
|  15 |     NESTED LOOPS                  |                  |      1 |    294M|    37G|   319M  (1)|   1307K|00:19:24.13 |      53M|
|  16 |      NESTED LOOPS                 |                  |      1 |   1177M|   140G|   147K(100)|   1307K|00:00:55.39 |   84980 |
|  17 |       MERGE JOIN                  |                  |      1 |  11777 |   724K|     4  (25)|  13085 |00:00:00.20 |     639 |
|  18 |        TABLE ACCESS BY INDEX ROWID| RECYCLEBIN$      |      1 |  13085 |   498K|     0   (0)|  13085 |00:00:00.08 |     609 |
|  19 |         INDEX FULL SCAN           | RECYCLEBIN$_TS   |      1 |      1 |       |     0   (0)|  13085 |00:00:00.03 |     157 |
|* 20 |        SORT JOIN                  |                  |  13085 |      4 |    96 |     4  (25)|  13085 |00:00:00.09 |      30 |
|* 21 |         TABLE ACCESS FULL         | TS$              |      1 |      4 |    96 |     3   (0)|     26 |00:00:00.01 |      30 |
|* 22 |       FIXED TABLE FIXED INDEX     | X$KTFBUE (ind:1) |  13085 |    100K|  6347K|    13 (100)|   1307K|00:00:54.54 |   84341 |
|* 23 |      TABLE ACCESS FULL            | FILE$            |   1307K|      1 |     9 |     0   (0)|   1307K|00:18:26.77 |      53M|

很容易看到性能差的原因就是由于NESTED LOOPS的关系，对FILE$分别全表扫描了677K次和1307K次，分别消耗了9分38秒和18分26秒。这里注意E-Rows，CBO的基数评估为1177M比A-rows还要多，为何还会选择NESTED LOOPS的连接方式呢？
  • 1.谓词关联不是等号，不能选择HASH JOIN
  • 2.使用了use_nl的hint

验证：
23 - filter(("RB"."TS#"="FI"."TS#" AND "U"."KTFBUEFNO"="FI"."RELFILE#")) --第一种可能排除
DBA_FREE_SPACE的视图定义：

  1. SELECT ts.name,
  2.        fi.file#,
  3.        f.block#,
  4.        f.LENGTH * ts.blocksize,
  5.        f.LENGTH,
  6.        f.file#
  7.   FROM sys.ts$ ts, sys.fet$ f, sys.file$ fi
  8.  WHERE ts.ts# = f.ts#
  9.        AND f.ts# = fi.ts#
  10.        AND f.file# = fi.relfile#
  11.        AND ts.bitmapped = 0
  12. UNION ALL
  13. SELECT /*+ ordered use_nl(f) use_nl(fi) */
  14.       ts.name,
  15.        fi.file#,
  16.        f.ktfbfebno,
  17.        f.ktfbfeblks * ts.blocksize,
  18.        f.ktfbfeblks,
  19.        f.ktfbfefno
  20.   FROM sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi
  21.  WHERE ts.ts# = f.ktfbfetsn
  22.        AND f.ktfbfetsn = fi.ts#
  23.        AND f.ktfbfefno = fi.relfile#
  24.        AND ts.bitmapped <> 0
  25.        AND ts.online$ IN (1, 4)
  26.        AND ts.contents$ = 0
  27. UNION ALL
  28. SELECT /*+ ordered use_nl(u) use_nl(fi) */
  29.       ts.name,
  30.        fi.file#,
  31.        u.ktfbuebno,
  32.        u.ktfbueblks * ts.blocksize,
  33.        u.ktfbueblks,
  34.        u.ktfbuefno
  35.   FROM sys.recyclebin$ rb,
  36.        sys.ts$ ts,
  37.        sys.x$ktfbue u,
  38.        sys.file$ fi
  39.  WHERE ts.ts# = rb.ts#
  40.        AND rb.ts# = fi.ts#
  41.        AND u.ktfbuefno = fi.relfile#
  42.        AND u.ktfbuesegtsn = rb.ts#
  43.        AND u.ktfbuesegfno = rb.file#
  44.        AND u.ktfbuesegbno = rb.block#
  45.        AND ts.bitmapped <> 0
  46.        AND ts.online$ IN (1, 4)
  47.        AND ts.contents$ = 0
  48. UNION ALL
  49. SELECT ts.name,
  50.        fi.file#,
  51.        u.block#,
  52.        u.LENGTH * ts.blocksize,
  53.        u.LENGTH,
  54.        u.file#
  55.   FROM sys.ts$ ts,
  56.        sys.uet$ u,
  57.        sys.file$ fi,
  58.        sys.recyclebin$ rb
  59.  WHERE ts.ts# = u.ts#
  60.        AND u.ts# = fi.ts#
  61.        AND u.segfile# = fi.relfile#
  62.        AND u.ts# = rb.ts#
  63.        AND u.segfile# = rb.file#
  64.        AND u.segblock# = rb.block#
  65.        AND ts.bitmapped = 0
果然是在视图内部使用了hint。但是当库比较大的时候EXTENT会比较多也就是sys.x$ktfbue和sys.x$ktfbue的记录数会非常多，此时选择USE_NL就是不合理的了。

优化：

sql改写：

  1. SELECT TABLESPACE_NAME,
  2.          FILE_ID,
  3.          COUNT (*) EXTENT_COUNT,
  4.          MIN (BYTES) BYTES_MIN,
  5.          MAX (BYTES) BYTES_MAX,
  6.          ROUND (AVG (BYTES)) BYTES_AVG,
  7.          SUM (BYTES) BYTES
  8.     FROM (SELECT ts.name TABLESPACE_NAME,
  9.                  fi.file# FILE_ID,
  10.                  f.block# BLOCK_ID,
  11.                  f.LENGTH * ts.blocksize BYTES,
  12.                  f.LENGTH BLOCKS,
  13.                  f.file# RELATIVE_FNO
  14.             FROM sys.ts$ ts, sys.fet$ f, sys.file$ fi
  15.            WHERE ts.ts# = f.ts#
  16.                  AND f.ts# = fi.ts#
  17.                  AND f.file# = fi.relfile#
  18.                  AND ts.bitmapped = 0
  19.           UNION ALL
  20.           SELECT /*+ opt_param('_optimizer_use_feedback' 'false') leading(ts f fi) use_nl(f) use_hash(fi) swap_join_inputs(fi) */
  21.                 ts.name,
  22.                  fi.file#,
  23.                  f.ktfbfebno,
  24.                  f.ktfbfeblks * ts.blocksize,
  25.                  f.ktfbfeblks,
  26.                  f.ktfbfefno
  27.             FROM sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi
  28.            WHERE ts.ts# = f.ktfbfetsn
  29.                  AND f.ktfbfetsn = fi.ts#
  30.                  AND f.ktfbfefno = fi.relfile#
  31.                  AND ts.bitmapped <> 0
  32.                  AND ts.online$ IN (1, 4)
  33.                  AND ts.contents$ = 0
  34.           UNION ALL
  35.           SELECT /*+ opt_param('_optimizer_use_feedback' 'false') leading(rb ts u fi) use_nl(ts) use_nl(u) use_hash(fi) swap_join_inputs(fi) */
  36.                 ts.name,
  37.                  fi.file#,
  38.                  u.ktfbuebno,
  39.                  u.ktfbueblks * ts.blocksize,
  40.                  u.ktfbueblks,
  41.                  u.ktfbuefno
  42.             FROM sys.recyclebin$ rb,
  43.                  sys.ts$ ts,
  44.                  sys.x$ktfbue u,
  45.                  sys.file$ fi
  46.            WHERE ts.ts# = rb.ts#
  47.                  AND rb.ts# = fi.ts#
  48.                  AND u.ktfbuefno = fi.relfile#
  49.                  AND u.ktfbuesegtsn = rb.ts#
  50.                  AND u.ktfbuesegfno = rb.file#
  51.                  AND u.ktfbuesegbno = rb.block#
  52.                  AND ts.bitmapped <> 0
  53.                  AND ts.online$ IN (1, 4)
  54.                  AND ts.contents$ = 0
  55.           UNION ALL
  56.           SELECT ts.name,
  57.                  fi.file#,
  58.                  u.block#,
  59.                  u.LENGTH * ts.blocksize,
  60.                  u.LENGTH,
  61.                  u.file#
  62.             FROM sys.ts$ ts,
  63.                  sys.uet$ u,
  64.                  sys.file$ fi,
  65.                  sys.recyclebin$ rb
  66.            WHERE ts.ts# = u.ts#
  67.                  AND u.ts# = fi.ts#
  68.                  AND u.segfile# = fi.relfile#
  69.                  AND u.ts# = rb.ts#
  70.                  AND u.segfile# = rb.file#
  71.                  AND u.segblock# = rb.block#
  72.                  AND ts.bitmapped = 0)
  73. GROUP BY TABLESPACE_NAME, FILE_ID;
这里为了得到更稳定的执行计划在sql级别禁用了_optimizer_use_feedback，并且改变了连接方式和连接顺序。

改写后的执行计划：

-----------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                  |    12 |   276 |    33  (46)| 00:00:01 |
|   1 |  HASH GROUP BY                   |                  |    12 |   276 |    33  (46)| 00:00:01 |
|   2 |   VIEW                           |                  |    75 |  1725 |    32  (44)| 00:00:01 |
|   3 |    UNION-ALL                     |                  |       |       |            |          |
|   4 |     NESTED LOOPS                 |                  |     1 |    66 |     4   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                |                  |     1 |    48 |     3   (0)| 00:00:01 |
|   6 |       TABLE ACCESS FULL          | FET$             |     1 |    39 |     3   (0)| 00:00:01 |
|   7 |       TABLE ACCESS BY INDEX ROWID| FILE$            |     1 |     9 |     0   (0)| 00:00:01 |
|*  8 |        INDEX UNIQUE SCAN         | I_FILE2          |     1 |       |     0   (0)| 00:00:01 |
|*  9 |      TABLE ACCESS CLUSTER        | TS$              |     1 |    18 |     1   (0)| 00:00:01 |
|* 10 |     HASH JOIN                    |                  |    72 |  5184 |     6  (17)| 00:00:01 |
|  11 |      TABLE ACCESS FULL           | FILE$            |     4 |    36 |     2   (0)| 00:00:01 |
|  12 |      NESTED LOOPS                |                  |    72 |  4536 |     3   (0)| 00:00:01 |
|* 13 |       TABLE ACCESS FULL          | TS$              |     4 |    96 |     3   (0)| 00:00:01 |
|* 14 |       FIXED TABLE FIXED INDEX    | X$KTFBFE (ind:1) |    20 |   780 |     0   (0)| 00:00:01 |
|* 15 |     HASH JOIN                    |                  |     1 |   137 |    18  (73)| 00:00:01 |
|  16 |      TABLE ACCESS FULL           | FILE$            |     4 |    36 |     2   (0)| 00:00:01 |
|  17 |      NESTED LOOPS                |                  |     1 |   128 |    16  (82)| 00:00:01 |
|  18 |       NESTED LOOPS               |                  |     1 |    63 |     3   (0)| 00:00:01 |
|  19 |        TABLE ACCESS FULL         | RECYCLEBIN$      |     1 |    39 |     2   (0)| 00:00:01 |
|* 20 |        TABLE ACCESS CLUSTER      | TS$              |     1 |    24 |     1   (0)| 00:00:01 |
|* 21 |         INDEX UNIQUE SCAN        | I_TS#            |     1 |       |     0   (0)| 00:00:01 |
|* 22 |       FIXED TABLE FIXED INDEX    | X$KTFBUE (ind:1) |   100K|  6347K|    13 (100)| 00:00:01 |
|  23 |     NESTED LOOPS                 |                  |     1 |   118 |     4   (0)| 00:00:01 |
|  24 |      NESTED LOOPS                |                  |     1 |   100 |     3   (0)| 00:00:01 |
|  25 |       NESTED LOOPS               |                  |     1 |    91 |     3   (0)| 00:00:01 |
|  26 |        TABLE ACCESS FULL         | RECYCLEBIN$      |     1 |    39 |     2   (0)| 00:00:01 |
|  27 |        TABLE ACCESS CLUSTER      | UET$             |     1 |    52 |     1   (0)| 00:00:01 |
|* 28 |         INDEX UNIQUE SCAN        | I_FILE#_BLOCK#   |     1 |       |     0   (0)| 00:00:01 |
|  29 |       TABLE ACCESS BY INDEX ROWID| FILE$            |     1 |     9 |     0   (0)| 00:00:01 |
|* 30 |        INDEX UNIQUE SCAN         | I_FILE2          |     1 |       |     0   (0)| 00:00:01 |
|* 31 |      TABLE ACCESS CLUSTER        | TS$              |     1 |    18 |     1   (0)| 00:00:01 |
|* 32 |       INDEX UNIQUE SCAN          | I_TS#            |     1 |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------


Elapsed: 00:01:00.96  --仅仅消耗了1分钟
Statistics
----------------------------------------------------------
      18987  recursive calls
     103412  db block gets
     144013  consistent gets
      13983  physical reads
          0  redo size
     236532  bytes sent via SQL*Net to client
       4484  bytes received via SQL*Net from client
        362  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       5404  rows processed
逻辑读之前为81M，现在只有144013


来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31347199/viewspace-2121176/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： Order By Elimination
下一篇： b*tree index的那些事(四):index rebuild
请登录后发表评论 登录
全部评论
ora_benben

注册时间：2016-05-22

  • 博文量
    21
  • 访问量
    36312

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593