태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

Oracle Hang 상황에서 액티브 세션 리스트 얻기

오라클 2010.04.26 10:20
Maxgauge와 같은 툴의 가장 큰 장점은 Oracle Hang 상황에서도 액티브 세션의 리스트를 얻을 수 있다는 것입니다. 대기 이벤트와 SQL 정보가 포함된 액티브 세션 목록이야말로 모든 성능 트러블슈팅의 시작입니다.

Maxgauge와 같은 툴이 Oracle Hang 상황에서도 데이터를 수집할 수 있는 것은 DMA(Direct Memory Access)를 사용하기 때문입니다. 이 방법은 Oracle의 COE(Center Of Expertise) 팀이 극한의 상황, 즉 SQL로 필요한 정보를 수집하지 못할 때 사용하던 방법입니다. 이것이 Maxgauge와 같은 툴 덕분에 보편화된 것입니다.

만일 Maxgauge와 같은 툴이 없다면 어떻게 해야 할까요? Oracle이 제공하는 ASHDUMP 기능을 Preliminary Connection과 함께 사용할 수 있습니다.

  • Preliminary Connection이란 SQL*Net 방식이 아닌 Direct Memory Access 방식으로 Connection을 맺는 것을 말합니다.
  • ASHDUMP는 액티브 세션 목록의 메모리 버전인 ASH(Active Session History)를 텍스트 파일에 기록하는 기능입니다.
따라서 위 두 기능을 같이 사용하면 마치 DMA 방식으로 액티브 세션의 목록을 얻는 것과 동일한 효과가 있습니다.

아래에 간단한 예제가 있습니다.

우선 Preliminary Connection을 맺습니다.

#> sqlplus -prelim sys/oracle@ukja1106 as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Apr 26 09:43:35 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.
일반적인 쿼리는 동작하지 않습니다.
alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss'
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0
Preliminary Connection 상태에서 ASHDUMP를 수행합니다. 레벨(10)은 10분을 의미합니다. 즉 지난 10분간의 ASH를 의미합니다.
SYS@ukja1106> oradebug setmypid
Statement processed.
SYS@ukja1106> oradebug dump ashdump 10
Statement processed.
SYS@ukja1106> oradebug tracefile_name
c:\oracle\diag\rdbms\ukja1106\ukja1106\trace\ukja1106_ora_12152.trc
Dump 파일의 내용은 다음과 같습니다. 11g부터는 액티브 세션 목록과 함께 해당 목록을 테이블로 로딩하는 스크립트까지 함께 제공합니다. Hang의 악몽이 지나간 후(대부분 리스타트) 사후 분석을 위한 것입니다.
Processing Oradebug command 'dump ashdump 10'
ASH dump
<<>>
****************
SCRIPT TO IMPORT
****************
------------------------------------------
Step 1: Create destination table 
------------------------------------------
CREATE TABLE ashdump AS
SELECT * FROM SYS.WRH$_ACTIVE_SESSION_HISTORY WHERE rownum < 0
----------------------------------------------------------------
Step 2: Create the SQL*Loader control file  as below
----------------------------------------------------------------
load data 
infile * "str '\n####\n'"
append
into table ashdump
fields terminated by ',' optionally enclosed by '"'
(                               
SNAP_ID  CONSTANT 0           , 
DBID                          , 
INSTANCE_NUMBER               , 
SAMPLE_ID                     , 
SAMPLE_TIME TIMESTAMP ENCLOSED BY '"' AND '"' "TO_TIMESTAMP(:SAMPLE_TIME   ,'MM-DD-YYYY HH24:MI:SSXFF')"   , 
SESSION_ID                    , 
SESSION_SERIAL#               , 
SESSION_TYPE                  , 
USER_ID                       , 
SQL_ID                        , 
SQL_CHILD_NUMBER              , 
SQL_OPCODE                    , 
FORCE_MATCHING_SIGNATURE      , 
TOP_LEVEL_SQL_ID              , 
TOP_LEVEL_SQL_OPCODE          , 
SQL_PLAN_HASH_VALUE           , 
SQL_PLAN_LINE_ID              , 
SQL_PLAN_OPERATION#           , 
SQL_PLAN_OPTIONS#             , 
SQL_EXEC_ID                   , 
SQL_EXEC_START DATE 'MM/DD/YYYY HH24:MI:SS' ENCLOSED BY '"' AND '"' ":SQL_EXEC_START"   , 
PLSQL_ENTRY_OBJECT_ID         , 
PLSQL_ENTRY_SUBPROGRAM_ID     , 
PLSQL_OBJECT_ID               , 
PLSQL_SUBPROGRAM_ID           , 
QC_INSTANCE_ID                , 
QC_SESSION_ID                 , 
QC_SESSION_SERIAL#            , 
EVENT_ID                      , 
SEQ#                          , 
P1                            , 
P2                            , 
P3                            , 
WAIT_TIME                     , 
TIME_WAITED                   , 
BLOCKING_SESSION              , 
BLOCKING_SESSION_SERIAL#      , 
CURRENT_OBJ#                  , 
CURRENT_FILE#                 , 
CURRENT_BLOCK#                , 
CURRENT_ROW#                  , 
CONSUMER_GROUP_ID             , 
XID                           , 
REMOTE_INSTANCE#              , 
TIME_MODEL                    , 
SERVICE_HASH                  , 
PROGRAM                       , 
MODULE                        , 
ACTION                        , 
CLIENT_ID                       
)                               
---------------------------------------------------
Step 3: Load the ash rows dumped in this trace file
---------------------------------------------------
sqlldr userid/password control=ashldr.ctl data= errors=1000000
---------------------------------------------------
<<>>
<<>>
####
58646642,1,12519562,"04-26-2010 09:44:01.822000000",160,1,2,0,
"",0,0,0,"",0,0,0,0,0,
0,"",0,0,0,0,0,0,0,
3213517201,9858,0,3,1,0,69788,4294967295,0,
4294967295,0,0,0,0,,0,0,165959219,
"ORACLE.EXE (CKPT)","",
"",""
####
58646642,1,12519486,"04-26-2010 09:42:45.808000000",160,1,2,0,
"",0,0,0,"",0,0,0,0,0,
0,"",0,0,0,0,0,0,0,
3213517201,9767,1,1,1,0,38825,4294967295,0,
4294967295,0,0,0,0,,0,0,165959219,
"ORACLE.EXE (CKPT)","",
"",""
####
58646642,1,12519416,"04-26-2010 09:41:35.770000000",160,1,2,0,
"",0,0,0,"",0,0,0,0,0,
0,"",0,0,0,0,0,0,0,
4078387448,9683,3,3,3,0,11083,4294967295,0,
4294967295,0,0,0,0,,0,0,165959219,
"ORACLE.EXE (CKPT)","",
"",""
####
58646642,1,12519384,"04-26-2010 09:41:03.774000000",163,1,2,0,
"",0,0,0,"",0,0,0,0,0,
0,"",0,0,0,0,0,0,0,
3176176482,40612,5,1,1000,999888,0,4294967291,0,
4294967295,0,0,0,0,,0,0,165959219,
"ORACLE.EXE (DIA0)","",
"",""
####
58646642,1,12519304,"04-26-2010 09:39:43.719000000",160,1,2,0,
"",0,0,0,"",0,0,0,0,0,
0,"",0,0,0,0,0,0,0,
3213517201,9551,1,1,1,0,38798,4294967295,0,
4294967295,0,0,0,0,,0,0,165959219,
"ORACLE.EXE (CKPT)","",
"",""
####
58646642,1,12519265,"04-26-2010 09:39:04.663000000",163,1,2,0,
"",0,0,0,"",0,0,0,0,0,
0,"",0,0,0,0,0,0,0,
3176176482,40493,5,1,1000,999941,0,4294967291,0,
4294967295,0,0,0,0,,0,0,165959219,
"ORACLE.EXE (DIA0)","",
"",""
####
58646642,1,12519183,"04-26-2010 09:37:42.554000000",160,1,2,0,
"",0,0,0,"",0,0,0,0,0,
0,"",0,0,0,0,0,0,0,
3213517201,9406,0,1,1,0,54077,4294967295,0,
4294967295,0,0,0,0,,0,0,165959219,
"ORACLE.EXE (CKPT)","",
"",""
####
<<>>

*** 2010-04-26 09:45:51.625
Oradebug command 'dump ashdump 10' console output: 
사후 분석 용도로 사용되면 대단히 유용할 것입니다. Preliminary Connection에서는 전통적인 트러블슈팅 데이터인 Hang Analyze나 System State Dump도 사용 가능합니다. 단, Preliminary Connection와 oradebug는 비공식적인 지원 기능이기 때문에 철저한 테스트 후 사용해야 되며, 가능한 오라클 지원 엔지니어의 승인하에 사용해야 합니다.

더 자세한 정보들은 아래 문서를 참조하세요.

저작자 표시
신고
tags : ,
Trackback 0 : Comments 3
  1. 유수익 2010.04.26 13:06 신고 Modify/Delete Reply

    음.. 유요한 정보 감사합니다.
    참고로, ora10g r1에서는 sqlplus -prelim "/as sysdba"하면 안되는 경우가 있어
    sqlplus -prelim => set -prelim on => connect as sysdba 로 분리해서 접속하시면 됩니다.

  2. 양용성 2010.05.27 18:14 신고 Modify/Delete Reply

    좋은 자료 감사합니다.
    자주 들리겠습니다

  3. dba_chris 2010.06.09 11:35 신고 Modify/Delete Reply

    감사합니다.

Write a comment

티스토리 툴바