오라클 RAC Estd.interconnect Traffic 관리 스크립트

1. IT Story/Scripts 2019. 3. 6. 21:39

오라클 RAC Estd.interconnect Traffic 관리 스크립트

SELECT SNAP_TIME, SNAP_TIME_RANGE,

TO_CHAR ("Estd.Interconnect Traffic(KB)", 'FM999,999,999.9')

"Estd.Interconnect Traffic(KB)",

TO_CHAR (

"Blocks Receive Time"

/ DECODE ("Blocks Received", 0, 1, "Blocks Received"),

'FM999,999,999.9')

"Interconnect Latency(ms)",

TO_CHAR (

"Blocks Served Time"

/ DECODE ("Blocks Served", 0, 1, "Blocks Served"),

'FM999,999,999.9')

"Prepare Latency(ms)",

TO_CHAR (

("Blocks Receive Time"

/ DECODE ("Blocks Received", 0, 1, "Blocks Received"))

- ("Blocks Served Time"

/ DECODE ("Blocks Served", 0, 1, "Blocks Served")),

'FM999,999,999.9')

"Transfer Latency(ms)",

TO_CHAR ("gc cr blocks served", 'FM999,999,999.9')

"gc cr blocks served",

TO_CHAR ("gc current blocks served", 'FM999,999,999.9')

"gc current blocks served",

TO_CHAR ("gc cr blocks received", 'FM999,999,999.9')

"gc cr blocks received",

TO_CHAR ("gc current blocks received", 'FM999,999,999.9')

"gc current blocks received",

TO_CHAR ("gcs messages sent", 'FM999,999,999.9') "gcs messages sent",

TO_CHAR ("ges messages sent", 'FM999,999,999.9') "ges messages sent",

TO_CHAR ("gcs msgs received", 'FM999,999,999.9') "gcs msgs received",

TO_CHAR ("ges msgs received", 'FM999,999,999.9') "ges msgs received",

TO_CHAR ("gc blocks lost", 'FM999,999,999.9') "gc blocks lost",

TO_CHAR ("gc cr block build time", 'FM999,999,999.99')

"gc cr block build tm(ms)",

TO_CHAR ("gc cr block flush time", 'FM999,999,999.99')

"gc cr block flush tm(ms)",

TO_CHAR ("gc current block flush time", 'FM999,999,999.99')

"gc current block flush tm(ms)",

TO_CHAR ("gc cr block receive time", 'FM999,999,999.99')

"gc cr block receive tm(ms)",

TO_CHAR ("gc current block receive time", 'FM999,999,999.99')

"gc current block receiv tm(ms)",

TO_CHAR ("gc cr block send time", 'FM999,999,999.99')

"gc cr block send tm(ms)",

TO_CHAR ("gc current block send time", 'FM999,999,999.99')

"gc current block send tm(ms)",

TO_CHAR ("gc current block pin time", 'FM999,999,999.99')

"gc current block pin tm(ms)",

TO_CHAR ("Blocks Served Time", 'FM999,999,999.99')

"Blocks Served tm(ms)",

TO_CHAR ("Blocks Served", 'FM999,999,999.9') "Blocks Served",

TO_CHAR ("Blocks Receive Time", 'FM999,999,999.99')

"Blocks Receive tm(ms)",

TO_CHAR ("Blocks Received", 'FM999,999,999.9') "Blocks Received"

FROM (SELECT SNAP_TIME,

SNAP_TIME_RANGE,

"gc cr blocks served",

"gc current blocks served",

"gc cr blocks received",

"gc current blocks received",

"gcs messages sent",

"ges messages sent",

"gcs msgs received",

"ges msgs received",

"gc blocks lost",

"gc cr block build time" * 10 "gc cr block build time",

"gc cr block flush time" * 10 "gc cr block flush time",

"gc current block flush time" * 10

"gc current block flush time",

"gc cr block send time" * 10 "gc cr block send time",

"gc current block send time" * 10 "gc current block send time",

"gc current block pin time" * 10 "gc current block pin time",

( ( "gc cr blocks served"

+ "gc current blocks served"

+ "gc cr blocks received"

+ "gc current blocks received")

* DB_BLOCK_SIZE

+ ( "gcs msgs received"

+ "ges msgs received"

+ "gcs messages sent"

+ "ges messages sent")

* 200)

/ 1024

"Estd.Interconnect Traffic(KB)",

( "gc cr block build time"

+ "gc cr block flush time"

+ "gc current block flush time"

+ "gc cr block send time"

+ "gc current block send time"

+ "gc current block pin time")

* 10

"Blocks Served Time",

"gc current blocks served" + "gc cr blocks served"

"Blocks Served",

("gc cr block receive time" + "gc current block receive time")

* 10

"Blocks Receive Time",

"gc cr blocks received" + "gc current blocks received"

"Blocks Received",

"gc cr block receive time" "gc cr block receive time",

"gc current block receive time"

FROM ( SELECT SNAP_TIME,

SNAP_TIME_RANGE,

SUM (DECODE (STAT_NAME, 'gc cr blocks served', VALUE))

"gc cr blocks served",

SUM (

DECODE (STAT_NAME,

'gc current blocks served', VALUE))

"gc current blocks served",

SUM (

DECODE (STAT_NAME, 'gc cr blocks received', VALUE))

"gc cr blocks received",

SUM (

DECODE (STAT_NAME,

'gc current blocks received', VALUE))

"gc current blocks received",

SUM (DECODE (STAT_NAME, 'gcs messages sent', VALUE))

"gcs messages sent",

SUM (DECODE (STAT_NAME, 'ges messages sent', VALUE))

"ges messages sent",

SUM (DECODE (STAT_NAME, 'gcs msgs received', VALUE))

"gcs msgs received",

SUM (DECODE (STAT_NAME, 'ges msgs received', VALUE))

"ges msgs received",

SUM (DECODE (STAT_NAME, 'gc blocks lost', VALUE))

"gc blocks lost",

SUM (

DECODE (STAT_NAME, 'gc cr block build time', VALUE))

"gc cr block build time",

SUM (

DECODE (STAT_NAME, 'gc cr block flush time', VALUE))

"gc cr block flush time",

SUM (

DECODE (STAT_NAME,

'gc current block flush time', VALUE))

"gc current block flush time",

SUM (

DECODE (STAT_NAME, 'gc cr block send time', VALUE))

"gc cr block send time",

SUM (

DECODE (STAT_NAME,

'gc current block send time', VALUE))

"gc current block send time",

SUM (

DECODE (STAT_NAME,

'gc current block pin time', VALUE))

"gc current block pin time",

SUM (

DECODE (STAT_NAME,

'gc cr block receive time', VALUE))

"gc cr block receive time",

SUM (

DECODE (STAT_NAME,

'gc current block receive time', VALUE))

"gc current block receive time",

MIN (P.PVALUE) DB_BLOCK_SIZE

FROM (SELECT STAT_NAME,

DECODE (

G1,

1, 'SUB AVG',

SUBSTR (SNAP_TIME,

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

SNAP_TIME,

SNAP_TIME SNAP_TIME_RANGE,

VALUE,

VALUE_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,

(CASE

WHEN VALUE_2 < VALUE_1

THEN

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)))

VALUE,

(CASE

WHEN VALUE_2 < VALUE_1

THEN

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,

MAX (

DI.STARTUP_TIME)

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) --변경

GROUP BY DI.DBID,

DI.INSTANCE_NUMBER) DBI,

DBA_HIST_SNAPSHOT SNAP,

DBA_HIST_SYSSTAT STAT

WHERE DBI.DBID = SNAP.DBID

AND DBI.INSTANCE_NUMBER =

SNAP.INSTANCE_NUMBER

AND DBI.DBID =

SNAP.DBID

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

AND STAT.STAT_NAME IN

('gc blocks lost',

'gc cr block build time',

'gc cr block flush time',

'gc current block flush time',

'gc cr block send time',

'gc current block send time',

'gc current block pin time',

'gc cr blocks served',

'gc current blocks served',

'gc cr blocks received',

'gc current blocks received',

'messages sent',

'messages received',

'gcs messages sent',

'ges messages sent',

'gc cr block receive time',

'gc current block receive time')

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)

UNION ALL

SELECT STAT_NAME,

DECODE (

G1,

1, 'SUB AVG',

SUBSTR (SNAP_TIME,

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

SNAP_TIME,

SNAP_TIME SNAP_TIME_RANGE,

VALUE,

VALUE_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

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

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,

NAME

ORDER BY

SNAP.SNAP_ID)

SNAP_TIME_C2,

STAT.NAME STAT_NAME,

STAT.VALUE VALUE_1,

LEAD (

STAT.VALUE)

OVER (

PARTITION BY DBI.INSTANCE_NUMBER,

NAME

ORDER BY

SNAP.SNAP_ID)

VALUE_2,

SNAP.SNAP_ID,

DBI.INSTANCE_NUMBER

FROM ( SELECT DI.DBID,

DI.INSTANCE_NUMBER,

MAX (

DI.STARTUP_TIME)

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) --변경

GROUP BY DI.DBID,

DI.INSTANCE_NUMBER) DBI,

DBA_HIST_SNAPSHOT SNAP,

DBA_HIST_DLM_MISC STAT

WHERE DBI.DBID = SNAP.DBID

AND DBI.INSTANCE_NUMBER =

SNAP.INSTANCE_NUMBER

AND DBI.DBID =

SNAP.DBID

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.NAME IN

('gcs msgs received',

'ges msgs received')

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)

ORDER BY STAT_NAME, SNAP_TIME) V,

(SELECT /*+ NO_MERGE */

VALUE PVALUE

FROM DBA_HIST_PARAMETER DHP, DBA_HIST_SNAPSHOT SNAP

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

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

AND SNAP.SNAP_ID = :11

AND SNAP.DBID = DHP.DBID

AND SNAP.INSTANCE_NUMBER = DHP.INSTANCE_NUMBER

AND SNAP.SNAP_ID = DHP.SNAP_ID

AND DHP.PARAMETER_NAME = 'db_block_size') P

GROUP BY SNAP_TIME, SNAP_TIME_RANGE))

ORDER BY SNAP_TIME

저작자표시 동일조건

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

PostgreSQL 인덱스 관리 스크립트 (0)	2019.03.10
PostgreSQL 테이블 상세 정보 조회 스크립트 (0)	2019.03.09
PostgreSQL 테이블별 사이즈 확인 스크립트 (0)	2019.03.08
PostgreSQL LOCK TREE 조회 스크립트 (0)	2019.03.07
오라클 물리적 IO 관리 스크립트 (0)	2019.03.05
오라클 인덱스 상세 조회 스크립트 (0)	2019.03.04
Oracle 수행 시간 기준 SQL 추출 스크립트 (0)	2019.03.03
Oracle Golden Gate 사전 점검 스크립트 (0)	2019.03.02