Search This Blog

Total Pageviews

Saturday, 12 November 2022

Oracle SQL Plan Management (SPM) from AWR !!! 

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"><![C'), SQL_PLAN_ROW_TYPE(NULL, NULL, '02-JUN-17', NULL, 'INDEX', 'RANGE SCAN', NULL, 'SYS', 'I_OBJ1', 'O@SEL$1', NULL, 'INDEX (UNIQUE)', NULL, 1, 2, 1, 2, 1, 2, 1, NULL, NULL, NULL, NULL, NULL, NULL, 14443, 2, NULL, NULL, NULL, NULL, 1, 'SEL$1', NULL))
     1072382624 87gaftwrm2h68    select o.owner#,o.name,o.namespace,o.remoteowner,o
                                 .linkname,o.subname from obj$




DECLARE
my_plans pls_integer;
BEGIN
  my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
    sqlset_name => 'STS_87gaftwrm2h68', 
    basic_filter=>'plan_hash_value = ''1072382624'''
    );
END;






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 ,
       FIXED,
ORIGIN,
        SQL_TEXT 
from        dba_sql_plan_baselines
where     DBMS_SQL_TRANSLATOR.SQL_ID(sql_text) = '87gaftwrm2h68'
and origin   like 'MANUA%'
--and created   > sysdate -3
;




SQL ID         SQL_HANDLE             PLAN_NAME                        ENABLED         ACCEPTED        FIX ORIGIN                        SQL_TEXT
-------------- ---------------------- -------------------------------- --------------- --------------- --- ----------------------------- ----------------------------------------
87gaftwrm2h68  SQL_3e0587805c3ad254   SQL_PLAN_3w1c7h1f3pnkn77dddc30   YES             YES             NO  MANUAL-LOAD-FROM-STS          select o.owner#,o.name,o.namespace,o.rem
                                                                                                                                         oteowner,o.linkname,o.subname from obj$



============
3rd  method



define sql_id='3kqrku32p6sfn'  ----- 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       9656     1          0 07-11-2022 04:00 07-11-2022 05:00 3kqrku32p6sfn 1774581179       15 SYS                                 0        0.144        0.139        0.000        0.000       0.000         0.000        0.000        0.000
gv$dba_hist_sqlstat       9650     1          0 06-11-2022 22:00 06-11-2022 23:00 3kqrku32p6sfn 1774581179        3 SYS                                 0        0.125        0.121        0.002        0.000       0.000         0.000        0.000        0.000
gv$dba_hist_sqlstat       9651     1          0 06-11-2022 23:00 07-11-2022 00:00 3kqrku32p6sfn 1774581179        2 SYS                                 0        0.290        0.286        0.000        0.000       0.000         0.000        0.000        0.000
gv$dba_hist_sqlstat       9653     1          0 07-11-2022 01:00 07-11-2022 02:00 3kqrku32p6sfn 1774581179        3 SYS                                 0        0.165        0.160        0.000        0.000       0.000         0.000        0.000        0.000
gv$dba_hist_sqlstat       9655     1          0 07-11-2022 03:00 07-11-2022 04:00 3kqrku32p6sfn 1774581179       11 SYS                                 0        0.150        0.146        0.000        0.000       0.000         0.000        0.000        0.000







define v_sql_id='3kqrku32p6sfn'
define v_fixed='YES'
define v_enabled='YES'
define v_begin_snap=9650
define v_end_snap=9651
define v_plan_hash_value=1774581179

set serveroutput on
 
declare
    rc integer;
    baseline_ref_cur  DBMS_SQLTUNE.SQLSET_CURSOR;
    v_sql_handle      varchar2(30);
    v_plan_name       varchar2(30);

begin
 
-- Step 1 : Create SQL Tuning SET
dbms_sqltune.create_sqlset(
  sqlset_name => '&v_sql_id'||'_spm',
  description => 'SQL Tuning Set to create SQL baseline for '||'&v_sql_id');
 

-- Step 2 : Select sql_id and plan_hash_value from AWR

open baseline_ref_cur for
select VALUE(p) from table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
  begin_snap => '&v_begin_snap',
  end_snap => '&v_end_snap',
  basic_filter => 'sql_id='||CHR(39)||'&v_sql_id'||CHR(39)||' and plan_hash_value=&v_plan_hash_value',
  attribute_list => 'ALL')) p;
 
-- Step 3 : Load the AWR cursor into SQLSET

DBMS_SQLTUNE.LOAD_SQLSET(
  sqlset_name=>'&v_sql_id'||'_spm',
  populate_cursor=> baseline_ref_cur);

--+ Step 3+: Close cursor and check

close baseline_ref_cur;

select count(*)
  into rc
  from dba_sqlset_statements
 where sqlset_name     = '&v_sql_id' || '_spm'
   and sql_id          = '&v_sql_id'
   and plan_hash_value = &v_plan_hash_value;

 if rc = 0
   then DBMS_SQLTUNE.drop_sqlset('fxgzfhx4fr9rv'||'_spm');
        raise NO_DATA_FOUND;
 end if;

-- Step 4 : Create baseline; that is loading plans from sqlset into SPM

rc := dbms_spm.load_plans_from_sqlset(
 sqlset_name  => '&v_sql_id'||'_spm',
 basic_filter => 'sql_id='||CHR(39)||'&v_sql_id'||CHR(39)||' and plan_hash_value=&v_plan_hash_value',
 fixed        => '&v_fixed',
 enabled      => '&v_enabled');

--+ Step 5: Get baseline names

   select sql_handle, plan_name
     into v_sql_handle, v_plan_name
     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 = '&v_sql_id'
      --and origin    = 'MANUAL-LOAD'
      and created   > sysdate - 15/24/60/60;


   dbms_output.put_line('');
   dbms_output.put_line('Baseline '||v_sql_handle||' '||v_plan_name||' was created from AWR');
   dbms_output.put_line('for SQL_ID='||'&v_sql_id'||', SQL_PLAN_HASH='||'&v_plan_hash_value');

end;
/




Baseline SQL_80f7845a69e12ff7 SQL_PLAN_81xw4b9ny2bzrf28fb0a6 was created from AWR
for SQL_ID=3kqrku32p6sfn, SQL_PLAN_HASH=1774581179





col sql_text 	for a50 wrap
col enabled 	for a15
col  accepted 	for a15
col fixed 	for a15
col CREATED 	for a30
col PLAN_NAME 	for a30
select CREATED,sa.sql_id,sql_handle, plan_name,origin, enabled, accepted, fixed, sa.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 = '&v_sql_id'
      --and origin    = 'MANUAL-LOAD'
      and created   > sysdate - 1;






CREATED                        SQL_ID        SQL_HANDLE                     PLAN_NAME                      ORIGIN                        ENABLED         ACCEPTED        FIXED           SQL_TEXT
------------------------------ ------------- ------------------------------ ------------------------------ ----------------------------- --------------- --------------- --------------- --------------------------------------------------
15-NOV-22 05.50.22.000000 AM   3kqrku32p6sfn SQL_80f7845a69e12ff7           SQL_PLAN_81xw4b9ny2bzrf28fb0a6 MANUAL-LOAD-FROM-STS          YES             YES             YES             MERGE /*+ OPT_PARAM('_parallel_syspls_obey_force'
                                                                                                                                                                                         'false') */ INTO OPTSTAT_USER_
Posted by at

1 comment:

Anuj Singh said...





alter session set nls_date_format = 'dd-MON-yyyy hh24:mi:ss';
col NAME for a35
col OWNER for a20
Select NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET;

12 November 2022 at 09:34

Post a Comment

Subscribe to: Post Comments (Atom)

Oracle DBA

My photo
Essex, United Kingdom

anuj blog Archive