Oracle SQL Plan Hash Value Flip : How to create SQL Profile identify and Fix

This is a common scenario when the SQL_ID flipped to a different, possibly a suboptimal execution plan or PHV and causes disaster to the database performance. This video is all about identifying the issues and how to tackle them proactively.
Files used: github.com/fat... (1.sql) and github.com/fat... (2.sql)

[@neerajgupta1140]

Nice explanation. Great work Prashant !!

[@thefatdba]

Thanks Neeraj!

[@mdhasibullah318]

Prashant it's very nice , please upload more .

[@madhusudanaraonallamothu2792]

Thank you very much for sharing your knowledge. Very much informative..🙏🙏🙏

[@RitulRastogi]

thanku sir for this amazing videos ...keep posting sir it helps alot :)

[@suryateja2043]

Very Good one, Thanks for sharing

[@thefatdba]

Thanks Surya!

[@harishharish.r3338]

Hi Prashanth,
very well explained , could you please share the query rea.sql

[@vigneshn1121]

Good Day Prashant,
Thank you so much for this explanation.
I need to know that, How we should track session details(start and end time, elapsed time, still how much rows pending to complete the session). Kindly suggest.

[@helpwithramesh]

Hi brother very excellent explain and one suggestion Please share the documents if possible thanks .

[@harelex07]

Good one !

[@thefatdba]

Thanks mate. Stay tuned for all upcoming videos

[@Mrpro119]

Hello Prashant , Really very good video and you explaining very well . if possible could you please share code snippet which you are using .

[@Farhan640]

Great video

[@thefatdba]

Thanks for the visit

[@mayankbaliyan5441]

Excellent video Prashant , please keep it up for all of us . Can I have those 1.sql and 2.sql scripts from you ?

[@thefatdba]

Sure, please share me your email

[@thefatdba]

Files used: github.com/fatdba/Oracle-Database-Scripts/blob/main/sqlflip1.sql (1.sql) and github.com/fatdba/Oracle-Database-Scripts/blob/main/sqlflip2.sql (2.sql)

[@mukkamalamahendra]

@@thefatdba Thanks a lot

[@navdeepbhatia5030]

thanks SIr very nicely explained , kindly share rea.sql as well

[@deepjitsaha7532]

Hi Prashant, could you please tell me how to release PHV value from sql ID after lock PHV value. It will be really helpfull for me if you can.

[@PKBIJ82]

After creating custom profile , still SQL is picking bad execution plan , how can I fix then, please suggest.

[@satendrarajput8120]

Thanks Sir, very good explanation for sql_id, I am getting issue to execute second query
ORA-01476: divisor is equal to zero

[@thefatdba]

Then you have more than 4 or 5 PHVs

[@thefatdba]

You can go and use DBA _HIST_SQL_STATS view to get same details

[@thefatdba]

Or try
easyoradba.com/2013/08/23/ora-01476-divisor-is-equal-to-zero/?amp=1

[@ravinbharathmaharaj9269]

How can you identify a ‘stale’ sql profile ?

[@thefatdba]

Thanks Ravin for watching the video and for your comment, and thats a great and a valid question as that can happen to a SQL prflofile. But I guess its not possible to answer it here, so I am pasting a link of asktom where same question was answer by Chris Saxon
asktom.oracle.com/pls/apex/asktom.search?tag=sql-profile-stale

[@thefatdba]

Maybe I have found my next blog topic to test and showcase.

[@faruqansari3342]

its possible to share doc

[@thefatdba]

Hi buddy,
Please check description of the video for scripts used in video

[@VenkyPotla369]

@Prashanth could you please share the rea.sql script

[@thefatdba]

Though all of them are available on my GitHub public repo, which in particular you are looking for and I will dhare the direct link

[@ramkumarsharma5490]

rea.sql Kindly share

[@palagirikrishna2719]

Sir.. could you please send @coe_xfr_sql_profile.sql script also..Prashant sir.. i need one more help sir... what is the sql profile and what use sql profile.. could you please explain please

[@rafiabbasi1]

you will get this script when you install SQLT ,

[@thefatdba]

Its inside SQLT package, download and you will find inside sqls folder