Showing posts with label Plan stability. Show all posts
Showing posts with label Plan stability. Show all posts
Friday, December 4, 2020

How to mark an existing SQL Plan baseline as fixed

›
Thanks a lot to Tim Hall for providing this simple example: SET SERVEROUTPUT ON DECLARE l_plans_altered PLS_INTEGER; BEGIN l_plans_...
Wednesday, October 21, 2020

How to transfer SQL Profiles from one database to another

›
Following up from part 1 on my post on How to transfer SQL Plan Baselines from one database to another , I will now show you the steps to ...

How to transfer SQL Plan baselines from one database to another

›
A customer called with a request to transfer a specific SQL plan from production to test. The query was found in the shared pool: SELECT T...
Thursday, September 20, 2018

How to load a plan from the AWR into the SMB

›
To load a plan found in an AWR snapshot into the SMB, you need to add it to an STS first, then load it into the SMB. Here is how: 1. cre...
Thursday, March 15, 2018

How to load a plan from the cursor cache into the SMB

›
set serveroutput on VARIABLE cnt NUMBER -- Specifying both the SQL ID and the plan hash value: EXECUTE :cnt := DBMS_SPM.LOAD_PLANS_FROM_C...
Monday, December 18, 2017

How to make use of the GATHER_PLAN_STATISTICS hint

›
Use the gather_plan_statistics hint to view the optimizer's estimation of rows vs. the actual returned number of rows. Notice the cas...
1 comment:

How to use dbms_spm.load_plans_from_sqlset

›
To load a specific SQL ID with a specific plan hash value from an STS into your SQL Plan Management Base, use: set serveroutput on declare...
1 comment:
Tuesday, December 5, 2017

How to execute and capture an SQL statement in the SMB at the same time

›
An experienced co-worker showed me this neat trick to execute an SQL statement and capture its execution plan in the SQL Plan Management Ba...
Thursday, November 2, 2017

How to bulk load execution plans for a statement into the SMB

›
Short background: Customer were complaining about long execution times for a specific SQL. Checking in the database: select distinct sq...

What does it mean to "evolve" a SQL baseline?

›
Simply put, "evolving" an SQL baseline means testing whether or not an unaccepted plan performs better than any current plan in th...
Tuesday, September 2, 2014

How to convert stored outlines to use SQL Plan Baselines

›
If you have been using stored outlines for plan stability in versions prior to Oracle 11g, you should migrate them to use SQL Plan Baseline...