sql_id and objects
efine sql_id='7jycxu86n60qh' -------
SET HEADING ON
SET PAGESIZE 1000 LINESIZE 500
COL text FOR A50 wrap
COL ctext FOR A50 wrap
col PARSING_SCHEMA_NAME for a20
col LAST_LOAD_TIME for a27
col FIRST_LOAD_TIME for a27
col OWNER for a20
col OBJECT_NAME for a20
select vs.sql_id,vs.last_load_time, ao.OWNER, parsing_schema_name, first_load_time , ao.OBJECT_NAME, vs.program_line#, executions exe, vs.sqltype, vs.sql_id, vs.rows_processed rows_processed, concurrency_wait_time, elapsed_time/1000000 elapsed_secs, elapsed_time/1000000/(case when executions = 0 then 1 else executions end) elap_per_exec_secs, vs.sql_text ctext
from gv$sql vs, all_objects ao
where vs.PROGRAM_ID = ao.OBJECT_ID and parsing_schema_name not in ('SYS','SYSTEM')
--and parsing_schema_name in ('')
--and owner not in ('SYS','SYSTEM','DBSNMP','SYSMAN','MDSYS')
and vs.sql_id='&sql_id'
order by vs.last_load_time desc , vs.parsing_schema_name, vs.first_load_time desc , program_id, vs.program_line#;
Saturday 12 November 2022
gv$sql with objects
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"><