Home » SQL & PL/SQL » SQL & PL/SQL » Which Method is Better Pl-Sql
Which Method is Better Pl-Sql [message #290768] Mon, 31 December 2007 10:56 Go to next message
qasim845
Messages: 95
Registered: March 2007
Location: Philadelphia
Member

Which one of the following is a better implementation? Why?

Method A

for i in 1 .. 1000
loop
   execute immediate 'insert into t values ( ' || i || ')';
end loop;

Method B
for i in 1 .. 1000
loop
   execute immediate 'insert into t values ( :1 )' using i;
end loop;


[Edited by DreamzZ] [Subject changed from "Why????"]

[Updated on: Mon, 31 December 2007 11:04] by Moderator

Report message to a moderator

Re: Which Method is Better Pl-Sql [message #290770 is a reply to message #290768] Mon, 31 December 2007 11:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is method C:
for i in 1..1000 loop
  insert into t values (i);
end loop;

or better method D:
declare 
  tab dbms_utility.number_array;
begin
  for i in 1..1000 loop
    tab(i):= i;
  end loop;
  forall i in tab.first..tab.last 
    insert into t values(tab(i)):
end;

Regards
Michel
Re: Which Method is Better Pl-Sql [message #290771 is a reply to message #290770] Mon, 31 December 2007 11:28 Go to previous messageGo to next message
qasim845
Messages: 95
Registered: March 2007
Location: Philadelphia
Member

Micheal Thanks and appreciated. Smile

I just wana be sure what u have in ur mind. Why It is Method C or D why not anyone from A and B.

Thanks again.

[Updated on: Mon, 31 December 2007 12:32]

Report message to a moderator

Re: Which Method is Better Pl-Sql [message #290772 is a reply to message #290771] Mon, 31 December 2007 12:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To understand we will take an analogy with a program.
We want to write into a file 1000 lines containing the number from 1 to 1000.

Method A:
program is:
compile and execute "write (file, '1')";

compile, link, execute.
Now, edit, change 1 to 2.
compile, link, execute.
Now, edit, change 2 to 3.
compile, link, execute.
and so on until 1000

Method B:
program is:
for i in 1..1000 loop
  compile and execute "write (file, i);"
end loop;

compile, link and execute program.

Method C:
program is:
for i in 1..1000 loop
  write (file, i);
end loop;

compile, link and execute program.

Method D:
program is:
write(file, numbers from 1 to 1000);

compile, link and execute program.

This is just an analogy and not fully true but it gives an idea of the work done.
In method A, you change the program, recompile and relink it each time you change the value.
In Method B, you compile only one the program but recompile the inner statement for each value.
In Method C, you compile the program (and all its statements) only once but you call write procedure 1000 times .
In Method D, you compile the program only once and call write procedure only once.

Of course, this does not take into account shared area. In method a, you use 1000 sql areas whereas you use only 1 in other methods. So in the first case you age other sessions SQL that have to be reloaded and recompiled to be executed.

Regards
Michel


[Updated on: Mon, 31 December 2007 12:53]

Report message to a moderator

Re: Which Method is Better Pl-Sql [message #290831 is a reply to message #290772] Tue, 01 January 2008 05:40 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
nice explanation.

Regards

Raj
Re: Which Method is Better Pl-Sql [message #290851 is a reply to message #290831] Tue, 01 January 2008 09:48 Go to previous messageGo to next message
peterl
Messages: 12
Registered: September 2007
Junior Member
Method B is much better. There are 3 important reasons why binding (using i) instead of concatenation is much better.

1. Performance

Binding performs much better, especially in a multi user environment. If you want to know why read the books of Thomas (Tom) Kyte. These excellent books will tell you how to built fast and scalable Oracle applications and these books are a pleasant read.

2. Protection against SQL injection attacks.

Read: http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/dynamic.htm#CHDFCHHJ .

3. Protection against errors with quotes.

Sometimes there is a ' inside a varchar2. If you concate your sql statement there will be a run time error. If you use binding there will be no run time error. Example:


drop table testname;

create table testname (name varchar2(20));

declare
  l_name testname.name%type;
begin
  l_name := 'Strange '' Name';
  execute immediate ' insert into testname values (' || l_name ||');
end;
/

This outputs:ERROR: ORA-01756, so no rows inserted.

select count(*) from testname;

COUNT(*)
----------
0
declare
l_name testname.name%type;
begin
  l_name := 'Strange '' Name';
  execute immediate ' insert into testname values (:name)'  using l_name;
end;
/

This works fine, 1 row inserted!!

select count(*) from testname;

COUNT(*)
----------
1


If you bind you don't have to worry about quotes !! Hey, inside the word don't there is a ' !!
Re: Which Method is Better Pl-Sql [message #290852 is a reply to message #290851] Tue, 01 January 2008 10:18 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
@Peterl,

Are you trying to say of all the methods Michael has explained, Method B is the best option just because it is using bind variables ?

regards

Raj
Re: Which Method is Better Pl-Sql [message #290853 is a reply to message #290852] Tue, 01 January 2008 10:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think he directly answered to OP's question.
Of course, methods C and D are using bind variables (all PL/SQL variables are bind variables).

He nevertheless touched a very important point I didn't talked about: SQL injection.
Every time you concatenate input variables you are in danger of SQL injection. You can limit it with value checks such ones provided by Oracle dbms_assert package or Steven Feuerstein sql_guard.

Regards
Michel
Re: Which Method is Better Pl-Sql [message #290860 is a reply to message #290768] Tue, 01 January 2008 12:43 Go to previous messageGo to next message
peterl
Messages: 12
Registered: September 2007
Junior Member
I have been unclear. I didn't really look at C and D. I did choose between A and B.

A and B are both dynamic sql, C and D are static SQL.

Dynamic sql is when you use sql with execute immediate ' ' or DBMS_SQL. (DBMS_SQL is an Oracle package, it gives approximately the same possibilities as execute immediate but it is more complicated to use. )

Static SQL is faster than Dynamic SQL, static SQL gives you also compile time safety. When it compiles you know that table T exists. With methods A and B you will only know if table T exists when you run!

However the three problems (1. performance, 2. SQL injection and 3. problems with quotes) I mentioned with method A are not going to happen when you use B,C or D.

If we order by performance

A. slow
B. fast
C. A little bit faster than B
D. really fast because it is a BULK action


Sometimes you have to use dynamic SQL because it is more flexible but when you don't have to use it, don't use it. But when you use it, use it properly and use binding (method B).



Re: Which Method is Better Pl-Sql [message #290861 is a reply to message #290860] Tue, 01 January 2008 12:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
C is really faster than B (and not a little bit).
B implies a (soft) parse each time. C parse only once, the first time.

In short:
A: 1000 hard parses, 1000 context swicthes between SQL and PL/SQL engines
B: 1 hard parse, 999 soft parses, 1000 context switches
C: 1 hard parse, 1000 context switches
D: 1 hard parse, 1 context switch

Regards
Michel
Re: Which Method is Better Pl-Sql [message #290862 is a reply to message #290861] Tue, 01 January 2008 13:51 Go to previous messageGo to next message
peterl
Messages: 12
Registered: September 2007
Junior Member
When I measure B and C using runstats (of Tom Kyte) I see differences but I think that the differences are small, C is faster than B but B is certainly acceptable.

Acceptable is highly subjective and depending on the situation so we should not debate about that. We use 'execute immediate' quite a lot and are happy with the performance.

We generate packages against tables and views that do the 'select', they return a ref cursor. That means no sql in our client, our client only calls stored procedures.

If it is possible to generate static sql our tool will do it but if you want more complicated things (range searching for instance), dynamic sql is generated.

I have understood that if you use DBMS_SQL instead of execute immediate there will be less soft parsing (with less soft parsing I mean no parsing at all). The big drawback of dbms_sql is that you can't use it to return a ref cursor. However in Oracle 11 this has changed (dbms_sql.to_refcursor) so will we look again to dbms_sql .

[Updated on: Tue, 01 January 2008 14:05] by Moderator

Report message to a moderator

Re: Which Method is Better Pl-Sql [message #290863 is a reply to message #290768] Tue, 01 January 2008 13:56 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>We use 'execute immediate' quite a lot and are happy with the performance.
Until such time when the non-scalability (hard parses for every statement execution) of this approach results in unacceptable performance.

[Updated on: Tue, 01 January 2008 14:30] by Moderator

Report message to a moderator

Re: Which Method is Better Pl-Sql [message #290864 is a reply to message #290862] Tue, 01 January 2008 14:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

When I measure B and C using runstats (of Tom Kyte) I see differences but I think that the differences are small,

Loop on it many times with 10, 20 or 50 concurrent sessions, there you will see the difference.

Regards
Michel

[Updated on: Tue, 01 January 2008 14:11]

Report message to a moderator

Re: Which Method is Better Pl-Sql [message #290865 is a reply to message #290863] Tue, 01 January 2008 14:43 Go to previous messageGo to next message
peterl
Messages: 12
Registered: September 2007
Junior Member
Different systems, different needs. Our customers don't do heavy online transaction processing (they are civil servants Wink ). We have done multi user testing and the way we use Oracle is not the bottleneck.

In our system you can search on nested tables (p_idnummer is a nested table).
      select *
      from   emp
      where (id) in
        ( select/*+ cardinality (tab 10) */ tab.nr
          from   table(p_idnummer) tab )

This greatly improved our performance because less calls to the database. In this example we search on the primary key so no need to use 'execute immediate' but you can bind a nested table to an 'execute immediate' statement.

We do have performance issues when we want to combine the results of different web services (SOA) but that has nothing to with the way we use Oracle. All the XML messages (SOAP) make our system slow. We don't use XML for storing information, only for messaging. I don't understand why people want to store information in XML-format.

I am studying Windows Communication Framework (WCF) right now.





Re: Which Method is Better Pl-Sql [message #290866 is a reply to message #290865] Tue, 01 January 2008 15:26 Go to previous messageGo to next message
peterl
Messages: 12
Registered: September 2007
Junior Member
@Michel Cadot

Michel I like talking to you. Look at it this way.

A user starts the application, first he starts to use a search form. He can search on lot of different fields (in our system) and after that he pushes the search button. I call this the first query. It is difficult to predict what combination of fields he has filled with search conditions. (age, street, day of birth, zip code, alive, death, ranges etc. ). For this first query we use an execute immediate.

Then we show the results of our first query in a grid with the search results. The user selects some rows in this grid because he wants to make some changes. We ask Oracle to get the detail records of the master rows the user has selected. We know exactly what kind of where clause we have to use to get the details of the chosen master records, so no need to use dynamic sql. We search with a nested table (collection) see above.

After that the user changes something and he saves the changes. No need to use execute immediate for insert, updates and deletes. We generate code for inserts, updates and deletes but we generate static SQL. After the data manipulation and the commit we requery the records the user changed. We know the records the user changed so no need for dynamic sql.

Than the user will search again in the search form.

Only for the first query you need dynamic sql because you don't know how the user will search. I will be happy if every first query is a soft parse instead of a hard parse. The aim of no parsing at all for the first query is imho only possible when you strongly limit the search possibilities for the user. Our users don't want that! The want a lot of search possibilities. We have a lot of search forms with a lot of fields, so a lot of combinations!




Re: Which Method is Better Pl-Sql [message #290867 is a reply to message #290866] Tue, 01 January 2008 15:42 Go to previous messageGo to next message
peterl
Messages: 12
Registered: September 2007
Junior Member
@anacedent

You write: "hard parses for every statement execution" .

This is not true, if you bind properly 'execute immediate' doesn't mean hard parsing for every execution. Method B will do do only one hard parsing, after the first hard parse you will get soft parses.

Re: Which Method is Better Pl-Sql [message #290868 is a reply to message #290864] Tue, 01 January 2008 15:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here tests I made a couple of years ago.
3 tests named "Literal", "Dynamic" and "Static" made with 1 to 10 concurrent sessions.
create table t (c1 int, c2 int, c3 int, c4 int) initrans 10 storage (freelists 10);

Results are given with the same scale to make easier the comparison.

Literal
declare
  l_number number;
begin
  for i in 1..10000 loop
    l_number := dbms_random.random;
    execute immediate
      'insert into t values (' || l_number || ', ' || l_number || ', ' ||
                                  l_number || ', ' || l_number || ')';
  end loop;
  commit;
end;
/

Results:
./fa/3671/0/
As you can see that to make the same job, the time increases from 4.54 seconds for one session alone to 29.74 seconds for the same session when they are 10 concurrent sessions.
The greatest part of the growth comes from latch waits during parses.

Dynamic
declare
  l_number number;
begin
  for i in 1..10000 loop
    l_number := dbms_random.random;
    execute immediate
      'insert into t values (:v1, :v2, :v3, :v4)'
      using l_number, l_number, l_number, l_number;
  end loop;
  commit;
end;
/

Results:
./fa/3672/0/
Here the time increases from 1.82 second for a lone session to 9.89 seconds for the same session when they 10 concurrent ones.

Static
declare
  l_number number;
begin
  for i in 1..10000 loop
    l_number := dbms_random.random;
    insert into t values (l_number, l_number, l_number, l_number);
  end loop;
  commit;
end;
/

Results:
./fa/3673/0/
In this optimal test time increase from 0.92 second to 4.53 seconds and this growth comes from log waits (2.5 seconds in average for 8 to 10 concurrent sessions).
Tests were made on a small Solaris server with Oracle 8.1.7.4.

Now if we directly compare parse CPU and latch wait times using the ratio of the first 2 tests to the last one, we get:
./fa/3674/0/
./fa/3675/0/


Regards
Michel


  • Attachment: Literal.jpg
    (Size: 37.91KB, Downloaded 336 times)
  • Attachment: Dynamic.jpg
    (Size: 34.26KB, Downloaded 326 times)
  • Attachment: Static.jpg
    (Size: 31.35KB, Downloaded 348 times)
  • Attachment: ParseRatio.jpg
    (Size: 37.70KB, Downloaded 330 times)
  • Attachment: LatchRatio.jpg
    (Size: 36.43KB, Downloaded 330 times)
Re: Which Method is Better Pl-Sql [message #290963 is a reply to message #290868] Wed, 02 January 2008 06:14 Go to previous messageGo to next message
peterl
Messages: 12
Registered: September 2007
Junior Member
@Michel Cadot

Nice info. Could you do the same test with DBMS_SQL instead of execute immediate? If you call DBMS_SQL.PARSE only once, I hope all the next executes do no parsing.

I believe 'execute immediate' is sometimes inevitable. When the user can search with a lot of different combinations, execute immediate is almost the only way.



Re: Which Method is Better Pl-Sql [message #290966 is a reply to message #290963] Wed, 02 January 2008 06:35 Go to previous message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said, I did this years ago but it is easy to adapt the code with dbms_sql.

Regards
Michel
Previous Topic: Duplicate rows (3 topics merged)
Next Topic: ONLINE REDEFINITION
Goto Forum:
  


Current Time: Tue Dec 06 02:45:29 CST 2016

Total time taken to generate the page: 0.10398 seconds