DMSQL 插入语句性能测试

1.创建数据库后进行如下参数修改

call SF_SET_SYSTEM_PARA_VALUE ('MAX_OS_MEMORY',95,1,2);
call SF_SET_SYSTEM_PARA_VALUE ('MAX_SESSIONS',1000,1,2);
call SF_SET_SYSTEM_PARA_VALUE ('MAX_SESSION_STATEMENT',5000,1,2);
call SF_SET_SYSTEM_PARA_VALUE ('BUFFER',1024,1,2);
call SF_SET_SYSTEM_PARA_VALUE ('MAX_BUFFER',1024,1,2);
call SF_SET_SYSTEM_PARA_VALUE ('BUFFER_POOLS',2,1,2);
call SF_SET_SYSTEM_PARA_VALUE ('RECYCLE',512,1,2);
call SF_SET_SYSTEM_PARA_VALUE ('RECYCLE_POOLS',1,1,2);
call SF_SET_SYSTEM_PARA_VALUE ('HJ_BUF_GLOBAL_SIZE',500,1,2);
call SF_SET_SYSTEM_PARA_VALUE ('HJ_BUF_SIZE',300,1,2);
call SF_SET_SYSTEM_PARA_VALUE ('DICT_BUF_SIZE',50,1,2);
call SF_SET_SYSTEM_PARA_VALUE ('TEMP_SIZE',200,1,2);
call SF_SET_SYSTEM_PARA_VALUE ('VM_POOL_SIZE',256,1,2);
call SF_SET_SYSTEM_PARA_VALUE ('SESS_POOL_SIZE',256,1,2);
call SF_SET_SYSTEM_PARA_VALUE ('CACHE_POOL_SIZE',200,1,2);
call SF_SET_SYSTEM_PARA_VALUE ('WORKER_THREADS',2,1,2);
call SF_SET_SYSTEM_PARA_VALUE ('TASK_THREADS',2,1,2);
call SF_SET_SYSTEM_PARA_VALUE ('USE_PLN_POOL',1,1,2);
call SF_SET_SYSTEM_PARA_VALUE ('OLAP_FLAG',2,1,2);
call SF_SET_SYSTEM_PARA_VALUE ('OPTIMIZER_MODE',1,1,2);
call SF_SET_SYSTEM_PARA_VALUE ('VIEW_PULLUP_FLAG',1,1,2);
call SF_SET_SYSTEM_PARA_VALUE ('COMPATIBLE_MODE',2,1,2);
call SF_SET_SYSTEM_PARA_VALUE ('ENABLE_MONITOR',1,1,2);
call SF_SET_SYSTEM_PARA_VALUE ('SVR_LOG',0,1,2);

修改后重启数据库实例
会用到的清除执行计划结果集缓存的过程

首先构造环境，保证下列语句执行过了了，T表已经存在：

disql sysdba/dameng123
DROP TABLE T purge;
CREATE TABLE t (x int);
--情况共享池
--清空执行缓存信息
CALL SP_CLEAR_PLAN_CACHE();

--写一个简单的存储过程，实现将1到10万的值插入T表的需求

CREATE OR REPLACE PROCEDURE proc1
AS
BEGIN
FOR i IN 1 .. 100000
loop
EXECUTE immediate
'insert into T values('||i||')';
commit;
END loop;
end;
/
--这里记得先预先执行一遍，将过程创建起来

语句从需求实现上没有问题，执行如下

disql sysdba/dameng123
DROP TABLE T purge;
CREATE TABLE t (x int);

--清空共享池
--清空执行缓存信息
CALL SP_CLEAR_PLAN_CACHE();

SET timing ON 
EXEC proc1;
DMSQL 过程已成功完成
已用时间: 00:00:21.771. 执行号:505
SELECT COUNT(*) FROM t;

行号     COUNT(*)            
---------- --------------------
1          100000

SELECT COUNT(*) FROM v$cachesql;
SELECT COUNT(*) FROM v$cachepln;

--绑定变量，单车变摩托

改进存储过程写法

CREATE OR REPLACE PROCEDURE proc2
AS 
BEGIN
FOR i IN 1 .. 100000
loop
EXECUTE immediate
'insert into t values (:x)' using i;
commit;
END loop;
end;
/

--这里要预先执行一遍，将偶刚才创建起来！

--继续测试proc2过程，重建的目的是为了公平，测试都在无记录的空表上进行，并且共享池都清空，

DROP TABLE T purge;
CREATE TABLE T (x int);
--清空共享池
--清空执行缓存信息
CALL SP_CLEAR_PLAN_CACHE();

SET timing ON

EXEC proc2;

SELECT COUNT(*) FROM t;

SELECT COUNT(*) FROM v$cachesql;
SELECT COUNT(*) FROM v$cachepln;

--静态改写，摩托变汽车
EXECUTE immediate 是一种动态SQL的写法，常用于表名字段名是表里、入参的情况，由于表名都不知道，所以当然不能直接写SQL语句，所以要考动态SQL语句根据传入的表名参数，来拼成一条SQL语句，由EXECUTE immediate调用执行。但这里显然不需要这么做，因为insert INTO t VALUES(i)完全可以满足需求，表名就是t。

改写成proc3

CREATE OR REPLACE PROCEDURE proc3
AS 
BEGIN
FOR i IN 1 .. 100000
loop
insert into t values (i);
commit;
END loop;
end;
/
--这里要预先执行一遍，将偶刚才创建起来！

DROP TABLE T purge;
CREATE TABLE T (x int);
--清空共享池
--清空执行缓存信息
CALL SP_CLEAR_PLAN_CACHE();

SET timing ON

EXEC proc3;

SELECT COUNT(*) FROM t;

SELECT COUNT(*) FROM v$cachesql;
SELECT COUNT(*) FROM v$cachepln;

一般来说，静态SQL会自动使用绑定变量。而动态SQL的特点是执行过程中再解析，而静态SQL的特点是编译的过程就解析好了。这点差别就是上帝在提升的原因。

--批量提交，汽车变动车

commit触发将REDO BUFFER写出到REDO LOG中，并将回滚段的活动事务标记为不活动，同时让回滚段中记录对应前镜像记录的所在位置标记为可以重写。

改写proc4

CREATE OR REPLACE PROCEDURE proc4
AS 
BEGIN
FOR i IN 1 .. 100000
loop
insert into t values (i);
END loop;
commit;
end;
/

--这里要预先执行一遍，将偶刚才创建起来！

DROP TABLE T purge;
CREATE TABLE T (x int);
--清空共享池
--清空执行缓存信息
CALL SP_CLEAR_PLAN_CACHE();

SET timing ON

EXEC proc4;

SELECT COUNT(*) FROM t;

SELECT COUNT(*) FROM v$cachesql;
SELECT COUNT(*) FROM v$cachepln;

--集合写法，动车变飞机

INSERT INTO t SELECT rownum FROM dual connect BY level<=100000;

DROP TABLE T purge;
CREATE TABLE T (x int);
--清空共享池
--清空执行缓存信息
CALL SP_CLEAR_PLAN_CACHE();

SET timing ON

INSERT INTO t SELECT rownum FROM dual connect BY level<=100000;

SELECT COUNT(*) FROM t;

SELECT COUNT(*) FROM v$cachesql;
SELECT COUNT(*) FROM v$cachepln;

原先的过程变成了SQL，一条一条插入的语句变成了一个集合得概率，变成了一整批地写进DATA BUFFER区里，好比运砖头到目的地，一种是一块砖一块砖的拿到目的地，直到全部拿完。另一种是全部放在板车上一起推到目的地。这里的目的地就是DATA BUFFER区。

--直接路径，飞机变火箭

DROP TABLE T purge;
CREATE TABLE T (x int);
--清空共享池
--清空执行缓存信息
CALL SP_CLEAR_PLAN_CACHE();

SET timing ON

INSERT INTO t SELECT rownum FROM dual connect BY level<=10000000;

SELECT COUNT(*) FROM t;

SELECT COUNT(*) FROM v$cachesql;
SELECT COUNT(*) FROM v$cachepln;

DROP TABLE T purge;

--清空共享池
--清空执行缓存信息
CALL SP_CLEAR_PLAN_CACHE();

SET timing ON

CREATE TABLE T AS SELECT rownum x FROM dual connect BY level<=10000000;

SELECT COUNT(*) FROM t;

SELECT COUNT(*) FROM v$cachesql;
SELECT COUNT(*) FROM v$cachepln;

速度提升的原因，INSERT INTO t SELECT ......的方式是将数据先写到DATA BUFFER中，然后再刷到磁盘中。而 CREATE TABLE T AS SELECT ......的方式却是跳过数据缓冲区，直接写进磁盘中，这种方式又称之为直接路径读写方式，因为原本是数据先到内存，再到磁盘，更改为直接到磁盘，少了一个步骤，因此速度提升了许多。

直接路径读写方式的缺点在于由于数据不经过数据缓存区，所以在数据缓存区中一定读不到这些数据，因此一定会有物理读。但是在很多时候，尤其是海量数据需要迁移插入时，快速插入才是真正的第一目的，该表一般记录巨大，DATA BUFFER 甚至还装不下其十分之一，百分之一，这些共享的数据意义也不大，这时，我们一般会选择直接路径读写的方式来完成海量数据的插入。

作者