Re: Using Host Variables in ODBC -Oracle Enviornment

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 29 Aug 2001 08:25:00 -0700
Message-ID: <9mj1gc018e8@drn.newsguy.com>

In article <9mhvvs$jm0$1_at_ctb-nnrp2.saix.net>, "Billy says...
>
>"FaheemRao" <faheemrao_at_yahoo.com> wrote in message
>>
>> My Application using ODBC to connect to Oracle Database,
>> Now as I am hardcoding the values in SQL queries like this
>> select * from table_name where column_name = 1234;
>> Now every time I issue above SQL if column_name is different every
>> time like
>> select * from table_name where column_name = 444;
>>
>> Oralce parse this SQL , which takes time,
>
>The time Oracle spend on parsing is negligible in a situation like this. It
>should not be a factor.
>

beg to differ -- it could be that 90% of the EXECUTION of that query is spent parsing.

This is huge -- bind variables are crucial.

Consider (flush the shared pool before running this for the second time):

tkyte_at_TKYTE816> declare

  2      type rc is ref cursor;
  3      l_rc rc;
  4      l_dummy all_objects.object_name%type;
  5      l_start number default dbms_utility.get_time;
  6  begin
  7      for i in 1 .. 1000
  8      loop
  9          open l_rc for
 10          'select object_name
 11             from all_objects
 12            where object_id = ' || i;
 13          fetch l_rc into l_dummy;
 14          close l_rc;
 15      end loop;
 16      dbms_output.put_line
 17      ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
 18        ' seconds...' );

 19 end;
 20 /
14.86 seconds...

PL/SQL procedure successfully completed.

versus:

tkyte_at_TKYTE816> declare

  2      type rc is ref cursor;
  3      l_rc rc;
  4      l_dummy all_objects.object_name%type;
  5      l_start number default dbms_utility.get_time;
  6  begin
  7      for i in 1 .. 1000
  8      loop
  9          open l_rc for
 10          'select object_name
 11             from all_objects
 12            where object_id = :x'
 13          using i;
 14          fetch l_rc into l_dummy;
 15          close l_rc;
 16      end loop;
 17      dbms_output.put_line
 18      ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
 19        ' seconds...' );

 20 end;
 21 /
1.27 seconds...

PL/SQL procedure successfully completed.

(your mileage may vary on the timings obviously). thats a huge different and it doesn't show the entire picture either -- you are missing the latching and all that was going on to do the parse. If you do this with 2 sessions, then 3, then 4 and so on -- the times for the hard parses will go through the roof. The times for the soft parsed query will be infinitely better in comparision.

here, the hard parse took 11 times longer.

>The primary reason for bind variables is when dealing with a high volume OLTP
>system, where you have 1000's of SQL update per minute, many using the the same
>SQL update statement, with different values.
>

Not so, not so -- the primary reason for bind varables is to avoid trashing the shared pool and expending the CPU to parse them all. He is running the same query over and over.

>However, you are doing a SELECT. It is unlikely that you are running 100's of
>this same SELECT at the same time.
>

why not? he might have hundreds or thousands of users connected all running this query. He might be doing it in a loop as above. Even if he is not -- he is adversely affecting his database.

>Not saying that you do not need to use bind variables. Where possible, use them
>always. But if you have a performance problem with your application and this
>particular SELECT statement, I doubt that making use of bind variables will
>solve it (unless, as I mentioned, you have 100's of these running at the same
>time).
>

it will solve many dozens of issues in the database and they don't have to be running at the same time (one after the other is bad). All it takes is ONE bad query without bind variables to potentially KILL a system performance wise (I'll run my query without bind variables over and over, filling up the shared pool -- bumping your stuff out with my stuff that'll never be reused causing you to hard parse as well -- increasing by orders of magnitudes the contention on the library cache -- using the CPU like crazy and so on). It takes ONE bad query.

>> now my question is that is there any way that I can use host
>> variables Using ODBC, Or any other way so that Oracle do not parse
>> my SQL Queries every time .
>
>The ODBC API does support bind variables. But you are likely not using the ODBC
>API directly, or else you will have seen that in the sample code and API specs.
>Which means that you are likely using Visual Basic? It should be possible in VB
>too.. checks the docs. In Delphi, it is pretty simple:
>--
>begin
> query.SQL.Clear;
> query.SQL.Add('select * from table_name where column_name = :NAME');
> query.Params[0].AsNumber := 1234;
> query.Open;
>
> -- do stuff --
> query.Close;
> query.Params[0].AsNumber := 400;
> query.Open;
>end;
>--
>
>VB should support something similar.
>
>
>--
>Billy
>
>
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Wed Aug 29 2001 - 10:25:00 CDT