| Different Query Plan and execution time of same Query [message #278156] |
Fri, 02 November 2007 02:29  |
junaidsystems
Messages: 30
Registered: June 2006
|
Member |
|
|
Hi,
I am getting a problem with a query. When I run my query seprately it run quickly while same takes too much time while called inside the package.
When I check the plan with explain(plsql developer) it shows me plan with reduced cost using unique scan and ran quickly in plsql developer, but during running the same query inside the package (again run through plsql dev) shows different plan using fas full scan( showing in OEM Top activity) and take 10 minutes to run.
It seems to me that query plan has already cahced in shared pool and not changing while running in pakcage.
should i change session_cached_cursor or any other parameter ?
thanks in advance
|
|
|
|
|
|
| Re: Different Query Plan and execution time of same Query [message #278205 is a reply to message #278162] |
Fri, 02 November 2007 06:46  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
I'll bet dollars to donuts you are cutting the SQL out of PL/SQL into PL/SQL developer and then CHANGING the bind variables to constants strings, dates, and numbers.
Surpise! Changing bind variables to constants affects the plan.
Stick a colon in front of them and then get the plan.
eg.
SELECT *
INTO my_rec
FROM my_table
WHERE col1 = vCol1
SELECT *
FROM my_table
WHERE col1 = :vCol1
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
