 Query using bind variable is slower?? [message #151750] |
Mon, 19 December 2005 00:16  |
claymentlim
Messages: 1
Registered: December 2005
Location: Kuala Lumpur, Malaysia
|
Junior Member |
|
|
Hi All,
Being a new guy in DB admin, would like to seek some help here.
My user complaints querying the DB using bind variable is slower than using SQL-Plus, and suggested to remove the bind variables from the DB cache. I am not sure if this is true, but I have no idea how to prove him wrong or to explain why is using bind variable makes the query slow.
Appreciate any help.
The user sent me 2 of his queries:
Firstly.
SELECT COUNT(DISTINCT t0.POLID)
FROM POLSEA t1, POLRES t0
WHERE (t0.POLID = t1.ENTID
AND t1.ENTTYP = :1
AND t1.POLNO LIKE :2)
Secondly.
SELECT DISTINCT t0.POLID, t0.CLASS, t0.LOCK, t0.ACCTID, t0.BTYP, t0.ACODE, t0.ANAME, t0.CDATE, t0.CNAME, t0.CNO, t0.EDATE, t0.EXDATE, t0.ADDR, t0.SX, t0.INAME, t0.LDATE, t0.PERF, t0.POLENG, t0.POLNO, t0.PRD, t0.STT, t0.UCO, t0.UCX
FROM POLSEA t1, POLRES t0
WHERE (t0.POLID = t1.ENTID
AND t1.ENTTYP = :1
AND t1.POLNO LIKE :2)
Please revert, many thanks in advance.
|
|
|
|
| Re: Query using bind variable is slower?? [message #151765 is a reply to message #151750] |
Mon, 19 December 2005 02:14  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Get comparative EXPLAIN PLANs for with and without bind variables.
Doubtless Oracle is using it's knowlege of the data distribution to choose a better plan when constant values are used.
You won't prove him wrong. Constant value predicates allow the optimizer to choose the best possible plan for those values. If three different sets of values result in three separate optimal plans (optimal for that combination of values) then constant predicates handles that, but bind variables wont.
With bind variables, Oracle must choose one execution plan only to cover all possible combinations of bind values. Some will be optimal, some won't.
The reason bind variables are encouraged is not because they result in more optimal SQL, but because they don't clog up the shared pool with many versions of the same SQL.
If the SQL is part of the user-accessible front-end, you must use bind variables - you can't have thousands of 'identical' SQLs in the shared pool - you'll bring the database to it's knees. Tell your guy to build a bridge (and "get over it").
If it's a once-per-day overnight batch process, go crazy and use constant predicates.
_____________
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
