Oracle 静态SQL引起性能问题

来源：互联网发布：苏州网络推广编辑：程序博客网时间：2024/06/05 01:51

遇到一个简单的存储过程，执行了半小时都无法执行出来，数据库版本是11.2.0.4.0。SQL的写法是静态SQL的写法，后来改为动态SQL就秒出了。

Oracle编译PL/SQL程序块分为两种：静态SQL和动态SQL。静态SQL采用的是早期绑定，在编译的时候就绑定。动态SQL是在运行的时候绑定，从两者的执行计划中就可以看出区别。

CREATE OR REPLACE PROCEDURE P_test(PURCHASE_ID IN VARCHAR2) IS
...................................
静态SQL的写法：
INSERT INTO GG_OBJECT_TRACK_G
SELECT T.OBJECT_ID, T.TRACK_ID
FROM GG_OBJECT_TRACK T
WHERE T.STATUS = 0
AND T.OBJECT_ID IN
(SELECT K.REQUIREMENT_ITEM_ID
FROM GG_PURCHASE_ITEM K
WHERE K.PURCHASE_ID = PURCHASE_ID);
HASH_VALUE 1763960727, child number 2
--------------------------------------
INSERT INTO GG_OBJECT_TRACK_G SELECT T.OBJECT_ID, T.TRACK_ID FROM
GG_OBJECT_TRACK T WHERE T.STATUS = 0 AND T.OBJECT_ID IN (SELECT
K.REQUIREMENT_ITEM_ID FROM GG_PURCHASE_ITEM K WHERE K.PURCHASE_ID =
PURCHASE_ID)
Plan hash value: 1111317390
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | | 977K(100)| | | |
| 1 | LOAD TABLE CONVENTIONAL | | | | | | | | |
|* 2 | HASH JOIN SEMI | | 273K| 22M| 157M| 977K (1)| 03:15:27 | | |
| 3 | PARTITION LIST ALL | | 2855K| 125M| | 797K (1)| 02:39:28 | 1 | 27 |
|* 4 | TABLE ACCESS FULL | GG_OBJECT_TRACK | 2855K| 125M| | 797K (1)| 02:39:28 | 1 | 27 |
| 5 | VIEW | index$_join$_003 | 7282K| 291M| | 153K (1)| 00:30:40 | | |
|* 6 | HASH JOIN | | | | | | | | |
| 7 | INDEX FAST FULL SCAN| IDX_SUPERVISE_PLAN_1 | 7282K| 291M| | 86442 (1)| 00:17:18 | | |
| 8 | INDEX FAST FULL SCAN| INDEX_REQUIREMENT_ITEM_ID_1 | 7282K| 291M| | 67835 (1)| 00:13:35 | | |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."OBJECT_ID"="K"."REQUIREMENT_ITEM_ID")
4 - filter(TO_NUMBER("T"."STATUS")=0)
6 - access(ROWID=ROWID)

动态SQL的写法：
v_sql1 :='INSERT INTO GG_OBJECT_TRACK_G
SELECT T.OBJECT_ID, T.TRACK_ID
FROM GG_OBJECT_TRACK T
WHERE T.STATUS = 0
AND T.OBJECT_ID IN
(SELECT K.REQUIREMENT_ITEM_ID
FROM GG_PURCHASE_ITEM K
WHERE K.PURCHASE_ID = :1)';
EXECUTE IGGEDIATE v_sql1 USING PURCHASE_ID;

HASH_VALUE 3518446178, child number 0
--------------------------------------
INSERT INTO GG_OBJECT_TRACK_G SELECT T.OBJECT_ID, T.TRACK_ID
FROM GG_OBJECT_TRACK T WHERE T.STATUS = 0 AND T.OBJECT_ID
IN (SELECT K.REQUIREMENT_ITEM_ID FROM
GG_PURCHASE_ITEM K WHERE K.PURCHASE_ID = :1)
Plan hash value: 1838864513
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 38 (100)| | | |
| 1 | LOAD TABLE CONVENTIONAL | | | | | | | |
| 2 | NESTED LOOPS | | 20 | 1760 | 38 (3)| 00:00:01 | | |
| 3 | NESTED LOOPS | | 20 | 1760 | 38 (3)| 00:00:01 | | |
| 4 | SORT UNIQUE | | 16 | 672 | 6 (0)| 00:00:01 | | |
| 5 | TABLE ACCESS BY GLOBAL INDEX ROWID| GG_PURCHASE_ITEM | 16 | 672 | 6 (0)| 00:00:01 | ROWID | ROWID |
|* 6 | INDEX RANGE SCAN | I_GG_PURCHASE_ITEM_MAIN_1 | 16 | | 3 (0)| 00:00:01 | | |
|* 7 | INDEX RANGE SCAN | INDEX_OBJECTIDANDSTATUS_1 | 1 | | 3 (0)| 00:00:01 | | |
| 8 | TABLE ACCESS BY GLOBAL INDEX ROWID | GG_OBJECT_TRACK | 1 | 46 | 4 (0)| 00:00:01 | ROWID | ROWID |
------------------------------------------------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (VARCHAR2(30), CSID=852): '0306PO2017080224'
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("K"."PURCHASE_ID"=:1)
7 - access("T"."OBJECT_ID"="K"."REQUIREMENT_ITEM_ID")
filter(TO_NUMBER("T"."STATUS")=0)

阅读全文

0 0