DB스토리

*sql문 자체 튜닝.

1. 해당 user 접속
2. User sid 값 찾기
3. 해당 session에 trace를 시작
4. Session에서 질의 실행 후 접속 해지
5. Tkprof로 레포트 생성
6. 레포트 분석

   User sid 값 찾는 법

1. S SYS> select sys_context('userenv','sid') from dual;

      SYS_CONTEXT('USERENV','SID')
   -----------------------------------------
   148

      

S SYS> select sid from v$session where username='SYS';

       SID

----------
       148

S SYS> select sid,serial# from v$session where username='SYS'

       SID    SERIAL#

---------- ----------
       148       1122

1. 해당 session에 trace를 시작

   다른 session 에서 작업

   S SYS> exec dbms_monitor.session_trace_enable(148,1122,waits=>TRUE,binds=>TRUE);

   PL/SQL procedure successfully completed.

   또는

      

   현재 session에서

   S SYS> alter session set sql_trace=TRUE;
   Session altered.

      

2. Session에서 질의 실행 후 접속 해지

   S SYS> create table myAccessableObject as select * from user_objects;
   Table created.

   Trace 파일은

   S SYS> show parameter user_dump

   NAME                                 TYPE     VALUE
------------------------------------ -------- ------------------------------
user_dump_dest                      string   /u01/app/oracle/admin/orcl/udump

[oracle@edrsr4p1 ~]$ cd /u01/app/oracle/admin/orcl/udump/

[oracle@edrsr4p1 udump]$ ls

orcl_ora_10567.trc orcl_ora_24430.trc orcl_ora_4772.trc orcl_ora_5187.trc orcl_ora_6317.trc

orcl_ora_10596.trc orcl_ora_2997.trc orcl_ora_4826.trc orcl_ora_5214.trc orcl_ora_6906.trc

orcl_ora_10952.trc orcl_ora_2999.trc orcl_ora_4856.trc orcl_ora_5229.trc orcl_ora_8609.trc

orcl_ora_12295.trc orcl_ora_3024.trc orcl_ora_4861.trc orcl_ora_5318.trc orcl_ora_9286.trc

orcl_ora_12482.trc orcl_ora_3025.trc orcl_ora_4877.trc orcl_ora_5345.trc orcl_ora_9377.trc

orcl_ora_12509.trc orcl_ora_3026.trc orcl_ora_4878.trc orcl_ora_5375.trc orcl_ora_9426.trc

orcl_ora_13734.trc orcl_ora_3027.trc orcl_ora_4888.trc orcl_ora_5399.trc orcl_ora_9456.trc

orcl_ora_13796.trc orcl_ora_3083.trc orcl_ora_4894.trc orcl_ora_5577.trc orcl_ora_9476.trc

orcl_ora_13824.trc orcl_ora_3110.trc orcl_ora_4903.trc orcl_ora_5942.trc orcl_ora_9490.trc

orcl_ora_13852.trc orcl_ora_3111.trc orcl_ora_4919.trc orcl_ora_5957.trc orcl_ora_9746.trc

orcl_ora_14597.trc orcl_ora_4661.trc orcl_ora_4971.trc orcl_ora_5984.trc orcl_ora_9773.trc

orcl_ora_14712.trc orcl_ora_4688.trc orcl_ora_4987.trc orcl_ora_5985.trc

orcl_ora_15332.trc orcl_ora_4716.trc orcl_ora_5010.trc orcl_ora_6168.trc

orcl_ora_22794.trc orcl_ora_4746.trc orcl_ora_5014.trc orcl_ora_6241.trc

orcl_ora_22810.trc orcl_ora_4747.trc orcl_ora_5186.trc orcl_ora_6268.trc

[oracle@edrsr4p1 udump]$ date

Tue Aug 11 13:40:35 KST 2009

[oracle@edrsr4p1 udump]$ ls -als | grep "Aug 11 13"

8 drwxr-x--- 2 oracle oinstall 4096 Aug 11 13:37 .

604 -rw-r----- 1 oracle oinstall 606344 Aug 11 13:39 orcl_ora_6906.trc

1. Tkprof 로 레포트생성

   [oracle@edrsr4p1 udump]$ tkprof orcl_ora_6906.trc output.txt

    TKPROF: Release 10.2.0.1.0 - Production on Tue Aug 11 13:42:14 2009

      Copyright (c) 1982, 2005, Oracle. All rights reserved.

      

      

2. 레포트 분석

   [oracle@edrsr4p1 udump]$ vi output.txt

      

   

      

      

   Test

      

   

      

Background Process Trace Files ( tkprof지원안함)

S SYS> show parameter background_dump

문제 발생시 해당 프로세스 모니터링.

   col stat_name form a50;

spool &sys_time_model_report_name;

select systimestamp from dual;

select stat_id,stat_name,to_char((value/

(select value from v$sys_time_model where stat_name='DB time')

)*100,'999.99') "%"

from v$sys_time_model

order by value desc

/

spool off

sav sysTimeModel2

   Lock 걸렸을때 cpu 타임 확인

Session time model로 확인

col stat_name form a50;

select &&sid from dual;

select systimestamp from dual;

select stat_id,stat_name,to_char((value/

(select value from v$sess_time_model where stat_name='DB time' and sid=&sid)

)*100,'999.99') "%"

from v$sess_time_model

where sid=&sid

order by value desc

/

sav sessTimeModel

   lock 건 session

Lock 걸린 session   

S SYS> select * from v$lock where block>0

S SYS> select * from v$lock where sid=134;
ADDR     KADDR           SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK

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

2F9C3F90 2F9C3FA8        134 TM        51146          0          3          0       2065          0

2FA08B84 2FA08CA0        134 TX       131088        767          6          0       2065          1

 S SYS> select event, total_waits, average_wait from v$session_event where sid=136;

S SYS> select event, total_waits, average_wait from v$session_event where sid=135;	


EVENT                                              TOTAL_WAITS AVERAGE_WAIT

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

wait for unread message on broadcast channel             11440        97.67

log file sync                                               14         3.67

db file sequential read                                      1         2.51

latch: shared pool                                           1          .01

latch: library cache                                         1          .04

SQL*Net message to client                                  462            0

SQL*Net message from client                                462          .14

SQL*Net break/reset to client                              756          .03

8 개의 행이 선택되었습니다. 



































S SYS> select * from ( 

select sid,event, total_waits,wait_class, average_wait 

from v$session_event s 

where (select username from v$session where sid=s.sid) is not null and sid not in( 

select sid from v$session where username in('SYS','DBSNMP','SYSMAN')) 

order by total_waits desc) 

where rownum <10    

sav topSessionEvent

  9 개의 행이 선택되었습니다.

Time Model

주요 뷰

V_$SESSION_EVENT

V_$SYSTEM_EVENT

V_$EVENT_NAME

V_$EVENT_HISTOGRAM

V_$SERVICE_EVENT

V_$EVENTMETRIC

V_$WAITSTAT

V_$SESSION_WAIT_CLASS

V_$SESSION_WAIT

V_$SESSION_WAIT_HISTORY

V_$SYSTEM_WAIT_CLASS

V_$SERVICE_WAIT_CLASS

V_$WAITCLASSMETRIC

V_$WAITCLASSMETRIC_HISTORY