Home » RDBMS Server » Performance Tuning » Query plan change
Query plan change [message #332707] Wed, 09 July 2008 07:18 Go to next message
jinga
Messages: 115
Registered: January 2003
Senior Member

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE\t10.2.0.2.0\tProduction
TNS for Solaris: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production


One of our stored procedures which usually takes about 3 hours to run took around 12 hours last week. We realised that its query plan got changed - explain plan for previous runs shows that it uses the primary key index to do a index range scan. This time it used a different index idx1_table1 - on a single column.

What makes the oracle to choose different query plan. Would it be safer to direct ORACLE to force the primary key index using INDEX HINT?

Anu
Re: Query plan change [message #332712 is a reply to message #332707] Wed, 09 July 2008 07:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What makes the oracle to choose different query plan

Statistics and environment parameters such as used memory, area size, literal values...

Regards
Michel
Re: Query plan change [message #332718 is a reply to message #332707] Wed, 09 July 2008 08:06 Go to previous messageGo to next message
jinga
Messages: 115
Registered: January 2003
Senior Member


SGA_MAX_SIZE was increased. there was a small change in the stored procedure.

Would increasing SGA_MAX_SIZE has to do with the different query plan.

Is forcing the index is the only way to have consistent query plan?

Anu
Re: Query plan change [message #332721 is a reply to message #332718] Wed, 09 July 2008 08:30 Go to previous message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Quote:
Is forcing the index is the only way to have consistent query plan?


No
You can use stored outlines

[Updated on: Wed, 09 July 2008 08:30]

Report message to a moderator

Previous Topic: update is slow with CLOB data
Next Topic: SQL Tuning (Multicolumn Range Parttiion Pruning)
Goto Forum:
  


Current Time: Mon Dec 05 09:11:18 CST 2016

Total time taken to generate the page: 0.12731 seconds