[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';