OraFAQ Forum: Performance Tuning » SQL ID changes Plan hash value

Home » RDBMS Server » Performance Tuning » SQL ID changes Plan hash value (12c R1)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL ID changes Plan hash value [message #683717]

Tue, 16 February 2021 07:38

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

Report message to a moderator

Re: SQL ID changes Plan hash value [message #683855 is a reply to message #683717]

Thu, 25 February 2021 00:49

JackBauer
Messages: 24
Registered: February 2021

Junior Member

No one for this?

Report message to a moderator

Re: SQL ID changes Plan hash value [message #683856 is a reply to message #683717]

Thu, 25 February 2021 01:01

John Watson
Messages: 8976
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.

Report message to a moderator

Re: SQL ID changes Plan hash value [message #683862 is a reply to message #683717]

Thu, 25 February 2021 09:22

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?

Report message to a moderator