'SQL 상태 조회'에 해당되는 글 1건

2019.01.08 Oracle SQL Stat 조회 스크립트

Oracle SQL Stat 조회 스크립트

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

Oracle SQL Stat 조회 스크립트

WITH A

AS ( SELECT /*+ MATERIALIZE LEADING(DBI SNAP) USE_HASH(SQLS TXT) NO_MERGE(SQLS) NO_MERGE(TXT) */

SQLS.DBID,

SQLS.SQL_ID,

SQLS.OPTIMIZER_MODE,

SQLS.MODULE,

SUM (SQLS.FETCHES_DELTA) FETCHES,

SUM (SQLS.EXECUTIONS_DELTA) EXECUTIONS,

SUM (SQLS.SORTS_DELTA) SORTS,

SUM (SQLS.DISK_READS_DELTA) DISK_READS,

SUM (SQLS.BUFFER_GETS_DELTA) BUFFER_GETS,

SUM (SQLS.ROWS_PROCESSED_DELTA) ROWS_PROCESSED,

SUM (SQLS.CPU_TIME_DELTA) / 1000000 CPU_TIME,

SUM (SQLS.ELAPSED_TIME_DELTA) / 1000000 ELAPSED_TIME,

SUM (SQLS.IOWAIT_DELTA) / 1000000 IOWAIT,

SUM (SQLS.CLWAIT_DELTA) / 1000000 CLWAIT,

SUM (SQLS.APWAIT_DELTA) / 1000000 APWAIT,

SUM (SQLS.CCWAIT_DELTA) / 1000000 CCWAIT,

SUM (DIRECT_WRITES_DELTA) DIRECT_WRITES,

NULL RNUM

FROM (SELECT DI.DBID, DI.INSTANCE_NUMBER, DI.STARTUP_TIME

FROM DBA_HIST_DATABASE_INSTANCE DI

WHERE DI.DBID = (SELECT dbid FROM v$database) --변경

AND DI.INSTANCE_NUMBER =

(SELECT INSTANCE_NUMBER FROM v$instance) --변경

AND ROWNUM <= 1) DBI,

DBA_HIST_SNAPSHOT SNAP,

DBA_HIST_SQLSTAT SQLS

WHERE DBI.DBID = SNAP.DBID

AND DBI.INSTANCE_NUMBER = SNAP.INSTANCE_NUMBER

AND SNAP.SNAP_ID >= :3 + 1

AND SNAP.SNAP_ID <= :4

AND SNAP.DBID = SQLS.DBID

AND SNAP.INSTANCE_NUMBER = SQLS.INSTANCE_NUMBER

AND SNAP.SNAP_ID = SQLS.SNAP_ID

GROUP BY SQLS.DBID,

SQLS.SQL_ID,

SQLS.OPTIMIZER_MODE,

SQLS.MODULE

HAVING SUM (SQLS.EXECUTIONS_DELTA) > 0),

A_TOT

AS (SELECT 0 DBID,

'TOT' SQL_ID,

NULL OPTIMIZER_MODE,

NULL MODULE,

MAX (STAT.EXECUTIONS) EXECUTIONS,

SUM (A.FETCHES) FETCHES,

(MAX (STAT.SORTS_D) + MAX (STAT.SORTS_M)) SORTS,

MAX (STAT.BUFFER_GETS) BUFFER_GETS,

MAX (STAT.DISK_READS) DISK_READS,

SUM (A.ROWS_PROCESSED) ROWS_PROCESSED,

MAX (TIME.DB_CPU) CPU_TIME,

MAX (TIME.DB_TIME) ELAPSED_TIME,

MAX (STAT.BUFFER_GETS) / MAX (STAT.EXECUTIONS) BUF_EXEC,

MAX (STAT.DISK_READS) / MAX (STAT.EXECUTIONS) DISK_EXEC,

SUM (A.ROWS_PROCESSED) / SUM (A.EXECUTIONS) ROWS_EXEC,

MAX (TIME.DB_CPU) / MAX (STAT.EXECUTIONS) CPU_EXEC,

MAX (TIME.DB_TIME) / MAX (STAT.EXECUTIONS) ELAP_EXEC,

MAX (STAT.IOWAIT) IOWAIT,

MAX (STAT.CLWAIT) CLWAIT,

MAX (STAT.APWAIT) APWAIT,

MAX (STAT.CCWAIT) CCWAIT,

COUNT (DISTINCT SQL_ID) RNUM

FROM A,

(SELECT MAX (DECODE (STAT_NAME, 'execute count', TOT_VALUE))

EXECUTIONS,

MAX (

DECODE (STAT_NAME,

'session logical reads', TOT_VALUE))

BUFFER_GETS,

MAX (DECODE (STAT_NAME, 'physical reads', TOT_VALUE))

DISK_READS,

MAX (DECODE (STAT_NAME, 'sorts (disk)', TOT_VALUE))

SORTS_D,

MAX (DECODE (STAT_NAME, 'sorts (memory)', TOT_VALUE))

SORTS_M,

MAX (

DECODE (STAT_NAME,

'cluster wait time', TOT_VALUE / 100))

CLWAIT,

MAX (

DECODE (STAT_NAME,

'application wait time', TOT_VALUE / 100))

APWAIT,

MAX (

DECODE (STAT_NAME,

'concurrency wait time', TOT_VALUE / 100))

CCWAIT,

MAX (

DECODE (STAT_NAME,

'user I/O wait time', TOT_VALUE / 100))

IOWAIT

FROM ( SELECT STAT_NAME, SUM (TOT_VALUE) TOT_VALUE

FROM (SELECT STAT_NAME,

TO_CHAR (SNAP_TIME_1, 'HH24:MI:SS')

SNAP_TIME,

DECODE (SNAP_TIME_2,

NULL, 0,

ROUND ( (VALUE_1 - VALUE_2)))

TOT_VALUE,

(EXTRACT (

DAY FROM SNAP_TIME_1

- SNAP_TIME_2)

* 86400

+ EXTRACT (

HOUR FROM SNAP_TIME_1

- SNAP_TIME_2)

* 3600

+ EXTRACT (

MINUTE FROM SNAP_TIME_1

- SNAP_TIME_2)

* 60

+ EXTRACT (

SECOND FROM SNAP_TIME_1

- SNAP_TIME_2))

TOT_TIME_SEC

FROM ( SELECT /*+ LEADING(DBI) USE_HASH(SNAP STAT) NO_MERGE(SNAP) NO_MERGE(STAT) */

SNAP.END_INTERVAL_TIME

SNAP_TIME_1,

STAT.STAT_NAME,

STAT.VALUE VALUE_1,

LAG (

STAT.VALUE)

OVER (PARTITION BY STAT_NAME

ORDER BY SNAP.SNAP_ID)

VALUE_2,

LAG (

SNAP.END_INTERVAL_TIME)

OVER (PARTITION BY STAT_NAME

ORDER BY SNAP.SNAP_ID)

SNAP_TIME_2,

SNAP.SNAP_ID

FROM (SELECT /*+ NO_MERGE */

DI.DBID,

DI.INSTANCE_NUMBER,

DI.STARTUP_TIME

FROM DBA_HIST_DATABASE_INSTANCE DI

WHERE DI.DBID =

(SELECT dbid

FROM v$database) --변경

AND DI.INSTANCE_NUMBER =

(SELECT INSTANCE_NUMBER

FROM v$instance) --변경

AND ROWNUM <= 1) DBI,

DBA_HIST_SNAPSHOT SNAP,

DBA_HIST_SYSSTAT STAT

WHERE DBI.DBID = SNAP.DBID

AND DBI.INSTANCE_NUMBER =

SNAP.INSTANCE_NUMBER

AND SNAP.SNAP_ID >= :7

AND SNAP.SNAP_ID <= :8

AND SNAP.DBID = STAT.DBID

AND SNAP.INSTANCE_NUMBER =

STAT.INSTANCE_NUMBER

AND SNAP.SNAP_ID =

STAT.SNAP_ID

AND STAT.STAT_NAME IN

('execute count',

'session logical reads',

'sorts (disk)',

'sorts (memory)',

'physical reads',

'cluster wait time',

'application wait time',

'user I/O wait time',

'concurrency wait time')

ORDER BY SNAP.SNAP_ID))

GROUP BY STAT_NAME)) STAT,

(SELECT MAX (

DECODE (STAT_NAME, 'DB CPU', TOT_VALUE / 1000000))

DB_CPU,

MAX (

DECODE (STAT_NAME, 'DB time', TOT_VALUE / 1000000))

DB_TIME

FROM ( SELECT STAT_NAME, SUM (TOT_VALUE) TOT_VALUE

FROM (SELECT STAT_NAME,

TO_CHAR (SNAP_TIME_1, 'HH24:MI:SS')

SNAP_TIME,

DECODE (SNAP_TIME_2,

NULL, 0,

ROUND ( (VALUE_1 - VALUE_2)))

TOT_VALUE,

(EXTRACT (

DAY FROM SNAP_TIME_1

- SNAP_TIME_2)

* 86400

+ EXTRACT (

HOUR FROM SNAP_TIME_1

- SNAP_TIME_2)

* 3600

+ EXTRACT (

MINUTE FROM SNAP_TIME_1

- SNAP_TIME_2)

* 60

+ EXTRACT (

SECOND FROM SNAP_TIME_1

- SNAP_TIME_2))

TOT_TIME_SEC

FROM ( SELECT /*+ LEADING(DBI) USE_HASH(SNAP STAT) NO_MERGE(SNAP) NO_MERGE(STAT) */

SNAP.END_INTERVAL_TIME

SNAP_TIME_1,

STAT.STAT_NAME,

STAT.VALUE VALUE_1,

LAG (

STAT.VALUE)

OVER (PARTITION BY STAT_NAME

ORDER BY SNAP.SNAP_ID)

VALUE_2,

LAG (

SNAP.END_INTERVAL_TIME)

OVER (PARTITION BY STAT_NAME

ORDER BY SNAP.SNAP_ID)

SNAP_TIME_2,

SNAP.SNAP_ID

FROM (SELECT /*+ NO_MERGE */

DI.DBID,

DI.INSTANCE_NUMBER,

DI.STARTUP_TIME

FROM DBA_HIST_DATABASE_INSTANCE DI

WHERE DI.DBID =

(SELECT dbid

FROM v$database) --변경

AND DI.INSTANCE_NUMBER =

(SELECT INSTANCE_NUMBER

FROM v$instance) --변경

AND ROWNUM <= 1) DBI,

DBA_HIST_SNAPSHOT SNAP,

DBA_HIST_SYS_TIME_MODEL STAT

WHERE DBI.DBID = SNAP.DBID

AND DBI.INSTANCE_NUMBER =

SNAP.INSTANCE_NUMBER

AND SNAP.SNAP_ID >= :11

AND SNAP.SNAP_ID <= :12

AND SNAP.DBID = STAT.DBID

AND SNAP.INSTANCE_NUMBER =

STAT.INSTANCE_NUMBER

AND SNAP.SNAP_ID =

STAT.SNAP_ID

AND STAT.STAT_NAME IN

('DB CPU', 'DB time')

ORDER BY SNAP.SNAP_ID))

GROUP BY STAT_NAME)) TIME),

A_ET

AS (SELECT *

FROM (SELECT 'ELAPSED TIME ALL' VIEWTYPE,

DBID,

SQL_ID,

OPTIMIZER_MODE,

MODULE,

EXECUTIONS,

FETCHES,

SORTS,

BUFFER_GETS,

DISK_READS,

ROWS_PROCESSED,

CPU_TIME,

ELAPSED_TIME,

BUFFER_GETS / EXECUTIONS BUF_EXEC,

DISK_READS / EXECUTIONS DISK_EXEC,

ROWS_PROCESSED / EXECUTIONS ROWS_EXEC,

CPU_TIME / EXECUTIONS CPU_EXEC,

ELAPSED_TIME / EXECUTIONS ELAP_EXEC,

IOWAIT,

CLWAIT,

APWAIT,

CCWAIT,

ROW_NUMBER ()

OVER (

ORDER BY ELAPSED_TIME DESC, CPU_TIME DESC, ROWNUM)

RNUM

FROM A

UNION ALL

SELECT 'ELAPSED TIME ALL' VIEWTYPE,

NULL DBID,

NULL SQL_ID,

NULL OPTIMIZER_MODE,

NULL MODULE,

NULL EXECUTIONS,

NULL FETCHES,

NULL SORTS,

NULL BUFFER_GETS,

NULL DISK_READS,

NULL ROWS_PROCESSED,

NULL CPU_TIME,

NULL ELAPSED_TIME,

NULL BUF_EXEC,

NULL DISK_EXEC,

NULL ROWS_EXEC,

NULL CPU_EXEC,

NULL ELAP_EXEC,

NULL IOWAIT,

NULL CLWAIT,

NULL APWAIT,

NULL CCWAIT,

NULL RNUM

FROM A

WHERE ROWNUM <= :13)

WHERE RNUM <= :14 AND ROWNUM <= :15),

A_CT

AS (SELECT *

FROM (SELECT 'CPU TIME ALL' VIEWTYPE,

DBID,

SQL_ID,

OPTIMIZER_MODE,

MODULE,

EXECUTIONS,

FETCHES,

SORTS,

BUFFER_GETS,

DISK_READS,

ROWS_PROCESSED,

CPU_TIME,

ELAPSED_TIME,

BUFFER_GETS / EXECUTIONS BUF_EXEC,

DISK_READS / EXECUTIONS DISK_EXEC,

ROWS_PROCESSED / EXECUTIONS ROWS_EXEC,

CPU_TIME / EXECUTIONS CPU_EXEC,

ELAPSED_TIME / EXECUTIONS ELAP_EXEC,

IOWAIT,

CLWAIT,

APWAIT,

CCWAIT,

ROW_NUMBER ()

OVER (

ORDER BY CPU_TIME DESC, BUFFER_GETS DESC, ROWNUM)

RNUM

FROM A

UNION ALL

SELECT 'CPU TIME ALL' VIEWTYPE,

NULL DBID,

NULL SQL_ID,

NULL OPTIMIZER_MODE,

NULL MODULE,

NULL EXECUTIONS,

NULL FETCHES,

NULL SORTS,

NULL BUFFER_GETS,

NULL DISK_READS,

NULL ROWS_PROCESSED,

NULL CPU_TIME,

NULL ELAPSED_TIME,

NULL BUF_EXEC,

NULL DISK_EXEC,

NULL ROWS_EXEC,

NULL CPU_EXEC,

NULL ELAP_EXEC,

NULL IOWAIT,

NULL CLWAIT,

NULL APWAIT,

NULL CCWAIT,

NULL RNUM

FROM A

WHERE ROWNUM <= :16)

WHERE RNUM <= :17 AND ROWNUM <= :18),

A_BG

AS (SELECT *

FROM (SELECT 'BUFFER GETS ALL' VIEWTYPE,

DBID,

SQL_ID,

OPTIMIZER_MODE,

MODULE,

EXECUTIONS,

FETCHES,

SORTS,

BUFFER_GETS,

DISK_READS,

ROWS_PROCESSED,

CPU_TIME,

ELAPSED_TIME,

BUFFER_GETS / EXECUTIONS BUF_EXEC,

DISK_READS / EXECUTIONS DISK_EXEC,

ROWS_PROCESSED / EXECUTIONS ROWS_EXEC,

CPU_TIME / EXECUTIONS CPU_EXEC,

ELAPSED_TIME / EXECUTIONS ELAP_EXEC,

IOWAIT,

CLWAIT,

APWAIT,

CCWAIT,

ROW_NUMBER ()

OVER (

ORDER BY BUFFER_GETS DESC, CPU_TIME DESC, ROWNUM)

RNUM

FROM A

UNION ALL

SELECT 'BUFFER GETS ALL' VIEWTYPE,

NULL DBID,

NULL SQL_ID,

NULL OPTIMIZER_MODE,

NULL MODULE,

NULL EXECUTIONS,

NULL FETCHES,

NULL SORTS,

NULL BUFFER_GETS,

NULL DISK_READS,

NULL ROWS_PROCESSED,

NULL CPU_TIME,

NULL ELAPSED_TIME,

NULL BUF_EXEC,

NULL DISK_EXEC,

NULL ROWS_EXEC,

NULL CPU_EXEC,

NULL ELAP_EXEC,

NULL IOWAIT,

NULL CLWAIT,

NULL APWAIT,

NULL CCWAIT,

NULL RNUM

FROM A

WHERE ROWNUM <= :19)

WHERE RNUM <= :20 AND ROWNUM <= :21),

A_DR

AS (SELECT *

FROM (SELECT 'DISK READS ALL' VIEWTYPE,

DBID,

SQL_ID,

OPTIMIZER_MODE,

MODULE,

EXECUTIONS,

FETCHES,

SORTS,

BUFFER_GETS,

DISK_READS,

ROWS_PROCESSED,

CPU_TIME,

ELAPSED_TIME,

BUFFER_GETS / EXECUTIONS BUF_EXEC,

DISK_READS / EXECUTIONS DISK_EXEC,

ROWS_PROCESSED / EXECUTIONS ROWS_EXEC,

CPU_TIME / EXECUTIONS CPU_EXEC,

ELAPSED_TIME / EXECUTIONS ELAP_EXEC,

IOWAIT,

CLWAIT,

APWAIT,

CCWAIT,

ROW_NUMBER ()

OVER (ORDER BY DISK_READS DESC, IOWAIT DESC, ROWNUM)

RNUM

FROM A

UNION ALL

SELECT 'DISK READS ALL' VIEWTYPE,

NULL DBID,

NULL SQL_ID,

NULL OPTIMIZER_MODE,

NULL MODULE,

NULL EXECUTIONS,

NULL FETCHES,

NULL SORTS,

NULL BUFFER_GETS,

NULL DISK_READS,

NULL ROWS_PROCESSED,

NULL CPU_TIME,

NULL ELAPSED_TIME,

NULL BUF_EXEC,

NULL DISK_EXEC,

NULL ROWS_EXEC,

NULL CPU_EXEC,

NULL ELAP_EXEC,

NULL IOWAIT,

NULL CLWAIT,

NULL APWAIT,

NULL CCWAIT,

NULL RNUM

FROM A

WHERE ROWNUM <= :22)

WHERE RNUM <= :23 AND ROWNUM <= :24),

A_CL

AS (SELECT *

FROM (SELECT 'CLUSTER WAIT ALL' VIEWTYPE,

DBID,

SQL_ID,

OPTIMIZER_MODE,

MODULE,

EXECUTIONS,

FETCHES,

SORTS,

BUFFER_GETS,

DISK_READS,

ROWS_PROCESSED,

CPU_TIME,

ELAPSED_TIME,

BUFFER_GETS / EXECUTIONS BUF_EXEC,

DISK_READS / EXECUTIONS DISK_EXEC,

ROWS_PROCESSED / EXECUTIONS ROWS_EXEC,

CPU_TIME / EXECUTIONS CPU_EXEC,

ELAPSED_TIME / EXECUTIONS ELAP_EXEC,

IOWAIT,

CLWAIT,

APWAIT,

CCWAIT,

ROW_NUMBER ()

OVER (ORDER BY CLWAIT DESC, DISK_READS DESC, ROWNUM)

RNUM

FROM A

UNION ALL

SELECT 'CLUSTER WAIT ALL' VIEWTYPE,

NULL DBID,

NULL SQL_ID,

NULL OPTIMIZER_MODE,

NULL MODULE,

NULL EXECUTIONS,

NULL FETCHES,

NULL SORTS,

NULL BUFFER_GETS,

NULL DISK_READS,

NULL ROWS_PROCESSED,

NULL CPU_TIME,

NULL ELAPSED_TIME,

NULL BUF_EXEC,

NULL DISK_EXEC,

NULL ROWS_EXEC,

NULL CPU_EXEC,

NULL ELAP_EXEC,

NULL IOWAIT,

NULL CLWAIT,

NULL APWAIT,

NULL CCWAIT,

NULL RNUM

FROM A

WHERE ROWNUM <= :25)

WHERE RNUM <= :26 AND ROWNUM <= :27),

A_CC

AS (SELECT *

FROM (SELECT 'CONSISTENT WAIT ALL' VIEWTYPE,

DBID,

SQL_ID,

OPTIMIZER_MODE,

MODULE,

EXECUTIONS,

FETCHES,

SORTS,

BUFFER_GETS,

DISK_READS,

ROWS_PROCESSED,

CPU_TIME,

ELAPSED_TIME,

BUFFER_GETS / EXECUTIONS BUF_EXEC,

DISK_READS / EXECUTIONS DISK_EXEC,

ROWS_PROCESSED / EXECUTIONS ROWS_EXEC,

CPU_TIME / EXECUTIONS CPU_EXEC,

ELAPSED_TIME / EXECUTIONS ELAP_EXEC,

IOWAIT,

CLWAIT,

APWAIT,

CCWAIT,

ROW_NUMBER ()

OVER (ORDER BY CCWAIT DESC, BUFFER_GETS DESC, ROWNUM)

RNUM

FROM A

UNION ALL

SELECT 'CONSISTENT WAIT ALL' VIEWTYPE,

NULL DBID,

NULL SQL_ID,

NULL OPTIMIZER_MODE,

NULL MODULE,

NULL EXECUTIONS,

NULL FETCHES,

NULL SORTS,

NULL BUFFER_GETS,

NULL DISK_READS,

NULL ROWS_PROCESSED,

NULL CPU_TIME,

NULL ELAPSED_TIME,

NULL BUF_EXEC,

NULL DISK_EXEC,

NULL ROWS_EXEC,

NULL CPU_EXEC,

NULL ELAP_EXEC,

NULL IOWAIT,

NULL CLWAIT,

NULL APWAIT,

NULL CCWAIT,

NULL RNUM

FROM A

WHERE ROWNUM <= :28)

WHERE RNUM <= :29 AND ROWNUM <= :30),

A_IW

AS (SELECT *

FROM (SELECT 'IO WAIT ALL' VIEWTYPE,

DBID,

SQL_ID,

OPTIMIZER_MODE,

MODULE,

EXECUTIONS,

FETCHES,

SORTS,

BUFFER_GETS,

DISK_READS,

ROWS_PROCESSED,

CPU_TIME,

ELAPSED_TIME,

BUFFER_GETS / EXECUTIONS BUF_EXEC,

DISK_READS / EXECUTIONS DISK_EXEC,

ROWS_PROCESSED / EXECUTIONS ROWS_EXEC,

CPU_TIME / EXECUTIONS CPU_EXEC,

ELAPSED_TIME / EXECUTIONS ELAP_EXEC,

IOWAIT,

CLWAIT,

APWAIT,

CCWAIT,

ROW_NUMBER ()

OVER (ORDER BY IOWAIT DESC, DISK_READS DESC, ROWNUM)

RNUM

FROM A

UNION ALL

SELECT 'IO WAIT ALL' VIEWTYPE,

NULL DBID,

NULL SQL_ID,

NULL OPTIMIZER_MODE,

NULL MODULE,

NULL EXECUTIONS,

NULL FETCHES,

NULL SORTS,

NULL BUFFER_GETS,

NULL DISK_READS,

NULL ROWS_PROCESSED,

NULL CPU_TIME,

NULL ELAPSED_TIME,

NULL BUF_EXEC,

NULL DISK_EXEC,

NULL ROWS_EXEC,

NULL CPU_EXEC,

NULL ELAP_EXEC,

NULL IOWAIT,

NULL CLWAIT,

NULL APWAIT,

NULL CCWAIT,

NULL RNUM

FROM A

WHERE ROWNUM <= :31)

WHERE RNUM <= :32 AND ROWNUM <= :33),

A_AW

AS (SELECT *

FROM (SELECT 'LOCK WAIT ALL' VIEWTYPE,

DBID,

SQL_ID,

OPTIMIZER_MODE,

MODULE,

EXECUTIONS,

FETCHES,

SORTS,

BUFFER_GETS,

DISK_READS,

ROWS_PROCESSED,

CPU_TIME,

ELAPSED_TIME,

BUFFER_GETS / EXECUTIONS BUF_EXEC,

DISK_READS / EXECUTIONS DISK_EXEC,

ROWS_PROCESSED / EXECUTIONS ROWS_EXEC,

CPU_TIME / EXECUTIONS CPU_EXEC,

ELAPSED_TIME / EXECUTIONS ELAP_EXEC,

IOWAIT,

CLWAIT,

APWAIT,

CCWAIT,

ROW_NUMBER ()

OVER (ORDER BY APWAIT DESC, CPU_TIME DESC, ROWNUM)

RNUM

FROM A

UNION ALL

SELECT 'LOCK WAIT ALL' VIEWTYPE,

NULL DBID,

NULL SQL_ID,

NULL OPTIMIZER_MODE,

NULL MODULE,

NULL EXECUTIONS,

NULL FETCHES,

NULL SORTS,

NULL BUFFER_GETS,

NULL DISK_READS,

NULL ROWS_PROCESSED,

NULL CPU_TIME,

NULL ELAPSED_TIME,

NULL BUF_EXEC,

NULL DISK_EXEC,

NULL ROWS_EXEC,

NULL CPU_EXEC,

NULL ELAP_EXEC,

NULL IOWAIT,

NULL CLWAIT,

NULL APWAIT,

NULL CCWAIT,

NULL RNUM

FROM A

WHERE ROWNUM <= :34)

WHERE RNUM <= :35 AND ROWNUM <= :36),

A_EC

AS (SELECT *

FROM (SELECT 'EXEC CNT ALL' VIEWTYPE,

DBID,

SQL_ID,

OPTIMIZER_MODE,

MODULE,

EXECUTIONS,

FETCHES,

SORTS,

BUFFER_GETS,

DISK_READS,

ROWS_PROCESSED,

CPU_TIME,

ELAPSED_TIME,

BUFFER_GETS / EXECUTIONS BUF_EXEC,

DISK_READS / EXECUTIONS DISK_EXEC,

ROWS_PROCESSED / EXECUTIONS ROWS_EXEC,

CPU_TIME / EXECUTIONS CPU_EXEC,

ELAPSED_TIME / EXECUTIONS ELAP_EXEC,

IOWAIT,

CLWAIT,

APWAIT,

CCWAIT,

ROW_NUMBER ()

OVER (

ORDER BY EXECUTIONS DESC, CPU_TIME DESC, ROWNUM)

RNUM

FROM A

UNION ALL

SELECT 'EXEC CNT ALL' VIEWTYPE,

NULL DBID,

NULL SQL_ID,

NULL OPTIMIZER_MODE,

NULL MODULE,

NULL EXECUTIONS,

NULL FETCHES,

NULL SORTS,

NULL BUFFER_GETS,

NULL DISK_READS,

NULL ROWS_PROCESSED,

NULL CPU_TIME,

NULL ELAPSED_TIME,

NULL BUF_EXEC,

NULL DISK_EXEC,

NULL ROWS_EXEC,

NULL CPU_EXEC,

NULL ELAP_EXEC,

NULL IOWAIT,

NULL CLWAIT,

NULL APWAIT,

NULL CCWAIT,

NULL RNUM

FROM A

WHERE ROWNUM <= :37)

WHERE RNUM <= :38 AND ROWNUM <= :39)

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

SQLSTAT.VIEWTYPE,

SQLSTAT.SQL_ID,

SQLSTAT.OPTIMIZER_MODE,

SQLSTAT.MODULE,

TO_CHAR (SQLSTAT.EXECUTIONS, 'FM999,999,999,999,999,999') EXECUTIONS,

TO_CHAR (SQLSTAT.FETCHES, 'FM999,999,999,999,999,999') FETCHES,

TO_CHAR (SQLSTAT.SORTS, 'FM999,999,999,999,999,999') SORTS,

TO_CHAR (SQLSTAT.BUFFER_GETS, 'FM999,999,999,999,999,999') BUFFER_GETS,

TO_CHAR (SQLSTAT.DISK_READS, 'FM999,999,999,999,999,999') DISK_READS,

TO_CHAR (SQLSTAT.ROWS_PROCESSED, 'FM999,999,999,999,999,999')

ROWS_PROCESSED,

TO_CHAR (SQLSTAT.CPU_TIME, 'FM999,999,999,999,999,999.9') CPU_TIME,

TO_CHAR (SQLSTAT.ELAPSED_TIME, 'FM999,999,999,999,999,999.9')

ELAPSED_TIME,

TO_CHAR (SQLSTAT.BUF_EXEC, 'FM999,999,999,999,999,999.9') BUF_EXEC,

TO_CHAR (SQLSTAT.DISK_EXEC, 'FM999,999,999,999,999,999.9') DISK_EXEC,

TO_CHAR (SQLSTAT.ROWS_EXEC, 'FM999,999,999,999,999,999.9') ROWS_EXEC,

TO_CHAR (SQLSTAT.CPU_EXEC, 'FM999,999,999,999,999.999') CPU_EXEC,

TO_CHAR (SQLSTAT.ELAP_EXEC, 'FM999,999,999,999,999.999') ELAP_EXEC,

TO_CHAR (SQLSTAT.IOWAIT, 'FM999,999,999,999,999,999.9') IOWAIT,

TO_CHAR (SQLSTAT.CLWAIT, 'FM999,999,999,999,999,999.9') CLWAIT,

TO_CHAR (SQLSTAT.APWAIT, 'FM999,999,999,999,999,999.9') APWAIT,

TO_CHAR (SQLSTAT.CCWAIT, 'FM999,999,999,999,999,999.9') CCWAIT,

SQLSTAT.RNUM,

DBMS_LOB.SUBSTR (SQLT.SQL_TEXT, 3000, 1) SQL_TEXT,

NULL SQL_PLAN,

(SELECT 'Table Count:'

|| COUNT (

DISTINCT CASE

WHEN OBJECT_ALIAS IS NOT NULL

AND OPERATION NOT IN ('VIEW')

THEN

OBJECT_ALIAS

END)

|| CHR (10)

|| 'View Count:'

|| COUNT (

DISTINCT CASE

WHEN OBJECT_ALIAS IS NOT NULL

AND OPERATION IN ('VIEW')

THEN

OBJECT_ALIAS

END)

|| CHR (10)

|| 'Window Func Count:'

|| COUNT (CASE WHEN OPERATION IN ('WINDOW') THEN OPTIONS END)

|| CHR (10)

|| 'Query Block Count:'

|| COUNT (DISTINCT QBLOCK_NAME)

|| CHR (10)

|| 'TIME:'

|| MAX (TIME)

|| CHR (10)

|| 'COST:'

|| TO_CHAR (MAX (COST), 'FM999,999,999')

|| CHR (10)

|| 'CPU COST:'

|| TO_CHAR (MAX (CPU_COST), 'FM999,999,999,999,999')

|| CHR (10)

|| 'IO COST:'

|| TO_CHAR (MAX (IO_COST), 'FM999,999,999,999')

FROM V$SQL_PLAN

WHERE SQL_ID = SQLSTAT.SQL_ID)

PLAN_COST1,

(SELECT 'Table Count:'

|| COUNT (

DISTINCT CASE

WHEN OBJECT_ALIAS IS NOT NULL

AND OPERATION NOT IN ('VIEW')

THEN

OBJECT_ALIAS

END)

|| CHR (10)

|| 'View Count:'

|| COUNT (

DISTINCT CASE

WHEN OBJECT_ALIAS IS NOT NULL

AND OPERATION IN ('VIEW')

THEN

OBJECT_ALIAS

END)

|| CHR (10)

|| 'Window Func Count:'

|| COUNT (CASE WHEN OPERATION IN ('WINDOW') THEN OPTIONS END)

|| CHR (10)

|| 'Query Block Count:'

|| COUNT (DISTINCT QBLOCK_NAME)

|| CHR (10)

|| 'TIME:'

|| MAX (TIME)

|| CHR (10)

|| 'COST:'

|| TO_CHAR (MAX (COST), 'FM999,999,999')

|| CHR (10)

|| 'CPU COST:'

|| TO_CHAR (MAX (CPU_COST), 'FM999,999,999,999,999')

|| CHR (10)

|| 'IO COST:'

|| TO_CHAR (MAX (IO_COST), 'FM999,999,999,999')

FROM DBA_HIST_SQL_PLAN

WHERE SQL_ID = SQLSTAT.SQL_ID AND DBID = SQLSTAT.DBID

AND TIMESTAMP IN

(SELECT MAX (TIMESTAMP)

FROM DBA_HIST_SQL_PLAN

WHERE SQL_ID = SQLSTAT.SQL_ID AND DBID = SQLSTAT.DBID))

PLAN_COST2,

NULL BIND_VALUE,

NULL SQL_PROFILE

FROM (SELECT * FROM A_ET

UNION ALL

SELECT 'ELAPSED TIME ALL' VIEWTYPE,

0 DBID,

'ETC' SQL_ID,

NULL OPTIMIZER_MODE,

'ETC' MODULE,

V2.EXECUTIONS - V1.EXECUTIONS EXECUTIONS,

V2.FETCHES - V1.FETCHES FETCHES,

V2.SORTS - V1.SORTS SORTS,

V2.BUFFER_GETS - V1.BUFFER_GETS BUFFER_GETS,

V2.DISK_READS - V1.DISK_READS DISK_READS,

V2.ROWS_PROCESSED - V1.ROWS_PROCESSED ROWS_PROCESSED,

V2.CPU_TIME - V1.CPU_TIME CPU_TIME,

V2.ELAPSED_TIME - V1.ELAPSED_TIME ELAPSED_TIME,

0 BUF_EXEC,

0 DISK_EXEC,

0 ROWS_EXEC,

0 CPU_EXEC,

0 ELAP_EXEC,

V2.IOWAIT - V1.IOWAIT IOWAIT,

V2.CLWAIT - V1.CLWAIT CLWAIT,

V2.APWAIT - V1.APWAIT APWAIT,

V2.CCWAIT - V1.CCWAIT CCWAIT,

NULL RNUM

FROM (SELECT SUM (EXECUTIONS) EXECUTIONS,

SUM (FETCHES) FETCHES,

SUM (SORTS) SORTS,

SUM (BUFFER_GETS) BUFFER_GETS,

SUM (DISK_READS) DISK_READS,

SUM (ROWS_PROCESSED) ROWS_PROCESSED,

SUM (CPU_TIME) CPU_TIME,

SUM (ELAPSED_TIME) ELAPSED_TIME,

SUM (IOWAIT) IOWAIT,

SUM (CLWAIT) CLWAIT,

SUM (APWAIT) APWAIT,

SUM (CCWAIT) CCWAIT

FROM A_ET) V1,

(SELECT SUM (EXECUTIONS) EXECUTIONS,

SUM (FETCHES) FETCHES,

SUM (SORTS) SORTS,

SUM (BUFFER_GETS) BUFFER_GETS,

SUM (DISK_READS) DISK_READS,

SUM (ROWS_PROCESSED) ROWS_PROCESSED,

SUM (CPU_TIME) CPU_TIME,

SUM (ELAPSED_TIME) ELAPSED_TIME,

SUM (IOWAIT) IOWAIT,

SUM (CLWAIT) CLWAIT,

SUM (APWAIT) APWAIT,

SUM (CCWAIT) CCWAIT

FROM A_TOT) V2

UNION ALL

SELECT *

FROM (SELECT 'ELAPSED TIME ONE' VIEWTYPE,

DBID,

SQL_ID,

OPTIMIZER_MODE,

MODULE,

EXECUTIONS,

FETCHES,

SORTS,

BUFFER_GETS,

DISK_READS,

ROWS_PROCESSED,

CPU_TIME,

ELAPSED_TIME,

BUFFER_GETS / EXECUTIONS BUF_EXEC,

DISK_READS / EXECUTIONS DISK_EXEC,

ROWS_PROCESSED / EXECUTIONS ROWS_EXEC,

CPU_TIME / EXECUTIONS CPU_EXEC,

ELAPSED_TIME / EXECUTIONS ELAP_EXEC,

IOWAIT,

CLWAIT,

APWAIT,

CCWAIT,

ROW_NUMBER ()

OVER (

ORDER BY

ELAPSED_TIME / EXECUTIONS DESC,

CPU_TIME DESC,

ROWNUM)

RNUM

FROM A

UNION ALL

SELECT 'ELAPSED TIME ONE' VIEWTYPE,

NULL DBID,

NULL SQL_ID,

NULL OPTIMIZER_MODE,

NULL MODULE,

NULL EXECUTIONS,

NULL FETCHES,

NULL SORTS,

NULL BUFFER_GETS,

NULL DISK_READS,

NULL ROWS_PROCESSED,

NULL CPU_TIME,

NULL ELAPSED_TIME,

NULL BUF_EXEC,

NULL DISK_EXEC,

NULL ROWS_EXEC,

NULL CPU_EXEC,

NULL ELAP_EXEC,

NULL IOWAIT,

NULL CLWAIT,

NULL APWAIT,

NULL CCWAIT,

NULL RNUM

FROM A

WHERE ROWNUM <= :40 + 1)

WHERE RNUM <= :41 + 1 AND ROWNUM <= :42 + 1

UNION ALL

SELECT * FROM A_CT

UNION ALL

SELECT 'CPU TIME ALL' VIEWTYPE,

0 DBID,

'ETC' SQL_ID,

NULL OPTIMIZER_MODE,

'ETC' MODULE,

V2.EXECUTIONS - V1.EXECUTIONS EXECUTIONS,

V2.FETCHES - V1.FETCHES FETCHES,

V2.SORTS - V1.SORTS SORTS,

V2.BUFFER_GETS - V1.BUFFER_GETS BUFFER_GETS,

V2.DISK_READS - V1.DISK_READS DISK_READS,

V2.ROWS_PROCESSED - V1.ROWS_PROCESSED ROWS_PROCESSED,

V2.CPU_TIME - V1.CPU_TIME CPU_TIME,

V2.ELAPSED_TIME - V1.ELAPSED_TIME ELAPSED_TIME,

0 BUF_EXEC,

0 DISK_EXEC,

0 ROWS_EXEC,

0 CPU_EXEC,

0 ELAP_EXEC,

V2.IOWAIT - V1.IOWAIT IOWAIT,

V2.CLWAIT - V1.CLWAIT CLWAIT,

V2.APWAIT - V1.APWAIT APWAIT,

V2.CCWAIT - V1.CCWAIT CCWAIT,

NULL RNUM

FROM (SELECT SUM (EXECUTIONS) EXECUTIONS,

SUM (FETCHES) FETCHES,

SUM (SORTS) SORTS,

SUM (BUFFER_GETS) BUFFER_GETS,

SUM (DISK_READS) DISK_READS,

SUM (ROWS_PROCESSED) ROWS_PROCESSED,

SUM (CPU_TIME) CPU_TIME,

SUM (ELAPSED_TIME) ELAPSED_TIME,

SUM (IOWAIT) IOWAIT,

SUM (CLWAIT) CLWAIT,

SUM (APWAIT) APWAIT,

SUM (CCWAIT) CCWAIT

FROM A_CT) V1,

(SELECT SUM (EXECUTIONS) EXECUTIONS,

SUM (FETCHES) FETCHES,

SUM (SORTS) SORTS,

SUM (BUFFER_GETS) BUFFER_GETS,

SUM (DISK_READS) DISK_READS,

SUM (ROWS_PROCESSED) ROWS_PROCESSED,

SUM (CPU_TIME) CPU_TIME,

SUM (ELAPSED_TIME) ELAPSED_TIME,

SUM (IOWAIT) IOWAIT,

SUM (CLWAIT) CLWAIT,

SUM (APWAIT) APWAIT,

SUM (CCWAIT) CCWAIT

FROM A_TOT) V2

UNION ALL

SELECT *

FROM (SELECT 'CPU TIME ONE' VIEWTYPE,

DBID,

SQL_ID,

OPTIMIZER_MODE,

MODULE,

EXECUTIONS,

FETCHES,

SORTS,

BUFFER_GETS,

DISK_READS,

ROWS_PROCESSED,

CPU_TIME,

ELAPSED_TIME,

BUFFER_GETS / EXECUTIONS BUF_EXEC,

DISK_READS / EXECUTIONS DISK_EXEC,

ROWS_PROCESSED / EXECUTIONS ROWS_EXEC,

CPU_TIME / EXECUTIONS CPU_EXEC,

ELAPSED_TIME / EXECUTIONS ELAP_EXEC,

IOWAIT,

CLWAIT,

APWAIT,

CCWAIT,

ROW_NUMBER ()

OVER (

ORDER BY

CPU_TIME / EXECUTIONS DESC,

BUFFER_GETS DESC,

ROWNUM)

RNUM

FROM A

UNION ALL

SELECT 'CPU TIME ONE' VIEWTYPE,

NULL DBID,

NULL SQL_ID,

NULL OPTIMIZER_MODE,

NULL MODULE,

NULL EXECUTIONS,

NULL FETCHES,

NULL SORTS,

NULL BUFFER_GETS,

NULL DISK_READS,

NULL ROWS_PROCESSED,

NULL CPU_TIME,

NULL ELAPSED_TIME,

NULL BUF_EXEC,

NULL DISK_EXEC,

NULL ROWS_EXEC,

NULL CPU_EXEC,

NULL ELAP_EXEC,

NULL IOWAIT,

NULL CLWAIT,

NULL APWAIT,

NULL CCWAIT,

NULL RNUM

FROM A

WHERE ROWNUM <= :43 + 1)

WHERE RNUM <= :44 + 1 AND ROWNUM <= :45 + 1

UNION ALL

SELECT * FROM A_BG

UNION ALL

SELECT 'BUFFER GETS ALL' VIEWTYPE,

0 DBID,

'ETC' SQL_ID,

NULL OPTIMIZER_MODE,

'ETC' MODULE,

V2.EXECUTIONS - V1.EXECUTIONS EXECUTIONS,

V2.FETCHES - V1.FETCHES FETCHES,

V2.SORTS - V1.SORTS SORTS,

V2.BUFFER_GETS - V1.BUFFER_GETS BUFFER_GETS,

V2.DISK_READS - V1.DISK_READS DISK_READS,

V2.ROWS_PROCESSED - V1.ROWS_PROCESSED ROWS_PROCESSED,

V2.CPU_TIME - V1.CPU_TIME CPU_TIME,

V2.ELAPSED_TIME - V1.ELAPSED_TIME ELAPSED_TIME,

0 BUF_EXEC,

0 DISK_EXEC,

0 ROWS_EXEC,

0 CPU_EXEC,

0 ELAP_EXEC,

V2.IOWAIT - V1.IOWAIT IOWAIT,

V2.CLWAIT - V1.CLWAIT CLWAIT,

V2.APWAIT - V1.APWAIT APWAIT,

V2.CCWAIT - V1.CCWAIT CCWAIT,

NULL RNUM

FROM (SELECT SUM (EXECUTIONS) EXECUTIONS,

SUM (FETCHES) FETCHES,

SUM (SORTS) SORTS,

SUM (BUFFER_GETS) BUFFER_GETS,

SUM (DISK_READS) DISK_READS,

SUM (ROWS_PROCESSED) ROWS_PROCESSED,

SUM (CPU_TIME) CPU_TIME,

SUM (ELAPSED_TIME) ELAPSED_TIME,

SUM (IOWAIT) IOWAIT,

SUM (CLWAIT) CLWAIT,

SUM (APWAIT) APWAIT,

SUM (CCWAIT) CCWAIT

FROM A_BG) V1,

(SELECT SUM (EXECUTIONS) EXECUTIONS,

SUM (FETCHES) FETCHES,

SUM (SORTS) SORTS,

SUM (BUFFER_GETS) BUFFER_GETS,

SUM (DISK_READS) DISK_READS,

SUM (ROWS_PROCESSED) ROWS_PROCESSED,

SUM (CPU_TIME) CPU_TIME,

SUM (ELAPSED_TIME) ELAPSED_TIME,

SUM (IOWAIT) IOWAIT,

SUM (CLWAIT) CLWAIT,

SUM (APWAIT) APWAIT,

SUM (CCWAIT) CCWAIT

FROM A_TOT) V2

UNION ALL

SELECT *

FROM (SELECT 'BUFFER GETS ONE' VIEWTYPE,

DBID,

SQL_ID,

OPTIMIZER_MODE,

MODULE,

EXECUTIONS,

FETCHES,

SORTS,

BUFFER_GETS,

DISK_READS,

ROWS_PROCESSED,

CPU_TIME,

ELAPSED_TIME,

BUFFER_GETS / EXECUTIONS BUF_EXEC,

DISK_READS / EXECUTIONS DISK_EXEC,

ROWS_PROCESSED / EXECUTIONS ROWS_EXEC,

CPU_TIME / EXECUTIONS CPU_EXEC,

ELAPSED_TIME / EXECUTIONS ELAP_EXEC,

IOWAIT,

CLWAIT,

APWAIT,

CCWAIT,

ROW_NUMBER ()

OVER (

ORDER BY

BUFFER_GETS / EXECUTIONS DESC,

CPU_TIME DESC,

ROWNUM)

RNUM

FROM A

UNION ALL

SELECT 'BUFFER GETS ONE' VIEWTYPE,

NULL DBID,

NULL SQL_ID,

NULL OPTIMIZER_MODE,

NULL MODULE,

NULL EXECUTIONS,

NULL FETCHES,

NULL SORTS,

NULL BUFFER_GETS,

NULL DISK_READS,

NULL ROWS_PROCESSED,

NULL CPU_TIME,

NULL ELAPSED_TIME,

NULL BUF_EXEC,

NULL DISK_EXEC,

NULL ROWS_EXEC,

NULL CPU_EXEC,

NULL ELAP_EXEC,

NULL IOWAIT,

NULL CLWAIT,

NULL APWAIT,

NULL CCWAIT,

NULL RNUM

FROM A

WHERE ROWNUM <= :46 + 1)

WHERE RNUM <= :47 + 1 AND ROWNUM <= :48 + 1

UNION ALL

SELECT * FROM A_DR

UNION ALL

SELECT 'DISK READS ALL' VIEWTYPE,

0 DBID,

'ETC' SQL_ID,

NULL OPTIMIZER_MODE,

'ETC' MODULE,

V2.EXECUTIONS - V1.EXECUTIONS EXECUTIONS,

V2.FETCHES - V1.FETCHES FETCHES,

V2.SORTS - V1.SORTS SORTS,

V2.BUFFER_GETS - V1.BUFFER_GETS BUFFER_GETS,

V2.DISK_READS - V1.DISK_READS DISK_READS,

V2.ROWS_PROCESSED - V1.ROWS_PROCESSED ROWS_PROCESSED,

V2.CPU_TIME - V1.CPU_TIME CPU_TIME,

V2.ELAPSED_TIME - V1.ELAPSED_TIME ELAPSED_TIME,

0 BUF_EXEC,

0 DISK_EXEC,

0 ROWS_EXEC,

0 CPU_EXEC,

0 ELAP_EXEC,

V2.IOWAIT - V1.IOWAIT IOWAIT,

V2.CLWAIT - V1.CLWAIT CLWAIT,

V2.APWAIT - V1.APWAIT APWAIT,

V2.CCWAIT - V1.CCWAIT CCWAIT,

NULL RNUM

FROM (SELECT SUM (EXECUTIONS) EXECUTIONS,

SUM (FETCHES) FETCHES,

SUM (SORTS) SORTS,

SUM (BUFFER_GETS) BUFFER_GETS,

SUM (DISK_READS) DISK_READS,

SUM (ROWS_PROCESSED) ROWS_PROCESSED,

SUM (CPU_TIME) CPU_TIME,

SUM (ELAPSED_TIME) ELAPSED_TIME,

SUM (IOWAIT) IOWAIT,

SUM (CLWAIT) CLWAIT,

SUM (APWAIT) APWAIT,

SUM (CCWAIT) CCWAIT

FROM A_DR) V1,

(SELECT SUM (EXECUTIONS) EXECUTIONS,

SUM (FETCHES) FETCHES,

SUM (SORTS) SORTS,

SUM (BUFFER_GETS) BUFFER_GETS,

SUM (DISK_READS) DISK_READS,

SUM (ROWS_PROCESSED) ROWS_PROCESSED,

SUM (CPU_TIME) CPU_TIME,

SUM (ELAPSED_TIME) ELAPSED_TIME,

SUM (IOWAIT) IOWAIT,

SUM (CLWAIT) CLWAIT,

SUM (APWAIT) APWAIT,

SUM (CCWAIT) CCWAIT

FROM A_TOT) V2

UNION ALL

SELECT *

FROM (SELECT 'DISK READS ONE' VIEWTYPE,

DBID,

SQL_ID,

OPTIMIZER_MODE,

MODULE,

EXECUTIONS,

FETCHES,

SORTS,

BUFFER_GETS,

DISK_READS,

ROWS_PROCESSED,

CPU_TIME,

ELAPSED_TIME,

BUFFER_GETS / EXECUTIONS BUF_EXEC,

DISK_READS / EXECUTIONS DISK_EXEC,

ROWS_PROCESSED / EXECUTIONS ROWS_EXEC,

CPU_TIME / EXECUTIONS CPU_EXEC,

ELAPSED_TIME / EXECUTIONS ELAP_EXEC,

IOWAIT,

CLWAIT,

APWAIT,

CCWAIT,

ROW_NUMBER ()

OVER (

ORDER BY

DISK_READS / EXECUTIONS DESC,

CLWAIT DESC,

ROWNUM)

RNUM

FROM A

UNION ALL

SELECT 'DISK READS ONE' VIEWTYPE,

NULL DBID,

NULL SQL_ID,

NULL OPTIMIZER_MODE,

NULL MODULE,

NULL EXECUTIONS,

NULL FETCHES,

NULL SORTS,

NULL BUFFER_GETS,

NULL DISK_READS,

NULL ROWS_PROCESSED,

NULL CPU_TIME,

NULL ELAPSED_TIME,

NULL BUF_EXEC,

NULL DISK_EXEC,

NULL ROWS_EXEC,

NULL CPU_EXEC,

NULL ELAP_EXEC,

NULL IOWAIT,

NULL CLWAIT,

NULL APWAIT,

NULL CCWAIT,

NULL RNUM

FROM A

WHERE ROWNUM <= :49 + 1)

WHERE RNUM <= :50 + 1 AND ROWNUM <= :51 + 1

UNION ALL

SELECT * FROM A_CL

UNION ALL

SELECT 'CLUSTER WAIT ALL' VIEWTYPE,

0 DBID,

'ETC' SQL_ID,

NULL OPTIMIZER_MODE,

'ETC' MODULE,

V2.EXECUTIONS - V1.EXECUTIONS EXECUTIONS,

V2.FETCHES - V1.FETCHES FETCHES,

V2.SORTS - V1.SORTS SORTS,

V2.BUFFER_GETS - V1.BUFFER_GETS BUFFER_GETS,

V2.DISK_READS - V1.DISK_READS DISK_READS,

V2.ROWS_PROCESSED - V1.ROWS_PROCESSED ROWS_PROCESSED,

V2.CPU_TIME - V1.CPU_TIME CPU_TIME,

V2.ELAPSED_TIME - V1.ELAPSED_TIME ELAPSED_TIME,

0 BUF_EXEC,

0 DISK_EXEC,

0 ROWS_EXEC,

0 CPU_EXEC,

0 ELAP_EXEC,

V2.IOWAIT - V1.IOWAIT IOWAIT,

V2.CLWAIT - V1.CLWAIT CLWAIT,

V2.APWAIT - V1.APWAIT APWAIT,

V2.CCWAIT - V1.CCWAIT CCWAIT,

NULL RNUM

FROM (SELECT SUM (EXECUTIONS) EXECUTIONS,

SUM (FETCHES) FETCHES,

SUM (SORTS) SORTS,

SUM (BUFFER_GETS) BUFFER_GETS,

SUM (DISK_READS) DISK_READS,

SUM (ROWS_PROCESSED) ROWS_PROCESSED,

SUM (CPU_TIME) CPU_TIME,

SUM (ELAPSED_TIME) ELAPSED_TIME,

SUM (IOWAIT) IOWAIT,

SUM (CLWAIT) CLWAIT,

SUM (APWAIT) APWAIT,

SUM (CCWAIT) CCWAIT

FROM A_CL) V1,

(SELECT SUM (EXECUTIONS) EXECUTIONS,

SUM (FETCHES) FETCHES,

SUM (SORTS) SORTS,

SUM (BUFFER_GETS) BUFFER_GETS,

SUM (DISK_READS) DISK_READS,

SUM (ROWS_PROCESSED) ROWS_PROCESSED,

SUM (CPU_TIME) CPU_TIME,

SUM (ELAPSED_TIME) ELAPSED_TIME,

SUM (IOWAIT) IOWAIT,

SUM (CLWAIT) CLWAIT,

SUM (APWAIT) APWAIT,

SUM (CCWAIT) CCWAIT

FROM A_TOT) V2

UNION ALL

SELECT * FROM A_CC

UNION ALL

SELECT 'CONSISTENT WAIT ALL' VIEWTYPE,

0 DBID,

'ETC' SQL_ID,

NULL OPTIMIZER_MODE,

'ETC' MODULE,

V2.EXECUTIONS - V1.EXECUTIONS EXECUTIONS,

V2.FETCHES - V1.FETCHES FETCHES,

V2.SORTS - V1.SORTS SORTS,

V2.BUFFER_GETS - V1.BUFFER_GETS BUFFER_GETS,

V2.DISK_READS - V1.DISK_READS DISK_READS,

V2.ROWS_PROCESSED - V1.ROWS_PROCESSED ROWS_PROCESSED,

V2.CPU_TIME - V1.CPU_TIME CPU_TIME,

V2.ELAPSED_TIME - V1.ELAPSED_TIME ELAPSED_TIME,

0 BUF_EXEC,

0 DISK_EXEC,

0 ROWS_EXEC,

0 CPU_EXEC,

0 ELAP_EXEC,

V2.IOWAIT - V1.IOWAIT IOWAIT,

V2.CLWAIT - V1.CLWAIT CLWAIT,

V2.APWAIT - V1.APWAIT APWAIT,

V2.CCWAIT - V1.CCWAIT CCWAIT,

NULL RNUM

FROM (SELECT SUM (EXECUTIONS) EXECUTIONS,

SUM (FETCHES) FETCHES,

SUM (SORTS) SORTS,

SUM (BUFFER_GETS) BUFFER_GETS,

SUM (DISK_READS) DISK_READS,

SUM (ROWS_PROCESSED) ROWS_PROCESSED,

SUM (CPU_TIME) CPU_TIME,

SUM (ELAPSED_TIME) ELAPSED_TIME,

SUM (IOWAIT) IOWAIT,

SUM (CLWAIT) CLWAIT,

SUM (APWAIT) APWAIT,

SUM (CCWAIT) CCWAIT

FROM A_CC) V1,

(SELECT SUM (EXECUTIONS) EXECUTIONS,

SUM (FETCHES) FETCHES,

SUM (SORTS) SORTS,

SUM (BUFFER_GETS) BUFFER_GETS,

SUM (DISK_READS) DISK_READS,

SUM (ROWS_PROCESSED) ROWS_PROCESSED,

SUM (CPU_TIME) CPU_TIME,

SUM (ELAPSED_TIME) ELAPSED_TIME,

SUM (IOWAIT) IOWAIT,

SUM (CLWAIT) CLWAIT,

SUM (APWAIT) APWAIT,

SUM (CCWAIT) CCWAIT

FROM A_TOT) V2

UNION ALL

SELECT * FROM A_IW

UNION ALL

SELECT 'IO WAIT ALL' VIEWTYPE,

0 DBID,

'ETC' SQL_ID,

NULL OPTIMIZER_MODE,

'ETC' MODULE,

V2.EXECUTIONS - V1.EXECUTIONS EXECUTIONS,

V2.FETCHES - V1.FETCHES FETCHES,

V2.SORTS - V1.SORTS SORTS,

V2.BUFFER_GETS - V1.BUFFER_GETS BUFFER_GETS,

V2.DISK_READS - V1.DISK_READS DISK_READS,

V2.ROWS_PROCESSED - V1.ROWS_PROCESSED ROWS_PROCESSED,

V2.CPU_TIME - V1.CPU_TIME CPU_TIME,

V2.ELAPSED_TIME - V1.ELAPSED_TIME ELAPSED_TIME,

0 BUF_EXEC,

0 DISK_EXEC,

0 ROWS_EXEC,

0 CPU_EXEC,

0 ELAP_EXEC,

V2.IOWAIT - V1.IOWAIT IOWAIT,

V2.CLWAIT - V1.CLWAIT CLWAIT,

V2.APWAIT - V1.APWAIT APWAIT,

V2.CCWAIT - V1.CCWAIT CCWAIT,

NULL RNUM

FROM (SELECT SUM (EXECUTIONS) EXECUTIONS,

SUM (FETCHES) FETCHES,

SUM (SORTS) SORTS,

SUM (BUFFER_GETS) BUFFER_GETS,

SUM (DISK_READS) DISK_READS,

SUM (ROWS_PROCESSED) ROWS_PROCESSED,

SUM (CPU_TIME) CPU_TIME,

SUM (ELAPSED_TIME) ELAPSED_TIME,

SUM (IOWAIT) IOWAIT,

SUM (CLWAIT) CLWAIT,

SUM (APWAIT) APWAIT,

SUM (CCWAIT) CCWAIT

FROM A_IW) V1,

(SELECT SUM (EXECUTIONS) EXECUTIONS,

SUM (FETCHES) FETCHES,

SUM (SORTS) SORTS,

SUM (BUFFER_GETS) BUFFER_GETS,

SUM (DISK_READS) DISK_READS,

SUM (ROWS_PROCESSED) ROWS_PROCESSED,

SUM (CPU_TIME) CPU_TIME,

SUM (ELAPSED_TIME) ELAPSED_TIME,

SUM (IOWAIT) IOWAIT,

SUM (CLWAIT) CLWAIT,

SUM (APWAIT) APWAIT,

SUM (CCWAIT) CCWAIT

FROM A_TOT) V2

UNION ALL

SELECT * FROM A_AW

UNION ALL

SELECT 'LOCK WAIT ALL' VIEWTYPE,

0 DBID,

'ETC' SQL_ID,

NULL OPTIMIZER_MODE,

'ETC' MODULE,

V2.EXECUTIONS - V1.EXECUTIONS EXECUTIONS,

V2.FETCHES - V1.FETCHES FETCHES,

V2.SORTS - V1.SORTS SORTS,

V2.BUFFER_GETS - V1.BUFFER_GETS BUFFER_GETS,

V2.DISK_READS - V1.DISK_READS DISK_READS,

V2.ROWS_PROCESSED - V1.ROWS_PROCESSED ROWS_PROCESSED,

V2.CPU_TIME - V1.CPU_TIME CPU_TIME,

V2.ELAPSED_TIME - V1.ELAPSED_TIME ELAPSED_TIME,

0 BUF_EXEC,

0 DISK_EXEC,

0 ROWS_EXEC,

0 CPU_EXEC,

0 ELAP_EXEC,

V2.IOWAIT - V1.IOWAIT IOWAIT,

V2.CLWAIT - V1.CLWAIT CLWAIT,

V2.APWAIT - V1.APWAIT APWAIT,

V2.CCWAIT - V1.CCWAIT CCWAIT,

NULL RNUM

FROM (SELECT SUM (EXECUTIONS) EXECUTIONS,

SUM (FETCHES) FETCHES,

SUM (SORTS) SORTS,

SUM (BUFFER_GETS) BUFFER_GETS,

SUM (DISK_READS) DISK_READS,

SUM (ROWS_PROCESSED) ROWS_PROCESSED,

SUM (CPU_TIME) CPU_TIME,

SUM (ELAPSED_TIME) ELAPSED_TIME,

SUM (IOWAIT) IOWAIT,

SUM (CLWAIT) CLWAIT,

SUM (APWAIT) APWAIT,

SUM (CCWAIT) CCWAIT

FROM A_AW) V1,

(SELECT SUM (EXECUTIONS) EXECUTIONS,

SUM (FETCHES) FETCHES,

SUM (SORTS) SORTS,

SUM (BUFFER_GETS) BUFFER_GETS,

SUM (DISK_READS) DISK_READS,

SUM (ROWS_PROCESSED) ROWS_PROCESSED,

SUM (CPU_TIME) CPU_TIME,

SUM (ELAPSED_TIME) ELAPSED_TIME,

SUM (IOWAIT) IOWAIT,

SUM (CLWAIT) CLWAIT,

SUM (APWAIT) APWAIT,

SUM (CCWAIT) CCWAIT

FROM A_TOT) V2

UNION ALL

SELECT * FROM A_EC

UNION ALL

SELECT 'EXEC CNT ALL' VIEWTYPE,

0 DBID,

'ETC' SQL_ID,

NULL OPTIMIZER_MODE,

'ETC' MODULE,

V2.EXECUTIONS - V1.EXECUTIONS EXECUTIONS,

V2.FETCHES - V1.FETCHES FETCHES,

V2.SORTS - V1.SORTS SORTS,

V2.BUFFER_GETS - V1.BUFFER_GETS BUFFER_GETS,

V2.DISK_READS - V1.DISK_READS DISK_READS,

V2.ROWS_PROCESSED - V1.ROWS_PROCESSED ROWS_PROCESSED,

V2.CPU_TIME - V1.CPU_TIME CPU_TIME,

V2.ELAPSED_TIME - V1.ELAPSED_TIME ELAPSED_TIME,

0 BUF_EXEC,

0 DISK_EXEC,

0 ROWS_EXEC,

0 CPU_EXEC,

0 ELAP_EXEC,

V2.IOWAIT - V1.IOWAIT IOWAIT,

V2.CLWAIT - V1.CLWAIT CLWAIT,

V2.APWAIT - V1.APWAIT APWAIT,

V2.CCWAIT - V1.CCWAIT CCWAIT,

NULL RNUM

FROM (SELECT SUM (EXECUTIONS) EXECUTIONS,

SUM (FETCHES) FETCHES,

SUM (SORTS) SORTS,

SUM (BUFFER_GETS) BUFFER_GETS,

SUM (DISK_READS) DISK_READS,

SUM (ROWS_PROCESSED) ROWS_PROCESSED,

SUM (CPU_TIME) CPU_TIME,

SUM (ELAPSED_TIME) ELAPSED_TIME,

SUM (IOWAIT) IOWAIT,

SUM (CLWAIT) CLWAIT,

SUM (APWAIT) APWAIT,

SUM (CCWAIT) CCWAIT

FROM A_EC) V1,

(SELECT SUM (EXECUTIONS) EXECUTIONS,

SUM (FETCHES) FETCHES,

SUM (SORTS) SORTS,

SUM (BUFFER_GETS) BUFFER_GETS,

SUM (DISK_READS) DISK_READS,

SUM (ROWS_PROCESSED) ROWS_PROCESSED,

SUM (CPU_TIME) CPU_TIME,

SUM (ELAPSED_TIME) ELAPSED_TIME,

SUM (IOWAIT) IOWAIT,

SUM (CLWAIT) CLWAIT,

SUM (APWAIT) APWAIT,

SUM (CCWAIT) CCWAIT

FROM A_TOT) V2

UNION ALL

SELECT 'TOTAL' VIEWTYPE,

DBID,

SQL_ID,

OPTIMIZER_MODE,

MODULE,

EXECUTIONS,

FETCHES,

SORTS,

BUFFER_GETS,

DISK_READS,

ROWS_PROCESSED,

CPU_TIME,

ELAPSED_TIME,

BUF_EXEC,

DISK_EXEC,

ROWS_EXEC,

CPU_EXEC,

ELAP_EXEC,

IOWAIT,

CLWAIT,

APWAIT,

CCWAIT,

RNUM

FROM A_TOT) SQLSTAT,

DBA_HIST_SQLTEXT SQLT

WHERE SQLSTAT.SQL_ID = SQLT.SQL_ID(+) AND SQLSTAT.DBID = SQLT.DBID(+)

ORDER BY VIEWTYPE, RNUM

--2.SQL_PLAN

/* Formatted on 2015-02-17 오후 2:17:18 (QP5 v5.163.1008.3004) */

SELECT LPAD (' ', DEPTH) || OPERATION || '_' || OPTIONS || ' ' || OBJECT_NAME

operation

FROM DBA_HIST_SQL_PLAN

WHERE SQL_ID = '8uru9t6frg2mw'

AND TIMESTAMP IN

(SELECT MAX (TIMESTAMP)

FROM DBA_HIST_SQL_PLAN

WHERE SQL_ID = '8uru9t6frg2mw'

AND DBID = (SELECT dbid FROM v$database)) --변경

ORDER BY ID, PLAN_HASH_VALUE;

/* Formatted on 2015-02-17 오후 1:17:13 (QP5 v5.163.1008.3004) */

SELECT DECODE (id, 0, CHR (10)) || LPAD (' ', 1 * (LEVEL - 1)) || OPERATION

|| DECODE (

id,

0, ' : '

|| optimizer

|| ' : '

|| TO_CHAR (TIMESTAMP, 'YYYY.MM.DD HH24:MI:SS')

|| ' ['

|| PLAN_HASH_VALUE

|| ']')

|| DECODE (options, NULL, NULL, ' (')

|| options

|| DECODE (options, NULL, NULL, ')')

|| DECODE (object_name, NULL, NULL, ' : ')

|| object_owner

|| DECODE (object_owner, NULL, NULL, '.')

|| OBJECT_NAME

|| DECODE (

operation,

'TABLE ACCESS', DECODE (

options,

'FULL', ' <'

|| TRIM (

TO_CHAR (

(SELECT num_rows

FROM all_tables at

WHERE at.owner =

object_owner

AND at.table_name =

object_name)))

|| '>'))

|| DECODE (

operation,

'INDEX', ' <'

|| TRIM (

TO_CHAR (

(SELECT avg_data_blocks_per_key

FROM all_indexes ai

WHERE ai.owner = object_owner

AND ai.index_name = object_name)))

|| '>')

|| DECODE (

SUBSTR (operation, 1, 5),

'INDEX', '(' || SEARCH_COLUMNS || ':'

|| REPLACE (

TRIM (

(SELECT /*+ LEADING(DIC.IO) USE_NL(DIC.IDX DIC.IC DIC.I) INDEX(DIC.BASE DIC.IDX) */

MAX (

DECODE (column_position,

1, column_name))

|| ' '

|| MAX (

DECODE (column_position,

2, column_name))

|| ' '

|| MAX (

DECODE (column_position,

3, column_name))

|| ' '

|| MAX (

DECODE (column_position,

4, column_name))

|| ' '

|| MAX (

DECODE (column_position,

5, column_name))

|| ' '

|| MAX (

DECODE (column_position,

6, column_name))

|| ' '

|| MAX (

DECODE (column_position,

7, column_name))

|| ' '

|| MAX (

DECODE (column_position,

8, column_name))

|| ' '

|| MAX (

DECODE (column_position,

9, column_name))

|| ' '

|| MAX (

DECODE (column_position,

10, column_name))

|| ' '

|| MAX (

DECODE (column_position,

11, column_name))

|| ' '

FROM all_ind_columns dic

WHERE dic.index_owner = object_owner

AND dic.index_name = object_name)),

' ',

',')

|| ')',

NULL)

|| DECODE (other_tag, NULL, NULL, '(')

|| other_tag

|| DECODE (other_tag, NULL, NULL, ')')

|| DECODE (partition_start, NULL, NULL, ' pst:')

|| partition_start

|| DECODE (partition_start, NULL, NULL, ' pstop: ')

|| partition_stop

AS OPERATION

FROM (SELECT /*+ ALL_ROWS NO_MERGE */

TO_CHAR (DBID),

SQL_ID,

PLAN_HASH_VALUE,

ID,

OPERATION,

OPTIONS,

OBJECT_NODE,

OBJECT#,

OBJECT_OWNER,

OBJECT_NAME,

OBJECT_ALIAS,

OBJECT_TYPE,

OPTIMIZER,

PARENT_ID,

DEPTH,

POSITION,

SEARCH_COLUMNS,

COST,

CARDINALITY,

BYTES,

OTHER_TAG,

PARTITION_START,

PARTITION_STOP,

PARTITION_ID,

OTHER,

DISTRIBUTION,

CPU_COST,

IO_COST,

TEMP_SPACE,

ACCESS_PREDICATES,

FILTER_PREDICATES,

PROJECTION,

TIME,

QBLOCK_NAME,

REMARKS,

TIMESTAMP,

RPAD (TO_CHAR (ID), 10, ' ')

|| TO_CHAR (TIMESTAMP, 'YYYYMMDDHH24MISS')

|| PLAN_HASH_VALUE

|| 'P'

|| SQL_ID

ID_M,

RPAD (TO_CHAR (PARENT_ID), 10, ' ')

|| TO_CHAR (TIMESTAMP, 'YYYYMMDDHH24MISS')

|| PLAN_HASH_VALUE

|| 'P'

|| SQL_ID

PID_M

FROM DBA_HIST_SQL_PLAN

WHERE SQL_ID = :1

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

AND TIMESTAMP IN (SELECT MAX (TIMESTAMP)

FROM DBA_HIST_SQL_PLAN

WHERE SQL_ID = :3 AND DBID = (select dbid from v$database) --변경

))

CONNECT BY PRIOR ID_M = PID_M

START WITH ID = 0 AND SQL_ID = :5;

--3.SQL_PROFILE 내역

/* Formatted on 2015-02-17 오후 12:58:52 (QP5 v5.163.1008.3004) */

SELECT LPAD ('DATE', 12, ' ')

|| LPAD ('BUFFER GETS TOTAL', 20, ' ')

|| LPAD ('CPU TIME TOTAL', 15, ' ')

|| LPAD ('CC WAIT TIME', 14, ' ')

|| LPAD ('IO WAIT TIME', 14, ' ')

|| LPAD ('ROWS PROCESSED', 15, ' ')

|| LPAD ('BUFF/EXEC', 15, ' ')

|| LPAD ('CPU/EXEC', 14, ' ')

|| LPAD ('CC/EXEC', 14, ' ')

|| LPAD ('IO/EXEC', 14, ' ')

|| CHR (10)

|| LPAD ('TOTAL EXECU', 12, ' ')

|| LPAD ('DISK READS TOTAL', 20, ' ')

|| LPAD ('ELAPSED TIME', 15, ' ')

|| LPAD ('CL WAIT TIME', 14, ' ')

|| LPAD ('AP WAIT TIME', 14, ' ')

|| LPAD ('ROWS PROC/EXEC', 15, ' ')

|| LPAD ('DISK/EXEC', 15, ' ')

|| LPAD ('ELAP/EXEC', 14, ' ')

|| LPAD ('CL/EXEC', 14, ' ')

|| LPAD ('AP/EXEC', 14, ' ')

FROM DUAL

UNION ALL

SELECT LPAD ('-', 147, '-') A FROM DUAL

UNION ALL

SELECT A

FROM ( SELECT LPAD (TO_CHAR (END_INTERVAL_TIME, 'YYYY.MM.DD'), 12, ' ')

|| LPAD (

TO_CHAR (SUM (BUFFER_GETS_DELTA),

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

20,

' ')

|| LPAD (

TO_CHAR (ROUND (SUM (CPU_TIME_DELTA) / 1000000, 1),

'FM999,999,999.9'),

15,

' ')

|| LPAD (

TO_CHAR (ROUND (SUM (CCWAIT_DELTA) / 1000000, 1),

'FM999,999,999.9'),

14,

' ')

|| LPAD (

TO_CHAR (ROUND (SUM (IOWAIT_DELTA) / 1000000, 1),

'FM999,999,999.9'),

14,

' ')

|| LPAD (

TO_CHAR (SUM (ROWS_PROCESSED_DELTA),

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

15,

' ')

|| LPAD (

TO_CHAR (

ROUND (

SUM (BUFFER_GETS_DELTA)

/ DECODE (SUM (EXECUTIONS_DELTA),

0, 1,

SUM (EXECUTIONS_DELTA)),

0),

'FM999,999,999,999.9'),

15,

' ')

|| LPAD (

TO_CHAR (

ROUND (

(SUM (CPU_TIME_DELTA) / 1000000)

/ DECODE (SUM (EXECUTIONS_DELTA),

0, 1,

SUM (EXECUTIONS_DELTA)),

1),

'FM999,999.9'),

14,

' ')

|| LPAD (

TO_CHAR (

ROUND (

(SUM (CCWAIT_DELTA) / 1000000)

/ DECODE (SUM (EXECUTIONS_DELTA),

0, 1,

SUM (EXECUTIONS_DELTA)),

1),

'FM999,999.9'),

14,

' ')

|| LPAD (

TO_CHAR (

ROUND (

(SUM (IOWAIT_DELTA) / 1000000)

/ DECODE (SUM (EXECUTIONS_DELTA),

0, 1,

SUM (EXECUTIONS_DELTA)),

1),

'FM999,999.9'),

14,

' ')

|| CHR (10)

|| LPAD (TO_CHAR (SUM (EXECUTIONS_DELTA), 'FM999,999,999'),

12,

' ')

|| LPAD (

TO_CHAR (SUM (DISK_READS_DELTA),

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

20,

' ')

|| LPAD (

TO_CHAR (ROUND (SUM (ELAPSED_TIME_DELTA) / 1000000, 1),

'FM999,999,999.9'),

15,

' ')

|| LPAD (

TO_CHAR (ROUND (SUM (CLWAIT_DELTA) / 1000000, 1),

'FM999,999,999.9'),

14,

' ')

|| LPAD (

TO_CHAR (ROUND (SUM (APWAIT_DELTA) / 1000000, 1),

'FM999,999,999.9'),

14,

' ')

|| LPAD (

TO_CHAR (

ROUND (

SUM (ROWS_PROCESSED_DELTA)

/ DECODE (SUM (EXECUTIONS_DELTA),

0, 1,

SUM (EXECUTIONS_DELTA)),

1),

'FM999,999,999.9'),

15,

' ')

|| LPAD (

TO_CHAR (

ROUND (

SUM (DISK_READS_DELTA)

/ DECODE (SUM (EXECUTIONS_DELTA),

0, 1,

SUM (EXECUTIONS_DELTA)),

0),

'FM999,999,999,999.9'),

15,

' ')

|| LPAD (

TO_CHAR (

ROUND (

(SUM (ELAPSED_TIME_DELTA) / 1000000)

/ DECODE (SUM (EXECUTIONS_DELTA),

0, 1,

SUM (EXECUTIONS_DELTA)),

1),

'FM999,999.9'),

14,

' ')

|| LPAD (

TO_CHAR (

ROUND (

(SUM (CLWAIT_DELTA) / 1000000)

/ DECODE (SUM (EXECUTIONS_DELTA),

0, 1,

SUM (EXECUTIONS_DELTA)),

1),

'FM999,999.9'),

14,

' ')

|| LPAD (

TO_CHAR (

ROUND (

(SUM (APWAIT_DELTA) / 1000000)

/ DECODE (SUM (EXECUTIONS_DELTA),

0, 1,

SUM (EXECUTIONS_DELTA)),

1),

'FM999,999.9'),

14,

' ')

FROM DBA_HIST_SQLSTAT DHSQL, DBA_HIST_SNAPSHOT DHSS

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

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

AND DHSQL.SQL_ID = :3

AND DHSS.DBID = DHSQL.DBID

AND DHSS.SNAP_ID = DHSQL.SNAP_ID

AND DHSS.INSTANCE_NUMBER = DHSQL.INSTANCE_NUMBER

GROUP BY TO_CHAR (END_INTERVAL_TIME, 'YYYY.MM.DD')

ORDER BY 1);

저작자표시 동일조건

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

Oracle Rawdevice구성 RMAN Backup스크립트 (0)	2019.01.15
오라클 SQL TRACE 추출 스크립트 (0)	2019.01.13
Oracle DB 기본 점검 스크립트 (0)	2019.01.10
Oracle RMAN FULL BACKUP 스크립트 (0)	2019.01.09
Oracle Top 10 Event 추출 스크립트 (0)	2019.01.08
Oracle DB AWR 활용하여 IO 성능 조회 스크립트 (0)	2019.01.07
Oracle DB 기본 상태 조회 스크립트 (0)	2019.01.07
Oracle AWR을 활용한 주요 Stats 지표 추출 스크립트 (0)	2019.01.07