Use SQL Baseline.. here are a couple of articles that describe the details and
examples for implementing them.
https://blogs.oracle.com/optimizer/post/what-is-the-difference-between-sql-profiles-and-sql-plan-baselines
What is the difference between SQL Profiles and SQL Plan Baselines? -
Oracle<https://blogs.oracle.com/optimizer/post/what-is-the-difference-between-sql-profiles-and-sql-plan-baselines>
I'm frequently asked about the difference between SQL profiles and SQL plan
baselines and why SQL profiles can be shared but SQL plan baselines can't. So I
thought it would be a good idea to write a post explaining the differences
between them and how they interact. But first let's briefly recap eac...
blogs.oracle.com
https://blogs.oracle.com/optimizer/post/using-sql-plan-management-to-control-sql-execution-plans
Using SQL Plan Management to Control SQL Execution
Plans<https://blogs.oracle.com/optimizer/post/using-sql-plan-management-to-control-sql-execution-plans>
SQL plan management (SPM) is designed to prevent performance regression for all
SQL statements used by an application (assuming that the SQL statements in
question are used more than once). SPM uses SQL plan baselines that are
associated with individual SQL statements to control what execution plans they
are permitted to use.
blogs.oracle.com
-Upendra
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Rajeev Prabhakar <rprabha01@xxxxxxxxx>
Sent: Wednesday, September 29, 2021 4:06 PM
To: Chrishna0007 <chrishna0007@xxxxxxxxx>
Cc: Oracle L <oracle-l@xxxxxxxxxxxxx>
Subject: Re: Plan changed After creating profile
Hi Krishna,
Besides other things, please also check if this
is the random case of data skew / due to usage
of bind variables. If yes, look into the option of
turning off peeking. At select/limited times,
depending upon the use case, using literals
instead might also be useful.
Rajeev
On Sep 29, 2021 at 3:27 PM, <Krishnaprasad
Yadav<mailto:chrishna0007@xxxxxxxxx>> wrote:
Hi All ,
Adding to my earlier mail ,
Below is details :
INST_ID PLAN_HASH_VALUE CHILD_NUMBER EXECUTIONS AVG_TIME
----------- --------------- ------------ ---------- ----------
1 1733635396<tel:1733635396> 3 208406 5.00887472
1 816394429<tel:816394429> 7 36bad plan