2] Plan Stability using DBMS_SPM
New Feature to ensure that the
Elapsed time of a new Execution Plan won’t be worse than actual Execution Plan.
Many possibilities when we
deal with Execution Plan to fix it to a good one :
[ 2.1 ] We already have a
correct Plan In-Memory and we don’t want the Optimizer to change it anymore.
[ 2.2 ] A correct Plan exists
In-Memory, but for another SQL_ID.
[ 3 ] We have a good Plan for
the SQL_ID in the Past in the Workload Repository
3.1 in the same DB
3.2 and possibly in another Database !
Every Procedure are ran
connected AS SYSDBA :
DBMS_SQLTUNE.CREATE_SQLSET
DBMS_SQLTUNE.create_stgtab_sqlset
DBMS_SQLTUNE.pack_stgtab_sqlset
DBMS_SQLTUNE.unpack_stgtab_sqlset
2.1 Create an SQL Plan
Baseline for a given SQL_ID from a Plan in Memory
[ DBMS_SPM.load_plans_from_cursor_cache
]
select first_load_time,sql_id, plan_hash_value,
HASH_VALUE, CHILD_NUMBER from v$sql where sql_id = 'cy9s9a39198u3'
2021-08-05/08:29:33 cy9s9a39198u3 1327504843 -> Good
set serveroutput on
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
sql_id => 'cy9s9a39198u3');
END;
/
If needed, Remove SQL_ID from
Memory to get new SPM Plan
select address , HASH_VALUE from v$sql where
sql_id = 'cy9s9a39198u3'
07000102C9A190D0 3524567875
exec DBMS_SHARED_POOL.PURGE
('07000102C9A190D0,3524567875','C');
Check for SPM Plan
SELECT sql_handle, plan_name, enabled, accepted
, origin, created , last_executed
FROM dba_sql_plan_baselines
SQL_6de9e1edf73dcb7c SQL_PLAN_6vug1xrvmvkvw3c76d2c9 YES
YES MANUAL-LOAD 05/08/2021 08:43:28,000000 05/08/2021 08:47:55,000000 --> OK
2.2 Create an SQL Plan
Baseline from a good Plan of another SQL_ID in Memory
[ DBMS_SPM.load_plans_from_cursor_cache
DBMS_SPM.DROP_SQL_PLAN_BASELINE ]
- SQL_ID with wrong Plan = 1hygduv1g12wm
SELECT sql_handle, plan_name, enabled, accepted , origin, created , last_executed, sql_text FROM dba_sql_plan_baselines order by created
SQL_621c9bd73edc6464 SQL_PLAN_6474vuwzdst347f17ff1d YES YES
MANUAL-LOAD-FROM-STS 15/03/2022
11:26:50,562621 15/03/2022
11:28:03,000000 (HUGECLOB)
- SQL_ID : fp4wf6h10698b with Good Plan : 3150554415
This SQL_ID is already fixed with a SPM, but it doesn’t need to be. If it is not already fixed by SPM, create the SPM as first step :
- Create
an SPM for the SQL_ID with Wrong Plan
set serveroutput on
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded :=
DBMS_SPM.load_plans_from_cursor_cache(
sql_id => '1hygduv1g12wm');
END;
/
Get values from newly created SPM :
SELECT sql_handle, plan_name, enabled, accepted , origin, created , last_executed, sql_text FROM dba_sql_plan_baselines order by created
- Put SPM with Correct Plan into SPM with Wrong Plan :
SET SERVEROUTPUT ON
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded :=
DBMS_SPM.load_plans_from_cursor_cache(
sql_id => '<SQL_ID_with_Correct_Plan>',
plan_hash_value => '<Correct_Plan>',
sql_handle => 'SQL Handle from SPM of
the SQL_ID with Wrong Plan');
DBMS_OUTPUT.put_line('Plans Loaded: ' ||
l_plans_loaded);
END;
/
Ex:
SET SERVEROUTPUT ON
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded :=
DBMS_SPM.load_plans_from_cursor_cache(
sql_id => 'fp4wf6h10698b',
plan_hash_value => '3150554415',
sql_handle => 'SQL_03b57094971cdecb');
DBMS_OUTPUT.put_line('Plans Loaded: ' ||
l_plans_loaded);
END;
/
A new SPM is created.
SELECT sql_handle, plan_name, enabled, accepted , origin, created , last_executed, sql_text FROM dba_sql_plan_baselines order by created
SQL_621c9bd73edc6464 SQL_PLAN_6474vuwzdst347f17ff1d YES YES MANUAL-LOAD-FROM-STS 15/03/2022 11:26:50,562621 15/03/2022 11:28:03,000000 (HUGECLOB)
---> SQL_ID with Good Plan
SQL_03b57094971cdecb SQL_PLAN_07dbhkkbjtrqb7f17ff1d YES YES MANUAL-LOAD-FROM-CURSOR-CACHE 15/03/2022 11:50:37,871564 (HUGECLOB)
Then Drop the previous SPM with wrong Plan :
SET SERVEROUTPUT ON
declare
drop_result pls_integer;
begin
drop_result :=
DBMS_SPM.DROP_SQL_PLAN_BASELINE(
sql_handle =>
'SQL_03b57094971cdecb',
plan_name =>
'SQL_PLAN_07dbhkkbjtrqbc4ac00a5');
dbms_output.put_line(drop_result);
end;
/
If needed, Purge Wrong Plan
from Memory to allow SPM to be chosen
select address , HASH_VALUE from v$sql where sql_id = '1hygduv1g12wm '
0700010012D7AEE0 1252826228
exec DBMS_SHARED_POOL.PURGE ('0700010012D7AEE0,1252826228','C');
SELECT sql_handle, plan_name, enabled, accepted , origin, created , last_executed, sql_text FROM dba_sql_plan_baselines order by created
SQL_621c9bd73edc6464 SQL_PLAN_6474vuwzdst347f17ff1d YES YES MANUAL-LOAD-FROM-STS 15/03/2022 11:26:50,562621 15/03/2022 11:28:03,000000 (HUGECLOB)
SQL_03b57094971cdecb SQL_PLAN_07dbhkkbjtrqb7f17ff1d YES YES MANUAL-LOAD-FROM-CURSOR-CACHE 15/03/2022 11:50:37,871564 15/03/2022 11:54:47,000000 (HUGECLOB)
SQL_03b57094971cdecb SQL_PLAN_07dbhkkbjtrqb3ab19b62 YES NO AUTO-CAPTURE 15/03/2022 11:54:47,426884 (HUGECLOB)
Check the execution of SQL_ID :
select sql_id , plan_hash_value , parsing_schema_name , executions , ceil(buffer_gets/executions) ,
disk_reads , first_load_time , rows_processed ,
ceil(elapsed_time/1000000/60) Min , last_load_time , sql_text from v$sql
where sql_id = '1hygduv1g12wm'
OK < 1000 blocks
3.1 Create an SQL Plan
Baseline for a given SQL_ID from a past Plan in AWR
DBMS_SQLTUNE.CREATE_SQLSET
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY
dbms_spm.load_plans_from_sqlset
Note, new feature is not
working :
With 12cR2, SPM plan baseline from AWR
historical execution plans can be loaded, using DBMS_SPM.LOAD_PLANS_FROM_AWR.
variable x number
begin
:x := dbms_spm.load_plans_from_awr(
begin_snap=>18416,end_snap=>18417,
basic_filter=>'sql_id = ''1hygduv1g12wm'' AND
plan_hash_value = 3150554415' );
end;
/
ERROR at line 1:
ORA-13769: Snapshots 18416 and 18417 do not
exist.
Too bad.
OK, let’s start, first,
identify which snam_id have good plan :
SQL_ID = 'fp4wf6h10698b'
SELECT sn.snap_id,
TO_CHAR(sn.begin_interval_time,'MM DD') "DAY", s.sql_id, plan_hash_value , module,
ROUND (sum(s.buffer_gets_delta) / 1000000, 2) "BG(M)",
ROUND (sum(s.disk_reads_delta) / 1000, 2) "DK(K)",
ROUND (sum(s.cpu_time_delta) / 1000000, 2) "CPU(s)",
ROUND (sum(s.elapsed_time_delta) / 1000000/60, 2) "ELAPSED(min)",
sum(s.fetches_delta) "FETCH",
sum(s.rows_processed_delta) "ROWS",
sum(s.executions_delta) "EXEC",
ROUND (sum(s.buffer_gets_delta) / DECODE (sum(s.executions_delta), 0, 1, sum(s.executions_delta))) "BG/EXEC",
ROUND (sum(s.disk_reads_delta) / DECODE (sum(s.executions_delta), 0, 1, sum(s.executions_delta))) "DK/EXEC",
ROUND (sum(s.cpu_time_delta) / DECODE (sum(s.executions_delta), 0, 1, sum(s.executions_delta)) / 1000000, 3 ) "CPU(s)/EXEC",
ROUND (sum(s.elapsed_time_delta) / DECODE (sum(s.executions_delta), 0, 1, sum(s.executions_delta)) / 1000000,3 ) "ELAPSED(s)/EXEC",
ROUND (sum(s.disk_reads_delta)/DECODE(sum(s.buffer_gets_delta),0,1,sum(s.buffer_gets_delta))*100, 2) "DK%BG",
ROUND (sum(s.cpu_time_delta)/DECODE(sum(s.elapsed_time_delta),0,1,sum(s.elapsed_time_delta))*100, 2) "CPU%ELAP"
FROM
dba_hist_sqlstat s,
dba_hist_snapshot sn
WHERE s.snap_id=sn.snap_id
AND sn.begin_interval_time
>= trunc(sysdate-60)
AND sql_id in ('fp4wf6h10698b')
GROUP BY sn.snap_id,TO_CHAR(sn.begin_interval_time,'MM DD'), s.sql_id, plan_hash_value , module
ORDER BY TO_CHAR(sn.begin_interval_time,'MM DD'), s.sql_id, plan_hash_value , module
Same day, 2 plans :
snap_id date sql_id plan hash value
BufferGets / exec
18575 03 15 fp4wf6h10698b 3150554415 768 à Good Plan
18576 03 15 fp4wf6h10698b 3150554415 978
18577 03 15 fp4wf6h10698b 1445784707 330818
In case, there is only one
line in AWR, plug DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(snap_id-1,
snap_id
Create SQL Tunig Set and
populate from AWR.
set serveroutput on
exec
DBMS_SQLTUNE.CREATE_SQLSET('SAS_fp4wf6h10698b');
declare
baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cursor for
select VALUE(p) from
table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(18575,18576,'sql_id='||CHR(39)||'fp4wf6h10698b'||CHR(39)||'
and plan_hash_value=3150554415',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET('SAS_fp4wf6h10698b',
baseline_ref_cursor);
end;
/
Double check the SQL Set :
SELECT NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET where name='SAS_fp4wf6h10698b';
select sqlset_name, sql_id, plan_hash_value from dba_sqlset_statements ;
SQLSET_NAME
SQL_ID PLAN_HASH_VALUE
------------------------------ ------------- ---------------
SAS_fp4wf6h10698b fp4wf6h10698b 3150554415
Then Load SPM.
SELECT
NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET where
name='SAS_fp4wf6h10698b';
select count(*) from
dba_sql_plan_baselines -- 0
set serveroutput on
declare
my_integer pls_integer;
begin
my_integer :=
dbms_spm.load_plans_from_sqlset(sqlset_name => 'SAS_fp4wf6h10698b',
sqlset_owner => 'SYS',
fixed => 'NO',
enabled => 'YES');
DBMS_OUTPUT.PUT_line('Number
of plans loaded: ' || my_integer);
end;
/
The output should be “1” plan loaded.
Purge Shared Pool from the
SQL_ID to get new SMP.
select 'exec DBMS_SHARED_POOL.PURGE
('''||ADDRESS||', '||HASH_VALUE||''', ''C'');' from V$SQLAREA where SQL_ID in
('fp4wf6h10698b')
exec DBMS_SHARED_POOL.PURGE
('0700010028942AF0, 3270544275', 'C');
3.2.1 Create an SQL Plan
Baseline for a given SQL_ID from a Plan in AWR of Another DB
Source DBMS_SQLTUNE.CREATE_SQLSET
Source DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY /
DBMS_SQLTUNE.LOAD_SQLSET
Source DBMS_SQLTUNE.create_stgtab_sqlset
Source DBMS_SQLTUNE.pack_stgtab_sqlset
expdp / impdp
Target DBMS_SQLTUNE.unpack_stgtab_sqlset
Target DBMS_SPM.load_plans_from_sqlset
Source DBMS_SQLTUNE.drop_sqlset
3.2.1.1 Populate the STS from
Source DB
Ø From a Plan found in an AWR Snap using
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY.
--This will create an empty
SQL Tuning set.
exec DBMS_SQLTUNE.CREATE_SQLSET('DTM_SCHEMA_MERGE');
declare
baseline_ref_cursor
DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cursor for
select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(42178,
42179,'sql_id='||CHR(39)||'d787swkmh37kg'||CHR(39)||' and
plan_hash_value=2371152508',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET('DTM_SCHEMA_MERGE',
baseline_ref_cursor);
end;
/
SELECT
NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET where name='DTM_SCHEMA_MERGE';
select count(*) from
dba_sql_plan_baselines -- 0
3.2.1.2 Transfer to target DB
Create stgtab
sqlset_tab table in DTM_SCHEMA schema:
BEGIN
DBMS_SQLTUNE.create_stgtab_sqlset(table_name
=> 'SQLSET_TAB', schema_name => 'DTM_SCHEMA', tablespace_name => 'USERS');
END;
/
Pack STS into
the stgtab From SYS
BEGIN
DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name
=> 'DTM_SCHEMA_MERGE', sqlset_owner => 'SYS', staging_table_name =>
'SQLSET_TAB', staging_schema_owner => 'DTM_SCHEMA');
END;
/
Transfer the table
containing the SQL SET (SQLSET_TAB) table to the TEST system
expdp
SYSTEM DIRECTORY=BI_DB_DP_DIR DUMPFILE=SQLSET_TAB.dmp TABLES=DTM_SCHEMA.SQLSET_TAB
BI_DB srv:/home/comp/oracle
> impdp system@PPR_BI_DB DIRECTORY=PPR_BI_DB_DP_DIR DUMPFILE=SQLSET_TAB.dmp
UNPACK THE
SQLSET_TAB TABLE IN THE TEST SYSTEM
BEGIN
DBMS_SQLTUNE.unpack_stgtab_sqlset(sqlset_name
=> 'DTM_SCHEMA_MERGE',
sqlset_owner => 'SYS',
replace => TRUE,
staging_table_name =>
'SQLSET_TAB',
staging_schema_owner => 'DTM_SCHEMA');
END;
/
Now the SQL
Tuning Set is transferred successfully to another database without any issues.
3.2.1.3 Load the Sql Plan
Baseline from STS
set
serveroutput on
declare
my_integer
pls_integer;
begin
my_integer
:= dbms_spm.load_plans_from_sqlset(sqlset_name => 'DTM_SCHEMA_MERGE',
sqlset_owner
=> 'SYS',
fixed => 'NO',
enabled => 'YES');
DBMS_OUTPUT.PUT_line(my_integer);
end;
/
select count(*) from
dba_sql_plan_baselines;
3.2.2 Another example of SPM
from AWR
Case
[ 3 ] I have a good Plan in AWR from another database, same SQL_ID.
Ex
:
SQL_ID 7934fsmpydkwa
Target Environment
PPR 3Hours 5000 exec. 270K BG / execution PHV: 3116998891 KO
Source Environment PRO
11Min. 5800 exec. 023K BG / execution PHV: 1688580575 OK
Connected
as SYSDBA
Source
: create SQLSet
Source
: Load Plan from AWR into SQLSet
Transport
SQLSet
Target
: load SQLSet
Source
Env : First create a SQLSet
SET
SERVEROUTPUT ON
exec
dbms_sqltune.create_sqlset(sqlset_name =>
'7934fsmpydkwa_sqlset_test',description => 'sqlset MYAPP SIMUMENS');
Get
snap_id from AWR
SELECT sn.snap_id,
TO_CHAR(sn.begin_interval_time,'MM DD') "DAY", s.sql_id,
plan_hash_value , module,
ROUND
(sum(s.buffer_gets_delta) / 1000000, 2) "BG(M)",
ROUND
(sum(s.disk_reads_delta) / 1000, 2)
"DK(K)",
ROUND
(sum(s.cpu_time_delta) / 1000000, 2)
"CPU(s)",
ROUND
(sum(s.elapsed_time_delta) / 1000000/60, 2)
"ELAPSED(min)",
sum(s.fetches_delta) "FETCH",
sum(s.rows_processed_delta) "ROWS",
sum(s.executions_delta) "EXEC",
ROUND
(sum(s.buffer_gets_delta) / DECODE (sum(s.executions_delta), 0, 1,
sum(s.executions_delta)))
"BG/EXEC",
ROUND
(sum(s.disk_reads_delta) / DECODE (sum(s.executions_delta), 0, 1,
sum(s.executions_delta)))
"DK/EXEC",
ROUND
(sum(s.cpu_time_delta) / DECODE (sum(s.executions_delta), 0, 1,
sum(s.executions_delta)) / 1000000, 3 )
"CPU(s)/EXEC",
ROUND
(sum(s.elapsed_time_delta) / DECODE (sum(s.executions_delta), 0, 1,
sum(s.executions_delta)) / 1000000,3 )
"ELAPSED(s)/EXEC",
ROUND (sum(s.disk_reads_delta)/DECODE(sum(s.buffer_gets_delta),0,1,sum(s.buffer_gets_delta))*100,
2) "DK%BG",
ROUND
(sum(s.cpu_time_delta)/DECODE(sum(s.elapsed_time_delta),0,1,sum(s.elapsed_time_delta))*100,
2) "CPU%ELAP"
FROM dba_hist_sqlstat s, dba_hist_snapshot
sn
WHERE s.snap_id=sn.snap_id
AND
sn.begin_interval_time >= trunc(sysdate-60)
AND sql_id in
('7934fsmpydkwa')
GROUP BY sn.snap_id,TO_CHAR(sn.begin_interval_time,'MM DD'), s.sql_id,
plan_hash_value , module
ORDER BY TO_CHAR(sn.begin_interval_time,'MM DD'), s.sql_id,
plan_hash_value , module
62651 12 09 7934fsmpydkwa 1688580575 SQL*Plus
12.35 0.5 22.2
1.07 536 536
536 23040 1
0.041 0.12 0
34.52
62652 12 09 7934fsmpydkwa 1688580575 SQL*Plus
93.93 12.22 166.45
7.34 3437 3437
3437 27330 4
0.048 0.128 0.01
37.8
62653 12 09 7934fsmpydkwa 1688580575 SQL*Plus
30.17 3.58 51.68
2.7 1837 1837
1837 16426 2
0.028 0.088 0.01
31.88
Or select
snap_id,sql_id,plan_hash_value from dba_hist_sqlstat where sql_id ='7934fsmpydkwa
' order by 1
Load
Plan into SQL_Set
declare
baseline_ref_cur
DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open
baseline_ref_cur for
select
VALUE(p) from table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(62651,
62653,'sql_id='||CHR(39)||'7934fsmpydkwa'||CHR(39)||' and
plan_hash_value=1688580575',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET('7934fsmpydkwa_sqlset_test',
baseline_ref_cur);
end;
/
display
SQL_Set
SELECT SQL_ID, PARSING_SCHEMA_NAME AS "SCH", SQL_TEXT,
ELAPSED_TIME AS "ELAPSED", BUFFER_GETS FROM TABLE( DBMS_SQLTUNE.SELECT_SQLSET(
'SQLT_WKLD_STS' ) );
Transport
SQLSet from one database to another one.
BEGIN
DBMS_SQLTUNE.CREATE_STGTAB_SQLSET
(table_name => 'MYAPP_SIMUMENS_7934', schema_name => 'ADB');
END;
/
BEGIN
DBMS_SQLTUNE.PACK_STGTAB_SQLSET
(
sqlset_name => '7934fsmpydkwa_sqlset_test',
sqlset_owner => 'SYS',
staging_table_name => 'MYAPP_SIMUMENS_7934',
staging_schema_owner => 'ADB'
);
END;
/
expdp system/$psw directory=DP_DIR tables=ADB.MYAPP_SIMUMENS_7934
dumpfile=MYAPP_SIMUMENS_7934fsmpydkwa.DMP logfile=e_MYAPP_SIMUMENS_7934fsmpydkwa.log
Target
DB
SYSDBA
impdp system/$psw directory=DP_DIR dumpfile=MYAPP_SIMUMENS_7934fsmpydkwa.DMP
logfile=i_MYAPP_SIMUMENS_7934fsmpydkwa.log
Unpack
staging table
set serveroutput on
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET
(
sqlset_name => '%'
, sqlset_owner => 'SYS'
, replace => true
, staging_table_name => 'MYAPP_SIMUMENS_7934'
, staging_schema_owner =>
'ADB');
END;
/
Load
SQL_Set to SPM
VARIABLE v_plan_cnt NUMBER
EXECUTE :v_plan_cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
-
sqlset_name =>
'7934fsmpydkwa_sqlset_test', -
sqlset_owner => 'SYS', -
basic_filter => 'sql_id =
''7934fsmpydkwa'' AND plan_hash_value = 1688580575' );
Check
SELECT sql_handle, plan_name, enabled, accepted
, origin, created , last_executed, sql_text
FROM dba_sql_plan_baselines
order by created
SQL_3ca35f91cc474453 SQL_PLAN_3t8uzk764fj2m711241d1 YES
YES MANUAL-LOAD 12/01/2022 15:06:09.000000 (HUGECLOB)
Flush
the two SQLs from the shared pool, so that the optimizer will pick the new plan
select 'exec DBMS_SHARED_POOL.PURGE ('''||ADDRESS||',
'||HASH_VALUE||''', ''C'');' from V$SQLAREA where SQL_ID in ('2t8gdjy0pbs57');
exec SYS.DBMS_SHARED_POOL.PURGE ('0000001006B396C8, 2113289046', 'C');
Clean
up Source
BEGIN
DBMS_SQLTUNE.drop_sqlset (sqlset_name =>
'7934fsmpydkwa_sqlset_test');
END;
/
2.4 SPM Administration
2.4.1 Drop an existing SPM Plan,
using SQL_HANDLE.
-- SELECT SQL_HANDLE,
SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE '%MERGE%';
set
serveroutput on
DECLARE
v_dropped_plans number;
BEGIN
v_dropped_plans := DBMS_SPM.DROP_SQL_PLAN_BASELINE (
sql_handle => 'SQL_3ad68510622ed12d'
);
DBMS_OUTPUT.PUT_LINE('dropped ' ||
v_dropped_plans || ' plans');
END;
/
dropped 1
plans
PL/SQL
procedure successfully completed.
2.4.2 Find if there is a Plan
Baseline for a given SQL_ID
Unfortunately, there is no
easy way to get the SQL_ID from DBA_SQL_PLAN_BASELINES, we have to use also
other Views.
From
an SQL_ID found in V$SQL
SELECT s.sql_id , b.sql_handle, b.plan_name, s.child_number, s.plan_hash_value, s.executions
FROM v$sql s,
dba_sql_plan_baselines b
WHERE s.exact_matching_signature = b.signature(+) AND s.sql_plan_baseline = b.plan_name(+)
AND s.sql_id='d787swkmh37kg' ;
From
an SQL_ID found in AWR History
-- From AWR
SELECT * -- sql_handle, plan_name
FROM dba_sql_plan_baselines
WHERE signature IN (
SELECT force_matching_signature FROM dba_hist_sqlstat s, dba_hist_snapshot sn
WHERE sql_id='d787swkmh37kg' and s.snap_id=sn.snap_id
AND
begin_interval_time >= TO_DATE('12/11/2015 08:30:00','DD/MM/YYYY HH24:MI:SS')
AND
begin_interval_time < TO_DATE('12/11/2015
22:30:00','DD/MM/YYYY
HH24:MI:SS')
)
So back to :
1] Outlines
|
DAY
|
SQL_ID
|
PLAN_HV
|
MODULE
|
BG(M)
|
DK(K)
|
CPU(s)
|
ELAPSED(min)
|
|
05 29
|
4m9p7qndv97gm
|
2852960567
|
SQL*Plus
|
4,76
|
27,06
|
35,42
|
0,72
|
|
06 07
|
4m9p7qndv97gm
|
250938196
|
SQL*Plus
|
6039,75
|
26,69
|
18143,84
|
297,38
|
|
FIRST_LOAD_TIME
|
SQL_ID
|
PLAN_HASH_VALUE
|
HASH_VALUE
|
CHILD_NUMBER
|
OUTLINE_CATEGORY
|
|
2012-06-07/15:25:40
|
4m9p7qndv97gm
|
2852960567
|
464821747
|
0
|
