Showing posts with label SQL Plan Management. Show all posts
Showing posts with label SQL Plan Management. 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...

How to drop an STS

›
exec dbms_sqltune.drop_sqlset ('mysts');
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 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...
Tuesday, November 14, 2017

What is the Adaptive SQL Plan Management introduced in Oracle 12c?

›
By "Adaptive SQL Plan Management", Oracle is referring to a new autotask that is executed every night during the maintenance win...
1 comment:
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...
Wednesday, November 19, 2014

How to increase the SMB space budget

›
When the message below shows up in the database alert.log, you should increase the SMB space budget. Message: SPM: SMB space usage (13523...
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...
Thursday, June 19, 2014

How to disable or drop an SQL Profile

›
Find the profiles currently saved in the database: SELECT NAME, CREATED,LAST_MODIFIED,TYPE,STATUS,FORCE_MATCHING FROM DBA_SQL_PROFILES ORD...
Friday, November 8, 2013

How to drop a plan from the SMB

›
Find the SQL Plan Baselines name and sql handle: SELECT SQL_HANDLE,PLAN_NAME FROM DBA_SQL_PLAN_BASELINES WHERE PLAN_NAME ='SQL_...
Wednesday, November 6, 2013

How to use dbms_xplan.display_sql_plan_baseline to reveal execution plan for a SQL in the SMB

›
1. Find the plan_name: SELECT sql_text,plan_name FROM DBA_SQL_PLAN_BASELINES WHERE sql_text LIKE '% 2. Use the dbms_xplan pack...

How to find the plan for a particular SQL in the SMB

›
SELECT sql_text,plan_name,sql_handle,creator,origin,created, last_executed,enabled,accepted,fixed,autopurge,module FROM DBA_SQL_PLAN_BASE...

How to manually load an execution plan for an SQL statement into the SMB

›
Facts: * Your customer has a query against a couple of tables with very volatile contents * The problem is simply solved by analyzing the...