遇到一个简单的存储过程，执行了半小时都无法执行出来，数据库版本是11.2.0.4.0。sql的写法是静态sql的写法，后来改为动态sql就秒出了。
Oracle编译PL/sql程序块分为两种：静态sql和动态sql。静态sql采用的是早期绑定，在编译的时候就绑定。动态sql是在运行的时候绑定，从两者的执行计划中就可以看出区别。
...................................
静态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)
