SQL Baseline - SPM - SQL Plan Management

In Arbeit

Die vorhandenen Baselines anzeigen

SET pagesize 1000
SET linesize 150
 
column SQL_TEXT format a23
column SQL_HANDLE format a20
column PARSING_SCHEMA_NAME format a14
column PLAN_NAME format a30
column ORIGIN format a16
column CREATED_TEXT format a18
column ENABLED format a3 heading "Ena|bld"
column ACCEPTED format a3 heading "Ac|ted"
 
 
select  SQL_HANDLE
		, replace(replace(substr(SQL_TEXT,1,20)||' ..',chr(10),''),'  ',' ') as SQL_TEXT
		, PARSING_SCHEMA_NAME
		, PLAN_NAME
		, ORIGIN
		, to_char(CREATED,'dd.mm.yyyy hh24:mi') as CREATED_TEXT
		, ENABLED
		, ACCEPTED
from DBA_SQL_PLAN_BASELINES
order by CREATED
/

Die Pläne in einer Baseline anzeigen

Mit der Format format ⇒'outline' lassen sich die Hints im Plan anzeigen: 

set long 10000
 
define SQL_BASELINE_PLAN=&1
 
prompt
prompt Parameter 1 = SQL_BASELINE_PLAN   => &&SQL_BASELINE_PLAN.
prompt
 
select *
  from table(dbms_xplan.display_sql_plan_baseline( plan_name=>'&&SQL_BASELINE_PLAN.'
                                                  ,format   =>'BASIC ROWS BYTES COST')
			   )
/
 
select *
  from table(dbms_xplan.display_sql_plan_baseline( plan_name=>'&&SQL_BASELINE_PLAN.'
                                                  ,format   =>'outline')
			   )
/

Quellen