정의 / 특징
ASH = Active Session History
ORACLE 10g 이상 사용 가능.
"짧은 시간 동안 발생한 문제를 파악하는데 유용"
AWR 데이터에 비해 수집되는 정보의 종류가 적음.
샘플 세션만 저장되므로 성능 문제 발생 시 이에 대한 데이터가 없을 수 있음.
사용
SYS> @?/rdbms/admin/ashrpt.sql
Enter value for report_type: [html(default) / text]
Enter value for begin_time: [측정 시작점 /Defaults to -15 mins]
Enter value for duration: [측정 구간 시간]
Enter value for report_name:
Report
ASH Report For ORCL/orcl
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
ORCL 1457022532 orcl 1 11.2.0.4.0 NO db01
CPUs SGA Size Buffer Cache Shared Pool ASH Buffer Size
---- ------------------ ------------------ ------------------ ------------------
2 597M (100%) 416M (69.6%) 160M (26.8%) 4.0M (0.7%)
Analysis Begin Time: 16-Dec-16 23:56:08
Analysis End Time: 17-Dec-16 00:06:08
Elapsed Time: 10.0 (mins)
Begin Data Source: V$ACTIVE_SESSION_HISTORY
End Data Source: V$ACTIVE_SESSION_HISTORY
Sample Count: 8
Average Active Sessions: 0.01
Avg. Active Session per CPU: 0.01
Report Target: None specified
Top User Events DB/Inst: ORCL/orcl (Dec 16 23:56 to 00:06)
Avg Active
Event Event Class % Event Sessions
----------------------------------- --------------- ---------- ----------
CPU + Wait for CPU CPU 12.50 0.00
-------------------------------------------------------------
Top Background Events DB/Inst: ORCL/orcl (Dec 16 23:56 to 00:06)
Avg Active
Event Event Class % Activity Sessions
----------------------------------- --------------- ---------- ----------
CPU + Wait for CPU CPU 87.50 0.01
-------------------------------------------------------------
Top Event P1/P2/P3 Values DB/Inst: ORCL/orcl (Dec 16 23:56 to 00:06)
No data exists for this section of the report.
-------------------------------------------------------------
Top Service/Module DB/Inst: ORCL/orcl (Dec 16 23:56 to 00:06)
Service Module % Activity Action % Action
-------------- ------------------------ ---------- ------------------ ----------
SYS$BACKGROUND UNNAMED 75.00 UNNAMED 75.00
MMON_SLAVE 12.50 Auto-Flush Slave A 12.50
SYS$USERS UNNAMED 12.50 UNNAMED 12.50
-------------------------------------------------------------
Top Client IDs DB/Inst: ORCL/orcl (Dec 16 23:56 to 00:06)
No data exists for this section of the report.
-------------------------------------------------------------
Top SQL Command Types DB/Inst: ORCL/orcl (Dec 16 23:56 to 00:06)
-> 'Distinct SQLIDs' is the count of the distinct number of SQLIDs
with the given SQL Command Type found over all the ASH samples
in the analysis period
Distinct Avg Active
SQL Command Type SQLIDs % Activity Sessions
---------------------------------------- ---------- ---------- ----------
INSERT 1 12.50 0.00
SELECT 1 12.50 0.00
-------------------------------------------------------------
Top Phases of Execution DB/Inst: ORCL/orcl (Dec 16 23:56 to 00:06)
Avg Active
Phase of Execution % Activity Sessions
------------------------------ ---------- ----------
SQL Execution 12.50 0.00
-------------------------------------------------------------
Top SQL with Top Events DB/Inst: ORCL/orcl (Dec 16 23:56 to 00:06)
Sampled #
SQL ID Planhash of Executions % Activity
----------------------- -------------------- -------------------- --------------
Event % Event Top Row Source % RwSrc
------------------------------ ------- --------------------------------- -------
36z7c4rvtpkw7 N/A 0 12.50
CPU + Wait for CPU 12.50 ** Row Source Not Available ** 12.50
** SQL Text Not Available **
3nkd3g3ju5ph1 2853959010 1 12.50
CPU + Wait for CPU 12.50 INDEX - RANGE SCAN 12.50
select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spar
e2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is nul
l and linkname is null and subname is null
-------------------------------------------------------------
Top SQL with Top Row Sources DB/Inst: ORCL/orcl (Dec 16 23:56 to 00:06)
Sampled #
SQL ID PlanHash of Executions % Activity
----------------------- -------------------- -------------------- --------------
Row Source % RwSrc Top Event % Event
---------------------------------------- ------- ----------------------- -------
36z7c4rvtpkw7 N/A 0 12.50
** Row Source Not Available ** 12.50 CPU + Wait for CPU 12.50
** SQL Text Not Available **
3nkd3g3ju5ph1 2853959010 1 12.50
INDEX - RANGE SCAN 12.50 CPU + Wait for CPU 12.50
select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spar
e2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is nul
l and linkname is null and subname is null
-------------------------------------------------------------
Top SQL using literals DB/Inst: ORCL/orcl (Dec 16 23:56 to 00:06)
No data exists for this section of the report.
-------------------------------------------------------------
Top Parsing Module/Action DB/Inst: ORCL/orcl (Dec 16 23:56 to 00:06)
No data exists for this section of the report.
-------------------------------------------------------------
Top PL/SQL Procedures DB/Inst: ORCL/orcl (Dec 16 23:56 to 00:06)
-> 'PL/SQL entry subprogram' represents the application's top-level
entry-point(procedure, function, trigger, package initialization
or RPC call) into PL/SQL.
-> 'PL/SQL current subprogram' is the pl/sql subprogram being executed
at the point of sampling . If the value is 'SQL', it represents
the percentage of time spent executing SQL for the particular
plsql entry subprogram
PLSQL Entry Subprogram % Activity
----------------------------------------------------------------- ----------
PLSQL Current Subprogram % Current
----------------------------------------------------------------- ----------
SYSMAN.EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS 12.50
SQL 12.50
-------------------------------------------------------------
Top Java Workload DB/Inst: ORCL/orcl (Dec 16 23:56 to 00:06)
No data exists for this section of the report.
-------------------------------------------------------------
Top Call Types DB/Inst: ORCL/orcl (Dec 16 23:56 to 00:06)
No data exists for this section of the report.
-------------------------------------------------------------
Top Sessions DB/Inst: ORCL/orcl (Dec 16 23:56 to 00:06)
-> '# Samples Active' shows the number of ASH samples in which the session
was found waiting for that particular event. The percentage shown
in this column is calculated with respect to wall clock time
and not total database activity.
-> 'XIDs' shows the number of distinct transaction IDs sampled in ASH
when the session was waiting for that particular event
-> For sessions running Parallel Queries, this section will NOT aggregate
the PQ slave activity into the session issuing the PQ. Refer to
the 'Top Sessions running PQs' section for such statistics.
Sid, Serial# % Activity Event % Event
--------------- ---------- ------------------------------ ----------
User Program # Samples Active XIDs
-------------------- ------------------------------ ------------------ --------
126, 1 62.50 CPU + Wait for CPU 62.50
SYS oracle@db01 (PSP0) 5/600 [ 1%] 0
125, 1051 12.50 CPU + Wait for CPU 12.50
SYS oracle@db01 (M000) 1/600 [ 0%] 0
132, 1 12.50 CPU + Wait for CPU 12.50
SYS oracle@db01 (MMON) 1/600 [ 0%] 0
135, 1221 12.50 CPU + Wait for CPU 12.50
SYS oracle@db01 (J000) 1/600 [ 0%] 0
-------------------------------------------------------------
Top Blocking Sessions DB/Inst: ORCL/orcl (Dec 16 23:56 to 00:06)
No data exists for this section of the report.
-------------------------------------------------------------
Top Sessions running PQs DB/Inst: ORCL/orcl (Dec 16 23:56 to 00:06)
No data exists for this section of the report.
-------------------------------------------------------------
Top DB Objects DB/Inst: ORCL/orcl (Dec 16 23:56 to 00:06)
No data exists for this section of the report.
-------------------------------------------------------------
Top DB Files DB/Inst: ORCL/orcl (Dec 16 23:56 to 00:06)
No data exists for this section of the report.
-------------------------------------------------------------
Top Latches DB/Inst: ORCL/orcl (Dec 16 23:56 to 00:06)
No data exists for this section of the report.
-------------------------------------------------------------
Activity Over Time DB/Inst: ORCL/orcl (Dec 16 23:56 to 00:06)
-> Analysis period is divided into smaller time slots
-> Top 3 events are reported in each of those slots
-> 'Slot Count' shows the number of ASH samples in that slot
-> 'Event Count' shows the number of ASH samples waiting for
that event in that slot
-> '% Event' is 'Event Count' over all ASH samples in the analysis period
Slot Event
Slot Time (Duration) Count Event Count % Event
-------------------- -------- ------------------------------ -------- -------
23:56:08 (52 secs) 1 CPU + Wait for CPU 1 12.50
23:58:00 (1.0 min) 1 CPU + Wait for CPU 1 12.50
00:00:00 (1.0 min) 2 CPU + Wait for CPU 2 25.00
00:01:00 (1.0 min) 1 CPU + Wait for CPU 1 12.50
00:03:00 (1.0 min) 1 CPU + Wait for CPU 1 12.50
00:04:00 (1.0 min) 1 CPU + Wait for CPU 1 12.50
00:05:00 (1.0 min) 1 CPU + Wait for CPU 1 12.50
-------------------------------------------------------------
End of Report
추적 및 탐색
1. dba_hist_system_event 조회, 다량으로 발생된 이벤트를 확인.
2. dba_hist_active_sess_history 조회, 해당 이벤트를 많이 대기한 세션을 확인.
3. 블로킹 세션 정보로 dba_hist_active_sess_history 재조회.
4. 블로킹 세션이 찾아지면 해당 세션이 그 시점에 어떤 작업을 수행 중이었는지 확인.
5. sql_id를 이용해 그 당시 SQL과 실행계획까지 확인. v$sql 및 v$sql_plan이 AWR에 저장되기 때문.