Oracle SEGMENT STAT 조회 스크립트

1. IT Story/Scripts 2019. 1. 29. 23:23

SEGMENT STAT 조회 스크립트

WITH A

AS ( SELECT DBID,

OBJ#,

SUM (LOGICAL_READS_DELTA) LOGICAL_READS,

SUM (BUFFER_BUSY_WAITS_DELTA) BUFFER_BUSY_WAITS,

SUM (DB_BLOCK_CHANGES_DELTA) DB_BLOCK_CHANGES,

SUM (PHYSICAL_READS_DELTA) PHYSICAL_READS,

SUM (PHYSICAL_WRITES_DELTA) PHYSICAL_WRITES,

SUM (PHYSICAL_READS_DIRECT_DELTA) PHYSICAL_READS_DIRECT,

SUM (PHYSICAL_WRITES_DIRECT_DELTA) PHYSICAL_WRITES_DIRECT,

SUM (ITL_WAITS_DELTA) ITL_WAITS,

SUM (ROW_LOCK_WAITS_DELTA) ROW_LOCK_WAITS,

SUM (GC_CR_BLOCKS_SERVED_DELTA) GC_CR_BLOCKS_SERVED,

SUM (GC_CU_BLOCKS_SERVED_DELTA) GC_CU_BLOCKS_SERVED,

SUM (SPACE_USED_DELTA) SPACE_USED,

SUM (SPACE_ALLOCATED_DELTA) SPACE_ALLOCATED,

SUM (TABLE_SCANS_DELTA) TABLE_SCANS

FROM DBA_HIST_SEG_STAT

WHERE DBID = (select dbid from v$database) --변경

AND INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance) --변경

AND SNAP_ID BETWEEN :3 AND :4

GROUP BY DBID, OBJ#),

A_TOT

AS (SELECT NULL DBID,

NULL OBJ#,

SUM (LOGICAL_READS) LOGICAL_READS,

SUM (BUFFER_BUSY_WAITS) BUFFER_BUSY_WAITS,

SUM (DB_BLOCK_CHANGES) DB_BLOCK_CHANGES,

SUM (PHYSICAL_READS) PHYSICAL_READS,

SUM (PHYSICAL_WRITES) PHYSICAL_WRITES,

SUM (PHYSICAL_READS_DIRECT) PHYSICAL_READS_DIRECT,

SUM (PHYSICAL_WRITES_DIRECT) PHYSICAL_WRITES_DIRECT,

SUM (ITL_WAITS) ITL_WAITS,

SUM (ROW_LOCK_WAITS) ROW_LOCK_WAITS,

SUM (GC_CR_BLOCKS_SERVED) GC_CR_BLOCKS_SERVED,

SUM (GC_CU_BLOCKS_SERVED) GC_CU_BLOCKS_SERVED,

SUM (SPACE_USED) SPACE_USED,

SUM (SPACE_ALLOCATED) SPACE_ALLOCATED,

SUM (TABLE_SCANS) TABLE_SCANS,

COUNT (DISTINCT OBJ#) OBJ_CNT

FROM A),

A_LR

AS (SELECT V.VIEWTYPE,

V.RNUM,

V.DBID,

DO.OWNER,

NVL (DO.OBJECT_NAME, TO_CHAR (V.OBJ#)) OBJECT_NAME,

DO.SUBOBJECT_NAME,

DO.OBJECT_TYPE,

V.LOGICAL_READS,

V.BUFFER_BUSY_WAITS,

V.DB_BLOCK_CHANGES,

V.PHYSICAL_READS,

V.PHYSICAL_WRITES,

V.PHYSICAL_READS_DIRECT,

V.PHYSICAL_WRITES_DIRECT,

V.ITL_WAITS,

V.ROW_LOCK_WAITS,

V.GC_CR_BLOCKS_SERVED,

V.GC_CU_BLOCKS_SERVED,

V.SPACE_USED,

V.SPACE_ALLOCATED,

V.TABLE_SCANS

FROM (SELECT 'LOGICAL READS' VIEWTYPE,

DBID,

OBJ#,

LOGICAL_READS,

BUFFER_BUSY_WAITS,

DB_BLOCK_CHANGES,

PHYSICAL_READS,

PHYSICAL_WRITES,

PHYSICAL_READS_DIRECT,

PHYSICAL_WRITES_DIRECT,

ITL_WAITS,

ROW_LOCK_WAITS,

GC_CR_BLOCKS_SERVED,

GC_CU_BLOCKS_SERVED,

SPACE_USED,

SPACE_ALLOCATED,

TABLE_SCANS,

ROW_NUMBER ()

OVER (

ORDER BY

LOGICAL_READS DESC, PHYSICAL_READS DESC, ROWNUM)

RNUM

FROM A) V,

DBA_OBJECTS DO

WHERE V.OBJ# = DO.OBJECT_ID(+) AND RNUM <= :5 AND ROWNUM <= :6),

A_BBW

AS (SELECT V.VIEWTYPE,

V.RNUM,

V.DBID,

DO.OWNER,

NVL (DO.OBJECT_NAME, TO_CHAR (V.OBJ#)) OBJECT_NAME,

DO.SUBOBJECT_NAME,

DO.OBJECT_TYPE,

V.LOGICAL_READS,

V.BUFFER_BUSY_WAITS,

V.DB_BLOCK_CHANGES,

V.PHYSICAL_READS,

V.PHYSICAL_WRITES,

V.PHYSICAL_READS_DIRECT,

V.PHYSICAL_WRITES_DIRECT,

V.ITL_WAITS,

V.ROW_LOCK_WAITS,

V.GC_CR_BLOCKS_SERVED,

V.GC_CU_BLOCKS_SERVED,

V.SPACE_USED,

V.SPACE_ALLOCATED,

V.TABLE_SCANS

FROM (SELECT 'BUFFER BUSY WAITS' VIEWTYPE,

DBID,

OBJ#,

LOGICAL_READS,

BUFFER_BUSY_WAITS,

DB_BLOCK_CHANGES,

PHYSICAL_READS,

PHYSICAL_WRITES,

PHYSICAL_READS_DIRECT,

PHYSICAL_WRITES_DIRECT,

ITL_WAITS,

ROW_LOCK_WAITS,

GC_CR_BLOCKS_SERVED,

GC_CU_BLOCKS_SERVED,

SPACE_USED,

SPACE_ALLOCATED,

TABLE_SCANS,

ROW_NUMBER ()

OVER (

ORDER BY

BUFFER_BUSY_WAITS DESC,

LOGICAL_READS DESC,

ROWNUM)

RNUM

FROM A) V,

DBA_OBJECTS DO

WHERE V.OBJ# = DO.OBJECT_ID(+) AND RNUM <= :7 AND ROWNUM <= :8),

A_DBC

AS (SELECT V.VIEWTYPE,

V.RNUM,

V.DBID,

DO.OWNER,

NVL (DO.OBJECT_NAME, TO_CHAR (V.OBJ#)) OBJECT_NAME,

DO.SUBOBJECT_NAME,

DO.OBJECT_TYPE,

V.LOGICAL_READS,

V.BUFFER_BUSY_WAITS,

V.DB_BLOCK_CHANGES,

V.PHYSICAL_READS,

V.PHYSICAL_WRITES,

V.PHYSICAL_READS_DIRECT,

V.PHYSICAL_WRITES_DIRECT,

V.ITL_WAITS,

V.ROW_LOCK_WAITS,

V.GC_CR_BLOCKS_SERVED,

V.GC_CU_BLOCKS_SERVED,

V.SPACE_USED,

V.SPACE_ALLOCATED,

V.TABLE_SCANS

FROM (SELECT 'DB_BLOCK CHANGES' VIEWTYPE,

DBID,

OBJ#,

LOGICAL_READS,

BUFFER_BUSY_WAITS,

DB_BLOCK_CHANGES,

PHYSICAL_READS,

PHYSICAL_WRITES,

PHYSICAL_READS_DIRECT,

PHYSICAL_WRITES_DIRECT,

ITL_WAITS,

ROW_LOCK_WAITS,

GC_CR_BLOCKS_SERVED,

GC_CU_BLOCKS_SERVED,

SPACE_USED,

SPACE_ALLOCATED,

TABLE_SCANS,

ROW_NUMBER ()

OVER (

ORDER BY

DB_BLOCK_CHANGES DESC,

LOGICAL_READS DESC,

ROWNUM)

RNUM

FROM A) V,

DBA_OBJECTS DO

WHERE V.OBJ# = DO.OBJECT_ID(+) AND RNUM <= :9 AND ROWNUM <= :10),

A_PR

AS (SELECT V.VIEWTYPE,

V.RNUM,

V.DBID,

DO.OWNER,

NVL (DO.OBJECT_NAME, TO_CHAR (V.OBJ#)) OBJECT_NAME,

DO.SUBOBJECT_NAME,

DO.OBJECT_TYPE,

V.LOGICAL_READS,

V.BUFFER_BUSY_WAITS,

V.DB_BLOCK_CHANGES,

V.PHYSICAL_READS,

V.PHYSICAL_WRITES,

V.PHYSICAL_READS_DIRECT,

V.PHYSICAL_WRITES_DIRECT,

V.ITL_WAITS,

V.ROW_LOCK_WAITS,

V.GC_CR_BLOCKS_SERVED,

V.GC_CU_BLOCKS_SERVED,

V.SPACE_USED,

V.SPACE_ALLOCATED,

V.TABLE_SCANS

FROM (SELECT 'PHYSICAL READS' VIEWTYPE,

DBID,

OBJ#,

LOGICAL_READS,

BUFFER_BUSY_WAITS,

DB_BLOCK_CHANGES,

PHYSICAL_READS,

PHYSICAL_WRITES,

PHYSICAL_READS_DIRECT,

PHYSICAL_WRITES_DIRECT,

ITL_WAITS,

ROW_LOCK_WAITS,

GC_CR_BLOCKS_SERVED,

GC_CU_BLOCKS_SERVED,

SPACE_USED,

SPACE_ALLOCATED,

TABLE_SCANS,

ROW_NUMBER ()

OVER (

ORDER BY

PHYSICAL_READS DESC, LOGICAL_READS DESC, ROWNUM)

RNUM

FROM A) V,

DBA_OBJECTS DO

WHERE V.OBJ# = DO.OBJECT_ID(+) AND RNUM <= :11 AND ROWNUM <= :12),

A_PW

AS (SELECT V.VIEWTYPE,

V.RNUM,

V.DBID,

DO.OWNER,

NVL (DO.OBJECT_NAME, TO_CHAR (V.OBJ#)) OBJECT_NAME,

DO.SUBOBJECT_NAME,

DO.OBJECT_TYPE,

V.LOGICAL_READS,

V.BUFFER_BUSY_WAITS,

V.DB_BLOCK_CHANGES,

V.PHYSICAL_READS,

V.PHYSICAL_WRITES,

V.PHYSICAL_READS_DIRECT,

V.PHYSICAL_WRITES_DIRECT,

V.ITL_WAITS,

V.ROW_LOCK_WAITS,

V.GC_CR_BLOCKS_SERVED,

V.GC_CU_BLOCKS_SERVED,

V.SPACE_USED,

V.SPACE_ALLOCATED,

V.TABLE_SCANS

FROM (SELECT 'PHYSICAL WRITES' VIEWTYPE,

DBID,

OBJ#,

LOGICAL_READS,

BUFFER_BUSY_WAITS,

DB_BLOCK_CHANGES,

PHYSICAL_READS,

PHYSICAL_WRITES,

PHYSICAL_READS_DIRECT,

PHYSICAL_WRITES_DIRECT,

ITL_WAITS,

ROW_LOCK_WAITS,

GC_CR_BLOCKS_SERVED,

GC_CU_BLOCKS_SERVED,

SPACE_USED,

SPACE_ALLOCATED,

TABLE_SCANS,

ROW_NUMBER ()

OVER (

ORDER BY

PHYSICAL_WRITES DESC,

LOGICAL_READS DESC,

ROWNUM)

RNUM

FROM A) V,

DBA_OBJECTS DO

WHERE V.OBJ# = DO.OBJECT_ID(+) AND RNUM <= :13 AND ROWNUM <= :14),

A_PRD

AS (SELECT V.VIEWTYPE,

V.RNUM,

V.DBID,

DO.OWNER,

NVL (DO.OBJECT_NAME, TO_CHAR (V.OBJ#)) OBJECT_NAME,

DO.SUBOBJECT_NAME,

DO.OBJECT_TYPE,

V.LOGICAL_READS,

V.BUFFER_BUSY_WAITS,

V.DB_BLOCK_CHANGES,

V.PHYSICAL_READS,

V.PHYSICAL_WRITES,

V.PHYSICAL_READS_DIRECT,

V.PHYSICAL_WRITES_DIRECT,

V.ITL_WAITS,

V.ROW_LOCK_WAITS,

V.GC_CR_BLOCKS_SERVED,

V.GC_CU_BLOCKS_SERVED,

V.SPACE_USED,

V.SPACE_ALLOCATED,

V.TABLE_SCANS

FROM (SELECT 'PHYSICAL READS DIRECT' VIEWTYPE,

DBID,

OBJ#,

LOGICAL_READS,

BUFFER_BUSY_WAITS,

DB_BLOCK_CHANGES,

PHYSICAL_READS,

PHYSICAL_WRITES,

PHYSICAL_READS_DIRECT,

PHYSICAL_WRITES_DIRECT,

ITL_WAITS,

ROW_LOCK_WAITS,

GC_CR_BLOCKS_SERVED,

GC_CU_BLOCKS_SERVED,

SPACE_USED,

SPACE_ALLOCATED,

TABLE_SCANS,

ROW_NUMBER ()

OVER (

ORDER BY

PHYSICAL_READS_DIRECT DESC,

LOGICAL_READS DESC,

ROWNUM)

RNUM

FROM A) V,

DBA_OBJECTS DO

WHERE V.OBJ# = DO.OBJECT_ID(+) AND RNUM <= :15 AND ROWNUM <= :16),

A_IW

AS (SELECT V.VIEWTYPE,

V.RNUM,

V.DBID,

DO.OWNER,

NVL (DO.OBJECT_NAME, TO_CHAR (V.OBJ#)) OBJECT_NAME,

DO.SUBOBJECT_NAME,

DO.OBJECT_TYPE,

V.LOGICAL_READS,

V.BUFFER_BUSY_WAITS,

V.DB_BLOCK_CHANGES,

V.PHYSICAL_READS,

V.PHYSICAL_WRITES,

V.PHYSICAL_READS_DIRECT,

V.PHYSICAL_WRITES_DIRECT,

V.ITL_WAITS,

V.ROW_LOCK_WAITS,

V.GC_CR_BLOCKS_SERVED,

V.GC_CU_BLOCKS_SERVED,

V.SPACE_USED,

V.SPACE_ALLOCATED,

V.TABLE_SCANS

FROM (SELECT 'ITL WAITS' VIEWTYPE,

DBID,

OBJ#,

LOGICAL_READS,

BUFFER_BUSY_WAITS,

DB_BLOCK_CHANGES,

PHYSICAL_READS,

PHYSICAL_WRITES,

PHYSICAL_READS_DIRECT,

PHYSICAL_WRITES_DIRECT,

ITL_WAITS,

ROW_LOCK_WAITS,

GC_CR_BLOCKS_SERVED,

GC_CU_BLOCKS_SERVED,

SPACE_USED,

SPACE_ALLOCATED,

TABLE_SCANS,

ROW_NUMBER ()

OVER (

ORDER BY

ITL_WAITS DESC, LOGICAL_READS DESC, ROWNUM)

RNUM

FROM A) V,

DBA_OBJECTS DO

WHERE V.OBJ# = DO.OBJECT_ID(+) AND RNUM <= :17 AND ROWNUM <= :18),

A_RLW

AS (SELECT V.VIEWTYPE,

V.RNUM,

V.DBID,

DO.OWNER,

NVL (DO.OBJECT_NAME, TO_CHAR (V.OBJ#)) OBJECT_NAME,

DO.SUBOBJECT_NAME,

DO.OBJECT_TYPE,

V.LOGICAL_READS,

V.BUFFER_BUSY_WAITS,

V.DB_BLOCK_CHANGES,

V.PHYSICAL_READS,

V.PHYSICAL_WRITES,

V.PHYSICAL_READS_DIRECT,

V.PHYSICAL_WRITES_DIRECT,

V.ITL_WAITS,

V.ROW_LOCK_WAITS,

V.GC_CR_BLOCKS_SERVED,

V.GC_CU_BLOCKS_SERVED,

V.SPACE_USED,

V.SPACE_ALLOCATED,

V.TABLE_SCANS

FROM (SELECT 'ROW LOCK WAITS' VIEWTYPE,

DBID,

OBJ#,

LOGICAL_READS,

BUFFER_BUSY_WAITS,

DB_BLOCK_CHANGES,

PHYSICAL_READS,

PHYSICAL_WRITES,

PHYSICAL_READS_DIRECT,

PHYSICAL_WRITES_DIRECT,

ITL_WAITS,

ROW_LOCK_WAITS,

GC_CR_BLOCKS_SERVED,

GC_CU_BLOCKS_SERVED,

SPACE_USED,

SPACE_ALLOCATED,

TABLE_SCANS,

ROW_NUMBER ()

OVER (

ORDER BY

ROW_LOCK_WAITS DESC, LOGICAL_READS DESC, ROWNUM)

RNUM

FROM A) V,

DBA_OBJECTS DO

WHERE V.OBJ# = DO.OBJECT_ID(+) AND RNUM <= :19 AND ROWNUM <= :20),

A_GCRBS

AS (SELECT V.VIEWTYPE,

V.RNUM,

V.DBID,

DO.OWNER,

NVL (DO.OBJECT_NAME, TO_CHAR (V.OBJ#)) OBJECT_NAME,

DO.SUBOBJECT_NAME,

DO.OBJECT_TYPE,

V.LOGICAL_READS,

V.BUFFER_BUSY_WAITS,

V.DB_BLOCK_CHANGES,

V.PHYSICAL_READS,

V.PHYSICAL_WRITES,

V.PHYSICAL_READS_DIRECT,

V.PHYSICAL_WRITES_DIRECT,

V.ITL_WAITS,

V.ROW_LOCK_WAITS,

V.GC_CR_BLOCKS_SERVED,

V.GC_CU_BLOCKS_SERVED,

V.SPACE_USED,

V.SPACE_ALLOCATED,

V.TABLE_SCANS

FROM (SELECT 'GC CR BLOCKS SERVED' VIEWTYPE,

DBID,

OBJ#,

LOGICAL_READS,

BUFFER_BUSY_WAITS,

DB_BLOCK_CHANGES,

PHYSICAL_READS,

PHYSICAL_WRITES,

PHYSICAL_READS_DIRECT,

PHYSICAL_WRITES_DIRECT,

ITL_WAITS,

ROW_LOCK_WAITS,

GC_CR_BLOCKS_SERVED,

GC_CU_BLOCKS_SERVED,

SPACE_USED,

SPACE_ALLOCATED,

TABLE_SCANS,

ROW_NUMBER ()

OVER (

ORDER BY

GC_CR_BLOCKS_SERVED DESC,

LOGICAL_READS DESC,

ROWNUM)

RNUM

FROM A) V,

DBA_OBJECTS DO

WHERE V.OBJ# = DO.OBJECT_ID(+) AND RNUM <= :21 AND ROWNUM <= :22),

A_GCUBS

AS (SELECT V.VIEWTYPE,

V.RNUM,

V.DBID,

DO.OWNER,

NVL (DO.OBJECT_NAME, TO_CHAR (V.OBJ#)) OBJECT_NAME,

DO.SUBOBJECT_NAME,

DO.OBJECT_TYPE,

V.LOGICAL_READS,

V.BUFFER_BUSY_WAITS,

V.DB_BLOCK_CHANGES,

V.PHYSICAL_READS,

V.PHYSICAL_WRITES,

V.PHYSICAL_READS_DIRECT,

V.PHYSICAL_WRITES_DIRECT,

V.ITL_WAITS,

V.ROW_LOCK_WAITS,

V.GC_CR_BLOCKS_SERVED,

V.GC_CU_BLOCKS_SERVED,

V.SPACE_USED,

V.SPACE_ALLOCATED,

V.TABLE_SCANS

FROM (SELECT 'GC CU BLOCKS SERVED' VIEWTYPE,

DBID,

OBJ#,

LOGICAL_READS,

BUFFER_BUSY_WAITS,

DB_BLOCK_CHANGES,

PHYSICAL_READS,

PHYSICAL_WRITES,

PHYSICAL_READS_DIRECT,

PHYSICAL_WRITES_DIRECT,

ITL_WAITS,

ROW_LOCK_WAITS,

GC_CR_BLOCKS_SERVED,

GC_CU_BLOCKS_SERVED,

SPACE_USED,

SPACE_ALLOCATED,

TABLE_SCANS,

ROW_NUMBER ()

OVER (

ORDER BY

GC_CU_BLOCKS_SERVED DESC,

LOGICAL_READS DESC,

ROWNUM)

RNUM

FROM A) V,

DBA_OBJECTS DO

WHERE V.OBJ# = DO.OBJECT_ID(+) AND RNUM <= :23 AND ROWNUM <= :24),

A_SU

AS (SELECT V.VIEWTYPE,

V.RNUM,

V.DBID,

DO.OWNER,

NVL (DO.OBJECT_NAME, TO_CHAR (V.OBJ#)) OBJECT_NAME,

DO.SUBOBJECT_NAME,

DO.OBJECT_TYPE,

V.LOGICAL_READS,

V.BUFFER_BUSY_WAITS,

V.DB_BLOCK_CHANGES,

V.PHYSICAL_READS,

V.PHYSICAL_WRITES,

V.PHYSICAL_READS_DIRECT,

V.PHYSICAL_WRITES_DIRECT,

V.ITL_WAITS,

V.ROW_LOCK_WAITS,

V.GC_CR_BLOCKS_SERVED,

V.GC_CU_BLOCKS_SERVED,

V.SPACE_USED,

V.SPACE_ALLOCATED,

V.TABLE_SCANS

FROM (SELECT 'SPACE USED' VIEWTYPE,

DBID,

OBJ#,

LOGICAL_READS,

BUFFER_BUSY_WAITS,

DB_BLOCK_CHANGES,

PHYSICAL_READS,

PHYSICAL_WRITES,

PHYSICAL_READS_DIRECT,

PHYSICAL_WRITES_DIRECT,

ITL_WAITS,

ROW_LOCK_WAITS,

GC_CR_BLOCKS_SERVED,

GC_CU_BLOCKS_SERVED,

SPACE_USED,

SPACE_ALLOCATED,

TABLE_SCANS,

ROW_NUMBER ()

OVER (

ORDER BY

SPACE_USED DESC, LOGICAL_READS DESC, ROWNUM)

RNUM

FROM A) V,

DBA_OBJECTS DO

WHERE V.OBJ# = DO.OBJECT_ID(+) AND RNUM <= :25 AND ROWNUM <= :26),

A_SA

AS (SELECT V.VIEWTYPE,

V.RNUM,

V.DBID,

DO.OWNER,

NVL (DO.OBJECT_NAME, TO_CHAR (V.OBJ#)) OBJECT_NAME,

DO.SUBOBJECT_NAME,

DO.OBJECT_TYPE,

V.LOGICAL_READS,

V.BUFFER_BUSY_WAITS,

V.DB_BLOCK_CHANGES,

V.PHYSICAL_READS,

V.PHYSICAL_WRITES,

V.PHYSICAL_READS_DIRECT,

V.PHYSICAL_WRITES_DIRECT,

V.ITL_WAITS,

V.ROW_LOCK_WAITS,

V.GC_CR_BLOCKS_SERVED,

V.GC_CU_BLOCKS_SERVED,

V.SPACE_USED,

V.SPACE_ALLOCATED,

V.TABLE_SCANS

FROM (SELECT 'SPACE ALLOCATED' VIEWTYPE,

DBID,

OBJ#,

LOGICAL_READS,

BUFFER_BUSY_WAITS,

DB_BLOCK_CHANGES,

PHYSICAL_READS,

PHYSICAL_WRITES,

PHYSICAL_READS_DIRECT,

PHYSICAL_WRITES_DIRECT,

ITL_WAITS,

ROW_LOCK_WAITS,

GC_CR_BLOCKS_SERVED,

GC_CU_BLOCKS_SERVED,

SPACE_USED,

SPACE_ALLOCATED,

TABLE_SCANS,

ROW_NUMBER ()

OVER (

ORDER BY

SPACE_ALLOCATED DESC,

LOGICAL_READS DESC,

ROWNUM)

RNUM

FROM A) V,

DBA_OBJECTS DO

WHERE V.OBJ# = DO.OBJECT_ID(+) AND RNUM <= :27 AND ROWNUM <= :28),

A_TS

AS (SELECT V.VIEWTYPE,

V.RNUM,

V.DBID,

DO.OWNER,

NVL (DO.OBJECT_NAME, TO_CHAR (V.OBJ#)) OBJECT_NAME,

DO.SUBOBJECT_NAME,

DO.OBJECT_TYPE,

V.LOGICAL_READS,

V.BUFFER_BUSY_WAITS,

V.DB_BLOCK_CHANGES,

V.PHYSICAL_READS,

V.PHYSICAL_WRITES,

V.PHYSICAL_READS_DIRECT,

V.PHYSICAL_WRITES_DIRECT,

V.ITL_WAITS,

V.ROW_LOCK_WAITS,

V.GC_CR_BLOCKS_SERVED,

V.GC_CU_BLOCKS_SERVED,

V.SPACE_USED,

V.SPACE_ALLOCATED,

V.TABLE_SCANS

FROM (SELECT 'TABLE SCANS' VIEWTYPE,

DBID,

OBJ#,

LOGICAL_READS,

BUFFER_BUSY_WAITS,

DB_BLOCK_CHANGES,

PHYSICAL_READS,

PHYSICAL_WRITES,

PHYSICAL_READS_DIRECT,

PHYSICAL_WRITES_DIRECT,

ITL_WAITS,

ROW_LOCK_WAITS,

GC_CR_BLOCKS_SERVED,

GC_CU_BLOCKS_SERVED,

SPACE_USED,

SPACE_ALLOCATED,

TABLE_SCANS,

ROW_NUMBER ()

OVER (

ORDER BY

TABLE_SCANS DESC, LOGICAL_READS DESC, ROWNUM)

RNUM

FROM A) V,

DBA_OBJECTS DO

WHERE V.OBJ# = DO.OBJECT_ID(+) AND RNUM <= :29 AND ROWNUM <= :30)

SELECT /*+ OPT_PARAM('_gby_hash_aggregation_enabled','TRUE') OPT_PARAM('_optimizer_distinct_agg_transform','FALSE') BUG_9002336 */

A.VIEWTYPE,

A.TOR,

A.RNUM,

A.OWNER,

DECODE (A.SUBOBJECT_NAME,

NULL, A.OBJECT_NAME,

A.OBJECT_NAME || '(' || A.SUBOBJECT_NAME || ')')

OBJECT_NAME,

A.OBJECT_TYPE,

TO_CHAR (A.LOGICAL_READS,

'FM999,999,999,999,999,999,999,999,999,999,999')

LOGICAL_READS,

TO_CHAR (A.BUFFER_BUSY_WAITS,

'FM999,999,999,999,999,999,999,999,999,999,999')

BUFFER_BUSY_WAITS,

TO_CHAR (A.DB_BLOCK_CHANGES,

'FM999,999,999,999,999,999,999,999,999,999,999')

DB_BLOCK_CHANGES,

TO_CHAR (A.PHYSICAL_READS,

'FM999,999,999,999,999,999,999,999,999,999,999')

PHYSICAL_READS,

TO_CHAR (A.PHYSICAL_WRITES,

'FM999,999,999,999,999,999,999,999,999,999,999')

PHYSICAL_WRITES,

TO_CHAR (A.PHYSICAL_READS_DIRECT,

'FM999,999,999,999,999,999,999,999,999,999,999')

PHYSICAL_READS_DIRECT,

TO_CHAR (A.PHYSICAL_WRITES_DIRECT,

'FM999,999,999,999,999,999,999,999,999,999,999')

PHYSICAL_WRITES_DIRECT,

TO_CHAR (A.ITL_WAITS, 'FM999,999,999,999,999,999,999,999,999,999,999')

ITL_WAITS,

TO_CHAR (A.ROW_LOCK_WAITS,

'FM999,999,999,999,999,999,999,999,999,999,999')

ROW_LOCK_WAITS,

TO_CHAR (A.GC_CR_BLOCKS_SERVED,

'FM999,999,999,999,999,999,999,999,999,999,999')

GC_CR_BLOCKS_SERVED,

TO_CHAR (A.GC_CU_BLOCKS_SERVED,

'FM999,999,999,999,999,999,999,999,999,999,999')

GC_CU_BLOCKS_SERVED,

TO_CHAR (A.SPACE_USED,

'FM999,999,999,999,999,999,999,999,999,999,999')

SPACE_USED,

TO_CHAR (A.SPACE_ALLOCATED,

'FM999,999,999,999,999,999,999,999,999,999,999')

SPACE_ALLOCATED,

TO_CHAR (A.TABLE_SCANS,

'FM999,999,999,999,999,999,999,999,999,999,999')

TABLE_SCANS

FROM (SELECT VIEWTYPE,

RNUM,

DBID,

OWNER,

OBJECT_NAME,

SUBOBJECT_NAME,

OBJECT_TYPE,

LOGICAL_READS,

BUFFER_BUSY_WAITS,

DB_BLOCK_CHANGES,

PHYSICAL_READS,

PHYSICAL_WRITES,

PHYSICAL_READS_DIRECT,

PHYSICAL_WRITES_DIRECT,

ITL_WAITS,

ROW_LOCK_WAITS,

GC_CR_BLOCKS_SERVED,

GC_CU_BLOCKS_SERVED,

SPACE_USED,

SPACE_ALLOCATED,

TABLE_SCANS,

1 TOR

FROM A_LR

UNION ALL

SELECT 'LOGICAL READS' VIEWTYPE,

NULL RNUM,

0 DBID,

NULL OWNER,

'ETC' OBJECT_NAME,

NULL SUBOBJECT_NAME,

NULL OBJECT_TYPE,

V2.LOGICAL_READS - V1.LOGICAL_READS LOGICAL_READS,

V2.BUFFER_BUSY_WAITS - V1.BUFFER_BUSY_WAITS BUFFER_BUSY_WAITS,

V2.DB_BLOCK_CHANGES - V1.DB_BLOCK_CHANGES DB_BLOCK_CHANGES,

V2.PHYSICAL_READS - V1.PHYSICAL_READS PHYSICAL_READS,

V2.PHYSICAL_WRITES - V1.PHYSICAL_WRITES PHYSICAL_WRITES,

V2.PHYSICAL_READS_DIRECT - V1.PHYSICAL_READS_DIRECT

PHYSICAL_READS_DIRECT,

V2.PHYSICAL_WRITES_DIRECT - V1.PHYSICAL_WRITES_DIRECT

PHYSICAL_WRITES_DIRECT,

V2.ITL_WAITS - V1.ITL_WAITS ITL_WAITS,

V2.ROW_LOCK_WAITS - V1.ROW_LOCK_WAITS ROW_LOCK_WAITS,

V2.GC_CR_BLOCKS_SERVED - V1.GC_CR_BLOCKS_SERVED

GC_CR_BLOCKS_SERVED,

V2.GC_CU_BLOCKS_SERVED - V1.GC_CU_BLOCKS_SERVED

GC_CU_BLOCKS_SERVED,

V2.SPACE_USED - V1.SPACE_USED SPACE_USED,

V2.SPACE_ALLOCATED - V1.SPACE_ALLOCATED SPACE_ALLOCATED,

V2.TABLE_SCANS - V1.TABLE_SCANS TABLE_SCANS,

1 TOR

FROM (SELECT SUM (LOGICAL_READS) LOGICAL_READS,

SUM (BUFFER_BUSY_WAITS) BUFFER_BUSY_WAITS,

SUM (DB_BLOCK_CHANGES) DB_BLOCK_CHANGES,

SUM (PHYSICAL_READS) PHYSICAL_READS,

SUM (PHYSICAL_WRITES) PHYSICAL_WRITES,

SUM (PHYSICAL_READS_DIRECT) PHYSICAL_READS_DIRECT,

SUM (PHYSICAL_WRITES_DIRECT) PHYSICAL_WRITES_DIRECT,

SUM (ITL_WAITS) ITL_WAITS,

SUM (ROW_LOCK_WAITS) ROW_LOCK_WAITS,

SUM (GC_CR_BLOCKS_SERVED) GC_CR_BLOCKS_SERVED,

SUM (GC_CU_BLOCKS_SERVED) GC_CU_BLOCKS_SERVED,

SUM (SPACE_USED) SPACE_USED,

SUM (SPACE_ALLOCATED) SPACE_ALLOCATED,

SUM (TABLE_SCANS) TABLE_SCANS

FROM A_LR) V1,

(SELECT SUM (LOGICAL_READS) LOGICAL_READS,

SUM (BUFFER_BUSY_WAITS) BUFFER_BUSY_WAITS,

SUM (DB_BLOCK_CHANGES) DB_BLOCK_CHANGES,

SUM (PHYSICAL_READS) PHYSICAL_READS,

SUM (PHYSICAL_WRITES) PHYSICAL_WRITES,

SUM (PHYSICAL_READS_DIRECT) PHYSICAL_READS_DIRECT,

SUM (PHYSICAL_WRITES_DIRECT) PHYSICAL_WRITES_DIRECT,

SUM (ITL_WAITS) ITL_WAITS,

SUM (ROW_LOCK_WAITS) ROW_LOCK_WAITS,

SUM (GC_CR_BLOCKS_SERVED) GC_CR_BLOCKS_SERVED,

SUM (GC_CU_BLOCKS_SERVED) GC_CU_BLOCKS_SERVED,

SUM (SPACE_USED) SPACE_USED,

SUM (SPACE_ALLOCATED) SPACE_ALLOCATED,

SUM (TABLE_SCANS) TABLE_SCANS

FROM A_TOT) V2

UNION ALL

SELECT VIEWTYPE,

RNUM,

DBID,

OWNER,

OBJECT_NAME,

SUBOBJECT_NAME,

OBJECT_TYPE,

LOGICAL_READS,

BUFFER_BUSY_WAITS,

DB_BLOCK_CHANGES,

PHYSICAL_READS,

PHYSICAL_WRITES,

PHYSICAL_READS_DIRECT,

PHYSICAL_WRITES_DIRECT,

ITL_WAITS,

ROW_LOCK_WAITS,

GC_CR_BLOCKS_SERVED,

GC_CU_BLOCKS_SERVED,

SPACE_USED,

SPACE_ALLOCATED,

TABLE_SCANS,

3 TOR

FROM A_BBW

UNION ALL

SELECT 'BUFFER BUSY WAITS' VIEWTYPE,

NULL RNUM,

0 DBID,

NULL OWNER,

'ETC' OBJECT_NAME,

NULL SUBOBJECT_NAME,

NULL OBJECT_TYPE,

V2.LOGICAL_READS - V1.LOGICAL_READS LOGICAL_READS,

V2.BUFFER_BUSY_WAITS - V1.BUFFER_BUSY_WAITS BUFFER_BUSY_WAITS,

V2.DB_BLOCK_CHANGES - V1.DB_BLOCK_CHANGES DB_BLOCK_CHANGES,

V2.PHYSICAL_READS - V1.PHYSICAL_READS PHYSICAL_READS,

V2.PHYSICAL_WRITES - V1.PHYSICAL_WRITES PHYSICAL_WRITES,

V2.PHYSICAL_READS_DIRECT - V1.PHYSICAL_READS_DIRECT

PHYSICAL_READS_DIRECT,

V2.PHYSICAL_WRITES_DIRECT - V1.PHYSICAL_WRITES_DIRECT

PHYSICAL_WRITES_DIRECT,

V2.ITL_WAITS - V1.ITL_WAITS ITL_WAITS,

V2.ROW_LOCK_WAITS - V1.ROW_LOCK_WAITS ROW_LOCK_WAITS,

V2.GC_CR_BLOCKS_SERVED - V1.GC_CR_BLOCKS_SERVED

GC_CR_BLOCKS_SERVED,

V2.GC_CU_BLOCKS_SERVED - V1.GC_CU_BLOCKS_SERVED

GC_CU_BLOCKS_SERVED,

V2.SPACE_USED - V1.SPACE_USED SPACE_USED,

V2.SPACE_ALLOCATED - V1.SPACE_ALLOCATED SPACE_ALLOCATED,

V2.TABLE_SCANS - V1.TABLE_SCANS TABLE_SCANS,

3 TOR

FROM (SELECT SUM (LOGICAL_READS) LOGICAL_READS,

SUM (BUFFER_BUSY_WAITS) BUFFER_BUSY_WAITS,

SUM (DB_BLOCK_CHANGES) DB_BLOCK_CHANGES,

SUM (PHYSICAL_READS) PHYSICAL_READS,

SUM (PHYSICAL_WRITES) PHYSICAL_WRITES,

SUM (PHYSICAL_READS_DIRECT) PHYSICAL_READS_DIRECT,

SUM (PHYSICAL_WRITES_DIRECT) PHYSICAL_WRITES_DIRECT,

SUM (ITL_WAITS) ITL_WAITS,

SUM (ROW_LOCK_WAITS) ROW_LOCK_WAITS,

SUM (GC_CR_BLOCKS_SERVED) GC_CR_BLOCKS_SERVED,

SUM (GC_CU_BLOCKS_SERVED) GC_CU_BLOCKS_SERVED,

SUM (SPACE_USED) SPACE_USED,

SUM (SPACE_ALLOCATED) SPACE_ALLOCATED,

SUM (TABLE_SCANS) TABLE_SCANS

FROM A_BBW) V1,

(SELECT SUM (LOGICAL_READS) LOGICAL_READS,

SUM (BUFFER_BUSY_WAITS) BUFFER_BUSY_WAITS,

SUM (DB_BLOCK_CHANGES) DB_BLOCK_CHANGES,

SUM (PHYSICAL_READS) PHYSICAL_READS,

SUM (PHYSICAL_WRITES) PHYSICAL_WRITES,

SUM (PHYSICAL_READS_DIRECT) PHYSICAL_READS_DIRECT,

SUM (PHYSICAL_WRITES_DIRECT) PHYSICAL_WRITES_DIRECT,

SUM (ITL_WAITS) ITL_WAITS,

SUM (ROW_LOCK_WAITS) ROW_LOCK_WAITS,

SUM (GC_CR_BLOCKS_SERVED) GC_CR_BLOCKS_SERVED,

SUM (GC_CU_BLOCKS_SERVED) GC_CU_BLOCKS_SERVED,

SUM (SPACE_USED) SPACE_USED,

SUM (SPACE_ALLOCATED) SPACE_ALLOCATED,

SUM (TABLE_SCANS) TABLE_SCANS

FROM A_TOT) V2

UNION ALL

SELECT VIEWTYPE,

RNUM,

DBID,

OWNER,

OBJECT_NAME,

SUBOBJECT_NAME,

OBJECT_TYPE,

LOGICAL_READS,

BUFFER_BUSY_WAITS,

DB_BLOCK_CHANGES,

PHYSICAL_READS,

PHYSICAL_WRITES,

PHYSICAL_READS_DIRECT,

PHYSICAL_WRITES_DIRECT,

ITL_WAITS,

ROW_LOCK_WAITS,

GC_CR_BLOCKS_SERVED,

GC_CU_BLOCKS_SERVED,

SPACE_USED,

SPACE_ALLOCATED,

TABLE_SCANS,

4 TOR

FROM A_DBC

UNION ALL

SELECT 'DB_BLOCK CHANGES' VIEWTYPE,

NULL RNUM,

0 DBID,

NULL OWNER,

'ETC' OBJECT_NAME,

NULL SUBOBJECT_NAME,

NULL OBJECT_TYPE,

V2.LOGICAL_READS - V1.LOGICAL_READS LOGICAL_READS,

V2.BUFFER_BUSY_WAITS - V1.BUFFER_BUSY_WAITS BUFFER_BUSY_WAITS,

V2.DB_BLOCK_CHANGES - V1.DB_BLOCK_CHANGES DB_BLOCK_CHANGES,

V2.PHYSICAL_READS - V1.PHYSICAL_READS PHYSICAL_READS,

V2.PHYSICAL_WRITES - V1.PHYSICAL_WRITES PHYSICAL_WRITES,

V2.PHYSICAL_READS_DIRECT - V1.PHYSICAL_READS_DIRECT

PHYSICAL_READS_DIRECT,

V2.PHYSICAL_WRITES_DIRECT - V1.PHYSICAL_WRITES_DIRECT

PHYSICAL_WRITES_DIRECT,

V2.ITL_WAITS - V1.ITL_WAITS ITL_WAITS,

V2.ROW_LOCK_WAITS - V1.ROW_LOCK_WAITS ROW_LOCK_WAITS,

V2.GC_CR_BLOCKS_SERVED - V1.GC_CR_BLOCKS_SERVED

GC_CR_BLOCKS_SERVED,

V2.GC_CU_BLOCKS_SERVED - V1.GC_CU_BLOCKS_SERVED

GC_CU_BLOCKS_SERVED,

V2.SPACE_USED - V1.SPACE_USED SPACE_USED,

V2.SPACE_ALLOCATED - V1.SPACE_ALLOCATED SPACE_ALLOCATED,

V2.TABLE_SCANS - V1.TABLE_SCANS TABLE_SCANS,

4 TOR

FROM (SELECT SUM (LOGICAL_READS) LOGICAL_READS,

SUM (BUFFER_BUSY_WAITS) BUFFER_BUSY_WAITS,

SUM (DB_BLOCK_CHANGES) DB_BLOCK_CHANGES,

SUM (PHYSICAL_READS) PHYSICAL_READS,

SUM (PHYSICAL_WRITES) PHYSICAL_WRITES,

SUM (PHYSICAL_READS_DIRECT) PHYSICAL_READS_DIRECT,

SUM (PHYSICAL_WRITES_DIRECT) PHYSICAL_WRITES_DIRECT,

SUM (ITL_WAITS) ITL_WAITS,

SUM (ROW_LOCK_WAITS) ROW_LOCK_WAITS,

SUM (GC_CR_BLOCKS_SERVED) GC_CR_BLOCKS_SERVED,

SUM (GC_CU_BLOCKS_SERVED) GC_CU_BLOCKS_SERVED,

SUM (SPACE_USED) SPACE_USED,

SUM (SPACE_ALLOCATED) SPACE_ALLOCATED,

SUM (TABLE_SCANS) TABLE_SCANS

FROM A_DBC) V1,

(SELECT SUM (LOGICAL_READS) LOGICAL_READS,

SUM (BUFFER_BUSY_WAITS) BUFFER_BUSY_WAITS,

SUM (DB_BLOCK_CHANGES) DB_BLOCK_CHANGES,

SUM (PHYSICAL_READS) PHYSICAL_READS,

SUM (PHYSICAL_WRITES) PHYSICAL_WRITES,

SUM (PHYSICAL_READS_DIRECT) PHYSICAL_READS_DIRECT,

SUM (PHYSICAL_WRITES_DIRECT) PHYSICAL_WRITES_DIRECT,

SUM (ITL_WAITS) ITL_WAITS,

SUM (ROW_LOCK_WAITS) ROW_LOCK_WAITS,

SUM (GC_CR_BLOCKS_SERVED) GC_CR_BLOCKS_SERVED,

SUM (GC_CU_BLOCKS_SERVED) GC_CU_BLOCKS_SERVED,

SUM (SPACE_USED) SPACE_USED,

SUM (SPACE_ALLOCATED) SPACE_ALLOCATED,

SUM (TABLE_SCANS) TABLE_SCANS

FROM A_TOT) V2

UNION ALL

SELECT VIEWTYPE,

RNUM,

DBID,

OWNER,

OBJECT_NAME,

SUBOBJECT_NAME,

OBJECT_TYPE,

LOGICAL_READS,

BUFFER_BUSY_WAITS,

DB_BLOCK_CHANGES,

PHYSICAL_READS,

PHYSICAL_WRITES,

PHYSICAL_READS_DIRECT,

PHYSICAL_WRITES_DIRECT,

ITL_WAITS,

ROW_LOCK_WAITS,

GC_CR_BLOCKS_SERVED,

GC_CU_BLOCKS_SERVED,

SPACE_USED,

SPACE_ALLOCATED,

TABLE_SCANS,

2 TOR

FROM A_PR

UNION ALL

SELECT 'PHYSICAL READS' VIEWTYPE,

NULL RNUM,

0 DBID,

NULL OWNER,

'ETC' OBJECT_NAME,

NULL SUBOBJECT_NAME,

NULL OBJECT_TYPE,

V2.LOGICAL_READS - V1.LOGICAL_READS LOGICAL_READS,

V2.BUFFER_BUSY_WAITS - V1.BUFFER_BUSY_WAITS BUFFER_BUSY_WAITS,

V2.DB_BLOCK_CHANGES - V1.DB_BLOCK_CHANGES DB_BLOCK_CHANGES,

V2.PHYSICAL_READS - V1.PHYSICAL_READS PHYSICAL_READS,

V2.PHYSICAL_WRITES - V1.PHYSICAL_WRITES PHYSICAL_WRITES,

V2.PHYSICAL_READS_DIRECT - V1.PHYSICAL_READS_DIRECT

PHYSICAL_READS_DIRECT,

V2.PHYSICAL_WRITES_DIRECT - V1.PHYSICAL_WRITES_DIRECT

PHYSICAL_WRITES_DIRECT,

V2.ITL_WAITS - V1.ITL_WAITS ITL_WAITS,

V2.ROW_LOCK_WAITS - V1.ROW_LOCK_WAITS ROW_LOCK_WAITS,

V2.GC_CR_BLOCKS_SERVED - V1.GC_CR_BLOCKS_SERVED

GC_CR_BLOCKS_SERVED,

V2.GC_CU_BLOCKS_SERVED - V1.GC_CU_BLOCKS_SERVED

GC_CU_BLOCKS_SERVED,

V2.SPACE_USED - V1.SPACE_USED SPACE_USED,

V2.SPACE_ALLOCATED - V1.SPACE_ALLOCATED SPACE_ALLOCATED,

V2.TABLE_SCANS - V1.TABLE_SCANS TABLE_SCANS,

2 TOR

FROM (SELECT SUM (LOGICAL_READS) LOGICAL_READS,

SUM (BUFFER_BUSY_WAITS) BUFFER_BUSY_WAITS,

SUM (DB_BLOCK_CHANGES) DB_BLOCK_CHANGES,

SUM (PHYSICAL_READS) PHYSICAL_READS,

SUM (PHYSICAL_WRITES) PHYSICAL_WRITES,

SUM (PHYSICAL_READS_DIRECT) PHYSICAL_READS_DIRECT,

SUM (PHYSICAL_WRITES_DIRECT) PHYSICAL_WRITES_DIRECT,

SUM (ITL_WAITS) ITL_WAITS,

SUM (ROW_LOCK_WAITS) ROW_LOCK_WAITS,

SUM (GC_CR_BLOCKS_SERVED) GC_CR_BLOCKS_SERVED,

SUM (GC_CU_BLOCKS_SERVED) GC_CU_BLOCKS_SERVED,

SUM (SPACE_USED) SPACE_USED,

SUM (SPACE_ALLOCATED) SPACE_ALLOCATED,

SUM (TABLE_SCANS) TABLE_SCANS

FROM A_PR) V1,

(SELECT SUM (LOGICAL_READS) LOGICAL_READS,

SUM (BUFFER_BUSY_WAITS) BUFFER_BUSY_WAITS,

SUM (DB_BLOCK_CHANGES) DB_BLOCK_CHANGES,

SUM (PHYSICAL_READS) PHYSICAL_READS,

SUM (PHYSICAL_WRITES) PHYSICAL_WRITES,

SUM (PHYSICAL_READS_DIRECT) PHYSICAL_READS_DIRECT,

SUM (PHYSICAL_WRITES_DIRECT) PHYSICAL_WRITES_DIRECT,

SUM (ITL_WAITS) ITL_WAITS,

SUM (ROW_LOCK_WAITS) ROW_LOCK_WAITS,

SUM (GC_CR_BLOCKS_SERVED) GC_CR_BLOCKS_SERVED,

SUM (GC_CU_BLOCKS_SERVED) GC_CU_BLOCKS_SERVED,

SUM (SPACE_USED) SPACE_USED,

SUM (SPACE_ALLOCATED) SPACE_ALLOCATED,

SUM (TABLE_SCANS) TABLE_SCANS

FROM A_TOT) V2

UNION ALL

SELECT VIEWTYPE,

RNUM,

DBID,

OWNER,

OBJECT_NAME,

SUBOBJECT_NAME,

OBJECT_TYPE,

LOGICAL_READS,

BUFFER_BUSY_WAITS,

DB_BLOCK_CHANGES,

PHYSICAL_READS,

PHYSICAL_WRITES,

PHYSICAL_READS_DIRECT,

PHYSICAL_WRITES_DIRECT,

ITL_WAITS,

ROW_LOCK_WAITS,

GC_CR_BLOCKS_SERVED,

GC_CU_BLOCKS_SERVED,

SPACE_USED,

SPACE_ALLOCATED,

TABLE_SCANS,

5 TOR

FROM A_PW

UNION ALL

SELECT 'PHYSICAL WRITES' VIEWTYPE,

NULL RNUM,

0 DBID,

NULL OWNER,

'ETC' OBJECT_NAME,

NULL SUBOBJECT_NAME,

NULL OBJECT_TYPE,

V2.LOGICAL_READS - V1.LOGICAL_READS LOGICAL_READS,

V2.BUFFER_BUSY_WAITS - V1.BUFFER_BUSY_WAITS BUFFER_BUSY_WAITS,

V2.DB_BLOCK_CHANGES - V1.DB_BLOCK_CHANGES DB_BLOCK_CHANGES,

V2.PHYSICAL_READS - V1.PHYSICAL_READS PHYSICAL_READS,

V2.PHYSICAL_WRITES - V1.PHYSICAL_WRITES PHYSICAL_WRITES,

V2.PHYSICAL_READS_DIRECT - V1.PHYSICAL_READS_DIRECT

PHYSICAL_READS_DIRECT,

V2.PHYSICAL_WRITES_DIRECT - V1.PHYSICAL_WRITES_DIRECT

PHYSICAL_WRITES_DIRECT,

V2.ITL_WAITS - V1.ITL_WAITS ITL_WAITS,

V2.ROW_LOCK_WAITS - V1.ROW_LOCK_WAITS ROW_LOCK_WAITS,

V2.GC_CR_BLOCKS_SERVED - V1.GC_CR_BLOCKS_SERVED

GC_CR_BLOCKS_SERVED,

V2.GC_CU_BLOCKS_SERVED - V1.GC_CU_BLOCKS_SERVED

GC_CU_BLOCKS_SERVED,

V2.SPACE_USED - V1.SPACE_USED SPACE_USED,

V2.SPACE_ALLOCATED - V1.SPACE_ALLOCATED SPACE_ALLOCATED,

V2.TABLE_SCANS - V1.TABLE_SCANS TABLE_SCANS,

5 TOR

FROM (SELECT SUM (LOGICAL_READS) LOGICAL_READS,

SUM (BUFFER_BUSY_WAITS) BUFFER_BUSY_WAITS,

SUM (DB_BLOCK_CHANGES) DB_BLOCK_CHANGES,

SUM (PHYSICAL_READS) PHYSICAL_READS,

SUM (PHYSICAL_WRITES) PHYSICAL_WRITES,

SUM (PHYSICAL_READS_DIRECT) PHYSICAL_READS_DIRECT,

SUM (PHYSICAL_WRITES_DIRECT) PHYSICAL_WRITES_DIRECT,

SUM (ITL_WAITS) ITL_WAITS,

SUM (ROW_LOCK_WAITS) ROW_LOCK_WAITS,

SUM (GC_CR_BLOCKS_SERVED) GC_CR_BLOCKS_SERVED,

SUM (GC_CU_BLOCKS_SERVED) GC_CU_BLOCKS_SERVED,

SUM (SPACE_USED) SPACE_USED,

SUM (SPACE_ALLOCATED) SPACE_ALLOCATED,

SUM (TABLE_SCANS) TABLE_SCANS

FROM A_PW) V1,

(SELECT SUM (LOGICAL_READS) LOGICAL_READS,

SUM (BUFFER_BUSY_WAITS) BUFFER_BUSY_WAITS,

SUM (DB_BLOCK_CHANGES) DB_BLOCK_CHANGES,

SUM (PHYSICAL_READS) PHYSICAL_READS,

SUM (PHYSICAL_WRITES) PHYSICAL_WRITES,

SUM (PHYSICAL_READS_DIRECT) PHYSICAL_READS_DIRECT,

SUM (PHYSICAL_WRITES_DIRECT) PHYSICAL_WRITES_DIRECT,

SUM (ITL_WAITS) ITL_WAITS,

SUM (ROW_LOCK_WAITS) ROW_LOCK_WAITS,

SUM (GC_CR_BLOCKS_SERVED) GC_CR_BLOCKS_SERVED,

SUM (GC_CU_BLOCKS_SERVED) GC_CU_BLOCKS_SERVED,

SUM (SPACE_USED) SPACE_USED,

SUM (SPACE_ALLOCATED) SPACE_ALLOCATED,

SUM (TABLE_SCANS) TABLE_SCANS

FROM A_TOT) V2

UNION ALL

SELECT VIEWTYPE,

RNUM,

DBID,

OWNER,

OBJECT_NAME,

SUBOBJECT_NAME,

OBJECT_TYPE,

LOGICAL_READS,

BUFFER_BUSY_WAITS,

DB_BLOCK_CHANGES,

PHYSICAL_READS,

PHYSICAL_WRITES,

PHYSICAL_READS_DIRECT,

PHYSICAL_WRITES_DIRECT,

ITL_WAITS,

ROW_LOCK_WAITS,

GC_CR_BLOCKS_SERVED,

GC_CU_BLOCKS_SERVED,

SPACE_USED,

SPACE_ALLOCATED,

TABLE_SCANS,

6 TOR

FROM A_PRD

UNION ALL

SELECT 'PHYSICAL READS DIRECT' VIEWTYPE,

NULL RNUM,

0 DBID,

NULL OWNER,

'ETC' OBJECT_NAME,

NULL SUBOBJECT_NAME,

NULL OBJECT_TYPE,

V2.LOGICAL_READS - V1.LOGICAL_READS LOGICAL_READS,

V2.BUFFER_BUSY_WAITS - V1.BUFFER_BUSY_WAITS BUFFER_BUSY_WAITS,

V2.DB_BLOCK_CHANGES - V1.DB_BLOCK_CHANGES DB_BLOCK_CHANGES,

V2.PHYSICAL_READS - V1.PHYSICAL_READS PHYSICAL_READS,

V2.PHYSICAL_WRITES - V1.PHYSICAL_WRITES PHYSICAL_WRITES,

V2.PHYSICAL_READS_DIRECT - V1.PHYSICAL_READS_DIRECT

PHYSICAL_READS_DIRECT,

V2.PHYSICAL_WRITES_DIRECT - V1.PHYSICAL_WRITES_DIRECT

PHYSICAL_WRITES_DIRECT,

V2.ITL_WAITS - V1.ITL_WAITS ITL_WAITS,

V2.ROW_LOCK_WAITS - V1.ROW_LOCK_WAITS ROW_LOCK_WAITS,

V2.GC_CR_BLOCKS_SERVED - V1.GC_CR_BLOCKS_SERVED

GC_CR_BLOCKS_SERVED,

V2.GC_CU_BLOCKS_SERVED - V1.GC_CU_BLOCKS_SERVED

GC_CU_BLOCKS_SERVED,

V2.SPACE_USED - V1.SPACE_USED SPACE_USED,

V2.SPACE_ALLOCATED - V1.SPACE_ALLOCATED SPACE_ALLOCATED,

V2.TABLE_SCANS - V1.TABLE_SCANS TABLE_SCANS,

6 TOR

FROM (SELECT SUM (LOGICAL_READS) LOGICAL_READS,

SUM (BUFFER_BUSY_WAITS) BUFFER_BUSY_WAITS,

SUM (DB_BLOCK_CHANGES) DB_BLOCK_CHANGES,

SUM (PHYSICAL_READS) PHYSICAL_READS,

SUM (PHYSICAL_WRITES) PHYSICAL_WRITES,

SUM (PHYSICAL_READS_DIRECT) PHYSICAL_READS_DIRECT,

SUM (PHYSICAL_WRITES_DIRECT) PHYSICAL_WRITES_DIRECT,

SUM (ITL_WAITS) ITL_WAITS,

SUM (ROW_LOCK_WAITS) ROW_LOCK_WAITS,

SUM (GC_CR_BLOCKS_SERVED) GC_CR_BLOCKS_SERVED,

SUM (GC_CU_BLOCKS_SERVED) GC_CU_BLOCKS_SERVED,

SUM (SPACE_USED) SPACE_USED,

SUM (SPACE_ALLOCATED) SPACE_ALLOCATED,

SUM (TABLE_SCANS) TABLE_SCANS

FROM A_PRD) V1,

(SELECT SUM (LOGICAL_READS) LOGICAL_READS,

SUM (BUFFER_BUSY_WAITS) BUFFER_BUSY_WAITS,

SUM (DB_BLOCK_CHANGES) DB_BLOCK_CHANGES,

SUM (PHYSICAL_READS) PHYSICAL_READS,

SUM (PHYSICAL_WRITES) PHYSICAL_WRITES,

SUM (PHYSICAL_READS_DIRECT) PHYSICAL_READS_DIRECT,

SUM (PHYSICAL_WRITES_DIRECT) PHYSICAL_WRITES_DIRECT,

SUM (ITL_WAITS) ITL_WAITS,

SUM (ROW_LOCK_WAITS) ROW_LOCK_WAITS,

SUM (GC_CR_BLOCKS_SERVED) GC_CR_BLOCKS_SERVED,

SUM (GC_CU_BLOCKS_SERVED) GC_CU_BLOCKS_SERVED,

SUM (SPACE_USED) SPACE_USED,

SUM (SPACE_ALLOCATED) SPACE_ALLOCATED,

SUM (TABLE_SCANS) TABLE_SCANS

FROM A_TOT) V2

UNION ALL

SELECT VIEWTYPE,

RNUM,

DBID,

OWNER,

OBJECT_NAME,

SUBOBJECT_NAME,

OBJECT_TYPE,

LOGICAL_READS,

BUFFER_BUSY_WAITS,

DB_BLOCK_CHANGES,

PHYSICAL_READS,

PHYSICAL_WRITES,

PHYSICAL_READS_DIRECT,

PHYSICAL_WRITES_DIRECT,

ITL_WAITS,

ROW_LOCK_WAITS,

GC_CR_BLOCKS_SERVED,

GC_CU_BLOCKS_SERVED,

SPACE_USED,

SPACE_ALLOCATED,

TABLE_SCANS,

7 TOR

FROM A_IW

UNION ALL

SELECT 'ITL WAITS' VIEWTYPE,

NULL RNUM,

0 DBID,

NULL OWNER,

'ETC' OBJECT_NAME,

NULL SUBOBJECT_NAME,

NULL OBJECT_TYPE,

V2.LOGICAL_READS - V1.LOGICAL_READS LOGICAL_READS,

V2.BUFFER_BUSY_WAITS - V1.BUFFER_BUSY_WAITS BUFFER_BUSY_WAITS,

V2.DB_BLOCK_CHANGES - V1.DB_BLOCK_CHANGES DB_BLOCK_CHANGES,

V2.PHYSICAL_READS - V1.PHYSICAL_READS PHYSICAL_READS,

V2.PHYSICAL_WRITES - V1.PHYSICAL_WRITES PHYSICAL_WRITES,

V2.PHYSICAL_READS_DIRECT - V1.PHYSICAL_READS_DIRECT

PHYSICAL_READS_DIRECT,

V2.PHYSICAL_WRITES_DIRECT - V1.PHYSICAL_WRITES_DIRECT

PHYSICAL_WRITES_DIRECT,

V2.ITL_WAITS - V1.ITL_WAITS ITL_WAITS,

V2.ROW_LOCK_WAITS - V1.ROW_LOCK_WAITS ROW_LOCK_WAITS,

V2.GC_CR_BLOCKS_SERVED - V1.GC_CR_BLOCKS_SERVED

GC_CR_BLOCKS_SERVED,

V2.GC_CU_BLOCKS_SERVED - V1.GC_CU_BLOCKS_SERVED

GC_CU_BLOCKS_SERVED,

V2.SPACE_USED - V1.SPACE_USED SPACE_USED,

V2.SPACE_ALLOCATED - V1.SPACE_ALLOCATED SPACE_ALLOCATED,

V2.TABLE_SCANS - V1.TABLE_SCANS TABLE_SCANS,

7 TOR

FROM (SELECT SUM (LOGICAL_READS) LOGICAL_READS,

SUM (BUFFER_BUSY_WAITS) BUFFER_BUSY_WAITS,

SUM (DB_BLOCK_CHANGES) DB_BLOCK_CHANGES,

SUM (PHYSICAL_READS) PHYSICAL_READS,

SUM (PHYSICAL_WRITES) PHYSICAL_WRITES,

SUM (PHYSICAL_READS_DIRECT) PHYSICAL_READS_DIRECT,

SUM (PHYSICAL_WRITES_DIRECT) PHYSICAL_WRITES_DIRECT,

SUM (ITL_WAITS) ITL_WAITS,

SUM (ROW_LOCK_WAITS) ROW_LOCK_WAITS,

SUM (GC_CR_BLOCKS_SERVED) GC_CR_BLOCKS_SERVED,

SUM (GC_CU_BLOCKS_SERVED) GC_CU_BLOCKS_SERVED,

SUM (SPACE_USED) SPACE_USED,

SUM (SPACE_ALLOCATED) SPACE_ALLOCATED,

SUM (TABLE_SCANS) TABLE_SCANS

FROM A_IW) V1,

(SELECT SUM (LOGICAL_READS) LOGICAL_READS,

SUM (BUFFER_BUSY_WAITS) BUFFER_BUSY_WAITS,

SUM (DB_BLOCK_CHANGES) DB_BLOCK_CHANGES,

SUM (PHYSICAL_READS) PHYSICAL_READS,

SUM (PHYSICAL_WRITES) PHYSICAL_WRITES,

SUM (PHYSICAL_READS_DIRECT) PHYSICAL_READS_DIRECT,

SUM (PHYSICAL_WRITES_DIRECT) PHYSICAL_WRITES_DIRECT,

SUM (ITL_WAITS) ITL_WAITS,

SUM (ROW_LOCK_WAITS) ROW_LOCK_WAITS,

SUM (GC_CR_BLOCKS_SERVED) GC_CR_BLOCKS_SERVED,

SUM (GC_CU_BLOCKS_SERVED) GC_CU_BLOCKS_SERVED,

SUM (SPACE_USED) SPACE_USED,

SUM (SPACE_ALLOCATED) SPACE_ALLOCATED,

SUM (TABLE_SCANS) TABLE_SCANS

FROM A_TOT) V2

UNION ALL

SELECT VIEWTYPE,

RNUM,

DBID,

OWNER,

OBJECT_NAME,

SUBOBJECT_NAME,

OBJECT_TYPE,

LOGICAL_READS,

BUFFER_BUSY_WAITS,

DB_BLOCK_CHANGES,

PHYSICAL_READS,

PHYSICAL_WRITES,

PHYSICAL_READS_DIRECT,

PHYSICAL_WRITES_DIRECT,

ITL_WAITS,

ROW_LOCK_WAITS,

GC_CR_BLOCKS_SERVED,

GC_CU_BLOCKS_SERVED,

SPACE_USED,

SPACE_ALLOCATED,

TABLE_SCANS,

8 TOR

FROM A_RLW

UNION ALL

SELECT 'ROW LOCK WAITS' VIEWTYPE,

NULL RNUM,

0 DBID,

NULL OWNER,

'ETC' OBJECT_NAME,

NULL SUBOBJECT_NAME,

NULL OBJECT_TYPE,

V2.LOGICAL_READS - V1.LOGICAL_READS LOGICAL_READS,

V2.BUFFER_BUSY_WAITS - V1.BUFFER_BUSY_WAITS BUFFER_BUSY_WAITS,

V2.DB_BLOCK_CHANGES - V1.DB_BLOCK_CHANGES DB_BLOCK_CHANGES,

V2.PHYSICAL_READS - V1.PHYSICAL_READS PHYSICAL_READS,

V2.PHYSICAL_WRITES - V1.PHYSICAL_WRITES PHYSICAL_WRITES,

V2.PHYSICAL_READS_DIRECT - V1.PHYSICAL_READS_DIRECT

PHYSICAL_READS_DIRECT,

V2.PHYSICAL_WRITES_DIRECT - V1.PHYSICAL_WRITES_DIRECT

PHYSICAL_WRITES_DIRECT,

V2.ITL_WAITS - V1.ITL_WAITS ITL_WAITS,

V2.ROW_LOCK_WAITS - V1.ROW_LOCK_WAITS ROW_LOCK_WAITS,

V2.GC_CR_BLOCKS_SERVED - V1.GC_CR_BLOCKS_SERVED

GC_CR_BLOCKS_SERVED,

V2.GC_CU_BLOCKS_SERVED - V1.GC_CU_BLOCKS_SERVED

GC_CU_BLOCKS_SERVED,

V2.SPACE_USED - V1.SPACE_USED SPACE_USED,

V2.SPACE_ALLOCATED - V1.SPACE_ALLOCATED SPACE_ALLOCATED,

V2.TABLE_SCANS - V1.TABLE_SCANS TABLE_SCANS,

8 TOR

FROM (SELECT SUM (LOGICAL_READS) LOGICAL_READS,

SUM (BUFFER_BUSY_WAITS) BUFFER_BUSY_WAITS,

SUM (DB_BLOCK_CHANGES) DB_BLOCK_CHANGES,

SUM (PHYSICAL_READS) PHYSICAL_READS,

SUM (PHYSICAL_WRITES) PHYSICAL_WRITES,

SUM (PHYSICAL_READS_DIRECT) PHYSICAL_READS_DIRECT,

SUM (PHYSICAL_WRITES_DIRECT) PHYSICAL_WRITES_DIRECT,

SUM (ITL_WAITS) ITL_WAITS,

SUM (ROW_LOCK_WAITS) ROW_LOCK_WAITS,

SUM (GC_CR_BLOCKS_SERVED) GC_CR_BLOCKS_SERVED,

SUM (GC_CU_BLOCKS_SERVED) GC_CU_BLOCKS_SERVED,

SUM (SPACE_USED) SPACE_USED,

SUM (SPACE_ALLOCATED) SPACE_ALLOCATED,

SUM (TABLE_SCANS) TABLE_SCANS

FROM A_RLW) V1,

(SELECT SUM (LOGICAL_READS) LOGICAL_READS,

SUM (BUFFER_BUSY_WAITS) BUFFER_BUSY_WAITS,

SUM (DB_BLOCK_CHANGES) DB_BLOCK_CHANGES,

SUM (PHYSICAL_READS) PHYSICAL_READS,

SUM (PHYSICAL_WRITES) PHYSICAL_WRITES,

SUM (PHYSICAL_READS_DIRECT) PHYSICAL_READS_DIRECT,

SUM (PHYSICAL_WRITES_DIRECT) PHYSICAL_WRITES_DIRECT,

SUM (ITL_WAITS) ITL_WAITS,

SUM (ROW_LOCK_WAITS) ROW_LOCK_WAITS,

SUM (GC_CR_BLOCKS_SERVED) GC_CR_BLOCKS_SERVED,

SUM (GC_CU_BLOCKS_SERVED) GC_CU_BLOCKS_SERVED,

SUM (SPACE_USED) SPACE_USED,

SUM (SPACE_ALLOCATED) SPACE_ALLOCATED,

SUM (TABLE_SCANS) TABLE_SCANS

FROM A_TOT) V2

UNION ALL

SELECT VIEWTYPE,

RNUM,

DBID,

OWNER,

OBJECT_NAME,

SUBOBJECT_NAME,

OBJECT_TYPE,

LOGICAL_READS,

BUFFER_BUSY_WAITS,

DB_BLOCK_CHANGES,

PHYSICAL_READS,

PHYSICAL_WRITES,

PHYSICAL_READS_DIRECT,

PHYSICAL_WRITES_DIRECT,

ITL_WAITS,

ROW_LOCK_WAITS,

GC_CR_BLOCKS_SERVED,

GC_CU_BLOCKS_SERVED,

SPACE_USED,

SPACE_ALLOCATED,

TABLE_SCANS,

9 TOR

FROM A_GCRBS

UNION ALL

SELECT 'GC CR BLOCKS SERVED' VIEWTYPE,

NULL RNUM,

0 DBID,

NULL OWNER,

'ETC' OBJECT_NAME,

NULL SUBOBJECT_NAME,

NULL OBJECT_TYPE,

V2.LOGICAL_READS - V1.LOGICAL_READS LOGICAL_READS,

V2.BUFFER_BUSY_WAITS - V1.BUFFER_BUSY_WAITS BUFFER_BUSY_WAITS,

V2.DB_BLOCK_CHANGES - V1.DB_BLOCK_CHANGES DB_BLOCK_CHANGES,

V2.PHYSICAL_READS - V1.PHYSICAL_READS PHYSICAL_READS,

V2.PHYSICAL_WRITES - V1.PHYSICAL_WRITES PHYSICAL_WRITES,

V2.PHYSICAL_READS_DIRECT - V1.PHYSICAL_READS_DIRECT

PHYSICAL_READS_DIRECT,

V2.PHYSICAL_WRITES_DIRECT - V1.PHYSICAL_WRITES_DIRECT

PHYSICAL_WRITES_DIRECT,

V2.ITL_WAITS - V1.ITL_WAITS ITL_WAITS,

V2.ROW_LOCK_WAITS - V1.ROW_LOCK_WAITS ROW_LOCK_WAITS,

V2.GC_CR_BLOCKS_SERVED - V1.GC_CR_BLOCKS_SERVED

GC_CR_BLOCKS_SERVED,

V2.GC_CU_BLOCKS_SERVED - V1.GC_CU_BLOCKS_SERVED

GC_CU_BLOCKS_SERVED,

V2.SPACE_USED - V1.SPACE_USED SPACE_USED,

V2.SPACE_ALLOCATED - V1.SPACE_ALLOCATED SPACE_ALLOCATED,

V2.TABLE_SCANS - V1.TABLE_SCANS TABLE_SCANS,

9 TOR

FROM (SELECT SUM (LOGICAL_READS) LOGICAL_READS,

SUM (BUFFER_BUSY_WAITS) BUFFER_BUSY_WAITS,

SUM (DB_BLOCK_CHANGES) DB_BLOCK_CHANGES,

SUM (PHYSICAL_READS) PHYSICAL_READS,

SUM (PHYSICAL_WRITES) PHYSICAL_WRITES,

SUM (PHYSICAL_READS_DIRECT) PHYSICAL_READS_DIRECT,

SUM (PHYSICAL_WRITES_DIRECT) PHYSICAL_WRITES_DIRECT,

SUM (ITL_WAITS) ITL_WAITS,

SUM (ROW_LOCK_WAITS) ROW_LOCK_WAITS,

SUM (GC_CR_BLOCKS_SERVED) GC_CR_BLOCKS_SERVED,

SUM (GC_CU_BLOCKS_SERVED) GC_CU_BLOCKS_SERVED,

SUM (SPACE_USED) SPACE_USED,

SUM (SPACE_ALLOCATED) SPACE_ALLOCATED,

SUM (TABLE_SCANS) TABLE_SCANS

FROM A_GCRBS) V1,

(SELECT SUM (LOGICAL_READS) LOGICAL_READS,

SUM (BUFFER_BUSY_WAITS) BUFFER_BUSY_WAITS,

SUM (DB_BLOCK_CHANGES) DB_BLOCK_CHANGES,

SUM (PHYSICAL_READS) PHYSICAL_READS,

SUM (PHYSICAL_WRITES) PHYSICAL_WRITES,

SUM (PHYSICAL_READS_DIRECT) PHYSICAL_READS_DIRECT,

SUM (PHYSICAL_WRITES_DIRECT) PHYSICAL_WRITES_DIRECT,

SUM (ITL_WAITS) ITL_WAITS,

SUM (ROW_LOCK_WAITS) ROW_LOCK_WAITS,

SUM (GC_CR_BLOCKS_SERVED) GC_CR_BLOCKS_SERVED,

SUM (GC_CU_BLOCKS_SERVED) GC_CU_BLOCKS_SERVED,

SUM (SPACE_USED) SPACE_USED,

SUM (SPACE_ALLOCATED) SPACE_ALLOCATED,

SUM (TABLE_SCANS) TABLE_SCANS

FROM A_TOT) V2

UNION ALL

SELECT VIEWTYPE,

RNUM,

DBID,

OWNER,

OBJECT_NAME,

SUBOBJECT_NAME,

OBJECT_TYPE,

LOGICAL_READS,

BUFFER_BUSY_WAITS,

DB_BLOCK_CHANGES,

PHYSICAL_READS,

PHYSICAL_WRITES,

PHYSICAL_READS_DIRECT,

PHYSICAL_WRITES_DIRECT,

ITL_WAITS,

ROW_LOCK_WAITS,

GC_CR_BLOCKS_SERVED,

GC_CU_BLOCKS_SERVED,

SPACE_USED,

SPACE_ALLOCATED,

TABLE_SCANS,

10 TOR

FROM A_GCUBS

UNION ALL

SELECT 'GC CU BLOCKS SERVED' VIEWTYPE,

NULL RNUM,

0 DBID,

NULL OWNER,

'ETC' OBJECT_NAME,

NULL SUBOBJECT_NAME,

NULL OBJECT_TYPE,

V2.LOGICAL_READS - V1.LOGICAL_READS LOGICAL_READS,

V2.BUFFER_BUSY_WAITS - V1.BUFFER_BUSY_WAITS BUFFER_BUSY_WAITS,

V2.DB_BLOCK_CHANGES - V1.DB_BLOCK_CHANGES DB_BLOCK_CHANGES,

V2.PHYSICAL_READS - V1.PHYSICAL_READS PHYSICAL_READS,

V2.PHYSICAL_WRITES - V1.PHYSICAL_WRITES PHYSICAL_WRITES,

V2.PHYSICAL_READS_DIRECT - V1.PHYSICAL_READS_DIRECT

PHYSICAL_READS_DIRECT,

V2.PHYSICAL_WRITES_DIRECT - V1.PHYSICAL_WRITES_DIRECT

PHYSICAL_WRITES_DIRECT,

V2.ITL_WAITS - V1.ITL_WAITS ITL_WAITS,

V2.ROW_LOCK_WAITS - V1.ROW_LOCK_WAITS ROW_LOCK_WAITS,

V2.GC_CR_BLOCKS_SERVED - V1.GC_CR_BLOCKS_SERVED

GC_CR_BLOCKS_SERVED,

V2.GC_CU_BLOCKS_SERVED - V1.GC_CU_BLOCKS_SERVED

GC_CU_BLOCKS_SERVED,

V2.SPACE_USED - V1.SPACE_USED SPACE_USED,

V2.SPACE_ALLOCATED - V1.SPACE_ALLOCATED SPACE_ALLOCATED,

V2.TABLE_SCANS - V1.TABLE_SCANS TABLE_SCANS,

10 TOR

FROM (SELECT SUM (LOGICAL_READS) LOGICAL_READS,

SUM (BUFFER_BUSY_WAITS) BUFFER_BUSY_WAITS,

SUM (DB_BLOCK_CHANGES) DB_BLOCK_CHANGES,

SUM (PHYSICAL_READS) PHYSICAL_READS,

SUM (PHYSICAL_WRITES) PHYSICAL_WRITES,

SUM (PHYSICAL_READS_DIRECT) PHYSICAL_READS_DIRECT,

SUM (PHYSICAL_WRITES_DIRECT) PHYSICAL_WRITES_DIRECT,

SUM (ITL_WAITS) ITL_WAITS,

SUM (ROW_LOCK_WAITS) ROW_LOCK_WAITS,

SUM (GC_CR_BLOCKS_SERVED) GC_CR_BLOCKS_SERVED,

SUM (GC_CU_BLOCKS_SERVED) GC_CU_BLOCKS_SERVED,

SUM (SPACE_USED) SPACE_USED,

SUM (SPACE_ALLOCATED) SPACE_ALLOCATED,

SUM (TABLE_SCANS) TABLE_SCANS

FROM A_GCUBS) V1,

(SELECT SUM (LOGICAL_READS) LOGICAL_READS,

SUM (BUFFER_BUSY_WAITS) BUFFER_BUSY_WAITS,

SUM (DB_BLOCK_CHANGES) DB_BLOCK_CHANGES,

SUM (PHYSICAL_READS) PHYSICAL_READS,

SUM (PHYSICAL_WRITES) PHYSICAL_WRITES,

SUM (PHYSICAL_READS_DIRECT) PHYSICAL_READS_DIRECT,

SUM (PHYSICAL_WRITES_DIRECT) PHYSICAL_WRITES_DIRECT,

SUM (ITL_WAITS) ITL_WAITS,

SUM (ROW_LOCK_WAITS) ROW_LOCK_WAITS,

SUM (GC_CR_BLOCKS_SERVED) GC_CR_BLOCKS_SERVED,

SUM (GC_CU_BLOCKS_SERVED) GC_CU_BLOCKS_SERVED,

SUM (SPACE_USED) SPACE_USED,

SUM (SPACE_ALLOCATED) SPACE_ALLOCATED,

SUM (TABLE_SCANS) TABLE_SCANS

FROM A_TOT) V2

UNION ALL

SELECT VIEWTYPE,

RNUM,

DBID,

OWNER,

OBJECT_NAME,

SUBOBJECT_NAME,

OBJECT_TYPE,

LOGICAL_READS,

BUFFER_BUSY_WAITS,

DB_BLOCK_CHANGES,

PHYSICAL_READS,

PHYSICAL_WRITES,

PHYSICAL_READS_DIRECT,

PHYSICAL_WRITES_DIRECT,

ITL_WAITS,

ROW_LOCK_WAITS,

GC_CR_BLOCKS_SERVED,

GC_CU_BLOCKS_SERVED,

SPACE_USED,

SPACE_ALLOCATED,

TABLE_SCANS,

11 TOR

FROM A_SU

UNION ALL

SELECT 'SPACE USED' VIEWTYPE,

NULL RNUM,

0 DBID,

NULL OWNER,

'ETC' OBJECT_NAME,

NULL SUBOBJECT_NAME,

NULL OBJECT_TYPE,

V2.LOGICAL_READS - V1.LOGICAL_READS LOGICAL_READS,

V2.BUFFER_BUSY_WAITS - V1.BUFFER_BUSY_WAITS BUFFER_BUSY_WAITS,

V2.DB_BLOCK_CHANGES - V1.DB_BLOCK_CHANGES DB_BLOCK_CHANGES,

V2.PHYSICAL_READS - V1.PHYSICAL_READS PHYSICAL_READS,

V2.PHYSICAL_WRITES - V1.PHYSICAL_WRITES PHYSICAL_WRITES,

V2.PHYSICAL_READS_DIRECT - V1.PHYSICAL_READS_DIRECT

PHYSICAL_READS_DIRECT,

V2.PHYSICAL_WRITES_DIRECT - V1.PHYSICAL_WRITES_DIRECT

PHYSICAL_WRITES_DIRECT,

V2.ITL_WAITS - V1.ITL_WAITS ITL_WAITS,

V2.ROW_LOCK_WAITS - V1.ROW_LOCK_WAITS ROW_LOCK_WAITS,

V2.GC_CR_BLOCKS_SERVED - V1.GC_CR_BLOCKS_SERVED

GC_CR_BLOCKS_SERVED,

V2.GC_CU_BLOCKS_SERVED - V1.GC_CU_BLOCKS_SERVED

GC_CU_BLOCKS_SERVED,

V2.SPACE_USED - V1.SPACE_USED SPACE_USED,

V2.SPACE_ALLOCATED - V1.SPACE_ALLOCATED SPACE_ALLOCATED,

V2.TABLE_SCANS - V1.TABLE_SCANS TABLE_SCANS,

11 TOR

FROM (SELECT SUM (LOGICAL_READS) LOGICAL_READS,

SUM (BUFFER_BUSY_WAITS) BUFFER_BUSY_WAITS,

SUM (DB_BLOCK_CHANGES) DB_BLOCK_CHANGES,

SUM (PHYSICAL_READS) PHYSICAL_READS,

SUM (PHYSICAL_WRITES) PHYSICAL_WRITES,

SUM (PHYSICAL_READS_DIRECT) PHYSICAL_READS_DIRECT,

SUM (PHYSICAL_WRITES_DIRECT) PHYSICAL_WRITES_DIRECT,

SUM (ITL_WAITS) ITL_WAITS,

SUM (ROW_LOCK_WAITS) ROW_LOCK_WAITS,

SUM (GC_CR_BLOCKS_SERVED) GC_CR_BLOCKS_SERVED,

SUM (GC_CU_BLOCKS_SERVED) GC_CU_BLOCKS_SERVED,

SUM (SPACE_USED) SPACE_USED,

SUM (SPACE_ALLOCATED) SPACE_ALLOCATED,

SUM (TABLE_SCANS) TABLE_SCANS

FROM A_SU) V1,

(SELECT SUM (LOGICAL_READS) LOGICAL_READS,

SUM (BUFFER_BUSY_WAITS) BUFFER_BUSY_WAITS,

SUM (DB_BLOCK_CHANGES) DB_BLOCK_CHANGES,

SUM (PHYSICAL_READS) PHYSICAL_READS,

SUM (PHYSICAL_WRITES) PHYSICAL_WRITES,

SUM (PHYSICAL_READS_DIRECT) PHYSICAL_READS_DIRECT,

SUM (PHYSICAL_WRITES_DIRECT) PHYSICAL_WRITES_DIRECT,

SUM (ITL_WAITS) ITL_WAITS,

SUM (ROW_LOCK_WAITS) ROW_LOCK_WAITS,

SUM (GC_CR_BLOCKS_SERVED) GC_CR_BLOCKS_SERVED,

SUM (GC_CU_BLOCKS_SERVED) GC_CU_BLOCKS_SERVED,

SUM (SPACE_USED) SPACE_USED,

SUM (SPACE_ALLOCATED) SPACE_ALLOCATED,

SUM (TABLE_SCANS) TABLE_SCANS

FROM A_TOT) V2

UNION ALL

SELECT VIEWTYPE,

RNUM,

DBID,

OWNER,

OBJECT_NAME,

SUBOBJECT_NAME,

OBJECT_TYPE,

LOGICAL_READS,

BUFFER_BUSY_WAITS,

DB_BLOCK_CHANGES,

PHYSICAL_READS,

PHYSICAL_WRITES,

PHYSICAL_READS_DIRECT,

PHYSICAL_WRITES_DIRECT,

ITL_WAITS,

ROW_LOCK_WAITS,

GC_CR_BLOCKS_SERVED,

GC_CU_BLOCKS_SERVED,

SPACE_USED,

SPACE_ALLOCATED,

TABLE_SCANS,

12 TOR

FROM A_SA

UNION ALL

SELECT 'SPACE ALLOCATED' VIEWTYPE,

NULL RNUM,

0 DBID,

NULL OWNER,

'ETC' OBJECT_NAME,

NULL SUBOBJECT_NAME,

NULL OBJECT_TYPE,

V2.LOGICAL_READS - V1.LOGICAL_READS LOGICAL_READS,

V2.BUFFER_BUSY_WAITS - V1.BUFFER_BUSY_WAITS BUFFER_BUSY_WAITS,

V2.DB_BLOCK_CHANGES - V1.DB_BLOCK_CHANGES DB_BLOCK_CHANGES,

V2.PHYSICAL_READS - V1.PHYSICAL_READS PHYSICAL_READS,

V2.PHYSICAL_WRITES - V1.PHYSICAL_WRITES PHYSICAL_WRITES,

V2.PHYSICAL_READS_DIRECT - V1.PHYSICAL_READS_DIRECT

PHYSICAL_READS_DIRECT,

V2.PHYSICAL_WRITES_DIRECT - V1.PHYSICAL_WRITES_DIRECT

PHYSICAL_WRITES_DIRECT,

V2.ITL_WAITS - V1.ITL_WAITS ITL_WAITS,

V2.ROW_LOCK_WAITS - V1.ROW_LOCK_WAITS ROW_LOCK_WAITS,

V2.GC_CR_BLOCKS_SERVED - V1.GC_CR_BLOCKS_SERVED

GC_CR_BLOCKS_SERVED,

V2.GC_CU_BLOCKS_SERVED - V1.GC_CU_BLOCKS_SERVED

GC_CU_BLOCKS_SERVED,

V2.SPACE_USED - V1.SPACE_USED SPACE_USED,

V2.SPACE_ALLOCATED - V1.SPACE_ALLOCATED SPACE_ALLOCATED,

V2.TABLE_SCANS - V1.TABLE_SCANS TABLE_SCANS,

12 TOR

FROM (SELECT SUM (LOGICAL_READS) LOGICAL_READS,

SUM (BUFFER_BUSY_WAITS) BUFFER_BUSY_WAITS,

SUM (DB_BLOCK_CHANGES) DB_BLOCK_CHANGES,

SUM (PHYSICAL_READS) PHYSICAL_READS,

SUM (PHYSICAL_WRITES) PHYSICAL_WRITES,

SUM (PHYSICAL_READS_DIRECT) PHYSICAL_READS_DIRECT,

SUM (PHYSICAL_WRITES_DIRECT) PHYSICAL_WRITES_DIRECT,

SUM (ITL_WAITS) ITL_WAITS,

SUM (ROW_LOCK_WAITS) ROW_LOCK_WAITS,

SUM (GC_CR_BLOCKS_SERVED) GC_CR_BLOCKS_SERVED,

SUM (GC_CU_BLOCKS_SERVED) GC_CU_BLOCKS_SERVED,

SUM (SPACE_USED) SPACE_USED,

SUM (SPACE_ALLOCATED) SPACE_ALLOCATED,

SUM (TABLE_SCANS) TABLE_SCANS

FROM A_SA) V1,

(SELECT SUM (LOGICAL_READS) LOGICAL_READS,

SUM (BUFFER_BUSY_WAITS) BUFFER_BUSY_WAITS,

SUM (DB_BLOCK_CHANGES) DB_BLOCK_CHANGES,

SUM (PHYSICAL_READS) PHYSICAL_READS,

SUM (PHYSICAL_WRITES) PHYSICAL_WRITES,

SUM (PHYSICAL_READS_DIRECT) PHYSICAL_READS_DIRECT,

SUM (PHYSICAL_WRITES_DIRECT) PHYSICAL_WRITES_DIRECT,

SUM (ITL_WAITS) ITL_WAITS,

SUM (ROW_LOCK_WAITS) ROW_LOCK_WAITS,

SUM (GC_CR_BLOCKS_SERVED) GC_CR_BLOCKS_SERVED,

SUM (GC_CU_BLOCKS_SERVED) GC_CU_BLOCKS_SERVED,

SUM (SPACE_USED) SPACE_USED,

SUM (SPACE_ALLOCATED) SPACE_ALLOCATED,

SUM (TABLE_SCANS) TABLE_SCANS

FROM A_TOT) V2

UNION ALL

SELECT VIEWTYPE,

RNUM,

DBID,

OWNER,

OBJECT_NAME,

SUBOBJECT_NAME,

OBJECT_TYPE,

LOGICAL_READS,

BUFFER_BUSY_WAITS,

DB_BLOCK_CHANGES,

PHYSICAL_READS,

PHYSICAL_WRITES,

PHYSICAL_READS_DIRECT,

PHYSICAL_WRITES_DIRECT,

ITL_WAITS,

ROW_LOCK_WAITS,

GC_CR_BLOCKS_SERVED,

GC_CU_BLOCKS_SERVED,

SPACE_USED,

SPACE_ALLOCATED,

TABLE_SCANS,

13 TOR

FROM A_TS

UNION ALL

SELECT 'TABLE SCANS' VIEWTYPE,

NULL RNUM,

0 DBID,

TO_CHAR (V2.OBJ_CNT) OWNER,

'ETC' OBJECT_NAME,

NULL SUBOBJECT_NAME,

NULL OBJECT_TYPE,

V2.LOGICAL_READS - V1.LOGICAL_READS LOGICAL_READS,

V2.BUFFER_BUSY_WAITS - V1.BUFFER_BUSY_WAITS BUFFER_BUSY_WAITS,

V2.DB_BLOCK_CHANGES - V1.DB_BLOCK_CHANGES DB_BLOCK_CHANGES,

V2.PHYSICAL_READS - V1.PHYSICAL_READS PHYSICAL_READS,

V2.PHYSICAL_WRITES - V1.PHYSICAL_WRITES PHYSICAL_WRITES,

V2.PHYSICAL_READS_DIRECT - V1.PHYSICAL_READS_DIRECT

PHYSICAL_READS_DIRECT,

V2.PHYSICAL_WRITES_DIRECT - V1.PHYSICAL_WRITES_DIRECT

PHYSICAL_WRITES_DIRECT,

V2.ITL_WAITS - V1.ITL_WAITS ITL_WAITS,

V2.ROW_LOCK_WAITS - V1.ROW_LOCK_WAITS ROW_LOCK_WAITS,

V2.GC_CR_BLOCKS_SERVED - V1.GC_CR_BLOCKS_SERVED

GC_CR_BLOCKS_SERVED,

V2.GC_CU_BLOCKS_SERVED - V1.GC_CU_BLOCKS_SERVED

GC_CU_BLOCKS_SERVED,

V2.SPACE_USED - V1.SPACE_USED SPACE_USED,

V2.SPACE_ALLOCATED - V1.SPACE_ALLOCATED SPACE_ALLOCATED,

V2.TABLE_SCANS - V1.TABLE_SCANS TABLE_SCANS,

13 TOR

FROM (SELECT SUM (LOGICAL_READS) LOGICAL_READS,

SUM (BUFFER_BUSY_WAITS) BUFFER_BUSY_WAITS,

SUM (DB_BLOCK_CHANGES) DB_BLOCK_CHANGES,

SUM (PHYSICAL_READS) PHYSICAL_READS,

SUM (PHYSICAL_WRITES) PHYSICAL_WRITES,

SUM (PHYSICAL_READS_DIRECT) PHYSICAL_READS_DIRECT,

SUM (PHYSICAL_WRITES_DIRECT) PHYSICAL_WRITES_DIRECT,

SUM (ITL_WAITS) ITL_WAITS,

SUM (ROW_LOCK_WAITS) ROW_LOCK_WAITS,

SUM (GC_CR_BLOCKS_SERVED) GC_CR_BLOCKS_SERVED,

SUM (GC_CU_BLOCKS_SERVED) GC_CU_BLOCKS_SERVED,

SUM (SPACE_USED) SPACE_USED,

SUM (SPACE_ALLOCATED) SPACE_ALLOCATED,

SUM (TABLE_SCANS) TABLE_SCANS

FROM A_TS) V1,

(SELECT SUM (LOGICAL_READS) LOGICAL_READS,

SUM (BUFFER_BUSY_WAITS) BUFFER_BUSY_WAITS,

SUM (DB_BLOCK_CHANGES) DB_BLOCK_CHANGES,

SUM (PHYSICAL_READS) PHYSICAL_READS,

SUM (PHYSICAL_WRITES) PHYSICAL_WRITES,

SUM (PHYSICAL_READS_DIRECT) PHYSICAL_READS_DIRECT,

SUM (PHYSICAL_WRITES_DIRECT) PHYSICAL_WRITES_DIRECT,

SUM (ITL_WAITS) ITL_WAITS,

SUM (ROW_LOCK_WAITS) ROW_LOCK_WAITS,

SUM (GC_CR_BLOCKS_SERVED) GC_CR_BLOCKS_SERVED,

SUM (GC_CU_BLOCKS_SERVED) GC_CU_BLOCKS_SERVED,

SUM (SPACE_USED) SPACE_USED,

SUM (SPACE_ALLOCATED) SPACE_ALLOCATED,

SUM (TABLE_SCANS) TABLE_SCANS,

SUM (OBJ_CNT) OBJ_CNT

FROM A_TOT) V2) A

ORDER BY TOR, VIEWTYPE, RNUM

저작자표시 동일조건

'1. IT Story > Scripts' 카테고리의 다른 글

Oracle DBMS_SCHEDULER 백업 설정 스크립트 (0)	2019.02.06
Oracle Hot Backup 스크립트 (0)	2019.02.05
Oracle 테이블스페이스 용량확인 스크립트 (0)	2019.02.04
Oracle 권한확인 스크립트 (0)	2019.02.03
Oracle 인덱스 정리 대상 추출 스크립트 (0)	2019.01.28
Oracle DB의 OS 상태 조회 스크립트 (0)	2019.01.27
OS(리눅스) 5초단위로 서버 및 DB 상태 출력 스크립트 (0)	2019.01.24
Oracle Alert Log Viewer (11gR2) (0)	2019.01.19