Must Read for Every Developer and DBA

From: Vikas Khanna <vkhanna_at_quark.co.in>
Date: Fri, 06 Sep 2002 05:33:20 -0800
Message-ID: <F001.004C99D9.20020906053320@fatcity.com>

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: vkhanna_at_quark.co.in

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: ListGuru_at_fatcity.com (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).

Received on Fri Sep 06 2002 - 08:33:20 CDT