Sunday, 23 May 2021

How to Create Oracle Package? ..

SQL> connect scott/tiger

SQL> desc scott.dept

Name Null? Type

----------------------------------------- -------- ----------------------------

DEPTNO NOT NULL NUMBER(2)

DNAME VARCHAR2(14)

LOC VARCHAR2(13)

SQL> create table depttest as select * from dept ;

Table created.

desc scott.depttest

Name Null? Type

----------------------------------------- -------- ----------------------------

DEPTNO NUMBER(2)

DNAME VARCHAR2(14)

LOC VARCHAR2(13)

CREATE OR REPLACE PACKAGE pkg_dept IS

FUNCTION prnt_strng RETURN VARCHAR2;

PROCEDURE proc_dept(d_DEPTNO number, d_dname VARCHAR2,d_loc VARCHAR2);

END pkg_dept;

--Package Body

CREATE OR REPLACE PACKAGE BODY pkg_dept IS

--Function

FUNCTION prnt_strng RETURN VARCHAR2 IS

BEGIN

RETURN 'http://anuj-singh.blogspot.com/';

END prnt_strng;

--Procedure

PROCEDURE proc_dept(d_DEPTNO number, d_dname VARCHAR2,d_loc VARCHAR2) IS

BEGIN

INSERT INTO depttest (DEPTNO, DNAME,LOC) VALUES(d_DEPTNO, d_dname,d_loc);

END;

END pkg_dept;

====================

Output

CREATE OR REPLACE PACKAGE pkg_dept IS

FUNCTION prnt_strng RETURN VARCHAR2;

PROCEDURE proc_dept(d_DEPTNO number, d_dname VARCHAR2,d_loc VARCHAR2);

END pkg_dept;

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY pkg_dept IS

2 --Function

FUNCTION prnt_strng RETURN VARCHAR2 IS

3 4 BEGIN

RETURN 'http://anuj-singh.blogspot.com/';

5 6 END prnt_strng;

--Procedure

7 8 PROCEDURE proc_dept(d_DEPTNO number, d_dname VARCHAR2,d_loc VARCHAR2) IS

9 BEGIN

INSERT INTO depttest (DEPTNO, DNAME,LOC) VALUES(d_DEPTNO, d_dname,d_loc);

10 11 END;

END pkg_dept; 12 13

14 /

Package body created.

===========================================

Oracle Package DBMS METADATA

set long 5000 pagesize 300

SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY','PKG_DEPT','SCOTT') FROM DUAL;

CREATE OR REPLACE EDITIONABLE PACKAGE BODY "SCOTT"."PKG_DEPT" IS

--Function

FUNCTION prnt_strng RETURN VARCHAR2 IS

BEGIN

RETURN 'http://anuj-singh.blogspot.com/';

END prnt_strng;

--Procedure

PROCEDURE proc_dept(d_DEPTNO number, d_dname VARCHAR2,d_loc VARCHAR2) IS

BEGIN

INSERT INTO depttest (DEPTNO, DNAME,LOC) VALUES(d_DEPTNO, d_dname,d_loc);

END;

END pkg_dept;

define v_owner='TEST' --- owner

define v_pkg='PKG_TEST' --- PACKAGE NAME

declare

myPackageSpec clob;

myPackageBody clob;

BEGIN

select dbms_metadata.get_ddl('PACKAGE_BODY', '&v_pkg', '&v_owner') into myPackageBody FROM DUAL;

select dbms_metadata.get_ddl('PACKAGE_SPEC', '&v_pkg', '&v_owner') into myPackageSpec FROM DUAL;

dbms_output.put_line(myPackageSpec);

dbms_output.put_line(myPackageBody);

end;

CREATE OR REPLACE EDITIONABLE PACKAGE "SCOTT"."PKG_DEPT" IS

FUNCTION

prnt_strng RETURN VARCHAR2;

PROCEDURE proc_dept(d_DEPTNO number, d_dname

VARCHAR2,d_loc VARCHAR2);

END pkg_dept;

CREATE OR REPLACE EDITIONABLE PACKAGE BODY "SCOTT"."PKG_DEPT" IS

--Function

FUNCTION prnt_strng RETURN VARCHAR2 IS

BEGIN

RETURN

'http://anuj-singh.blogspot.com/';

END prnt_strng;

--Procedure

PROCEDURE proc_dept(d_DEPTNO number, d_dname VARCHAR2,d_loc VARCHAR2) IS

BEGIN

INSERT INTO depttest (DEPTNO, DNAME,LOC) VALUES(d_DEPTNO,

d_dname,d_loc);

END;

END pkg_dept;

PL/SQL procedure successfully completed.

=========================================================================

Testing !!!!!!!!!!

--Test Function

set serveroutput on ;

BEGIN

DBMS_OUTPUT.PUT_LINE (pkg_dept.prnt_strng);

END;

http://anuj-singh.blogspot.com/

PL/SQL procedure successfully completed.

exec DBMS_OUTPUT.PUT_LINE (pkg_dept.prnt_strng);

==========================================================

--Test procedure

SQL>

exec pkg_dept.proc_dept(11,'FFF','UUUU');SQL>

PL/SQL procedure successfully completed.

SQL> select * from depttest;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

11 FFF UUUU <<<< New row

Wednesday, 5 May 2021

SQL History

ORACLE SQL History ....








define sql_id='XXXXXXX'

set long 30000 pagesize 500 linesize 300
col frm         heading from 
select * from (select 'gv$sql' frm ,  sql_fulltext sql_text from gv$sql where sql_id='&&sql_id'
               union all
	select 'gv$sqlstats' frm ,  sql_fulltext sql_text from gv$sqlstats where sql_id='&&sql_id'
	union all
          select 'dba_hist', sql_text from dba_hist_sqltext where sql_id='&&sql_id' 
			   )
			 where 1=1
			 and rownum<2
			 ;



https://anuj-singh.blogspot.com/2017/09/sql-information-from-memory.html


-- with round 


define sql_id='9px0f4mv0tn0t'

VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
VARIABLE x VARCHAR2(30)
exec select max(snap_id) -1000 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;

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 executions_total 	for 999,999 	HEA 'Execs|Total'
COL rows_per_exec 	 	for 999999.999 	HEA 'Rows Per Exec'
COL et_secs_per_exec 	for 999999.999 	HEA 'Elap Secs|Per Exec'
COL cpu_secs_per_exec 	for 999999.999 	HEA 'CPU Secs|Per Exec'
COL io_secs_per_exec 	for 999999.999 	HEA 'IO Secs|Per Exec'
COL cl_secs_per_exec  	for 999999.999 	HEA 'Clus Secs|Per Exec'
COL ap_secs_per_exec 	for 999999.999 	HEA 'App Secs|Per Exec'
COL cc_secs_per_exec 	for 999999.999 	HEA 'Conc Secs|Per Exec'
COL pl_secs_per_exec 	for 999999.999 	HEA 'PLSQL Secs|Per Exec'
COL ja_secs_per_exec 	for 999999.999 	HEA 'Java Secs|Per Exec'
SELECT 'gv$dba_hist_sqlstat' source,h.instance_number,s.snap_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,
TO_CHAR(ROUND(h.rows_processed_total / h.executions_total), '999,999,999,999') 	rows_per_exec,
ROUND(h.elapsed_time_total / h.executions_total / 1e6, 3) 						et_secs_per_exec,
ROUND(h.cpu_time_total / h.executions_total / 1e6, 3) 							cpu_secs_per_exec,
ROUND(h.iowait_total / h.executions_total / 1e6, 3) 							io_secs_per_exec,
ROUND(h.clwait_total / h.executions_total / 1e6, 3) 							cl_secs_per_exec,
ROUND(h.apwait_total / h.executions_total / 1e6, 3) 							ap_secs_per_exec,
ROUND(h.ccwait_total / h.executions_total / 1e6, 3) 							cc_secs_per_exec,
ROUND(h.plsexec_time_total / h.executions_total / 1e6, 3) 						pl_secs_per_exec,
ROUND(h.javexec_time_total / h.executions_total / 1e6, 3) 						ja_secs_per_exec
FROM dba_hist_sqlstat h, dba_hist_snapshot s 
WHERE 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 s.snap_id > :BgnSnap 
UNION ALL
SELECT 'gv$sqlarea_plan_hash' source,h.inst_id,0 "snap_id",
TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') snap_time,
TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') end_time,
h.sql_id, h.plan_hash_value, h.executions,
TO_CHAR(ROUND(h.rows_processed / h.executions), '999,999,999,999') rows_per_exec,
ROUND(h.elapsed_time / h.executions / 1e6, 3) et_secs_per_exec,
ROUND(h.cpu_time / h.executions / 1e6, 3) cpu_secs_per_exec,
ROUND(h.USER_IO_WAIT_TIME / h.executions / 1e6, 3) io_secs_per_exec,
ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3) cl_secs_per_exec,
ROUND(h.APPLICATION_WAIT_TIME / h.executions / 1e6, 3) ap_secs_per_exec,
ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3) cc_secs_per_exec,
ROUND(h.PLSQL_EXEC_TIME / h.executions / 1e6, 3) pl_secs_per_exec,
ROUND(h.JAVA_EXEC_TIME / h.executions / 1e6, 3) ja_secs_per_exec
FROM gv$sqlarea_plan_hash h
WHERE h.sql_id = '&&sql_id'
AND h.executions > 0
order by SNAP_ID ;

*************************************************************



 set linesize 300 pagesize 1000
SELECT t.* FROM gv$sql s, table(dbms_xplan.display_cursor(s.sql_id,s.child_number,FORMAT => 'ADVANCED +ROWS +BYTES +COST +PARALLEL +PARTITION +IOSTATS +MEMSTATS +ALIAS +PEEKED_BINDS +OUTLINE +PREDICATE -PROJECTION +REMOTE +NOTE')) t WHERE s.sql_id in (select sql_id from gv$session where sql_id is not null 
--and USERNAME='USER' 
and sql_id='gygw3jdhjdm46'
 )  ;






define sql_id='0jrngm5htnzuv'

set lines 500 pages 100
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    for a15		HEA 'Rows Per Exec'
col et_secs_per_exec  for a15	HEA 'Elap Secs|Per Exec'
col cpu_secs_per_exec  for a15	HEA 'CPU Secs|Per Exec'
col io_secs_per_exec  for a15	HEA 'IO Secs|Per Exec'
col cl_secs_per_exec  for a15	HEA 'Clus Secs|Per Exec'
col ap_secs_per_exec  for a15	HEA 'App Secs|Per Exec'
col cc_secs_per_exec  for a15	HEA 'Conc Secs|Per Exec'
col pl_secs_per_exec  for a15	HEA 'PLSQL Secs|Per Exec'
col ja_secs_per_exec  for a15	HEA 'Java Secs|Per Exec'
col executions_total  for a15  FOR 999,999 HEA 'Execs|Total'
col "End Time" for a27
col end_time for a27
select 'gv$dba_hist_sqlstat' source,h.instance_number,
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,
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 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
UNION ALL
SELECT 'gv$sqlarea_plan_hash' source,h.inst_id,
TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') snap_time,
TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') end_time,
h.sql_id,
h.plan_hash_value,
h.executions,
to_char(ROUND(h.rows_processed / h.executions), '999,999,999,999') 				rows_per_exec,
to_char(ROUND(h.elapsed_time / h.executions / 1e6, 3), '999,990.000') 			et_secs_per_exec,
to_char(ROUND(h.cpu_time / h.executions / 1e6, 3), '999,990.000') 				cpu_secs_per_exec,
to_char(ROUND(h.USER_IO_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') 		io_secs_per_exec,
to_char(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') 		cl_secs_per_exec,
to_char(ROUND(h.APPLICATION_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') 	ap_secs_per_exec,
to_char(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') 		cc_secs_per_exec,
to_char(ROUND(h.PLSQL_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') 		pl_secs_per_exec,
to_char(ROUND(h.JAVA_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') 		ja_secs_per_exec
FROM gv$sqlarea_plan_hash h
WHERE h.sql_id = '&sql_id'
--and h.inst_id=inst_id
AND h.executions > 0
order by source ;




***

 
 
define sql_id='gqkr2um43ga39'
 
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
VARIABLE x VARCHAR2(30)
exec select max(snap_id) -60 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;


set lines 500 pages 100
col instance_number FOR 9999   			
col end_time 							
col plan_hash_value 	        		
col rows_per_exec    for  a20			
col et_secs_per_exec  for a20			
col cpu_secs_per_exec for a20			
col io_secs_per_exec  for a20			
col cl_secs_per_exec  for a20			
col ap_secs_per_exec  for a20			
col cc_secs_per_exec  for a20			
col pl_secs_per_exec  for a20			
col ja_secs_per_exec  for a20			
col executions_total  for a20  FOR 999,999 
col "End Time" for a27
col end_time for a27
select 'gv$dba_hist_sqlstat' source,h.instance_number,
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,
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 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 s.snap_id > :BgnSnap 
UNION ALL
SELECT 'gv$sqlarea_plan_hash' source,h.inst_id,
TO_CHAR(FIRST_LOAD_TIME, 'DD-MM-YYYY HH24:MI') snap_time,
TO_CHAR(LAST_LOAD_TIME, 'DD-MM-YYYY HH24:MI') end_time,
h.sql_id,
h.plan_hash_value,
h.executions,
to_char(ROUND(h.rows_processed / h.executions), '999,999,999,999') 				rows_per_exec,
to_char(ROUND(h.elapsed_time / h.executions / 1e6, 3), '999,990.000') 			et_secs_per_exec,
to_char(ROUND(h.cpu_time / h.executions / 1e6, 3), '999,990.000') 				cpu_secs_per_exec,
to_char(ROUND(h.USER_IO_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') 		io_secs_per_exec,
to_char(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') 		cl_secs_per_exec,
to_char(ROUND(h.APPLICATION_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') 	ap_secs_per_exec,
to_char(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') 		cc_secs_per_exec,
to_char(ROUND(h.PLSQL_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') 		pl_secs_per_exec,
to_char(ROUND(h.JAVA_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') 		ja_secs_per_exec
FROM gv$sqlarea_plan_hash h
WHERE h.sql_id = '&sql_id'
--and h.inst_id=inst_id
AND h.executions > 0
and FIRST_LOAD_TIME > sysdate -4
order by source ;












 define sql_id='b3cyyrgtgxakt'
COL instance_number FOR 9999 HEA 'Inst'
COL end_time for a18 HEA 'End Time'

COL executions_total FOR 99,999,999 
COL rows_per_exec  FOR A12 		
COL et_secs_per_exec  FOR A16 	
COL cpu_secs_per_exec  FOR A16 	
COL io_secs_per_exec  FOR A16	
COL cl_secs_per_exec  FOR A16 	
COL ap_secs_per_exec  FOR A16	
COL cc_secs_per_exec  FOR A16	
COL pl_secs_per_exec  FOR A16	
COL ja_secs_per_exec  FOR A16	
-- col "End Time" for a27
SELECT h.instance_number,
       TO_CHAR(CAST(s.end_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') end_time,
       h.plan_hash_value, 
       h.executions_total,
       TO_CHAR(ROUND(h.rows_processed_total / h.executions_total), '999999999') 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 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
 ORDER BY
       h.sql_id,
       h.instance_number,
       s.end_interval_time,
       h.plan_hash_value
/



from
https://github.com/tanelpoder/tpt-oracle/blob/master/awr/awr_sqlstats_per_exec.sql

alter session set nla_date_format-'dd-mm-yyyy hh24:mi';
define 1='dnm7fqpf08p8w'
DEF   3="TIMESTAMP'2023-07-01 01:00:00'"
DEF   4="TIMESTAMP'2023-07-15 03:15:00'"

define 2 =' '

SET TERMOUT OFF pagesize 5000 tab off verify off linesize 999 trimspool on trimout on null ""
SET TERMOUT ON


col BEGIN_INTERVAL_TIME for a27
COL executions       FOR 999999999990
COL ela_ms_per_exec  FOR 999999999990
COL rows_per_exec    FOR 999999999990.0
COL lios_per_exec    FOR 999999999990
COL blkrd_per_exec   FOR 999999999990
COL cpu_ms_per_exec  FOR 999999999990
COL iow_ms_per_exec  FOR 999999999990
COL avg_iow_ms       FOR 999999999990.0
COL clw_ms_per_exec  FOR 999999999990
COL apw_ms_per_exec  FOR 999999999990
COL ccw_ms_per_exec  FOR 999999999990

SELECT
   -- CAST(begin_interval_time AS DATE) begin_interval_time
begin_interval_time
  , sql_id
  , plan_hash_value
  , SUM(executions_delta)     executions
  , ROUND(SUM(elapsed_time_delta  ) / DECODE(SUM(executions_delta),0,1,SUM(executions_delta))/1000)                         ela_ms_per_exec
  , ROUND(SUM(cpu_time_delta      ) / DECODE(SUM(executions_delta),0,1,SUM(executions_delta))/1000)                         cpu_ms_per_exec
  , ROUND(SUM(rows_processed_delta) / DECODE(SUM(executions_delta),0,1,SUM(executions_delta)),1)                            rows_per_exec
  , ROUND(SUM(buffer_gets_delta   ) / DECODE(SUM(executions_delta),0,1,SUM(executions_delta)),1)                            lios_per_exec
  , ROUND(SUM(disk_reads_delta    ) / DECODE(SUM(executions_delta),0,1,SUM(executions_delta)),1)                            blkrd_per_exec
  , ROUND(SUM(iowait_delta        ) / DECODE(SUM(executions_delta),0,1,SUM(executions_delta))/1000)                         iow_ms_per_exec
  , ROUND(SUM(iowait_delta        ) / DECODE(SUM(physical_read_requests_delta)+SUM(physical_write_requests_delta),0,1
                                            ,SUM(physical_read_requests_delta)+SUM(physical_write_requests_delta))/1000,1)  avg_iow_ms
  , ROUND(SUM(clwait_delta        ) / DECODE(SUM(executions_delta),0,1,SUM(executions_delta))/1000)                         clw_ms_per_exec
  , ROUND(SUM(apwait_delta        ) / DECODE(SUM(executions_delta),0,1,SUM(executions_delta))/1000)                         apw_ms_per_exec
  , ROUND(SUM(ccwait_delta        ) / DECODE(SUM(executions_delta),0,1,SUM(executions_delta))/1000)                         ccw_ms_per_exec
--  , ROUND(SUM(elapsed_time_delta - cpu_time_delta - iowait_delta - clwait_delta - apwait_delta - ccwait_delta) / DECODE(SUM(executions_delta),0,1,SUM(executions_delta)) / 1000, 1) oth_ms_per_exec
FROM
    dba_hist_snapshot sn
  , dba_hist_sqlstat st
WHERE
    sn.snap_id = st.snap_id
AND sn.dbid    = st.dbid
AND sn.instance_number = st.instance_number
AND sql_id = '&1'
--AND plan_hash_value LIKE '&2'
AND begin_interval_time >= &3
AND end_interval_time   <= &4
AND (elapsed_time_delta != 0 AND cpu_time_delta != 0)
GROUP BY
 --   CAST(begin_interval_time AS DATE)
begin_interval_time
  , sql_id
  , plan_hash_value
ORDER BY
    begin_interval_time
  , sql_id
  , plan_hash_value
/




***********



 select DBMS_SQL_TRANSLATOR.SQL_ID(sql_text) as sql_id,sql_handle,plan_name  from dba_sql_plan_baselines 
  where DBMS_SQL_TRANSLATOR.SQL_ID(sql_text) = '&SQL_ID';
  



set linesize 32000  pagesize 1000 long 2000000000 longchunksize 1000  head off  verify off termout off



set head on  echo on  termout on  trimspool on 

column u new_value us noprint;
column n new_value ns noprint;
 
select name n from v$database;
select user u from dual;




column END_INTERVAL_TIME format a25

-- 48vf4pg4g5508


select 
sql_id,
plan_hash_value,
END_INTERVAL_TIME,
executions_delta,
ELAPSED_TIME_DELTA/(nonzeroexecutions*1000) "Elapsed Average ms",
CPU_TIME_DELTA/(nonzeroexecutions*1000) "CPU Average ms",
IOWAIT_DELTA/(nonzeroexecutions*1000) "IO Average ms",
CLWAIT_DELTA/(nonzeroexecutions*1000) "Cluster Average ms",
APWAIT_DELTA/(nonzeroexecutions*1000) "Application Average ms",
CCWAIT_DELTA/(nonzeroexecutions*1000) "Concurrency Average ms",
BUFFER_GETS_DELTA/nonzeroexecutions "Average buffer gets",
DISK_READS_DELTA/nonzeroexecutions "Average disk reads",
trunc(PHYSICAL_WRITE_BYTES_DELTA/(1024*1024*nonzeroexecutions)) "Average disk write megabytes",
ROWS_PROCESSED_DELTA/nonzeroexecutions "Average rows processed"
from
(select 
ss.snap_id,
ss.sql_id,
ss.plan_hash_value,
sn.END_INTERVAL_TIME,
ss.executions_delta,
case ss.executions_delta when 0 then 1 else ss.executions_delta end nonzeroexecutions,
ELAPSED_TIME_DELTA,
CPU_TIME_DELTA,
IOWAIT_DELTA,
CLWAIT_DELTA,
APWAIT_DELTA,
CCWAIT_DELTA,
BUFFER_GETS_DELTA,
DISK_READS_DELTA,
PHYSICAL_WRITE_BYTES_DELTA,
ROWS_PROCESSED_DELTA
from DBA_HIST_SQLSTAT ss,DBA_HIST_SNAPSHOT sn
where ss.sql_id = '48vf4pg4g5508'
and ss.snap_id=sn.snap_id
and ss.INSTANCE_NUMBER=sn.INSTANCE_NUMBER)
where ELAPSED_TIME_DELTA > 0
order by snap_id,sql_id;


===


define sql_id='48vf4pg4g5508'
set linesize 999
col avg_et_secs justify right format 9999999.99
col cost justify right format 9999999999
col timestamp justify center format a25
col parsing_schema_name justify center format a30
col inst_id format 999999999

alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';

select  'gv$sqlarea_plan_hash' source, INST_ID, SQL_ID, PLAN_HASH_VALUE,
                round(elapsed_time/decode(nvl(executions,0),0,1,executions)/1e6/
                decode(px_servers_executions,0,1,px_servers_executions)/decode(nvl(executions,0),0,1,executions),2)     avg_et_secs,
                px_servers_executions/decode(nvl(executions,0),0,1,executions) avg_px,
                optimizer_cost cost, LAST_LOAD_TIME timestamp, parsing_schema_name --FIRST_LOAD_TIME, LAST_LOAD_TIME, LAST_ACTIVE_TIME, SQL_PROFILE
from gv$sqlarea_plan_hash
where sql_id = nvl(trim('&&sql_id'),sql_id)
UNION
SELECT 'dba_hist_sql_plan' source, null INST_ID, t1.sql_id sql_id, t1.plan_hash_value plan_hash_value, t2.avg_et_secs avg_et_secs, t2.avg_px, t1.cost cost, t1.timestamp timestamp, NULL parsing_schema_name
FROM dba_hist_sql_plan t1,
        (
                SELECT sql_id, plan_hash_value, --round(SUM(elapsed_time_total)/decode(SUM(executions_total),0,1,SUM(executions_total))/1e6,2) avg_et_secs
                round(SUM(elapsed_time_total)/decode(SUM(executions_total),0,1,SUM(executions_total))/1e6/
                decode(SUM(px_servers_execs_total),0,1,SUM(px_servers_execs_total))/decode(SUM(executions_total),0,1,SUM(executions_total)),2)  avg_et_secs,
                SUM(px_servers_execs_total)/decode(SUM(executions_total),0,1,SUM(executions_total)) avg_px
                FROM dba_hist_sqlstat
                WHERE
                        executions_total > 0
                GROUP BY        sql_id, plan_hash_value
        ) t2
WHERE
        t1.sql_id = nvl(TRIM('&sql_id.'), t1.sql_id)
        AND t1.depth = 0
        AND t1.sql_id = t2.sql_id(+)
        AND t1.plan_hash_value = t2.plan_hash_value(+)
order by avg_et_secs, cost
/
undef sql_id

with cpu !!!

define sql_id='7grvag6ayaxn1'

SET lin 400 ver OFF
 
COL instance_number FOR 9999 HEA 'Inst'
COL end_time HEA 'End Time'


COL plan_hash_value HEA 'Plan|Hash Value'
COL executions_total FOR 999,999,999,999 HEA 'Execs|Total'
COL rows_per_exec   for a15	 HEA 'Rows Per Exec'
COL et_secs_per_exec   for a15	HEA 'Elap Secs|Per Exec'
COL cpu_secs_per_exec   for a15	HEA 'CPU Secs|Per Exec'
COL io_secs_per_exec   for a15	HEA 'IO Secs|Per Exec'
COL cl_secs_per_exec   for a15	HEA 'Clus Secs|Per Exec'
COL ap_secs_per_exec   for a15	HEA 'App Secs|Per Exec'
COL cc_secs_per_exec   for a15	HEA 'Conc Secs|Per Exec'
COL pl_secs_per_exec   for a15	HEA 'PLSQL Secs|Per Exec'
COL ja_secs_per_exec   for a15	HEA 'Java Secs|Per Exec'
COL cpu_per_exec   	for a15	HEA 'Cpu Secs|Per Exec'
 
SELECT h.instance_number,h.snap_id,
       TO_CHAR(CAST(s.end_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') end_time,
	   h.con_id,
	   h.sql_id,
       h.plan_hash_value, 
       h.executions_total,
       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,
	   TO_CHAR(ROUND(h.CPU_TIME_TOTAL / h.executions_total / 1e6, 3), '999,990.000') cpu_per_exec
  FROM dba_hist_sqlstat h, 
       dba_hist_snapshot s
 WHERE h.sql_id = '&sql_id.'
 --  AND h.executions_total > 16000000
   AND s.snap_id = h.snap_id
   AND s.dbid = h.dbid
   AND s.instance_number = h.instance_number
 ORDER BY
       h.sql_id,
       h.instance_number,
       s.end_interval_time,
       h.plan_hash_value
/

set linesize 400 pagesize 300
col sql_text  for a80
select * from (
select count(sc.sql_id) cursor_count, a.con_id ,a.sql_id, a.sql_text from v$sqlarea a, v$sql_shared_cursor sc
where  a.sql_id = sc.sql_id
group by a.sql_id, a.con_id,a.sql_text
order by cursor_count desc)
where rownum <=10;

Oracle version count from History

define sql_id='xxxx'
 -- define sql_id            ='xxxxx'
set serveroutput on size 1000000  lines 200 pages 1000
set verify off
col begin_time for a16
col end_time for a10
col module for a20
col action for a20
col execs for 999,999,990 heading "Executions"
col physrd_exec for 999,999,990 heading "Disk|Reads|per Exec"
col logrds_exec for 999,999,990 heading "Buffer|Gets|per Exec"
col CPU_EXEC for 999,990.9999 heading "CPU|Time per|Exec(secs)"
col ELA_EXEC for 999,990.9999 heading "Elapsed|Time per|Exec(secs)"
col drwrites_exec for 999,999,990 heading "Direct|Writes|per Exec"
col rowsprc_exec for 999,999,990 heading "Rows|Processed|per Exec"
col fetch_EXEC for 999,990 heading "Fetches|per Exec"
col sharable_mb for 999,990.99 heading "Sharable|Mem(mb)"
col VERSION_COUNT for 999,990 heading "Version|Count"
col plan_hash for 999999999999
 
 
select to_char(b.BEGIN_INTERVAL_TIME,'DD-MON-RR HH24:MI') begin_time,
       to_char(b.END_INTERVAL_TIME,'HH24:MI') end_time,
       a.sql_id,
       a.plan_hash_value plan_hash,
       --a.invalidations_delta,
       --a.module,
           --a.action,
       a.executions_delta execs,
           (case when a.executions_delta > 0 then round((a.elapsed_time_delta/a.executions_delta)/1000000,4)
             when a.executions_delta = 0 then NULL end) ELA_EXEC,
           (case when a.executions_delta > 0 then round((a.cpu_time_delta/a.executions_delta)/1000000,4)
             when a.executions_delta = 0 then NULL end) CPU_EXEC,
       (case when a.executions_delta > 0 then round(a.buffer_gets_delta/a.executions_delta,0)
             when a.executions_delta = 0 then NULL end) logrds_exec,
       (case when a.executions_delta > 0 then round(a.disk_reads_delta/a.executions_delta,0)
             when a.executions_delta = 0 then NULL end) physrd_exec,
       (case when a.executions_delta > 0 then round(a.DIRECT_WRITES_DELTA/a.executions_delta,0)
             when a.executions_delta = 0 then NULL end) drwrites_exec,
       (case when a.executions_delta > 0 then round(a.ROWS_PROCESSED_DELTA/a.executions_delta,0)
             when a.executions_delta = 0 then NULL end) rowsprc_exec
       ,round(a.SHARABLE_MEM/1024/1024,2) sharable_mb
       ,a.VERSION_COUNT
       --a.disk_reads_delta physrds,
       --a.buffer_gets_delta logrds,
       --round(a.cpu_time_delta/1000000,0) "cpu_time(s)",
       --round(a.elapsed_time_delta/1000000,0) "ela_time(s)",
       --(case when a.executions_delta > 0 then round((a.FETCHES_DELTA/a.executions_delta)/1000000,0)
       --      when a.executions_delta = 0 then NULL end) fetch_EXEC
       --(case when a.executions_delta > 0 then round(a.disk_reads_delta/a.executions_delta,0) when a.executions_delta = 0 then NULL end)/(case when a.executions_delta > 0 then round(a.ROWS_PROCESSED_DELTA/a.executions_delta,0) when a.executions_delta = 0 then NULL end) pr_per_row
from   DBA_HIST_SQLSTAT a,
       DBA_HIST_SNAPSHOT b
where  1=1
-- a.snap_id between (SELECT MIN(SNAP_ID) FROM DBA_HIST_SNAPSHOT WHERE BEGIN_INTERVAL_TIME >= to_date('&start_in','DD-MON-RRRR HH24:MI:SS')) and (SELECT MAX(SNAP_ID) FROM DBA_HIST_SNAPSHOT WHERE BEGIN_INTERVAL_TIME < to_date('&end_in','DD-MON-RRRR HH24:MI:SS'))
and    a.snap_id = b.snap_id
and    a.instance_number = b.instance_number
and    a.sql_id = '&sql_id'
and    a.executions_delta > 0
order by b.BEGIN_INTERVAL_TIME
--,a.plan_hash_value
/

set linesize 300 pagesize 300
col is_bind_aware for a15
col is_bind_sensitive for a18
col is_shareable  for a18
select sql_id
    , child_number
    , is_bind_aware
    , is_bind_sensitive
    , is_shareable
    , to_char(exact_matching_signature) sig
    , executions
    , plan_hash_value
    from gv$sql
    where sql_id = '&sql_id'
	;

define sql_id='7grvag6ayaxn1'

SET lin 400 ver OFF
 
COL instance_number FOR 9999 HEA 'Inst'
COL end_time HEA 'End Time'


COL plan_hash_value HEA 'Plan|Hash Value'
COL executions_total FOR 999,999,999,999 HEA 'Execs|Total'
COL rows_per_exec   for a15	 HEA 'Rows Per Exec'
COL et_secs_per_exec   for a15	HEA 'Elap Secs|Per Exec'
COL cpu_secs_per_exec   for a15	HEA 'CPU Secs|Per Exec'
COL io_secs_per_exec   for a15	HEA 'IO Secs|Per Exec'
COL cl_secs_per_exec   for a15	HEA 'Clus Secs|Per Exec'
COL ap_secs_per_exec   for a15	HEA 'App Secs|Per Exec'
COL cc_secs_per_exec   for a15	HEA 'Conc Secs|Per Exec'
COL pl_secs_per_exec   for a15	HEA 'PLSQL Secs|Per Exec'
COL ja_secs_per_exec   for a15	HEA 'Java Secs|Per Exec'
COL cpu_per_exec   	for a15	HEA 'Cpu Secs|Per Exec'
 
SELECT h.instance_number,h.snap_id,
       TO_CHAR(CAST(s.end_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') end_time,
	   h.con_id,
	   h.sql_id,
       h.plan_hash_value, 
       h.executions_total,
       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,
	   TO_CHAR(ROUND(h.CPU_TIME_TOTAL / h.executions_total / 1e6, 3), '999,990.000') cpu_per_exec
  FROM dba_hist_sqlstat h, 
       dba_hist_snapshot s
 WHERE h.sql_id = '&sql_id.'
 --  AND h.executions_total > 16000000
   AND s.snap_id = h.snap_id
   AND s.dbid = h.dbid
   AND s.instance_number = h.instance_number
 ORDER BY
       h.sql_id,
       h.instance_number,
       s.end_interval_time,
       h.plan_hash_value
/

--- ===

set linesize 400
col TIME for a30

-- def sql_id="  "
def days_history="10"
def interval_hours="1"
select hss.instance_number inst,
 to_char(trunc(sysdate-&days_history+1)+trunc((cast(hs.begin_interval_time as date)- (trunc(sysdate-&days_history+1)))*24/(&interval_hours))*(&interval_hours)/24,'dd.mm.yyyy hh24:mi:ss') time,
 plan_hash_value,
 hss.sql_id,
 sum(hss.executions_delta) executions,
 round(sum(hss.elapsed_time_delta)/1000000,3) elapsed_time_s,
 round(sum(hss.cpu_time_delta)/1000000,3) cpu_time_s,
 round(sum(hss.iowait_delta)/1000000,3) iowait_s,
 round(sum(hss.clwait_delta)/1000000,3) clwait_s,
 round(sum(hss.apwait_delta)/1000000,3) apwait_s,
 round(sum(hss.ccwait_delta)/1000000,3) ccwait_s,
 round(sum(hss.rows_processed_delta),3) rows_processed,
 round(sum(hss.buffer_gets_delta),3) buffer_gets,
 round(sum(hss.disk_reads_delta),3) disk_reads,
 round(sum(hss.direct_writes_delta),3) direct_writes
from dba_hist_sqlstat hss, dba_hist_snapshot hs
where hss.sql_id='&sql_id'
 and hss.snap_id=hs.snap_id
 and hs.begin_interval_time>=trunc(sysdate)-&days_history+1
group by hss.sql_id,hss.instance_number, trunc(sysdate-&days_history+1)+trunc((cast(hs.begin_interval_time as date)-(trunc(sysdate-&days_history+1)))*24/(&interval_hours))*(&interval_hours)/24,plan_hash_value
having sum(hss.executions_delta)>0
order by hss.instance_number, trunc(sysdate-&days_history+1)+trunc((cast(hs.begin_interval_time as date)-(trunc(sysdate-&days_history+1)))*24/(&interval_hours))*(&interval_hours)/24, 4 desc;

--- =====

from Web

DEF days_of_history_accessed = '10'
DEF captured_at_least_x_times = '10'
DEF captured_at_least_x_days_apart = '5'
DEF med_elap_microsecs_threshold = '1e4'
DEF min_slope_threshold = '0.1'
DEF max_num_rows = '20'
 

WITH per_time AS
(
SELECT h.dbid,
h.sql_id,
SYSDATE - CAST(s.end_interval_time AS DATE) days_ago,
SUM(h.elapsed_time_total) / SUM(h.executions_total) time_per_exec
FROM dba_hist_sqlstat h,
dba_hist_snapshot s
WHERE h.executions_total > 0
AND s.snap_id = h.snap_id
AND s.dbid = h.dbid
AND s.instance_number = h.instance_number
AND CAST(s.end_interval_time AS DATE) > SYSDATE - &&days_of_history_accessed
GROUP BY
h.dbid,
h.sql_id,
SYSDATE - CAST(s.end_interval_time AS DATE)
),
avg_time AS (
SELECT dbid,
sql_id,
MEDIAN(time_per_exec) med_time_per_exec,
STDDEV(time_per_exec) std_time_per_exec,
AVG(time_per_exec) avg_time_per_exec,
MIN(time_per_exec) min_time_per_exec,
MAX(time_per_exec) max_time_per_exec
FROM per_time
GROUP BY
dbid,
sql_id
HAVING COUNT(*) >= &&captured_at_least_x_times
AND MAX(days_ago) - MIN(days_ago) >= &&captured_at_least_x_days_apart.
AND MEDIAN(time_per_exec) > &&med_elap_microsecs_threshold
),
time_over_median AS (
SELECT h.dbid,
h.sql_id,
h.days_ago,
(h.time_per_exec / a.med_time_per_exec) time_per_exec_over_med,
a.med_time_per_exec,
a.std_time_per_exec,
a.avg_time_per_exec,
a.min_time_per_exec,
a.max_time_per_exec
FROM per_time h, avg_time a
WHERE a.sql_id = h.sql_id
),
ranked AS (
SELECT RANK () OVER (ORDER BY ABS(REGR_SLOPE(t.time_per_exec_over_med, t.days_ago)) DESC) rank_num,
t.dbid,
t.sql_id,
CASE WHEN REGR_SLOPE(t.time_per_exec_over_med, t.days_ago) > 0 THEN 'IMPROVING' ELSE 'REGRESSING' END change,
ROUND(REGR_SLOPE(t.time_per_exec_over_med, t.days_ago), 3) slope,
ROUND(AVG(t.med_time_per_exec)/1e6, 3) med_secs_per_exec,
ROUND(AVG(t.std_time_per_exec)/1e6, 3) std_secs_per_exec,
ROUND(AVG(t.avg_time_per_exec)/1e6, 3) avg_secs_per_exec,
ROUND(MIN(t.min_time_per_exec)/1e6, 3) min_secs_per_exec,
ROUND(MAX(t.max_time_per_exec)/1e6, 3) max_secs_per_exec
FROM time_over_median t
GROUP BY
t.dbid,
t.sql_id
HAVING ABS(REGR_SLOPE(t.time_per_exec_over_med, t.days_ago)) > &&min_slope_threshold.
)
SELECT LPAD(ROWNUM, 2) row_n,
r.sql_id,
r.change,
TO_CHAR(r.slope, '990.000MI') slope,
TO_CHAR(r.med_secs_per_exec, '999,990.000') med_secs_per_exec,
TO_CHAR(r.std_secs_per_exec, '999,990.000') std_secs_per_exec,
TO_CHAR(r.avg_secs_per_exec, '999,990.000') avg_secs_per_exec,
TO_CHAR(r.min_secs_per_exec, '999,990.000') min_secs_per_exec,
TO_CHAR(r.max_secs_per_exec, '999,990.000') max_secs_per_exec,
(SELECT COUNT(DISTINCT p.plan_hash_value) FROM dba_hist_sql_plan p WHERE p.dbid = r.dbid AND p.sql_id = r.sql_id) plans, REPLACE((SELECT DBMS_LOB.SUBSTR(s.sql_text, 80) FROM dba_hist_sqltext s
 WHERE s.dbid = r.dbid AND s.sql_id = r.sql_id and rownum<2), CHR(10)) sql_text_80
FROM ranked r
WHERE r.rank_num <= &&max_num_rows.
ORDER BY
r.rank_num;

set linesize 300 
col LAST_SEEN 	for a25
col FIRST_SEEN 	for a25
col SQL_TEXT 	for a50 wrap
select   'CACHE' plan_source
, p.sql_id
, p.plan_hash_value
, min(to_date(p.first_load_time, 'YYYY-MM-DD/HH24:MI:SS')) first_seen
, max(to_date(p.last_active_time)) last_seen
, to_char(substr(p.sql_text, 1, 50)) sql_text 
from   gv$sqlarea p
where
  p.sql_id='&sql_id'
group by  p.sql_id, p.plan_hash_value, to_char(substr(p.sql_text, 1, 50))
union all
select   'AWR'
, h.sql_id
, h.plan_hash_value
, min(s.begin_interval_time)
, max(s.end_interval_time)
, to_char(substr(t.sql_text, 1, 50))
from 
  dba_hist_sqlstat h
, dba_hist_sql_plan p
, dba_hist_snapshot s
, dba_hist_sqltext t
where 
  h.snap_id=s.snap_id
  and h.dbid=p.dbid
  and h.sql_id=p.sql_id
  and h.sql_id='&sql_id'
  and t.sql_id=h.sql_id
group by   h.sql_id, h.plan_hash_value, to_char(substr(t.sql_text, 1, 50))
union all
select   'SQLSET'
, p.sql_id
, p.plan_hash_value
, p.plan_timestamp
, p.plan_timestamp
, to_char(p.sql_text) 
from   dba_sqlset_statements p
where   p.sql_id='&sql_id'
;

===

set linesize 300  pages 1200
col snap_id 		for 999999
col BEGIN_INTERVAL_TIME for a27
col INST 		for 9
col END_INTERVAL_TIME 	for a23
col INSTANCE_NUMBER 	for 99
col OPTIMIZER_COST 	for 9999999999
col PLSEXEC_TIME_TOTAL 	for 999
col STARTUP_TIME 	for a22
col PARSING_SCHEMA_ID 	for 9999999999999
col ELAPSED_TIME1 	for a15
select st.snap_id,
st.INSTANCE_NUMBER as INST,
st.sql_id ,
st.OPTIMIZER_COST,
sn.BEGIN_INTERVAL_TIME,
st.PLAN_HASH_VALUE, 
round( st.CPU_TIME_DELTA/1000000,3) 	as CPU,
round(st.IOWAIT_DELTA/1000000,3) 	as IO_Wait, 
round(st.ELAPSED_TIME_DELTA/1000000,3)	as elapsed_time,
round(st.BUFFER_GETS_DELTA/1000000,3) 	as buffer_gets_M,  
round(st.BUFFER_GETS_TOTAL/1000000,3) 	as BUFFER_GETS_TOTAL_M, 
round(st.ELAPSED_TIME_TOTAL/1000000,3)	as ELAPSED_TIME_TOTAL , 
round(st.FETCHES_DELTA,3) 		as Num_executions ,
case (ELAPSED_TIME_TOTAL-ELAPSED_TIME_DELTA) when 0 then 'start' end ELAPSED_TIME1 
from dba_hist_sqlstat st, dba_hist_snapshot sn
where st.snap_id=sn.snap_id 
and  st.INSTANCE_NUMBER=sn.INSTANCE_NUMBER 
and st.sql_id in  ('&sql_id') 
order by  st.INSTANCE_NUMBER , st.SQL_ID, st.snap_id;

=====


set linesize 999
col avg_et_secs justify right format 9999999.99
col cost justify right format 9999999999
col timestamp justify center format a25
col parsing_schema_name justify center format a30
col inst_id format 999999999
alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';
select 'gv$sqlarea_plan_hash' source, INST_ID, SQL_ID, PLAN_HASH_VALUE,
round(elapsed_time/decode(nvl(executions,0),0,1,executions)/1e6/decode(px_servers_executions,0,1,px_servers_executions)/decode(nvl(executions,0),0,1,executions),2) avg_et_secs,
px_servers_executions/decode(nvl(executions,0),0,1,executions) avg_px,
optimizer_cost cost, LAST_LOAD_TIME timestamp, parsing_schema_name --FIRST_LOAD_TIME, LAST_LOAD_TIME, LAST_ACTIVE_TIME, SQL_PROFILE
from gv$sqlarea_plan_hash
where sql_id = nvl(trim('&sql_id'),sql_id)
UNION
SELECT 'dba_hist_sql_plan' source, null INST_ID, t1.sql_id sql_id, t1.plan_hash_value plan_hash_value, t2.avg_et_secs avg_et_secs, t2.avg_px, t1.cost cost, t1.timestamp timestamp, NULL parsing_schema_name
FROM dba_hist_sql_plan t1,
(
SELECT sql_id, plan_hash_value, --round(SUM(elapsed_time_total)/decode(SUM(executions_total),0,1,SUM(executions_total))/1e6,2) avg_et_secs
round(SUM(elapsed_time_total)/decode(SUM(executions_total),0,1,SUM(executions_total))/1e6/decode(SUM(px_servers_execs_total),0,1,SUM(px_servers_execs_total))/decode(SUM(executions_total),0,1,SUM(executions_total)),2) avg_et_secs,
SUM(px_servers_execs_total)/decode(SUM(executions_total),0,1,SUM(executions_total)) avg_px
FROM dba_hist_sqlstat
WHERE
executions_total > 0
GROUP BY sql_id, plan_hash_value
) t2
WHERE
t1.sql_id = nvl(TRIM('&sql_id'), t1.sql_id)
AND t1.depth = 0
AND t1.sql_id = t2.sql_id(+)
AND t1.plan_hash_value = t2.plan_hash_value(+)
order by avg_et_secs, cost
/

SOURCE INST_ID SQL_ID PLAN_HASH_VALUE AVG_ET_SECS AVG_PX COST TIMESTAMP PARSING_SCHEMA_NAME

-------------------- ---------- ------------- --------------- ----------- ---------- ----------- ------------------------- ------------------------------

dba_hist_sql_plan 8fc3jxu7p0y25 0 .00 0 1 03-10-2021 15:18:50

gv$sqlarea_plan_hash 1 8fc3jxu7p0y25 0 .00 0 1 04-05-2022 02:53:02 SYS

gv$sqlarea_plan_hash 2 8fc3jxu7p0y25 0 .00 0 1 04-05-2022 03:38:06 SYS

--- ===

SET TERMOUT OFF pagesize 5000 tab off verify off linesize 999 trimspool on trimout on null ""
SET TERMOUT ON


COL exec_per_sec    FOR 99999990
COL ela_ms_per_sec  FOR 99999990
COL rows_per_sec    FOR 99999990
COL lios_per_sec    FOR 99999990
COL blkrd_per_sec   FOR 99999990
COL cpu_ms_per_sec  FOR 99999990
COL iow_ms_per_sec  FOR 99999990
COL clw_ms_per_sec  FOR 99999990
COL apw_ms_per_sec  FOR 99999990
COL ccw_ms_per_sec  FOR 99999990


SELECT
    CAST(begin_interval_time AS DATE) begin_interval_time
  , sql_id
  , plan_hash_value
  , ROUND(SUM(executions_delta    )        / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) exec_per_sec
  , ROUND(SUM(elapsed_time_delta  ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) ela_ms_per_sec
  , ROUND(SUM(rows_processed_delta)        / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) rows_per_sec
  , ROUND(SUM(buffer_gets_delta   )        / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) lios_per_sec
  , ROUND(SUM(disk_reads_delta    )        / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) blkrd_per_sec
  , ROUND(SUM(cpu_time_delta      ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) cpu_ms_per_sec
  , ROUND(SUM(iowait_delta        ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) iow_ms_per_sec
  , ROUND(SUM(clwait_delta        ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) clw_ms_per_sec
  , ROUND(SUM(apwait_delta        ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) apw_ms_per_sec
  , ROUND(SUM(ccwait_delta        ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) ccw_ms_per_sec
FROM
    dba_hist_snapshot sn
  , dba_hist_sqlstat st
WHERE
    sn.snap_id = st.snap_id
AND sn.dbid    = st.dbid
AND sn.instance_number = st.instance_number
AND sql_id = '&1'
AND plan_hash_value LIKE '&2'
AND begin_interval_time >= &3
AND end_interval_time   <= &4
GROUP BY
    CAST(begin_interval_time AS DATE)
  , CAST(end_interval_time AS DATE)
  , sql_id
  , plan_hash_value
ORDER BY
    begin_interval_time
  , sql_id
  , plan_hash_value
/

----===

set linesize 500 pagesize 300 
SELECT
    hsq.sql_id,
    hsq.plan_hash_value,
    NVL(SUM(hsq.executions_delta),0) AS total_exec,
    ROUND(SUM(hsq.elapsed_time_delta)/1000000,2) AS elapsed_time_total,
		ROUND(SUM(hsq.px_servers_execs_delta)/DECODE(SUM(hsq.executions_delta),0,NULL,SUM(hsq.executions_delta)),2) 				AS avg_px_servers_execs,
    ROUND(SUM(hsq.elapsed_time_delta)/DECODE(SUM(hsq.executions_delta),0,NULL,SUM(hsq.executions_delta))/1000000,2) 				AS avg_elapsed_time,
    ROUND(SUM(hsq.cpu_time_delta)/DECODE(SUM(hsq.executions_delta),0,NULL,SUM(hsq.executions_delta))/1000000,2) 					AS avg_cpu_time,
    ROUND(SUM(hsq.iowait_delta)/DECODE(SUM(hsq.executions_delta),0,NULL,SUM(hsq.executions_delta))/1000000,2) 						AS avg_iowait,
    ROUND(SUM(hsq.clwait_delta)/DECODE(SUM(hsq.executions_delta),0,NULL,SUM(hsq.executions_delta))/1000000,2) 						AS avg_cluster_wait,
    ROUND(SUM(hsq.apwait_delta)/DECODE(SUM(hsq.executions_delta),0,NULL,SUM(hsq.executions_delta))/1000000,2) 						AS avg_application_wait,
    ROUND(SUM(hsq.ccwait_delta)/DECODE(SUM(hsq.executions_delta),0,NULL,SUM(hsq.executions_delta))/1000000,2) 						AS avg_concurrency_wait,
    ROUND(SUM(hsq.rows_processed_delta)/DECODE(SUM(hsq.executions_delta),0,NULL,SUM(hsq.executions_delta)),2) 						AS avg_rows_processed,
    ROUND(SUM(hsq.buffer_gets_delta)/DECODE(SUM(hsq.executions_delta),0,NULL,SUM(hsq.executions_delta)),2) 							AS avg_buffer_gets,
    ROUND(SUM(hsq.disk_reads_delta)/DECODE(SUM(hsq.executions_delta),0,NULL,SUM(hsq.executions_delta)),2) 							AS avg_disk_reads,
    ROUND(SUM(hsq.direct_writes_delta)/DECODE(SUM(hsq.executions_delta),0,NULL,SUM(hsq.executions_delta)),2) 						AS avg_direct_writes,
    ROUND(SUM(hsq.io_interconnect_bytes_delta)/DECODE(SUM(hsq.executions_delta),0,NULL,SUM(hsq.executions_delta))/(1024*1024),0) 	AS avg_io_interconnect_mb,
    ROUND(SUM(hsq.physical_read_requests_delta)/DECODE(SUM(hsq.executions_delta),0,NULL,SUM(hsq.executions_delta)),0) 				AS avg_phys_read_requests,
    ROUND(SUM(hsq.physical_read_bytes_delta)/DECODE(SUM(hsq.executions_delta),0,NULL,SUM(hsq.executions_delta))/(1024*1024),0) 		AS avg_phys_read_mb,
    ROUND(SUM(hsq.physical_write_requests_delta)/DECODE(SUM(hsq.executions_delta),0,NULL,SUM(hsq.executions_delta)),0) 				AS avg_phys_write_requests,
    ROUND(SUM(hsq.physical_write_bytes_delta)/DECODE(SUM(hsq.executions_delta),0,NULL,SUM(hsq.executions_delta))/(1024*1024),0) 	AS avg_phys_write_mb
FROM dba_hist_sqlstat hsq
WHERE hsq.sql_id='&sql_id.'
GROUP BY hsq.sql_id, hsq.plan_hash_value;

from web

set linesize 1500 pagesize 500

col sql_text for a70 wrap

col begin_interval_time for a25 col end_interval_time for a25

col sql_profile for a15

col module for a15

col action for a15

select

s.sql_id,

round(DECODE(io_offload_elig_bytes_delta, 0, 0, 100 *(io_offload_elig_bytes_delta - io_interconnect_bytes_delta) / DECODE(io_offload_elig_bytes_delta , 0, 1, io_offload_elig_bytes_delta)), 6) "IO_SAVED_PCT",

round(DECODE(io_offload_elig_bytes_delta, 0, 0, 100 *(io_offload_elig_bytes_delta) / DECODE(physical_read_bytes_delta , 0, 1, physical_read_bytes_delta)), 6) "CELL_OFFLOAD_EFFICIENCY",

begin_interval_time,

end_interval_time,

executions_delta,

elapsed_time_delta / 1000000 "ELAPSED_TIME in SECONDS",

round(elapsed_time_delta / nvl(nullif(executions_delta, 0), 1) / 1000000, 6) "ELAPSED_TIME per ex in SEC",

cpu_time_delta / 1000000 "CPU_TIME_delta in SECONDS",

round(cpu_time_delta / nvl(nullif(executions_delta, 0), 1) / 1000000, 6) "CPU_TIME per ex in SEC",

plan_hash_value plan_hash,

command_type,

DECODE(io_offload_elig_bytes_delta, 0, 'No', 'Yes') offload,

s.instance_number,

module,

action,

sql_profile,

fetches_delta,

end_of_fetch_count_delta,

sorts_delta,

px_servers_execs_delta,

loads_delta,

invalidations_delta,

parse_calls_delta,

disk_reads_delta,

buffer_gets_delta,

rows_processed_delta,

iowait_delta / 1000000 "IO WAIT in SECONDS",

clwait_delta / 1000000 "CLUSTER WAIT in SECONDS",

apwait_delta / 1000000 "APPLICATION WAIT in SECONDS",

ccwait_delta / 1000000 "CONCURRENCY WAIT in SECONDS",

plsexec_time_delta / 1000000 "PL/SQL WAIT in SECONDS",

javexec_time_delta / 1000000 "Java EXEC WAIT in SECONDS",

round(io_offload_elig_bytes_delta / 1024 / 1024, 2) "CELL_OFFLD_ELIG in MB",

round(io_offload_elig_bytes_delta / nvl(nullif(executions_delta, 0), 1) / 1024 / 1024, 2) "CELL_OFFLD_ELIG per ex in MB",

round(cell_uncompressed_bytes_delta / 1024 / 1024, 2) "CELL_UNCOMPRSD in MB",

round(cell_uncompressed_bytes_delta / nvl(nullif(executions_delta, 0), 1) / 1024 / 1024, 2) "CELL_UNCOMPRSD per ex in MB",

round(io_offload_return_bytes_delta / 1024 / 1024, 2) "CELL_OFFLD_RTN in MB",

round(io_offload_return_bytes_delta / nvl(nullif(executions_delta, 0), 1) / 1024 / 1024, 2) "CELL_OFFLD_RTN per ex in MB",

round(io_interconnect_bytes_delta / 1024 / 1024, 2) "IO_INTERCONNECT in MB",

round(io_interconnect_bytes_delta / nvl(nullif(executions_delta, 0), 1) / 1024 / 1024, 2) "IO_INTERCONNECT per ex in MB",

round(physical_read_bytes_delta / 1024 / 1024, 2) "PHYSICAL_READ in MB",

round(physical_read_bytes_delta / nvl(nullif(executions_delta, 0), 1) / 1024 / 1024, 2) "PHYSICAL_READ per ex in MB",

physical_read_requests_delta,

round(physical_read_requests_delta / nvl(nullif(executions_delta, 0), 1)) "PHYSICAL_READ per ex",

optimized_physical_reads_delta,

round(optimized_physical_reads_delta / nvl(nullif(executions_delta, 0), 1)) "OPTIMIZED_READS per ex",

round(physical_write_bytes_delta / 1024 / 1024, 2) "PHYSICAL_WRITE in MB",

round(physical_write_bytes_delta / nvl(nullif(executions_delta, 0), 1) / 1024 / 1024, 2) "PHYSICAL_WRITE per ex in MB",

physical_write_requests_delta,

round(physical_write_requests_delta / nvl(nullif(executions_delta, 0), 1)) "OPTIMIZED_WRITES per ex",

direct_writes_delta,

dbms_lob.substr(sql_text, 4000, 1) sql_text

FROM dba_hist_sqlstat s, dba_hist_sqltext t, dba_hist_snapshot u

WHERE 1=1

-- and io_offload_elig_bytes_delta > 0

and s.sql_id = t.sql_id

AND ( s.snap_id = u.snap_id AND s.instance_number = u.instance_number )

and t.sql_id='&sql_id'

ORDER BY 2 DESC

;

SELECT

p.sql_id

,p.plan_hash_value

,p.child_number

,t.phv2

FROM

gv$sql_plan p

,xmltable('for $i in /other_xml/info

where $i/@type eq "plan_hash_2"

return $i'

passing xmltype(p.other_xml)

columns phv2 number path '/') t

WHERE p.sql_id = '&sql_id'

AND p.other_xml is not null;

set linesize 300 pagesize 300

col INDEX_OWNER for a20

col COLUMN_NAME for a20

col TABLE_NAME for a20

col INDEX_NAME for a20

SELECT ic.index_owner, ic.index_name, ic.table_name, ic.column_name, ic.column_position col_pos, tc.last_analyzed, tc. sample_size, tc.num_distinct, tc.num_nulls, tc.density, tc.histogram, tc.num_buckets

FROM dba_ind_columns ic

, dba_tab_columns tc

WHERE ic.index_name IN (

select distinct rtrim(substr(plan_table_output, instr(plan_table_output, '|', 1, 3)+2, (instr(plan_table_output, '|', 1, 4)-instr(plan_table_output, '|', 1, 3)-2)), ' ')

from (

SELECT plan_table_output

FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', 0, 'BASIC'))

)

where plan_table_output like '%INDEX%'

)

AND ic.table_owner=tc.owner

AND ic.table_name=tc.table_name

AND ic.column_name=tc.column_name

ORDER BY ic.table_owner, ic.table_name, ic.index_name, ic.column_position

INDEX_OWNER INDEX_NAME TABLE_NAME COLUMN_NAME COL_POS LAST_ANALYZED SAMPLE_SIZE NUM_DISTINCT NUM_NULLS DENSITY HISTOGRAM NUM_BUCKETS

-------------------- -------------------- -------------------- -------------------- ---------- -------------- ----------- ------------ ---------- ---------- --------------- -----------

SYS T_R_I1 T_RANGE ID 1 NONE

SYS T_R_I1 T_RANGE C1 2 NONE

==========================

/* --from

--https://www.williamrobertson.net/documents/performance-for-sqlid.html

-- Diagnostic queries for a specified SQL ID

-- PL/SQL Developer format - open in an SQL window, select-all and execute, entering SQL ID at the prompt.

-- William Robertson, 2015

https://www.williamrobertson.net/documents/

define sql_id='22356bkgsdcnh' ---!!!!!

set linesize 500 pagesize 300

col "Avg time/1M rows" for a15

col "Avg time/row" for a15

col "Total time" for a15

col "Average seconds" for 999999999999

col "Average time" for a15

select

sql_id ,

plan_hash_value as "Plan hash"

, sum(executions_calc) as "Times called"

, sum(end_of_fetch_count) as "Times completed"

, round(100 * sum(end_of_fetch_count) / sum(executions_calc),1) as "Success %"

, cast(numtodsinterval(sum(elapsed_time)/1E6,'SECOND') as interval day(1) to second(2)) as "Total time"

-- , round(sum(elapsed_time)/1e6) as "Total seconds"

, round(sum(elapsed_time)/1E6 / nvl(sum(executions_calc),1),1) as "Average seconds"

, cast(numtodsinterval(sum(elapsed_time)/1E6 / nvl(sum(executions_calc),1),'SECOND') as interval day(1) to second(1)) as "Average time"

-- , sum(buffer_gets) as "Buffer gets"

, round(sum(buffer_gets)/sum(executions_calc)) as "Buffer gets/exec"

, round(sum(buffer_gets)/nullif(sum(rows_processed),0)) as "Buffer gets/row"

, sum(rows_processed) as "Rows"

, round(sum(rows_processed) / sum(executions_calc)) as "Rows/exec"

, cast(numtodsinterval(sum(elapsed_time)/nullif(sum(rows_processed),0)/1E6,'SECOND') as interval day(1) to second(3)) as "Avg time/row"

, cast

( case

when sum(elapsed_time)/nullif(sum(rows_processed),0) < 2147483647 then -- 2**31 -1, limit for 32 bit integers and a

numtodsinterval(sum(elapsed_time)/nullif(sum(rows_processed),0),'SECOND')

else numtodsinterval(sum(elapsed_time/3600)/nullif(sum(rows_processed),0),'HOUR')

end -- Avoid "ORA-01873: the leading precision of the interval is too small" for large values

as interval day(5) to second(0)) as "Avg time/1M rows"

, sum(px_servers_executions) as "PX server executions"

from ( select s.sql_id sql_id

, trunc(s.last_active_time) as exec_date

, plan_hash_value

--, executions

, case executions when 0 then 1 else executions end as executions_calc -- to use in expressions without NULLIF

, px_servers_executions

, elapsed_time

, buffer_gets

, rows_processed

, end_of_fetch_count

from gv$sqlstats s

union

select s.sql_id

, trunc(cast(h.begin_interval_time as date)) as exec_date

, plan_hash_value

--, executions_delta executions

, nullif(executions_delta,0) executions_calc

, px_servers_execs_delta as px_servers_executions

, elapsed_time_delta as elapsed_time

, buffer_gets_delta as buffer_gets

, rows_processed_delta as rows_processed

, end_of_fetch_count_delta as end_of_fetch_count

from dba_hist_sqlstat s

join dba_hist_snapshot h on h.snap_id = s.snap_id and h.dbid = s.dbid and h.instance_number = s.instance_number

)

where 1=1

and sql_id = '&sql_id'

group by sql_id,plan_hash_value

having sum(executions_calc) > 0

order by plan_hash_value;

set linesize 700 pagesize 300

col "Avg time/1M rows" for a15

col "Avg time/row" for a15

col "Total time" for a15

col "Average seconds" for 999999999999

col "Average time" for a15

col AVG_TIME for a15

col AVG_TIME_PER_ROW for a15

select

--h.snap_id,

s.sql_id ,case when s.plan_hash_value =

( select plan_hash_value from

( select plan_hash_value, row_number() over (order by timestamp desc) as seq

from gv$sql_plan p

where p.sql_id = '&sql_id'

-- and p.child_number = :sql_child_number

-- and p.inst_id = :instance

)

where seq = 1 )

then 'Y'

end as current_plan

, trunc(cast (h.begin_interval_time as date)) as exec_date

, plan_hash_value

, sum(executions_delta) executions

, cast(numtodsinterval(sum(elapsed_time_delta)/1E6,'SECOND') as interval day(1) to second(2)) as total_time

, cast(numtodsinterval(sum(elapsed_time_delta)/ nullif(sum(executions_delta),0)/1E6,'SECOND') as interval day(1) to second(1)) as avg_time

, sum(buffer_gets_delta) buffer_gets

, round(sum(buffer_gets_delta)/nullif(sum(executions_delta),0)) as buffer_gets_per_exec

, sum(rows_processed_delta) as "ROWS"

, round(sum(rows_processed_delta) / nullif(sum(executions_delta),0),1) as rows_per_exec

, cast(numtodsinterval(sum(elapsed_time_delta)/nullif(sum(rows_processed_delta),0)/1E6,'SECOND') as interval day(1) to second(3)) as avg_time_per_row

, round(sum(buffer_gets_delta)/nullif(sum(rows_processed_delta),0)) as buffer_gets_per_row

from dba_hist_sqlstat s

join dba_hist_snapshot h on h.snap_id = s.snap_id and h.dbid = s.dbid

where s.sql_id = '&sql_id'

group by

--h.snap_id,

s.sql_id ,trunc(cast (h.begin_interval_time as date)), s.plan_hash_value

order by trunc(cast (h.begin_interval_time as date)) desc, 1 nulls last, s.plan_hash_value

;

==============

set linesize 700 pagesize 300

col "Avg time/1M rows" for a15

col "Avg time/row" for a15

col "Total time" for a15

col "Average seconds" for 999999999999

col "Average time" for a15

col PLSQL_EXEC_TIME for a15

col TOTAL_TIME for a15

col JAVA_EXEC_TIME for a15

col AVERAGE_TIME for a15

select sql_id

, plan_hash_value

, last_active_time

, executions

, round(100 * parse_calls/nullif(executions,0),1) as "Parsed%"

, parse_calls

, cast(numtodsinterval(elapsed_time/1E6,'SECOND') as interval day(0) to second(0)) as total_time

, cast(numtodsinterval(elapsed_time / nullif(executions,0) / 1E6,'SECOND') as interval day(0) to second(0)) as average_time

, round(100 * cpu_time / elapsed_time,1) "CPU%"

, round(100 * user_io_wait_time / elapsed_time,1) "IO%"

, round(100 * concurrency_wait_time / elapsed_time,1) "CONCURRRENCY%"

, round(100 * application_wait_time / elapsed_time,1) "APPLICATION%"

, round(100 * plsql_exec_time / elapsed_time,1) "PL/SQL%"

, buffer_gets buffer_gets_total

, round(buffer_gets / nullif(executions,0)) as buffer_gets_per_exec

, disk_reads

, round(rows_processed / nullif(fetches,0),1) as rows_per_fetch

, round(rows_processed / nullif(executions,0),1) as rows_per_exec

, direct_writes

, rows_processed

, fetches

, end_of_fetch_count

, loads

, version_count

, invalidations

, px_servers_executions

, round(avg_hard_parse_time / 1E6,2) as avg_hard_parse_secs

, cluster_wait_time

, cast(numtodsinterval(plsql_exec_time/1E6,'SECOND') as interval day(0) to second(0)) as plsql_exec_time

, cast(numtodsinterval(java_exec_time/1E6,'SECOND') as interval day(0) to second(0)) as java_exec_time

, sorts

, sharable_mem

, total_sharable_mem

, last_active_child_address

, serializable_aborts

from gv$sqlstats s

where s.sql_id = '&sql_id'

;

with parsing schema

set linesize 300 pagesize 200

column_object_name for a31

column owner for a15

column "%TOT%" for a6

column SQL_TEXT for a80 wrap on

col parsing_schema_name for A12

select distinct sql_id1,sub.parsing_schema_name,

-- replace(replace(replace(DBMS_LOB.SUBSTR(DHST.sql_text,4000,1),chr(10),' '),chr(9),' '),' ',' ') SQL_TEXT,

sub.EXECUTIONS,round((sub.EXECUTIONS/sub2.EXECUTIONS_TOTAL)*100)||'%' "%TOT%",

sub.DISK_READS,round((sub.DISK_READS/sub2.DISK_READS_TOTAL)*100)||'%' "%TOT%",

sub.BUFFER_GETS,round((sub.BUFFER_GETS/sub2.BUFFER_GETS_TOTAL)*100)||'%' "%TOT%",

sub.ELAPSED_TIME,round((sub.ELAPSED_TIME/sub2.ELAPSED_TIME_TOTAL)*100)||'%' "%TOT%",

sub.IOWAIT,sub.ROWS_PROCESSED,

sub.SEC_PER_EXEC "SEC/EXE",

round((sub.EXECUTIONS/sub2.EXECUTIONS_TOTAL)*100)+round((sub.DISK_READS/sub2.DISK_READS_TOTAL)*100)+round((sub.BUFFER_GETS/sub2.BUFFER_GETS_TOTAL)*100)+round((sub.ELAPSED_TIME/sub2.ELAPSED_TIME_TOTAL)*100) RANK ,

replace(replace(replace(DBMS_LOB.SUBSTR(DHST.sql_text,4000,1),chr(10),' '),chr(9),' '),' ',' ') SQL_TEXT

from DBA_HIST_SQLTEXT DHST,

(

select distinct

SQL_ID sql_id1,

PARSING_SCHEMA_NAME,

round(sum(EXECUTIONS_DELTA)) as EXECUTIONS,

round(sum(PARSE_CALLS_DELTA)) as PARSE_CALLS,

round(sum(DISK_READS_DELTA)) as DISK_READS,

round(sum(BUFFER_GETS_DELTA)) as BUFFER_GETS,

round(sum(ROWS_PROCESSED_DELTA)) as ROWS_PROCESSED,

round(sum(CPU_TIME_DELTA/1000000)) as CPU_TIME,

round(sum(ELAPSED_TIME_DELTA/1000000)) ELAPSED_TIME,

round(sum(IOWAIT_DELTA)/1000000) as IOWAIT,

sum(ELAPSED_TIME_DELTA/1000000)/decode(sum(EXECUTIONS_DELTA),0,1,sum(EXECUTIONS_DELTA)) SEC_PER_EXEC

from

dba_hist_snapshot

natural join

dba_hist_sqlstat DHS

natural join

dba_hist_sql_plan DHSP

where 1=1

and SQL_ID='&sql_id'

group by SQL_ID,PARSING_SCHEMA_NAME

) sub,

(

select decode(round(sum(EXECUTIONS_DELTA)),0,1,round(sum(EXECUTIONS_DELTA))) as EXECUTIONS_TOTAL,

decode(round(sum(DISK_READS_DELTA)),0,1,round(sum(DISK_READS_DELTA))) as DISK_READS_TOTAL,

decode(round(sum(BUFFER_GETS_DELTA)),0,1,round(sum(BUFFER_GETS_DELTA))) as BUFFER_GETS_TOTAL,

decode(round(sum(ELAPSED_TIME_DELTA/1000000)),0,1,round(sum(ELAPSED_TIME_DELTA/1000000))) as ELAPSED_TIME_TOTAL

from

dba_hist_snapshot

natural join

dba_hist_sqlstat DHS

natural join

dba_hist_sql_plan DHSP

where

1=1

) sub2

where DHST.sql_id = sub.sql_id1

order by RANK Desc

col NAME for a20

col VALUE_STRING for a20

select name, datatype_string, last_captured, value_string

from ( select name

, datatype_string

, last_captured

, value_string

, row_number() over(partition by position order by last_captured nulls last) as seq

from gv$sql_bind_capture

where sql_id = '&sql_id'

-- and child_number = :sql_child_number

--and address = :sql_address

-- and hash_value = :sql_hash_value

)

where seq = 1

order by last_captured

;

--=============

select * from

(select sql_id, nonshared_reason, count(*) from gv$sql_shared_cursor

unpivot

(nonshared_value for nonshared_reason in (

UNBOUND_CURSOR as 'UNBOUND_CURSOR',

SQL_TYPE_MISMATCH as 'SQL_TYPE_MISMATCH',

OPTIMIZER_MISMATCH as 'OPTIMIZER_MISMATCH',

OUTLINE_MISMATCH as 'OUTLINE_MISMATCH',

STATS_ROW_MISMATCH as 'STATS_ROW_MISMATCH',

LITERAL_MISMATCH as 'LITERAL_MISMATCH',

FORCE_HARD_PARSE as 'FORCE_HARD_PARSE',

EXPLAIN_PLAN_CURSOR as 'EXPLAIN_PLAN_CURSOR',

BUFFERED_DML_MISMATCH as 'BUFFERED_DML_MISMATCH',

PDML_ENV_MISMATCH as 'PDML_ENV_MISMATCH',

INST_DRTLD_MISMATCH as 'INST_DRTLD_MISMATCH',

SLAVE_QC_MISMATCH as 'SLAVE_QC_MISMATCH',

TYPECHECK_MISMATCH as 'TYPECHECK_MISMATCH',

AUTH_CHECK_MISMATCH as 'AUTH_CHECK_MISMATCH',

BIND_MISMATCH as 'BIND_MISMATCH',

DESCRIBE_MISMATCH as 'DESCRIBE_MISMATCH',

LANGUAGE_MISMATCH as 'LANGUAGE_MISMATCH',

TRANSLATION_MISMATCH as 'TRANSLATION_MISMATCH',

BIND_EQUIV_FAILURE as 'BIND_EQUIV_FAILURE',

INSUFF_PRIVS as 'INSUFF_PRIVS',

INSUFF_PRIVS_REM as 'INSUFF_PRIVS_REM',

REMOTE_TRANS_MISMATCH as 'REMOTE_TRANS_MISMATCH',

LOGMINER_SESSION_MISMATCH as 'LOGMINER_SESSION_MISMATCH',

INCOMP_LTRL_MISMATCH as 'INCOMP_LTRL_MISMATCH',

OVERLAP_TIME_MISMATCH as 'OVERLAP_TIME_MISMATCH',

EDITION_MISMATCH as 'EDITION_MISMATCH',

MV_QUERY_GEN_MISMATCH as 'MV_QUERY_GEN_MISMATCH',

USER_BIND_PEEK_MISMATCH as 'USER_BIND_PEEK_MISMATCH',

TYPCHK_DEP_MISMATCH as 'TYPCHK_DEP_MISMATCH',

NO_TRIGGER_MISMATCH as 'NO_TRIGGER_MISMATCH',

FLASHBACK_CURSOR as 'FLASHBACK_CURSOR',

ANYDATA_TRANSFORMATION as 'ANYDATA_TRANSFORMATION',

PDDL_ENV_MISMATCH as 'PDDL_ENV_MISMATCH',

TOP_LEVEL_RPI_CURSOR as 'TOP_LEVEL_RPI_CURSOR',

DIFFERENT_LONG_LENGTH as 'DIFFERENT_LONG_LENGTH',

LOGICAL_STANDBY_APPLY as 'LOGICAL_STANDBY_APPLY',

DIFF_CALL_DURN as 'DIFF_CALL_DURN',

BIND_UACS_DIFF as 'BIND_UACS_DIFF',

PLSQL_CMP_SWITCHS_DIFF as 'PLSQL_CMP_SWITCHS_DIFF',

CURSOR_PARTS_MISMATCH as 'CURSOR_PARTS_MISMATCH',

STB_OBJECT_MISMATCH as 'STB_OBJECT_MISMATCH',

CROSSEDITION_TRIGGER_MISMATCH as 'CROSSEDITION_TRIGGER_MISMATCH',

PQ_SLAVE_MISMATCH as 'PQ_SLAVE_MISMATCH',

TOP_LEVEL_DDL_MISMATCH as 'TOP_LEVEL_DDL_MISMATCH',

MULTI_PX_MISMATCH as 'MULTI_PX_MISMATCH',

BIND_PEEKED_PQ_MISMATCH as 'BIND_PEEKED_PQ_MISMATCH',

MV_REWRITE_MISMATCH as 'MV_REWRITE_MISMATCH',

ROLL_INVALID_MISMATCH as 'ROLL_INVALID_MISMATCH',

OPTIMIZER_MODE_MISMATCH as 'OPTIMIZER_MODE_MISMATCH',

PX_MISMATCH as 'PX_MISMATCH',

MV_STALEOBJ_MISMATCH as 'MV_STALEOBJ_MISMATCH',

FLASHBACK_TABLE_MISMATCH as 'FLASHBACK_TABLE_MISMATCH',

LITREP_COMP_MISMATCH as 'LITREP_COMP_MISMATCH',

PLSQL_DEBUG as 'PLSQL_DEBUG',

LOAD_OPTIMIZER_STATS as 'LOAD_OPTIMIZER_STATS',

ACL_MISMATCH as 'ACL_MISMATCH',

FLASHBACK_ARCHIVE_MISMATCH as 'FLASHBACK_ARCHIVE_MISMATCH',

LOCK_USER_SCHEMA_FAILED as 'LOCK_USER_SCHEMA_FAILED',

REMOTE_MAPPING_MISMATCH as 'REMOTE_MAPPING_MISMATCH',

LOAD_RUNTIME_HEAP_FAILED as 'LOAD_RUNTIME_HEAP_FAILED',

HASH_MATCH_FAILED as 'HASH_MATCH_FAILED',

PURGED_CURSOR as 'PURGED_CURSOR',

BIND_LENGTH_UPGRADEABLE as 'BIND_LENGTH_UPGRADEABLE',

USE_FEEDBACK_STATS as 'USE_FEEDBACK_STATS'

))

where nonshared_value = 'Y'

group by sql_id, nonshared_reason

)

where 1=1

--and sql_id = :sql_id

and rownum <30

order by 3 desc

SQL_ID NONSHARED_REASON COUNT(*)

------------- ----------------------------- ----------

01xv155rhts3j ROLL_INVALID_MISMATCH 17

03gumnj26cbhx ROLL_INVALID_MISMATCH 11

02mh01mykt89k ROLL_INVALID_MISMATCH 9

062savj8zgzut ROLL_INVALID_MISMATCH 7

05tfhbgcrxa1t ROLL_INVALID_MISMATCH 5

04ws1hx445575 ROLL_INVALID_MISMATCH 5

04g72vx2dm3hj ROLL_INVALID_MISMATCH 5

04kug40zbu4dm OPTIMIZER_MISMATCH 4

---

define sql_id ='8m0g708ggsfsm'

set serveroutput on

DECLARE

v_count number;

v_sql varchar2(500);

v_sql_id varchar2(30) := '&sql_id';

BEGIN

v_sql_id := lower(v_sql_id);

dbms_output.put_line(chr(13)||chr(10));

dbms_output.put_line('sql_id: '||v_sql_id);

dbms_output.put_line('------------------------');

FOR c1 in

(select column_name

from dba_tab_columns

where table_name ='V_$SQL_SHARED_CURSOR'

and column_name not in ('SQL_ID', 'ADDRESS', 'CHILD_ADDRESS', 'CHILD_NUMBER', 'REASON', 'CON_ID')

order by column_id)

LOOP

v_sql := 'select count(*) from V_$SQL_SHARED_CURSOR

where sql_id='||''''||v_sql_id||''''||'

and '||c1.column_name||'='||''''||'Y'||'''';

execute immediate v_sql into v_count;

IF v_count > 0

THEN

dbms_output.put_line(' - '||rpad(c1.column_name,30)||' count: '||v_count);

END IF;

END LOOP;

END;

11.2.0.3: _cursor_obsolete_threshold=100

11.2.0.4: _cursor_obsolete_threshold=1024

12.1.0.1: _cursor_obsolete_threshold=1024

12.1.0.2: _cursor_obsolete_threshold=1024

12.2.0.1: _cursor_obsolete_threshold=8192 <<<<< _cursor_obsolete_threshold 1024 OD

18.3.0: _cursor_obsolete_threshold=8192

_cursor_obsolete_threshold

http://anuj-singh.blogspot.com/2021/05/sql-history.html

-- tab=Executions (historical)

set linesize 500

col "Run date" for a25

select run_date as "Run date"

, sql_id

, "First"

, "Last"

, plan_hash_value as "Plan hash"

, executions as "Times called"

, end_of_fetch_count as "Times completed"

, success_rate as "Success %"

, elapsed_time as "Total time"

, avg_time as "Avg time"

, avg_s as "Avg seconds"

, round(avg_s * (sum(avg_rows) over() / greatest(sum(avg_s) over(),1))) as "Avg s scaled to rows" -- for charting time vs rows

, avg_rows as "Avg rows"

, avg_bg as "Avg Buffer gets"

, bg_per_row as "Buffer gets/row"

, avg_time_per_row as "Time/row"

, px_servers_execs as "PX server executions"

from

( select s.sql_id,trunc(cast(t.begin_interval_time as date)) as run_date

, plan_hash_value

, to_char(min(cast(t.begin_interval_time as date)),'HH24:MI:SS') as "First"

, to_char(max(cast(t.end_interval_time as date)),'HH24:MI:SS') as "Last"

, sum(s.executions_delta) as executions

, sum(s.end_of_fetch_count_delta) as end_of_fetch_count

, max(s.executions_total) as executions_total

, max(s.end_of_fetch_count_total) as end_of_fetch_count_total

, least(100, round(100 * max(s.end_of_fetch_count_total) / nullif(max(s.executions_total),0),1)) as success_rate

, cast(numtodsinterval(max(s.elapsed_time_total)/1E6,'SECOND') as interval day(1) to second(2)) as elapsed_time

, cast(numtodsinterval(max(s.elapsed_time_total)/1E6 / nvl(nullif(max(s.executions_total),0),1),'SECOND') as interval day(1) to second(1)) as avg_time

, round(max(s.elapsed_time_total)/1E6 / nvl(nullif(max(s.executions_total),0),1),1) as avg_s

, round(max(s.buffer_gets_total)/nullif(max(s.executions_total),0)) as avg_bg

, round(max(s.buffer_gets_total)/nullif(max(s.rows_processed_total),0)) as bg_per_row

, max(s.rows_processed_total) as rows_processed

, round(max(s.rows_processed_total) / nullif(max(s.executions_total),0)) as avg_rows

, cast(numtodsinterval(max(s.elapsed_time_total)/nullif(max(s.rows_processed_total),0)/1E6,'SECOND') as interval day(1) to second(3)) as avg_time_per_row

, max(s.elapsed_time_total)/nullif(max(s.rows_processed_total),0)/1E6 as avg_s_per_row

, max(s.px_servers_execs_total) as px_servers_execs

from dba_hist_sqlstat s

join dba_hist_snapshot t on t.snap_id = s.snap_id and t.dbid = s.dbid and t.instance_number = s.instance_number

where 1=1

and sql_id = '&sql_id'

and rownum <10

group by sql_id,trunc(cast(t.begin_interval_time as date)), s.plan_hash_value )

order by 1, 2, plan_hash_value;

-- tab=Executions (recent)

set linesize 1000

col Average time" for a20

col "Average time PX" for a20

col "Average time" for a20

col "Total time" for a20

select trunc(last_active_time) as "Run date"

, to_char(min(last_active_time),'HH24:MI:SS') as "First"

, to_char(max(last_active_time),'HH24:MI:SS') as "Last"

, plan_hash_value as "Plan hash"

, sum(executions_calc) as "Times called"

, sum(end_of_fetch_count) as "Times completed"

, least(100, round(100 * sum(end_of_fetch_count) / sum(executions_calc),1)) as "Success %"

, cast(numtodsinterval(sum(elapsed_time)/1E6,'SECOND') as interval day(1) to second(2)) as "Total time"

-- , round(sum(elapsed_time)/1e6) as "Total seconds"

, round(sum(elapsed_time)/1E6 / nvl(sum(executions_calc),1),1) as "Average (s)"

, round(sum(elapsed_time)/1E6 / nvl(sum(executions_calc),1)/nvl(nullif(sum(px_servers_executions),0),1)) as "Average (s) PX"

, cast(numtodsinterval(sum(elapsed_time)/1E6 / nvl(sum(executions_calc),1),'SECOND') as interval day(1) to second(1)) as "Average time"

, cast(numtodsinterval(sum(elapsed_time)/1E6 / nvl(sum(executions_calc),1) /nvl(nullif(sum(px_servers_executions),0),1),'SECOND') as interval day(1) to second(1)) as "Average time PX"

-- , sum(buffer_gets) as "Buffer gets"

, round(sum(buffer_gets)/sum(executions_calc)) as "Buffer gets/exec"

, round(sum(buffer_gets)/nullif(sum(rows_processed),0)) as "Buffer gets/row"

, sum(rows_processed) as "Rows"

, round(sum(rows_processed) / sum(executions_calc)) as "Rows/exec"

, cast(numtodsinterval(sum(elapsed_time)/nullif(sum(rows_processed),0)/1E6,'SECOND') as interval day(1) to second(3)) as "Avg time/row"

, cast

( case

when sum(elapsed_time)/nullif(sum(rows_processed),0) < 2147483647 then -- 2**31 -1, limit for 32 bit integers

numtodsinterval(sum(elapsed_time)/nullif(sum(rows_processed),0),'SECOND')

else numtodsinterval(sum(elapsed_time/3600)/nullif(sum(rows_processed),0),'HOUR')

end -- Avoid "ORA-01873: the leading precision of the interval is too small" for large values

as interval day(5) to second(0)) as "Avg time/1M rows"

, sum(px_servers_executions) as "PX server executions"

from ( select s.sql_id

, s.last_active_time

, plan_hash_value

--, executions

, case executions when 0 then 1 else executions end as executions_calc -- to use in expressions without NULLIF

, px_servers_executions

, elapsed_time

, buffer_gets

, rows_processed

, end_of_fetch_count

from gv$sqlstats s )

where 1=1

and sql_id = '&sql_id'

group by trunc(last_active_time), plan_hash_value

having sum(executions_calc) > 0

order by trunc(last_active_time), plan_hash_value;

-- tab=Recent ASH history

set linesize 500

col TOP_LEVEL_CALL for a50

col CURRENT_PROCEDURE for a70 wrap

select ash.sql_id, ash.sql_exec_start, ash.sql_exec_id, ash.sql_plan_hash_value, ash.sql_child_number

, cast(numtodsinterval(count(*),'SECOND') as interval day(0) to second(0)) as elapsed

, ash.qc_session_id, ash.session_id, ash.session_serial#

-- , sum(io.block_gets) as block_gets, sum(io.consistent_gets) as consistent_gets, sum(io.physical_reads) as physical_reads, sum(io.consistent_changes) as consistent_changes

, tls.sql_text as top_level_call

, rtrim(p.owner ||'.'|| p.object_name ||'.'|| p.procedure_name,'.') as current_procedure

from gv$active_session_history ash

left join dba_procedures p on p.object_id = ash.plsql_object_id and nvl(p.subprogram_id,.5) = nvl(ash.plsql_subprogram_id,.5)

left join gv$sqlstats tls on tls.sql_id = ash.top_level_sql_id

where 1=1

and ash.sql_id = '&sql_id'

-- and ash.sql_exec_id is not null

and rownum<10

group by ash.sql_id, ash.sql_exec_start,ash.sql_plan_hash_value, ash.sql_child_number, ash.qc_session_id, ash.session_id, ash.session_serial#, ash.sql_exec_id

, tls.sql_text , p.owner, p.object_name, p.procedure_name

order by min(ash.sample_time), ash.sql_exec_start, ash.sql_exec_id, ash.qc_session_id nulls first;

-- tab=Recent ASH history with wait objs

set linesize 500

col USERNAME for a25

col WAIT_OBJECT for a15

col ELAPSED for a25

select ash.sql_id, ash.sql_exec_start, ash.sql_exec_id, ash.sql_plan_hash_value

, ash.session_id, ash.session_serial#, u.username

, case when current_obj# > 0 then

( select distinct o.owner || '.' || o.object_name || rtrim('.' || o.subobject_name,'.')

from dba_objects o where o.object_id = current_obj# )

end as wait_object

, round(100*(ratio_to_report(count(*)) over())) as percent

, cast(numtodsinterval(count(*),'SECOND') as interval day(0) to second(0)) as elapsed

, sum(ash.delta_read_io_bytes) as read_bytes

, sum(ash.delta_write_io_requests) as write_bytes

, ash.sql_child_number, ash.qc_session_id

from gv$active_session_history ash

join dba_users u on u.user_id = ash.user_id

where 1=1

and ash.sql_id = '&sql_id'

and ash.sql_exec_id is not null

and rownum<10

group by ash.sql_id, ash.sql_exec_start,ash.sql_plan_hash_value, ash.sql_child_number, ash.qc_session_id, ash.session_id, ash.session_serial#, u.username, ash.sql_exec_id, ash.current_obj#

order by ash.sql_exec_start;

-- tab=SQL Stats

set linesize 1000

col PARSING_SCHEMA_NAME for a20

col SQL_TEXT for a70 wrap

col SOURCE for a20

col TOTAL_TIME for a15

col AVG_TIME for a15

col PLSQL_EXEC_TIME for a15

col JAVA_EXEC_TIME for a15

select sql_id

, s.child_number

, s.parsing_schema_name

, rtrim(ltrim(o.owner || '.' || o.object_name || '.', '.') ||

( select regexp_substr(min(ltrim(upper(sp1.text))) keep (dense_rank first order by sp1.line desc),'[^( ]+',1,2)

from dba_source sp1

where sp1.owner = o.owner and sp1.name = o.object_name and sp1.type = o.object_type

and sp1.line < s.program_line#

and regexp_like(ltrim(upper(sp1.text)),'^(PROCEDURE|FUNCTION)\s') )

, '.') as source

, s.program_line# as source_line

, plan_hash_value

, last_active_time

, executions

, parse_calls

, least(100, round(100 * parse_calls/nvl(nullif(executions,0),1),1)) as "Parsed%"

, cast(numtodsinterval(elapsed_time/1E6,'SECOND') as interval day(0) to second(0)) as total_time

, cast(numtodsinterval(elapsed_time / nvl(nullif(executions,0),1) / 1E6,'SECOND') as interval day(0) to second(4)) as avg_time

, round(100 * cpu_time / nullif(elapsed_time,0),1) "CPU%"

, round(100 * user_io_wait_time / nullif(elapsed_time,0),1) "IO%"

, round(100 * concurrency_wait_time / nullif(elapsed_time,0),1) "CONCURRRENCY%"

, round(100 * application_wait_time / nullif(elapsed_time,0),1) "APPLICATION%"

, round(100 * plsql_exec_time / nullif(elapsed_time,0),1) "PL/SQL%"

, buffer_gets buffer_gets_total

, round(buffer_gets / nvl(nullif(executions,0),1)) as buffer_gets_per_exec

, disk_reads

, round(rows_processed / nullif(fetches,0),1) as rows_per_fetch

, round(rows_processed / nvl(nullif(executions,0),1),1) as rows_per_exec

, direct_writes

, rows_processed

, fetches

, end_of_fetch_count

, loads

, s.loaded_versions version_count

, invalidations

, px_servers_executions

, cluster_wait_time

, cast(numtodsinterval(plsql_exec_time/1E6,'SECOND') as interval day(0) to second(0)) as plsql_exec_time

, cast(numtodsinterval(java_exec_time/1E6,'SECOND') as interval day(0) to second(0)) as java_exec_time

, sorts

, sharable_mem

, serializable_aborts

, sql_text

from gv$sql s

left join dba_objects o on o.object_id = s.program_id

where 1=1

--and rownum<10

and sql_id = '&sql_id'

;

-- sql text

set linesize 300 pagesize 300

col SQL_TEXT for a100 wrap

select *

from (

select sql_id,address

, hash_value

, count(*) cnt,

max(decode(piece,0,sql_text))||

max(decode(piece,1,sql_text))||

max(decode(piece,2,sql_text))||

max(decode(piece,3,sql_text))||

max(decode(piece,4,sql_text))||

max(decode(piece,5,sql_text))||

max(decode(piece,6,sql_text))||

max(decode(piece,7,sql_text))||

max(decode(piece,8,sql_text))||

max(decode(piece,9,sql_text))||

max(decode(piece,10,sql_text))||

max(decode(piece,11,sql_text))||

max(decode(piece,12,sql_text))||

max(decode(piece,13,sql_text))||

max(decode(piece,14,sql_text))||

max(decode(piece,15,sql_text))||

max(decode(piece,16,sql_text))||

max(decode(piece,17,sql_text))||

max(decode(piece,18,sql_text))||

max(decode(piece,19,sql_text))||

max(decode(piece,20,sql_text))||

max(decode(piece,21,sql_text))||

max(decode(piece,22,sql_text))||

max(decode(piece,23,sql_text))||

max(decode(piece,24,sql_text) )||

max(decode(piece,25,sql_text))||

max(decode(piece,26,sql_text))||

max(decode(piece,27,sql_text))||

max(decode(piece,28,sql_text))||

max(decode(piece,29,sql_text))||

max(decode(piece,30,sql_text))||

max(decode(piece,31,sql_text))||

max(decode(piece,32,sql_text))||

max(decode(piece,33,sql_text))||

max(decode(piece,34,sql_text))||

max(decode(piece,35,sql_text))||

max(decode(piece,36,sql_text))||

max(decode(piece,37,sql_text))||

max(decode(piece,38,sql_text))||

max(decode(piece,39,sql_text))||

max(decode(piece,40,sql_text)) ||

max(decode(piece,41,sql_text))||

max(decode(piece,42,sql_text))||

max(decode(piece,43,sql_text))||

max(decode(piece,44,sql_text))||

max(decode(piece,45,sql_text))||

max(decode(piece,46,sql_text))||

max(decode(piece,47,sql_text)) ||

max(decode(piece,48,sql_text))||

max(decode(piece,49,sql_text) )||

max(decode(piece,50,sql_text))||

max(decode(piece,51,sql_text))||

max(decode(piece,52,sql_text))||

max(decode(piece,53,sql_text))||

max(decode(piece,54,sql_text))||

max(decode(piece,55,sql_text))||

max(decode(piece,56,sql_text))||

max(decode(piece,57,sql_text))||

max(decode(piece,58,sql_text))||

max(decode(piece,59,sql_text) )||

max(decode(piece,60,sql_text) )||

max(decode(piece,61,sql_text)

--)||

/* max(decode(piece,62,sql_text)

) sql_text

from gv$sqltext

where 1=1

and sql_id='&sql_id'

group by sql_id,address , hash_value

order by 3 desc

)

where rownum = 1



CREATE OR REPLACE FUNCTION GET_CLOB (srcclob in clob ,s in integer, e in integer)
   RETURN CLOB
   IS
       newclob clob ;
       l_buffer VARCHAR2 (32767);
       l_amount integer := 24000;
         dev integer;
         md integer;    
    BEGIN
       dev:=floor((e-s+1)/l_amount);
       md:=mod(e-s+1,l_amount);
       dbms_output.put_line('DEV:' || dev || ' MOD:' || md);
       dbms_output.put_line('e:' || e || ' s:' || s);
       FOR i in 1 .. dev LOOP
       newclob:=newclob || dbms_lob.substr(srcclob,l_amount,(i-1)*l_amount +s);
       END LOOP ;
       newclob:=newclob || dbms_lob.substr(srcclob,md,dev*l_amount+s);
       RETURN(newclob);
    END;
    /
	
	
	-- Select GET_CLOB( <CLOB_COLUMN> , 1 , dbms_lob.getlength(<CLOB_COLUMN>))  from Table_name ;
	

set long 10000 linesize 300  longchunksize 30000
Select sql_id,GET_CLOB( sql_fulltext , 1 , dbms_lob.getlength(sql_fulltext)) sql_text  from gv$sql where sql_id in ('c8bj35pys60g5','72y7hnnquc17u','g9m7yw866z4d4','6a82k6u4sbaav');
	
define sql_id='6a82k6u4sbaav'
set long 10000 linesize 300  longchunksize 30000
Select GET_CLOB( sql_fulltext , 1 , dbms_lob.getlength(sql_fulltext)) sql_text from gv$sql where sql_id in ('&sql_id'); 
	

drop FUNCTION GET_CLOB ;

from https://asktom.oracle.com/pls/apex/f?p=100:11::::RP:P11_QUESTION_ID:9539494200346328435


set pagesize 0
with data as 
 ( select sql_fulltext, dbms_lob.getlength(sql_fulltext) len from gv$sql 
where 1=1
--and sql_text like '%ayyyyyyyyyyyyyyyyyyyy%'
and sql_id='c93a0ckz9rh38'
)
select
  dbms_lob.substr(sql_fulltext,4000,1) piece1,
  case when len > 4000 then dbms_lob.substr(sql_fulltext,4000,4001) end piece2,
  case when len > 8000 then dbms_lob.substr(sql_fulltext,4000,8001) end piece3,
  case when len > 12000 then dbms_lob.substr(sql_fulltext,4000,12001) end piece4,
  case when len > 16000 then dbms_lob.substr(sql_fulltext,4000,165001) end piece5
from data
;
set pagesize 80

-- search text 

set pagesize 0
with data as 
 ( select sql_fulltext, dbms_lob.getlength(sql_fulltext) len from gv$sql 
where 1=1
--and sql_id='c93a0ckz9rh38'
and ( sql_text like '%I_OBJ#%' or sql_text like '%I_OBJ#%' )
and    sql_text not like '%select sql_id, sql_text from gv$sql%'
)
select
  dbms_lob.substr(sql_fulltext,4000,1) piece1,
  case when len > 4000 then dbms_lob.substr(sql_fulltext,4000,4001) end piece2,
  case when len > 8000 then dbms_lob.substr(sql_fulltext,4000,8001) end piece3,
  case when len > 12000 then dbms_lob.substr(sql_fulltext,4000,12001) end piece4,
  case when len > 16000 then dbms_lob.substr(sql_fulltext,4000,165001) end piece5
from data
;
set pagesize 80

====with sql _id

set pagesize 0
with data as 
 ( select sql_id,sql_fulltext, dbms_lob.getlength(sql_fulltext) len from gv$sql 
where 1=1
--and sql_id='c93a0ckz9rh38'
and ( sql_text like '%MERGE INTO %' or sql_text like '%MERGE INTO %' )
and    sql_text not like '%select sql_id, sql_text from gv$sql%'
)
select
sql_id,
  dbms_lob.substr(sql_fulltext,4000,1) piece1,
  case when len > 4000 then dbms_lob.substr(sql_fulltext,4000,4001) end piece2,
  case when len > 8000 then dbms_lob.substr(sql_fulltext,4000,8001) end piece3,
  case when len > 12000 then dbms_lob.substr(sql_fulltext,4000,12001) end piece4,
  case when len > 16000 then dbms_lob.substr(sql_fulltext,4000,165001) end piece5
from data
;
set pagesize 80



set lines 400
undef sql_id
define sql_id='9zg9qd9bm4spu'

col apwpx               format 99999.999        head 'AppWms|PerX'
col bgpx                format 999999999        head 'BGets|PerX'
col conw                format 99999.999        head 'Conwms|PerX'
col cpx                 format 99999999.999     head 'CPUms|PerX'
col drpx                format 9999999.99       head 'DReads|PerX'
col fetchx              format 99999            head 'Fetchs|PerX'
col sortx               format 999              head 'Sorts|PerX'
col elpx                format 99999999.999     head 'Elapms|PerX'
col exec                format 999999999999     head 'Execs'
col iowpx               format 99999999.999     head 'IOWms|PerX'
col latime              format a11              head 'Last Active'
col lltime              format a14              head 'Last Load'
col maxsnapid           format 999999           head 'Max|SnapId'
col m                   format a01      trunc
col minsnapid           format 999999           head 'Min|SnapId'
col module              format a14      trunc   head 'Module'
col o                   format a01              head 'O'                trunc
col opt_mode            format a08      trunc   head 'Opt|Mode'         trunc
col parse_usr           format a08              head 'ParsUser'         trunc
col phash               format 9999999999       head 'PlanHash'
col phashp              format a12              head 'PlanHash   P'
col rwpx                format 999999.99        head 'RwsP|PerX'
col s_cn                format a07              head 'S:Child'          trunc
col sql_id              format a15              head 'SQL Id'
col sqltext             format a12      trunc   head 'Sql Text'
col ue                  format 999
col cpct                format 999              head 'CPU|Pct'          trunc
col ipct                format 999              head 'IO|Pct'           trunc
col btime               format a11              head 'Begin Time'
col smem                format 99999            head 'ShrMem|KB'

-- profile/baseline associated with sql_id
col name        format a26
col category    format a04              trunc
col created     format a14
col type        format a03              trunc
col status      format a03              trunc
col sql_text    format a80              trunc
col min_ago     format 9999             trunc   head 'Mins|Ago'
select   parsing_schema_name                                            parse_usr
        ,sql_id
,con_id
        ,lpad(plan_hash_value,10,' ')||' '||
         case when sql_profile is not null then 'P'
              when sql_plan_baseline is not null then 'B'
              else ' '
         end                                                            phashp
        --,is_bind_sensitive||is_bind_aware||is_shareable       pas
        --,optimizer_mode                                       m
        --,is_obsolete                                          o
        ,users_executing                                                ue
        ,substr(object_status,1,1)||':'||lpad(child_number,5,' ')       s_cn
       ,to_char(to_date(last_load_time,'YYYY-MM-DD/HH24:MI:SS'),'MMDD HH24MI')       lltime
        ,to_char(last_active_time,'MMDD HH24:MI')                       latime
        ,(sysdate-last_active_time)*1440                                min_ago
        ,decode(executions,0,1,executions)                              exec
        ,cpu_time/decode(executions,0,1,executions)/1000                cpx
        ,elapsed_time/decode(executions,0,1,executions)/1000            elpx
        ,(cpu_time/elapsed_time)*100                                    cpct
        ,buffer_gets/decode(executions,0,1,executions)                  bgpx
        ,user_io_wait_time/decode(executions,0,1,executions)/1000       iowpx
        ,(user_io_wait_time/elapsed_time)*100                           ipct
        ,disk_reads/decode(executions,0,1,executions)                   drpx
        ,application_wait_time/decode(executions,0,1,executions)/1000   apwpx
        ,concurrency_wait_time/decode(executions,0,1,executions)/1000   conw
        ,rows_processed/decode(executions,0,1,executions)               rwpx
        ,sorts/decode(executions,0,1,executions)                        sortx
        ,fetches/decode(executions,0,1,executions)                      fetchx
        ,module                                                         module
        ,replace(sql_text,chr(30))                                      sqltext
from     gv$sql
where    sql_id                 = '&&sql_id'
and      last_active_time       > sysdate-7
order by parsing_schema_name
        ,last_active_time
;

-- tab=Object stats

col OBJECT_OWNER for a20

col OBJECT_TYPE for a20

col PARTITION_START for a20

col OBJECT_NAME for a25

col OPERATION for a25

col PARTITION_STOP for a25

select * from (

with plan_objects as

( select --+ materialize

p.object_owner

, p.object_name

, p.object_type

, p.partition_start

, p.partition_stop

, p.cardinality

, p.operation

, p.options

, count(*) as occurs_in_plan

from gv$sql_plan_statistics_all p

where 1=1

and p.sql_id = '&sql_id'

and p.plan_hash_value =

( select plan_hash_value from

( select plan_hash_value, row_number() over (order by timestamp desc) as seq

from gv$sql_plan p

where 1=1

and p.sql_id = '&sql_id'

and p.inst_id = 1 )

where seq = 1 )

and p.object_type != 'VIEW'

group by p.object_owner, p.object_name, p.object_type, p.partition_start, p.partition_stop, p.cardinality, p.operation, p.options )

, object_stats as

( select ts.owner as object_owner

, ts.table_name as object_name

, ts.table_name as display_name

, ts.num_rows

, ts.blocks

, ts.last_analyzed

, ts.stale_stats

from dba_tab_statistics ts

where (ts.owner, ts.table_name) in

(select object_owner, object_name from plan_objects where object_type like 'TABLE%')

and ts.partition_name is null

union

select xs.owner

, xs.index_name

, '(' || xs.table_name || ') ' || index_name as display_name

, xs.num_rows

, xs.leaf_blocks as blocks

, xs.last_analyzed

, xs.stale_stats

from dba_ind_statistics xs

where (xs.owner, xs.index_name) in

(select object_owner, object_name from plan_objects where object_type like 'INDEX%')

and xs.partition_name is null

)

select --+ dynamic_sampling(8)

object_owner

, o.object_type

, nvl(s.display_name,object_name) as object_name

, s.stale_stats as "Stale?"

-- , o.occurs_in_plan

, o.operation || ' ' || o.options as operation

, o.cardinality

, s.num_rows as "Rows (global)"

, s.blocks

, s.last_analyzed

, o.partition_start

, o.partition_stop

from plan_objects o

left join object_stats s using(object_owner, object_name)

order by

case object_owner when 'SYS' then 2 else 1 end

, object_owner

, ltrim(object_name,'(')

);

===

set linesize 300

col SOURCE for a30

col BEGIN_TIME for a25

-- define sql_id='4wrkq5qmp8004'

SELECT *

FROM (SELECT '1.v$sql'||'Instance number:'||GV$SQL.inst_id source, SQL_ID,

plan_hash_value, TO_CHAR (FIRST_LOAD_TIME) begin_time, ' In the cursor cache' end_time, executions "No. of exec", (buffer_gets / executions) "LIO/exec", (cpu_time / executions / 1000000) "CPUTIM/exec",

(elapsed_time / executions / 1000000) "ETIME/exec ", (disk_reads / executions) "PIO/exec", (ROWS_PROCESSED / executions) "ROWs/exec"

FROM Gv$SQL

WHERE sql_id ='&sql_id'

UNION ALL

SELECT '2.sqltuning set' source, sql_id, plan_hash_value, 'JUST SQLSET NO DATE' begin_time, 'JUST SQLSET NO DATE' end_time,

EXECUTIONS "No. of exec", (buffer_gets / executions) "LIO/exec", (cpu_time / executions / 1000000) "CPUTIM/exec",

(elapsed_time / executions / 1000000) "ETIME/exec", (disk_reads / executions) "PIO/exec",

(ROWS_PROCESSED / executions) "ROWs/exec"

FROM dba_sqlset_statements

WHERE SQL_ID ='&sql_id'

UNION ALL

SELECT '3.dba_advisor_sqlstats' source, sql_id,

plan_hash_value,

'JUST SQLSET NO DATE' begin_time,

'JUST SQLSET NO DATE' end_time,

EXECUTIONS "No. of exec",

(buffer_gets / executions) "LIO/exec",

(cpu_time / executions / 1000000) "CPUTIM/exec",

(elapsed_time / executions / 1000000) "ETIME/exec",

(disk_reads / executions) "PIO/exec",

(ROWS_PROCESSED / executions) "ROWs/exec"

FROM dba_sqlset_statements

WHERE SQL_ID = '&sql_id'

UNION ALL

SELECT DISTINCT '4.dba_hist_sqlstat' ||'Instance number:' || SQL.INSTANCE_NUMBER source,

sql_id,

PLAN_HASH_VALUE,

TO_CHAR (s.BEGIN_INTERVAL_TIME ,'YYYY-MM-DD hh24:mi:ss') begin_time,

TO_CHAR (s. END_INTERVAL_TIME,'YYYY-MM-DD hh24:mi:ss') end_time,

SQL.executions_delta,

SQL.buffer_gets_delta / DECODE (NVL (SQL.executions_delta, 0), 0, 1, SQL.executions_delta) "LIO/exec",

(SQL.cpu_time_delta / 1000000) / DECODE (NVL (SQL.executions_delta, 0), 0, 1, SQL.executions_delta) "CPUTIM/exec",

(SQL.elapsed_time_delta / 1000000) / DECODE (NVL (SQL.executions_delta, 0), 0, 1, SQL .executions_delta) "ETIME/exec",

SQL.DISK_READS_DELTA / DECODE (NVL (SQL.executions_delta, 0), 0, 1, SQL.executions_delta) "PIO/exec",

SQL.ROWS_PROCESSED_DELTA / DECODE (NVL (SQL.executions_delta, 0), 0, 1, SQL.executions_delta) "ROWs/exec"

FROM dba_hist_sqlstat SQL, dba_hist_snapshot s

WHERE SQL.INSTANCE_NUMBER = s.INSTANCE_NUMBER

--AND SQL.dbid FROM v$database)

AND s.snap_id = SQL.snap_id

AND sql_id IN ('&sql_id')

)

ORDER BY source, begin_time DESC;

SOURCE SQL_ID PLAN_HASH_VALUE BEGIN_TIME END_TIME No. of exec LIO/exec CPUTIM/exec ETIME/exec PIO/exec ROWs/exec

------------------------------ ------------- --------------- ------------------------- -------------------- ----------- ---------- ----------- ----------- ---------- ----------

1.v$sqlInstance number:1 4wrkq5qmp8004 873175999 2021-09-12/11:10:05 In the cursor cache 1 14 .002351 .001692 0 29

===

SET TERMOUT OFF pagesize 5000 tab off verify off linesize 999 trimspool on trimout on null ""

SET TERMOUT ON

COL exec_per_sec FOR 99999990

COL ela_ms_per_sec FOR 99999990

COL rows_per_sec FOR 99999990

COL lios_per_sec FOR 99999990

COL blkrd_per_sec FOR 99999990

COL cpu_ms_per_sec FOR 99999990

COL iow_ms_per_sec FOR 99999990

COL clw_ms_per_sec FOR 99999990

COL apw_ms_per_sec FOR 99999990

COL ccw_ms_per_sec FOR 99999990

SELECT

CAST(begin_interval_time AS DATE) begin_interval_time

, sql_id

, plan_hash_value

, ROUND(SUM(executions_delta ) / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) exec_per_sec

, ROUND(SUM(elapsed_time_delta ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) ela_ms_per_sec

, ROUND(SUM(rows_processed_delta) / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) rows_per_sec

, ROUND(SUM(buffer_gets_delta ) / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) lios_per_sec

, ROUND(SUM(disk_reads_delta ) / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) blkrd_per_sec

, ROUND(SUM(cpu_time_delta ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) cpu_ms_per_sec

, ROUND(SUM(iowait_delta ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) iow_ms_per_sec

, ROUND(SUM(clwait_delta ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) clw_ms_per_sec

, ROUND(SUM(apwait_delta ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) apw_ms_per_sec

, ROUND(SUM(ccwait_delta ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) ccw_ms_per_sec

FROM

dba_hist_snapshot sn

, dba_hist_sqlstat st

WHERE

sn.snap_id = st.snap_id

AND sn.dbid = st.dbid

AND sn.instance_number = st.instance_number

AND sql_id = '&sql_id'

--AND plan_hash_value LIKE '2'

--AND begin_interval_time >= 3

--AND end_interval_time <= 4

-- AND begin_interval_time > sysdate -1

GROUP BY

CAST(begin_interval_time AS DATE)

, CAST(end_interval_time AS DATE)

, sql_id

, plan_hash_value

ORDER BY

begin_interval_time