# index 확인
--script로 사용시 주석 없앨 것.
column INDEX_NAME heading "TYPE : INDEX_NAME" format a42
column "UQ" format a2
column column_name format a20
column ordinal heading "ORD" format 99
column descend heading "DESC" format a4
column column_emp format a30
--set verify off
--set echo off
--set feedback off
--set serveroutput on
--set pagesize 999
--set linesize 200
--break on index_name skip 1
--prompt
--accept tname prompt 'TABLE : '
select decode(i.index_type,
'NORMAL','NORMAL',
'BITMAP','BITMAP',
'IOT_TOP','IOT',
'FUNCTION-BASED NORMAL','FBI-NR',
'FUNCTION-BASED BITMAP','FBI-BI',
'DOMAIN','DOMAIN',
'NORMAL/REV','NR/REV',
' ') || ' : ' || i.index_name || decode(partitioned,'YES','(P)') INDEX_NAME
,decode(i.uniqueness,'UNIQUE','Y','N') "UQ"
,column_name, i.column_position ordinal, descend
,e.column_expression column_exp
from (
select i.table_name, c.index_name, i.index_type
, i.uniqueness
, c.column_name
, c.column_position
, descend
, partitioned
, decode(t.constraint_type,'P','Y','N') ispk
, decode(t.constraint_type,'U','Y','N') isuk
from user_ind_columns c, user_indexes i, user_constraints t
where i.index_name = c.index_name
and i.table_name = UPPER('&tab_name')
and t.table_name(+) = UPPER('&tab_name')
and t.constraint_type(+) = 'P'
and t.index_name(+) = i.index_name) i, user_ind_expressions e
where e.table_name(+) = UPPER('&tab_name')
and e.index_name(+) = i.index_name
and e.column_position(+) = i.column_position
order by decode(i.ispk,'Y','A','B')
,i.index_name,ordinal
;
# index status
-- ind_stat
set linesize 200
col owner for a10
col ind_name for a20
col tab_name for a20
col tbs for a10
col uni for a10
col status for a10
col con for a10
SELECT
A.OWNER,
A.INDEX_NAME IND_NAME,
A.TABLE_NAME TAB_NAME,
A.TABLESPACE_NAME TBS,
A.UNIQUENESS UNI,
A.PARTITIONED PART,
DECODE(B.CONSTRAINT_TYPE,'P','PK','NON PK') CON, A.STATUS
FROM DBA_INDEXES A, DBA_CONSTRAINTS B
WHERE A.INDEX_NAME=B.INDEX_NAME(+)
AND A.OWNER = UPPER('&owner')
AND A.TABLE_NAME = UPPER('&tab_name');-- ind_part_stat
set linesize 200
col owner for a10
col ind_name for a20
col tbs for a10
col uni for a10
col status for a10
SELECT
A.INDEX_OWNER OWNER,
A.INDEX_NAME IND_NAME,
A.TABLESPACE_NAME TBS,
B.UNIQUENESS UNI,
A.PARTITION_NAME PART_NAME,
A.STATUS,
A.TABLESPACE_NAME TBS
FROM DBA_IND_PARTITIONS A, DBA_INDEXES B
WHERE A.INDEX_NAME=B.INDEX_NAME
AND A.INDEX_NAME IN (SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_OWNER = UPPER('&owner') AND TABLE_NAME = UPPER('&tab_name'))
ORDER BY A.INDEX_OWNER,A.INDEX_NAME,A.PARTITION_POSITION;
# plan 확인
-- script로 사용시 주석 없앨 것
--set verify off
--set echo off
--set feedback off
--set serveroutput on
--set pagesize 999
--set linesize 200
column PLAN_TABLE_OUTPUT format a150
select * from table(dbms_xplan.display(null,null,decode('&1','null','typical','basic','basic')));
-- all_stats 쿼리
select * from table(dbms_xplan.display_cursor(null, null, decode('&1','null','allstats last')));
# TX,TM LOCK 확인
-- lock.sql
set linesize 200
set pagesize 1000
col LOCK_TYPE for a15
col MODE_HELD for a15
col MODE_REQUESTED for a10
col LOCK_ID1 for a10
col LOCK_ID2 for a10
col BLOCKING_OTHERS for a30
select
sid session_id,
decode(type,
'TX', 'Transaction',
'TM', 'DML',
'DX', 'Distributed Xaction',
type) lock_type,
decode(lmode,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(lmode)) mode_held,
decode(request,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(request)) mode_requested,
to_char(id1) lock_id1, to_char(id2) lock_id2,
decode(block,
0, 'Not Blocking', /* Not blocking any other processes */
1, 'Blocking', /* This lock blocks other processes */
2, 'Global', /* This lock is global, so we can't tell */
to_char(block)) blocking_others
from v$lock
where type = 'TX'
or type = 'TM'
order by sid,block desc,request desc,lmode desc,decode(type,'TM',1,'TX',2,3);
# ASM DISK 사용량 및 상태확인
set pagesize 300;
column group_nm a10;
column disk_nm a30;
select (case when r_num = 1 then group_number
when r_num <> 1 then null end) as group_no,
(case when r_num = 1 then g_name
when r_num <> 1 then null end) as group_nm,
(case when r_num = 1 then state
when r_num <> 1 then null end) as state,
(case when r_num = 1 then group_tot_mb
when r_num <> 1 then null end) as group_tot_mb,
(case when r_num = 1 then group_free_mb
when r_num <> 1 then null end) as group_free_mb,
disk_nm,
disk_tot_mb,
disk_free_mb,
mnt_stat
from
(select
a.group_number,
row_number() over(partition by a.group_number order by a.group_number) as r_num,
a.name as g_name,
a.state,
a.total_mb as group_tot_mb,
a.free_mb as group_free_mb,
b.PATH||' ['||b.name||']' as disk_nm,
b.mount_status as mnt_stat,
b.total_mb as disk_tot_mb,
b.free_mb as disk_free_mb
from v$asm_diskgroup a, v$asm_disk b
where a.group_number = b.group_number
order by a.group_number,r_num);
# Hidden parameter 확인
-- hidden.sql
col KSPPSTVL for a10
col KSPPINM for a50
SELECT KSPPINM, KSPPSTVL
FROM X$KSPPI X, X$KSPPCV Y
WHERE X.INDX = Y.INDX
AND X.KSPPINM LIKE '%¶m_name%'
AND SUBSTR(X.KSPPINM, 1, 1) = '_';
# expdp 작업 예상시간 확인 (초단위)
select sid, serial#, sofar, totalwork||' sec' as remain_time
from v$session_longops
where opname='&value' -- job_name 입력
and sofar != totalwork;
# table CF(Clustering Factor) 확인
select i.index_name
, t.blocks tab_blks
, i.num_rows num_rows
, i.clustering_factor cl_factor
, round((i.num_rows-i.clustering_factor)/i.num_rows*100,3) ratio
from all_indexes i, all_tables t
where t.owner = '&user_name'
and t.table_name like '&tab_name%'
and i.table_owner = t.owner
and i.table_name = t.table_name;
# SQL 튜닝 대상 쿼리 TOP N 확인
set linesize 200;
set pagesize 1000;
col sql_id for a15;
col sql_org for a50;
select
nvl(parsing_schema_name,'EXPIRED USER')||chr(10)||chr(13)|| sql_id as sql_id,
replace(translate(sql_fulltext,'0123456789','999999999'),'9','') as sql_org,
optimizer_cost,
parse_calls,
fetches,
executions,
disk_reads,
buffer_gets,
cpu_time/1000000/executions as "CPU_AVG(sec)",
elapsed_time/1000000/executions as "EL_AVG(sec)",
avg_hard_parse_time/1000000 as "H_PARSE_AVG(sec)",
total_sharable_mem as TOT_SHM
from (
select
b.parsing_schema_name,
a.sql_id,
a.sql_fulltext,
a.parse_calls,
a.fetches,
a.executions,
a.disk_reads,
a.buffer_gets,
a.cpu_time,
a.elapsed_time,
a.avg_hard_parse_time,
a.total_sharable_mem,
b.module,
b.optimizer_cost
from v$sqlstats a, v$sqlarea b
where b.sql_id = a.sql_id
and a.parse_calls > 0
and a.executions > 0
and b.parsing_schema_name = nvl(upper('&user_name'),b.parsing_schema_name)
and b.parsing_schema_name not in ('SYS','SYSTEM','SCOTT','DBSNMP','OUTLN','WKPROXY','WMSYS','ORDSYS','ORDPLUGINS','MDSYS','CTXSYS','XDB',
'ANONYMOUS','OWNER','WKSYS','ODM_MTR','ODM','OLAPSYS','HR','OE','PM','SH','QS_ADM','QS','QS_WS',
'QS_ES','QS_OS','QS_CBADM','QS_CB','QS_CS','PERFSTAT','EXFSYS','APPQOSSYS','DIP','ORACLE_OCM')
order by decode(nvl('&order','opt'),'opt',b.optimizer_cost,a.elapsed_time/a.executions) desc
) T
where rownum <= nvl(to_number('&row_cnt'),10);
# Keep Buffer 대상 확인
SELECT owner , table_name ,
index_name , partition_name ,
SUM( blocks ) AS t_blocks
FROM (
SELECT sg.owner ,
decode( SUBSTR( s.ob_type , 1 , 5 ) , 'TABLE' , s.ob_name , 'INDEX' , ( SELECT table_name
FROM dba_indexes
WHERE index_name = s.ob_name ) ) AS table_name ,
decode( SUBSTR( s.ob_type , 1 , 5 ) , 'INDEX' , s.ob_name ) AS index_name , sg.partition_name ,
sg.blocks
FROM (
SELECT DISTINCT object_name AS ob_name ,
object_type AS ob_type FROM v$sql_plan
WHERE ( operation = 'TABLE ACCESS' AND options = 'FULL' )
OR ( operation = 'INDEX'
AND options = 'FULL SCAN' )
OR ( operation = 'INDEX'
AND options = 'FAST FULL SCAN' ) --> 선정 기준[3]
)s
,dba_segments sg
WHERE s.ob_name = sg.segment_name )
GROUP BY owner , table_name ,
index_name ,
partition_name
HAVING SUM( blocks ) > 100000 --> 선정 기준[2]SELECT * FROM DUAL;
# 테이블 크기 확인
select
table_name,
num_rows,
num_rows * avg_row_len,
round((num_rows * avg_row_len/1024/1024),2) "SIZE(Mb)",
round((num_rows * avg_row_len/1024/1024/1024),2) "SIZE(Gb)",
last_analyzed
from user_tables
where table_name=UPPER('&tab_name')
;
# redolog 확인
col member for a55;
select a.group#,b.member,a.bytes/1024/1024 mb, a.sequence# seq, a.archived, a.status
from v$log a, v$logfile b
where a.group#=b.group#
order by a.group#,b.member;
# 통계(statistic) 확인
set verify off
set echo off
set feedback off
set serveroutput on
set pagesize 999
set linesize 200
prompt
accept owner prompt 'OWNER : '
prompt
accept tabname prompt 'TABLE : '
prompt
accept indname prompt 'INDEX : '
select blevel, leaf_blocks, clustering_factor, num_rows, distinct_keys,
avg_leaf_blocks_per_key, avg_data_blocks_per_key,
sample_size, last_analyzed
from dba_indexes
where owner = upper('&owner')
and table_name = upper('&tab_name')
and index_name = upper('&ind_name');
# wait event 확인
-- sys_wait.sql
set lines 300
set pages 1000
set long 20
col username for a15
col program for a25
col event for a45
col SID for 999999
col "OS-Pid" for 999999
col sql_text for a50
select /*+ ordered */ distinct
to_char(sysdate, 'hh24:mi:ss') as ctime,
s.sid SID,
s.username,
p.spid "OS-Pid",
w.seconds_in_wait as "W_time(Sec)",
decode(w.wait_time,0,'Wai-ting', 'Waited') Status,
w.ename event,
s.sql_id
from (
select a.*,
decode(a.event, 'latch free', 'latch free (' ||b.name||')',
'row cache lock', 'row cache lock (' || c.parameter || ')',
'enqueue', 'enqueue ('||chr(bitand(p1, -16777216)/16777215)|| chr(bitand(p1,16711680)/65535)||
':'||decode(bitand(p1,65535), 1, 'N',
2, 'SS',
3, 'SX',
4, 'S',
5, 'SSX',
6, 'X'
)
||')',
a.event
) ename
from v$session_wait a,
v$latchname b, v$rowcache c
where a.p2 = b.latch#(+)
and a.p1 = c.cache#(+)
and c.type(+) = 'PARENT'
and a.event not in (
'rdbms ipc message',
'smon timer',
'pmon timer',
'slave wait',
'pipe get',
'null event',
'SQL*Net message from client',
'SQL*Net message to client',
'PX Idle Wait',
'PX Deq: Execution Msg',
'KXFQ: kxfqdeq - normal deqeue',
'wakeup time manager',
'lock manager wait for remote message',
'single-task message',
'Streams AQ: qmn coordinator idle wait',
'Streams AQ: qmn slave idle wait',
'Streams AQ: waiting for messages in the queue',
'Streams AQ: waiting for time management or cleanup tasks',
'DIAG idle wait','jobq slave wait'
)
) w,
v$session s,
v$process p
where w.sid = s.sid
and s.paddr = p.addr
order by w.ename;
# SGA 메모리(memory) free check
col Memory for a20
select
to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') || chr(10) || 'SGA' as Memory,
round(sum(bytes)/1024/1024,2) as tot_mb,
round(sum(case when name != 'free memory' then bytes end)/1024/1024,2) as used_mb,
round(sum(case when name = 'free memory' then bytes end)/1024/1024,2) as free_mb
from v$sgastat
union all
select POOL,
round(sum(bytes)/1024/1024,2),
round(sum(case when name != 'free memory' then bytes end)/1024/1024,2) as used_mb,
round(sum(case when name = 'free memory' then bytes end)/1024/1024,2) as free_mb
from V$sgastat
group by pool;
# PGA 메모리(memory) check
select
sum(max_allocated)/1024/1024 max_mb,sum(allocated)/1024/1024 alloc_mb,
sum(used)/1024/1024 used_mb
from v$process_memory;
# undo usage per session
-- undo_usg.sql
set linesize 150
col duration for a15
select a.sid,a.serial#,a.xidusn,a.used_ublk,a.used_urec,a.used_undo_mb,
extract(day from diff) || ' ' ||
ltrim(to_char(extract(hour from diff),'00')) || ':' ||
ltrim(to_char(extract(minute from diff),'00')) || ':' ||
ltrim(to_char(extract(second from diff),'00')) as duration
from (
select s.sid,s.serial#,t.xidusn,t.used_ublk,t.used_urec,(t.used_ublk * (select value from v$parameter where name = 'db_block_size'))/1024/1024 as used_undo_mb,(cast(sysdate as timestamp) - cast(to_date(start_time,'MM/DD/YY HH24:MI:SS') as timestamp)) as diff
from gv$session s, gv$transaction t
where t.addr = s.taddr
and t.inst_id = s.inst_id
and s.sid = nvl(to_number('&sid'),s.sid)
) a;
# lock duration check
-- lock_obj.sql
set linesize 200
set serveroutput off;
set echo off;
set feedback off;
set verify off;
col username for a10
col obj for a50
col spid for a10
col status for a10
col lock_mode for a20
select a.sid,
decode(a.lmode,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(a.lmode)) as lock_mode,
(select owner || '.' || object_name from dba_objects where object_id = a.object_id) as obj,
a.oracle_username as username,
(select p.spid from gv$session s, gv$process p where p.inst_id = s.inst_id and p.addr = s.paddr and a.sid = s.sid) as spid,
a.status,
(case when length(to_char(mod(trunc(second/60/60),24))) > 2 then to_char(mod(trunc(second/60/60),24))
else lpad(to_char(mod(trunc(second/60/60),24)),2,'0') end) || ':' ||
lpad(to_char(mod(trunc(second/60),60)),2,'0') || ':' ||
lpad(to_char(mod(second,60)),2,'0') as duration
from ( select
l.sid,l.lmode,o.object_id,o.oracle_username,t.status,t.start_time,trunc((sysdate-to_date(start_time,'MM/DD/YY HH24:MI:SS'))*24*60*60) as second
from gv$lock l, gv$locked_object o, gv$transaction t
where l.inst_id = o.inst_id
and l.sid = o.session_id
and o.inst_id = t.inst_id
and o.xidusn = t.xidusn
and o.xidslot = t.xidslot
and o.xidsqn = t.xidsqn
and l.sid = nvl(to_number('&sid'),l.sid)
) a
order by a.start_time,a.sid,a.lmode desc;
# 세션별 temp 사용률
col tablespace for a15
col sql_id for a15
col username for a10
col osuser for a10
col spid for a10
col status for a10
col sid_serial for a20
set linesize 300
SELECT
A.sid || ',' || A.serial# || ',@' || A.inst_id as sid_serial,
A.username,
A.status,
C.spid,
A.osuser,
A.sql_id ,
B.tablespace,
Round(B.blocks * 8 / 1024 / 1024, 2) USED_GB
FROM gv$session A, gv$sort_usage B, gv$process C
WHERE A.saddr = B.session_addr
AND A.paddr = C.addr
ORDER BY B.tablespace, used_gb DESC, B.segfile#, B.segblk#, B.blocks;
# sequence usg
col owner for a10
col SEQUENCE for a20
col "MAX VALUE" for 9999999999999999999999999999
SELECT
SEQUENCE_OWNER "OWNER",
SEQUENCE_NAME "SEQUENCE",
MIN_VALUE "MIN VALUE",
MAX_VALUE "MAX VALUE",
INCREMENT_BY "INCREASE",
CYCLE_FLAG "CYCLE",
ORDER_FLAG "ORDER",
CACHE_SIZE "CACHE",
LAST_NUMBER "LAST NUMBER",
ROUND((LAST_NUMBER/MAX_VALUE)*100) "USED"
FROM DBA_SEQUENCES
WHERE SEQUENCE_OWNER = NVL(UPPER('&owner'),SEQUENCE_OWNER)
AND SEQUENCE_OWNER NOT IN ('SYS','SYSTEM',
'SCOTT','DBSNMP','OUTLN','WKPROXY','WMSYS','ORDSYS','ORDPLUGINS','MDSYS',
'CTXSYS','XDB','ANONYMOUS','OWNER','WKSYS','ODM_MTR','ODM','OLAPSYS',
'HR','OE','PM','SH','QS_ADM','QS','QS_WS','QS_ES','QS_OS','QS_CBADM',
'QS_CB','QS_CS','PERFSTAT','EXFSYS','APPQOSSYS','DIP','ORACLE_OCM')
ORDER BY 10 DESC;
# tkprof trace file
-- trc_file.sql
col trace_file for a200
set linesize 200
select r.value || '/' || t.instance_name || '_ora_'
|| ltrim(to_char(p.spid)) || '.trc' as trace_file
from v$process p , v$session s, v$parameter r, v$instance t
where p.addr = s.paddr
and r.name = 'user_dump_dest'
and s.sid = (select sid from v$mystat where rownum = 1);# session monitor
-- sess.sql
set linesize 250
set pagesize 1000
col sid_serial for a20
col username for a15
col logon_time for a15
col status for a8
col sql_id for a15
col sql_exec_start for a15
col event for a30
col module for a30
col "MOD/PRO/MCN" for a30
col state for a20
col "L_TYPE(MOD)" for a8
col pq_lvl for a7
select
decode(level,1,decode(p.sid,null,'',decode(p.qcserial#,null,'',' └-')),lpad('└',(level-1)*2,' ')) || lpad (s.sid || ',' || s.serial# || ',@' || s.inst_id,length(s.sid || ',' || s.serial# || ',@' || s.inst_id)+(level-1),'-') as sid_serial,
s.username,
--wait_time,
--state,
decode(p.qcserial#, null, decode(p.sid,null,to_char(null),'PARENT'), 'CHILD' || '.' || to_char(p.server_set)) as pq_lvl,
s.event,
s.status,
s.seconds_in_wait as "WAIT(S)",
s.sql_id,
to_char(s.sql_exec_start,'MM/DD HH24:MI:SS') as sql_exec_start,
(case when bitand(s.p1,-16777216)/16777215 > 32 and bitand(s.p1,16711680)/65535 > 32 then upper(chr(bitand(s.p1,-16777216)/16777215) || chr(bitand(s.p1,16711680)/65535)) end)||
decode(to_char(bitand(s.p1,65535)), 0, ''
, 1, '(Null)'
, 2, '(RS)'
, 3, '(RX)'
, 4, '(S)'
, 5, '(SRX)'
, 6, '(X)') as "L_TYPE(MOD)",
trunc(s.p2/power(2,16)) as undo_seg_num,
bitand(s.p2,to_number('ffff','xxxx')) + 0 as tran_tab_slot,
--p1,
--p2,
s.p3, -- transaction slot wrap sequence
--module || chr(10) || program || chr(10) || machine as "MOD/PRO/MCN",
s.module,
to_char(s.logon_time,'MM/DD HH24:MI:SS') as logon_time
from gv$session s, gv$px_session p
where p.sid(+) = s.sid
and type = 'USER'
start with s.blocking_instance is null
and s.blocking_session is null
connect by prior s.inst_id = s.blocking_instance
and prior s.sid = s.blocking_session
order siblings by order siblings by s.inst_id,(to_char(p.qcsid) || to_char(p.server_group) || to_char(p.qcserial#) || to_char(p.server_set)) nulls first,s.sql_exec_start desc;
# segment stat - ITL slot wait
-- seg_stat.sql
select ts#, obj#, dataobj#, sum(value) as itl_waits
from v$segstat
where statistic_name = 'ITL waits'
group by ts#, obj#, dataobj#
having sum(value) > 0
order by sum(value) desc;
# statspack
-- stpck.sql
set linesize 300
col "Library Pin|Get Hit %" for a21
select
-- con_id,
-- inst_id,
round(100 * (1 - (select sum(count) from v$waitstat) / gets), 2) "Buffer Nowait %", -- buffer busy wait 대기 없이 곧바로 읽기 성공한 비율
round(100 * (1 - rlsr / rent), 2) "Redo Nowait %", -- Redo 로그를 기록할 공간을 요청하지 않고 곧바로 Redo 엔트리를 기록한 비율
round(100 * (1 - (phyr - phyrd - nvl(phyrdl, 0)) / gets),2) "Buffer Hit %",
round(100 * (1 - (select sum(misses)/sum(gets) from v$latch)), 2) "Latch Hit %",
lpad((select to_char(round(100 * sum(pinhits)/sum(pins), 2)) || ' | ' || to_char(round(100 * sum(gethits)/sum(gets), 2)) from v$librarycache),21,' ') "Library Pin|Get Hit %",
round(100 * (1 - hprs/ prse), 2) "Soft Parse %",
round(100 * (1 - prse / exe), 2) "Exec Noparse %", -- Parse Call 없이 곧바로 SQL 수행한 비율
decode(prsela, 0, to_number(null), round(prscpu / prsela * 100, 2)) "Parse CPU to Parse Elaps %", -- 파싱 총 소요시간 중 CPU time이 차지하는 비율
decode(tcpu, 0, to_number(null), round(prscpu / tcpu * 100, 2)) "% Non-Parse CPU", -- SQL 수행하면서 사용한 전체 CPU time 중 파싱 이외의 작업이 차지한 비율
decode((srtm + srtd), 0, to_number(null), round(srtm / (srtd + srtm) * 100, 2)) "In-mem Sort %",
round(100 * chng / gets, 2) "% Blocks changed per Read", -- 읽은 블록 중 갱신이 발생하는 비중
round(100 * urol / (ucom + urol), 2) "rollback per trans %",
round(100 * recr / (recr + ucom), 2) "Recurs Call %",
decode((srtm + srtd), 0, to_number(null), round(srtr / (srtd + srtm) * 100, 2)) "Rows per Sort" -- 소트 수행 시 평균 몇 건씩 처리했는지
from (
select
*
from
(select con_id,inst_id,name,value from gv$sysstat where name in ('redo log space requests',
'redo entries',
'physical reads',
'physical reads direct',
'physical reads direct (lob)',
'session logical reads',
'parse count (hard)',
'parse count (total)',
'execute count',
'parse time elapsed',
'parse time cpu',
'CPU used by this session',
'sorts (memory)',
'sorts (disk)',
'sorts (rows)',
'db block changes',
'user calls',
'user rollbacks',
'recursive calls')
)
pivot(min(value) for name in('redo log space requests' as rlsr,
'redo entries' as rent,
'physical reads' as phyr,
'physical reads direct' as phyrd,
'physical reads direct (lob)' as phyrdl,
'session logical reads' as gets,
'parse count (hard)' as hprs,
'parse count (total)' as prse,
'execute count' as exe,
'parse time elapsed' as prsela,
'parse time cpu' as prscpu,
'CPU used by this session' as tcpu,
'sorts (memory)' as srtm,
'sorts (disk)' as srtd,
'sorts (rows)' as srtr,
'db block changes' as chng,
'user calls' as ucom,
'user rollbacks' as urol,
'recursive calls' as recr))
);-- stpck_unpivot.sql
col stats_name for a26
set pagesize 1000;
select *
from (
select
con_id,
inst_id,
round(100 * (1 - (select sum(count) from v$waitstat) / gets), 2) "Buffer Nowait %", -- buffer busy wait 대기 없이 곧바로 읽기 성공한 비율
round(100 * (1 - rlsr / rent), 2) "Redo Nowait %", -- Redo 로그를 기록할 공간을 요청하지 않고 곧바로 Redo 엔트리를 기록한 비율
round(100 * (1 - (phyr - phyrd - nvl(phyrdl, 0)) / gets),2) "Buffer Hit %",
round(100 * (1 - (select sum(misses)/sum(gets) from v$latch)), 2) "Latch Hit %",
(select round(100 * sum(pinhits)/sum(pins), 2) from v$librarycache) "Library Pin Hit %",
(select round(100 * sum(gethits)/sum(gets), 2) from v$librarycache) "Library Get Hit %",
round(100 * (1 - hprs/ prse), 2) "Soft Parse %",
round(100 * (1 - prse / exe), 2) "Exec Noparse %", -- Parse Call 없이 곧바로 SQL 수행한 비율
decode(prsela, 0, to_number(null), round(prscpu / prsela * 100, 2)) "Parse CPU to Parse Elaps %", -- 파싱 총 소요시간 중 CPU time이 차지하는 비율
decode(tcpu, 0, to_number(null), round(prscpu / tcpu * 100, 2)) "% Non-Parse CPU", -- SQL 수행하면서 사용한 전체 CPU time 중 파싱 이외의 작업이 차지한 비율
decode((srtm + srtd), 0, to_number(null), round(srtm / (srtd + srtm) * 100, 2)) "In-mem Sort %",
round(100 * chng / gets, 2) "% Blocks changed per Read", -- 읽은 블록 중 갱신이 발생하는 비중
round(100 * urol / (ucom + urol), 2) "rollback per trans %",
round(100 * recr / (recr + ucom), 2) "Recurs Call %",
decode((srtm + srtd), 0, to_number(null), round(srtr / (srtd + srtm) * 100, 2)) "Rows per Sort" -- 소트 수행 시 평균 몇 건씩 처리했는지
from (
select
*
from
(select con_id,inst_id,name,value from gv$sysstat where name in ('redo log space requests',
'redo entries',
'physical reads',
'physical reads direct',
'physical reads direct (lob)',
'session logical reads',
'parse count (hard)',
'parse count (total)',
'execute count',
'parse time elapsed',
'parse time cpu',
'CPU used by this session',
'sorts (memory)',
'sorts (disk)',
'sorts (rows)',
'db block changes',
'user calls',
'user rollbacks',
'recursive calls')
)
pivot(min(value) for name in('redo log space requests' as rlsr,
'redo entries' as rent,
'physical reads' as phyr,
'physical reads direct' as phyrd,
'physical reads direct (lob)' as phyrdl,
'session logical reads' as gets,
'parse count (hard)' as hprs,
'parse count (total)' as prse,
'execute count' as exe,
'parse time elapsed' as prsela,
'parse time cpu' as prscpu,
'CPU used by this session' as tcpu,
'sorts (memory)' as srtm,
'sorts (disk)' as srtd,
'sorts (rows)' as srtr,
'db block changes' as chng,
'user calls' as ucom,
'user rollbacks' as urol,
'recursive calls' as recr))
)
)
unpivot("Percent(%)" for stats_name in (
"Buffer Nowait %",
"Redo Nowait %",
"Buffer Hit %",
"Latch Hit %",
"Library Pin Hit %",
"Library Get Hit %",
"Soft Parse %",
"Exec Noparse %",
"Parse CPU to Parse Elaps %",
"% Non-Parse CPU",
"In-mem Sort %",
"% Blocks changed per Read",
"rollback per trans %",
"Recurs Call %",
"Rows per Sort")
);-- stpck_hist.sql
select
-- con_id,
-- instance_number,
round(100 * (1 - (select sum(count) from v$waitstat) / gets), 2) "Buffer Nowait %", -- buffer busy wait 대기 없이 곧바로 읽기 성공한 비율
round(100 * (1 - rlsr / rent), 2) "Redo Nowait %", -- Redo 로그를 기록할 공간을 요청하지 않고 곧바로 Redo 엔트리를 기록한 비율
round(100 * (1 - (phyr - phyrd - nvl(phyrdl, 0)) / gets),2) "Buffer Hit %",
round(100 * (1 - (select sum(misses)/sum(gets) from v$latch)), 2) "Latch Hit %",
(select round(100 * sum(pinhits)/sum(pins), 2) from v$librarycache) "Library Pin Hit %",
(select round(100 * sum(gethits)/sum(gets), 2) from v$librarycache) "Library Get Hit %",
round(100 * (1 - hprs/ prse), 2) "Soft Parse %",
round(100 * (1 - prse / exe), 2) "Exec Noparse %", -- Parse Call 없이 곧바로 SQL 수행한 비율
decode(prsela, 0, to_number(null), round(prscpu / prsela * 100, 2)) "Parse CPU to Parse Elaps %", -- 파싱 총 소요시간 중 CPU time이 차지하는 비율
decode(tcpu, 0, to_number(null), round(prscpu / tcpu * 100, 2)) "% Non-Parse CPU", -- SQL 수행하면서 사용한 전체 CPU time 중 파싱 이외의 작업이 차지한 비율
decode((srtm + srtd), 0, to_number(null), round(srtm / (srtd + srtm) * 100, 2)) "In-mem Sort %",
round(100 * chng / gets, 2) "% Blocks changed per Read", -- 읽은 블록 중 갱신이 발생하는 비중
round(100 * urol / (ucom + urol), 2) "rollback per trans %",
round(100 * recr / (recr + ucom), 2) "Recurs Call %",
decode((srtm + srtd), 0, to_number(null), round(srtr / (srtd + srtm) * 100, 2)) "Rows per Sort" -- 소트 수행 시 평균 몇 건씩 처리했는지
from (
select * from (
select
a.con_id,
a.instance_number,
a.stat_name as name,
sum(a.value) as value
from dba_hist_sysstat a, dba_hist_sysstat b, dba_hist_snapshot s
where s.instance_number = nvl(to_number('&inst_id'),s.instance_number)
and s.snap_id between nvl(to_number('&begin_snap'),0) and nvl(to_number('&end_snap'),(select max(snap_id) from dba_hist_snapshot))
and b.stat_id = a.stat_id
and b.snap_id = s.snap_id
and a.snap_id = b.snap_id - 1
and a.instance_number = s.instance_number
and b.instance_number = s.instance_number
and a.stat_name in ('redo log space requests',
'redo entries',
'physical reads',
'physical reads direct',
'physical reads direct (lob)',
'session logical reads',
'parse count (hard)',
'parse count (total)',
'execute count',
'parse time elapsed',
'parse time cpu',
'CPU used by this session',
'sorts (memory)',
'sorts (disk)',
'sorts (rows)',
'db block changes',
'user calls',
'user rollbacks',
'recursive calls')
group by a.con_id,a.instance_number,a.stat_name
)
pivot(min(value) for name in('redo log space requests' as rlsr,
'redo entries' as rent,
'physical reads' as phyr,
'physical reads direct' as phyrd,
'physical reads direct (lob)' as phyrdl,
'session logical reads' as gets,
'parse count (hard)' as hprs,
'parse count (total)' as prse,
'execute count' as exe,
'parse time elapsed' as prsela,
'parse time cpu' as prscpu,
'CPU used by this session' as tcpu,
'sorts (memory)' as srtm,
'sorts (disk)' as srtd,
'sorts (rows)' as srtr,
'db block changes' as chng,
'user calls' as ucom,
'user rollbacks' as urol,
'recursive calls' as recr)
)
);-- stpck_hist_unpivot.sql
col stats_name for a26
set pagesize 1000;
select * from (
select
con_id,
instance_number,
round(100 * (1 - (select sum(count) from v$waitstat) / gets), 2) "Buffer Nowait %", -- buffer busy wait 대기 없이 곧바로 읽기 성공한 비율
round(100 * (1 - rlsr / rent), 2) "Redo Nowait %", -- Redo 로그를 기록할 공간을 요청하지 않고 곧바로 Redo 엔트리를 기록한 비율
round(100 * (1 - (phyr - phyrd - nvl(phyrdl, 0)) / gets),2) "Buffer Hit %",
round(100 * (1 - (select sum(misses)/sum(gets) from v$latch)), 2) "Latch Hit %",
(select round(100 * sum(pinhits)/sum(pins), 2) from v$librarycache) "Library Pin Hit %",
(select round(100 * sum(gethits)/sum(gets), 2) from v$librarycache) "Library Get Hit %",
round(100 * (1 - hprs/ prse), 2) "Soft Parse %",
round(100 * (1 - prse / exe), 2) "Exec Noparse %", -- Parse Call 없이 곧바로 SQL 수행한 비율
decode(prsela, 0, to_number(null), round(prscpu / prsela * 100, 2)) "Parse CPU to Parse Elaps %", -- 파싱 총 소요시간 중 CPU time이 차지하는 비율
decode(tcpu, 0, to_number(null), round(prscpu / tcpu * 100, 2)) "% Non-Parse CPU", -- SQL 수행하면서 사용한 전체 CPU time 중 파싱 이외의 작업이 차지한 비율
decode((srtm + srtd), 0, to_number(null), round(srtm / (srtd + srtm) * 100, 2)) "In-mem Sort %",
round(100 * chng / gets, 2) "% Blocks changed per Read", -- 읽은 블록 중 갱신이 발생하는 비중
round(100 * urol / (ucom + urol), 2) "rollback per trans %",
round(100 * recr / (recr + ucom), 2) "Recurs Call %",
decode((srtm + srtd), 0, to_number(null), round(srtr / (srtd + srtm) * 100, 2)) "Rows per Sort" -- 소트 수행 시 평균 몇 건씩 처리했는지
from (
select * from (
select
a.con_id,
a.instance_number,
a.stat_name as name,
sum(a.value) as value
from dba_hist_sysstat a, dba_hist_sysstat b, dba_hist_snapshot s
where s.instance_number = nvl(to_number('&inst_id'),s.instance_number)
and s.snap_id between nvl(to_number('&begin_snap'),0) and nvl(to_number('&end_snap'),(select max(snap_id) from dba_hist_snapshot))
and b.stat_id = a.stat_id
and b.snap_id = s.snap_id
and a.snap_id = b.snap_id - 1
and a.instance_number = s.instance_number
and b.instance_number = s.instance_number
and a.stat_name in ('redo log space requests',
'redo entries',
'physical reads',
'physical reads direct',
'physical reads direct (lob)',
'session logical reads',
'parse count (hard)',
'parse count (total)',
'execute count',
'parse time elapsed',
'parse time cpu',
'CPU used by this session',
'sorts (memory)',
'sorts (disk)',
'sorts (rows)',
'db block changes',
'user calls',
'user rollbacks',
'recursive calls')
group by a.con_id,a.instance_number,a.stat_name
)
pivot(min(value) for name in('redo log space requests' as rlsr,
'redo entries' as rent,
'physical reads' as phyr,
'physical reads direct' as phyrd,
'physical reads direct (lob)' as phyrdl,
'session logical reads' as gets,
'parse count (hard)' as hprs,
'parse count (total)' as prse,
'execute count' as exe,
'parse time elapsed' as prsela,
'parse time cpu' as prscpu,
'CPU used by this session' as tcpu,
'sorts (memory)' as srtm,
'sorts (disk)' as srtd,
'sorts (rows)' as srtr,
'db block changes' as chng,
'user calls' as ucom,
'user rollbacks' as urol,
'recursive calls' as recr)
)
)
)
unpivot("Percent(%)" for stats_name in (
"Buffer Nowait %",
"Redo Nowait %",
"Buffer Hit %",
"Latch Hit %",
"Library Pin Hit %",
"Library Get Hit %",
"Soft Parse %",
"Exec Noparse %",
"Parse CPU to Parse Elaps %",
"% Non-Parse CPU",
"In-mem Sort %",
"% Blocks changed per Read",
"rollback per trans %",
"Recurs Call %",
"Rows per Sort")
);-- snap_id.sql
col begin_interval for a30
prompt
accept snap_date prompt 'date : '
select snap_id,begin_interval_time
from dba_hist_snapshot
where decode('&snap_date','',sysdate,to_date('&snap_date'||nvl(lpad('&hour',2,'0'),'00'),'YYYYMMDDHH24')) <= begin_interval_time
order by snap_id desc;
# sql tunning point
-- sql_tune.sql
col PARSING_SCHEMA_NAME for a20
set linesize 300
select
parsing_schema_name,
count(*) sql_cnt,
count(distinct substr(sql_text,1,100)) sql_cnt2,
sum(executions) executions,
round(avg(buffer_gets/executions)) buffer_gets,
round(avg(disk_reads/executions)) disk_reads,
round(avg(rows_processed/executions)) rows_processed,
round(avg(elapsed_time/executions/1000000),2) "ELAPSED_TIME(AVG)",
count(case when elapsed_time/executions/1000000 >= 10 then 1 end) "BAD SQL",
round(max(elapsed_time/executions/1000000),2) "ELAPSED_TIME(MAX)"
from v$sql
where 1=1
and parsing_schema_name not in ('SYS','SYSTEM','SCOTT','DBSNMP','OUTLN','WKPROXY','WMSYS','ORDSYS','ORDPLUGINS','MDSYS','CTXSYS','XDB',
'ANONYMOUS','OWNER','WKSYS','ODM_MTR','ODM','OLAPSYS','HR','OE','PM','SH','QS_ADM','QS','QS_WS',
'QS_ES','QS_OS','QS_CBADM','QS_CB','QS_CS','PERFSTAT','EXFSYS','APPQOSSYS','DIP','ORACLE_OCM')
and last_active_time >= trunc(sysdate-1)
and executions > 0
group by parsing_schema_name;
# log switch
--redo_sw.sql
COL CDATE FOR A16
COL DAY FOR A5
COL TOTAL FOR 9,999
select *
from (
select
nvl(decode(no,'1',null,'2',to_char(first_time,'YYYY-MM-DD (Dy)'),cdate),'TOTAL') as cdate,
decode(no,'3','TOTAL',to_char(first_time,'HH24')) hh,
decode(no,'3',total,1) as num
from (
select
no,
decode(b.no,'3',to_char(first_time,'YYYY-MM-DD (Dy)')) as cdate,
decode(b.no,'3',to_date(null),first_time) as first_time,
count(decode(b.no,'3','1')) as total
from (
select
first_time
from v$log_history
where first_time >= to_date(to_char(add_months(sysdate,-1),'YYYYMM')||'01','YYYYMMDD')
and first_time < to_date(to_char(sysdate + 1,'YYYYMM')||'01','YYYYMMDD') ) a,
(select to_char(rownum) no from dual connect by level <= 3) b
group by no,decode(b.no,'3',to_char(first_time,'YYYY-MM-DD (Dy)')),decode(b.no,'3',to_date(null),first_time)
)
)
pivot (sum(num) for hh in ('TOTAL' as "TOTAL",
'00' as "00",
'01' as "01",
'02' as "02",
'03' as "03",
'04' as "04",
'05' as "05",
'06' as "06",
'07' as "07",
'08' as "08",
'09' as "09",
'10' as "10",
'11' as "11")
)
order by 1 desc;
select *
from (
select
nvl(decode(no,'1',null,'2',to_char(first_time,'YYYY-MM-DD (Dy)'),cdate),'TOTAL') as cdate,
decode(no,'3','TOTAL',to_char(first_time,'HH24')) hh,
decode(no,'3',total,1) as num
from (
select
no,
decode(b.no,'3',to_char(first_time,'YYYY-MM-DD (Dy)')) as cdate,
decode(b.no,'3',to_date(null),first_time) as first_time,
count(decode(b.no,'3','1')) as total
from (
select
first_time
from v$log_history
where first_time >= to_date(to_char(add_months(sysdate,-1),'YYYYMM')||'01','YYYYMMDD')
and first_time < to_date(to_char(sysdate + 1,'YYYYMM')||'01','YYYYMMDD') ) a,
(select to_char(rownum) no from dual connect by level <= 3) b
group by no,decode(b.no,'3',to_char(first_time,'YYYY-MM-DD (Dy)')),decode(b.no,'3',to_date(null),first_time)
)
)
pivot (sum(num) for hh in ('TOTAL' as "TOTAL",
'12' as "12",
'13' as "13",
'14' as "14",
'15' as "15",
'16' as "16",
'17' as "17",
'18' as "18",
'19' as "19",
'20' as "20",
'21' as "21",
'22' as "22",
'23' as "23")
)
order by 1 desc;
# sga stat
-- sga.sql
col pool for a20
col name for a19
set linesize 300
select
pool,
name,
sum_bytes/1024 as kb
from (
select
b.no,
decode(b.no,1,pool,2,pool || ' (total)',3,'SGA (total)') as pool,
decode(b.no,1,
decode(name,
'buffer_cache','buffer_cache',
'SQLA','SQL Area',
'SO private sga','SO private sga(19c)',
'row cache mutex','row cache mutex',
'log_buffer','log_buffer',
'row cache','row cache',
'row cache mutex','row cache mutex',
'row cache hash','row cache hash',
'fixed_sga','fixed_sga',
'shared_io_pool','shared_io_pool',
'free memory','free memory')
) as name,
sum(bytes) as sum_bytes
from v$sgastat a,(select rownum no from dual connect by level <= 3) b
group by b.no,decode(b.no,1,pool,2,pool || ' (total)',3,'SGA (total)'),
decode(b.no,1,
decode(name,
'buffer_cache','buffer_cache',
'SQLA','SQL Area',
'SO private sga','SO private sga(19c)',
'row cache mutex','row cache mutex',
'log_buffer','log_buffer',
'row cache','row cache',
'row cache mutex','row cache mutex',
'row cache hash','row cache hash',
'fixed_sga','fixed_sga',
'shared_io_pool','shared_io_pool',
'free memory','free memory')
)
order by decode(b.no,3,2,1) desc,pool nulls first,decode(name,'free memory','z',name) nulls last,3 desc
) where name is not null or (name is null and instr(pool,'(total)') > 0 and pool <> ' (total)');
# file single multi io
-- file_s_m_io.sql
col name for a100
set linesize 300
set pagesize 1000
select f.file#, f.name,
s.phyrds,s.phyblkrd,s.readtim, -- 전체 읽기 작업 정보
s.singleblkrds,s.singleblkrdtim, -- single block i/o
(s.phyblkrd * s.singleblkrdtim) as multiblkrd, -- multi block i/o 횟수
(s.readtim * s.singleblkrdtim) as multiblkrdtim,
round(s.singleblkrdtim/decode(s.singleblkrds,0,1,s.singleblkrds),3) as singleblk_avgtim, -- single block i/o 평균대기시간(cs)
round(decode((s.phyblkrd - s.singleblkrds),0,to_number(null),(s.readtim - s.singleblkrdtim) / (s.phyblkrd - s.singleblkrds)),3) as multiblk_avgtim -- multi block i/o 평균대기시간(cs)
from v$filestat s, v$datafile f
where s.file# = f.file#;'Oracle' 카테고리의 다른 글
| [Oracle] 12c 메인테넌트 (CDB, PDB) (0) | 2020.07.20 |
|---|---|
| [Oracle] Oracle 12cR2 New Feature (0) | 2020.03.02 |
| [Oracle] RMAN (0) | 2019.11.20 |
| [Oracle] OCR, votedisk 위치 바꾸기 (0) | 2019.11.18 |
| [Oracle] Oracle RAC 11gR2 install (0) | 2019.11.04 |