RE: Must Read for Every Developer and DBA

From: Fink, Dan <Dan.Fink_at_mdx.com>
Date: Fri, 06 Sep 2002 09:23:38 -0800
Message-ID: <F001.004CA004.20020906092338@fatcity.com>

Another issue is the ability of the CBO to use bind variables. Until recently (9i, I think), the CBO would use a single execution plan for a statement with bind variables, even if the data values of the bind variables actually indicated that the plan was not optimal. For example, a query that retrieves employee addresses by city (bind variable) would use the same execution plan for the city of New York, NY as for the city of Twin Lakes, Colorado. In the first case, a fts may be in order, where an index lookup would be more appropriate for the second. The execution plan is determined by which statement was executed first.

Dan

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

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.
dwilliams_at_lifetouch.com

-----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: 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: DENNIS WILLIAMS
INET: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------