Home » RDBMS Server » Performance Tuning » Different Query Plan and execution time of same Query (ORACLE 10g)
Different Query Plan and execution time of same Query [message #278156] Fri, 02 November 2007 02:29 Go to next message
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 #278162 is a reply to message #278156] Fri, 02 November 2007 03:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

should i change session_cached_cursor or any other parameter ?

Just change your way of thinking. Why each time someone has a performances problem he thinks which database parameter should I change? It's killing me.

Read and follow OraFAQ Forum Guide, especially "Performance tuning" section and How to Identify Performance Problem and Bottleneck .
Always post your Oracle version (4 decimals) and all relevant information for tuning.


Regards
Michel
Re: Different Query Plan and execution time of same Query [message #278205 is a reply to message #278162] Fri, 02 November 2007 06:46 Go to previous message
rleishman
Messages: 3724
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
becomes
SELECT *
FROM my_table
WHERE col1 = :vCol1


Ross Leishman
Previous Topic: Anti-Join (HASH_AJ) with multiple columns in the "NOT IN" clause
Next Topic: Help needed in querying View
Goto Forum:
  


Current Time: Sun Dec 04 08:18:35 CST 2016

Total time taken to generate the page: 0.07874 seconds