SELECT NULL SAMPLE_TIME, NULL INST, NULL SID, NULL SQL_ID, NULL ACTION, NULL TIME_US, NULL OBJECT_NAME,

NULL FILE_NAME, NULL SQL_TYPE, NULL PROGRAM, NULL CLIENT_ID, NULL XID, NULL P1, NULL P2, NULL P3, NULL BLOCKING_SESSION,

NULL COORDINATOR, NULL PLAN_HASH, NULL SQL_TEXT FROM DUAL WHERE 1 = 0

UNION ALL (

SELECT NULL SAMPLE_TIME, NULL INST, NULL SID, NULL SQL_ID, NULL ACTION, NULL TIME_US, NULL OBJECT_NAME,

NULL FILE_NAME, NULL SQL_TYPE, NULL PROGRAM, NULL CLIENT_ID, NULL XID, NULL P1, NULL P2, NULL P3, NULL BLOCKING_SESSION,

NULL COORDINATOR, NULL PLAN_HASH, NULL SQL_TEXT FROM DUAL WHERE 1 = 0

) UNION ALL ( SELECT * FROM (

WITH BASIS_INFO AS

( SELECT /*+ INLINE */ /* hint sometimes required to push SNAP_IDs into DBA_HIST_ACTIVE_SESS_HISTORY */

DECODE(DBID, -1, OWN_DBID, DBID) DBID,

DECODE(INSTANCE_NUMBER, -1, USERENV('INSTANCE'), INSTANCE_NUMBER) INSTANCE_NUMBER,

BEGIN_DATE,

END_DATE,

TO_TIMESTAMP(TO_CHAR(BEGIN_DATE, 'dd.mm.yyyy hh24:mi:ss'),

'dd.mm.yyyy hh24:mi:ss') BEGIN_TIME,

TO_TIMESTAMP(TO_CHAR(END_DATE, 'dd.mm.yyyy hh24:mi:ss'),

'dd.mm.yyyy hh24:mi:ss') END_TIME,

BEGIN_SNAP_ID,

END_SNAP_ID,

SQL_ID,

PLAN_HASH,

EVENT,

SESSION_ID,

INCLUDE_SLAVES_FOR_SID,

PROGRAM,

CLIENT_ID,

XID,

ONLY_PARALLEL,

MIN_TIME_WAITED_MS,

NUM_RECORDS,

DATA_SOURCE

FROM

( SELECT

-1 DBID,

-1 INSTANCE_NUMBER, /* -2 for all instances, -1 for current instance */

TO_DATE('01.01.1000 01:04:00', 'dd.mm.yyyy hh24:mi:ss') BEGIN_DATE,

TO_DATE('31.12.9999 01:05:00', 'dd.mm.yyyy hh24:mi:ss') END_DATE,

-1 BEGIN_SNAP_ID, /* explicit SNAP_IDs sometimes required for ASH partition pruning */

-1 END_SNAP_ID,

'%' SQL_ID,

-1 PLAN_HASH,

'%' EVENT,

-1 SESSION_ID,

'X' INCLUDE_SLAVES_FOR_SID,

'%' PROGRAM,

'%' CLIENT_ID,

'%' XID,

-1 NUM_RECORDS,

-1 MIN_TIME_WAITED_MS,

' ' ONLY_PARALLEL,

'AWR' DATA_SOURCE /* AWR, CURRENT */

FROM

DUAL

),

( SELECT DBID OWN_DBID FROM V$DATABASE )

),

SNAPSHOTS AS

( SELECT

HSS.DBID,

HSS.INSTANCE_NUMBER,

MIN(HSS.SNAP_ID) BEGIN_SNAP_ID,

MIN(HSS.BEGIN_INTERVAL_TIME) BEGIN_TIME,

MAX(HSS.SNAP_ID) END_SNAP_ID,

MAX(HSS.END_INTERVAL_TIME) END_TIME

FROM

DBA_HIST_SNAPSHOT HSS,

BASIS_INFO BI

WHERE

HSS.DBID = BI.DBID AND

HSS.END_INTERVAL_TIME >= BI.BEGIN_TIME AND

HSS.BEGIN_INTERVAL_TIME <= BI.END_TIME AND

( BI.BEGIN_SNAP_ID = -1 OR

HSS.SNAP_ID >= BI.BEGIN_SNAP_ID ) AND

( BI.END_SNAP_ID = -1 OR

HSS.SNAP_ID <= BI.END_SNAP_ID )

GROUP BY

HSS.DBID,

HSS.INSTANCE_NUMBER

),

DATAFILES AS

( SELECT

FILE_ID,

FILE_NAME

FROM

DBA_DATA_FILES

UNION ALL

( SELECT

P.VALUE + DTF.FILE_ID FILE_ID,

DTF.FILE_NAME

FROM

DBA_TEMP_FILES DTF,

V$PARAMETER P

WHERE

UPPER(P.NAME) = 'DB_FILES'

)

)

SELECT DISTINCT

TO_CHAR(SAMPLE_TIME, 'YYYY-MM-DD HH24:MI:SS') SAMPLE_TIME,

INSTANCE_NUMBER INST,

SESSION_ID SID,

SQL_ID,

DECODE(SESSION_STATE,

'WAITING', EVENT || DECODE(SUBSTR(EVENT, 1, 5),

'enq: ', ' (' || TO_CHAR(BITAND(P1, 65535)) || ' / ' ||

TO_CHAR(DECODE(BITAND(P1, 65535),

1, 'Null',

2, 'Sub-Share',

3, 'Sub-Exclusive',

4, 'Share',

5, 'Share/Sub-Exclusive',

6, 'Exclusive', 'Other')) || ')' ), 'CPU') ACTION,

TO_CHAR(TIME_WAITED, 999999990) TIME_US,

CASE

WHEN EVENT = 'direct path read temp' OR

EVENT = 'direct path write temp' OR

EVENT = 'Data file init write'

THEN

'n/a'

WHEN WAIT_CLASS = 'Application' OR

WAIT_CLASS = 'Cluster' OR

WAIT_CLASS = 'User I/O'

THEN

NVL(OBJECT_NAME, DECODE(CURRENT_OBJ#,

-1, 'Header / Rollback / ANALYZE',

0, 'Undo Data',

SEGSTAT_OBJECT_NAME || ' (previous)'))

ELSE

'n/a'

END OBJECT_NAME,

DECODE(WAIT_CLASS, 'User I/O',

DECODE(EVENT, 'Data file init write',

NULL, FILE_NAME)) FILE_NAME,

NAME SQL_TYPE,

PROGRAM,

CLIENT_ID,

TO_CHAR(RAWTOHEX(XID)) XID,

P1,

P2,

P3,

DECODE(DECODE(SESSION_STATE, 'WAITING', EVENT, 'CPU'),

'cursor: pin S wait on X', DECODE(SIGN(P2 - 4294967295), 1,

P2 / 4294967296, P2 / 65536), BLOCKING_SESSION)

BLOCKING_SESSION,

QC_SESSION_ID COORDINATOR,

SQL_PLAN_HASH_VALUE PLAN_HASH,

TO_CHAR(SUBSTR(NVL(SQL_TEXT, NAME), 1, 400)) SQL_TEXT

FROM

( SELECT

ASH.SAMPLE_TIME,

ASH.INSTANCE_NUMBER,

ASH.SESSION_ID,

ASH.SQL_ID,

ASH.SESSION_STATE,

ASH.EVENT,

ASH.TIME_WAITED,

ASH.WAIT_CLASS,

O.OBJECT_NAME,

SSO.OBJECT_NAME SEGSTAT_OBJECT_NAME,

ASH.CURRENT_OBJ#,

DF.FILE_NAME,

A.NAME,

ASH.PROGRAM,

ASH.CLIENT_ID,

ASH.XID,

ASH.P1,

ASH.P2,

ASH.P3,

ASH.BLOCKING_SESSION,

ASH.QC_SESSION_ID,

ASH.SQL_PLAN_HASH_VALUE,

TO_CHAR(SUBSTR(S.SQL_TEXT, 1, 400)) SQL_TEXT

FROM

DBA_HIST_ACTIVE_SESS_HISTORY ASH,

DBA_HIST_SQLTEXT S,

DATAFILES DF,

DBA_OBJECTS O,

DBA_HIST_SEG_STAT_OBJ SSO,

AUDIT_ACTIONS A,

SNAPSHOTS SS,

BASIS_INFO BI

WHERE

SS.DBID = ASH.DBID AND

ASH.DBID = S.DBID (+) AND

ASH.DBID = SSO.DBID (+) AND

ASH.SNAP_ID BETWEEN SS.BEGIN_SNAP_ID AND SS.END_SNAP_ID AND

NVL(ASH.SQL_OPCODE, -99) = A.ACTION (+) AND

NVL(ASH.SQL_ID, ' ') = S.SQL_ID (+) AND

ASH.CURRENT_OBJ# = O.OBJECT_ID (+) AND

ASH.CURRENT_OBJ# = SSO.OBJ# (+) AND

ASH.SAMPLE_TIME BETWEEN BI.BEGIN_TIME AND BI.END_TIME AND

NVL(ASH.SQL_ID, ' ') LIKE BI.SQL_ID AND

( BI.PLAN_HASH = -1 OR

NVL(ASH.SQL_PLAN_HASH_VALUE, 0) = BI.PLAN_HASH ) AND

NVL(ASH.EVENT, 'CPU') LIKE BI.EVENT AND

( BI.INSTANCE_NUMBER = -2 OR

BI.INSTANCE_NUMBER = ASH.INSTANCE_NUMBER ) AND

( BI.SESSION_ID = -1 OR

BI.SESSION_ID = ASH.SESSION_ID OR

( BI.INCLUDE_SLAVES_FOR_SID = 'X' AND

BI.SESSION_ID = ASH.QC_SESSION_ID

)

) AND

( BI.ONLY_PARALLEL = ' ' OR ASH.QC_SESSION_ID IS NOT NULL) AND

NVL(ASH.PROGRAM, ' ') LIKE BI.PROGRAM AND

NVL(ASH.CLIENT_ID, ' ') LIKE BI.CLIENT_ID AND

( ASH.XID IS NULL AND BI.XID = '%' OR ASH.XID LIKE BI.XID ) AND

ASH.P1 = DF.FILE_ID (+) AND

( BI.MIN_TIME_WAITED_MS = -1 OR

ASH.TIME_WAITED >= BI.MIN_TIME_WAITED_MS * 1000 ) AND

( BI.NUM_RECORDS = -1 OR ROWNUM <= BI.NUM_RECORDS ) AND

BI.DATA_SOURCE = 'AWR'

UNION ALL

( SELECT

ASH.SAMPLE_TIME,

ASH.INST_ID INSTANCE_NUMBER,

ASH.SESSION_ID,

ASH.SQL_ID,

ASH.SESSION_STATE,

ASH.EVENT,

ASH.TIME_WAITED,

ASH.WAIT_CLASS,

O.OBJECT_NAME,

SSO.OBJECT_NAME SEGSTAT_OBJECT_NAME,

ASH.CURRENT_OBJ#,

DF.FILE_NAME,

A.NAME,

ASH.PROGRAM,

ASH.CLIENT_ID,

ASH.XID,

ASH.P1,

ASH.P2,

ASH.P3,

ASH.BLOCKING_SESSION,

ASH.QC_SESSION_ID,

ASH.SQL_PLAN_HASH_VALUE,

S.SQL_TEXT

FROM

GV$ACTIVE_SESSION_HISTORY ASH,

GV$SQLTEXT S,

DATAFILES DF,

DBA_OBJECTS O,

DBA_HIST_SEG_STAT_OBJ SSO,

AUDIT_ACTIONS A,

BASIS_INFO BI

WHERE

NVL(ASH.SQL_OPCODE, -99) = A.ACTION (+) AND

NVL(ASH.SQL_ID, ' ') = S.SQL_ID (+) AND

S.PIECE (+) = 0 AND

ASH.CURRENT_OBJ# = O.OBJECT_ID (+) AND

ASH.CURRENT_OBJ# = SSO.OBJ# (+) AND

ASH.SAMPLE_TIME BETWEEN BI.BEGIN_TIME AND BI.END_TIME AND

NVL(ASH.SQL_ID, ' ') LIKE BI.SQL_ID AND

( BI.PLAN_HASH = -1 OR

NVL(ASH.SQL_PLAN_HASH_VALUE, 0) = BI.PLAN_HASH ) AND

NVL(ASH.EVENT, 'CPU') LIKE BI.EVENT AND

( BI.INSTANCE_NUMBER = -2 OR

BI.INSTANCE_NUMBER = ASH.INST_ID ) AND

( BI.SESSION_ID = -1 OR

BI.SESSION_ID = ASH.SESSION_ID OR

( BI.INCLUDE_SLAVES_FOR_SID = 'X' AND

BI.SESSION_ID = ASH.QC_SESSION_ID

)

) AND

( BI.ONLY_PARALLEL = ' ' OR ASH.QC_SESSION_ID IS NOT NULL) AND

NVL(ASH.PROGRAM, ' ') LIKE BI.PROGRAM AND

NVL(ASH.CLIENT_ID, ' ') LIKE BI.CLIENT_ID AND

( ASH.XID IS NULL AND BI.XID = '%' OR ASH.XID LIKE BI.XID ) AND

ASH.P1 = DF.FILE_ID (+) AND

( BI.MIN_TIME_WAITED_MS = -1 OR

ASH.TIME_WAITED >= BI.MIN_TIME_WAITED_MS * 1000 ) AND

( BI.NUM_RECORDS = -1 OR ROWNUM <= BI.NUM_RECORDS ) AND

BI.DATA_SOURCE = 'CURRENT'

)

)

ORDER BY

SAMPLE_TIME DESC,

INST,

SID

));