|
원본 출처 : http://bora.dacom.co.kr/~mhan/oracle/tun/sql/index.html
[b][color=005511]SESS.SQL ... Session 상황 조회 [/color][/b]
Doc
Name: SESS.sql
Author: Mahnho Han
List of current active session status
#
TTITLE OFF
CLEAR COLUMN
SET PAGESIZE 60
SET LINESIZE 130
COLUMN SID FORMAT 9999
COLUMN USERNAME FORMAT A12
COLUMN COMMAND FORMAT A20
SELECT PROCESS PID,SE.SID,SERIAL#,STATUS,USERNAME,
DECODE(COMMAND, 0,' ', -- Normal
1,'CREATE TABLE', 2,'INSERT',
3,'SELECT',
5,'ALTER CLUSTER', 6,'UPDATE',
7,'DELETE',
9,'CREATE INDEX',
45,'ROLLBACK',
47,'PL/SQL EXECUTE',
COMMAND) COMMAND,
TERMINAL
FROM V$SESSION SE,V$SESS_IO IO
WHERE STATUS != 'INACTIVE'
AND USERNAME != ' '
AND SE.SID = IO.SID
ORDER BY STATUS;
[b][color=005511]HIT_INS.SQL ... Instance의 hit ratio 조회 [/color][/b]
Doc
Name: HIT_INS.sql
Author: Mahnho Han
Hit Ratio of Instance
100%에 가까울수록 바람직
#
TTITLE 'The Hit Ratio'
SELECT SUM(DECODE(NAME,'consistent gets',VALUE,0)) "CONSIS GETS",
SUM(DECODE(NAME,'db block gets',VALUE,0)) "DB BLK GETS",
SUM(DECODE(NAME,'physical reads',VALUE,0)) "PHYS READS",
(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 "HIT RATIO"
FROM V$SYSSTAT;
[b][color=005511]LIB_HIT.SQL ... Library hit ratio 조회 [/color][/b]
Doc
Name: LIB_HIT.sql
Author: Mahnho Han
Hit Ratio of Library
100%에 가까울수록 바람직
#
TTITLE 'The Library Hit Ratio'
select round(sum(pinhits)/sum(pins) * 100,2)
from v$librarycache;
[b][color=005511]SHAREDPOOL.SQL ... Shared pool의 free buffer 조회 [/color][/b]
Doc
Name: SHAREDPOOL.sql
Author: Mahnho Han
Percentage of free space in the SGA shared pool area
This percentage should not drop below 5%
#
TTITLE 'Percentage of free space in the SGA shared pool area'
select round((sum(decode(name, 'free memory', bytes, 0)) /
sum(bytes)) * 100,2)
from v$sgastat;
[b][color=005511]TS_STATUS.SQL ... Tablespace list와 그 storage 정의 내용 조회 [/color][/b]
Doc
Name: TS_STATUS.sql
Author: Mahnho Han
List of all defined tablespaces
#
SELECT tablespace_name "TABLESPACE",
initial_extent "INITIAL_EXT",
next_extent "NEXT_EXT",
min_extents "MIN_EXT",
max_extents "MAX_EXT",
pct_increase
FROM dba_tablespaces;
[b]출력 [/b]
TABLESPACE INITIAL_EXT NEXT_EXT MIN_EXT MAX_EXT PCT_INCREASE
---------- ----------- -------- ------- ------- ------------
SYSTEM 10240000 10240000 1 99 50
USERS 10240000 10240000 1 99 50
[b][color=005511]TS_DF.SQL ... Tablespace가 어떤 크기의 datafile로 구성되어 있는가? [/color][/b]
Doc
Name: TS_DF.sql
Author: Mahnho Han
List all data files on a tablespace
#
SET VERIFY OFF
SET PAGESIZE 0
COMPUTE SUM of MB
select TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 MB
from DBA_DATA_FILES
where TABLESPACE_NAME = '&TABLESPACENAME'
order by FILE_NAME;
[b]출력 [/b]
TABLESPACE_NAME FILE_NAME MB
--------------- ------------ ----------
TS01 filename1 10
TS01 filename2 8
TS01 filename3 10
----
sum 28
[b][color=005511]TS_FREE.SQL ... Tablespace의 freespace는 얼마나 남아 있는가? [/color][/b]
Doc
Name: TS_FREE.sql
Author: Mahnho Han
Freespace information of all tablespaces
#
SELECT TABLESPACE_NAME, BYTES
FROM DBA_FREE_SPACE
ORDER BY TABLESPACE_NAME, BYTES DESC;
[b][color=005511]BLOCK_ROWS.SQL ... Object의 block당 row수 조회 [/color][/b]
Doc
Name: BLOCK_ROWS.sql
Author: Mahnho Han
The number of rows per block
#
SELECT substr(t.rowid,1,8) || '-' || substr(t.rowid,15,4) BLOCK,
count(*) ROW_NUM
FROM &table_name t
WHERE rownum < 2000
GROUP BY substr(t,rowid,1,8) || '-' || substr(t.rowid,15,4)
/
[b][color=005511]DFIO.SQL ... Datafile별 I/O count는 얼마나 발생하고 있는가? [/color][/b]
Doc
Name: DFIO.sql
Author: Mahnho Han
Display the Datafile I/O rate for the specified tablespace.
#
SET PAGESIZE 60
SELECT FILE_NAME,PHYRDS,PHYWRTS,PHYRDS+PHYWRTS
FROM V$FILESTAT FS,DBA_DATA_FILES DF
WHERE DF.TABLESPACE_NAME = '&TABLESPACE'
AND FS.FILE# = DF.FILE_ID;
[b][color=005511]OBJ_FRAG.SQL ... Extent가 많이 발생한 object list [/color][/b]
Doc
Name: OBJ_FRAG.sql
Author: Mahnho Han
Information what is the objects which is occured extents many times
#
SET LINESIZE 130
SET PAGESIZE 80
COLUMN OWNER FORMAT A16
COLUMN SEGMENT_NAME FORMAT A30
COLUMN TYPE FORMAT A8
COLUMN EXTENTS FORMAT 999
COLUMN TS_NAME FORMAT A10
COLUMN MAX_EXTENTS HEADING 'MAX|EXTENTS' FORMAT 999
COLUMN INI_EXT HEADING 'INITIAL|EXTENT(KB)' FORMAT 99,999,999
COLUMN NXT_EXT HEADING 'NEXT|EXTENT(KB)' FORMAT 99,999,999
COLUMN PCT_INCREASE HEADING 'PCT|INCR' FORMAT 999
SELECT OWNER,SEGMENT_NAME,SEGMENT_TYPE TYPE,EXTENTS,MAX_EXTENTS,
BLOCKS,INITIAL_EXTENT/1024 INI_EXT,
NEXT_EXTENT/1024 NXT_EXT,PCT_INCREASE,TABLESPACE_NAME TS_NAME
FROM DBA_SEGMENTS
WHERE EXTENTS > &EXTENTS
ORDER BY EXTENTS DESC;
[b][color=005511]OBJ_ACCESS.SQL ... Object별 access하고 있는 session 수 [/color][/b]
Doc
Name: OBJ_ACCESS.sql
Author: Mahnho Han
The number of sessions that is using a object
#
SET PAGESIZE 80
SET LINESIZE 130
CLEAR COLUMN
COLUMN OBJNAME FORMAT A40 HEADING "OBJECT NAME"
COLUMN ACNUM FORMAT 999 HEADING "No of Users"
SELECT A.OWNER||'.'||OBJECT OBJNAME, COUNT(SID) ACNUM,
INI_TRANS,MAX_TRANS,FREELISTS,PCT_FREE
FROM V$ACCESS A, DBA_TABLES T
WHERE OB_TYP = 2 /* table */
AND A.OWNER = T.OWNER
AND A.OBJECT = T.TABLE_NAME
GROUP BY A.OWNER,OBJECT,INI_TRANS,MAX_TRANS,FREELISTS,PCT_FREE
HAVING COUNT(SID) > 1
ORDER BY COUNT(SID) DESC
/
[b][color=005511]IND_5.SQL ... Index를 5개 이상 가진 table list [/color][/b]
Doc
Name: IND_5.sql
Author: Mahnho Han
Tables that has indexes more than 5
#
COLUMN TABLE_NAME FORMAT A30
SELECT TABLE_OWNER,TABLE_NAME,COUNT(*)
FROM DBA_INDEXES
GROUP BY TABLE_OWNER,TABLE_NAME
HAVING COUNT(*) > 5 or COUNT(*) = 0
ORDER BY TABLE_OWNER,TABLE_NAME;
[b][color=005511]IND_0.SQL ... Index가 전혀 없는 table들 [/color][/b]
Doc
Name : IND_0.sql
Author: Mahnho Han
List all tables that do not have any indexes
#
TTITLE 'Report on all tables without indexes'
select owner,table_name
from dba_tables
MINUS
select owner,table_name
from dba_indexes;
[b][color=005511]ROLL_STAT.SQL ... Rollback segment 가 충분한가? [/color][/b]
Doc
Name: ROLL_STAT.sql
Author: Mahnho Han
If the value is > 1%, add more rollback segment
#
select n.name,round(100*s.waits/s.gets) "%Cont"
from v$rollname n,v$rollstat s
where n.usn = s.usn;
[b][color=005511]LOC_HLD.SQL ... 어떤 object에 어떤 lock이 진행중인가? [/color][/b]
Doc
Name : LOC_HLD.sql
Author: Mahnho Han
List the objects whis is currently locked
#
set linesize 130
select distinct o.object_name, sh.username||'('||sh.sid||')' "Holder",
sw.username||'('||sw.sid||')' "Waiter",
decode(lh.lmode,1,'null',
2,'row share',
3,'row exclusive',
4,'share',
5,'share row exclusive',
6,'exclusive') "Lock Type"
from all_objects o,
v$session sw, v$lock lw,
v$session sh, v$lock lh
where lh.id1 = o.object_id
and lh.id1 = lw.id1
and sh.sid = lh.sid
and sw.sid = lw.sid
and sh.lockwait is null
and sw.lockwait is not null
and lh.type = 'TM'
and lw.type = 'TM';
[b][color=005511]SQL_ACT.SQL ... 현재 running중인 SQL 조회 [/color][/b]
Doc
Name: SQL_ACT.sql
Author: Mahnho Han
Get the SQL that is running this point
#
SPOOL SQL_ACT
SET PAGESIZE 60
SET LINESIZE 80
SET HEADING OFF
COLUMN PROCESS NEW_VALUE PIDVAR NOPRINT
COLUMN SID NEW_VALUE SIDVAR NOPRINT
COLUMN DISK_READ NEW_VALUE DSKVAR NOPRINT
COLUMN SVRNAME NEW_VALUE SVRVAR NOPRINT
TTITLE LEFT 'PID: ' PIDVAR COL20 'SID: ' SIDVAR COL 40
'SVRNAME: ' SVRVARD: ' COL 80 'DISK_READ: ' DSKVAR SKIP 2
BREAK ON SID SKIP PAGE
BTITLE OFF
SELECT SID,SVRNAME,PROCESS,
ROUND(DISK_READS/NVL(SA.EXECUTIONS,0),0) DISK_READ,
SQL_TEXT
FROM V$SQLTEXT ST,
V$SQLAREA SA,
(SELECT SE.PROCESS,SE.SID,SERIAL#,STATUS,USERNAME,
SVRNAME,SQL_HASH_VALUE
FROM V$SESSION SE, SYSTEM.HMH_PS H
WHERE STATUS = 'ACTIVE'
AND USERNAME != ' '
AND SE.PROCESS = H.PROCES(+)) SI
WHERE SI.SQL_HASH_VALUE = ST.HASH_VALUE
AND SI.SQL_HASH_VALUE = SA.HANS_VALUE
ORDER BY SID,PIECE
/
SPOOL OFF
[b][color=005511]SQL_10SEC.SQL ... Response time이 10초 이상인 SQL source [/color][/b]
Doc
Name: SQL_10SEC.sql
Author: Mahnho Han
List all SQL statements that have a response time more than 10 seconds
#
column nl newline;
set heading off
set pagesize 9999
set linesize 80
set echo off
set feedback off
set verify off
column runt format 999,999.99 newline;
spool diskread.sql
select ' ' nl,'doc' nl,translate(sql_text,'&',':') nl,
' Expected Run Time = '||
DISK_Reads / decode(executions,0,1,executions) / 50 runt,
' Executions = ' || executions nl,
'#' nl,
'delete sys.han_table;' nl,
'explain plan into sys.han_table for ' nl,
sql_text ||';' nl,
'@$HOME/mhan/plan '
from v$sqlarea
where disk_reads / decode(executions,0,1,executions) / 50 > 10
and upper(sql_text) not like '%BEGIN%'
and upper(sql_text) not like '%SQLAREA%'
and upper(sql_text) not like '%DBA_%'
and upper(sql_text) not like '%USER_%'
and upper(sql_text) not like '%ALL_%'
order by executions desc
/
spool off
spool bufget.sql
select ' ' nl,
'doc' nl,
translate(sql_text,'&',':') nl,
' Expected Run Time = '||
buffer_gets / decode(executions,0,1,executions) / 500 runt,
' Executions = ' || executions nl,
'#' nl,
'delete sys.han_table;' nl,
'explain plan into sys.han_table for ' nl,
sql_text ||';' nl,
'@$HOME/mhan/plan '
from v$sqlarea
where buffer_gets / decode(executions,0,1,executions) / 500 > 10
and upper(sql_text) not like '%BEGIN%'
and upper(sql_text) not like '%SQLAREA%'
and upper(sql_text) not like '%DBA_%'
and upper(sql_text) not like '%USER_%'
and upper(sql_text) not like '%ALL_%'
order by executions desc
/
spool off
spool badstmt.lis
doc
got an excessive number of disk reads
#
@diskread.sql
doc
got an excessive number of scan through the buffer cache.
#
@bufget.sql
spool off
[b][color=005511]EXPLAIN.SQL ... Explain plan [/color][/b]
Doc
Name: EXPLAIN.sql
Author: Mahnho Han
Display execution plan
#
column operation format A30
column options format A20
column object_name format A28
set pages 80
select lpad(' ',2*(level-1))||operation operation,options,object_name
from plan_table
start with id=0 and statement_id='han'
connect by prior id=parent_id and
statement_id = 'han';
|
