'1. IT Story/Scripts' 카테고리의 글 목록 (9 Page)

Oracle Rawdevice구성 RMAN Backup스크립트

1. IT Story/Scripts 2019. 1. 15. 22:06

Oracle Rawdevice구성 RMAN Backup스크립트

#/bin/bash

#RMAN backup script 2018/12/28

echo ##### BACKUP START!!!! #####

date

echo ###########################

# archive file delete(after 30 days)

echo ##### rac1,rac2 ARCHIVE LOG FILE DELETE -AFTER 30 DAYS- #####

find /archive/. -mtime +30 -print -exec rm {} \;

ssh rac2 'find /archive/. -mtime +30 -print -exec ssh rac2 rm {} \;'

# ocr, vote backup

echo ##### OCR BACKUP START #####

dd if=/dev/raw/raw13 of=/dbbackup/rman/ocrvote_bak/ocr.bak bs=200M count=1

echo ##### OCR BACKUP END #####

echo ##### VOTE BACKUP START #####

dd if=/dev/raw/raw15 of=/dbbackup/rman/ocrvote_bak/vote.bak bs=100M count=1

echo ##### VOTE BACKUP END #####

# spfile backup

echo ##### SPFILE BACKUP START #####

sqlplus "/ as sysdba" << EOF

create pfile='/backup/rman/pfile/initrac1.bak' from spfile;

exit

EOF

echo ##### SPFILE BACKUP END #####

# rman full backup

rman target / << EOF

rman target /

sql 'alter system archive log current';

sql "alter session set nls_date_format=''dd.mm.yyyy hh24:mi:ss''";

RUN

{

configure controlfile autobackup on;

set command id to 'ORCLOnlineBackupFull';

ALLOCATE CHANNEL c1 DEVICE TYPE disk;

ALLOCATE CHANNEL c2 DEVICE TYPE disk;

ALLOCATE CHANNEL c3 DEVICE TYPE disk;

ALLOCATE CHANNEL c4 DEVICE TYPE disk;

backup AS COMPRESSED BACKUPSET full database tag ORCL_FULL format '/backup/rman/data/%d_%T_%s_%p_FULL' ;

sql 'alter system archive log current';

backup tag ORCL_ARCHIVE format '/backup/rman/archive/%d_%T_%s_%p_ARCHIVE' archivelog all delete all input ;

backup tag ORCL_CONTROL current controlfile format '/backup/rman/control/%d_%T_%s_%p_CONTROL';

release channel c1;

release channel c2;

release channel c3;

release channel c4;

}

crosscheck backup;

crosscheck backupset;

crosscheck copy;

crosscheck archivelog all;

delete noprompt expired backup;

delete noprompt obsolete;

list backup;

exit

EOF

저작자표시 동일조건

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

Oracle DB의 OS 상태 조회 스크립트 (0)	2019.01.27
OS(리눅스) 5초단위로 서버 및 DB 상태 출력 스크립트 (0)	2019.01.24
Oracle Alert Log Viewer (11gR2) (0)	2019.01.19
Oracle Wait Event 조회 스크립트 (0)	2019.01.16
오라클 SQL TRACE 추출 스크립트 (0)	2019.01.13
Oracle DB 기본 점검 스크립트 (0)	2019.01.10
Oracle RMAN FULL BACKUP 스크립트 (0)	2019.01.09
Oracle SQL Stat 조회 스크립트 (0)	2019.01.08

운명을바꾸는자

IT와 함께 살아가는 삶

,

오라클 SQL TRACE 추출 스크립트

1. IT Story/Scripts 2019. 1. 13. 20:29

오라클 SQL Trace 추출 스크립트

alter session set tracefile_identifier='test'; -- User Trace를 찾기 쉽게 네이밍

alter session set timed_statistics = true;

-- alter session set statistics_level = all;

alter session set max_dump_file_size = unlimited;

alter session set events '10046 trace name context forever, level 12';

-- alter session set "_rowsource_execution_statistics"=true;

set lines 600;

-- SQL TRACE TARGET SQL

Select count(*) from scott.test;

alter session set events '10046 trace name context off';

SELECT p.osuser, p.process cospid, p.spid sospid, p.username dbuser, p.sid, p.serial#,

'tkprof '||d.value||'/'||lower(rtrim(i.instance,chr(0)))||'_ora_'||p.spid||'_great.trc'

||' ./'||lower(rtrim(i.instance,chr(0)))||'_ora_'||p.spid||'.prf sys=no' tkprof_cmd,

'cat ./'||lower(rtrim(i.instance,chr(0)))||'_ora_'||p.spid||'.prf' view_cmd

FROM

(SELECT s.osuser, s.process, p.spid, s.username, s.sid, s.serial#

FROM v$session s, v$process p

WHERE s.paddr = p.addr and s.sid = (select distinct sid from v$mystat)

) p,

(SELECT instance

FROM v$thread

WHERE thread# = (SELECT to_number(value)+1 FROM v$parameter WHERE name = 'thread')

) i,

(SELECT value FROM v$parameter WHERE name = 'user_dump_dest'

) d;

저작자표시 동일조건

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

OS(리눅스) 5초단위로 서버 및 DB 상태 출력 스크립트 (0)	2019.01.24
Oracle Alert Log Viewer (11gR2) (0)	2019.01.19
Oracle Wait Event 조회 스크립트 (0)	2019.01.16
Oracle Rawdevice구성 RMAN Backup스크립트 (0)	2019.01.15
Oracle DB 기본 점검 스크립트 (0)	2019.01.10
Oracle RMAN FULL BACKUP 스크립트 (0)	2019.01.09
Oracle SQL Stat 조회 스크립트 (0)	2019.01.08
Oracle Top 10 Event 추출 스크립트 (0)	2019.01.08

운명을바꾸는자

IT와 함께 살아가는 삶

,

Oracle DB 기본 점검 스크립트

1. IT Story/Scripts 2019. 1. 10. 21:50

Oracle 기본 점검 스크립트

column timecol new_value timestamp

column spool_extension new_value suffix

select to_char(sysdate,'yymmdd') timecol, '_mon.txt' spool_extension from sys.dual;

column output new_value instancename

select '_' || value output from v$parameter where name = 'instance_name';

spool &&timestamp&&instancename&&suffix

alter session set sort_area_size=50000000;

prompt *********************************************************************************

prompt * *

prompt * BASE INFO *

prompt * *

prompt *********************************************************************************

set head off

select ' HOST_NAME : '||HOST_NAME from v$instance;

select ' Database Name : '||name from v$database;

select ' Instance Name : '||INSTANCE_NAME from v$instance;

select ' Global Name : '||value from v$parameter where name='global_names';

select ' Version : '||VERSION from v$instance;

select ' Startup Time : '||STARTUP_TIME from v$instance;

select ' Archive : '||ARCHIVER from v$instance;

col comp_id for a10

col comp_Name for a30

col version for a15

set head on

SELECT substr(comp_id,1,15) comp_id, status,

substr(version,1,10) version,

substr(comp_name,1,30) comp_name

FROM dba_registry

ORDER BY 1;

set head off

PROMPT

PROMPT *********************************************************************

PROMPT ** Buffer Cache Hit Ratio Statistics **

PROMPT ** **

PROMPT *********************************************************************

SET LINESIZE 70

COL "Get1" FORMAT 999,999,999,999,999 HEADING "Consistent Gets"

COL "Get2" FORMAT 999,999,999,999,999 HEADING "DB Block Gets"

COL "Get3" FORMAT 999,999,999,999,999 HEADING "Physical Reads"

COL "Hit" FORMAT 999.99 HEADING "Hit|Ratio"

select sum(decode(name, 'consistent gets', value, 0)) "Get1",

sum(decode(name, 'db block gets', value, 0)) "Get2",

sum(decode(name, 'physical reads', value, 0)) "Get3",

round((sum(decode(name, 'consistent gets', value, 0)) +

sum(decode(name, 'db block gets', value, 0)) -

sum(decode(name, 'physical reads', value, 0))) /

(sum(decode(name, 'consistent gets', value, 0)) +

sum(decode(name, 'db block gets', value, 0))) * 100, 2) "Hit"

from sys.v_$sysstat;

PROMPT

PROMPT *********************************************************************

PROMPT ** Library Cache Hit Ratio Statistics **

PROMPT ** **

PROMPT *********************************************************************

SET LINESIZE 70

COL "Executions" FORMAT 999,999,999,999,999 HEADING "Executions"

COL "EH" FORMAT 999,999,999,999,999 HEADING "Executions Hits"

COL "Hit1" FORMAT 999.99 HEADING "Hit|Ratio"

COL "Misses" FORMAT 999,999,999 HEADING "Misses"

COL "Hit2" FORMAT 999.99 HEADING "Hit|Ratio"

select sum(pins) "Executions", sum(pinhits) "EH",

round((sum(pinhits) / sum(pins)) * 100, 3) "Hit1",

sum(reloads) "Misses",

round((sum(pins) / (sum(pins) + sum(reloads))) * 100, 3) "Hit2"

from sys.v_$librarycache;

PROMPT

PROMPT *********************************************************************

PROMPT ** Data Dictionary Cache Hit Ratio Statistics **

PROMPT *********************************************************************

SET LINESIZE 45

COL "Gets" FORMAT 999,999,999,999,999 HEADING "Gets"

COL "Cache" FORMAT 999,999,999,999 HEADING "Cache Missess"

COL "Hit" FORMAT 999.99 HEADING "Hit|Ratio"

select sum(gets) "Gets", sum(getmisses) "Cache",

round((1 - (sum(getmisses) / sum(gets))) * 100, 2) "Hit"

from sys.v_$rowcache;

PROMPT

PROMPT *********************************************************************

PROMPT ** Sort Statistics **

PROMPT ** **

PROMPT *********************************************************************

SET LINESIZE 80

COL name FORMAT A30 HEADING "Sort Parameter"

COL value FORMAT A30 HEADING "Value"

select name, value

from v$parameter

where name like '%sort%';

PROMPT *********************************************************************

PROMPT ** Sort Ratio 가 < 1% 이하 **

PROMPT *********************************************************************

SET LINESIZE 60

COL Disk FORMAT 999,999,999,999,999

COL Mem FORMAT 999,999,999,999,999

COL Ratio FORMAT 999.99 HEADING "Sort|Ratio"

select disk.value Disk, mem.value Mem,

round((1 - (mem.value )/(mem.value+disk.value))*100,2) Ratio

from v$sysstat mem, v$sysstat disk

where mem.name = 'sorts (memory)'

and disk.name = 'sorts (disk)';

PROMPT

PROMPT *********************************************************************

PROMPT ** Tablespace Statistics **

PROMPT ** **

PROMPT *********************************************************************

SET LINESIZE 120

COL tablespace_name FORMAT A20 HEADING "Tablespace Name"

COL "Bytes" FORMAT 999,999,999.99 HEADING "TBS Size(MB)"

COL "Free" FORMAT 999,999.99 HEADING "Free Size(MB)"

COL "Percent" FORMAT 999.99 HEADING "Usage(%)"

COL "Used" FORMAT 999,999,999.99 HEADING "Used(MB)"

COL "FreeP" FORMAT 999.99 HEADING "Free(%)"

select * from (

select ddf.tablespace_name,

ddf.bytes/1024/1024 "Bytes",

dfs.bytes/1024/1024 "Free",

round(((ddf.bytes - dfs.bytes) / ddf.bytes) * 100, 2) "Percent"

from ( select tablespace_name, sum(bytes) bytes

from dba_data_files

group by tablespace_name) ddf,

( select tablespace_name, sum(bytes) bytes

from dba_free_space

group by tablespace_name) dfs

where ddf.tablespace_name = dfs.tablespace_name

order by ((ddf.bytes-dfs.bytes)/ddf.bytes) desc)

where rownum < 6

;

PROMPT

PROMPT *********************************************************************

PROMPT ** Datafile I/O Statistics **

PROMPT ** **

PROMPT *********************************************************************

PROMPT

set lines 120

col File_name form a52 heading 'File name'

col tablespace_name form a20 heading 'Tablespace'

col br form 99,999,999 heading 'BLK Read'

col bw form 99,999,999 heading 'BLK Write'

col Weight form 999.99 heading 'Weight|(%)'

select * from (

select tablespace_name,file_name,

round((phyblkrd+phyblkwrt*1.2)/z.sum*100, 2) "Weight"

from dba_data_files df,

v$filestat fs,

(select sum(phyblkrd+phyblkwrt*1.2) as sum from v$filestat) z

where df.file_id = fs.file# order by 3 desc)

where rownum<6

;

PROMPT

PROMPT *********************************************************************

PROMPT ** Object Extents > 20 **

PROMPT *********************************************************************

col Owner form a9

col Object_name form a30

col Type form a1 trunc

col Tablespace form a12

col Size(KB) form 999,999,999

col Init(KB) form 999,999

col Next(KB) form 999,999

col Ext form 999,999

col Owner form a9

col Object_name form a30

col Type form a1 trunc

col Tablespace form a15

col Size(KB) form 999,999,999

col Init(KB) form 999,999

col Next(KB) form 999,999

col Ext form 999,999

select * from (

select Owner,

segment_name Object_name,

segment_type Type,

tablespace_name Tablespace ,

Bytes/1024 "Size(KB)",

initial_extent/1024 "Init(KB)",

next_extent/1024 "Next(KB)",

extents Ext

from dba_segments

where owner != 'SYS' and owner != 'SYSTEM'

and extents > 20

order by 8 desc, 1,2,3,4)

where rownum < 6;

PROMPT

PROMPT *********************************************************************

PROMPT ** Tablespace SIZE **

PROMPT ** **

PROMPT *********************************************************************

PROMPT

set head on

set trimspool on

column totalsize heading "total size(M)"

column usedsize heading "used size(M)"

column freesize heading "free size(M)"

column tablespace_name format a20

column totalsize format 999,999.9

column usedsize format 999,999.9

column freesize format 999,999.9

column used_percent format 999.9

select a.tablespace_name, a.totalsize,

nvl(b.usedsize,0) usedsize,nvl(round(((b.usedsize/a.totalsize)*100),1),0) Used_Percent,c.freesize

from ( select tablespace_name, sum(bytes)/1024/1024 totalsize

from dba_data_files

group by tablespace_name ) a,

( select tablespace_name, sum(bytes)/1024/1024 usedsize

from dba_segments

group by tablespace_name ) b,

( select tablespace_name, sum(bytes)/1024/1024 freesize

from dba_free_space

group by tablespace_name ) c

where a.tablespace_name = b.tablespace_name(+)

and a.tablespace_name = c.tablespace_name(+)

order by 1;

set head off

prompt *******************************************************

prompt * *

prompt * Cache Fusion For RAC

prompt * *

prompt *******************************************************

prompt

set linesize 100

col name format a16

col kind format a10

col partition_name format a10

column partition_name for a15

select inst_id,name, substr(kind,1,10) kind, partition_name,

sum(xnc) xnc,sum(FORCED_READS) reads, sum(FORCED_writes) writes

from gv$cache_transfer

group by inst_id,name, kind, partition_name

having sum(xnc) > 1

order by 1,4

;

prompt

prompt *************************************************************

prompt * RESOURCE *

prompt *************************************************************

prompt

set head on

col RESOURCE_NAME for a10

col CURRENT_UTILIZATION for 9999

col MAX_UTILIZATION for 9999

col INITIAL_ALLOCATION for a10

col LIMIT_VALUE for a10

select * from v$resource_limit where resource_name in ('processes', 'sessions');

set head off

Prompt

prompt 2. Data file total size

select ' TOTAL DATA FILE SIZE : '||sum(bytes)/1024/1024||' MB' FROM v$datafile

union

SELECT ' USED SIZE : '||sum(bytes)/1024/1024||' MB' FROM dba_segments

union

SELECT ' FREE SIZE : '||sum(bytes)/1024/1024||' MB' FROM dba_free_space;

prompt

set head on

prompt

prompt 3. Backup check

col time for a80

select 'Hot Backup : '||to_char(min(b.time),'yyyy-mm-dd hh24:mi:ss')||' ~ '||to_char(max(b.time),'yyyy-mm-dd hh24:mi:ss') Time

from v$backup b, v$datafile d

where b.file#=d.file#

and b.status = 'NOT ACTIVE';

select *

from v$backup b, v$datafile d

where b.file#=d.file#

and b.status != 'NOT ACTIVE';

set head off

prompt

prompt *************************************************************

prompt * NEXT CHECK *

prompt *************************************************************

prompt

prompt alert log and trace file check

show parameter dump

prompt

prompt archive log list

archive log list;

Prompt

prompt

prompt *************************************************************

prompt * NLS_CHARACTERSET *

prompt *************************************************************

prompt

select * from NLS_DATABASE_PARAMETERS where parameter = 'NLS_CHARACTERSET';

Prompt

prompt

prompt *************************************************************

prompt * NLS_CHARACTERSET *

prompt *************************************************************

prompt

Select * from v$version;

Prompt

prompt *************************************************************

prompt * *

prompt *************************************************************

select name, state, type, total_mb, free_mb from v$asm_diskgroup;

spool off

저작자표시 동일조건

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

Oracle Alert Log Viewer (11gR2) (0)	2019.01.19
Oracle Wait Event 조회 스크립트 (0)	2019.01.16
Oracle Rawdevice구성 RMAN Backup스크립트 (0)	2019.01.15
오라클 SQL TRACE 추출 스크립트 (0)	2019.01.13
Oracle RMAN FULL BACKUP 스크립트 (0)	2019.01.09
Oracle SQL Stat 조회 스크립트 (0)	2019.01.08
Oracle Top 10 Event 추출 스크립트 (0)	2019.01.08
Oracle DB AWR 활용하여 IO 성능 조회 스크립트 (0)	2019.01.07

운명을바꾸는자

IT와 함께 살아가는 삶

,

Oracle RMAN FULL BACKUP 스크립트

1. IT Story/Scripts 2019. 1. 9. 21:49

Oracle RMAN FULL BACKUP 스크립트

rman target /

sql 'alter system archive log current';

sql "alter session set nls_date_format=''dd.mm.yyyy hh24:mi:ss''";

RUN

{

configure controlfile autobackup on;

set command id to 'ORCLOnlineBackupFull';

ALLOCATE CHANNEL c1 DEVICE TYPE disk;

ALLOCATE CHANNEL c2 DEVICE TYPE disk;

ALLOCATE CHANNEL c3 DEVICE TYPE disk;

ALLOCATE CHANNEL c4 DEVICE TYPE disk;

backup AS COMPRESSED BACKUPSET full database tag ORCL_FULL format '/backup/rman/data/%d_%T_%s_%p_FULL' ;

sql 'alter system archive log current';

backup tag ORCL_ARCHIVE format '/backup/rman/archive/%d_%T_%s_%p_ARCHIVE' archivelog all delete all input ;

backup tag ORCL_CONTROL current controlfile format '/backup/rman/control/%d_%T_%s_%p_CONTROL';

release channel c1;

release channel c2;

release channel c3;

release channel c4;

}

저작자표시 동일조건

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

Oracle Wait Event 조회 스크립트 (0)	2019.01.16
Oracle Rawdevice구성 RMAN Backup스크립트 (0)	2019.01.15
오라클 SQL TRACE 추출 스크립트 (0)	2019.01.13
Oracle DB 기본 점검 스크립트 (0)	2019.01.10
Oracle SQL Stat 조회 스크립트 (0)	2019.01.08
Oracle Top 10 Event 추출 스크립트 (0)	2019.01.08
Oracle DB AWR 활용하여 IO 성능 조회 스크립트 (0)	2019.01.07
Oracle DB 기본 상태 조회 스크립트 (0)	2019.01.07

운명을바꾸는자

IT와 함께 살아가는 삶

,

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

A

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,

' ')

A

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

운명을바꾸는자

IT와 함께 살아가는 삶

,

Oracle Top 10 Event 추출 스크립트

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

Oracle Top 10 Event 추출 스크립트

SELECT SNAP_TIME,

TO_CHAR (SUM (DECODE (RNK, 1, VALUE)), 'FM999,999,999,999.999')

"VALUE Per SEC_1",

TO_CHAR (SUM (DECODE (RNK, 2, VALUE)), 'FM999,999,999,999.999')

"VALUE Per SEC_2",

TO_CHAR (SUM (DECODE (RNK, 3, VALUE)), 'FM999,999,999,999.999')

"VALUE Per SEC_3",

TO_CHAR (SUM (DECODE (RNK, 4, VALUE)), 'FM999,999,999,999.999')

"VALUE Per SEC_4",

TO_CHAR (SUM (DECODE (RNK, 5, VALUE)), 'FM999,999,999,999.999')

"VALUE Per SEC_5",

TO_CHAR (SUM (DECODE (RNK, 6, VALUE)), 'FM999,999,999,999.999')

"VALUE Per SEC_6",

TO_CHAR (SUM (DECODE (RNK, 7, VALUE)), 'FM999,999,999,999.999')

"VALUE Per SEC_7",

TO_CHAR (SUM (DECODE (RNK, 8, VALUE)), 'FM999,999,999,999.999')

"VALUE Per SEC_8",

TO_CHAR (SUM (DECODE (RNK, 9, VALUE)), 'FM999,999,999,999.999')

"VALUE Per SEC_9",

TO_CHAR (SUM (DECODE (RNK, 10, VALUE)), 'FM999,999,999,999.999')

"VALUE Per SEC_10",

TO_CHAR (SUM (DECODE (RNK, 11, VALUE)), 'FM999,999,999,999.999')

"VALUE Per SEC_ETC",

TO_CHAR (SUM (DECODE (RNK, 1, VALUE_DIFF)), 'FM999,999,999,999,999')

VALUE_DIFF_1,

TO_CHAR (SUM (DECODE (RNK, 2, VALUE_DIFF)), 'FM999,999,999,999,999')

VALUE_DIFF_2,

TO_CHAR (SUM (DECODE (RNK, 3, VALUE_DIFF)), 'FM999,999,999,999,999')

VALUE_DIFF_3,

TO_CHAR (SUM (DECODE (RNK, 4, VALUE_DIFF)), 'FM999,999,999,999,999')

VALUE_DIFF_4,

TO_CHAR (SUM (DECODE (RNK, 5, VALUE_DIFF)), 'FM999,999,999,999,999')

VALUE_DIFF_5,

TO_CHAR (SUM (DECODE (RNK, 6, VALUE_DIFF)), 'FM999,999,999,999,999')

VALUE_DIFF_6,

TO_CHAR (SUM (DECODE (RNK, 7, VALUE_DIFF)), 'FM999,999,999,999,999')

VALUE_DIFF_7,

TO_CHAR (SUM (DECODE (RNK, 8, VALUE_DIFF)), 'FM999,999,999,999,999')

VALUE_DIFF_8,

TO_CHAR (SUM (DECODE (RNK, 9, VALUE_DIFF)), 'FM999,999,999,999,999')

VALUE_DIFF_9,

TO_CHAR (SUM (DECODE (RNK, 10, VALUE_DIFF)), 'FM999,999,999,999,999')

VALUE_DIFF_10,

TO_CHAR (SUM (DECODE (RNK, 11, VALUE_DIFF)), 'FM999,999,999,999,999')

VALUE_DIFF_ETC

FROM (SELECT A.EVENT_NAME,

B.EVENT_NAME B_EVENT_NAME,

A.SNAP_TIME,

A.VALUE,

A.VALUE_DIFF,

B.RNK RNK

FROM (SELECT EVENT_NAME,

DECODE (G1, 1, 'SUB AVG', SNAP_TIME) SNAP_TIME,

VALUE,

VALUE_DIFF

FROM ( SELECT EVENT_NAME,

END_TIME SNAP_TIME,

AVG (NVL (VALUE, 0)) VALUE,

AVG (NVL (VALUE_DIFF, 0)) VALUE_DIFF,

GROUPING (END_TIME) G1,

GROUPING (EVENT_NAME) G2

FROM (SELECT EVENT_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 0

ELSE VALUE_2 - VALUE_1

END)

/ (1000000

* (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 0

ELSE VALUE_2 - VALUE_1

END)

/ 1000000

VALUE_DIFF,

ROW_NUMBER ()

OVER (

PARTITION BY INSTANCE_NUMBER,

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

EVENT_NAME

ORDER BY SNAP.SNAP_ID)

SNAP_TIME_C2,

EVENT.EVENT_NAME,

EVENT.TIME_WAITED_MICRO

VALUE_1,

LEAD (

EVENT.TIME_WAITED_MICRO)

OVER (

PARTITION BY DBI.INSTANCE_NUMBER,

EVENT_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_SYSTEM_EVENT EVENT

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 = EVENT.DBID

AND SNAP.INSTANCE_NUMBER =

EVENT.INSTANCE_NUMBER

AND SNAP.SNAP_ID =

EVENT.SNAP_ID

AND UPPER (EVENT.WAIT_CLASS) <>

'IDLE'

ORDER BY SNAP.SNAP_ID))

WHERE START_TIME IS NOT NULL

AND END_TIME IS NOT NULL

GROUP BY ROLLUP (EVENT_NAME, END_TIME))

WHERE NOT (G1 = 1 AND G2 = 1)) A,

(SELECT EVENT_NAME, RNK

FROM (SELECT EVENT_NAME, RNK

FROM (SELECT EVENT_NAME,

RANK ()

OVER (

ORDER BY

VALUE_DIFF DESC,

EVENT_NAME ASC NULLS LAST)

RNK

FROM (SELECT EVENT_NAME,

DECODE (G1,

1, 'SUB AVG',

SNAP_TIME)

SNAP_TIME,

VALUE,

VALUE_DIFF

FROM ( SELECT EVENT_NAME,

END_TIME SNAP_TIME,

AVG (NVL (VALUE, 0))

VALUE,

AVG (

NVL (VALUE_DIFF, 0))

VALUE_DIFF,

GROUPING (END_TIME) G1,

GROUPING (EVENT_NAME)

G2

FROM (SELECT EVENT_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

0

ELSE

VALUE_2

- VALUE_1

END)

/ (1000000

* (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

0

ELSE

VALUE_2

- VALUE_1

END)

/ 1000000

VALUE_DIFF,

ROW_NUMBER ()

OVER (

PARTITION BY INSTANCE_NUMBER,

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

EVENT_NAME

ORDER BY

SNAP.SNAP_ID)

SNAP_TIME_C2,

EVENT.EVENT_NAME,

EVENT.TIME_WAITED_MICRO

VALUE_1,

LEAD (

EVENT.TIME_WAITED_MICRO)

OVER (

PARTITION BY DBI.INSTANCE_NUMBER,

EVENT_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_SYSTEM_EVENT EVENT

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 =

EVENT.DBID

AND SNAP.INSTANCE_NUMBER =

EVENT.INSTANCE_NUMBER

AND SNAP.SNAP_ID =

EVENT.SNAP_ID

AND UPPER (

EVENT.WAIT_CLASS) <>

'IDLE'

ORDER BY SNAP.SNAP_ID))

WHERE START_TIME IS NOT NULL

AND END_TIME

IS NOT NULL

GROUP BY ROLLUP (EVENT_NAME,

END_TIME))

WHERE NOT (G1 = 1 AND G2 = 1))

WHERE SNAP_TIME = 'SUB AVG')

WHERE RNK <= 10)) B

WHERE A.EVENT_NAME = B.EVENT_NAME

UNION ALL

SELECT ' 나머지 Event Sum' EVENT_NAME,

NULL B_EVENT_NAME,

A.SNAP_TIME,

SUM (A.VALUE) VALUE,

SUM (A.VALUE_DIFF) VALUE_DIFF,

11 RNK

FROM (SELECT EVENT_NAME,

DECODE (G1, 1, 'SUB AVG', SNAP_TIME) SNAP_TIME,

VALUE,

VALUE_DIFF

FROM ( SELECT EVENT_NAME,

END_TIME SNAP_TIME,

AVG (NVL (VALUE, 0)) VALUE,

AVG (NVL (VALUE_DIFF, 0)) VALUE_DIFF,

GROUPING (END_TIME) G1,

GROUPING (EVENT_NAME) G2

FROM (SELECT EVENT_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 0

ELSE VALUE_2 - VALUE_1

END)

/ (1000000

* (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 0

ELSE VALUE_2 - VALUE_1

END)

/ 1000000

VALUE_DIFF,

ROW_NUMBER ()

OVER (

PARTITION BY INSTANCE_NUMBER,

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

EVENT_NAME

ORDER BY SNAP.SNAP_ID)

SNAP_TIME_C2,

EVENT.EVENT_NAME,

EVENT.TIME_WAITED_MICRO

VALUE_1,

LEAD (

EVENT.TIME_WAITED_MICRO)

OVER (

PARTITION BY DBI.INSTANCE_NUMBER,

EVENT_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_SYSTEM_EVENT EVENT

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 = EVENT.DBID

AND SNAP.INSTANCE_NUMBER =

EVENT.INSTANCE_NUMBER

AND SNAP.SNAP_ID =

EVENT.SNAP_ID

AND UPPER (EVENT.WAIT_CLASS) <>

'IDLE'

ORDER BY SNAP.SNAP_ID))

WHERE START_TIME IS NOT NULL

AND END_TIME IS NOT NULL

GROUP BY ROLLUP (EVENT_NAME, END_TIME))

WHERE NOT (G1 = 1 AND G2 = 1)) A

WHERE A.EVENT_NAME NOT IN (SELECT EVENT_NAME

FROM (SELECT EVENT_NAME, RNK

FROM (SELECT EVENT_NAME,

RANK ()

OVER (

ORDER BY

VALUE_DIFF DESC,

EVENT_NAME ASC NULLS LAST)

RNK

FROM (SELECT EVENT_NAME,

DECODE (

G1,

1, 'SUB AVG',

SNAP_TIME)

SNAP_TIME,

VALUE,

VALUE_DIFF

FROM ( SELECT EVENT_NAME,

END_TIME

SNAP_TIME,

AVG (

NVL (

VALUE,

0))

VALUE,

AVG (

NVL (

VALUE_DIFF,

0))

VALUE_DIFF,

GROUPING (

END_TIME)

G1,

GROUPING (

EVENT_NAME)

G2

FROM (SELECT EVENT_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

0

ELSE

VALUE_2

- VALUE_1

END)

/ (1000000

* (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

0

ELSE

VALUE_2

- VALUE_1

END)

/ 1000000

VALUE_DIFF,

ROW_NUMBER ()

OVER (

PARTITION BY INSTANCE_NUMBER,

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

EVENT_NAME

ORDER BY

SNAP.SNAP_ID)

SNAP_TIME_C2,

EVENT.EVENT_NAME,

EVENT.TIME_WAITED_MICRO

VALUE_1,

LEAD (

EVENT.TIME_WAITED_MICRO)

OVER (

PARTITION BY DBI.INSTANCE_NUMBER,

EVENT_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_SYSTEM_EVENT EVENT

WHERE DBI.DBID =

SNAP.DBID

AND DBI.INSTANCE_NUMBER =

SNAP.INSTANCE_NUMBER

AND SNAP.SNAP_ID >=

:15

AND SNAP.SNAP_ID <=

:16

AND SNAP.DBID =

EVENT.DBID

AND SNAP.INSTANCE_NUMBER =

EVENT.INSTANCE_NUMBER

AND SNAP.SNAP_ID =

EVENT.SNAP_ID

AND UPPER (

EVENT.WAIT_CLASS) <>

'IDLE'

ORDER BY SNAP.SNAP_ID))

WHERE START_TIME

IS NOT NULL

AND END_TIME

IS NOT NULL

GROUP BY ROLLUP (EVENT_NAME,

END_TIME))

WHERE NOT (G1 =

1

AND G2 =

1))

WHERE SNAP_TIME =

'SUB AVG')

WHERE RNK <= 10)) B)

GROUP BY A.SNAP_TIME, A.SNAP_TIME

ORDER BY RNK, SNAP_TIME)

GROUP BY SNAP_TIME

ORDER BY SNAP_TIME;

저작자표시 동일조건

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

오라클 SQL TRACE 추출 스크립트 (0)	2019.01.13
Oracle DB 기본 점검 스크립트 (0)	2019.01.10
Oracle RMAN FULL BACKUP 스크립트 (0)	2019.01.09
Oracle SQL Stat 조회 스크립트 (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
Oracle 테이블 사이즈 및 테이블별 카운트 출력 스크립트 (0)	2019.01.07

운명을바꾸는자

IT와 함께 살아가는 삶

,

Oracle DB AWR 활용하여 IO 성능 조회 스크립트

1. IT Story/Scripts 2019. 1. 7. 21:08

Oracle DB AWR 활용하여 IO 성능 조회 스크립트

SELECT SNAP_TIME, SNAP_TIME_RANGE,

TO_CHAR (SUM (DECODE (STAT_NAME, 'session logical reads', VALUE)),

'FM999,999,999,999.9')

"Session Logical Reads/Sec",

TO_CHAR (SUM (DECODE (STAT_NAME, 'physical reads', VALUE)),

'FM999,999,999.9')

"Physical Reads/Sec",

TO_CHAR (

SUM (DECODE (STAT_NAME, 'physical read total IO requests', VALUE))

+ SUM (

DECODE (STAT_NAME, 'physical write total IO requests', VALUE)),

'FM999,999,999.9')

"IOPS",

TO_CHAR (

(SUM (DECODE (STAT_NAME, 'physical read total bytes', VALUE))

+ SUM (DECODE (STAT_NAME, 'physical write total bytes', VALUE)))

/ 1024

/ 1024,

'FM999,999,999.99')

"MBPS",

TO_CHAR (

GREATEST (

LEAST (

(1

- (SUM (DECODE (STAT_NAME, 'physical reads', VALUE))

- SUM (

DECODE (STAT_NAME, 'physical reads direct', VALUE))

- SUM (

DECODE (STAT_NAME,

'physical reads direct (lob)', VALUE)))

/ DECODE (

(SUM (

DECODE (STAT_NAME,

'session logical reads', VALUE))

- SUM (

DECODE (STAT_NAME,

'physical reads direct', VALUE))

- SUM (

DECODE (STAT_NAME,

'physical reads direct (lob)', VALUE))),

0, 1,

(SUM (

DECODE (STAT_NAME,

'session logical reads', VALUE))

- SUM (

DECODE (STAT_NAME,

'physical reads direct', VALUE))

- SUM (

DECODE (STAT_NAME,

'physical reads direct (lob)', VALUE)))))

* 100,

100),

0),

'FM999.99')

"Buffer Cache Hit Ratio(%)",

TO_CHAR (

NVL (SUM (DECODE (STAT_NAME, 'db file sequential read', WAITS)), 0),

'FM999,999,999.99')

"db file sequential r avg.(ms)",

TO_CHAR (

NVL (SUM (DECODE (STAT_NAME, 'db file scattered read', WAITS)), 0),

'FM999,999,999.99')

"db file scattered r avg.(ms)",

TO_CHAR (

NVL (SUM (DECODE (STAT_NAME, 'db file parallel write', WAITS)), 0),

'FM999,999,999.99')

"db file parallel w avg.(ms)",

TO_CHAR (

NVL (SUM (DECODE (STAT_NAME, 'log file parallel write', WAITS)), 0),

'FM999,999,999.99')

"log file parallel w avg.(ms)",

TO_CHAR (NVL (SUM (DECODE (STAT_NAME, 'log file sync', WAITS)), 0),

'FM999,999,999.99')

"log file sync avg.(ms)",

TO_CHAR (

SUM (DECODE (STAT_NAME, 'physical read total IO requests', VALUE)),

'FM999,999,999,999.9')

"Physical R total IO req./Sec",

TO_CHAR (

SUM (DECODE (STAT_NAME, 'physical write total IO requests', VALUE)),

'FM999,999,999,999.9')

"Physical W total IO req./Sec",

TO_CHAR (

SUM (DECODE (STAT_NAME, 'physical read total bytes', VALUE))

/ 1024

/ 1024,

'FM999,999,999,999.9')

"Physical R total MB/Sec",

TO_CHAR (

SUM (DECODE (STAT_NAME, 'physical write total bytes', VALUE))

/ 1024

/ 1024,

'FM999,999,999,999.9')

"Physical W total MB/Sec",

TO_CHAR (SUM (DECODE (STAT_NAME, 'physical reads direct', VALUE)),

'FM999,999,999,999.9')

"Physical Reads Direct/Sec",

TO_CHAR (

SUM (DECODE (STAT_NAME, 'physical reads direct (lob)', VALUE)),

'FM999,999,999,999.9')

"Physical Reads Direct(LOB)/Sec"

FROM (SELECT STAT_NAME,

DECODE (G1,

1, 'SUB AVG',

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

SNAP_TIME,

SNAP_TIME SNAP_TIME_RANGE,

NVL (VALUE, 0) VALUE,

NVL (VALUE_DIFF, 0) VALUE_DIFF,

NVL (WAITS, 0) WAITS,

NVL (WAITS_DIFF, 0) WAITS_DIFF

FROM ( SELECT STAT_NAME,

START_TIME || '-' || END_TIME SNAP_TIME,

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

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

ROUND (AVG (NVL (WAITS, 0)), 3) WAITS,

ROUND (AVG (NVL (WAITS_DIFF, 0)), 3) WAITS_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,

NULL WAITS,

NULL WAITS_DIFF,

SNAP_ID,

INSTANCE_NUMBER

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

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 /*+ 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 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

('session logical reads',

'physical reads',

'physical read total IO requests',

'physical write total IO requests',

'physical read total bytes',

'physical write total bytes',

'physical reads direct',

'physical reads direct (lob)')

ORDER BY SNAP.SNAP_ID)

WHERE SNAP_TIME_C2 <> SNAP_TIME_C1

UNION ALL

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,

ROUND (

DECODE (

SNAP_TIME_C2,

NULL, 0,

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

/ 1000000,

3)

VALUE,

ROUND (

(CASE

WHEN VALUE_2 < VALUE_1 THEN 0

ELSE VALUE_2 - VALUE_1

END)

/ 1000000,

1)

VALUE_DIFF,

ROUND (

DECODE (

SNAP_TIME_C2,

NULL, 0,

(CASE

WHEN WAITS_2 <= WAITS_1 THEN 0

ELSE VALUE_2 - VALUE_1

END)

/ (WAITS_2 - WAITS_1))

/ 1000,

3)

WAITS,

ROUND (

(CASE

WHEN WAITS_2 <= WAITS_1 THEN 0

ELSE WAITS_2 - WAITS_1

END),

1)

WAITS_DIFF,

SNAP_ID,

INSTANCE_NUMBER

FROM ( SELECT SNAP.END_INTERVAL_TIME SNAP_TIME_C1,

LEAD (

SNAP.END_INTERVAL_TIME)

OVER (

PARTITION BY DBI.INSTANCE_NUMBER,

EVENT_NAME

ORDER BY SNAP.SNAP_ID)

SNAP_TIME_C2,

STAT.EVENT_NAME STAT_NAME,

STAT.TIME_WAITED_MICRO VALUE_1,

LEAD (

STAT.TIME_WAITED_MICRO)

OVER (

PARTITION BY DBI.INSTANCE_NUMBER,

EVENT_NAME

ORDER BY SNAP.SNAP_ID)

VALUE_2,

STAT.TOTAL_WAITS WAITS_1,

LEAD (

STAT.TOTAL_WAITS)

OVER (

PARTITION BY DBI.INSTANCE_NUMBER,

EVENT_NAME

ORDER BY SNAP.SNAP_ID)

WAITS_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_SYSTEM_EVENT 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.EVENT_NAME IN

('db file sequential read',

'db file scattered read',

'db file parallel write',

'log file parallel write',

'log file sync')

ORDER BY SNAP.SNAP_ID)

WHERE SNAP_TIME_C2 <> SNAP_TIME_C1

AND WAITS_2 <> WAITS_1)

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 SNAP_TIME, SNAP_TIME_RANGE

ORDER BY SNAP_TIME

저작자표시 동일조건

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

Oracle DB 기본 점검 스크립트 (0)	2019.01.10
Oracle RMAN FULL BACKUP 스크립트 (0)	2019.01.09
Oracle SQL Stat 조회 스크립트 (0)	2019.01.08
Oracle Top 10 Event 추출 스크립트 (0)	2019.01.08
Oracle DB 기본 상태 조회 스크립트 (0)	2019.01.07
Oracle AWR을 활용한 주요 Stats 지표 추출 스크립트 (0)	2019.01.07
Oracle 테이블 사이즈 및 테이블별 카운트 출력 스크립트 (0)	2019.01.07
Oracle Cold(콜드) 백업 스크립트 (0)	2019.01.07

운명을바꾸는자

IT와 함께 살아가는 삶

,

Oracle DB 기본 상태 조회 스크립트

1. IT Story/Scripts 2019. 1. 7. 21:05

Oracle DB 기본 상태 조회 스크립트

SELECT 'MEASURE TIME' FTYPE,

TO_CHAR (SYSDATE, 'YYYY.MM.DD HH24:MI:SS') NAME,

NULL VALUE,

NULL NOTE

FROM DUAL

UNION ALL

SELECT 'CURRENT SCN' FTYPE,

TO_CHAR (CURRENT_SCN, 'FM999,999,999,999,999,999,999') NAME,

NULL,

NULL

FROM GV$DATABASE

WHERE INST_ID = (select INST_ID from GV$DATABASE)

UNION ALL

SELECT 'DB_NAME' FTYPE,

DB_NAME,

NULL VALUE,

NULL VTYPE

FROM DBA_HIST_DATABASE_INSTANCE

WHERE INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)

AND STARTUP_TIME = TO_DATE (20180217091655, 'YYYYMMDDHH24MISS')

UNION ALL

SELECT 'DBID' FTYPE,

TO_CHAR (DBID) NAME,

NULL,

NULL

FROM DBA_HIST_DATABASE_INSTANCE

WHERE INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)

AND STARTUP_TIME = TO_DATE (20180217091655, 'YYYYMMDDHH24MISS')

UNION ALL

SELECT 'INSTANCE NUMBER' FTYPE,

TO_CHAR (INSTANCE_NUMBER) NAME,

NULL,

NULL

FROM DBA_HIST_DATABASE_INSTANCE

WHERE INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)

AND STARTUP_TIME = TO_DATE (20180217091655, 'YYYYMMDDHH24MISS')

UNION ALL

SELECT 'INSTANCE NAME' FTYPE,

INSTANCE_NAME NAME,

NULL,

NULL

FROM DBA_HIST_DATABASE_INSTANCE

WHERE INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)

AND STARTUP_TIME = TO_DATE (20180217091655, 'YYYYMMDDHH24MISS')

UNION ALL

SELECT 'HOST NAME' FTYPE,

HOST_NAME NAME,

NULL,

NULL

FROM DBA_HIST_DATABASE_INSTANCE

WHERE INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)

AND STARTUP_TIME = TO_DATE (20180217091655, 'YYYYMMDDHH24MISS')

UNION ALL

SELECT 'STARTUP TIME' FTYPE,

TO_CHAR (STARTUP_TIME, 'YYYY.MM.DD HH24:MI:SS') NAME,

NULL,

NULL

FROM DBA_HIST_DATABASE_INSTANCE

WHERE INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)

AND STARTUP_TIME = TO_DATE (20180217091655, 'YYYYMMDDHH24MISS')

UNION ALL

SELECT 'VERSION' FTYPE,

VERSION NAME,

NULL,

NULL

FROM DBA_HIST_DATABASE_INSTANCE

WHERE INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)

AND STARTUP_TIME = TO_DATE (20180217091655, 'YYYYMMDDHH24MISS')

UNION ALL

SELECT 'PARAM' FTYPE,

PARAMETER_NAME NAME,

TO_CHAR (VALUE) VALUE,

NULL

FROM DBA_HIST_PARAMETER

WHERE DBID = (select dbid from v$database)

AND INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)

AND SNAP_ID = 873

UNION ALL

SELECT 'SGA' FTYPE,

NAME,

ROUND (VALUE / 1024 / 1024, 1) || ' MB' VALUE,

NULL

FROM DBA_HIST_SGA

WHERE DBID = (select dbid from v$database)

AND INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)

AND SNAP_ID = 873

UNION ALL

SELECT 'PGA' FTYPE,

NAME,

ROUND (VALUE / 1024 / 1024, 1) || ' MB' VALUE,

NULL

FROM DBA_HIST_PGASTAT

WHERE DBID = (select dbid from v$database)

AND INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)

AND SNAP_ID = 873

UNION ALL

SELECT 'CURRENT SGA' FTYPE,

NAME,

ROUND (BYTES / 1024 / 1024, 1) || ' MB' VALUE,

RESIZEABLE VTYPE

FROM GV$SGAINFO

WHERE INST_ID = (select distinct INST_ID from GV$SGAINFO)

UNION ALL

SELECT 'CURRENT PGA' FTYPE,

NAME,

DECODE (

UNIT,

'bytes', TO_CHAR (ROUND (VALUE / 1024 / 1024, 1),

'FM999,999,999,999.9'),

TO_CHAR (VALUE))

VALUE,

DECODE (UNIT, 'bytes', 'MB', UNIT) VTYPE

FROM GV$PGASTAT

WHERE INST_ID = (select distinct INST_ID from GV$PGASTAT)

UNION ALL

SELECT 'CURRENT SIZE : SEGMENT' FTYPE,

TO_CHAR (SUM (BLOCKS), 'FM999,999,999,999') || ' Blocks',

TO_CHAR (ROUND ( (SUM (BYTES) / 1024 / 1024 / 1024), 1),

'FM999,999,999,999')

|| ' GB',

NULL VTYPE

FROM DBA_SEGMENTS

UNION ALL

SELECT 'CURRENT SIZE : DATAFILE' FTYPE,

TO_CHAR (SUM (BLOCKS), 'FM999,999,999,999') || ' Blocks',

TO_CHAR (ROUND ( (SUM (BYTES) / 1024 / 1024 / 1024), 1),

'FM999,999,999,999')

|| ' GB',

NULL VTYPE

FROM DBA_DATA_FILES

ORDER BY 1, 2;

저작자표시 동일조건

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

Oracle DB 기본 점검 스크립트 (0)	2019.01.10
Oracle RMAN FULL BACKUP 스크립트 (0)	2019.01.09
Oracle SQL Stat 조회 스크립트 (0)	2019.01.08
Oracle Top 10 Event 추출 스크립트 (0)	2019.01.08
Oracle DB AWR 활용하여 IO 성능 조회 스크립트 (0)	2019.01.07
Oracle AWR을 활용한 주요 Stats 지표 추출 스크립트 (0)	2019.01.07
Oracle 테이블 사이즈 및 테이블별 카운트 출력 스크립트 (0)	2019.01.07
Oracle Cold(콜드) 백업 스크립트 (0)	2019.01.07

운명을바꾸는자

IT와 함께 살아가는 삶

,

'1. IT Story/Scripts'에 해당되는 글 75건

Oracle Rawdevice구성 RMAN Backup스크립트

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

오라클 SQL TRACE 추출 스크립트

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

Oracle DB 기본 점검 스크립트

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

Oracle RMAN FULL BACKUP 스크립트

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

Oracle SQL Stat 조회 스크립트

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

Oracle Top 10 Event 추출 스크립트

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

Oracle DB AWR 활용하여 IO 성능 조회 스크립트

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

Oracle DB 기본 상태 조회 스크립트

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

공지사항

카테고리

태그목록

글 보관함

달력

링크

운명을바꾸는자

LATEST FROM OUR BLOG

LATEST COMMENTS

티스토리툴바