SELECT NULL MIN_SAMPLE_TIME, NULL MAX_SAMPLE_TIME, NULL INST, NULL SID, NULL SQL_ID, NULL SQL_TYPE,

NULL PLAN_HASH, NULL PROGRAM, NULL CLIENT_ID, NULL XID, NULL P1, NULL EVENT, NULL OBJECT_NAME,

NULL OCCURRENCES, NULL SESSIONS, NULL PERCENT FROM DUAL WHERE 1 = 0

UNION ALL (

SELECT NULL MIN_SAMPLE_TIME, NULL MAX_SAMPLE_TIME, NULL INST, NULL SID, NULL SQL_ID, NULL SQL_TYPE,

NULL PLAN_HASH, NULL PROGRAM, NULL CLIENT_ID, NULL XID, NULL P1, NULL EVENT, NULL OBJECT_NAME,

NULL OCCURRENCES, NULL SESSIONS, NULL PERCENT 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,

SQL_TYPE,

PLAN_HASH,

PROGRAM,

CLIENT_ID,

XID,

P1,

EVENT,

SESSION_ID,

ONLY_PARALLEL,

NUM_RECORDS,

AGGREGATE_BY,

DATA_SOURCE,

MIN_PERCENT_THRESHOLD

FROM

( SELECT

-1 DBID,

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

TO_DATE('01.01.1000 14:27:44', 'dd.mm.yyyy hh24:mi:ss') BEGIN_DATE,

TO_DATE('31.12.9999 14:37:26', '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,

'%' SQL_TYPE,

-1 PLAN_HASH,

'%' PROGRAM,

'%' CLIENT_ID,

'SQL*Net more data to client' EVENT,

-1 SESSION_ID,

'%' XID,

-1 P1,

' ' ONLY_PARALLEL,

30 NUM_RECORDS,

0.5 MIN_PERCENT_THRESHOLD,

'PROGRAM' AGGREGATE_BY, /* 'XID SQL_ID SQL_TYPE EVENT SID OBJECT INSTANCE PLAN_HASH PROGRAM CLIENT_ID P1' or subset */

'AWR' DATA_SOURCE /* CURRENT, AWR */

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,

SUM(TO_CHAR(LEAST(HSS.END_INTERVAL_TIME, BI.END_TIME), 'SSSSS') -

TO_CHAR(GREATEST(HSS.BEGIN_INTERVAL_TIME, BI.BEGIN_TIME), 'SSSSS') +

86400 * (TO_CHAR(LEAST(HSS.END_INTERVAL_TIME, BI.END_TIME), 'J') -

TO_CHAR(GREATEST(HSS.BEGIN_INTERVAL_TIME, BI.BEGIN_TIME), 'J')))

SECONDS

FROM

DBA_HIST_SNAPSHOT HSS,

BASIS_INFO BI

WHERE

HSS.DBID = BI.DBID AND

( BI.INSTANCE_NUMBER = -2 OR HSS.INSTANCE_NUMBER = BI.INSTANCE_NUMBER ) AND

HSS.END_INTERVAL_TIME >= BI.BEGIN_TIME AND

HSS.BEGIN_INTERVAL_TIME <= BI.END_TIME

GROUP BY

HSS.DBID,

HSS.INSTANCE_NUMBER

),

TOTAL_SECONDS AS

( SELECT

MAX(SECONDS) SECONDS

FROM

( SELECT

SECONDS

FROM

BASIS_INFO BI,

SNAPSHOTS

WHERE

BI.DATA_SOURCE = 'AWR'

UNION ALL

( SELECT

( TO_DATE(TO_CHAR(LEAST(MIN(BI.END_TIME), MAX(ASH.SAMPLE_TIME)),

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

TO_DATE(TO_CHAR(GREATEST(MIN(BI.BEGIN_TIME), MIN(ASH.SAMPLE_TIME)),

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

86400 SECONDS

FROM

BASIS_INFO BI,

GV$ACTIVE_SESSION_HISTORY ASH

WHERE

BI.DATA_SOURCE = 'CURRENT' AND

( BI.INSTANCE_NUMBER = -2 OR

BI.INSTANCE_NUMBER = ASH.INST_ID )

)

)

WHERE

SECONDS IS NOT NULL

),

ASH_DISTRIBUTION AS

( SELECT

MIN(SAMPLE_TIME) MIN_SAMPLE_TIME,

MAX(SAMPLE_TIME) MAX_SAMPLE_TIME,

INSTANCE_NUMBER,

SID,

SQL_ID,

SQL_TYPE,

PLAN_HASH,

PROGRAM,

CLIENT_ID,

EVENT,

P1,

XID,

OBJECT_NAME,

COUNT(*) OCCURRENCES

FROM

( SELECT DISTINCT

INSTANCE_NUMBER,

SID,

SQL_ID,

TO_CHAR(RAWTOHEX(XID)) XID,

SQL_TYPE,

PLAN_HASH,

PROGRAM,

CLIENT_ID,

SAMPLE_TIME,

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') EVENT,

P1,

WAIT_CLASS,

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', P3, 0) BLOCKS_ACCESSED,

BI_SQL_TYPE

FROM

( SELECT

ASH.INSTANCE_NUMBER,

ASH.SESSION_ID SID,

ASH.SQL_ID,

ASH.XID,

ASH.SQL_PLAN_HASH_VALUE PLAN_HASH,

ASH.PROGRAM,

ASH.CLIENT_ID,

ASH.SAMPLE_TIME,

ASH.SESSION_STATE,

ASH.EVENT,

ASH.P1,

ASH.P3,

ASH.WAIT_CLASS,

ASH.CURRENT_OBJ#,

A.NAME SQL_TYPE,

O.OBJECT_NAME,

SSO.OBJECT_NAME SEGSTAT_OBJECT_NAME,

BI.SQL_TYPE BI_SQL_TYPE

FROM

BASIS_INFO BI,

SNAPSHOTS SS,

DBA_HIST_ACTIVE_SESS_HISTORY ASH,

AUDIT_ACTIONS A,

DBA_OBJECTS O,

DBA_HIST_SEG_STAT_OBJ SSO

WHERE

SS.DBID = ASH.DBID AND

ASH.DBID = SSO.DBID (+) AND

SS.INSTANCE_NUMBER = ASH.INSTANCE_NUMBER AND

( BI.BEGIN_SNAP_ID = -1 OR ASH.SNAP_ID >= BI.BEGIN_SNAP_ID ) AND

( BI.END_SNAP_ID = -1 OR ASH.SNAP_ID <= BI.END_SNAP_ID ) AND

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

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

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

ASH.SQL_OPCODE = A.ACTION (+) AND

( ASH.SQL_ID IS NULL AND BI.SQL_ID = '%' OR

ASH.SQL_ID LIKE BI.SQL_ID ) AND

( BI.SQL_TYPE = '%' OR A.NAME = BI.SQL_TYPE ) AND

( BI.PLAN_HASH = -1 OR

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

( ASH.PROGRAM IS NULL AND BI.PROGRAM = '%' OR

ASH.PROGRAM LIKE BI.PROGRAM ) AND

( ASH.CLIENT_ID IS NULL AND BI.CLIENT_ID = '%' OR

ASH.CLIENT_ID LIKE BI.CLIENT_ID ) AND

( BI.SESSION_ID = -1 OR ASH.SESSION_ID = BI.SESSION_ID ) AND

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

( BI.P1 = -1 OR ASH.P1 = BI.P1 ) AND

DECODE(ASH.SESSION_STATE, 'WAITING', ASH.EVENT, 'CPU') LIKE BI.EVENT AND

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

BI.DATA_SOURCE = 'AWR'

UNION

( SELECT

ASH.INST_ID INSTANCE_NUMBER,

ASH.SESSION_ID SID,

ASH.SQL_ID,

ASH.XID,

ASH.SQL_PLAN_HASH_VALUE PLAN_HASH,

ASH.PROGRAM,

ASH.CLIENT_ID,

ASH.SAMPLE_TIME,

ASH.SESSION_STATE,

ASH.EVENT,

ASH.P1,

ASH.P3,

ASH.WAIT_CLASS,

ASH.CURRENT_OBJ#,

A.NAME SQL_TYPE,

O.OBJECT_NAME,

SSO.OBJECT_NAME SEGSTAT_OBJECT_NAME,

BI.SQL_TYPE BI_SQL_TYPE

FROM

BASIS_INFO BI,

GV$ACTIVE_SESSION_HISTORY ASH,

AUDIT_ACTIONS A,

DBA_OBJECTS O,

DBA_HIST_SEG_STAT_OBJ SSO

WHERE

(BI.INSTANCE_NUMBER = -2 OR BI.INSTANCE_NUMBER = ASH.INST_ID) AND

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

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

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

ASH.SQL_OPCODE = A.ACTION (+) AND

( ASH.SQL_ID IS NULL AND BI.SQL_ID = '%' OR

ASH.SQL_ID LIKE BI.SQL_ID ) AND

( BI.SQL_TYPE = '%' OR A.NAME = BI.SQL_TYPE ) AND

( BI.PLAN_HASH = -1 OR

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

( ASH.PROGRAM IS NULL AND BI.PROGRAM = '%' OR

ASH.PROGRAM LIKE BI.PROGRAM ) AND

( ASH.CLIENT_ID IS NULL AND BI.CLIENT_ID = '%' OR

ASH.CLIENT_ID LIKE BI.CLIENT_ID ) AND

( BI.SESSION_ID = -1 OR ASH.SESSION_ID = BI.SESSION_ID ) AND

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

DECODE(ASH.SESSION_STATE, 'WAITING', ASH.EVENT, 'CPU') LIKE BI.EVENT AND

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

BI.DATA_SOURCE = 'CURRENT'

)

)

)

GROUP BY

SID,

SQL_ID,

SQL_TYPE,

PLAN_HASH,

PROGRAM,

CLIENT_ID,

EVENT,

P1,

XID,

OBJECT_NAME,

INSTANCE_NUMBER

)

SELECT

MIN_SAMPLE_TIME,

MAX_SAMPLE_TIME,

INST_ID INST,

SID,

SQL_ID,

SQL_TYPE,

PLAN_HASH,

PROGRAM,

CLIENT_ID,

XID,

P1,

EVENT,

OBJECT_NAME,

OCCURRENCES,

SESSIONS,

PERCENT

FROM

( SELECT

TO_CHAR(MIN(MIN_SAMPLE_TIME), 'dd.mm.yyyy hh24:mi:ss') MIN_SAMPLE_TIME,

TO_CHAR(MAX(MAX_SAMPLE_TIME), 'dd.mm.yyyy hh24:mi:ss') MAX_SAMPLE_TIME,

INST_ID,

SID,

SQL_ID,

SQL_TYPE,

PLAN_HASH,

PROGRAM,

CLIENT_ID,

XID,

EVENT,

P1,

OBJECT_NAME,

TO_CHAR(SUM(OCCURRENCES), 9999999990) OCCURRENCES,

TO_CHAR(SUM(OCCURRENCES) * DECODE(DATA_SOURCE, 'AWR', 10, 1) / SECONDS,

9990.99) SESSIONS,

TO_CHAR(RATIO_TO_REPORT(SUM(OCCURRENCES)) OVER () * 100, 990.99) PERCENT,

NUM_RECORDS,

MIN_PERCENT_THRESHOLD

FROM

( SELECT

ASH.MIN_SAMPLE_TIME,

ASH.MAX_SAMPLE_TIME,

DECODE(BI.INSTANCE_NUMBER, -2,

DECODE(INSTR(BI.AGGREGATE_BY, 'INSTANCE'),

0, ' any', TO_CHAR(ASH.INSTANCE_NUMBER, 990)), 'any') INST_ID,

DECODE(BI.SESSION_ID, -1,

DECODE(INSTR(BI.AGGREGATE_BY, 'SID'), 0, 'any', TO_CHAR(ASH.SID, 99990)),

TO_CHAR(ASH.SID, 99990)) SID,

DECODE(INSTR(BI.SQL_ID, '%'), 0, ASH.SQL_ID,

DECODE(INSTR(BI.AGGREGATE_BY, 'SQL_ID'), 0, 'any', ASH.SQL_ID)) SQL_ID,

DECODE(INSTR(BI.SQL_TYPE, '%'), 0, ASH.SQL_TYPE,

DECODE(INSTR(BI.AGGREGATE_BY, 'SQL_TYPE'), 0, 'any', ASH.SQL_TYPE)) SQL_TYPE,

DECODE(BI.PLAN_HASH, -1, DECODE(INSTR(BI.AGGREGATE_BY,

'PLAN_HASH'), 0, 'any', TO_CHAR(ASH.PLAN_HASH)), TO_CHAR(ASH.PLAN_HASH)) PLAN_HASH,

DECODE(INSTR(BI.PROGRAM, '%'), 0, TO_CHAR(ASH.PROGRAM),

DECODE(INSTR(BI.AGGREGATE_BY, 'PROGRAM'), 0, 'any', TO_CHAR(ASH.PROGRAM))) PROGRAM,

DECODE(INSTR(BI.CLIENT_ID, '%'), 0, TO_CHAR(ASH.CLIENT_ID),

DECODE(INSTR(BI.AGGREGATE_BY, 'CLIENT_ID'), 0, 'any', TO_CHAR(ASH.CLIENT_ID))) CLIENT_ID,

DECODE(INSTR(BI.EVENT, '%'), 0, ASH.EVENT,

DECODE(INSTR(BI.AGGREGATE_BY, 'EVENT'), 0, 'any', ASH.EVENT)) EVENT,

DECODE(INSTR(BI.AGGREGATE_BY, 'P1'), 0, 'any', ASH.P1) P1,

DECODE(INSTR(BI.AGGREGATE_BY, 'OBJECT'), 0, 'any', OBJECT_NAME) OBJECT_NAME,

DECODE(INSTR(BI.XID, '%'), 0, ASH.XID,

DECODE(INSTR(BI.AGGREGATE_BY, 'XID'), 0, 'any', ASH.XID)) XID,

ASH.OCCURRENCES,

TS.SECONDS,

BI.DATA_SOURCE,

BI.NUM_RECORDS,

BI.MIN_PERCENT_THRESHOLD

FROM

BASIS_INFO BI,

TOTAL_SECONDS TS,

ASH_DISTRIBUTION ASH

)

GROUP BY

INST_ID,

SID,

SQL_ID,

SQL_TYPE,

PLAN_HASH,

PROGRAM,

CLIENT_ID,

XID,

EVENT,

P1,

OBJECT_NAME,

DATA_SOURCE,

SECONDS,

NUM_RECORDS,

MIN_PERCENT_THRESHOLD

ORDER BY

OCCURRENCES DESC

)

WHERE

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

( MIN_PERCENT_THRESHOLD = -1 OR PERCENT >= MIN_PERCENT_THRESHOLD )

));