Oracle Time Model 조회 스크립트

1. IT Story/Scripts 2019. 3. 22. 10:43

Oracle Time Model 조회 스크립트

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

SELECT DECODE (G1,

1, 'SUB AVG',

SUBSTR (SNAP_TIME, 1, INSTR (SNAP_TIME, '-') - 1))

SNAP_TIME,

SNAP_TIME SNAP_TIME_RANGE,

TO_CHAR (SUM (DECODE (STAT_NAME, 'DB time', VALUE)),

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

"DB time/SEC",

TO_CHAR (SUM (DECODE (STAT_NAME, 'DB CPU', VALUE)),

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

"DB CPU/SEC",

TO_CHAR (SUM (DECODE (STAT_NAME, 'background cpu time', VALUE)),

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

"Background CPU Time/SEC",

TO_CHAR (SUM (DECODE (STAT_NAME, 'background elapsed time', VALUE)),

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

"Background Elap. Time/SEC",

TO_CHAR (SUM (DECODE (STAT_NAME, 'SQL execute elapsed time', VALUE)),

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

"SQL Exec Elap. Time/SEC",

TO_CHAR (SUM (DECODE (STAT_NAME, 'parse time elapsed', VALUE)),

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

"Parse Time Elapsed/SEC",

TO_CHAR (SUM (DECODE (STAT_NAME, 'hard parse elapsed time', VALUE)),

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

"Hard Parse Elap. Time/SEC",

TO_CHAR (

SUM (

DECODE (STAT_NAME,

'hard parse (bind mismatch) elapsed time', VALUE)),

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

"H Parse B Miss Elap. Time/SEC",

TO_CHAR (

SUM (

DECODE (STAT_NAME,

'hard parse (sharing criteria) elapsed time', VALUE)),

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

"H Parse S Crit Elap. Time/SEC",

TO_CHAR (SUM (DECODE (STAT_NAME, 'failed parse elapsed time', VALUE)),

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

"Failed Parse Elap. Time/SEC",

TO_CHAR (

SUM (DECODE (STAT_NAME, 'repeated bind elapsed time', VALUE)),

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

"Repeated Bind Elap. Time/SEC",

TO_CHAR (

SUM (

DECODE (

STAT_NAME,

'failed parse (out of shared memory) elapsed time', VALUE)),

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

"F Parse O Mem Elap. Time/SEC",

TO_CHAR (

SUM (DECODE (STAT_NAME, 'PL/SQL execution elapsed time', VALUE)),

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

"PL/SQL Exec Elap. Time/SEC",

TO_CHAR (

SUM (DECODE (STAT_NAME, 'PL/SQL compilation elapsed time', VALUE)),

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

"PL/SQL Compile Elap. Time/SEC",

TO_CHAR (

SUM (DECODE (STAT_NAME, 'inbound PL/SQL rpc elapsed time', VALUE)),

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

"Inbound PL/SQL Elap. Time/SEC",

TO_CHAR (

SUM (DECODE (STAT_NAME, 'sequence load elapsed time', VALUE)),

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

"Sequence Load Elap. Time/SEC",

TO_CHAR (

SUM (DECODE (STAT_NAME, 'Java execution elapsed time', VALUE)),

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

"Java Exec Elap. Time/SEC",

TO_CHAR (

SUM (DECODE (STAT_NAME, 'RMAN cpu time (backup/restore)', VALUE)),

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

"RMAN CPU Time/SEC",

TO_CHAR (

SUM (

DECODE (STAT_NAME,

'connection management call elapsed time', VALUE)),

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

"Conn Mngt Call Elap. Time/SEC",

TO_CHAR (SUM (DECODE (STAT_NAME, 'DB CPU', VALUE_DIFF)),

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

"DB CPU DIFF",

TO_CHAR (SUM (DECODE (STAT_NAME, 'DB time', VALUE_DIFF)),

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

"DB time DIFF",

TO_CHAR (SUM (DECODE (STAT_NAME, 'background cpu time', VALUE_DIFF)),

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

"Background CPU Time DIFF",

TO_CHAR (

SUM (DECODE (STAT_NAME, 'background elapsed time', VALUE_DIFF)),

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

"Background Elap. Time DIFF",

TO_CHAR (

SUM (DECODE (STAT_NAME, 'SQL execute elapsed time', VALUE_DIFF)),

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

"SQL Exec Elap. Time DIFF",

TO_CHAR (SUM (DECODE (STAT_NAME, 'parse time elapsed', VALUE_DIFF)),

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

"Parse Time Elapsed DIFF",

TO_CHAR (

SUM (DECODE (STAT_NAME, 'hard parse elapsed time', VALUE_DIFF)),

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

"Hard Parse Elap. Time DIFF",

TO_CHAR (

SUM (

DECODE (STAT_NAME,

'hard parse (bind mismatch) elapsed time', VALUE_DIFF)),

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

"H Parse B Miss Elap. Time DIFF",

TO_CHAR (

SUM (

DECODE (

STAT_NAME,

'hard parse (sharing criteria) elapsed time', VALUE_DIFF)),

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

"H Parse S Crit Elap. Time DIFF",

TO_CHAR (

SUM (DECODE (STAT_NAME, 'failed parse elapsed time', VALUE_DIFF)),

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

"Failed Parse Elap. Time DIFF",

TO_CHAR (

SUM (

DECODE (

STAT_NAME,

'failed parse (out of shared memory) elapsed time', VALUE_DIFF)),

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

"F Parse O Mem Elap. Time DIFF",

TO_CHAR (

SUM (DECODE (STAT_NAME, 'repeated bind elapsed time', VALUE_DIFF)),

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

"Repeated Bind Elap. Time DIFF",

TO_CHAR (

SUM (

DECODE (STAT_NAME, 'PL/SQL execution elapsed time', VALUE_DIFF)),

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

"PL/SQL Exec Elap. Time DIFF",

TO_CHAR (

SUM (

DECODE (STAT_NAME,

'PL/SQL compilation elapsed time', VALUE_DIFF)),

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

"PL/SQL Compile Elap. Time DIFF",

TO_CHAR (

SUM (

DECODE (STAT_NAME,

'inbound PL/SQL rpc elapsed time', VALUE_DIFF)),

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

"Inbound PL/SQL Elap. Time DIFF",

TO_CHAR (

SUM (DECODE (STAT_NAME, 'sequence load elapsed time', VALUE_DIFF)),

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

"Sequence Load Elap. Time DIFF",

TO_CHAR (

SUM (DECODE (STAT_NAME, 'Java execution elapsed time', VALUE_DIFF)),

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

"Java Exec Elap. Time DIFF",

TO_CHAR (

SUM (

DECODE (STAT_NAME, 'RMAN cpu time (backup/restore)', VALUE_DIFF)),

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

"RMAN CPU Time DIFF",

TO_CHAR (

SUM (

DECODE (STAT_NAME,

'connection management call elapsed time', VALUE_DIFF)),

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

"Conn Mngt Call Elap. Time DIFF"

FROM ( SELECT STAT_NAME,

START_TIME || '-' || END_TIME SNAP_TIME,

ROUND (AVG (NVL (VALUE, 0)), 1) VALUE,

ROUND (AVG (NVL (VALUE_DIFF, 0)), 1) VALUE_DIFF,

GROUPING (START_TIME || '-' || END_TIME) G1,

GROUPING (STAT_NAME) G2

FROM (SELECT STAT_NAME,

TO_CHAR (SNAP_TIME_C1, 'MM.DD HH24:MI') START_TIME,

TO_CHAR (SNAP_TIME_C2, 'MM.DD HH24:MI') END_TIME,

DECODE (

SNAP_TIME_C2,

NULL, 0,

ROUND (

(CASE

WHEN VALUE_2 < VALUE_1 THEN 0

ELSE VALUE_2 - VALUE_1

END)

/ (EXTRACT (

DAY FROM SNAP_TIME_C2 - SNAP_TIME_C1)

* 86400

+ EXTRACT (

HOUR FROM SNAP_TIME_C2 - SNAP_TIME_C1)

* 3600

+ EXTRACT (

MINUTE FROM SNAP_TIME_C2 - SNAP_TIME_C1)

* 60

+ EXTRACT (

SECOND FROM SNAP_TIME_C2 - SNAP_TIME_C1)),

1))

VALUE,

(CASE

WHEN VALUE_2 < VALUE_1 THEN 0

ELSE VALUE_2 - VALUE_1

END)

VALUE_DIFF,

ROW_NUMBER ()

OVER (PARTITION BY INSTANCE_NUMBER, STAT_NAME

ORDER BY SNAP_ID)

RNUM,

SNAP_ID,

INSTANCE_NUMBER

FROM ( SELECT SNAP.END_INTERVAL_TIME SNAP_TIME_C1,

LEAD (

SNAP.END_INTERVAL_TIME)

OVER (

PARTITION BY DBI.INSTANCE_NUMBER, STAT_NAME

ORDER BY SNAP.SNAP_ID)

SNAP_TIME_C2,

STAT.STAT_NAME,

STAT.VALUE VALUE_1,

LEAD (

STAT.VALUE)

OVER (

PARTITION BY DBI.INSTANCE_NUMBER, STAT_NAME

ORDER BY SNAP.SNAP_ID)

VALUE_2,

SNAP.SNAP_ID,

DBI.INSTANCE_NUMBER

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_SYS_TIME_MODEL STAT

WHERE DBI.DBID = SNAP.DBID

AND DBI.INSTANCE_NUMBER = SNAP.INSTANCE_NUMBER

AND SNAP.SNAP_ID >= :3

AND SNAP.SNAP_ID <= :4

AND SNAP.DBID = STAT.DBID

AND SNAP.INSTANCE_NUMBER =

STAT.INSTANCE_NUMBER

AND SNAP.SNAP_ID = STAT.SNAP_ID

ORDER BY SNAP.SNAP_ID)

WHERE SNAP_TIME_C2 <> SNAP_TIME_C1)

WHERE START_TIME IS NOT NULL AND END_TIME IS NOT NULL

GROUP BY ROLLUP (STAT_NAME, START_TIME || '-' || END_TIME))

WHERE NOT (G1 = 1 AND G2 = 1)

GROUP BY DECODE (G1,

1, 'SUB AVG',

SUBSTR (SNAP_TIME, 1, INSTR (SNAP_TIME, '-') - 1)),

SNAP_TIME

ORDER BY SNAP_TIME

저작자표시 동일조건

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

Oracle Index Reorg 대상 추출 스크립트 (0)	2019.03.26
Oracle Alter log Viewer 조회 스크립트 (0)	2019.03.25
PostgreSQL 데이터베이스 조회 스크립트 (0)	2019.03.24
Oracle 미사용 테이블 조회 스크립트 (0)	2019.03.23
Oracle High Temp tablespace 사용률 조회 스크립트 (0)	2019.03.21
MySQL Session Lock 관리 스크립트 (0)	2019.03.20
MySQL 테이블/인덱스 사이즈 조회 스크립트 (0)	2019.03.19
PostgreSQL 10, Export/Import (0)	2019.03.18