【DB笔试面试260】在Oracle中,给出下面语句的几种可能的优化思路。

【DB笔试面试260】在Oracle中,给出下面语句的几种可能的优化思路。

【DB笔试面试260】在Oracle中,给出下面语句的几种可能的优化思路。


Q          题目如下所示:

在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;



     
A          答案如下所示:          


 优化思路有:①采用绑定变量;②使用静态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;


上一篇:conan安装使用


下一篇:Oracle修改密码复杂度策略