RE: Must Read for Every Developer and DBA

From: Nicoll, Iain $Calanais$ <iain.nicoll_at_calanais.com>
Date: Fri, 06 Sep 2002 06:23:21 -0800
Message-ID: <F001.004C9A96.20020906062321@fatcity.com>

I thought that bind variables were faster but you always have to ensure that if you're accessing by data which may be heavily skewed and histograms would usually help you may not want to use bind variables as they will disable the use of histograms.

In saying that it doesn't look as though that would be the case here.

Iain Nicoll

-----Original Message-----
Sent: Friday, September 06, 2002 2:33 PM 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: 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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Nicoll, Iain \(Calanais\)
  INET: iain.nicoll_at_calanais.com

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 - 09:23:21 CDT