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  |
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 #448644 is a reply to message #448589] |
Wed, 24 March 2010 08:07   |
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   |
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   |
mnitu
Messages: 159 Registered: February 2008 Location: Reims
|
Senior Member |
|
|
JRowbottom wrote on Wed, 24 March 2010 16:48Here'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   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
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   |
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   |
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 #448778 is a reply to message #448765] |
Thu, 25 March 2010 03:10   |
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   |
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.
|
|
|
|
|
Goto Forum:
Current Time: Sun Feb 16 00:37:36 CST 2025
|