ORACLE 트러블 슈팅(성능 고도화 원리와 해법!)

==================================================================================

-- Hidden Paramter 조회
col name format a30
col value format a10
select a.ksppinm name, b.ksppstvl value
from x$ksppi a, x$ksppsv b
where a.indx=b.indx
and a.ksppinm like '%db_block_hash%';

==================================================================================

-- p.43
-- Undo 사용현황 조회
select s.sid, s.serial#, t.xidusn, t.used_ublk, t.used_urec
from v$session s, v$transaction t
where t.addr = s.taddr and s.sid = 133;

==================================================================================

-- p.95
-- 딕셔너리 캐쉬 히트율 확인(수치가 낮으면 Shared Pool 사이즈를 늘리는 것을 고려)
col hit_ratio format 990.99
select round((sum(gets - getmisses)) / sum(gets) * 100, 2) hit_ratio
from v$rowcache;
select
    parameter, gets, getmisses
    , round((gets - getmisses) / gets * 100, 2) hit_ratio
    , modifications
from v$rowcache
where gets > 0
order by hit_ratio desc;

==================================================================================

-- p.132~133
-- TX Lock 조회
select
    sid, type, id1, id2, lmode, request, block
    , to_char(trunc(id1/power(2,16))) usn -- Undo 세그먼트 번호
    , bitand(id1, to_number('ffff', 'xxxx')) + 0 slot -- 트랜잭션 테이블 슬롯번호
    , id2 sqn -- 트랜잭션 슬롯 Wrap 시퀀스
from v$lock
where type = 'TX';
-- TX Lock 원인 파악
select
    sid, seq#, event, state, seconds_in_wait, p1, p2, p3
from v$session_wait
where event like 'enq: TX%';

==================================================================================

-- p.137

-- ITL 경합에 의한 대기 현상이 자주 발생하는 세그먼트 조회

select ts#, obj#, dataobj#, sum(value) itl_waits

from v$segstat

where statistic_name = 'ITL waits'

group by ts#, obj#, dataobj#

having sum(value) > 0

order by sum(value) desc;

==================================================================================

-

- p.147

-- Lock 모니터링

select l.session_id SID

     ,(case when lock_type = 'Transaction' then 'TX' 

            when lock_type = 'DML' then 'TM' end) TYPE

     , mode_held

     , mode_requested mode_reqd

     ,(case when lock_type = 'Transaction' then 

                 to_char(trunc(lock_id1/power(2,16)))

            when lock_type = 'DML' then 

                (select object_name from dba_objects 

                 where object_id = l.lock_id1)

       end) "USN/Table"

     ,(case when lock_type = 'Transaction' then 

                 bitand(lock_id1, to_number('ffff', 'xxxx')) + 0

       end) "SLOT"

     ,(case when lock_type = 'Transaction' then 

                to_number(lock_id2) end) "SQN"

     ,(case when blocking_others = 'Blocking' then ' <<<<<' end) Blocking

from   dba_lock l

where  lock_type in ('Transaction', 'DML' )

order by session_id, lock_type, lock_id1, lock_id2;

==================================================================================

-- p.148

-- 이벤트 발생 상황 조회

select event, wait_time, seconds_in_wait, state -- seconds_in_wait : 기다리는 시간

from v$session_wait

where sid = 52;

==================================================================================

-- p.158~159

-- Explain plan

explain plan set statement_id ='query1' for 

select * from emp where empno = 7900;

set linesize 200

- 싱글실행계획

@?/rdbms/admin/utlxpls

- 병렬실행계획

@?/rdbms/admin/utlxplp

==================================================================================

-- p.160

-- 실행 계획 테이블에 저장

create table sql_repository( sql_id varchar2(30), sql_text varchar2(4000) );

begin

    for c in (select sql_id, sql_text from sql_repository)

    loop

        execute immediate 'explain plan set statement_id = ''' || c.sql_id

            || ''' into sql_plan_repository'

            || ' for ' || c.sql_text;

        commit;

    end loop;

end;

/

==================================================================================

-- p.163

-- AutoTrace 권한 부여(sysdba)

@?/sqlplus/admin/plustrce

grant plustrace to scott;

==================================================================================

-- p.165

-- sql trace 파일(trc) 경로 확인

select r.value || '/' || lower(t.instance_name) || '_ora_'  

    || ltrim(to_char(p.spid)) || '.trc' trace_file          

from   v$process p, v$session s, v$parameter r, v$instance t

where  p.addr = s.paddr                                     

and    r.name = 'user_dump_dest'                            

and    s.sid = (select sid from v$mystat where rownum = 1) ;

==================================================================================

-- p.165

-- 자기 세션에 트레이스 걸기

- grant alter session to scott;

alter session set sql_trace = true;

- 자기 세션에 식별자 붙이기(trc 파일 뒤에 붙음)

alter session set tracefile_identifier ='jbt';

select * from emp where empno = 7788;

select * from dual;

alter session set sql_trace = false;

==================================================================================

-- p.166

-- trc 포맷팅

tkprof testdb_ora_19305_jbt.trc jbt_report.prf sys=no

==================================================================================

-- p.169

-- SQL 트레이스에 이벤트 트레이스 추가

alter session set events '10046 trace name context forever, level 8';

alter session set events '10046 trace name context off';

==================================================================================

-- p.175

-- 다른 세션에 트레이스 걸기(10g~)

begin

    DBMS_MONITOR.SESSION_TRACE_ENABLE (

        session_id => 145

        , serial_num => 3

        , waits => TRUE

        , binds => TRUE);

end;

/

begin

    DBMS_MONITOR.SESSION_TRACE_DISABLE (

        session_id => 145

        , serial_num => 3);

end;

/

==================================================================================

-- p.175~176

-- 다른 세션에 트레이스 걸기2(버전 상관 없음)

oradebug setospid 3796

- 트레이스 파일의 크기 제한을 없앰

oradebug unlimit

oradebug event 10046 trace name context forever, level 8

oradebug tracefile_name

-- 트레이스 해제

oradebug event 10046 trace name context off

oradebug close_trace

==================================================================================

-- p.177

-- 현재 접속해 있는 시스템의 service, module, action 확인

select sid, service_name, module, action

from v$session

where service_name <> 'SYS$BACKGROUND';

==================================================================================

-- p.177

-- 특정 서비스에 트레이스 걸기(10g~)

begin

    DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE (

        service_name => 'orcl' --> 대소문자 주의!!!

        , module_name => dbms_monitor.all_modules

        , action_name => dbms_monitor.all_actions

        , waits => TRUE

        , binds => TRUE);

end;

/

-- 트레이스 확인

select primary_id service_name

    , qualifier_id1 module

    , qualifier_id2 action

    , waits, binds

from dba_enabled_traces;

-- 트레이스 해제

begin

    DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE (

        service_name => 'orcl'

        , module_name => dbms_monitor.all_modules

        , action_name => dbms_monitor.all_actions);

end;

/

==================================================================================

-- p.178,180

-- 현재 세션의 module, action 변경

begin

    dbms_application_info.set_module (

        module_name => 'emp manager'

        , action_name => 'select emp');

end;

/

-- 현재 세션의 action 변경

dbms_application_info.set_action('update emp');

==================================================================================

-- p.179

-- 특정 모듈에 트레이스 걸기(10g~)

begin

    DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE (

        service_name => 'orcl' --> 대소문자 주의!!!

        , module_name => 'emp manager'

        , action_name => dbms_monitor.all_actions

        , waits => TRUE

        , binds => TRUE);

end;

/

begin

    DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE (

        service_name => 'orcl'

        , module_name => 'emp manager'

        , action_name => dbms_monitor.all_actions);

end;

/

==================================================================================

-- p.180

-- 특정 액션에 트레이스 걸기(10g~)

begin

    DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE (

        service_name => 'orcl' --> 대소문자 주의!!!

        , module_name => 'emp manager'

        , action_name => 'update emp'

        , waits => TRUE

        , binds => TRUE);

end;

/

==================================================================================

-- p.182

-- 특정 세션에 트레이스 걸기(10g~)

exec dbms_session.set_identifier('jbt');

begin

    DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE (

        client_id => 'jbt'

        , waits => FALSE

        , binds => FALSE);

end;

/

==================================================================================

-- p.182

==> 트레이스 거는거와 사용법 동일

-- service, module_action 단위로 v$sesstat 통계정보 수집

dbms_monitor.serv_mod_act_stat_enable();

dbms_monitor.serv_mod_act_stat_disable();

dba_enabled_aggregations --> 진행상태 확인

v$serv_mod_act_stats --> 수집된 수행통계 확인

-- client_identifier 별로 v$sesstat 통계정보 수집

dbms_monitor.client_id_stat_enable();

dbms_monitor.client_id_stat_disable();

v$client_stats --> 수집된 수행통계 확인

==================================================================================

-- p.183

-- DBMS_XPLAN 패키지로 예상 실행계획 출력

select plan_table_output

from table(dbms_xplan.display('plan_table', null, 'serial'));

select * from table(dbms_xplan.display('plan_table', 'sql1', 'basic'));

select * from table(dbms_xplan.display('plan_table', 'sql1', 'typical'));

select * from table(dbms_xplan.display('plan_table', 'sql1', 'serial'));

select * from table(dbms_xplan.display('plan_table', 'sql1', 'all'));

select * from table(dbms_xplan.display('plan_table', 'sql1', 'basic rows bytes cost'));

--> rows, bytes, cost 외 기타 옵션 : partition, parallel, predicate, projection, alias, remote, note

select * from table(dbms_xplan.display('plan_table', 'sql1', 'outline'));

select * from table(dbms_xplan.display('plan_table', 'sql1', 'advanced')); --> all + outline

==================================================================================

-- p.186

-- 마지막에 수행한 SQL의 sql_id와 child_number 값 조회

select prev_sql_id, prev_child_number

from v$session

where sid=userenv('sid')

    and username is not null and prev_hash_value <> 0;

==================================================================================

-- p.187

-- DBMS_XPLAN 패키지로 현재 캐싱된 커서의 실행계획 조회(3번째 포맷 인자는 display 함수와 동일)

set serveroutput off

select *

from emp e, dept d

where d.deptno = e.deptno and e.sal >= 1000; 

- 확인

select * from table(dbms_xplan.display_cursor('&sql_id', &child_no, 'serial'));

select * from table(dbms_xplan.display_cursor(null, null, 'serial'));

-- DBMS_XPLAN 패키지로 AWR에 수집된 과거 수행 SQL의 실행계획 조회(3번째 포맷 인자는 display 함수와 동일)

select * from table(dbms_xplan.display_awr('&sql_id', &child_no, 'serial'));

==> User has no SELECT privilege on V$SESSION 이 떴을 때 다음과 같이 권한 필요

grant select on v_$sql_plan_statistics_all to scott;

grant select on v_$session to scott;

grant select on v_$sql to scott;

grant select on v_$sql_plan to scott;

==================================================================================

-- p.188

-- DBMS_XPLAN 패키지로 현재 캐싱된 커서의 Row Source별 수행통계 출력

==> SQL 트레이스를 확인하기 전에 우선 활용 - Call 통계만 제외

set serveroutput off

select /*+ gather_plan_statistics */ *

from emp e, dept d

where d.deptno = e.deptno and e.sal >= 1000;

- 확인

select * from table(dbms_xplan.display_cursor('&sql_id', &child_no, 'iostats'));

select * from table(dbms_xplan.display_cursor(null, null, 'iostats'));

select * from table(dbms_xplan.display_cursor(null, null, 'memstats'));

select * from table(dbms_xplan.display_cursor(null, null, 'iostats memstats'));

select * from table(dbms_xplan.display_cursor(null, null, 'allstats'));

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

==================================================================================

-- p.192~3

-- 시스템 수행 통계 수집 및 분석(별도의 세션을 열어 수행)

- 우선 실행

create table sess_stat

as

select 1 no, statistic#, value

from   v$sesstat

where  sid = &SID;

- query 혹은 job 실행 후 실행

insert into sess_stat

select 2 no, statistic#, value

from   v$sesstat

where  sid = &SID;

commit;

select b.statistic# stat#, b.name, (b.value - a.value) delta_value

from (

        select n.statistic#, n.name, b.value

        from   v$statname n, sess_stat b

        where  b.statistic# = n.statistic#

        and    b.value > 0

        and    b.no = 2

     ) b, sess_stat a 

where  a.no = 1

and    a.statistic# = b.statistic#

and   (b.value - a.value) > 0

order by delta_value desc

;

==================================================================================

-- p.194

-- Ratio 기반 성능 분석

select round(100*(1-bfwt/gets),2) "Buffer Nowait %"

from

    ( select sum(count) bfwt from v$waitstat ),

    ( select value gets from v$sysstat where name = 'session logical reads' )

;

select round(100*(1-rlsr/rent),2) "Redo Nowait %"

from

    ( select value rlsr from v$sysstat where name = 'redo log space requests' ),

    ( select value rent from v$sysstat where name = 'redo entries' )

;

select round(100*(1-(phyr-phyrd-nvl(phyrdl,0))/gets),2) "Buffer Hit %"

from

    ( select value phyr from v$sysstat where name = 'physical reads' ),

    ( select value phyrd from v$sysstat where name = 'physical reads direct' ),

    ( select value phyrdl from v$sysstat where name = 'physical reads direct (lob)' ),

    ( select value gets from v$sysstat where name = 'session logical reads' )

;

select round(100*(1-sum(misses)/sum(gets)),2) "Latch Hit %"

from v$latch

;

select round(100*sum(gethits)/sum(pins),2) "Library Cache Pin Hit %"

from v$librarycache

;

select round(100*sum(gethits)/sum(gets),2) "Library Cache Get Hit %"

from v$librarycache

;

select round(100*(1-hprs/prse),2) "Soft Parse %"

from

    ( select value hprs from v$sysstat where name = 'parse count (hard)' ),

    ( select value prse from v$sysstat where name = 'parse count (total)' )

;

select round(100*(1-prse/exe),2) "Execute to Parse %"

from

    ( select value prse from v$sysstat where name = 'parse count (total)' ),

    ( select value exe from v$sysstat where name = 'execute count' )

;

select decode( prsela

        , 0, to_number(null)

        , round(prscpu/prsela*100,2)) "Parse CPU to Parse Elapsed %"

from

    ( select value prsela from v$sysstat where name = 'parse time elapsed' ),

    ( select value prscpu from v$sysstat where name = 'parse time cpu' )

;

select decode( tcpu

        , 0, to_number(null)

        , round(100*(1-(prscpu/tcpu)),2)) "Non-Parse CPU %"

from

    ( select value tcpu from v$sysstat where name = 'CPU used by this session' ),

    ( select value prscpu from v$sysstat where name = 'parse time cpu' )

;

select decode( (srtm+srtd)

        , 0, to_number(null)

        , round(100*srtm/(srtd+srtm),2)) "In-memory Sort %"

from

    ( select value srtm from v$sysstat where name = 'sorts (memory)' ),

    ( select value srtd from v$sysstat where name = 'sorts (disk)' )

;

select round(100*(1-sum(decode(name, 'free memory', bytes))/sum(bytes)),2) "Memory Usage %"

from v$sgastat

where pool = 'shared pool'

;

==================================================================================

-- p.204

-- 현재 이벤트 분석(직전 쿼리 성능 체크)

col wait_time heading 'WAIT|TIME'

col seconds_in_wait heading 'SECONDS|IN_WAIT'

select event

    , wait_time

    , seconds_in_wait

    , state

    , p1text || '->' || p1 || ',' || p2text || '->' || p2

        || ',' || p3text || '->' || p3 param

from v$session_wait

where sid = 145

order by seq#

;

-----------------------------------------------------------------

wait_time > 0 : 마지막 대기 이벤트를 대기한 시간

wait_time = 0 : 이 이벤트를 현재 대기 중

wait_time = -1 : 마지막 대기 이벤트를 대기한 시간이 10ms 미만

wait_time = -2 : 타이밍이 활성화되지 않음

-----------------------------------------------------------------

==================================================================================

-- p.213

-- 구간별 SQL 수행횟수 조회

select to_char(min(s.begin_interval_time), 'hh24:mi') begin

     , to_char(min(s.end_interval_time), 'hh24:mi') end

     , sum(b.value-a.value) "execute count"

from   dba_hist_sysstat a, dba_hist_sysstat b, dba_hist_snapshot s

where  s.instance_number = &instance_number

and    s.snap_id between &begin_snap and &end_snap

and    a.stat_name = 'execute count'

and    b.stat_id = a.stat_id

and    b.snap_id = s.snap_id

and    a.snap_id = b.snap_id - 1

and    a.instance_number = s.instance_number

and    b.instance_number = s.instance_number

group by s.snap_id

order by s.snap_id;

==================================================================================

-- p.222

-- ASH 버퍼에 저장된 세션 히스토리 정보 조회

select sample_id, sample_time

  , session_id, session_serial#, user_id, xid

  , sql_id, sql_child_number, sql_plan_hash_value

  , session_state

  , qc_instance_id , qc_session_id

  , blocking_session, blocking_session_serial#, blocking_session_status

  , event, event#, seq#, wait_class, wait_time, time_waited

  , p1text, p1, p2text, p2, p3text, p3

  , current_obj#, current_file#, current_block#

  , program, module, action, client_id 

from v$active_session_history

where session_id = 143;

==================================================================================

-- p.223

-- 세션 히스토리(ASH) 정보 조회 2

column current_obj# format 99999 heading 'CUR_|OBJ#'

column current_file# format 999 heading 'CUR_|FIL#'

column current_block# format 999 heading 'CUR_|BLK#'

select

    to_char(sample_time, 'yyyy-mm-dd hh24:mi:ss') sample_tm, session_state

    , event, wait_class, current_obj#, current_file#, current_block#

from v$active_session_history

where session_id = 143 and session_serial# = 506

order by sample_time;

-> 과거치는 dba_hist_active_sess_history 뷰 참조

==================================================================================

-- p.227

-- SQL 통계치 조회

select parsing_schema_name 

     , count(*) sql_cnt -- SQL 개수

     , count(distinct substr(sql_text, 1, 100)) sql_cnt2 -- SQL 개수(Unique)

     , sum(executions) executions -- 수행횟수

     , round(avg(buffer_gets/executions)) buffer_gets -- 논리적 I/O

     , round(avg(disk_reads/executions)) disk_reads -- 디스크 I/O

     , round(avg(rows_processed/executions)) rows_processed -- 처리건수

     , round(avg(elapsed_time/executions/1000000),2) "ELAPSED_TIME(AVG)" -- 평균소요시간

     , count(case when elapsed_time/executions/1000000 >= 10 then 1 end) "BAD SQL" -- 악성SQL(10초 이상)

     , round(max(elapsed_time/executions/1000000),2) "ELAPSED_TIME(MAX)" -- 최대 소요시간

from   v$sql

where parsing_schema_name in ('SCOTT')

and last_active_time >= sysdate - 7

and    executions > 0

group by parsing_schema_name;

==================================================================================

-- p.232

-- Colored SQL : 사용자가 명시적으로 지정한 커서의 수행통계가 AWR에 수집되도록 마킹

begin

dbms_workload_repository.add_colored_sql(sql_id => '803b7z0t84sq7')

end;

/

select * from dba_hist_colored_sql;

begin

dbms_workload_repository.remove_colored_sql(sql_id => '803b7z0t84sq7')

end;

/

==================================================================================

-- p.254

-- 래치 정보 조회

select child#, gets, misses, sleeps, immediate_gets, immediate_misses

from v$latch_children

where name = 'shared pool'

--where name = 'library cache'

order by child#;

==================================================================================

-- p.260

-- 라이브러리 캐시에 공유돼 있는 커서의 수행통계 조회

select sql_id, parse_calls, loads, executions, invalidations

, decode(sign(invalidations), 1, (loads-invalidations), 0) reloads

from v$sql

where sql_text like '%cursor_test%'

and sql_text not like '%v$sql%';

==================================================================================

-- p.265

-- Parent 커서 정보 조회

select sql_id, version_count, optimizer_mode, address, hash_value

from v$sqlarea

where sql_text like '%select * from emp%'

and sql_text not like '%v$sql%';

-- Child 커서 정보 조회

select sql_id, child_number, optimizer_mode, address, hash_value, parsing_user_id

from v$sql

where sql_text like '%select * from emp%'

and sql_text not like '%v$sql%';

==================================================================================

-- p.268~269

-- child커서 미공유 원인 확인

select child_number, child_address, optimizer_mode_mismatch, optimizer_mismatch, bind_mismatch

from v$sql_shared_cursor

where sql_id = '73sc1t0zhj2q2';

==================================================================================
-- p.273
-- 바인드 변수 사용 여부 체크(loads vs parse_calls, executions)
select sql_text, loads, parse_calls, executions, fetches from v$sql
where  sql_text like '%test1%'
and    sql_text not like '%v$sql%'
and    sql_text not like 'declare%' ;
==================================================================================

-- p.453

-- Prefetch 정보 확인

select name, value from v$sysstat

where name in ('physical reads cache prefetch'

    , 'prefetched blocks aged out before use');

==================================================================================

-- p.471

-- RAC 캐시 다운그레이드 Ratio 확인

select data_requests, fairness_down_converts

, decode(data_requests,0,'N/A',round(fairness_down_converts/data_requests*100)) "DOWNGRADE RATIO(%)"

from v$cr_block_server;