lhpapa

86 积分	0 好友	2 主题

发消息

请教sqlprofile使用问题。

1^#

发表于 2012-5-10 16:43:14 | 查看: 4527| 回复: 2

有一大段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 编辑 ]

分享0

收藏0 回复只看该作者道具举报

lhpapa

2^#

发表于 2012-5-10 16:43:52

现在想用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(刘相兵

3^#

发表于 2012-5-10 19:12:53

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

回复只看该作者道具举报

返回列表

		自动登录	找回密码
密码			注册