====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 ====
* https://blogs.oracle.com/optimizer/entry/sql_plan_management_part_3_of_4_evolving_sql_plan_baselines_1
* http://oracle-base.com/articles/11g/sql-plan-management-11gr1.php
* https://blogs.oracle.com/optimizer/entry/what_is_the_different_between
* https://blogs.oracle.com/optimizer/entry/sql_plan_management_part_1_of_4_creating_sql_plan_baselines