Vikas
   I had a developer recently report that his program was actually a little
faster by not using bind variables. Once I stopped screaming I calmly
explained the following.
  The issue isn't whether bind variables are faster or not, but rather what
SQL statements that don't use bind variables do to the shared pool. I will
simplify some of the details following. When Oracle receives a SQL
statement, it first scans the SQL buffer to see if it has encountered this
statement before. If it finds the SQL statement in the buffer, then it
proceeds to execute it. If it doesn't find the SQL statement, then it must
parse it and find a place in the buffer to keep it in hopes it will
encounter it again. 
   The real problem with SQL statements that don't use bind variables is
that the SQL buffer becomes filled with statements that will never be used
again. So Oracle has to expend a lot of effort searching, parsing, aging out
the oldest statements, etc. All for nothing because you aren't using bind
variables and those statements will never be used again.
   How you use bind variables varies a lot depending on which language you
are using.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]


-----Original Message-----
Sent: Friday, September 06, 2002 8:33 AM
To: Multiple recipients of list ORACLE-L


Hello Vikas,

As You said We should always make use of bind variables as it executes
faster as compare to the statements where we do not
make use of bind variables.

Q1) Can you please take a more specific example as how a statement can be
altered to make use of bind variable.

Q2) I made use of SELECT SQL_TEXT FROM V$SQLAREA WHERE ROWNUM < 5 to get few
samples for you 

These are as follows 

UPDATE   CNST_QUEUE   SET      PROCESS = -1     ,USER_ID ='A101675'
,DATE_QUEUED = sysdate  WHERE      AWB_PREFIX
 = '125'  AND      AWB_NUMBER  = 67557405  AND      AWB_SUFFIX  = '  '  AND
PROCESS = 1 AND    USER_ID = 'A101675'

UPDATE   CNST_QUEUE   SET      PROCESS = -1     ,USER_ID = 'A101675'
,DATE_QUEUED = sysdate  WHERE      AWB_PREFIX
 = '125'  AND      AWB_NUMBER  = 68221156  AND      AWB_SUFFIX  = '  '  AND
PROCESS = 1 AND      USER_ID = 'A101675'

UPDATE   CNST_QUEUE   SET      PROCESS = -1     ,USER_ID ='A105722'
,DATE_QUEUED = sysdate  WHERE      AWB_PREFIX
 = '125'  AND      AWB_NUMBER  = 67557405  AND      AWB_SUFFIX  = 'A '  AND
PROCESS = 1 AND      USER_ID = 'A105722'

UPDATE   CNST_QUEUE   SET      PROCESS = -1     ,USER_ID = 'A105722'
,DATE_QUEUED = sysdate  WHERE   AWB_PREFIX = '125'  AND      AWB_NUMBER  =
67557416  AND      AWB_SUFFIX  = '  '  AND      PROCESS = 1 AND      USER_ID
= 'A105722

How can I Introduce bind variables in these statements ?

I may be sending a wrong SAMPLE as I feel I should apply your remove
constant function and then send few SQL statements

Warm Regards,
Om

In your case -- you are NOT using bind variables. 

Taking your update statement here:

 UPDATE   CNST_QUEUE   SET      PROCESS = -1     ,USER_ID = 'A101675'
,DATE_QUEUED = sysdate  WHERE      AWB_PREFIX
 = '125'  AND      AWB_NUMBER  = 67557405  AND      AWB_SUFFIX  = '  '  AND
PROCESS = 1 AND      USER_ID = 'A101675'

that SHOULD BE recoded in the application to become : 

update cnst_queue set process = :b1, user_id = :b2, date_queued = sysdate,
where awb_prefix = :b3
   and awb_number = :b4
   and awb_suffix = :b5
   and awb_process = :b6
   and user_id = :b7;

and bind in those values before you execute this statement. There are ways
in which it could be done and vary from language to language and environment
to environment but they ALL support it.  You MUST do this. In this case,the
first time you execute this statement you need to parse this statement (HARD
PARSING) and once the execution plan gets into the SHARED POOL
(V$libraryCache) the other users can use this to great effect. They would
not reparse this statement again and again and but does do the soft parsing
of it. So One Parse may lead to MANY executions instead of 1 Parsing <-> 1
Execution.

At least 90% of your database execution time is spent PARSING and OPTIMIZING
that update -- 10% is spent actually DOING it.  If you use bind variables --
very little time will be spent parsing (you can get that statement to
execute in 1/10 of the time).  Not only that -- but the concurrency and
scalability of your database will go WAY up.

This is the root cause of your issues, this must be fixed -- no questions
about it.    

Vikas Khanna 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Vikas Khanna
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Reply via email to