oracle如何更改执行计划扫描,如何替换/固化执行计划？

没见效果，请帮忙看看哪里有问题？

--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>