Home » RDBMS Server » Server Administration » creating a sql plan baseline throws ORA-13846: Cannot create SQL plan baseline on the given plan (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0)
creating a sql plan baseline throws ORA-13846: Cannot create SQL plan baseline on the given plan [message #685273] Wed, 01 December 2021 11:00 Go to next message
wtolentino
Messages: 354
Registered: March 2005
Senior Member
i am attempting to create a SQL plan baseline based on the recommendation from the SQL advisor.


-------------------------------------------------------------------------------                                                             
FINDINGS SECTION (1 finding)                                                                                                                
-------------------------------------------------------------------------------                                                             
                                                                                                                                            
1- Alternative Plan Finding                                                                                                                 
---------------------------                                                                                                                 
  Some alternative execution plans for this statement were found by searching                                                               
  the system's real-time and historical performance data.                                                                                   
                                                                                                                                            
  The following table lists these plans ranked by their average elapsed time.                                                               
  See section "ALTERNATIVE PLANS SECTION" for detailed information on each                                                                  
  plan.                                                                                                                                     
                                                                                                                                            
  id plan hash  last seen            elapsed (s)  origin          note                                                                      
  -- ---------- -------------------- ------------ --------------- ----------------                                                          
   1 2675695437  2021-11-29/08:00:23        8.068 AWR             original plan                                                             
   2 2201765684  2021-12-01/10:36:12      303.150 Cursor Cache                                                                              
                                                                                                                                            
  Information                                                                                                                               
  -----------                                                                                                                               
  - The Original Plan appears to have the best performance, based on the                                                                    
    elapsed time per execution.  However, if you know that one alternative                                                                  
    plan is better than the Original Plan, you can create a SQL plan baseline                                                               
    for it. This will instruct the Oracle optimizer to pick it over any other                                                               
    choices in the future.                                                                                                                  
    execute dbms_sqltune.create_sql_plan_baseline(task_name => 'TASK_265886',                                                               
            owner_name => 'WTOLENTINO', plan_hash_value => xxxxxxxx);  

i choose the first option with the plan hash 2675695437 and executed this command:
SQL> execute dbms_sqltune.create_sql_plan_baseline(task_name => 'TASK_265886', owner_name => 'WTOLENTINO', plan_hash_value => '2675695437');

then it throws an error:
ERROR at line 1:
ORA-13846: Cannot create SQL plan baseline on the given plan
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 19154
ORA-06512: at "SYS.DBMS_SQLTUNE", line 11150
ORA-06512: at line 1
was it only applicable to the non-original plan? please advise thank you.
Re: creating a sql plan baseline throws ORA-13846: Cannot create SQL plan baseline on the given plan [message #685274 is a reply to message #685273] Wed, 01 December 2021 12:39 Go to previous message
Michel Cadot
Messages: 68259
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

ORA-13846: Cannot create SQL plan baseline on the given plan
 *Cause: There are either multiple plans exist for the given value or the
         plan is not reproducible.
 *Action: Call Oracle Support.

[Updated on: Wed, 01 December 2021 12:40]

Report message to a moderator

Previous Topic: ORA-30928: Connect by filtering phase runs out of temp tablespace
Next Topic: message XDB initialized appeared multiple time in alert log (merged)
Goto Forum:
  


Current Time: Sun Aug 14 10:51:10 CDT 2022