77 lines
2.6 KiB
Plaintext
Executable File
77 lines
2.6 KiB
Plaintext
Executable File
-- Creation du SQL baseline à partir d'un plan dans le library cache
|
|
-- et injection dans un sql_text spécifique
|
|
|
|
-- Dans l'exemple suivant, nous créons un SQL Baseline pour le SQL ayant le texte du SQL_ID=81qv4d7vkb571,
|
|
-- à partir d'un plan dans le library cache: SQL_ID=chhkmc32mdkak, PLAN_HASH_VALUE=2494645258
|
|
|
|
|
|
set serveroutput ON
|
|
declare
|
|
sqltext_without_hint clob;
|
|
ret pls_integer;
|
|
begin
|
|
select SQL_FULLTEXT into sqltext_without_hint from V$SQL where sql_id = '81qv4d7vkb571' and CHILD_NUMBER=0;
|
|
ret :=
|
|
DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
|
|
SQL_ID =>'chhkmc32mdkak',
|
|
PLAN_HASH_VALUE => 2494645258,
|
|
SQL_TEXT => sqltext_without_hint);
|
|
dbms_output.put_line(ret || ' SQL plan baseline(s) created');
|
|
end;
|
|
/
|
|
|
|
|
|
-- Liste des SQL baseslines, par contre, il n'y a pas le plan_id
|
|
-----------------------------------------------------------------
|
|
set lines 180 pages 999
|
|
|
|
col created for a20 trunc
|
|
col signature for 9999999999999999999
|
|
|
|
select signature, sql_handle, plan_name, enabled, accepted, fixed, origin,created
|
|
from dba_sql_plan_baselines;
|
|
|
|
|
|
-- Pour avoir le plan_id
|
|
------------------------
|
|
SELECT TO_CHAR(so.signature) signature
|
|
, so.plan_id
|
|
, DECODE(ad.origin, 1, 'MANUAL-LOAD',
|
|
2, 'AUTO-CAPTURE',
|
|
3, 'MANUAL-SQLTUNE',
|
|
4, 'AUTO-SQLTUNE',
|
|
5, 'STORED-OUTLINE',
|
|
'UNKNOWN') origin
|
|
, DECODE(BITAND(so.flags, 1), 1, 'YES', 'NO') enabled
|
|
, DECODE(BITAND(so.flags, 2), 2, 'YES', 'NO') accepted
|
|
, DECODE(BITAND(so.flags, 64), 64, 'NO', 'YES') reproduced
|
|
FROM sys.sqlobj$ so
|
|
, sys.sqlobj$auxdata ad
|
|
WHERE ad.signature = so.signature
|
|
AND ad.plan_id = so.plan_id
|
|
AND so.signature = 9005682359107037619;
|
|
|
|
|
|
-- Pour avoir le OUTLINE du plan_id
|
|
-----------------------------------
|
|
select cast(extractvalue(value(x), '/hint') as varchar2(500)) as outline_hints
|
|
from xmltable('/outline_data/hint'
|
|
passing (select xmltype(comp_data) xml
|
|
from sys.sqlobj$data
|
|
where signature = 9005682359107037619
|
|
and plan_id = 263533726)) x;
|
|
|
|
|
|
-- Drop d'un SQL baseline
|
|
-------------------------
|
|
set serveroutput ON
|
|
DECLARE
|
|
v_dropped_plans number;
|
|
BEGIN
|
|
v_dropped_plans := DBMS_SPM.DROP_SQL_PLAN_BASELINE (
|
|
sql_handle => 'SQL_7cfa9c643693a9b3',
|
|
plan_name=>'SQL_PLAN_7tynwchv97admdbd90e8e'
|
|
);
|
|
DBMS_OUTPUT.PUT_LINE('dropped ' || v_dropped_plans || ' plans');
|
|
END;
|
|
/ |