142 lines
4.4 KiB
Plaintext
Executable File
142 lines
4.4 KiB
Plaintext
Executable File
created: 20190623003755119
|
|
creator: vplesnila
|
|
modified: 20190906141112050
|
|
modifier: vplesnila
|
|
tags: Oracle
|
|
title: SQL Baseline from AWR
|
|
type: text/plain
|
|
|
|
-- Création d'un SQL baseline à partir du AWR
|
|
---------------------------------------------
|
|
|
|
|
|
-- Création d'un SQLSET
|
|
---------------------
|
|
BEGIN
|
|
DBMS_SQLTUNE.DROP_SQLSET(
|
|
sqlset_name => 'MySTS01');
|
|
END;
|
|
/
|
|
BEGIN
|
|
DBMS_SQLTUNE.CREATE_SQLSET(
|
|
sqlset_name => 'MySTS01',
|
|
description => 'SQL Tuning Set for loading plan into SQL Plan Baseline');
|
|
END;
|
|
/
|
|
|
|
|
|
-- Dans le SQLSET=MySTS01 créé précédemment, nous chargons l'historique du SQL_ID=d1khdngkga3nm
|
|
-- entre les snapshot AWR 50947 et 50951
|
|
DECLARE
|
|
cur sys_refcursor;
|
|
BEGIN
|
|
OPEN cur FOR
|
|
SELECT VALUE(P)
|
|
FROM TABLE(
|
|
dbms_sqltune.select_workload_repository(
|
|
begin_snap=>50947,
|
|
end_snap=>50951,
|
|
basic_filter=>'sql_id = ''d1khdngkga3nm''',
|
|
attribute_list=>'ALL')
|
|
) p;
|
|
DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'MySTS01', populate_cursor=>cur);
|
|
CLOSE cur;
|
|
END;
|
|
/
|
|
|
|
-- Pour voir ce que contient le SQLSET=MySTS01
|
|
-----------------------------------------------
|
|
SELECT
|
|
buffer_gets ,
|
|
optimizer_cost ,
|
|
plan_hash_value ,
|
|
sql_id
|
|
FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => 'MySTS01')
|
|
)
|
|
/
|
|
|
|
|
|
-- On crée un SQL Baseline à partir du plan_hash_value=473902782 du SQLSET=MySTS01
|
|
----------------------------------------------------------------------------------
|
|
DECLARE
|
|
my_plans pls_integer;
|
|
BEGIN
|
|
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
|
|
sqlset_name => 'MySTS01',
|
|
basic_filter=>'plan_hash_value = ''473902782'''
|
|
);
|
|
END;
|
|
/
|
|
|
|
|
|
-- Liste des SQL baseslines
|
|
----------------------------
|
|
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 fixer un SQL basesline
|
|
------------------------------
|
|
begin dbms_output.put_line(dbms_spm.ALTER_SQL_PLAN_BASELINE(plan_name=>'SQL_PLAN_8uy4magb69vtuf8f30e4c',attribute_name=>'fixed',attribute_value=>'yes'));
|
|
end;
|
|
/
|
|
|
|
|
|
-- Obtenir le PLAN_ID et le OUTLINE d'un SQL basesline
|
|
-- A partir de la SIGNATURE=9005682359107037619 du SQL Baseline, nous retrouvons 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 obtenir le OUTLINE du SQL Baseline, il nous faut connaître
|
|
-- la SIGNATURE=9005682359107037619 et le PLAN_ID=263533726
|
|
------------------------------------------------------------------
|
|
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;
|
|
|
|
|
|
|
|
-- Supression du SQL Baseline ayant le SQL_HANDLE=SQL_7cfa9c643693a9b3 et
|
|
-- le PLAN_NAME=SQL_PLAN_7tynwchv97admdbd90e8e
|
|
-------------------------------------------------------------------------
|
|
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;
|
|
/
|
|
|
|
|
|
-- Afficher le plan d'exécution d'un SQL basesline
|
|
-- Dans notre exemple, le SQL Baseline a le SQL_HANDLE=SQL_24c0db16ff852641
|
|
------------------------------------------------------------------------------
|
|
set lines 200 pages 0
|
|
select * from table(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('SQL_24c0db16ff852641')); |