请教sqlprofile使用问题。

lhpapa

有一大段sql ，分开来执行计划尚可，合在一起就不太好。这是分开的：
SELECT t.Title         as title,
               t.FilePath      as filepath,
               t.FileName      as filename,
               t.LayOutPath    as layoutpath,
               t.PublishDate   as publishdate,
               t.ClassID       as classid,
               t.InfoLink      as infolink,
               t.DefaultPicUrl as defaultpicurl,
               t.Hits          as hits
          FROM Article t
         WHERE EXISTS
         (SELECT a.ArticleID
                  FROM Article a, ArticleKeyWord k
                 WHERE a.Deleted = :"SYS_B_00"
                   AND a.Passed = :"SYS_B_01"
                   AND a.ToHtml = :"SYS_B_02"
                   AND a.ArticleID != :"SYS_B_03"
                   AND t.ArticleID = a.ArticleID
                   AND a.InfoShareType = :"SYS_B_04"
                   AND a.ArticleID = k.INFOID
                   AND (k.KeyWord = :"SYS_B_05" OR k.KeyWord = :"SYS_B_06" OR
                       k.KeyWord = :"SYS_B_07" OR k.KeyWord = :"SYS_B_08" OR
                       k.KeyWord = :"SYS_B_09"))



SQL> select * from table(dbms_xplan.display(null,null,'outline'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 7110343

------------------------------------------------------------------------------
--------------------------------------------
| Id  | Operation                               | Name           | Rows  | Byt
es | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------
--------------------------------------------
|   0 | SELECT STATEMENT                        |                |     1 |   1
51 |   691   (1)| 00:00:09 |       |       |
|   1 |  SORT ORDER BY                          |                |     1 |   1
51 |   691   (1)| 00:00:09 |       |       |
|   2 |   NESTED LOOPS                          |                |     1 |   1
51 |   690   (1)| 00:00:09 |       |       |
|   3 |    VIEW                                 | VW_SQ_1        |   227 |  13
62 |   687   (0)| 00:00:09 |       |       |
|   4 |     HASH UNIQUE                         |                |     1 |  68
10 |            |          |       |       |
|   5 |      NESTED LOOPS                       |                |   227 |  68
10 |   687   (0)| 00:00:09 |       |       |
|   6 |       INLIST ITERATOR                   |                |       |
   |            |          |       |       |
|*  7 |        TABLE ACCESS BY INDEX ROWID      | ARTICLEKEYWORD |   227 |  29
51 |   233   (0)| 00:00:03 |       |       |
|*  8 |         INDEX RANGE SCAN                | KEYWORD_LH     |   227 |
   |     8   (0)| 00:00:01 |       |       |
|*  9 |       TABLE ACCESS BY GLOBAL INDEX ROWID| ARTICLE        |     1 |
17 |     2   (0)| 00:00:01 | ROWID | ROWID |
|* 10 |        INDEX UNIQUE SCAN                | PKKEY_ARTICLE  |     1 |
   |     1   (0)| 00:00:01 |       |       |
|* 11 |    TABLE ACCESS BY GLOBAL INDEX ROWID   | ARTICLE        |     1 |   1
45 |     2   (0)| 00:00:01 | ROWID | ROWID |
|* 12 |     INDEX UNIQUE SCAN                   | PKKEY_ARTICLE  |     1 |
   |     1   (0)| 00:00:01 |       |       |
------------------------------------------------------------------------------
--------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_NL(@"SEL$683B0107" "A"@"SEL$2")
      LEADING(@"SEL$683B0107" "K"@"SEL$2" "A"@"SEL$2")
      INDEX_RS_ASC(@"SEL$683B0107" "A"@"SEL$2" ("ARTICLE"."ARTICLEID"))
      INDEX_RS_ASC(@"SEL$683B0107" "K"@"SEL$2" ("ARTICLEKEYWORD"."KEYWORD"))
      USE_NL(@"SEL$C772B8D1" "T"@"SEL$1")
      LEADING(@"SEL$C772B8D1" "VW_SQ_1"@"SEL$7511BFD2" "T"@"SEL$1")
      INDEX_RS_ASC(@"SEL$C772B8D1" "T"@"SEL$1" ("ARTICLE"."ARTICLEID"))
      NO_ACCESS(@"SEL$C772B8D1" "VW_SQ_1"@"SEL$7511BFD2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$7511BFD2")
      OUTLINE(@"SEL$2")
      UNNEST(@"SEL$2")
      OUTLINE_LEAF(@"SEL$C772B8D1")
      OUTLINE_LEAF(@"SEL$683B0107")
      ALL_ROWS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   7 - filter("K"."INFOID"<>TO_NUMBER(:SYS_B_03))
   8 - access("K"."KEYWORD"=:SYS_B_05 OR "K"."KEYWORD"=:SYS_B_06 OR "K"."KEYWO
RD"=:SYS_B_07 OR
              "K"."KEYWORD"=:SYS_B_08 OR "K"."KEYWORD"=:SYS_B_09)
   9 - filter("A"."DELETED"=TO_NUMBER(:SYS_B_00) AND "A"."PASSED"=TO_NUMBER(:S
YS_B_01) AND
              "A"."TOHTML"=TO_NUMBER(:SYS_B_02) AND "A"."INFOSHARETYPE"=TO_NUM
BER(:SYS_B_04))
  10 - access("A"."ARTICLEID"="K"."INFOID")
       filter("A"."ARTICLEID"<>TO_NUMBER(:SYS_B_03))
  11 - filter("T"."CSSTYPE"=:SYS_B_11 AND "T"."SITEID"=TO_NUMBER(:SYS_B_10))
  12 - access("T"."ARTICLEID"="ARTICLEID")

已选择57行。

=====合在一起后：
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 1297965229

------------------------------------------------------------------------------
-----------------------------------------------------
| Id  | Operation                                | Name           | Rows  | By
tes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------
-----------------------------------------------------
|   0 | SELECT STATEMENT                         |                |     1 |
840 |       |   147K  (1)| 00:29:28 |       |       |
|*  1 |  COUNT STOPKEY                           |                |       |
    |       |            |          |       |       |
|   2 |   VIEW                                   |                |     1 |
840 |       |   147K  (1)| 00:29:28 |       |       |
|*  3 |    SORT ORDER BY STOPKEY                 |                |     1 |
157 |  8072K|   147K  (1)| 00:29:28 |       |       |
|*  4 |     FILTER                               |                |       |
    |       |            |          |       |       |
|*  5 |      TABLE ACCESS BY GLOBAL INDEX ROWID  | ARTICLE        | 21666 |  3
321K|       | 23447   (1)| 00:04:42 | ROWID | ROWID |
|*  6 |       INDEX RANGE SCAN                   | ARTICLE_SITEID | 64998 |
    |       |    88   (2)| 00:00:02 |       |       |
|*  7 |      FILTER                              |                |       |
    |       |            |          |       |       |
|   8 |       NESTED LOOPS                       |                |     1 |
30 |       |    10   (0)| 00:00:01 |       |       |
|*  9 |        TABLE ACCESS BY GLOBAL INDEX ROWID| ARTICLE        |     1 |
17 |       |     3   (0)| 00:00:01 | ROWID | ROWID |
|* 10 |         INDEX UNIQUE SCAN                | PKKEY_ARTICLE  |     1 |
    |       |     2   (0)| 00:00:01 |       |       |
|  11 |        INLIST ITERATOR                   |                |       |
    |       |            |          |       |       |
|* 12 |         INDEX UNIQUE SCAN                | INFO_KEYWORD   |     1 |
13 |       |     7   (0)| 00:00:01 |       |       |
------------------------------------------------------------------------------
-----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<TO_NUMBER(:SYS_B_12))
   3 - filter(ROWNUM<TO_NUMBER(:SYS_B_12))
   4 - filter( EXISTS (SELECT /*+ */ 0 FROM "ARTICLEKEYWORD" "K","ARTICLE" "A"
WHERE TO_NUMBER(:SYS_B_03)<>:B1 AND
              "A"."ARTICLEID"=:B2 AND "A"."DELETED"=TO_NUMBER(:SYS_B_00) AND "
A"."PASSED"=TO_NUMBER(:SYS_B_01) AND
              "A"."TOHTML"=TO_NUMBER(:SYS_B_02) AND "A"."INFOSHARETYPE"=TO_NUM
BER(:SYS_B_04) AND "A"."ARTICLEID"<>TO_NUMBER(:SYS_B_03)
              AND ("K"."KEYWORD"=:SYS_B_05 OR "K"."KEYWORD"=:SYS_B_06 OR "K"."
KEYWORD"=:SYS_B_07 OR "K"."KEYWORD"=:SYS_B_08 OR
              "K"."KEYWORD"=:SYS_B_09) AND "K"."INFOID"=:B3 AND "K"."INFOID"<>
TO_NUMBER(:SYS_B_03)))
   5 - filter("T"."CSSTYPE"=:SYS_B_11)
   6 - access("T"."SITEID"=TO_NUMBER(:SYS_B_10))
   7 - filter(TO_NUMBER(:SYS_B_03)<>:B1)
   9 - filter("A"."DELETED"=TO_NUMBER(:SYS_B_00) AND "A"."PASSED"=TO_NUMBER(:S
YS_B_01) AND
              "A"."TOHTML"=TO_NUMBER(:SYS_B_02) AND "A"."INFOSHARETYPE"=TO_NUM
BER(:SYS_B_04))
  10 - access("A"."ARTICLEID"=:B1)
       filter("A"."ARTICLEID"<>TO_NUMBER(:SYS_B_03))
  12 - access("K"."INFOID"=:B1 AND ("K"."KEYWORD"=:SYS_B_05 OR "K"."KEYWORD"=:
SYS_B_06 OR "K"."KEYWORD"=:SYS_B_07 OR
              "K"."KEYWORD"=:SYS_B_08 OR "K"."KEYWORD"=:SYS_B_09))
       filter("K"."INFOID"<>TO_NUMBER(:SYS_B_03))

已选择44行。

实际上查询v$sql_plan 对article表走的是全表扫描.v$sql_plan没有及时保存。

[ 本帖最后由 lhpapa 于 2012-5-10 16:46 编辑 ]

lhpapa

现在想用sqlprofile固定住执行计划hint：缺发现虽然使用了profile，但是hints没有生效，请赐教

SQL>
SQL> declare
  2    v_hints sys.sqlprof_attr;
  3  begin
  4    v_hints := sys.sqlprof_attr('USE_NL(@"SEL$683B0107" "A"@"SEL$2")','LEADIN
G(@"SEL$683B0107" "K"@"SEL$2" "A"@"SEL$2")','INDEX_RS_ASC(@"SEL$683B0107" "A"@"S
EL$2" ("ARTICLE"."ARTICLEID"))','INDEX_RS_ASC(@"SEL$683B0107" "K"@"SEL$2" ("ARTI
CLEKEYWORD"."KEYWORD"))','USE_NL(@"SEL$C772B8D1" "T"@"SEL$1")','LEADING(@"SEL$C7
72B8D1" "VW_SQ_1"@"SEL$7511BFD2" "T"@"SEL$1")','LEADING(@"SEL$C772B8D1" "VW_SQ_1
"@"SEL$7511BFD2" "T"@"SEL$1")','INDEX_RS_ASC(@"SEL$C772B8D1" "T"@"SEL$1" ("ARTIC
LE"."ARTICLEID"))','NO_ACCESS(@"SEL$C772B8D1" "VW_SQ_1"@"SEL$7511BFD2")');
  5
  6    dbms_sqltune.import_sql_profile('select rowid,
  7         title,
  8         filepath,
  9         filename,
10         layoutpath,
11         publishdate,
12         classid,
13         infolink,
14         defaultpicurl,
15         hits
16    FROM (SELECT t.Title         as title,
17                 t.FilePath      as filepath,
18                 t.FileName      as filename,
19                 t.LayOutPath    as layoutpath,
20                 t.PublishDate   as publishdate,
21                 t.ClassID       as classid,
22                 t.InfoLink      as infolink,
23                 t.DefaultPicUrl as defaultpicurl,
24                 t.Hits          as hits
25            FROM Article t
26           WHERE EXISTS
27           (SELECT a.ArticleID
28                    FROM Article a, ArticleKeyWord k
29                   WHERE a.Deleted = :"SYS_B_00"
30                     AND a.Passed = :"SYS_B_01"
31                     AND a.ToHtml = :"SYS_B_02"
32                     AND a.ArticleID != :"SYS_B_03"
33                     AND t.ArticleID = a.ArticleID
34                     AND a.InfoShareType = :"SYS_B_04"
35                     AND a.ArticleID = k.INFOID
36                     AND (k.KeyWord = :"SYS_B_05" OR k.KeyWord = :"SYS_B_06"
OR
37                         k.KeyWord = :"SYS_B_07" OR k.KeyWord = :"SYS_B_08" O
R
38                         k.KeyWord = :"SYS_B_09"))
39             AND t.SiteID = :"SYS_B_10"
40             AND t.cssType = :"SYS_B_11"
41           ORDER BY t.ArticleID DESC)
42   WHERE ROWNUM < :"SYS_B_12"
43  ',
44                                    v_hints,
45                                    'LH_ARTICLE',
46                                    force_match => true);
47  end;
48  /

PL/SQL 过程已成功完成。

SQL>
SQL> explain plan for
  2  select rowid,
  3         title,
  4         filepath,
  5         filename,
  6         layoutpath,
  7         publishdate,
  8         classid,
  9         infolink,
10         defaultpicurl,
11         hits
12    FROM (SELECT t.Title         as title,
13                 t.FilePath      as filepath,
14                 t.FileName      as filename,
15                 t.LayOutPath    as layoutpath,
16                 t.PublishDate   as publishdate,
17                 t.ClassID       as classid,
18                 t.InfoLink      as infolink,
19                 t.DefaultPicUrl as defaultpicurl,
20                 t.Hits          as hits
21            FROM Article t
22           WHERE EXISTS
23           (SELECT a.ArticleID
24                    FROM Article a, ArticleKeyWord k
25                   WHERE a.Deleted = :"SYS_B_00"
26                     AND a.Passed = :"SYS_B_01"
27                     AND a.ToHtml = :"SYS_B_02"
28                     AND a.ArticleID != :"SYS_B_03"
29                     AND t.ArticleID = a.ArticleID
30                     AND a.InfoShareType = :"SYS_B_04"
31                     AND a.ArticleID = k.INFOID
32                     AND (k.KeyWord = :"SYS_B_05" OR k.KeyWord = :"SYS_B_06"
OR
33                         k.KeyWord = :"SYS_B_07" OR k.KeyWord = :"SYS_B_08" O
R
34                         k.KeyWord = :"SYS_B_09"))
35             AND t.SiteID = :"SYS_B_10"
36             AND t.cssType = :"SYS_B_11"
37           ORDER BY t.ArticleID DESC)
38   WHERE ROWNUM < :"SYS_B_12";

已解释。

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 1297965229

------------------------------------------------------------------------------
-----------------------------------------------------
| Id  | Operation                                | Name           | Rows  | By
tes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------
-----------------------------------------------------
|   0 | SELECT STATEMENT                         |                |     1 |
840 |       |   147K  (1)| 00:29:28 |       |       |
|*  1 |  COUNT STOPKEY                           |                |       |
    |       |            |          |       |       |
|   2 |   VIEW                                   |                |     1 |
840 |       |   147K  (1)| 00:29:28 |       |       |
|*  3 |    SORT ORDER BY STOPKEY                 |                |     1 |
157 |  8072K|   147K  (1)| 00:29:28 |       |       |
|*  4 |     FILTER                               |                |       |
    |       |            |          |       |       |
|*  5 |      TABLE ACCESS BY GLOBAL INDEX ROWID  | ARTICLE        | 21666 |  3
321K|       | 23447   (1)| 00:04:42 | ROWID | ROWID |
|*  6 |       INDEX RANGE SCAN                   | ARTICLE_SITEID | 64998 |
    |       |    88   (2)| 00:00:02 |       |       |
|*  7 |      FILTER                              |                |       |
    |       |            |          |       |       |
|   8 |       NESTED LOOPS                       |                |     1 |
30 |       |    10   (0)| 00:00:01 |       |       |
|*  9 |        TABLE ACCESS BY GLOBAL INDEX ROWID| ARTICLE        |     1 |
17 |       |     3   (0)| 00:00:01 | ROWID | ROWID |
|* 10 |         INDEX UNIQUE SCAN                | PKKEY_ARTICLE  |     1 |
    |       |     2   (0)| 00:00:01 |       |       |
|  11 |        INLIST ITERATOR                   |                |       |
    |       |            |          |       |       |
|* 12 |         INDEX UNIQUE SCAN                | INFO_KEYWORD   |     1 |
13 |       |     7   (0)| 00:00:01 |       |       |
------------------------------------------------------------------------------
-----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<TO_NUMBER(:SYS_B_12))
   3 - filter(ROWNUM<TO_NUMBER(:SYS_B_12))
   4 - filter( EXISTS (SELECT /*+ */ 0 FROM "ARTICLEKEYWORD" "K","ARTICLE" "A"
WHERE TO_NUMBER(:SYS_B_03)<>:B1 AND
              "A"."ARTICLEID"=:B2 AND "A"."DELETED"=TO_NUMBER(:SYS_B_00) AND "
A"."PASSED"=TO_NUMBER(:SYS_B_01) AND
              "A"."TOHTML"=TO_NUMBER(:SYS_B_02) AND "A"."INFOSHARETYPE"=TO_NUM
BER(:SYS_B_04) AND "A"."ARTICLEID"<>TO_NUMBER(:SYS_B_03)
              AND ("K"."KEYWORD"=:SYS_B_05 OR "K"."KEYWORD"=:SYS_B_06 OR "K"."
KEYWORD"=:SYS_B_07 OR "K"."KEYWORD"=:SYS_B_08 OR
              "K"."KEYWORD"=:SYS_B_09) AND "K"."INFOID"=:B3 AND "K"."INFOID"<>
TO_NUMBER(:SYS_B_03)))
   5 - filter("T"."CSSTYPE"=:SYS_B_11)
   6 - access("T"."SITEID"=TO_NUMBER(:SYS_B_10))
   7 - filter(TO_NUMBER(:SYS_B_03)<>:B1)
   9 - filter("A"."DELETED"=TO_NUMBER(:SYS_B_00) AND "A"."PASSED"=TO_NUMBER(:S
YS_B_01) AND
              "A"."TOHTML"=TO_NUMBER(:SYS_B_02) AND "A"."INFOSHARETYPE"=TO_NUM
BER(:SYS_B_04))
  10 - access("A"."ARTICLEID"=:B1)
       filter("A"."ARTICLEID"<>TO_NUMBER(:SYS_B_03))
  12 - access("K"."INFOID"=:B1 AND ("K"."KEYWORD"=:SYS_B_05 OR "K"."KEYWORD"=:
SYS_B_06 OR "K"."KEYWORD"=:SYS_B_07 OR
              "K"."KEYWORD"=:SYS_B_08 OR "K"."KEYWORD"=:SYS_B_09))
       filter("K"."INFOID"<>TO_NUMBER(:SYS_B_03))

Note
-----
   - SQL profile "LH_ARTICLE" used for this statement

已选择44行。

SQL>

Maclean Liu(刘相兵

请使用 sql health check 脚本 分析该SQL 然后上传HTML
http://www.oracledatabase12g.com ... h-check-script.html