Oracle 静态SQL引起性能问题

本是古典何须时尚 2022-06-09 01:45 366阅读 0赞

遇到一个简单的存储过程，执行了半小时都无法执行出来，数据库版本是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)