1. 数据准备
表5张。数据量分别为1W,10W,100W,1000W,1亿。
DECLARE
TABNAME VARCHAR(20);
SCHNAME VARCHAR(20);
TABSIZE INTEGER;
V_SQL VARCHAR(8188);
V_SQL1 VARCHAR(8188);
CURSOR C1;
BEGIN
--SELECT TABLE_NAME INTO TBNAME FROM DBA_TABLES WHERE OWNER = 'DMTEST' AND TABLE_NAME LIKE '%TB%';
OPEN C1 FOR 'SELECT T1.NAME AS SCHNAME, T2.NAME AS TABNAME FROM SYSOBJECTS T1, SYSOBJECTS T2 WHERE T1.NAME = ''DMTEST'' AND T1.TYPE$=''SCH''' ||
'AND T2.NAME LIKE ''%TB%'' AND T2.TYPE$=''SCHOBJ'' AND T2.SUBTYPE$=''UTAB'' AND T1.ID = T2.SCHID ';
LOOP
FETCH C1 INTO SCHNAME,TABNAME;
EXIT WHEN C1%NOTFOUND;
V_SQL1 = 'SELECT COUNT(*) FROM ' || SCHNAME || '.' || TABNAME;
EXECUTE IMMEDIATE V_SQL1 INTO TABSIZE;
PRINT 'SCHNAME: ' || SCHNAME || ', TABNAME: ' || TABNAME || ', SIZE: ' || TABSIZE;
END LOOP;
CLOSE C1;
END;
2. 性能测试
2.1 1W数据测试
单行读取:
--单行 数据量10000
DECLARE
RET TB1%ROWTYPE;
CURSOR C1;
BEGIN
OPEN C1 FOR SELECT * FROM DMTEST.TB1;
LOOP
FETCH C1 INTO RET;
EXIT WHEN C1%NOTFOUND;
NULL;
END LOOP;
CLOSE C1;
END;
耗时85毫秒
多行读取:
--多行 数据量10000
DECLARE
TYPE ARR IS TABLE OF DMTEST.TB1%ROWTYPE;
RET ARR;
CURSOR C1;
BEGIN
OPEN C1 FOR SELECT * FROM DMTEST.TB1;
LOOP
FETCH C1 BULK COLLECT INTO RET LIMIT 3000;
EXIT WHEN C1%NOTFOUND;
FOR I IN 1..RET.COUNT LOOP
NULL;
END LOOP;
/*
FOR I IN RET.FIRST..RET.LAST LOOP
NULL;
END LOOP;
*/
END LOOP;
CLOSE C1;
END;
耗时6毫秒
2.2 10W 性能测试
单行读取:
--单行 数据量100000
DECLARE
RET TB1%ROWTYPE;
CURSOR C1;
BEGIN
OPEN C1 FOR SELECT * FROM DMTEST.TB2;
LOOP
FETCH C1 INTO RET;
EXIT WHEN C1%NOTFOUND;
NULL;
END LOOP;
CLOSE C1;
END;
耗时80毫秒
多行读取:
--多行 数据量100000
DECLARE
TYPE ARR IS TABLE OF DMTEST.TB1%ROWTYPE;
RET ARR;
CURSOR C1;
BEGIN
OPEN C1 FOR SELECT * FROM DMTEST.TB2;
LOOP
FETCH C1 BULK COLLECT INTO RET LIMIT 3000;
EXIT WHEN C1%NOTFOUND;
FOR I IN 1..RET.COUNT LOOP
NULL;
END LOOP;
/*
FOR I IN RET.FIRST..RET.LAST LOOP
NULL;
END LOOP;
*/
END LOOP;
CLOSE C1;
END;
耗时34毫秒
2.3 100W性能测试
单行读取:
--单行 数据量1000000
DECLARE
RET TB1%ROWTYPE;
CURSOR C1;
BEGIN
OPEN C1 FOR SELECT * FROM DMTEST.TB3;
LOOP
FETCH C1 INTO RET;
EXIT WHEN C1%NOTFOUND;
NULL;
END LOOP;
CLOSE C1;
END;
耗时634毫秒
多行读取:
DECLARE
TYPE ARR IS TABLE OF DMTEST.TB1%ROWTYPE;
RET ARR;
CURSOR C1;
BEGIN
OPEN C1 FOR SELECT * FROM DMTEST.TB3;
LOOP
FETCH C1 BULK COLLECT INTO RET LIMIT 3000;
EXIT WHEN C1%NOTFOUND;
FOR I IN 1..RET.COUNT LOOP
NULL;
END LOOP;
/*
FOR I IN RET.FIRST..RET.LAST LOOP
NULL;
END LOOP;
*/
END LOOP;
CLOSE C1;
END;
耗时251毫秒
2.4 1000W性能测试
单行读取:
--单行 数据量10000000
DECLARE
RET TB1%ROWTYPE;
CURSOR C1;
BEGIN
OPEN C1 FOR SELECT * FROM DMTEST.TB4;
LOOP
FETCH C1 INTO RET;
EXIT WHEN C1%NOTFOUND;
NULL;
END LOOP;
CLOSE C1;
END;
耗时7秒277毫秒
多行读取:
DECLARE
TYPE ARR IS TABLE OF DMTEST.TB1%ROWTYPE;
RET ARR;
CURSOR C1;
BEGIN
OPEN C1 FOR SELECT * FROM DMTEST.TB4;
LOOP
FETCH C1 BULK COLLECT INTO RET LIMIT 3000;
EXIT WHEN C1%NOTFOUND;
FOR I IN 1..RET.COUNT LOOP
NULL;
END LOOP;
/*
FOR I IN RET.FIRST..RET.LAST LOOP
NULL;
END LOOP;
*/
END LOOP;
CLOSE C1;
END;
耗时2秒559毫秒
2.5 1亿性能测试
单行读取:
--单行 数据量100000000
DECLARE
RET TB1%ROWTYPE;
CURSOR C1;
BEGIN
OPEN C1 FOR SELECT * FROM DMTEST.TB5;
LOOP
FETCH C1 INTO RET;
EXIT WHEN C1%NOTFOUND;
NULL;
END LOOP;
CLOSE C1;
END;
耗时1分22秒170毫秒
多行读取:
--多行 数据量100000000
DECLARE
TYPE ARR IS TABLE OF DMTEST.TB1%ROWTYPE;
RET ARR;
CURSOR C1;
BEGIN
OPEN C1 FOR SELECT * FROM DMTEST.TB5;
LOOP
FETCH C1 BULK COLLECT INTO RET LIMIT 3000;
EXIT WHEN C1%NOTFOUND;
FOR I IN 1..RET.COUNT LOOP
NULL;
END LOOP;
/*
FOR I IN RET.FIRST..RET.LAST LOOP
NULL;
END LOOP;
*/
END LOOP;
CLOSE C1;
END;
耗时1分8秒49毫秒
3 总结
上述实验可看出数据量从1W-1亿量级。对于表数据的遍历。批量读取的效率都高于单行读取。底层原因为当运行一个pl/sql程序时, pl/sql语句引擎会执行pl/sql语句。但如果在这个过程中引擎遇到sql语句,它会把这个语句传给sql引擎(后台发生上下文切换)。在PL/SQL和SQL引擎(engines)中,太多的上下文切换(context switches)会影响性能。这个会发生在当一个循环为集合中的每个元素执行一个单个SQL语句时。而使用批挷定能显著提高性能。缺点为会消耗更多的内存(PGA)。由于该数集合据存储在每个会话中,假设一个会话多消耗5M,内存,那么1000个就消耗约5G内存。