database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Tutorials 8913 게시물 읽기
 News | Q&A | Columns | Tutorials | Devel | Files | Links
No. 8913
Oracle Performance Tuning SQL Scripts
작성자
정재익(advance)
작성일
2001-12-14 22:17
조회수
6,541

원본 출처 : 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';

[Top]
No.
제목
작성자
작성일
조회
9002OCP 문제 - Admin 파트 (3)
정재익
2001-12-23
6295
9001OCP 문제 - ADMIN 파트 (1)
정재익
2001-12-23
13646
9000Oracle Library Cache and Dictionary Cache
정재익
2001-12-23
5258
8913Oracle Performance Tuning SQL Scripts
정재익
2001-12-14
6541
8909Oracle System Documentation
정재익
2001-12-14
5316
8875오라클 퍼포먼스 향상방법
정재익
2001-12-13
4681
8869템포러리 테이블 스페이스
정재익
2001-12-13
4732
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2022 DSN, All rights reserved.
작업시간: 0.064초, 이곳 서비스는
	PostgreSQL v13.3으로 자료를 관리합니다