ITPUB论坛-中国最专业的IT技术社区

 
 注册
热搜:
查看: 671|回复: 40

如何替换/固化执行计划?

[复制链接]
论坛徽章:
304
奥迪
日期:2013-07-29 13:45:59红旗
日期:2014-02-07 10:47:20路虎
日期:2014-02-13 10:34:03保时捷
日期:2014-02-14 09:46:462014年新春福章
日期:2014-02-18 16:41:11马上有车
日期:2014-02-18 16:41:11马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14
跳转到指定楼层
1#
发表于 2017-11-30 15:52 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
ORACLE 11204,

如下语句(没提示的),执行计划走索引,效率低下,我对T0表作了列表分区(t0.FUSEORGID ),若优化器不走索引,则将走分区裁剪,这样效率提高一倍,
下面的语句,得到的执行计划是我期望的,但应用程序中,并没有 NO_INDEX() 这个提示。现我希望,该语句走不使用索引的执行计划,
也就是下面语句的执行计划(有提示的),问,该如何固化语句使用这个加了提示的执行计划?此外,希望这些加粗变量值改变时,还是使用此执行计划,怎么办到?

SELECT * FROM (SELECT /*+ no_index(T0) */t0.FNUMBER FNUMBER, t0_L.FNAME FNAME, t0.FLOTID FLOTID
FROM T_BD_LOTMASTER T0 LEFT OUTER JOIN T_BD_LOTMASTER_L T0_L ON (t0.FLOTID = t0_L.FLOTID AND t0_L.FLocaleId = 2052)
WHERE (((t0.FUSEORGID IN (100132)) AND t0.FBIZTYPE = '1') AND ((t0.FNUMBER LIKE N'%001Y171125000333%') OR
(t0_L.FNAME LIKE N'%001Y171125000333%'))) ORDER BY t0.FNUMBER ASC) WHERE ROWNUM <= 5
/


论坛徽章:
180
红宝石
日期:2014-05-09 08:24:37萤石
日期:2014-01-03 10:25:39马上有车
日期:2014-02-18 16:41:11马上有钱
日期:2014-11-24 15:17:08马上有钱
日期:2014-11-12 09:33:24马上有房
日期:2014-11-07 08:46:05马上有钱
日期:2014-10-27 09:26:57马上有对象
日期:2014-10-28 10:28:08itpub13周年纪念徽章
日期:2014-10-10 10:38:25马上有对象
日期:2015-01-14 17:33:15
2#
发表于 2017-11-30 16:44 | 只看该作者
sql profile 啊

使用道具 举报

回复
论坛徽章:
180
红宝石
日期:2014-05-09 08:24:37萤石
日期:2014-01-03 10:25:39马上有车
日期:2014-02-18 16:41:11马上有钱
日期:2014-11-24 15:17:08马上有钱
日期:2014-11-12 09:33:24马上有房
日期:2014-11-07 08:46:05马上有钱
日期:2014-10-27 09:26:57马上有对象
日期:2014-10-28 10:28:08itpub13周年纪念徽章
日期:2014-10-10 10:38:25马上有对象
日期:2015-01-14 17:33:15
3#
发表于 2017-11-30 16:46 | 只看该作者
我使用脚本:
-- @create_profile_from_shared_pool c2trqja6wh561 0 TEST true
-- @spsw good_sql_id 0 bad_sql_id 0 test true
-- @spsw good_sql_id 0 bad_sql_id 0 '' true
DECLARE
   ar_profile_hints   SYS.sqlprof_attr;
   cl_sql_text        CLOB;
BEGIN
   SELECT EXTRACTVALUE (VALUE (d), '/hint') AS outline_hints
     BULK COLLECT INTO ar_profile_hints
     FROM XMLTABLE (
             '/*/outline_data/hint'
             PASSING (SELECT xmltype (other_xml) AS xmlval
                        FROM v$sql_plan
                       WHERE     sql_id = '&&1'
                             AND child_number = &&2
                             AND other_xml IS NOT NULL)) d;

   SELECT SQL_FULLTEXT
     INTO cl_sql_text
     FROM -- replace with dba_hist_sqltext
          -- if required for AWR based
          -- execution
          v$sql
    -- sys.dba_hist_sqltext
    WHERE sql_id = '&&3' AND child_number = &&4;

   -- plan_hash_value = &&2;

   DBMS_SQLTUNE.import_sql_profile (sql_text      => cl_sql_text,
                                    profile       => ar_profile_hints,
                                    category      => '&&5',
                                    DESCRIPTION   => 'switch &&1 => &&3',
                                                                        name          => 'switch tuning &&3' -- use force_match => true
                                                                         -- to use CURSOR_SHARING=SIMILAR
                                                                         -- behaviour, i.e. match even with
                                                                         -- differing literals
                                    ,
                                    force_match   => &&6);
END;
/

使用道具 举报

回复
论坛徽章:
180
红宝石
日期:2014-05-09 08:24:37萤石
日期:2014-01-03 10:25:39马上有车
日期:2014-02-18 16:41:11马上有钱
日期:2014-11-24 15:17:08马上有钱
日期:2014-11-12 09:33:24马上有房
日期:2014-11-07 08:46:05马上有钱
日期:2014-10-27 09:26:57马上有对象
日期:2014-10-28 10:28:08itpub13周年纪念徽章
日期:2014-10-10 10:38:25马上有对象
日期:2015-01-14 17:33:15
4#
发表于 2017-11-30 16:49 | 只看该作者
>些加粗变量值改变时,还是使用此执行计划,怎么办到?
force_match   => true就是这个功能.

使用道具 举报

回复
论坛徽章:
180
红宝石
日期:2014-05-09 08:24:37萤石
日期:2014-01-03 10:25:39马上有车
日期:2014-02-18 16:41:11马上有钱
日期:2014-11-24 15:17:08马上有钱
日期:2014-11-12 09:33:24马上有房
日期:2014-11-07 08:46:05马上有钱
日期:2014-10-27 09:26:57马上有对象
日期:2014-10-28 10:28:08itpub13周年纪念徽章
日期:2014-10-10 10:38:25马上有对象
日期:2015-01-14 17:33:15
5#
发表于 2017-11-30 16:51 | 只看该作者
另外你提示没有超过500吧.

使用道具 举报

回复
论坛徽章:
304
奥迪
日期:2013-07-29 13:45:59红旗
日期:2014-02-07 10:47:20路虎
日期:2014-02-13 10:34:03保时捷
日期:2014-02-14 09:46:462014年新春福章
日期:2014-02-18 16:41:11马上有车
日期:2014-02-18 16:41:11马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14
6#
 楼主| 发表于 2017-11-30 18:16 | 只看该作者
lfree 发表于 2017-11-30 16:51
另外你提示没有超过500吧.

提示没有超过500,什么意思?

使用道具 举报

回复
论坛徽章:
180
红宝石
日期:2014-05-09 08:24:37萤石
日期:2014-01-03 10:25:39马上有车
日期:2014-02-18 16:41:11马上有钱
日期:2014-11-24 15:17:08马上有钱
日期:2014-11-12 09:33:24马上有房
日期:2014-11-07 08:46:05马上有钱
日期:2014-10-27 09:26:57马上有对象
日期:2014-10-28 10:28:08itpub13周年纪念徽章
日期:2014-10-10 10:38:25马上有对象
日期:2015-01-14 17:33:15
7#
发表于 2017-12-1 08:32 | 只看该作者
ZALBB 发表于 2017-11-30 18:16
提示没有超过500,什么意思?

提示超过500,我上面的脚本无法执行.

使用道具 举报

回复
认证徽章
论坛徽章:
8
2009新春纪念徽章
日期:2009-01-04 14:52:28祖国60周年纪念徽章
日期:2009-10-09 08:28:002010新春纪念徽章
日期:2010-03-01 11:07:24ITPUB9周年纪念徽章
日期:2010-10-08 09:32:25ITPUB十周年纪念徽章
日期:2011-11-01 16:23:262013年新春福章
日期:2013-02-25 14:51:24沸羊羊
日期:2015-03-04 14:51:522015年新春福章
日期:2015-03-06 11:57:31
8#
发表于 2017-12-1 14:30 | 只看该作者
lfree 发表于 2017-11-30 16:51
另外你提示没有超过500吧.

dbms_sqltune.import_sql_profile.profile-->(重载)profile_xml。

使用道具 举报

回复
认证徽章
论坛徽章:
8
2009新春纪念徽章
日期:2009-01-04 14:52:28祖国60周年纪念徽章
日期:2009-10-09 08:28:002010新春纪念徽章
日期:2010-03-01 11:07:24ITPUB9周年纪念徽章
日期:2010-10-08 09:32:25ITPUB十周年纪念徽章
日期:2011-11-01 16:23:262013年新春福章
日期:2013-02-25 14:51:24沸羊羊
日期:2015-03-04 14:51:522015年新春福章
日期:2015-03-06 11:57:31
9#
发表于 2017-12-1 15:59 | 只看该作者
ZALBB 发表于 2017-11-30 18:16
提示没有超过500,什么意思?

你这个应该不会超过500,按照lfree老大的回复试试吧。

使用道具 举报

回复
论坛徽章:
304
奥迪
日期:2013-07-29 13:45:59红旗
日期:2014-02-07 10:47:20路虎
日期:2014-02-13 10:34:03保时捷
日期:2014-02-14 09:46:462014年新春福章
日期:2014-02-18 16:41:11马上有车
日期:2014-02-18 16:41:11马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14
10#
 楼主| 发表于 2017-12-6 18:14 | 只看该作者
lfree 发表于 2017-11-30 16:46
我使用脚本:
-- @create_profile_from_shared_pool c2trqja6wh561 0 TEST true
-- @spsw good_sql_id 0  ...

没见效果,请帮忙看看哪里有问题?


--1  这是使用了提示的执行计划,可以看到,对 T_BD_LOTMASTER 走分区裁剪扫描
KDSA918@clouddb_1>explain plan for
  2  SELECT * FROM (SELECT /*+ no_index(T0) */t0.FNUMBER FNUMBER, t0_L.FNAME FNAME, t0.FLOTID FLOTID
FROM T_BD_LOTMASTER T0 LEFT OUTER JOIN T_BD_LOTMASTER_L T0_L ON (t0.FLOTID = t0_L.FLOTID AND t0_L.FLocaleId = 2052)
WHERE (((t0.FUSEORGID IN (100132)) AND t0.FBIZTYPE = '1') AND ((t0.FNUMBER LIKE N'%001Y171125000333%') OR
(t0_L.FNAME LIKE N'%001Y171125000333%'))) ORDER BY t0.FNUMBER ASC) WHERE ROWNUM <= 5
/  3    4    5    6  

Explained.

Elapsed: 00:00:00.01
KDSA918@clouddb_1>select * from display;

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
Plan hash value: 1759511327

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                |     5 |  2635 |       |   164K  (3)| 00:05:07 |       |       |
|*  1 |  COUNT STOPKEY             |                |       |       |       |            |          |       |       |
|   2 |   VIEW                     |                | 21115 |    10M|       |   164K  (3)| 00:05:07 |       |       |
|*  3 |    SORT ORDER BY STOPKEY   |                | 21115 |  1917K|   222M|   164K  (3)| 00:05:07 |       |       |
|*  4 |     FILTER                 |                |       |       |       |            |          |       |       |
|*  5 |      HASH JOIN OUTER       |                | 21115 |  1917K|   127M|   115K  (4)| 00:03:35 |       |       |
|   6 |       PARTITION LIST SINGLE|                |  2221K|   101M|       | 66348   (3)| 00:02:04 |   KEY |   KEY |
|   7 |        PARTITION LIST ALL  |                |  2221K|   101M|       | 66348   (3)| 00:02:04 |     1 |     3 |
|*  8 |         TABLE ACCESS FULL  | T_BD_LOTMASTER |  2221K|   101M|       | 66348   (3)| 00:02:04 |   KEY |   KEY |
|*  9 |       INDEX FAST FULL SCAN | IDX_TEST2      |  8292K|   355M|       | 18069   (4)| 00:00:34 |       |       |
---------------------------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=5)
   3 - filter(ROWNUM<=5)
   4 - filter("T0"."FNUMBER" LIKE U'%001Y171125000333%' OR "T0_L"."FNAME" LIKE U'%001Y171125000333%')
   5 - access("T0"."FLOTID"="T0_L"."FLOTID"(+))
   8 - filter("T0"."FUSEORGID"=100132)
   9 - filter("T0_L"."FLOCALEID"(+)=2052)

26 rows selected.

Elapsed: 00:00:00.02

--2  在SQLPLUS里执行,耗时11秒
KDSA918@clouddb_1>SELECT * FROM (SELECT /*+ no_index(T0) */t0.FNUMBER FNUMBER, t0_L.FNAME FNAME, t0.FLOTID FLOTID
FROM T_BD_LOTMASTER T0 LEFT OUTER JOIN T_BD_LOTMASTER_L T0_L ON (t0.FLOTID = t0_L.FLOTID AND t0_L.FLocaleId = 2052)
WHERE (((t0.FUSEORGID IN (100132)) AND t0.FBIZTYPE = '1') AND ((t0.FNUMBER LIKE N'%001Y171125000333%') OR
(t0_L.FNAME LIKE N'%001Y171125000333%'))) ORDER BY t0.FNUMBER ASC) WHERE ROWNUM <= 5
/  2    3    4    5  

FNUMBER
--------------------------------------------------------------------------------------------------------------------------
FNAME
--------------------------------------------------------------------------------------------------------------------------
    FLOTID
----------
1001Y171125000333
1001Y171125000333
   9461045


Elapsed: 00:00:10.91

--3 通过EM,找到该语句的sql_id后,再找出 child_number
KDSA918@clouddb_1>select child_number from v$sql where sql_id='0tf2tg7bj39xy';

CHILD_NUMBER
------------
           0

Elapsed: 00:00:00.01
KDSA918@clouddb_1>select child_number from v$sql_plan where sql_id='0tf2tg7bj39xy' group by child_number;

CHILD_NUMBER
------------
           0

Elapsed: 00:00:00.03

--4  这是原始语句,我希望此语句,使用有 hint 的执行计划,注意此语句耗时24秒
KDSA918@clouddb_1>SELECT * FROM (SELECT t0.FNUMBER FNUMBER, t0_L.FNAME FNAME, t0.FLOTID FLOTID
FROM T_BD_LOTMASTER T0 LEFT OUTER JOIN T_BD_LOTMASTER_L T0_L ON (t0.FLOTID = t0_L.FLOTID AND t0_L.FLocaleId = 2052)
WHERE (((t0.FUSEORGID IN (100132)) AND t0.FBIZTYPE = '1') AND ((t0.FNUMBER LIKE N'%001Y171125000333%') OR
(t0_L.FNAME LIKE N'%001Y171125000333%'))) ORDER BY t0.FNUMBER ASC) WHERE ROWNUM <= 5
/  2    3    4    5  

FNUMBER
----------------------------------------------------------------------------------------------------------------------------
FNAME
----------------------------------------------------------------------------------------------------------------------------
    FLOTID
----------
1001Y171125000333
1001Y171125000333
   9461045


Elapsed: 00:00:24.83

--5  通过EM,找到该语句的sql_id后,再找出 child_number
KDSA918@clouddb_1>select child_number from v$sql_plan where sql_id='0s9paumn4u4g6' group by child_number;

CHILD_NUMBER
------------
           0

Elapsed: 00:00:00.01

--6  执行更换脚本
KDSA918@clouddb_1>DECLARE
   ar_profile_hints   SYS.sqlprof_attr;
   cl_sql_text        CLOB;
BEGIN
   SELECT EXTRACTVALUE (VALUE (d), '/hint') AS outline_hints
     BULK COLLECT INTO ar_profile_hints
     FROM XMLTABLE ('/*/outline_data/hint'
             PASSING (SELECT xmltype (other_xml) AS xmlval
                        FROM v$sql_plan
                       WHERE     sql_id = '0tf2tg7bj39xy' -- 这是带了 hint 的语句的 SQL_ID 及 child_number = 0
                             AND child_number = 0
                             AND other_xml IS NOT NULL)) d;

   SELECT SQL_FULL  2    3    4    5  TEXT
     INTO cl_sql_text
     FROM -- replace with dba_hist_sqltext
          -- if required for AWR based
          -- execution
          v$sql
    -- sys.dba_hist_sqltext
    WHERE sql_id = '0s9paumn4u4g6' AND child_number = 0; -- 这是原始语句的 SQL_ID 及 child_number = 0

   

   DBMS_SQLTUNE.import_sql_profile (sql_text      => cl_sql_text,
                                    profile       => ar_profile_hints,
                                    category      => 'zhf_sql_profile_bindvalue',
                                    DESCRIPTION   => 'switch 0tf2tg7bj39xy => 0s9paumn4u4g6',
                                    name          => 'switch tuning 0s9paumn4u4g6'
                                                                         -- use force_match => true
                                                                         -- to use CURSOR_SHARING=SIMILAR
                                                                         -- behaviour, i.e. match even with
                                                                         -  6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   32   33   34   35   36   37  
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.57
KDSA918@clouddb_1>

--6 测试原始语句的执行计划,并未变
KDSA918@clouddb_1>explain plan for
  2  SELECT * FROM (SELECT t0.FNUMBER FNUMBER, t0_L.FNAME FNAME, t0.FLOTID FLOTID
FROM T_BD_LOTMASTER T0 LEFT OUTER JOIN T_BD_LOTMASTER_L T0_L ON (t0.FLOTID = t0_L.FLOTID AND t0_L.FLocaleId = 2052)
WHERE (((t0.FUSEORGID IN (100132)) AND t0.FBIZTYPE = '1') AND ((t0.FNUMBER LIKE N'%001Y171125000333%') OR
(t0_L.FNAME LIKE N'%001Y171125000333%'))) ORDER BY t0.FNUMBER ASC) WHERE ROWNUM <= 5
/  3    4    5    6  

Explained.

Elapsed: 00:00:00.02
KDSA918@clouddb_1>select * from display;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 19389659

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                     |     5 |  2635 |   193   (0)| 00:00:01 |       |       |
|*  1 |  COUNT STOPKEY                         |                     |       |       |            |          |       |       |
|   2 |   VIEW                                 |                     |     6 |  3162 |   193   (0)| 00:00:01 |       |       |
|*  3 |    FILTER                              |                     |       |       |            |          |       |       |
|   4 |     NESTED LOOPS OUTER                 |                     |     6 |   558 |   193   (0)| 00:00:01 |       |       |
|*  5 |      TABLE ACCESS BY GLOBAL INDEX ROWID| T_BD_LOTMASTER      |  2221K|   101M|    94   (0)| 00:00:01 | ROWID | ROWID |
|*  6 |       INDEX SKIP SCAN                  | IDX_BDLOTMASTERTEST |    59 |       |     4   (0)| 00:00:01 |       |       |
|*  7 |      INDEX RANGE SCAN                  | IDX_TEST2           |     1 |    45 |     2   (0)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=5)
   3 - filter("T0"."FNUMBER" LIKE U'%001Y171125000333%' OR "T0_L"."FNAME" LIKE U'%001Y171125000333%')
   5 - filter("T0"."FUSEORGID"=100132)
   6 - access("T0"."FBIZTYPE"='1')
       filter("T0"."FBIZTYPE"='1')
   7 - access("T0"."FLOTID"="T0_L"."FLOTID"(+) AND "T0_L"."FLOCALEID"(+)=2052)
       filter("T0_L"."FLOCALEID"(+)=2052)

25 rows selected.

Elapsed: 00:00:00.05
KDSA918@clouddb_1>

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 |
  | | |
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表