▶ 오라클 트러블 슈팅이 어려운 이유:
-필수적인 툴에 대한 사용법이 체계적으로 정립되어 있지 않아서임
-그래서, 툴들과 성능문제가 어떻게 연결되어 있는지 파악할 것!
🟨 오라클 트러블 슈팅에 필요한 기본 개념과 툴 🟨
- 스냅샷과 프로파일링
- SQL*PLUS 스크립팅
- 딕셔너리뷰
- 진단이벤트
- 덤프
- PL/SQL패키지
- 자바 저장 프로시저
- 정규식
- oradebug
1. 스냅샷과 프로파일링
트러블 슈팅하는 과정은 데이터를 체계적으로 수집하고 수집한 데이터에 기반해서 문제를 해석하고 해결책을 찾는 일련의 과정이다!
- 필요한 데이터를 수집하는 방법 스냅샷데이터와 프로파일링
- 스냅샷 데이터: 특정 시점의 상태 데이터(공간적개념)
- Delta(차이) 값구하는것이 목표> 특정시점A와B의 어떤차이가 있는지가 핵심
- ex)logical read가 얼마나 증가?
- 프로파일링 데이터: 특정 구간에서 수행된 작업에 대한 데이터(시간적 개념)-진단이벤트, 매뉴얼 프로파일링
- -집계를 수행하는것이 목표(TKROF리포트)
- 스냅샷 데이터: 특정 시점의 상태 데이터(공간적개념)
🟩 스냅샷 데이터
1. 기본: 시점 A와 B의 스냅샷을 만들고 B-A 차이 구하기
- V$SYSTAT뷰에 대한 스냅샷을 만들기
| 💡 성능 분석에 이용되는 뷰 v$sysstat : 오라클에서 인스턴스 구동후 현재까지의 "누적 통계치"를 확인할 때 사용할 수 있는 뷰 v$sesstat : 수행하는 세션별로 통계치를 확인 하는 뷰 v$mystat : 현재 접속해 있는 자기 세션에 대한 수행통계 v$system_event : 이벤트에 대한 대기시간 정보를 포함하고 있는 뷰. 이벤트 발생시의 정보을 볼 수 있다. |
2. AWR스냅샷 과 리포트
- AWR이란, 자동으로 DB에 대한 통계 및 성능자료 등을 수집해 스냅샷으로 만들어 일정기간 보관하고, 이를 활용할 수 있게 해주는 기능
-성능자료 > Buffer/CPU/Pin/Latch/Library 등의 히트율, 자원 사용률, Soft/Hard Parse 정도, 가장 느리게 돌았던 쿼리 등
- AWR 스냅샷은 1시간마다 자동으로 생성 ,BUT DBMS_WORKLOAD_REPOSISTORY패키지로 수동으로 생성할수 있음
- A와 B시점의 AWR스냅샷 리포트를 생성하고 B-A 차이 얻기-어떤 뷰들을 스냅샷으로 구할지 생각해야함
select dbms_workload_repositroy.create_snapshot as begin_snap from dual;
select count(*) from all_objects;
select dbms_workload_repositroy.create_snapshot as begin_snap from dual;
--2개의 스냅샷 차이 리포팅
select * from table (dbms_workload_repostitory.awr_report_text(
&db_id,
&inst_num,
&begin_snap,
&end_snap)
3.덤프와 스냅샷
-트러블 슈팅을 할때, 동적뷰로 성능 조회를 하지만 동적뷰만으로 부족한 경우가 있음
- PGA크기가 계속 증가하면서 성능 저하가 될때 V$SESSTAT뷰로 알수 있는것은 PGA크기가 얼마나 증가했는지임, 하지만 왜 증가했는지는 모름
- 이때, PGA힙 덤프 사용
alter session set events 'immediate trace name heapdump levl 1';
>>힙정보가 트레이스 파일에 기록
🟩 프로파일링 데이터
1)간단: 프로파일링 대상이 되는 데이터를 필요한 만큼 반복적으로 읽기 →읽은 데이터를 원본데이터로 이용 →원본데이터를 적절히 집계해서 리포팅
즉,
--세션1에서 all_objects조회
select count(*) from all_objects;
--세션2에서 세션1이 어떤 대기 이벤트를 대기하는지 프로파일링 할것
--우선 세션2에서 세션1의 id값 조회
select sid from v$session where client_info ='session1';
--그리고 1000회에 걸쳐 V$SESISON_WAIT뷰를 반복적으로 읽으면서 대기이벤트 정보 얻기
--대기 이벤트 정보 조회
declare
v_sw v$session_wait%rowtype;
begin
for idx in 1 .. 1000 loop
begin
select * into v_sw
from v$session_wait
where sid = &sid
and state='WAITING';
dbms_output.put_line(
'event =' ||v_sw.event ||
', seq#=' ||v_sw.seq#||
'p1=' ||v_sw.pl ||
'elapsed=' ||
trunc(v_sw.wait_time_micor/1000,2)||'(ms)');
exception when others then
null;
end;
end loop;
end;
/- 반복적으로 엑세스 하면 프로파일링 데이터가 됨
- 대기 이벤8트를 기준으로 hit 회수를 집계
- 어떤 대기 이벤트가 주로 발생했는지 알수 있음
#프로파일링 저장 테이블 생성
create table session_wait
as
select * from v$session_wait
where 1=0
;
#세션1 대기이벤트를 프로파일링하면서 그 결과를 테이블에 저장
begin
for idx in 1 ..1000 loop
insert into session_wait
select *
from v$session_wait
where sid = &sid
and state='WARNING';
dbms_lock.sleep(0.001);
end loop;
end
/
##집계 리포트 생성
select event, count(*) as hits
from session_wait
group by event
order by 2 desc;
###위의 과정을 하나의 쿼리로!
select /*no_query_transformation ordered use_nl(w) */
w.event
count(*) as hits
from (select level from dual connect by level <=50000) x
(select decode(state, 'WAITING', event,'ON CPU') as event
from v$session_wait
where sid=&sid) w
group by w.event
order by 2 desc;
--dual 테이블에서 50000건을 읽는 뷰x가 선행 테이블이 됨
--뷰x를 선행테이블로해서 v$session_wsit를 nested loops join으로 읽음
--위와 같이 읽은 결과를 대기이벤트 별로 집계
2) 진단이벤트와 프로파일링
- 1046 진단 이벤트(= Extended SQL Trace)
: SQL수행의 각 단계별로 시간 정보를 얻는것!
:프로파일링을 수행하는 가장 완벽한 방법
alter session set events '10046 trace context forever, level8';
select count(*) from all_objects;
alter session set events '10046 trace name context off';
: 서버프로세스의 트레이스 파일에 기록!
→TKPROF툴을 통해 집계 리포트 추출가능
- 1053 진단 이벤트
- :옵티마이저가 최적화를 수행하는 과정을 시간 순으로 기록 , 쿼리 최적화 프로파일링
🟩 V$ACTIVE_SESSION_HISTORY
:1초 간격으로 저장된 액티브 세션의 과거 목록을 가져옴
select
to_char(h.sample_time,'mi:ss') as sample_time,
h.sample_id,
h.session_id as sid,
h.session_state as st,
h.sql_id,
(select sql_text from v$sqlarea a where a.sql_id = h.sql_id) as sql_text,
event,
blocking_session as broker
from
V$active_session_history h
where session_id = &sid ;
2. SQL PLUS 스크립팅
1. 치환 변수
특정 상수 값으로 치환되는 변수를 의미
-&문자를 사용해서 치환변수 사용!
-치환변수 &SID를 선언하고 값 지정
[define sid =135]
위에서 정의한 치환 변수는 아래와 같이 어떤 위치에서라도 사용 가능!
select
sid,
event
from
v$session
where sid =&sid;
2. 파라미터
파일을 호출할 때 파라미터를 줄 수 있음
첫번째 파라미터는 치환변수 &1, 두세번째는 &2,&3사용 가능
--&1 첫번째 파라미터 값을 $sid에 저장
define sid = &1
select sid, event
from v$session
where sid = &sid;
3. ACCEPT명령어
accept명령은 사용자에게 값을 입력 받는 용도로 사용
default옵션과 함꼐 사용하면 디폴트 값 지정 가능
ACCEPT SID2 DEFAULT &SID 로 sid에 지정된 값을 디폴트 값으로 사용할수 있도록 하기
define sid = &1
accept sid2 number default &sid prompt 'sid to monitor[&sid]:'
--sid to monitor [135]
select sid, event
from v$session
where sid = &sid2;
4. column new_value 명령
column 명령을 new value 옵션과 사용하면 select에 의해 얻어진 특정 컬럼 값을 치환변수로 저장 가능
--sid라는 컬럼 결과 값을 sid라는 치환 변수에 저장하라는 의미
col sid new_value sid
--위의 명령어 실행한후 아래 쿼리 수행하면 치환변수 $sid에 135값 저장 됨
select sid from v$session where client_info='session1';
5. spool을 이용한 동적 sql 스크립팅 구현
동적으로 생성된 SQL 파일을 기록하고 그 파일을 다시 호출하는 과정이 필요 할때가 있음
이때, SPOOL기능 이용
:호출만으로도 간단히 실행 가능
--spool결과를 완벽한 형태의 스크립트로 사용할수 있도록 set명령 사용하여 불필요한 옵션 off
--spool명령어를 사용해서 temp.sql에 저장하고 dbms_db_version패키지를 이용해서 버전구분
set echo off
set termout off
set pagesize 0
set heading off
set vertify off
set feedback off
set serveroutput on
set timing off
set scan off
spool temp.sql
begin
dbms_output.put_line('select sid, event,sql_id');
if dbms_db_version.version >=11 then
dbms_output.put_line(',(sysdate-sql_exec_start)*24*60*60 as
elapsed');
end if;
dbms_output.put_line('from v$session ');
dbms_output.put_line('where sid =&1; ');
end;
/
spool off
set echo on
set termout on
set pagesize 100
set heading on
set vertify on
set feedback on
set serveroutput off
set timing on
set scan on
--파일 실행
@make_temp
--성공적으로 실행되면 temp.sql파일이 생성됨
🟩 동적 성능뷰
동적 성능뷰, V$뷰!!
V$뷰가 제공하지 않는 정보 X$에있음
- 동적 성능뷰가 제공하는 데이터는 shared pool에 존재하는것임
- 즉, 동적뷰를 탐색한다는 것은 메모리 구조를 탐색한다는 의미임
3. PL/SQL 패키지
PL/SQL패키지
오라클 PL/SQL에서 패키지(Package)란,
논리적으로 연관된 PL/SQL 타입,변수 등(프로시저, 함수) 들이 집합된 스키마 오브젝트
-주요기능
- DBMS_UTILITY : 시간값 얻기
: 특정 작업의 소요시간 (Elpased Time) 측정하고자 할때,
: DBMS_UTILITY.GET_TIME 사용
-get_time함수로 현재시간 얻기
-치환변수 &cur_hesc값 저장
col cur_hsec new_value cur_hesc
select dbms_utility.get_time as cur_hesc from dual;
--all_objects읽기
select count(*) from all_objects;
--다시 get time함수 호출해서 그 차이 구하면 소요시간 구할수 있음
select trunc(dbms_utility.get_time - &cur_hesc)/100,2) as elapsed from dual;
:10진수의 데이터 블럭값 얻기
data_block_address_file , data_block_address_block 함수 사용
--예제 테이블 생성
creaate table t1
as
select level as c1
from dual
connect by level <= 100;
create index tl_n1 n t1(c1);
--t1_n1의 오브젝트 아이디 얻기
col data_object_id new_value obj_id
select data_object_id from dba_objects
where owner = user and object_name ='T1_N1';
--인덱스 트리덤프 수행
alter session set events 'immediate trace name treedump level &obj_id';
>>트레이스 파일에 인덱스 트리에 대한 덤프 수행
--10진수의 값을 변환
'Database > Oracle' 카테고리의 다른 글
| 데이터 이관 (0) | 2025.01.21 |
|---|---|
| 오라클 기동과 정지/ 커넥션과 서버프로세스 (1) | 2024.09.23 |
| DML성능 저하 원인 (0) | 2024.03.21 |
| Oracle profile, user, role조회 (0) | 2024.03.21 |
| 오라클 구성 참고 (0) | 2024.03.18 |