오라클자바커뮤니티에서 설립한 오엔제이프로그래밍 실무교육센터
(신입사원채용무료교육, 오라클SQL, 튜닝, 힌트,자바프레임워크, 안드로이드, 아이폰, 닷넷)
8.1.6버전 이상 사용 :
1. SYTEM TABLESPACE의 파일 autoextend on로 만든다.
$sqlplus "/as sysdba "
SQL> startup
SQL> ALTER DATABASE DATAFILE '$HOME/ORADATA/u01/system01.dbf' autoextend on;
2. DB 기동 후 timed_statistics para.를 true로 설정한다.(9i부터는 default가 true)
$ sqlplus "system/manager as sysdba"
SQL> show parameter timed_statistics
( false 이면 alter session set timed_statistics = true; )
3. statspack user가 필요한 table과 segment 저장을 위해 sys user로 tools tablespace 생성
( size 100m 이상, dictionary managed 로 )
SQL> connect / as sysdba
SQL> create tablespace tools
datafile '$HOME/ORADATA/u05/tools01.dbf' size 100m
extent management dictionary;
4. dba_free_space를 통행 tools TS 확인 ( 값을 기록해 두자 )
SQL> select tablespace_name, sum(bytes)
from dba_free_space
where tablespace_name = 'TOOLS'
group by tablespace_name;
5. sys user로 $HOME/LABS 밑에 있는 spcreate.sql를 실행시켜 STATSPACK을 설치하자. 실행 중 질문에 다음과 같이 답하라.
User's Default Tablespace = TOOLS
User's Temporary Tablespace = TEMP
SQL>connect /as sysdba
SQL>@$HOME/LABS/spcreate.sql ==> user가 perfstat로 바뀐다.
6. DBA_FREE_SPACE를 조회하여 4번 실행과 비교하라.
이 차이는 STATSPACK 실행 시 필요한 공간의 크기를 의미한다. 그리고 STATSPACK에 저장하는 정보에 따라 더 많은 공간이 필요하게 된다.
7. STATSPACK을 수동으로 현재의 통계 정보를 수집하기위해서는 아래의 snap.sql을 실행하자. 이 스크립트는 snap_id를 보여 주는 데 이를 기록해 두자.(현재시점)
SQL>@$ORACLE_HOME/IMSI/snap.sql ==> user가 perfstat로 바뀐다.
$cat snap.sql
connect perfstat/perfstat
variable snapshot_id number;
begin :snapshot_id := statspack.snap;
end;
/
print snapshot_id
8. STATSPACK을 1시간마다 자동으로 실행하기 위해서는 $ORACLE_HOME/rdbms/admin 밑에 있는 spauto.sql을 실행하자. 해당 job이 DB에 등록되었는지는 user_jobs를 조회한다.
( 단 먼저 parameter file의 job_queue_processes = 1 로 setting 한후 실행 )
SQL> connect /as sysdba
SQL> shutdown
$ cd dbs
$ vi init<SID>.ora ==> job_queue_processes 수정
$ cd
$sqlplus "/as sysdba"
SQL> startup
또는 DB기동정지 없이 ...
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES =1;(0보다 커야된다.)
처럼 변경해도 된다.
SQL> connect perfstat/perfstat
SQL>@$HOME/LABS/spauto.sql
SQL> select job, next_date, next_sec, last_sec
from user_jobs;
9. 한시간 경과한 후(default : 1시간) stats$snapshot view를 조회하자.
SQL> select snap_id, to_char( startup_time, ' dd Mon "at" HH24:mi:ss') instart_fm,
to_char( snap_time, ' dd Mon YYYY HH24:mi') snapdat, snap_level "level"
from stats$snapshot
order by snap_id;
10. 적어도 두 개 이상의 snapshot이 있다면 $ORACLE_HOME/rdbms/admin/ 밑에 있는 spreport.sql을 실행시켜 report를 생성하자. 이 스크립트는 시작 snap id 와 끝 snap id, 그리고 생성될 파일 이름을 요구하는데 이름은 default 값을 이용하는 게 바람직하다.
SQL>@$HOME/rdbms/admin/spreport.sql
11. 현 directory에 생성된 파일을 확인하자.
$ vi sp_X_Y.lst ( X는 시작 snap id, Y는 끝 snap id )
12. sys로 접속하여 DB 기동 후 발생한 event를 조회한다.
$ sqlplus "/as sysdba"
SQL> select event, total_waits, time_waited
from v$system_event;
13. v$session_wait을 이용하여 resource를 기다린 session이 있는지 확인하자.
SQL> select sid, event, p1text, wait_time, state
from v$session_wait;
14. 자동으로 통계를 수집하던 job을 제거하자. perfstat user로 user_jobs를 조회하여 job number를 확인 후 DBMS_JOB.REMOVE(<job_number>);를 실행한다.
SQL>connect perfstat/perfstat
SQL> select job, log_user
from user_jobs;
SQL> execute DBMS_JOB.REMOVE(<job_number>);
Parameter 설정 : Timed_statistics=true, Job_queue_processes>=1
SQL> ALTER SYSTEM set Timed_statistics=true;
SQL> ALTER SYSTEM set Job_queue_processes =1;
TABLESPACE 공간 : Tablespace(TS)생성 또는 기존 TS사용
SQL> CREATE TABLESPACE TOOLS
DATAFILE ‘$ORACLE_HOME/oradata/tools01.dbf’
SIZE 100m;
SQL> SELECT tablespace_name, sum(bytes)
FROM dba_free_space
WHERE tablespace_name = ‘TOOLS’
GROUP BY tablespace_name;
STATSPACK 설치 : spcreate.sql 실행, Default로 PERFSTAT USER생성
$sqlplus “/as sysdba”
SQL> @$ORACLE_HOME/rdbms/admin/spcreate.sql
User’s Default Tablespace = TOOLS
User’s Temporary Tablespace = TEMP(미리생성 되어있음)
통계 자동 수집 : spauto.sql 실행, Default 1시간 간격 실행
$sqlplus perfstat/perfstat
SQL> @$ORACLE_HOME/rdbms/admin/spauto.sql
* 한번 실행시 150K~200K 공간
Report작성 : spreport.sql 실행, (시작 snap id, 끝 snap id, 보고서이름 지정)
$sqlplus perfstat/perfstat
SQL> @$ORACLE_HOME/rdbms/admin/spreport.sql
* 시작 snap id와 끝 snap id 입력
* Report명: default로 sp_시작id_끝id
SQL 상세분석 : sprepsql.sql 실행, (구간, hash_value) --> 해당 SQL에 대한 실행계획과 실행시 소모되는 resource 상세 파악
$sqlplus perfstat/perfstat
SQL> @$ORACLE_HOME/rdbms/admin/sprepsql.sql
* 구간
* hash_value
통계 수집 JOB 제거 : DBMS_JOB.REMOVE 실행
$sqlplus perfstat/perfstat
SQL> SELECT job, job_user
FROM user_jobs;
SQL> execute DBMS_JOB.REMOVE(<job_number>);
통계수집 JOB 정보확인
$sqlplus perfstat/perfstat
SQL> SELECT job, next_date, next_sec, last_sec
FROM user_jobs;
통계수집된 Snap id 확인
$sqlplus perfstat/perfstat
SQL>select snap_id, to_char(startup_time, ' dd Mon "at" HH24:mi:ss') instart_fm,
to_char( snap_time, ' dd Mon YYYY HH24:mi') snapdat, snap_level "level“
from stats$snapshot
order by snap_id;
STATSPACK 제거
$sqlplus “/as sysdba”
SQL>@$ORACLE_HOME/rdbms/admin/spdrop
※ 참고 sql : $ORACLE_HOME/rdbms/admin/에 위치
- sprepsql.sql : snap_id 구간, hash_value를 통한 과도한 i/o가 발생한 object 찾기(9i부터 적용)
- sptrunc.sql : 통계수집된 data 제거
- sppurge.sql : 구간별 통계수집 data 제거
- spdrop.sql : perfstat user와 object 모두 제거
※ 작업제어(sqlplus perfstat/perfstat)
execute dbms_job.run(job); 중단된 작업 수행
execute dbms_job.broken(job); 수행중인 작업 중지
execute dbms_job.remove(job); 수행중인 작업 제거 --> 스태츠팩 자체제거는 아님(스태츠팩
스냅 사용가능)
===> 즉, exec statspack.snap(i_snap_level=>7);(한번에 하나의 snap_id 생성) --> 여기서 level 7은 segment의 경합정보 제공함.
alter system set job_queue_processes = 0; --> 스태츠팩 작업 중지(default 16번 시도후 중단)
※ STATSPACK LEVEL 변경 :(default : 5)
* snapshot을 받아내변서, 새로운 값을 데이터베이스에 저장
(statspack.snap 호출 시, i_modify_parameter 변수 값을 지정)
SQL> execute statspack.snap(i_snap_level=>10,
i_modify_parameter= >'true');
i_modify_parameter 를 true로 지정하면, 이 값이
stats$statspack_parameter 테이블에 저장되며, 이 값이 이후 실행
되는 snapshot에 사용된다.
i_modify_parameter 값을 false로 지정하거나 생략하면, 해당
snapshot 작업에만 지정된 정보가 사용되며, 이후 작업에는
stats$statspack_parameter 에 지정된 값을 사용하여 작업이 수행
된다.
* snapshot 수행을 하지 않고 기본값을 바꾸기 위해서는
statspack.modify_statspack_parameter 프로시져를 사용한다.
예를 들어 snapshot level을 10으로 변경하고, SQL 관련 임계치
가운데 buffer_gets, disk_reads 값을 변경하고자 할 때 다음과
같이 한다.
SQL> execute statspack.modify_statspack_parameter
(i_snap_level=>10, i_buffer_gets_th=>10000,
i_disk_reads_th=>1000);
이 프로시져를 수행시키면, snapshot 수행 없이, 변경 사항이
계속해서 남게 된다.
댓글 없음:
댓글 쓰기