You Asked
Hi Tom,
To verify "No parse" and "Softer Soft Parse", following test have been executed.
Both the execution time and latches used of "Softer Soft Parse" are better than "No Parse" (see below).
Could you suggest the cause?
Is there problem for setting up the "No Parse" case?
Thanks
Regards
Thomas
drop table t;
create table t ( x varchar2(30) );
--Softer Soft Parse VS No Parse
exec runstats_pkg.rs_start;
alter session set session_cached_cursors=50;
begin
for i in 1 .. 10000
loop
insert into t(x) values(i);
end loop;
end;
/
exec runstats_pkg.rs_middle;
declare
m_cursor number;
m_rows_processed number;
begin
m_cursor := dbms_sql.open_cursor;
dbms_sql.parse(m_cursor, 'insert into t(x) values(:n)', dbms_sql.native );
for i in 1..10000 loop
dbms_sql.bind_variable_char(m_cursor, ':n', i);
m_rows_processed := dbms_sql.execute(m_cursor);
end loop;
dbms_sql.close_cursor(m_cursor);
end;
/
exec runstats_pkg.rs_stop(50);
Run1 ran in 120 hsecs
Run2 ran in 183 hsecs
run 1 ran in 65.57% of the time
Name Run1 Run2 Diff
STAT...db block changes 20,291 20,240 -51
STAT...db block gets from cach 10,345 10,293 -52
STAT...db block gets 10,345 10,293 -52
STAT...DB time 131 184 53
STAT...consistent gets pin (fa 42 97 55
STAT...consistent gets pin 42 97 55
STAT...CPU used when call star 127 183 56
STAT...consistent gets 74 130 56
STAT...consistent gets from ca 74 130 56
STAT...no work - consistent re 8 66 58
STAT...CPU used by this sessio 120 183 63
LATCH.SQL memory manager worka 70 3 -67
LATCH.cache buffers chains 51,261 51,175 -86
STAT...KTFB alloc time (ms) 142 50 -92
STAT...buffer is not pinned co 3 100 97
STAT...table scan disk non-IMC 321 0 -321
STAT...table scan rows gotten 321 0 -321
STAT...bytes sent via SQL*Net 1,485 990 -495
STAT...undo change vector size 686,508 685,944 -564
STAT...bytes received via SQL* 2,545 1,969 -576
LATCH.simulator hash latch 20 1,361 1,341
STAT...redo size 2,624,228 2,619,620 -4,608
STAT...non-idle wait count 12 10,009 9,997
STAT...session cursor cache hi 10,004 6 -9,998
STAT...opened cursors cumulati 10,012 7 -10,005
STAT...logical read bytes from#################### 32,768
STAT...KTFB alloc space (block 1,179,648 65,536-1,114,112
Run1 latches total versus runs --difference and pct
Run1 Run2 Diff Pct
62,371 63,474 1,103 98.26%
PL/SQL procedure successfully completed.
and Connor said...
It's tough get a "close enough" comparison because obviously it takes CPU cycles and the like to just run PLSQL, so the amount of PLSQL code you have (because its interpreted) ends up having a bearing.
So perhaps here's a better way of comparing the impact
Test 1: session cached cursors = 0
for i in 1..50000 loop
dbms_sql.parse(m_cursor, 'insert into t(x) values(:n)', dbms_sql.native );
dbms_sql.bind_variable_char(m_cursor, ':n', i);
m_rows_processed := dbms_sql.execute(m_cursor);
end loop;
Test 2: session cached cursors = 50
for i in 1..50000 loop
dbms_sql.parse(m_cursor, 'insert into t(x) values(:n)', dbms_sql.native );
dbms_sql.bind_variable_char(m_cursor, ':n', i);
m_rows_processed := dbms_sql.execute(m_cursor);
end loop;
Test 3: session cached cursors = 0
dbms_sql.parse(m_cursor, 'insert into t(x) values(:n)', dbms_sql.native );
for i in 1..50000 loop
dbms_sql.bind_variable_char(m_cursor, ':n', i);
m_rows_processed := dbms_sql.execute(m_cursor);
end loop;
And I get this out of tkprof
insert into t(x)
values
(:n)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 49999 0.37 0.51 0 0 0 0
Execute 49999 1.49 1.46 1 50347 152067 49999
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 99998 1.87 1.98 1 50347 152067 49999
insert into t(x)
values
(:n)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 50000 0.37 0.28 0 0 0 0
Execute 50000 1.37 1.41 0 50347 152062 50000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 100000 1.76 1.69 0 50347 152062 50000
insert into t(x)
values
(:n)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 50000 0.95 0.80 0 644 52144 50000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 50001 0.95 0.80 0 644 52144 50000
and validating the hit counts with
<code>
select
s.name, st.value
from v$statname s, v$sesstat st
where st.STATISTIC# = s.STATISTIC#
and st.sid = sys_context('USERENV','SID')
and s.name = 'session cursor cache hits';
<code>
So try those permutations on your machine/database and see how you go
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment