前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >【DB笔试面试623】在Oracle中,给出下面语句的可能的优化思路。

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

作者头像
AiDBA宝典
发布2019-09-29 16:11:58
5180
发布2019-09-29 16:11:58
举报
文章被收录于专栏:小麦苗的DB宝专栏

题目部分

在Oracle中,给出下面语句的可能的优化思路。

代码语言:javascript
复制
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;”。

实验如下所示:

代码语言:javascript
复制
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秒完成,因为未用绑定变量:

代码语言:javascript
复制
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%';
一、 采用绑定变量
代码语言:javascript
复制
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
代码语言:javascript
复制
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会自动使用绑定变量。

循环外提交
代码语言:javascript
复制
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秒即可完成。

去掉存储过程,采用插入表的写法
代码语言:javascript
复制
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,所以下面采用直接路径。

五、 直接路径
代码语言:javascript
复制
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;
六、 并行原理
代码语言:javascript
复制
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程序员面试笔试宝典》,作者:李华荣。

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2019-08-24,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 DB宝 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 一、 采用绑定变量
  • 二、 将动态SQL改造成静态SQL
  • 三、 循环外提交
  • 四、 去掉存储过程,采用插入表的写法
  • 五、 直接路径
  • 六、 并行原理
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档