| Query plan change [message #332707] |
Wed, 09 July 2008 07:18  |
jinga
Messages: 116
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 #332718 is a reply to message #332707] |
Wed, 09 July 2008 08:06  |
jinga
Messages: 116
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
