|
|
没见效果，请帮忙看看哪里有问题？
--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>
|
|
IP卡
狗仔卡
发表于 2017-11-30 15:52
收藏
提升卡
置顶卡
变色卡
显身卡
楼主
