{SQL計画管理}SPMで実行計画を進化

演習

 

https://docs.oracle.com/cd/F19136_01/arpls/DBMS_SPM.html#GUID-7C052FDA-4F12-4B20-8967-0010CC1C3E24

 

SELECT SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED ,origin, created, SQL_TEXT
FROM   DBA_SQL_PLAN_BASELINES
where sql_text like 'select count(*) from sh.sales where prod_id = 150';

 


alter session set optimizer_capture_sql_plan_baselines= true;

alter index sh.SALES_PROD_BIX invisible;

select count(*) from sh.sales where prod_id = 150;
select count(*) from sh.sales where prod_id = 150;

alter index sh.SALES_PROD_BIX visible;

select count(*) from sh.sales where prod_id = 150;
select count(*) from sh.sales where prod_id = 150;

alter session set optimizer_capture_sql_plan_baselines= false;


SELECT SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED ,origin, created, SQL_TEXT
FROM   DBA_SQL_PLAN_BASELINES
where sql_text like 'select count(*) from sh.sales where prod_id = 150';


--sysで実行

SET SERVEROUTPUT ON
SET LONG 10000
DECLARE
    report clob;
BEGIN
    report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
                  sql_handle => 'SQL_f554bd6a0ec06442');
    DBMS_OUTPUT.PUT_LINE(report);
END;
/