Re: Plan changed After creating profile

From: Upendra nerilla <nupendra_at_hotmail.com>
Date: Wed, 29 Sep 2021 20:24:04 +0000
Message-ID: <BN6PR13MB1265D5512050A60CA78964C8D8A99_at_BN6PR13MB1265.namprd13.prod.outlook.com>

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_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Rajeev Prabhakar <rprabha01_at_gmail.com> Sent: Wednesday, September 29, 2021 4:06 PM To: Chrishna0007 <chrishna0007_at_gmail.com> Cc: Oracle L <oracle-l_at_freelists.org> 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_at_gmail.com>> 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 >>>>>>>> bad plan
         1        816394429<tel:816394429>            7         36 .043790694<tel:043790694>
         2        816394429<tel:816394429>            0       1020 .005087153<tel:005087153>
         2        816394429<tel:816394429>            1     764142 .001283816<tel:001283816>



  INST_ID  PLAN_HASH_VALUE EXECUTIONS   AVG_TIME        AVG_LIO    AVG_ROW SQL_PROFIL


----------  --------------- ---------- ---------- -------------- ---------- ----------


         1       1733635396<tel:1733635396>     208442 5.01300161        5,565.1             2.44969344 SYS_SQLPRF_014d7f1895d0000

         2        816394429<tel:816394429>     765162 .001288886            9.3<tel:001288886%209.3> 2.32738949 SYS_SQLPRO F_014d7f18a95d0000

SQL Profile was created in 2015 and it is enabled in state , however today , sql was picking bad plan from good plan suddenly and creating huge mess up

If any light on this can be shared would be great .

Regards,
Krishna

On Thu, 30 Sept 2021 at 00:44, Krishnaprasad Yadav <chrishna0007_at_gmail.com<mailto:chrishna0007_at_gmail.com>> wrote: Hi All ,

We have created sql profile on select statement ,but we see plan is changed and worst plan is used in execution rather than sticking to plan for which fixed it with profile .

Is any way to avoid it or how can we stick permanently so that it can be fixed with permanent plan .

Regards,
Krishna

--

http://www.freelists.org/webpage/oracle-l Received on Wed Sep 29 2021 - 22:24:04 CEST