Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL, Static SQL slower then Dynamic SQL
PL/SQL, Static SQL slower then Dynamic SQL [message #448588] Wed, 24 March 2010 03:06 Go to next message
mnitu
Messages: 159
Registered: February 2008
Location: Reims
Senior Member
In a recent post someone affirms that (Oracle 9.2.0.8 )
Quote:
[Native] "Dynamic SQL makes the whole thing slower".


A recent blog entry says basically the same thing (no Oracle version specified)
Quote:
"Most important problems that I have encountered with EXECUTE IMMEDIATE:
- Slower than static SQL"


But an well known white papers written by Bryn Llewellyn, PL/SQL Product Manager : Doing SQL from PL/SQL: Best and Worst Practices (Oracle 11g) states that:
Quote:
"At run time, the SQL statement that has been generated from each embedded SQL statement is executed in the only way that the SQL subsystem in Oracle Database supports: a session cursor is opened; the SQL statement is presented as text and is parsed; for a select statement, targets for the select list elements are defined; if the SQL statement has placeholders, then a bind argument is bound to each; the session cursor is executed; for a select statement, the results are fetched; and the session cursor is closed. When a PL/SQL program uses dynamic SQL, the run-time processing of the SQL statement is identical. The only difference is in who did the "thinking" needed to produces the runtime code: for native dynamic SQL, and especially for the DBMS_Sql API, the programmer does the work; for embedded SQL, the PL/SQL compiler does the work."


So, can someone provide some test case capable to prove that [Native] dynamic SQL is slower that embedded SQL or is this one just another mythological believe?

[Updated on: Wed, 24 March 2010 03:08]

Report message to a moderator

Re: PL/SQL, Static SQL slower then Dynamic SQL [message #448589 is a reply to message #448588] Wed, 24 March 2010 03:14 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
So, can someone provide some test case capable to prove that [Native] dynamic SQL is slower that embedded SQL or is this one just another mythological believe?

Easy, just put a query inside your PL/SQL block (in a loop), then does the same thing but inside execute immediate and measure both.

Do it with different Oracle versions, you will see the results are different.

Regards
Michel
Re: PL/SQL, Static SQL slower then Dynamic SQL [message #448644 is a reply to message #448589] Wed, 24 March 2010 08:07 Go to previous messageGo to next message
mnitu
Messages: 159
Registered: February 2008
Location: Reims
Senior Member
Create table t_pl_sql as
Select rownum as id, object_name as name, object_type as typ
  From all_objects
 Where rownum <= 10000
/


ConnectÚ Ó :
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> set serveroutput on
SQL> exec runStats_pkg.rs_start;

ProcÚdure PL/SQL terminÚe avec succÞs.

SQL> Declare
  2    l_name  t_pl_sql.name%Type;
  3    l_typ   t_pl_sql.typ%Type;
  4  Begin
  5    For i in 1..10000 Loop
  6      Select name, typ
  7        Into  l_name, l_typ
  8        From t_pl_sql
  9       Where id = i;
 10    End Loop;
 11  End;
 12  /

ProcÚdure PL/SQL terminÚe avec succÞs.

SQL> exec runStats_pkg.rs_middle;

ProcÚdure PL/SQL terminÚe avec succÞs.

SQL>
SQL> Declare
  2    l_name  t_pl_sql.name%Type;
  3    l_typ   t_pl_sql.typ%Type;
  4    l_stmt  Varchar2(100) := 'Select name, typ From t_pl_sql Where id = :b1';

  5  Begin
  6    For i in 1..10000 Loop
  7      Execute Immediate l_stmt Into l_name, l_typ Using i;
  8    End Loop;
  9  End;
 10  /

ProcÚdure PL/SQL terminÚe avec succÞs.

SQL> exec runStats_pkg.rs_stop(10000);
Run1 ran in 520 hsecs
Run2 ran in 521 hsecs
run 1 ran in 99,81% of the time

Name                                  Run1        Run2        Diff
STAT...session uga memory           65,560       7,488     -58,072
STAT...session pga memory           65,536           0     -65,536
STAT...session uga memory max      261,964      65,560    -196,404
STAT...session pga memory max      262,144      65,536    -196,608

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
1,121,216   1,121,064        -152    100.01%


Re: PL/SQL, Static SQL slower then Dynamic SQL [message #448676 is a reply to message #448644] Wed, 24 March 2010 10:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Here's a good example - run on Oracle 10.2.0.4 on my laptop.

We look at how long it takes to execute a trivial pl/sql block many times - this gets the amount of time spent actually doing something down as low as possible, so as much of the difference as possible is from the NDS overhead.
declare
  v_iter       pls_integer := 1000000;
  v_start_time pls_integer;
  v_end_time   pls_integer;
begin

  v_start_time := to_number(to_char(systimestamp,'sssss.ff'))*1000;
  for i in 1..v_iter loop
    
    begin
      null;
    end;
    
  end loop;
  v_end_time := to_number(to_char(systimestamp,'sssss.ff'))*1000;
  
  dbms_output.put_line('Test 1 '||to_char(v_end_time - v_start_time));

  v_start_time := to_number(to_char(systimestamp,'sssss.ff'))*1000;
  for i in 1..v_iter loop
    
    execute immediate 'begin null; end;';
  end loop;
  v_end_time := to_number(to_char(systimestamp,'sssss.ff'))*1000;
  
  dbms_output.put_line('Test 2 '||to_char(v_end_time - v_start_time));
end;
/


Run over 1,000,000 iterations, test 1 takes me 16 thousandths of a second, and test 2 takes 56328 thousandths of a second.

YMMV
Re: PL/SQL, Static SQL slower then Dynamic SQL [message #448678 is a reply to message #448676] Wed, 24 March 2010 11:09 Go to previous messageGo to next message
mnitu
Messages: 159
Registered: February 2008
Location: Reims
Senior Member
JRowbottom wrote on Wed, 24 March 2010 16:48
Here's a good example - run on Oracle 10.2.0.4 on my laptop.
...


Sorry, but I don't agree with your test, it's not the same thing to test dynamic SQL and dynamic PL/SQL. See my first comment for an explication.

Same test Oracle 9
Connected to Oracle9i Enterprise Edition Release 9.2.0.5.0 
Connected as mni
 
SQL> 
SQL> set serveroutput on
SQL> Declare
  2    l_name  t_pl_sql.name%Type;
  3    l_typ   t_pl_sql.typ%Type;
  4    l_stmt  Varchar2(100) := 'Select name, typ From t_pl_sql Where id = :b1';
  5  Begin
  6    runStats_pkg.rs_start;
  7    --
  8    For i in 1..10000 Loop
  9      Select name, typ
 10        Into  l_name, l_typ
 11        From t_pl_sql
 12       Where id = i;
 13    End Loop;
 14    --
 15    runStats_pkg.rs_middle;
 16    --
 17    For i in 1..10000 Loop
 18      Execute Immediate l_stmt Into l_name, l_typ Using i;
 19    End Loop;
 20    --
 21    runStats_pkg.rs_stop(10000);
 22  End;
 23  /
 
Run1 ran in 6531 hsecs
Run2 ran in 6503 hsecs
run 1 ran in 100,43% of the time
	
Name                                  Run1        Run2        Diff
LATCH.library cache pin alloca       2,906      22,965      20,059
LATCH.shared pool                   14,358      34,441      20,083
LATCH.library cache pin             25,094      65,210      40,116
LATCH.library cache                 27,825      67,993      40,168
STAT...session pga memory max       65,536           0     -65,536
	
Run1 latches total versus runs -- difference and pct
        Run1        Run2        Diff       Pct
   1,057,607   1,178,200     120,593     89.76%
 
PL/SQL procedure successfully completed
 
SQL> 

Re: PL/SQL, Static SQL slower then Dynamic SQL [message #448679 is a reply to message #448678] Wed, 24 March 2010 11:25 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is why I said:
Quote:
Do it with different Oracle versions, you will see the results are different.

This case has been greatly improved in 10.2 comparing to previous versions as you do not see the difference.
Actually there is none because PL/SQL sees that this is a static SQL and do not reparse the statement but keep it in cursor cache.
Here's the tkprof trace (removing runstats package call and keeping only the relevant part of trace file):
Declare
  l_name  t_pl_sql.name%Type;
  l_typ   t_pl_sql.typ%Type;
  l_stmt  Varchar2(100) := 'Select name, typ From t_pl_sql Where id = :b1';
Begin
   For i in 1..10000 Loop
     Select name, typ
       Into  l_name, l_typ
       From t_pl_sql
     Where id = i;
   End Loop;
   For i in 1..10000 Loop
     Execute Immediate l_stmt Into l_name, l_typ Using i;
   End Loop;
End;
/

SELECT NAME, TYP 
FROM
 T_PL_SQL WHERE ID = :B1 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  10000      0.85       1.67          0          0          0           0
Fetch    10000      4.25       4.21          0     510000          0       10000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    20001      5.10       5.88          0     510000          0       10000

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 50     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
  10000  TABLE ACCESS FULL T_PL_SQL (cr=510000 pr=0 pw=0 time=4182399 us)

********************************************************************************

Select name, typ 
From
 t_pl_sql Where id = :b1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  10000      0.87       0.95          0          0          0           0
Fetch    10000      4.31       4.18          0     510000          0       10000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    20001      5.18       5.14          0     510000          0       10000

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 50     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
  10000  TABLE ACCESS FULL T_PL_SQL (cr=510000 pr=0 pw=0 time=4155989 us)



Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand

[Edit: Fix mistyped version in first sentence.]

[Updated on: Wed, 24 March 2010 13:33]

Report message to a moderator

Re: PL/SQL, Static SQL slower then Dynamic SQL [message #448686 is a reply to message #448678] Wed, 24 March 2010 12:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What I see from you initial post is:
1) a quote from this forum concerning a version of Oracle in which NDS most definitely was slower (about which more later)
2) a blog entry - if an entry in a blog is incorrect or inaccurate, I for one will not be terribly suprised
3) a paragraph from a white paper taken almost completely out of context - that paragraph is from a section discussing coding practices and the structure and operation of the compiler, and how SQL is executed behind the scenes.

If I'm missing the bit where you've described why this only applies to SQL, do please point me at it.

In Oracle 8 and 9, there was a significant performance problem with dynamic SQL - every time a dynamic statement was executed, it had to be parsed, and parsing takes time and resources.

This requirement for a soft-parse every time the statement was executed has been removed in 10g, , but as your own post demonstrates, NDS still uses more system resources, and so in a multi-user environment, will tend to cause a drop in performance, even if we disregard the performance impact that my prefious post highlighted.

What I've not seen said is what you're trying to provide counter arguments to - that fetching rows from dynamic SQL is slower than from static (or embedded) SQL.
That's not where the performance hit is with NDS, and that's why my example removed the SQL entirely - to demonstrate that doing ANYTHING with NDS incurrs a performance hit
Re: PL/SQL, Static SQL slower then Dynamic SQL [message #448690 is a reply to message #448686] Wed, 24 March 2010 13:17 Go to previous messageGo to next message
mnitu
Messages: 159
Registered: February 2008
Location: Reims
Senior Member
@Michel, are you saying that starting with Oracle 9.2 static SQL executes as quickly as dynamic SQL ? Did I misinterpreted you ?

@JRowbottom,

I agree with you that in Oracle 8 and maybe in 9.1 that was a problem; it seems to me that there was a separate SQL Engine build in PL/SQL, not sure about that.

The paragraph I quoted says that
"When a PL/SQL program uses dynamic SQL, the run-time processing of the SQL statement is identical."
so that make me think that basically there is no important difference as we can see (maybe really true in Oracle 11).

In order to explain why I think your test case is wrong I will rewrite the first part of your code to something like this

declare
...
Procedure p Is
Begin
  Null;
End;
Begin
...
for i in 1..v_iter Loop
  p;
End loop;
...

As you can see all this code executes en single PL/SQL context.

But in the second part: the NDS one, execution starts with PL/SQL engine. The NDS hires SQL engine. This one discovers that it is non SQL but PL/SQL and hires back PL/SQL Engine. So this time there are some context changes: PL/SQL => SQL and SQL => PL/SQL and this explains the results we are seeing.

So you are comparing things that are really different and your conclusion
Quote:

doing ANYTHING with NDS incurrs a performance hit

seems wrong to me.
Re: PL/SQL, Static SQL slower then Dynamic SQL [message #448691 is a reply to message #448690] Wed, 24 March 2010 13:32 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry I mistyped, I meant 10.2 (I now fix my post for future readers), 9.2 parses aty each time the dynamic SQL, here's a trace for the same test as in my previous post:
SELECT NAME, TYP 
FROM
 T_PL_SQL WHERE ID = :B1 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  10000      0.46       1.20          0          0          0           0
Fetch    10000     18.78      18.52          0    1240000          0       10000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    20001     19.25      19.73          0    1240000          0       10000

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: 29     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
  10000  TABLE ACCESS FULL T_PL_SQL 

********************************************************************************

Select name, typ 
From
 t_pl_sql Where id = :b1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse    10000      0.14       0.15          0          0          0           0
Execute  10000      0.64       0.63          0          0          0           0
Fetch    10000     17.25      17.08          0    1240000          0       10000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    30000     18.03      17.87          0    1240000          0       10000

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: 29     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS FULL T_PL_SQL 

As you can see there is a parse each time.


Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand
Re: PL/SQL, Static SQL slower then Dynamic SQL [message #448765 is a reply to message #448691] Thu, 25 March 2010 02:30 Go to previous messageGo to next message
mnitu
Messages: 159
Registered: February 2008
Location: Reims
Senior Member
Oracle 9.2: more parses means more latches that's potentially slower.
Starting with Oracle 10 seems there is no difference any more.
Re: PL/SQL, Static SQL slower then Dynamic SQL [message #448767 is a reply to message #448765] Thu, 25 March 2010 02:32 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, this is what I said (or wanted to say).

Regards
Michel
Re: PL/SQL, Static SQL slower then Dynamic SQL [message #448778 is a reply to message #448765] Thu, 25 March 2010 03:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
@mnitu - your own results show that the NDS approach used more latches:
Name                                  Run1        Run2        Diff
LATCH.library cache pin alloca       2,906      22,965      20,059
LATCH.shared pool                   14,358      34,441      20,083
LATCH.library cache pin             25,094      65,210      40,116
LATCH.library cache                 27,825      67,993      40,168
STAT...session pga memory max       65,536           0     -65,536
Re: PL/SQL, Static SQL slower then Dynamic SQL [message #448870 is a reply to message #448778] Thu, 25 March 2010 08:13 Go to previous messageGo to next message
mnitu
Messages: 159
Registered: February 2008
Location: Reims
Senior Member
JRowbottom wrote on Thu, 25 March 2010 09:10
@mnitu - your own results show that the NDS approach used more latches


Yes that's true for Oracle 9.X As I already said more parsing means more latches and this is potentially slower. Seems not true anymore starting with Oracle 10.
Re: PL/SQL, Static SQL slower then Dynamic SQL [message #449100 is a reply to message #448870] Fri, 26 March 2010 11:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Could you post the code for your stats gathering package - I'd like to run a few tests of my own and see what's going on.
Re: PL/SQL, Static SQL slower then Dynamic SQL [message #449103 is a reply to message #449100] Fri, 26 March 2010 11:47 Go to previous message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551378329289980701

Regards
Michel
Previous Topic: How to store the content of a csv file into an array in oracle
Next Topic: check availability of datafile for ETL process
Goto Forum:
  


Current Time: Tue Sep 27 09:18:27 CDT 2016

Total time taken to generate the page: 0.21207 seconds