| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Must Read for Every Developer and DBA
...But only on the first execution of a session. It's a bug.
Test (credit Jonathan Lewis):
0. Set up "select c1, c2 from t1 where c1=:bind1" where different bind1 values would motivate different execution plans if we used literals. E.g., insert only a few rows where c1=5, and thousands of rows where c1=70.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Hotsos Clinic, Oct 1-3 San Francisco, Oct 15-17 Dallas, Dec 9-11
Honolulu
- 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas
- Next event: Miracle Database Forum, Sep 20-22 Middelfart Denmark
-----Original Message-----
BALA,PRAKASH (Non-HP-USA,ex1)
Sent: Friday, September 06, 2002 5:08 PM
To: Multiple recipients of list ORACLE-L
This is true in 8i. But in 9i, this has changed per Gaja. Even if bind variables are used, it will use histograms if histograms are present.
Prakash
-----Original Message-----
Sent: Friday, September 06, 2002 10:23 AM
To: Multiple recipients of list ORACLE-L
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
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 ANDUSER_ID
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 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-LReceived on Fri Sep 06 2002 - 18:18:20 CDT
(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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: BALA,PRAKASH (Non-HP-USA,ex1) INET: prakash_bala_at_non.hp.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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cary Millsap INET: cary.millsap_at_hotsos.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).
 |
 |