유용한 오라클 튜닝 scripts

[b][color=BLUE]SGA의 shared pool 의 hitratio를 연산해주는 SQL[/color][/b]

/*
**  Shared_pool의 hit ratio보는 스크립트..                      <박제용>
**
**  이 영역은 SQL 쿼리문이 저장되고, 유저별 사용 영역과, 데이터 딕셔너리등이 저장된다.
**  만일 적게 할당되면 유저의 접속이 많아질수록 throughput에 큰 영향을 준다.
**  hit ratio는 95% 이상을 유지시켜야 한다.
**  
*/
select sum(gets) "Gets", sum(getmisses) "Misses",
       (1-(sum(getmisses) / (sum(gets)+sum(getmisses))))*100
       "HitRate"
from v$rowcache;

[b][color=BLUE]SGA에 큰 영향을 주는 파라미터들의 설정을 표시해주는 SQL[/color][/b]

/*
**  DB의 주요 메모리 사용 조회                     <박제용>
**
**  DB의 주요 메모리 사용을 보여준다. DB가 사용하는 메모리는
**  v7.3의 경우 OS메모리의 2/5 를, v8.x 버젼의 경우 1/2 정도를
**  할당해 주는 것이 좋다.
**  
*/
select name, value
from  v$parameter
where name in('db_block_buffers','db_block_size','shared_pool_size','sort_area_size');

[b][color=BLUE]SGA중에 Block buffer의 현재 사용량과 빈공간을 보고싶을 때[/color][/b]

/*
**  DB_BLOCK_BUFFERS의 현재 사용 현황을 보여줌.            <박제용>
**
**  block_buffer를 튜닝하기 전에 현재의 사용현황을 보여준다.
**  이 데이터를 주기적으로 보관하여 분석한다.
*/
select decode(state, 0, 'FREE', 
                     1, decode(lrba_seq,0,'AVAILABLE','BEING USED'),
                     3, 'BEING USED', state) 
        "BLOCK STATUS", count(*)
from x$bh
group by decode(state,0, 'FREE',
                      1, decode(lrba_seq,0,'AVAILABLE','BEING USED'),
                      3, 'BEING USED', state);

[b][color=BLUE]SGA의 Block buffer의 hitratio를 연산해주는 SQL[/color][/b]

/*
**  DB_BLOCK_BUFFERS의 hit ratio보는 스크립트..                      <박제용>
**
**  이 영역은 유저의 쿼리 내용이 버퍼링 되는 공간으로 크기가 적으면 
**  유저별로 과도한 disk read를 발생시킨다.
**  hit ratio는 90~95% 이상을 유지시켜야 한다.
**  
*/
select 1-(sum(decode(name, 'physical reads', value,0))/
	 (sum(decode(name, 'db block gets', value,0)) +
	 (sum(decode(name, 'consistent gets', value,0))))) * 100
	 "Read Hit Ratio"
from v$sysstat;

[b][color=BLUE]한 유저 스키마의 모든 객체를 Analyze 해주는 SQL[/color][/b]

/*
**  테이블 analyze 스크립트 2..                      <박제용>
**  한 유저에 속한 모든 객체를 analyze한다. 
**
**  사용방법 SQL>@analyze0 [유저ID]   
**                          유저ID는 반드시 대문자로.
*/
exec dbms_utility.analyze_schema('&1','DELETE');
exec dbms_utility.analyze_schema('&1','COMPUTE');

[b][color=BLUE]지정한 테이블을 다시 Analyze 해주는 SQL[/color][/b]

/*
**  테이블 analyze 스크립트..                      <박제용>
**  Query를 파싱하는 Optimizer로 하여금 더욱 정확하고, 빠른 파싱을 유도하기위해 Analyze를 한다.
**
**  사용방법 SQL>@analyze1 [테이블명]   
*/
analyze table &1 delete statistics;
analyze table &1 compute statistics;

[b][color=BLUE]TKPROF 사용방법[/color][/b]

** trace 결과를 파악을 위한 Tkprof 유틸리티 사용

** TKPROF trace_file output_file [옵션설정] [explain=username/password]

**        trace_file	        SQL_TRACE로 생성한 트레이스 *.trc 트레이스 파일.
**        output_file	        결과가 저장될 파일명
**        SORT=parameters	소팅 파리미터 execpu, ....
**        EXPLAIN=username/password
**        SYS=no/yes          시스템이 사용한 쿼리를 볼때는 yes로 설정한다.

예제))))

tkprof ora_12626.trc result.txt explain=scott/tiger sys=no

c:\orawin95\bin\tkprof73.exe 2.TRC ORA%1.TXT explain=TUNING/TUNING sys=no sort=execpu

EDIT ORA%1.PRF

 

원본출처 : http://members.tripod.lycos.co.kr/jelong