Oracle SQL Plan Management (SPM) from AWR !!!
How to Generate an AWR Report and Create Baselines (Doc ID 748642.1)
How to Create A SQL Plan Baseline From A Historical Execution Plan In The Automatic Workload Repository (AWR) [RDBMS Version 12.2 or Higher] (Doc ID 2885167.1)
Oracle version=>12.2
SQL> def
DEFINE _DATE = "12-NOV-22" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "rac1" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1202000100" (CHAR)
=========================================================
define sql_id='8cnh50qfgwg73' ----- For this sql !!!!
set lines 1000 pages 9999
col instance_number FOR 9999 HEA 'Inst'
col end_time HEA 'End Time'
col plan_hash_value HEA 'Plan|Hash Value'
col rows_per_exec HEA 'Rows Per Exec'
col et_secs_per_exec HEA 'Elap Secs|Per Exec'
col cpu_secs_per_exec HEA 'CPU Secs|Per Exec'
col io_secs_per_exec HEA 'IO Secs|Per Exec'
col cl_secs_per_exec HEA 'Clus Secs|Per Exec'
col ap_secs_per_exec HEA 'App Secs|Per Exec'
col cc_secs_per_exec HEA 'Conc Secs|Per Exec'
col pl_secs_per_exec HEA 'PLSQL Secs|Per Exec'
col ja_secs_per_exec HEA 'Java Secs|Per Exec'
col executions_total FOR 999,999 HEA 'Execs|Total'
col PARSING_SCHEMA_NAME for a20
select 'gv$dba_hist_sqlstat' source,h.snap_id,h.instance_number,
h.CON_ID,
to_char(CAST(s.begin_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') snap_time,
to_char(CAST(s.end_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') end_time,
h.sql_id,
h.plan_hash_value,
h.executions_total,
h.PARSING_SCHEMA_NAME,
to_char(ROUND(h.rows_processed_total / h.executions_total), '999,999,999,999') rows_per_exec,
to_char(ROUND(h.elapsed_time_total / h.executions_total / 1e6, 3), '999,990.000') et_secs_per_exec,
to_char(ROUND(h.cpu_time_total / h.executions_total / 1e6, 3), '999,990.000') cpu_secs_per_exec,
to_char(ROUND(h.iowait_total / h.executions_total / 1e6, 3), '999,990.000') io_secs_per_exec,
to_char(ROUND(h.clwait_total / h.executions_total / 1e6, 3), '999,990.000') cl_secs_per_exec,
to_char(ROUND(h.apwait_total / h.executions_total / 1e6, 3), '999,990.000') ap_secs_per_exec,
to_char(ROUND(h.ccwait_total / h.executions_total / 1e6, 3), '999,990.000') cc_secs_per_exec,
to_char(ROUND(h.plsexec_time_total / h.executions_total / 1e6, 3), '999,990.000') pl_secs_per_exec,
to_char(ROUND(h.javexec_time_total / h.executions_total / 1e6, 3), '999,990.000') ja_secs_per_exec
FROM dba_hist_sqlstat h,dba_hist_snapshot s
WHERE 1=1
and h.sql_id = '&sql_id'
AND h.executions_total > 0
AND s.snap_id = h.snap_id
AND s.dbid = h.dbid
AND s.instance_number = h.instance_number
--and PARSING_SCHEMA_NAME!='SYS'
;
Plan Execs Elap Secs CPU Secs IO Secs Clus Secs App Secs Conc Secs PLSQL Secs Java Secs
SOURCE SNAP_ID Inst CON_ID SNAP_TIME End Time SQL_ID Hash Value Total PARSING_SCHEMA_NAME Rows Per Exec Per Exec Per Exec Per Exec Per Exec Per Exec Per Exec Per Exec Per Exec
------------------- ---------- ----- ---------- ---------------- ---------------- ------------- ---------- -------- -------------------- ---------------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------
gv$dba_hist_sqlstat 9720 1 0 09-11-2022 20:00 09-11-2022 21:00 8cnh50qfgwg73 2772691065 17,496 SVCDBEM7ADM 1 1.990 0.638 1.227 0.417 0.000 0.000 0.000 0.000
gv$dba_hist_sqlstat 9748 1 0 11-11-2022 00:00 11-11-2022 01:00 8cnh50qfgwg73 2772691065 10,368 SVCDBEM7ADM 1 1.907 0.634 1.147 0.417 0.000 0.000 0.000 0.000
gv$dba_hist_sqlstat 9770 1 0 11-11-2022 22:00 11-11-2022 23:00 8cnh50qfgwg73 2772691065 384 SVCDBEM7ADM 1 1.910 0.648 1.148 0.408 0.000 0.000 0.000 0.000
gv$dba_hist_sqlstat 9578 1 0 03-11-2022 23:00 04-11-2022 00:00 8cnh50qfgwg73 2772691065 9,984 SVCDBEM7ADM 1 1.913 0.633 1.155 0.415 0.000 0.000 0.000 0.000
gv$dba_hist_sqlstat 9633 1 0 06-11-2022 05:00 06-11-2022 06:00 8cnh50qfgwg73 2772691065 3,840 SVCDBEM7ADM 1 1.874 0.634 1.127 0.402 0.000 0.000 0.000 0.000
gv$dba_hist_sqlstat 9640 1 0 06-11-2022 12:00 06-11-2022 13:00 8cnh50qfgwg73 2772691065 6,528 SVCDBEM7ADM 1 1.867 0.639 1.114 0.405 0.000 0.000 0.000 0.000
gv$dba_hist_sqlstat 9738 1 0 10-11-2022 14:00 10-11-2022 15:00 8cnh50qfgwg73 2772691065 6,528 SVCDBEM7ADM 1 1.924 0.633 1.165 0.416 0.000 0.000 0.000 0.000
gv$dba_hist_sqlstat 9745 1 0 10-11-2022 21:00 10-11-2022 22:00 8cnh50qfgwg73 2772691065 9,216 SVCDBEM7ADM 1 1.907 0.631 1.149 0.417 0.000 0.000 0.000 0.000
or
define sql_id='8cnh50qfgwg73' ----- For this sql !!!!
SET LINESIZE 2000 PAGESIZE 20000
SET LONG 99999
select
SNAP_ID,
TO_CHAR(begin_interval_time,'DD-MON-YYYY HH24') begin_interval_time,
TO_CHAR(end_interval_time,'DD-MON-YYYY HH24') end_interval_time,
SQL_ID,
round((round((avg(ELAPSED_TIME_DELTA)/1000000),2)/(case when avg(EXECUTIONS_DELTA)=0 then 1 else avg(EXECUTIONS_DELTA) end)),2) as ELAPSED_TIME_SECS,
round((round((avg(CPU_TIME_DELTA)/1000000),2)/(case when avg(EXECUTIONS_DELTA)=0 then 1 else avg(EXECUTIONS_DELTA) end)),2) as CPU_TIME_SECS,
round((avg(BUFFER_GETS_DELTA)/(case when avg(EXECUTIONS_DELTA)=0 then 1 else avg(EXECUTIONS_DELTA) end)),2) as GETS_PER_EXEC
from dba_hist_snapshot natural join dba_hist_sqlstat natural join dba_hist_sqltext
where
(elapsed_time_delta > 0 or elapsed_time_delta is not null)
and SQL_ID = '&sql_id'
group by
SNAP_ID,
TO_CHAR(begin_interval_time,'DD-MON-YYYY HH24'),
TO_CHAR(end_interval_time,'DD-MON-YYYY HH24'),
SQL_ID
order by snap_id asc
/
SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME SQL_ID ELAPSED_TIME_SECS CPU_TIME_SECS GETS_PER_EXEC
---------- ----------------------- ----------------------- -------------- ----------------- ------------- -------------
9574 03-NOV-2022 19 03-NOV-2022 20 8cnh50qfgwg73 1.86 .63 10988.01
9575 03-NOV-2022 20 03-NOV-2022 21 8cnh50qfgwg73 1.89 .63 10966.74
9576 03-NOV-2022 21 03-NOV-2022 22 8cnh50qfgwg73 1.85 .62 10978.95
9577 03-NOV-2022 22 03-NOV-2022 23 8cnh50qfgwg73 1.94 .66 10980.97
9578 03-NOV-2022 23 04-NOV-2022 00 8cnh50qfgwg73 1.9 .65 10982.64
9579 04-NOV-2022 00 04-NOV-2022 01 8cnh50qfgwg73 1.9 .65 10983.89
-- to check sql text !!!!
col sql_text for a50 wrap
select CON_ID,sql_text from dba_hist_sqltext
where 1=1
and sql_id ='&sql_id'
and rownum<3;
CON_ID SQL_TEXT
---------- --------------------------------------------------
0 SELECT NVL(SUM(BYTES),0) FROM SYS.DBA_FREE_SPACE W
HERE TABLESPACE_NAME = :B1
--- change the value for sql_id and plan_hash_value
variable x number
begin
:x := dbms_spm.load_plans_from_awr( begin_snap=>9738,end_snap=>9745,
basic_filter=>q'[ sql_id='8cnh50qfgwg73' and plan_hash_value='2772691065' ]' );
end;
/
print x
X
----------
1
set linesize 300
col CREATOR for a20
col PLAN_NAME for a30
col PARSING_SCHEMA_NAME for a25
col CREATED for a30
col ENABLED for a15
col ACCEPTED for a15
col REPRODUCED for a15
select SQL_HANDLE,PLAN_NAME,CREATOR,ORIGIN,PARSING_SCHEMA_NAME, ENABLED,ACCEPTED,REPRODUCED,CREATED
from dba_sql_plan_baselines
where 1=1
--and origin like 'MANUAL-LOAD%'
and CREATED >sysdate -1
-- and SQL_HANDLE='SQL_7a5adea0a1422e62'
order by created desc;
SQL_HANDLE PLAN_NAME CREATOR ORIGIN PARSING_SCHEMA_NAME ENABLED ACCEPTED REPRODUCED CREATED
------------------------------ ------------------------------ -------------------- ----------------------------- ------------------------- --------------- --------------- --------------- ------------------------------
SQL_bb8610f7a061889d SQL_PLAN_br1hhyyh6324xf1ad1f6a SYS MANUAL-LOAD-FROM-AWR SVCDBEM7ADM YES YES NO 12-NOV-22 03.14.49.000000 AM
or
col sql_id format a14
col sql_handle format a22
col plan_name format a32
col sql_text format a40
col ENABLED for a15
col ACCEPTED for a15
col SQL_TEXT for a40 wrap
select
DBMS_SQL_TRANSLATOR.SQL_ID(sql_text) as sql_id,
sql_handle ,
plan_name ,
enabled ,
accepted ,
SQL_TEXT
from dba_sql_plan_baselines
where DBMS_SQL_TRANSLATOR.SQL_ID(sql_text) = '8cnh50qfgwg73';
SQL_ID SQL_HANDLE PLAN_NAME ENABLED ACCEPTED SQL_TEXT
-------------- ---------------------- -------------------------------- --------------- --------------- ----------------------------------------
8cnh50qfgwg73 SQL_bb8610f7a061889d SQL_PLAN_br1hhyyh6324xf1ad1f6a YES YES SELECT NVL(SUM(BYTES),0) FROM SYS.DBA_FR
EE_SPACE WHERE TABLESPACE_NAME = :B1
====
with sql text !!!!
define sql_id='8cnh50qfgwg73'
set linesize 400
col CREATOR for a20
col PLAN_NAME for a30
col PARSING_SCHEMA_NAME for a25
col CREATED for a30
col ENABLED for a15
col ACCEPTED for a15
col REPRODUCED for a15
col SQL_TEXT for a50 wrap
select sql_id,plan_name,SQL_HANDLE,PLAN_NAME,CREATOR,ORIGIN,PARSING_SCHEMA_NAME, ENABLED,ACCEPTED,REPRODUCED,CREATED,substr(sa.SQL_TEXT,1,50) SQL_TEXT
from dba_sql_plan_baselines bl, dba_hist_sqltext sa
where DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(bl.sql_text) = DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sa.sql_text)
and sa.sql_id = '&sql_id'
and origin like 'MANUA%'
and created > sysdate -1;
SQL_ID PLAN_NAME SQL_HANDLE PLAN_NAME CREATOR ORIGIN PARSING_SCHEMA_NAME ENABLED ACCEPTEDREPRODUCED CREATED
------------- ------------------------------ ------------------------------ ------------------------------ -------------------- ----------------------------- ------------------------- --------------- --------------- --------------- ------------------------------
8cnh50qfgwg73 SQL_PLAN_br1hhyyh6324xf1ad1f6a SQL_bb8610f7a061889d SQL_PLAN_br1hhyyh6324xf1ad1f6a SYS MANUAL-LOAD-FROM-AWR SVCDBEM7ADM YES YES NO 12-NOV-22 03.14.49.000000 AM
-- to check
col PLAN_TABLE_OUTPUT for a120
select t2.*
from dba_sql_plan_baselines t1,
table( dbms_xplan.display_sql_plan_baseline(t1.sql_handle,t1.plan_name) ) t2
where 1=1
and SQL_HANDLE ='&SQL_HANDLE'
--and PLAN_NAME='SQL_PLAN_br1hhyyh6324xf1ad1f6a'
;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_bb8610f7a061889d
SQL text: SELECT NVL(SUM(BYTES),0) FROM SYS.DBA_FREE_SPACE WHERE
TABLESPACE_NAME = :B1
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_br1hhyyh6324xf1ad1f6a Plan id: 4054654826
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD-FROM-AWR
Plan rows: From dictionary
--------------------------------------------------------------------------------
Plan hash value: 2772691065
======
Check if possible!!!!!!
alter session set current_schema=SVCDBEM7ADM;
var B1 varchar2(10);
begin :B1 := 'abcdf'; end;
/
set autotrace traceonly explain
SELECT NVL(SUM(BYTES),0) FROM SYS.DBA_FREE_SPACE WHERE TABLESPACE_NAME = :B1
.
.
.
.
.
.
Note
-----
- SQL plan baseline "SQL_PLAN_br1hhyyh6324xf1ad1f6a" used for this statement <<<<<<<<<<<
- this is an adaptive plan
=======
2nd method
show parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean TRUE
define sql_id='87gaftwrm2h68'
col BEGIN_INTERVAL_TIME for a30
SELECT SS.SNAP_ID,
SS.INSTANCE_NUMBER,
BEGIN_INTERVAL_TIME,
SQL_ID,
PLAN_HASH_VALUE,OPTIMIZER_COST,
DISK_READS_TOTAL,
BUFFER_GETS_TOTAL,
ROWS_PROCESSED_TOTAL,
CPU_TIME_TOTAL,
ELAPSED_TIME_TOTAL,
IOWAIT_TOTAL,
NVL (EXECUTIONS_DELTA, 0) EXECS,
( ELAPSED_TIME_DELTA / DECODE (NVL (EXECUTIONS_DELTA, 0), 0, 1, EXECUTIONS_DELTA)) / 1000000 AVG_ETIME,
( BUFFER_GETS_DELTA / DECODE (NVL (BUFFER_GETS_DELTA, 0), 0, 1, EXECUTIONS_DELTA)) AVG_LIO
FROM DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
WHERE 1=1
and SQL_ID = '&SQL_ID'
AND SS.SNAP_ID = S.SNAP_ID
AND SS.INSTANCE_NUMBER = S.INSTANCE_NUMBER
AND EXECUTIONS_DELTA > 0
ORDER BY 1, 2, 3;
SNAP_ID INSTANCE_NUMBER BEGIN_INTERVAL_TIME SQL ID PLAN_HASH_VALUE OPTIMIZER_COST DISK_READS_TOTAL BUFFER_GETS_TOTAL ROWS_PROCESSED_TOTAL CPU_TIME_TOTAL ELAPSED_TIME_TOTAL IOWAIT_TOTAL EXECS AVG_ETIME AVG_LIO
---------- --------------- ------------------------------ ---------------- --------------- -------------- ---------------- ----------------- -------------------- -------------- ------------------ ------------ ---------- ---------- ----------
9819 1 13-NOV-22 11.00.22.179 PM 87gaftwrm2h68 1072382624 3 16 2861552 135012 67293418 67261553 1537478 4476 .000033331 2.02033065
9819 2 13-NOV-22 11.00.22.135 PM 87gaftwrm2h68 1072382624 3 26 3421346 112091 56183976 56243522 403516 6090 .000027044 2.06962233
9820 1 14-NOV-22 12.00.30.958 AM 87gaftwrm2h68 1072382624 3 16 2872967 135125 67518772 67434327 1538665 5650 .000030579 2.02035398
--Create STS.
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'STS_87gaftwrm2h68',
description => 'SQL Tuning Set for loading plan into SQL Plan Baseline');
END;
--Load STS
DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR
SELECT VALUE(P)
FROM TABLE(
dbms_sqltune.select_workload_repository(begin_snap=>9819, end_snap=>9820,basic_filter=>'sql_id = ''87gaftwrm2h68''',attribute_list=>'ALL')
) p;
DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'STS_87gaftwrm2h68', populate_cursor=>cur);
CLOSE cur;
END;
set linesize 2000
col SQL_TEXT for a50 wrap
col x for a200
SELECT
first_load_time ,
executions as execs ,
parsing_schema_name ,
elapsed_time / 1000000 as elapsed_time_secs ,
cpu_time / 1000000 as cpu_time_secs ,
buffer_gets ,
disk_reads ,
direct_writes ,
rows_processed ,
fetches ,
optimizer_cost ,
sql_plan ,
plan_hash_value ,
sql_id ,
sql_text
FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => 'STS_87gaftwrm2h68')
);
FIRST_LOAD_TIME EXECS PARSING_SCHEMA_NAME ELAPSED_TIME_SECS CPU_TIME_SECS BUFFER_GETS DISK_READS DIRECT_WRITES ROWS_PROCESSED FETCHES OPTIMIZER_COST
------------------- ---------- ------------------------- ----------------- ------------- ----------- ---------- ------------- -------------- ---------- --------------
SQL_PLAN(STATEMENT_ID, PLAN_ID, TIMESTAMP, REMARKS, OPERATION, OPTIONS, OBJECT_NODE, OBJECT_OWNER, OBJECT_NAME, OBJECT_ALIAS, OBJECT_INSTANCE, OBJECT_TYPE, OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID, DEPTH, POSITION, COST, CARDINALITY, BYTES, OTHER_TAG, PARTITION_START, PARTITION_STOP, PARTITION_ID, DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES, FILTER_PREDICATES, PROJECTION, TIME, QBLOCK_NAME, OTHER_XML)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PLAN_HASH_VALUE SQL ID SQL_TEXT
--------------- ---------------- --------------------------------------------------
12521 SYS .387451 .43431 25561 0 0 443 12521 3
SQL_PLAN_TABLE_TYPE(SQL_PLAN_ROW_TYPE(NULL, NULL, '02-JUN-17', NULL, 'SELECT STATEMENT', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'CHOOSE', 0, 0, NULL, 0, 3, 3, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL), SQL_PLAN_ROW_TYPE(NULL, NULL, '02-JUN-17', NULL, 'TABLE ACCESS', 'BY INDEX ROWID BATCHED', NULL, 'SYS', 'OBJ$', 'O@SEL$1', NULL, 'TABLE', NULL, 0, 1, 0, 1, 1, 3, 1, 107, NULL, NULL, NULL, NULL, NULL, 21954, 3, NULL, NULL, NULL, NULL, 1, 'SEL$1', '<other_xml><info type="db_version">12.2.0.1</info><info type="parse_schema"><
