写了一个存储函数
comp_sum用来测试不同列数的表查询的时间
按理说从第二遍开始，数据就在内存了，为什么查询时间和第一次相同？
exec createtab(100);
exec createtab(200);
exec createtab(300);
exec comp_sum(2,4);
init 2037
buf1 2
buf2 3
buf3 7
buf1 2
buf2 3
buf3 7
buf1 2
buf2 3
buf3 6
buf1 2
buf2 3
buf3 7
create or replace procedure
comp_sum( tm in number,tm2 in number )is
buf varchar(100):='';
buf1 varchar(100):='select sum(v1)||sum(v51)||sum(v100) from tcol100';
buf2 varchar(100):='select sum(v1)||sum(v101)||sum(v200) from tcol200';
buf3 varchar(100):='select sum(v1)||sum(v151)||sum(v300) from tcol300';
start_time number;
end_time number;
begin
--execute immediate 'alter system flush shared_pool';
--execute immediate 'alter system flush buffer_cache';
start_time := DBMS_UTILITY.get_time;
execute immediate 'truncate table tcol300';
execute immediate 'truncate table tcol200';
execute immediate 'truncate table tcol100';
inserttab2(10000*tm,100);
commit;
inserttab2(10000*tm,200);
commit;
inserttab2(10000*tm,300);
commit;
end_time := DBMS_UTILITY.get_time;
DBMS_OUTPUT.PUT_LINE('init '||to_char(end_time-start_time));
for i in 1..tm2
loop
start_time := DBMS_UTILITY.get_time;
execute immediate buf1 into buf;
end_time := DBMS_UTILITY.get_time;
DBMS_OUTPUT.PUT_LINE('buf1 '||to_char(end_time-start_time));
start_time := DBMS_UTILITY.get_time;
execute immediate buf2 into buf;
end_time := DBMS_UTILITY.get_time;
DBMS_OUTPUT.PUT_LINE('buf2 '||to_char(end_time-start_time));
start_time := DBMS_UTILITY.get_time;
execute immediate buf3 into buf;
end_time := DBMS_UTILITY.get_time;
DBMS_OUTPUT.PUT_LINE('buf3 '||to_char(end_time-start_time));
end loop;
end;
/
create or replace procedure inserttab2(rowc NUMBER,colc NUMBER)
is
buf varchar(24000):='insert into tcol'||to_char(colc)||' select level,';
begin
for i in 1..colc-1
loop
buf:=buf||'mod(level+'||to_char(i)||',1e10),';
end loop;
buf:=buf||'mod(level+'||to_char(colc)||',1e10) from dual connect by level set serverout on
SQL> exec comp_sum(4,3)
init 4189
buf1 4
buf2 6
buf3 14
buf1 3
buf2 6
buf3 13
buf1 4
buf2 6
buf3 13
PL/SQL procedure successfully completed.
SQL> exec comp_sum(3,3)
init 2763
buf1 2
buf2 5
buf3 10
buf1 3
buf2 4
buf3 10
buf1 3
buf2 4
buf3 10
PL/SQL procedure successfully completed.
SQL> exec comp_sum(2,3)
init 2330
buf1 2
buf2 3
buf3 7
buf1 1
buf2 3
buf3 7
buf1 2
buf2 3
buf3 6
PL/SQL procedure successfully completed.
SQL> exec comp_sum(1,3)
init 1400
buf1 2
buf2 1
buf3 4
buf1 1
buf2 1
buf3 3
buf1 1
buf2 1
buf3 4
PL/SQL procedure successfully completed.
SQL>
SQL> exec comp_sum(5,3)
init 4483
buf1 5
buf2 8
buf3 16
buf1 5
buf2 7
buf3 16
buf1 5
buf2 7
buf3 17
PL/SQL procedure successfully completed.
SQL>
SQL> exec comp_sum(6,3)
init 5325
buf1 6
buf2 9
buf3 21
buf1 5
buf2 9
buf3 20
buf1 5
buf2 9
buf3 20
PL/SQL procedure successfully completed.
SQL> exec comp_sum(7,3)
init 6981
buf1 6
buf2 11
buf3 23
buf1 6
buf2 11
buf3 23
buf1 6
buf2 10
buf3 23
PL/SQL procedure successfully completed.
SQL> exec comp_sum(8,3)
init 7523
buf1 8
buf2 12
buf3 27
buf1 7
buf2 12
buf3 26
buf1 7
buf2 12
buf3 26
PL/SQL procedure successfully completed.
SQL> exec comp_sum(9,3)
init 9581
buf1 8
buf2 14
buf3 30
buf1 8
buf2 14
buf3 29
buf1 8
buf2 13
buf3 30
PL/SQL procedure successfully completed.
SQL> exec comp_sum(10,3)
init 10530
buf1 9
buf2 15
buf3 34
buf1 9
buf2 15
buf3 33
buf1 8
buf2 15
buf3 33
PL/SQL procedure successfully completed.
|
