SELECT NVL(b.oprdefndesc,'Blank'),
a.CLIENT_INFO,
a.OSUSER,
a.machine,
a.PROGRAM,
a.LOCKWAIT,
a.LOGON_TIME,
'ALTER SYSTEM KILL SESSION '''|| a.SID|| ','|| a.SERIAL# ||''';'as Kill_SQL
FROM v$session a,
psoprdefn b where status = 'ACTIVE'
AND a.USERNAME IS NOT null and SUBSTR(a.client_info, 1, 7) = b.oprid (+);

SELECT 'SELECT * FROM table(dbms_xplan.display_awr('''
||sql_id
||''','
||sql_plan_hash_value
||',NULL,''ADVANCED''))/*'
||tot_ash_secs
||','
||tot_awr_secs
||'*/;'
FROM
(SELECT ROW_NUMBER()over (PARTITION BY x.sql_plan_hash_value order by x.awr_secs DESC) AS ranking ,
x.sql_id,
x.sql_plan_hash_value ,
SUM(x.ash_secs) over (PARTITION BY x.sql_plan_hash_value) tot_ash_secs ,
SUM(x.awr_secs) over (PARTITION BY x.sql_plan_hash_value) tot_awr_secs ,
COUNT(DISTINCT sql_id) over (PARTITION BY x.sql_plan_hash_value) sql_ids
FROM
(SELECT h.sql_id ,
h.sql_plan_hash_value ,
SUM(10) ash_secs ,
10*COUNT(t.sql_id) awr_secs

SELECT SESION.SID,
SESION.USERNAME,
OPTIMIZER_MODE,
HASH_VALUE,
ADDRESS,
CPU_TIME,
ELAPSED_TIME,
DISK_READS,
DIRECT_WRITES,
SQL_fullTEXT
,'ALTER SYSTEM KILL SESSION '''|| SESION.SID|| ','|| SESION.SERIAL# ||''';'as Kill_SQL
FROM V$SQLAREA SQLAREA, V$SESSION SESION
WHERE SESION.SQL_HASH_VALUE = SQLAREA.HASH_VALUE
AND SESION.SQL_ADDRESS = SQLAREA.ADDRESS
AND SESION.USERNAME IS NOT NULL
AND ROWNUM < 6
AND SESION.USERNAME IS NOT null
AND SESION.status = 'ACTIVE'
ORDER BY DISK_READS DESC,ELAPSED_TIME DESC;
FROM DBA_HIST_SNAPSHOT x ,
DBA_HIST_ACTIVE_SESS_HISTORY h
LEFT OUTER JOIN dba_hist_sqltext t
ON t.sql_id = h.sql_id
WHERE x.end_interval_time >= TRUNC(SYSDATE,'mm')
AND x.begin_interval_time <= TRUNC(SYSDATE,'mm') +7
AND h.sample_time BETWEEN TRUNC(SYSDATE,'mm') AND TRUNC(SYSDATE,'mm')+7
AND h.snap_id = x.snap_id
AND h.dbid = x.dbid
AND h.instance_number = x.instance_number
AND h.module = h.program
GROUP BY h.sql_id,
h.sql_plan_hash_value
) x
) y
WHERE y.ranking = 1
AND tot_ash_secs > 9
ORDER BY tot_ash_secs DESC,
ranking
) x
) y
WHERE y.ranking = 1
AND tot_ash_secs > 900
ORDER BY tot_ash_secs DESC,
ranking

— For statistics use:
SELECT *
FROM
(SELECT hash_value ,
SUM(disk_reads) ,
SUM(buffer_gets) ,
SUM(rows_processed),
SUM(buffer_gets)/greatest(SUM(rows_processed),1) ,
SUM(executions) ,
SUM(buffer_gets)/greatest(SUM(executions), 1)
FROM V$SQL
WHERE command_type IN (2,3,6,7)
GROUP BY hash_value
ORDER BY 5 DESC
)
WHERE rownum <= 10;
—To get the SQL text for the above statistics use the following:
SELECT t.SQL_TEXT
FROM v$sqlarea t
WHERE t.HASH_VALUE IN
(SELECT hash_value
FROM
(SELECT hash_value ,
SUM(disk_reads) ,
SUM(buffer_gets) ,
SUM(rows_processed),
SUM(buffer_gets)/greatest(SUM(rows_processed),1) ,
SUM(executions) ,
SUM(buffer_gets)/greatest(SUM(executions), 1)
FROM V$SQL
WHERE command_type IN (2,3,6,7)
GROUP BY hash_value
ORDER BY 5 DESC
)
WHERE rownum <= 10
);

— Find the Process Instance No.

SELECT *
FROM PS_PMN_PRCSLIST
WHERE OPRID = 'MAESTRO' AND RUNSTATUSDESCR LIKE '%Proc%';

—If PRCSTYPE column says it is an Sqr PRocess, then fire below with input as PRCSINSTANCE value.

SELECT SID, serial#
FROM V$Session
WHERE PROCESS IN (SELECT TO_CHAR (SESSIONIDNUM + 2)
FROM PSPRCSque
WHERE PRCSINSTANCE = );

— If PRCSTYPE column says it is an Application Engine, then fire below with input as PRCSINSTANCE value.

SELECT SID, serial#
FROM V$Session
WHERE PROCESS IN (SELECT TO_CHAR (SESSIONIDNUM)
FROM PSPRCSque
WHERE PRCSINSTANCE = );

SELECT SESION.SID,
SESION.USERNAME,
OPTIMIZER_MODE,
HASH_VALUE,
ADDRESS,
CPU_TIME,
ELAPSED_TIME,
DISK_READS,
DIRECT_WRITES,
SQL_fullTEXT
,'ALTER SYSTEM KILL SESSION '''|| SESION.SID|| ','|| SESION.SERIAL# ||''';'as Kill_SQL
FROM V$SQLAREA SQLAREA, V$SESSION SESION
WHERE SESION.SQL_HASH_VALUE = SQLAREA.HASH_VALUE
AND SESION.SQL_ADDRESS = SQLAREA.ADDRESS
AND SESION.USERNAME IS NOT NULL
AND ROWNUM < 6
AND SESION.USERNAME IS NOT null
AND SESION.status = 'ACTIVE'
ORDER BY DISK_READS DESC,ELAPSED_TIME DESC