【DB筆試面試623】在Oracle中，給出下面語句的可能的優化思路。

2019 年 10 月 10 日
筆記

♣

題目部分

在Oracle中，給出下面語句的可能的優化思路。

CREATE TABLE T_YH_20170705_LHR( X INT);  BEGIN      FOR I IN 1 .. 100000 LOOP          EXECUTE IMMEDIATE 'INSERT INTO T_YH_20170705_LHR VALUES ( '||I||')';      COMMIT;      END LOOP;  END;

♣

答案部分

優化思路有：①採用綁定變數；②使用靜態SQL；③採用批量提交或循環外提交；④根據功能，可以去掉PL/SQL塊，採用直接一次性插入的方式來完成，SQL為「INSERT INTO T_YH_20170705_LHR SELECT ROWNUM FROM DUAL CONNECT BY LEVEL<=100000;」；⑤採用直接路徑方式，例如，「CREATE TABLE T_YH_20170705_LHR AS SELECT ROWNUM X FROM DUAL CONNECT BY LEVEL<=100000;」；⑥採用NOLOGGING和PARALLEL的方式，例如，「CREATE TABLE T_YH_20170705_LHR NOLOGGING PARALLEL 8 AS SELECT ROWNUM X FROM DUAL CONNECT BY LEVEL<=100000;」。

實驗如下所示：

DROP TABLE T_YH_20170705_LHR PURGE;  CREATE TABLE T_YH_20170705_LHR( X INT);  ALTER SYSTEM FLUSH SHARED_POOL;  CREATE OR REPLACE PROCEDURE PRO_YH_LHR  AS  BEGIN      FOR I IN 1 .. 100000 LOOP          EXECUTE IMMEDIATE 'INSERT INTO T_YH_20170705_LHR VALUES ( '||I||')';      COMMIT;      END LOOP;  END;  /  SET TIMING ON  EXEC PRO_YH_LHR; --Elapsed: 00:00:59.45

首次實驗需要40秒完成，因為未用綁定變數：

SELECT T.SQL_TEXT, T.SQL_ID,T.PARSE_CALLS, T.EXECUTIONS    FROM V$SQL T   WHERE SQL_TEXT LIKE 'INSERT INTO T_YH_20170705_LHR VALUES%';

一、採用綁定變數

DROP TABLE T_YH_20170705_LHR PURGE;  CREATE TABLE T_YH_20170705_LHR( X INT);  ALTER SYSTEM FLUSH SHARED_POOL;  CREATE OR REPLACE PROCEDURE PRO_YH_LHR2  AS  BEGIN      FOR I IN 1 .. 100000 LOOP           EXECUTE IMMEDIATE  'INSERT INTO T_YH_20170705_LHR VALUES ( :X )' USING I;      COMMIT;      END LOOP;  END;  /  SET TIMING ON  EXEC PRO_YH_LHR2; --Elapsed: 00:00:08.14    --採用綁定變數後，8秒鐘執行完成。SQL解析次數也變為了1次：  SELECT T.SQL_TEXT, T.SQL_ID,T.PARSE_CALLS, T.EXECUTIONS    FROM V$SQL T   WHERE SQL_TEXT LIKE 'INSERT INTO T_YH_20170705_LHR VALUES%';

二、將動態SQL改造成靜態SQL

DROP TABLE T_YH_20170705_LHR PURGE;  CREATE TABLE T_YH_20170705_LHR ( X INT);  ALTER SYSTEM FLUSH SHARED_POOL;  CREATE OR REPLACE PROCEDURE PRO_YH_LHR3  AS  BEGIN      FOR I IN 1 .. 100000 LOOP           INSERT INTO T_YH_20170705_LHR VALUES (I);      COMMIT;      END LOOP;  END;  /  SET TIMING ON  EXEC PRO_YH_LHR3; --Elapsed: 00:00:05.99    --改造後6秒鐘即可完成，再次查看解析情況：  SELECT T.SQL_TEXT, T.SQL_ID,T.PARSE_CALLS, T.EXECUTIONS    FROM V$SQL T   WHERE SQL_TEXT LIKE 'INSERT INTO T_YH_20170705_LHR VALUES%';

靜態SQL會自動使用綁定變數。

三、循環外提交

DROP TABLE T_YH_20170705_LHR PURGE;  CREATE TABLE T_YH_20170705_LHR ( X INT);  ALTER SYSTEM FLUSH SHARED_POOL;  CREATE OR REPLACE PROCEDURE PRO_YH_LHR4  AS  BEGIN      FOR I IN 1 .. 100000 LOOP           INSERT INTO T_YH_20170705_LHR VALUES (I);  END LOOP;      COMMIT;  END;  /  SET TIMING ON  EXEC PRO_YH_LHR4; --Elapsed: 00:00:04.75

5秒即可完成。

四、去掉存儲過程，採用插入表的寫法

DROP TABLE T_YH_20170705_LHR PURGE;  CREATE TABLE T_YH_20170705_LHR( X INT);  ALTER SYSTEM FLUSH SHARED_POOL;  SET TIMING ON  INSERT INTO T_YH_20170705_LHR SELECT ROWNUM FROM DUAL CONNECT BY LEVEL<=100000;--Elapsed: 00:00:00.01  COMMIT;  SELECT COUNT(*) FROM T_YH_20170705_LHR;

用了0.01秒，太快了。由於要使用到Data Buffer，所以下面採用直接路徑。

五、直接路徑

DROP TABLE T_YH_20170705_LHR PURGE;  ALTER SYSTEM FLUSH SHARED_POOL;  SET TIMING ON  CREATE TABLE T_YH_20170705_LHR AS SELECT ROWNUM X FROM DUAL CONNECT BY LEVEL<=100000;

六、並行原理

DROP TABLE T_YH_20170705_LHR PURGE;  ALTER SYSTEM FLUSH SHARED_POOL;  SET TIMING ON  CREATE TABLE T_YH_20170705_LHR NOLOGGING PARALLEL 8 AS SELECT ROWNUM X FROM DUAL CONNECT BY LEVEL<=100000;

本文選自《Oracle程式設計師面試筆試寶典》，作者：李華榮。