Home » RDBMS Server » Performance Tuning » SQL ID changes Plan hash value (12c R1)
SQL ID changes Plan hash value [message #683717] Tue, 16 February 2021 07:38 Go to next message
JackBauer
Messages: 24
Registered: February 2021
Junior Member
Hello,

When you see that an SQL ID has changed its plan hash value and you notice that the old plan hash value is better, do you just fix the old plan hash value with a profile?

I assume that Oracle changed the plan for a reason, right? So why keep the old one?

Thanks
Re: SQL ID changes Plan hash value [message #683855 is a reply to message #683717] Thu, 25 February 2021 00:49 Go to previous messageGo to next message
JackBauer
Messages: 24
Registered: February 2021
Junior Member
No one for this?
Re: SQL ID changes Plan hash value [message #683856 is a reply to message #683717] Thu, 25 February 2021 01:01 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You could investigate why the optimizer felt it necessary to reparse and create a new cursor. There are many reasons, and either plan may be appropriate depending on circumstances. v$sql_shared_cursor will help.
Re: SQL ID changes Plan hash value [message #683862 is a reply to message #683717] Thu, 25 February 2021 09:22 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
JackBauer wrote on Tue, 16 February 2021 05:38
Hello,

When you see that an SQL ID has changed its plan hash value and you notice that the old plan hash value is better, do you just fix the old plan hash value with a profile?

I assume that Oracle changed the plan for a reason, right? So why keep the old one?

Thanks

does SQL use bind variables? Bind variable peeking?
Re: SQL ID changes Plan hash value [message #683863 is a reply to message #683717] Fri, 26 February 2021 05:02 Go to previous messageGo to next message
JackBauer
Messages: 24
Registered: February 2021
Junior Member
I am talking in general, yes they normally use bind variables.
Re: SQL ID changes Plan hash value [message #683865 is a reply to message #683863] Fri, 26 February 2021 08:45 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
JackBauer wrote on Fri, 26 February 2021 03:02
I am talking in general, yes they normally use bind variables.

In general the output changes after the inputs change.
Previous Topic: I/O performance problem
Next Topic: Cannot index temp table after inserting rows
Goto Forum:
  


Current Time: Thu Mar 28 12:03:09 CDT 2024