Skip to Main Content
  • Questions
  • "Softer Soft Parse" faster than "No Parse"

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Thomas.

Asked: June 14, 2016 - 3:15 am UTC

Last updated: June 15, 2016 - 1:01 am UTC

Version: 12.1.0.2

Viewed 1000+ times

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

Comments

Thomas Fung, June 14, 2016 - 7:31 am UTC

Thanks for prompt response.

I have verified the 3 test and the test 3 "No Parse" outperform than others (1908,1785,992 hsec).

However, is it meant that "Softer Soft Parse" method (as below) is most efficient in PLSQL ?

Thanks

alter session set session_cached_cursors=50;
begin
for i in 1 .. 50000
loop
insert into t(x) values(i);
end loop;
end;
/

Connor McDonald
June 15, 2016 - 1:01 am UTC

Static SQL in PL/SQL is going to run about as fast as any SQL I can think of. That's one of the great things about PLSQL. The easiest and default usage is typically the best as well.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library