| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: RE: Parse Vs Execute
On Fri, 2003-11-28 at 09:24, Jared Still wrote:
> By using DBMS_SQL you can open a cursor and re-execute as many
> times as needed.
>
> You can't do that with execute immediate.
>
> On Wed, 2003-11-26 at 12:04, ryan_oracle_at_cox.net wrote:
> > i remember in tom kytes new book there is a 'softer parse' he was referring to using dbms_sql instead of execute immediate. Im not referring to using dbms_sql when you have to loop and use the same cursor repeatedly so you eliminate all parsing.
> >
> > he didnt go into great detail on this just gave benchmarks. do you know anymore?
Here's a test using both DBMS_SQL and EXECUTE IMMEDIATE.
DBMS_SQL is RUN1 and EXEC IMMEDIATE is RUN2
DBMS_SQL is much less demanding of the latching mechanisms
NAME RUN1 RUN2 DIFF ---------------------------------------- ---------- ---------- ---------- LATCH.enqueues 0 1 1 LATCH.session idle bit 0 1 1 LATCH.session timer 0 1 1 STAT...calls to kcmgcs 7 6 -1 STAT...cleanout - number of ktugct calls 0 1 1 STAT...calls to kcmgas 0 1 1 STAT...active txn count during cleanout 0 1 1 STAT...consistent gets 18010 18011 1 STAT...cursor authentications 1 0 -1 STAT...user commits 0 1 1 STAT...parse count (hard) 1 0 -1 STAT...messages sent 0 1 1 STAT...execute count 5003 5004 1 STAT...deferred (CURRENT) block cleanout 4 3 -1applications
STAT...db block changes 24 25 1 STAT...consistent gets - examination 4000 4001 1 LATCH.dml lock allocation 0 2 2 STAT...redo entries 17 19 2 LATCH.session allocation 0 2 2 STAT...enqueue releases 0 2 2 STAT...db block gets 28 30 2 LATCH.messages 4 7 3 STAT...session logical reads 18038 18041 3 LATCH.redo allocation 19 22 3 LATCH.undo global data 1 4 3 LATCH.enqueue hash chains 0 4 4 LATCH.child cursor hash table 7 0 -7 STAT...commit cleanouts successfully com 0 7 7pleted
STAT...commit cleanouts 0 7 7 STAT...opened cursors current 11 3 -8 STAT...parse time cpu 0 9 9 STAT...parse time elapsed 0 14 14 LATCH.cache buffers chains 32114 32133 19 LATCH.row cache enqueue latch 28 0 -28 LATCH.row cache objects 32 0 -32 STAT...recursive cpu usage 50 91 41 STAT...redo size 27144 27624 480 STAT...opened cursors cumulative 12 4003 3991 STAT...parse count (total) 12 4003 3991 STAT...recursive calls 17026 9008 -8018 LATCH.shared pool 5131 17004 11873 LATCH.library cache pin allocation 134 16006 15872 LATCH.library cache pin 10210 34014 23804 LATCH.library cache 10333 46018 35685
44 rows selected.
declare
l_start number(16,6);
--add any other variables you need here for the test...
begin
delete from run_stats;
commit;
declare
v_sql varchar2(200);
c1 integer;
counter integer := 0;
rowcount integer;
BEGIN
c1 := dbms_sql.open_cursor;
v_sql := 'select count(*) empcount
from scott.emp e, scott.dept d
where e.deptno = d.deptno
and e.deptno = :find_deptno';
dbms_sql.parse(c1, v_sql, dbms_sql.native);
-- bind and execute a few times
for i in 1..100
loop
for drec in (select deptno from scott.dept )
loop
dbms_sql.bind_variable(c1,':find_deptno',drec.deptno);
rowcount := dbms_sql.execute_and_fetch(c1);
counter := counter+1;
--dbms_output.put_line(counter);
end loop;
end loop;
dbms_sql.close_cursor(c1);
end;
end loop;
dbms_output.put_line( (get_epoch_microseconds-l_start) || ' secs' );
l_start := get_epoch_microseconds;
for i in 1 .. 10
loop
declare
v_sql varchar2(200);
counter integer := 0;
rowcount integer;
BEGIN
v_sql := 'select count(*) empcount
from scott.emp e, scott.dept d
where e.deptno = d.deptno
and e.deptno = :find_deptno';
-- parse and execute a few times
for i in 1..100
loop
for drec in (select deptno from scott.dept )
loop
execute immediate v_sql into rowcount using drec.deptno;
counter := counter+1;
--dbms_output.put_line(counter);
end loop;
end loop;
end;
end loop;
dbms_output.put_line( (get_epoch_microseconds-l_start) || ' secs' );
insert into run_stats select 'after 2', stats.* from stats;
end;
/
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: jkstill_at_cybcon.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Sat Nov 29 2003 - 15:04:25 CST
 |
 |