Oracle 静态SQL引起性能问题
发布时间：2020-12-12 16:28:13 所属栏目：百科 来源：网络整理
导读：遇到一个简单的存储过程，执行了半小时都无法执行出来，数据库版本是11.2.0.4.0。SQL的写法是静态SQL的写法，后来改为动态SQL就秒出了。 Oracle编译PL/SQL程序块分为两种：静态SQL和动态SQL。静态SQL采用的是早期绑定，在编译的时候就绑定。动态SQL是在运行
|
遇到一个简单的存储过程，执行了半小时都无法执行出来，数据库版本是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) （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |