| Query performance: Hard code values vs. parmeterized values [message #179493] |
Tue, 27 June 2006 13:06  |
lamhe2804
Messages: 4
Registered: June 2006
Location: Dallas
|
Junior Member |
|
|
I am seeing huge delay for query when I pass values as paramters compare to hard coded in SQL.
Paramter Query example:
SELECT *
FROM table1 t0
WHERE t0.sent_Dttm >= TO_DATE(?,'YYYY-MM-DD HH24:MI:SS') AND
t0.sent_Dttm <= TO_DATE(?,'YYYY-MM-DD HH24:MI:SS') AND
t0.field1=? AND
t0.field2=? AND
ROWNUM <=500
[params=(String) 2006-06-19 00:00:00, (String) 2006-06-20 23:59:59, (String) EMAIL, (String) 0000]
Result: Total time (ms): 47874 Total rows :500
Hardcode Query example:
SELECT *
FROM table1 t0
WHERE t0.sent_Dttm >= TO_DATE('2006-06-19 00:00:00','YYYY-MM-DD HH24:MI:SS') AND
t0.sent_Dttm <= TO_DATE('2006-06-20 00:00:00','YYYY-MM-DD HH24:MI:SS') AND
t0.field1='EMAIL' AND
t0.field2='0000' AND
ROWNUM <=500
Result: Total time (ms): 16 Total rows :500
What could cause such a difference?
Thanks,
|
|
|
|
| Re: Query performance: Hard code values vs. parmeterized values [message #179546 is a reply to message #179493] |
Tue, 27 June 2006 22:23  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Oracle uses the same execution plan to execute identical SQLs. If you were to run the first SQL twice with different values, it would use the same plan. For this reason, Oracle chooses a plan that it deems appropriate (although not necessarily optimal) for a variety of possible values.
When values are hard-coded, every SQL is different, so Oracle can choose a different plan for every SQL. In this case, it looks at the values you have supplied and optimises the SQL for that data.
There is a feature in more recent versions or Oracle whereby Oracle may "peek" at the values you are supplying for bind variables and optimise accordingly. This can be pretty unreliable though, because it only works the first time. When you resubmit the SQL with different variables it continues to use the first plan it came up with.
If bind-variables result in a poor plan, and hard-coded values result in a good plan, the easiest solution is to provide hints to the optimizer (see the Oracle Performance Tuning Manual) to get it to choose the same path as the hard-coded version. There are also other methods whereby you can effectively "save" the execution plan to the database so that it always uses the same plan no matter what. You can discuss this with your DBA.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
