재현 불가능한 문제의 실시간 분석
| 1 | 진단 팩(Diagnostics Pack)과 튜닝 팩(Tuning Pack)을 사용한 분석 방법 |
| 2 | 이 두 옵션을 사용하지 못할 때 분석 방법
※ Standard Edition에서는 위 두 옵션을 사용할 수 없다. |
- 관련 파라미터(11.1 이후): control_management_pack_access
SQL> SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'control_management_pack_access';
NAME VALUE
-------------------------------- ------------------------------
control_management_pack_access DIAGNOSTIC+TUNING
| VALUE | 내용 |
| DIAGNOSTIC+TUNING | 진단팩과 튜닝팩 모두 활성화 |
| DIAGNOSTIC | 진단팩만 활성화 |
| NONE | 진단팩과 튜닝팩 모두 비활성화 |
- 동적 성능 뷰
- 오라클 데이터베이스는 메모리 또는 데이터베이스 파일에 있는 일부 데이터 구조의 내용을 SQL로 조회 할 수 있는 뷰를 제공
- v$ 또는 gv$로 시작하는 뷰
| v$ 뷰 | 단일 인스턴스 동적 성능 뷰 |
| gv$ 뷰 | RAC에서의 글로벌 동적 성능 뷰 / v$뷰와 구조 동일, inst_id 칼럼으로 인스턴스 구별 |
- 관련 파라미터: timed_statistics
SQL> SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'timed_statistics';
NAME VALUE
----------------- ------------------------------
timed_statistics TRUE
>>> 시간 정보 사용 가능
SQL> SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'statistics_level';
NAME VALUE
------------------ ------------------------------
statistics_level TYPICAL
>>> TYPICAL 또는 ALL 이면 timed_statistics의 기본값이 TRUE
※ 위 두 파라미터 값을 변경하는 것은 비권장
SQL> SELECT * FROM v$osstat;
STAT_NAME VALUE OSSTAT_ID COMMENTS CUMULATIVE
--------------------------- --------- --------- ---------------------------------------------------------------- ----------
NUM_CPUS 16 0 Number of active CPUs NO
IDLE_TIME 6.3e+009 1 Time (centi-secs) that CPUs have been in the idle state YES
BUSY_TIME 91748353 2 Time (centi-secs) that CPUs have been in the busy state YES
USER_TIME 63104850 3 Time (centi-secs) spent in user code YES
SYS_TIME 28643121 4 Time (centi-secs) spent in the kernel YES
IOWAIT_TIME 6448213 5 Time (centi-secs) spent waiting for IO YES
AVG_IDLE_TIME 394451646 7 Average time (centi-secs) that CPUs have been in the idle state NO
AVG_BUSY_TIME 5608914 8 Average time (centi-secs) that CPUs have been in the busy state NO
AVG_USER_TIME 3818665 9 Average time (centi-secs) spent in user code NO
AVG_SYS_TIME 1664826 10 Average time (centi-secs) spent in the kernel NO
AVG_IOWAIT_TIME 313373 11 Average time (centi-secs) spent waiting for IO NO
OS_CPU_WAIT_TIME 283519300 13 Time (centi-secs) processes spent on the run queue to be schedul YES
RSRC_MGR_CPU_WAIT_TIME 620 14 Time (centi-secs) processes spent in the runnable state waiting YES
LOAD 6.0e-001 15 Number of processes running or waiting on the run queue NO
NUM_CPU_CORES 4 16 Number of CPU cores NO
NUM_VCPUS 4 18 NO
NUM_LCPUS 16 19 NO
PHYSICAL_MEMORY_BYTES 3.3e+010 1008 Physical memory size in bytes NO
VM_IN_BYTES 1.0e+013 1009 Bytes paged in due to virtual memory swapping YES
VM_OUT_BYTES 1.5e+012 1010 Bytes paged out due to virtual memory swapping YES
TCP_SEND_SIZE_MIN 4096 2000 TCP Send Buffer Min Size NO
TCP_SEND_SIZE_DEFAULT 16384 2001 TCP Send Buffer Default Size NO
TCP_SEND_SIZE_MAX 9.2e+018 2002 TCP Send Buffer Max Size NO
TCP_RECEIVE_SIZE_MIN 4096 2003 TCP Receive Buffer Min Size NO
TCP_RECEIVE_SIZE_DEFAULT 16384 2004 TCP Receive Buffer Default Size NO
TCP_RECEIVE_SIZE_MAX 9.2e+018 2005 TCP Receive Buffer Max Size NO
GLOBAL_SEND_SIZE_MAX 4194304 2006 Global send size max (sb_max) NO
GLOBAL_RECEIVE_SIZE_MAX 4194304 2007 Global receive size max (sb_max) NO
- 시간 모델 통계: v$sess_time_model
1) background elapsed time
2) background cpu time
3) RMAN cpu time (backup/restore)
1) DB time
2) DB CPU
2) connection management call elapsed time
2) sequence load elapsed time
2) sql execute elapsed time
2) parse time elapsed
3) hard parse elapsed time
4) hard parse (sharing criteria) elapsed time
5) hard parse (bind mismatch) elapsed time
3) failed parse elapsed time
4) failed parse (out of shared memory) elapsed time
2) PL/SQL execution elapsed time
2) inbound PL/SQL rpc elapsed time
2) PL/SQL compilation elapsed time
2) Java execution elapsed time
2) repeated bind elapsed time
SQL> SELECT * FROM v$sess_time_model WHERE sid = 3;
SID STAT_ID STAT_NAME VALUE
--------- --------- ---------------------------------------------------------------- ---------
3 3.6e+009 DB time 3367885
3 2.7e+009 DB CPU 2280000
3 4.2e+009 background elapsed time 0
3 2.5e+009 background cpu time 0
3 4.1e+009 sequence load elapsed time 0
3 1.4e+009 parse time elapsed 590368
3 372226525 hard parse elapsed time 117318
3 2.8e+009 sql execute elapsed time 1028826
3 2.0e+009 connection management call elapsed time 19093
3 1.8e+009 failed parse elapsed time 0
3 4.1e+009 failed parse (out of shared memory) elapsed time 0
3 3.1e+009 hard parse (sharing criteria) elapsed time 90843
3 268357648 hard parse (bind mismatch) elapsed time 90844
3 2.6e+009 PL/SQL execution elapsed time 141561
3 290749718 inbound PL/SQL rpc elapsed time 0
3 1.3e+009 PL/SQL compilation elapsed time 5186
3 751169994 Java execution elapsed time 0
3 1.2e+009 repeated bind elapsed time 411
3 2.4e+009 RMAN cpu time (backup/restore) 0
--세션이 시작된 이후 처리에 얼마나 많은 시간을 사용 했는지 보여주는 쿼리
SQL> WITH db_time AS (SELECT sid, value
FROM v$sess_time_model
WHERE sid = 3
AND stat_name ='DB time')
SELECT ses.stat_name AS statistic
,round(ses.value / 1E6, 3) AS seconds
,round(ses.value / nullif(tot.value, 0) * 1E2, 1) AS "%"
FROM v$sess_time_model ses, db_time tot
WHERE ses.sid = tot.sid
AND ses.stat_name <> 'DB time'
AND ses.value > 0
ORDER BY ses.value DESC;
STATISTIC SECONDS %
---------------------------------------------------------------- --------- ---------
DB CPU 2.28 67.7
sql execute elapsed time 1.029 30.5
parse time elapsed 0.59 17.5
PL/SQL execution elapsed time 0.142 4.2
hard parse elapsed time 0.117 3.5
hard parse (bind mismatch) elapsed time 0.091 2.7
hard parse (sharing criteria) elapsed time 0.091 2.7
connection management call elapsed time 0.019 0.6
PL/SQL compilation elapsed time 0.005 0.2
repeated bind elapsed time 0 0
- 이는 데이터베이스의 CPU 처리 시간만을 계측하기 때문에 총 소요 시간을 알기 위해서는 대기 클래스 정보 및 대기 이벤트 대한 정보가 필요
- 대기 클래스 및 대기 이벤트: v$event_name
- 데이터베이스 인스턴스에서 소모한 시간과 CPU 시간이 차이가 클 경우 성능 문제를 분석하기 위해서는 서버 프로세스가 무엇을 대기했는지 알아야 한다.(대기 이벤트)
SQL> SELECT * FROM v$event_name WHERE rownum < 12;
EVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
--------- --------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ------------- ----------- ----------------------------------------------------------------
0 2.5e+009 null event 1.9e+009 0 Other
1 3.5e+009 pmon timer duration 2.7e+009 6 Idle
2 3.9e+009 logout restrictor 3.9e+009 4 Concurrency
3 939791390 VKTM Logical Idle Wait 2.7e+009 6 Idle
4 1.5e+009 VKTM Init Wait for GSGA 2.7e+009 6 Idle
5 1.4e+009 IORM Scheduler Slave Idle Wait 2.7e+009 6 Idle
6 1.2e+009 Parameter File I/O blkno #blks read/write 1.7e+009 8 User I/O
7 866018717 rdbms ipc message timeout 2.7e+009 6 Idle
8 3.2e+009 remote db operation clientid operation timeout 2.0e+009 7 Network
9 2.2e+009 remote db file read clientid count intr 2.0e+009 7 Network
10 538064841 remote db file write clientid count intr 2.0e+009 7 Network
SQL> SELECT wait_class, count(*)
FROM v$event_name
GROUP BY rollup(wait_class)
ORDER BY wait_class;
WAIT_CLASS COUNT(*)
--------------------- ---------
Administrative 55
Application 17
Cluster 50
Commit 2
Concurrency 33
Configuration 24
Idle 95
Network 35
Other 736
Queueing 9
Scheduler 8
System I/O 30
User I/O 48
1142
| v$system_wait_class | 시스템 레벨의 대기 이벤트 클래스 |
| v$session_wait_class | 세션에 대한 대기 이벤트 클래스 |
| wait_class | 대기 클래스 구분 |
| total_waits | 데이터베이스 인스턴스, 세션, 컨테이너(12c)가 시작된 이후 대기 이벤트의 누적 발생 횟수 |
| time_waited | 데이터베이스 인스턴스, 세션, 컨테이너(12c)가 시작된 이후 대기시간을 1/100초 단위로 누적한 것 |
SQL> SELECT wait_class
, round(time_waited, 3) AS time_waited
, round(1E2 * ratio_to_report(time_waited) OVER (), 1) as "%"
FROM (
SELECT sid, wait_class, time_waited / 1E2 AS time_waited
FROM v$session_wait_class
WHERE total_waits > 0
UNION ALL
SELECT sid, 'CPU', value / 1E6
FROM v$sess_time_model
WHERE stat_name = 'DB CPU'
)
WHERE sid = 3
ORDER by 2 DESC;
WAIT_CLASS TIME_WAITED %
---------------------------------------------------------------- ----------- ---------
Idle 4.1e+006 100
CPU 2.28 0
User I/O 0.08 0
Network 0.04 0
SQL> SELECT event
, round(time_waited, 3) AS time_waited
, round(1E2 * ratio_to_report(time_waited) OVER (), 1) as "%"
FROM (
SELECT sid, event, time_waited_micro / 1E6 AS time_waited
FROM v$session_event
WHERE total_waits > 0
UNION ALL
SELECT sid, 'CPU', value / 1E6
FROM v$sess_time_model
WHERE stat_name = 'DB CPU'
)
WHERE sid = 3
ORDER by 2 DESC;
EVENT TIME_WAITED %
---------------------------------------------------------------- ----------- ---------
SQL*Net message from client 4.1e+006 100
CPU 2.28 0
db file sequential read 0.077 0
SQL*Net message to client 0.038 0
Disk file operations I/O 0 0
SQL> SELECT TRUNC(time_waited_micro/total_waits/1E3, 6) AS avg_wait_ms
FROM v$system_event
WHERE event = 'db file sequential read';
AVG_WAIT_MS
-----------
0.037143
SQL> SELECT wait_time_milli, wait_count, TRUNC(100 * ratio_to_report(wait_count) OVER (), 2) AS "%"
FROM v$event_histogram
WHERE event = 'db file sequential read';
WAIT_TIME_MILLI WAIT_COUNT %
--------------- ---------- ---------
1 359996708 99.77
2 572332 0.15
4 188217 0.05
8 51696 0.01
16 9444 0
32 1422 0
64 1094 0
128 1097 0
256 310 0
512 43 0
1024 1 0
2048 4 0
4096 1 0
8192 1 0
- 시스템 통계 및 세션 통계: v$sysstat, v$sesstat
- 특정 오퍼페이션이 수행된 횟수 또는 특정 기능을 통해 처리된 데이터의 양 같은 통계 정보를 기록
SQL> SELECT * FROM v$sysstat WHERE ROWNUM < 12;
STATISTIC# NAME CLASS VALUE STAT_ID
---------- ---------------------------------------------------------------- --------- --------- ---------
0 OS CPU Qt wait time 1 0 576270482
1 logons cumulative 1 502463 2.7e+009
2 logons current 1 197 3.1e+009
3 opened cursors cumulative 1 2.1e+009 85052502
4 opened cursors current 1 606 2.3e+009
5 user commits 1 6084531 582481098
6 user rollbacks 1 10626 3.7e+009
7 user calls 1 124811076 2.9e+009
8 recursive calls 1 2.6e+009 2.7e+009
9 recursive cpu usage 1 26504057 4.0e+009
10 pinned cursors current 1 161 2.8e+009
-- 데이터베이스 인스턴스가 시작된 이후의 로그온 횟수, 커밋 획수, 메모리 내 정렬 횟수
SQL> SELECT name, value
FROM v$sysstat
WHERE name IN ('logons cumulative', 'user commits', 'sorts (memory)');
NAME VALUE
---------------------------------------------------------------- ---------
logons cumulative 502471
user commits 6084571
sorts (memory) 68256921
-- 디스크 I/O 오페레이션을 통해 처리된 데이터의 양
SQL> SELECT name, value
FROM v$sysstat
WHERE name LIKE 'physical % total bytes';
NAME VALUE
---------------------------------------------------------------- ---------
physical read total bytes 1.8e+013
physical write total bytes 1.4e+012
- v$sesstat는 name 칼럼이 없기 때문에 v$statname와 조인해서 조회
--현재 할당된 PGA 메모리 크기, 세션이 생성된 후 할당된 PGA 메모리 최대 크기
SQL> SELECT sn.name, ss.value
FROM v$statname sn, v$sesstat ss
WHERE sn.statistic# = ss.statistic#
AND sn.name LIKE 'session pga memory%'
AND ss.sid = sys_context('userenv', 'sid');
NAME VALUE
---------------------------------------------------------------- ---------
session pga memory 27459480
session pga memory max 36962200
- 메트릭
- 동적 성능 뷰의 누적 통계 값을 기반으로 메트릭을 제공
SQL> SELECT * FROM v$metricname WHERE rownum < 10;
GROUP_ID GROUP_NAME METRIC_ID METRIC_NAME METRIC_UNIT
--------- ---------------------------------------------------------------- --------- ---------------------------------------------------------------- ----------------------------------------------------------------
0 Event Metrics 4 Total Wait Counts (Foreground) Waits
0 Event Metrics 3 Total Time Waited (Foreground) CentiSeconds
0 Event Metrics 2 Total Wait Counts Waits
0 Event Metrics 1 Total Time Waited CentiSeconds
0 Event Metrics 0 Number of Sessions Waiting (Event) Sessions
1 Event Class Metrics 1004 Total Wait Counts (Foreground) Waits
1 Event Class Metrics 1005 Total Time Waited (Foreground) CentiSeconds
1 Event Class Metrics 1002 Total Wait Counts Waits
1 Event Class Metrics 1003 Total Time Waited CentiSeconds
SQL> SELECT * FROM v$metricgroup ORDER BY group_id;
GROUP_ID NAME INTERVAL_SIZE MAX_INTERVAL >>> MAX_INTERVAL: 1/100초
--------- ---------------------------------------------------------------- ------------- ------------
0 Event Metrics 6000 1
1 Event Class Metrics 6000 60
2 System Metrics Long Duration 6000 60
3 System Metrics Short Duration 1500 12
4 Session Metrics Long Duration 6000 60
5 Session Metrics Short Duration 1500 1
6 Service Metrics 6000 60
7 File Metrics Long Duration 60000 6
9 Tablespace Metrics Long Duration 6000 0
10 Service Metrics (Short) 500 24
11 I/O Stats by Function Metrics 6000 60
12 Resource Manager Stats 6000 60
13 WCR metrics 6000 60
14 WLM PC Metrics 500 24
--메트릭의 현재 값 보기
SQL> SELECT begin_time, end_time, value, metric_unit
FROM v$metric
WHERE metric_name = 'Host CPU Usage Per Sec';
BEGIN_TIME END_TIME VALUE METRIC_UNIT
-------------------- -------------------- --------- ----------------------------------------------------------------
2017/11/09 16:31:29 2017/11/09 16:32:29 1.7e+001 CentiSeconds Per Second
2017/11/09 16:32:44 2017/11/09 16:32:59 1.1e+001 CentiSeconds Per Second
| sid, serial#, saddr, audsid | 세션 식별 |
| type | 세션 구분(BACKGROUND, USER) |
| login_time | 세션이 생성된 시간 |
| username, user# | 사용자 식별 |
| schemaname | 현재 사용 스키마 |
| service_name | 접속하는데 사용된 서비스 이름 |
| program | 애플리케이션 정보 |
| machine | 애플리케이션 머신 정보 |
| process | 애플리케이션의 프로세스 ID |
| osuser | 애플리케이션을 실행한 OS 사용자 |
| server | 서버 프로세스 유형(DEDICATED, SHARED, PSEUDO, POOLED, NONE) |
| paddr | 서버 프로세스 주소 |
| taddr | 현재 활성 트랙잭션의 주소 |
| status | 세션 상태(ACTIVE, INACTIVE, KILLED, SNIPED, CACHED) |
| last_call_et | 현재 세션 상태의 유지 시간(초 단위) |
| command | 실행한 SQL 구문의 유형 |
| sql_address, sql_hash_value, sql_id, sql_child_number | SQL과 관련된 커서 식별 |
| sql_exec_start | SQL이 시작된 시간 |
| sql_exec_id | SQL 실행 ID, 같은 커서가 초당 여러번 실행될 수 있기 때문에 필요 |
| prev_sql_address, prev_hash_value, prev_sql_id, prev_child_number | 직전에 수행한 커서를 식별 |
| prev_sql_exec_start | 이전 SQL이 시작된 시간 |
| prev_sql_exec_id | 이전 SQL 실행 ID |
| plsql_entry_object_id | PL/SQL 실행 시 호출된 최상위 레벨 프로그램 식별 |
| plsql_entry_subprogram_id | PL/SQL 실행 시 호출된 서브프로그램 식별 |
| plsql_object_id | 현재 실행 중인 프로그램 식별(SQL 실행 시 NULL) |
| plsql_subprogram_id | 현재 실행 중인 서브프로그램 식별(SQL 실행 시 NULL) |
| client_identifier, module, action, client_info | 애플케이션 세션 속성 설정 시 조회 가능 |
| stat | WATING 시 대기 상태, 아니면 CPU 사용 중(status의 ACTIVE와 동일) |
| event | 이벤트 이름 |
| wait_class, wait_class# | 대기 클래스 |
| p1text, p1, p1raw, p2text, p2, p2raw, p3text, p3, p3raw | 대기 이벤트의 상태 정보 |
| seconds_in_wait, wait_time_micro | 해당 대기 이벤트를 대기한 시간 |
| blocking_session_status | 세션이 다른 세션에 의해 블로킹 되었는지 표시(블로킹 시 VALID) |
| blocking_instance, blocking_session | 블로킹 시 블로킹하고 있는 세션 식별 |
| row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row# | 로우 락과 같이 특정 로우를 대기 중일 때 대기 중인 로우 식별 |
- 액티브 세션 히스토리(ASH)
- 세션의 상태에 대한 이력 정보 제공
- MMNL 백그라운드 프로세스가 1초 간격으로 샘플링 수행
| Sample ID | Timestamp | Session | SQL ID | Activity |
|---|
| 20 | 06:28:09 | 1 | gd90ygn1j4026 | CPU |
| 20 | 06:28:09 | 2 | 5m6mu5pd9w028 | CPU |
| 21 | 06:28:10 | 1 | gd90ygn1j4026 | CPU |
| 21 | 06:28:10 | 2 | 5m6mu5pd9w028 | CPU |
| 22 | 06:28:11 | 1 | gd90ygn1j4026 | User I/O |
| 22 | 06:28:11 | 2 | 5m6mu5pd9w028 | CPU |
| 23 | 06:28:12 | 1 | gd90ygn1j4026 | User I/O |
| 23 | 06:28:12 | 2 | 5m6mu5pd9w028 | CPU |
| 24 | 06:28:13 | 1 | 7ztv2z24kw0s0 | CPU |
| 24 | 06:28:13 | 2 | 5m6mu5pd9w028 | CPU |
| 25 | 06:28:14 | 2 | 5m6mu5pd9w028 | CPU |
| 27 | 06:28:16 | 1 | d9gdx5a4gc13y | CPU |
| 28 | 06:28:17 | 1 | 1uaz41wrxw03k | User I/O |
| 29 | 06:28:18 | 1 | 1uaz41wrxw03k | CPU |
| 30 | 06:28:19 | 1 | 1uaz41wrxw03k | User I/O |
| 31 | 06:28:20 | 1 | 1uaz41wrxw03k | User I/O |
- 샘플링 된 데이터는 v$active_session_history로 조회
- 액티브 세션 히스토리 버퍼
-- ASH 버퍼 크기 확인
SQL> SELECT pool, bytes
FROM v$sgastat
WHERE name = 'ASH buffers';
POOL BYTES
------------ ---------
shared pool 29360128
-- ASH 버퍼 저장 가능 시간 확인
SQL> SELECT max(sample_time) - min(sample_time) AS interval
FROM v$active_session_history;
INTERVAL
-----------
+000000006 07:22:42.263
- v$active_session_history 주요 칼럼
| sample_id | 샘플링 식별 |
| sample_time | MMNL 프로세스가 샘플을 수집한 시점의 타임스탬프 |
| session_state | 세션 상태(WAITING, ON CPU) |
| time_waited | session_state가 WAITING시 대기 시간(마이크로초 단위)
하나의 대기 이벤트가 두 개 이상의 샘플링에 걸쳐 있을 경우 가장 마지막 샘필링에 저장된 값이 실제 대개한 시간(이전 샘플링은 0) |
| sql_plan_hash_value | 실행 계획의 해시 값 |
| sql_plan_line_id, sql_plan_operation, sql_plan_options | 해당 시전에 수행했던 오퍼페이션 정보 |
| qc_instance_id, qc_session_id, qc_session_serial# | 병렬 실행 SQL의 코디네이터에 대한 정보 |
- v$active_session_history 조회 방법
- sample_time을 사용하여 조회 범위 설정
- 컨텍스트 정보를 제공하는 하나 이상의 칼럼(session_id, sql_id, program 등)을 기준으로 집계
- 샘플링 횟수를 카운트
--업무 시간에 DB_time 순으로 top 10 SQL 검색
SQL> SELECT activity_pct
,db_time
,sql_id
FROM ( SELECT round(100 * ratio_to_report(count(*)) OVER (), 1) AS activity_pct
,count(*) AS db_time
,sql_id
FROM v$active_session_history
WHERE sample_time BETWEEN to_timestamp('2017-11-09 09:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND to_timestamp('2017-11-09 18:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND sql_id IS NOT NULL
GROUP BY sql_id
ORDER BY count(*) DESC
)
WHERE rownum <= 10;
ACTIVITY_PCT DB_TIME SQL_ID
------------ --------- -------------
30.8 747 968q4f33a89pd
12.1 294 0na1v0nnadrda
7.2 174 bkhnb5yaasjpw
6.7 162 7bjbf28b1qf1q
6.6 161 c4dsnt467vhtr
2.1 51 cjbspmqcfn3p8
1.6 39 6ch1ny26yjatf
1.5 36 14ys3d7nmvxbv
1.4 33 104u7tya3hg55
1.4 33 fxgtjyvzb0pxm
- 엔터프라즈 관리자(EM) 12c 사용 시 ASH Analytics 이용
- ASH 보고서: ashrpt.sql, ashrpti.sql ($ORACLE_HOME/rdbms/admin/)
SQL> @ashrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1368679494 DCBIS 1 DCBIS
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: text
Type Specified: text
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 1368679494 1 DCBIS DCBIS kdcbdb1a
Defaults to current database
Using database id: 1368679494
Enter instance numbers. Enter 'ALL' for all instances in a
RAC cluster or explicitly specify list of instances (e.g., 1,2,3).
Defaults to current instance.
Using instance number(s): 1
ASH Samples in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Oldest ASH sample available: 02-Nov-17 09:10:39 [ 11690 mins in the past]
Latest ASH sample available: 10-Nov-17 12:00:30 [ 0 mins in the past]
Specify the timeframe to generate the ASH report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter begin time for report:
-- Valid input formats:
-- To specify absolute begin time:
-- [MM/DD[/YY]] HH24:MI[:SS]
-- Examples: 02/23/03 14:30:15
-- 02/23 14:30:15
-- 14:30:15
-- 14:30
-- To specify relative begin time: (start with '-' sign)
-- -[HH24:]MI
-- Examples: -1:15 (SYSDATE - 1 Hr 15 Mins)
-- -25 (SYSDATE - 25 Mins)
Defaults to -15 mins
Enter value for begin_time: 09:00
Report begin time specified: 09:00
Enter duration in minutes starting from begin time:
Defaults to SYSDATE - begin_time
Press Enter to analyze till current time
Enter value for duration: 5
Report duration specified: 5
Using 10-Nov-17 09:00:00 as report begin time
Using 10-Nov-17 09:05:00 as report end time
Specify Slot Width (using ashrpti.sql) for 'Activity Over Time' section
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- Explanation:
-- In the 'Activity Over Time' section of the ASH report,
-- the analysis period is divided into smaller slots
-- and top wait events are reported in each of those slots.
-- Default:
-- The analysis period will be automatically split upto 10 slots
-- complying to a minimum slot width of
-- 1 minute, if the source is V$ACTIVE_SESSION_HISTORY or
-- 5 minutes, if the source is DBA_HIST_ACTIVE_SESS_HISTORY.
Specify Slot Width in seconds to use in the 'Activity Over Time' section:
Defaults to a value as explained above:
Slot Width specified:
Specify Report Targets (using ashrpti.sql) to generate the ASH report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- Explanation:
-- ASH Report can accept "Report Targets",
-- like a particular SQL statement, or a particular SESSION,
-- to generate the report on. If one or more report targets are
-- specified, then the data used to generate the report will only be
-- the ASH samples that pertain to ALL the specified report targets.
-- Default:
-- If none of the report targets are specified,
-- then the target defaults to all activity in the database instance.
Specify SESSION_ID (eg: from V$SESSION.SID) report target:
Defaults to NULL:
SESSION report target specified:
Specify SQL_ID (eg: from V$SQL.SQL_ID) report target:
Defaults to NULL: (% and _ wildcards allowed)
SQL report target specified:
Specify WAIT_CLASS name (eg: from V$EVENT_NAME.WAIT_CLASS) report target:
[Enter 'CPU' to investigate CPU usage]
Defaults to NULL: (% and _ wildcards allowed)
WAIT_CLASS report target specified:
Specify SERVICE_HASH (eg: from V$ACTIVE_SERVICES.NAME_HASH) report target:
Defaults to NULL:
SERVICE report target specified:
Specify MODULE name (eg: from V$SESSION.MODULE) report target:
Defaults to NULL: (% and _ wildcards allowed)
MODULE report target specified:
Specify ACTION name (eg: from V$SESSION.ACTION) report target:
Defaults to NULL: (% and _ wildcards allowed)
ACTION report target specified:
Specify CLIENT_ID (eg: from V$SESSION.CLIENT_IDENTIFIER) report target:
Defaults to NULL: (% and _ wildcards allowed)
CLIENT_ID report target specified:
Specify PLSQL_ENTRY name (eg: "SYS.DBMS_LOB.*") report target:
Defaults to NULL: (% and _ wildcards allowed)
PLSQL_ENTRY report target specified:
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is ashrpt_1_1110_0905.txt. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name ashrpt_1_1110_0905.txt
Summary of All User Input
-------------------------
Format : TEXT
DB Id : 1368679494
Inst num : 1
Begin time : 10-Nov-17 09:00:00
End time : 10-Nov-17 09:05:00
Slot width : Default
Report targets : 0
Report name : ashrpt_1_1110_0905.txt
ASH Report For DCBIS/DCBIS
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
DCBIS 1368679494 DCBIS 1 11.2.0.2.0 NO kdcbdb1a
CPUs SGA Size Buffer Cache Shared Pool ASH Buffer Size
---- ------------------ ------------------ ------------------ ------------------
16 1,449M (100%) 304M (21.0%) 576M (39.7%) 28.0M (1.9%)
Analysis Begin Time: 10-Nov-17 09:00:00
Analysis End Time: 10-Nov-17 09:05:00
Elapsed Time: 5.0 (mins)
Begin Data Source: V$ACTIVE_SESSION_HISTORY
End Data Source: V$ACTIVE_SESSION_HISTORY
Sample Count: 26
Average Active Sessions: 0.09
Avg. Active Session per CPU: 0.01
Report Target: None specified
Top User Events DB/Inst: DCBIS/DCBIS (Nov 10 09:00 to 09:05)
Avg Active
Event Event Class % Event Sessions
----------------------------------- --------------- ---------- ----------
CPU + Wait for CPU CPU 73.08 0.06
null event Other 3.85 0.00
-------------------------------------------------------------
Top Background Events DB/Inst: DCBIS/DCBIS (Nov 10 09:00 to 09:05)
Avg Active
Event Event Class % Activity Sessions
----------------------------------- --------------- ---------- ----------
CPU + Wait for CPU CPU 15.38 0.01
os thread startup Concurrency 7.69 0.01
-------------------------------------------------------------
Top Event P1/P2/P3 Values DB/Inst: DCBIS/DCBIS (Nov 10 09:00 to 09:05)
No data exists for this section of the report.
-------------------------------------------------------------
Top Service/Module DB/Inst: DCBIS/DCBIS (Nov 10 09:00 to 09:05)
Service Module % Activity Action % Action
-------------- ------------------------ ---------- ------------------ ----------
SYS$USERS JDBC Thin Client 65.38 UNNAMED 65.38
SYS$BACKGROUND UNNAMED 15.38 UNNAMED 15.38
DCBIS httpd@pdbmon1x (TNS V1-V 7.69 UNNAMED 7.69
SYS$BACKGROUND KTSJ 3.85 KTSJ Coordinator 3.85
MMON_SLAVE 3.85 Auto-Flush Slave A 3.85
-------------------------------------------------------------
Top Client IDs DB/Inst: DCBIS/DCBIS (Nov 10 09:00 to 09:05)
No data exists for this section of the report.
-------------------------------------------------------------
Top SQL Command Types DB/Inst: DCBIS/DCBIS (Nov 10 09:00 to 09:05)
-> '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
---------------------------------------- ---------- ---------- ----------
SELECT 3 50.00 0.04
INSERT 3 11.54 0.01
ALTER SESSION 2 11.54 0.01
UPDATE 1 3.85 0.00
PL/SQL EXECUTE 1 3.85 0.00
-------------------------------------------------------------
Top Phases of Execution DB/Inst: DCBIS/DCBIS (Nov 10 09:00 to 09:05)
Avg Active
Phase of Execution % Activity Sessions
------------------------------ ---------- ----------
SQL Execution 65.38 0.06
PLSQL Execution 3.85 0.00
Parse 3.85 0.00
-------------------------------------------------------------
Top SQL with Top Events DB/Inst: DCBIS/DCBIS (Nov 10 09:00 to 09:05)
Sampled #
SQL ID Planhash of Executions % Activity
----------------------- -------------------- -------------------- --------------
Event % Event Top Row Source % RwSrc
------------------------------ ------- --------------------------------- -------
968q4f33a89pd 3487762668 10 38.46
CPU + Wait for CPU 38.46 ** Row Source Not Available ** 23.08
SELECT * FROM (SELECT A.TABLESPACE_NAME, ROUND(A.TOTAL_BYTES/1024/1024,1) AS TOT
AL_MB, ROUND(NVL(B.FREE_BYTES, 0)/1024/1024,1) AS FREE_MB, ROUND(NVL(B.FREE_BYTE
S, 0) * 100 / A.TOTAL_BYTES, 1) AS FREE_PCT FROM (SELECT TABLESPACE_NAME, SUM(BY
TES) AS TOTAL_BYTES FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A, (SELECT TAB
0pgs023t72bmb 2630486262 0 7.69
CPU + Wait for CPU 7.69 ** Row Source Not Available ** 7.69
** SQL Text Not Available **
7bjbf28b1qf1q 31388834 2 7.69
CPU + Wait for CPU 7.69 FIXED TABLE - FIXED INDEX 3.85
SELECT X.* FROM ( SELECT '' || RPAD(A1, 13, ' ') || '' || '[' || RPAD(
LPAD(' ', CEIL(A2 / 5)+1, '#') || ' ', 20, ' ') || ']' || '[' || LPAD(A2,
3, ' ') || '%]' || '[' || LPAD(ROUND(A4/1024,1), 6, ' ') || '/' || LPAD(
ROUND(A3/1024,1), 6, ' ') || ' GB]<BR>' AS TEXT , A2, A3, A4 FROM
14ys3d7nmvxbv N/A 1 3.85
CPU + Wait for CPU 3.85 ** Row Source Not Available ** 3.85
** SQL Text Not Available **
2sb1y6m7w1s5t 2525217753 1 3.85
null event 3.85 FIXED TABLE - FULL 3.85
INSERT INTO KSDBA.TBDB41L (INSTANCE_NUMBER, SID, SERIAL#, USERNAME, SERVER, SCHE
MANAME, OSUSER, PROCESS, MACHINE, PORT, TERMINAL, PROGRAM, MODULE, ACTION, LOGON
_TIME, IP) SELECT (SELECT INSTANCE_NUMBER FROM V$INSTANCE) AS INSTANCE_NUMBER, S
ID, SERIAL#, USERNAME, SERVER, SCHEMANAME, OSUSER, PROCESS, MACHINE, PORT, TERMI
-------------------------------------------------------------
Top SQL with Top Row Sources DB/Inst: DCBIS/DCBIS (Nov 10 09:00 to 09:05)
Sampled #
SQL ID PlanHash of Executions % Activity
----------------------- -------------------- -------------------- --------------
Row Source % RwSrc Top Event % Event
---------------------------------------- ------- ----------------------- -------
968q4f33a89pd 3487762668 10 38.46
** Row Source Not Available ** 23.08 CPU + Wait for CPU 23.08
SELECT * FROM (SELECT A.TABLESPACE_NAME, ROUND(A.TOTAL_BYTES/1024/1024,1) AS TOT
AL_MB, ROUND(NVL(B.FREE_BYTES, 0)/1024/1024,1) AS FREE_MB, ROUND(NVL(B.FREE_BYTE
S, 0) * 100 / A.TOTAL_BYTES, 1) AS FREE_PCT FROM (SELECT TABLESPACE_NAME, SUM(BY
TES) AS TOTAL_BYTES FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A, (SELECT TAB
FIXED TABLE - FIXED INDEX 11.54 CPU + Wait for CPU 11.54
HASH JOIN 3.85 CPU + Wait for CPU 3.85
0pgs023t72bmb 2630486262 0 7.69
** Row Source Not Available ** 7.69 CPU + Wait for CPU 7.69
** SQL Text Not Available **
7bjbf28b1qf1q 31388834 2 7.69
FIXED TABLE - FIXED INDEX 3.85 CPU + Wait for CPU 3.85
SELECT X.* FROM ( SELECT '' || RPAD(A1, 13, ' ') || '' || '[' || RPAD(
LPAD(' ', CEIL(A2 / 5)+1, '#') || ' ', 20, ' ') || ']' || '[' || LPAD(A2,
3, ' ') || '%]' || '[' || LPAD(ROUND(A4/1024,1), 6, ' ') || '/' || LPAD(
ROUND(A3/1024,1), 6, ' ') || ' GB]<BR>' AS TEXT , A2, A3, A4 FROM
** Row Source Not Available ** 3.85 CPU + Wait for CPU 3.85
14ys3d7nmvxbv N/A 1 3.85
** Row Source Not Available ** 3.85 CPU + Wait for CPU 3.85
** SQL Text Not Available **
2sb1y6m7w1s5t 2525217753 1 3.85
FIXED TABLE - FULL 3.85 null event 3.85
INSERT INTO KSDBA.TBDB41L (INSTANCE_NUMBER, SID, SERIAL#, USERNAME, SERVER, SCHE
MANAME, OSUSER, PROCESS, MACHINE, PORT, TERMINAL, PROGRAM, MODULE, ACTION, LOGON
_TIME, IP) SELECT (SELECT INSTANCE_NUMBER FROM V$INSTANCE) AS INSTANCE_NUMBER, S
ID, SERIAL#, USERNAME, SERVER, SCHEMANAME, OSUSER, PROCESS, MACHINE, PORT, TERMI
-------------------------------------------------------------
Top SQL using literals DB/Inst: DCBIS/DCBIS (Nov 10 09:00 to 09:05)
No data exists for this section of the report.
-------------------------------------------------------------
Top Parsing Module/Action DB/Inst: DCBIS/DCBIS (Nov 10 09:00 to 09:05)
Module Action % Activ Event
------------------------------ -------------------------------- ------- --------
JDBC Thin Client 3.85 CPU + Wa
-------------------------------------------------------------
Top PL/SQL Procedures DB/Inst: DCBIS/DCBIS (Nov 10 09:00 to 09:05)
-> '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
----------------------------------------------------------------- ----------
KSAPP.SP_KSCORE_V10_RESULT 7.69
KSAPP.SP_KSCORE_V10_RESULT 3.85
SQL 3.85
-------------------------------------------------------------
Top Java Workload DB/Inst: DCBIS/DCBIS (Nov 10 09:00 to 09:05)
No data exists for this section of the report.
-------------------------------------------------------------
Top Call Types DB/Inst: DCBIS/DCBIS (Nov 10 09:00 to 09:05)
Call Type Count % Activity Avg Active
---------------------------------------- ---------- ---------- ----------
V8 Bundled Exec 17 65.38 0.06
EXEC & FCH 2 7.69 0.01
-------------------------------------------------------------
Top Sessions DB/Inst: DCBIS/DCBIS (Nov 10 09:00 to 09:05)
-> '# 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
-------------------- ------------------------------ ------------------ --------
415,57699 42.31 CPU + Wait for CPU 42.31
DBMON JDBC Thin Client 11/300 [ 4%] 0
301, 1 7.69 CPU + Wait for CPU 7.69
SYS oracle@kdcbdb1a (PSP0) 2/300 [ 1%] 0
313,27651 7.69 CPU + Wait for CPU 7.69
ACSAPP JDBC Thin Client 2/300 [ 1%] 0
213,28167 3.85 CPU + Wait for CPU 3.85
DBMON httpd@pdbmon1x (TNS V1-V3) 1/300 [ 0%] 0
608,10005 3.85 CPU + Wait for CPU 3.85
DBMON httpd@pdbmon1x (TNS V1-V3) 1/300 [ 0%] 0
-------------------------------------------------------------
Top Blocking Sessions DB/Inst: DCBIS/DCBIS (Nov 10 09:00 to 09:05)
No data exists for this section of the report.
-------------------------------------------------------------
Top Sessions running PQs DB/Inst: DCBIS/DCBIS (Nov 10 09:00 to 09:05)
No data exists for this section of the report.
-------------------------------------------------------------
Top DB Objects DB/Inst: DCBIS/DCBIS (Nov 10 09:00 to 09:05)
No data exists for this section of the report.
-------------------------------------------------------------
Top DB Files DB/Inst: DCBIS/DCBIS (Nov 10 09:00 to 09:05)
No data exists for this section of the report.
-------------------------------------------------------------
Top Latches DB/Inst: DCBIS/DCBIS (Nov 10 09:00 to 09:05)
No data exists for this section of the report.
-------------------------------------------------------------
Activity Over Time DB/Inst: DCBIS/DCBIS (Nov 10 09:00 to 09:05)
-> 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
-------------------- -------- ------------------------------ -------- -------
09:00:00 (1.0 min) 7 CPU + Wait for CPU 5 19.23
null event 1 3.85
os thread startup 1 3.85
09:01:00 (1.0 min) 5 CPU + Wait for CPU 4 15.38
os thread startup 1 3.85
09:02:00 (1.0 min) 7 CPU + Wait for CPU 7 26.92
09:03:00 (1.0 min) 3 CPU + Wait for CPU 3 11.54
09:04:00 (1.0 min) 4 CPU + Wait for CPU 4 15.38
-------------------------------------------------------------
End of Report
Report written to ashrpt_1_1110_0905.txt
| v$sqlarea | SQL과 관련된 부모 커서 정보 |
| v$sql | SQL과 관련된 자식 커서 정보 |
| v$sqlstats | SQL과 관련된 부모 커서의 성능 통계 |
- v$sqlstats의 장점
- v$sql, v$sqlarea 보다 정보를 더 오래 보관, 라이브러리 캐시에서 밀려난 정보 확인 가능
- v$sqlstats 액세스 시 필요로 하는 자원이 더 적음
- v$sql 주요 칼럼
| address, hash_value, sql_id, child_number | 커서 식별 정보 |
| command_type | SQL 유형 정보 |
| sql_text, sql_fulltext | SQL 텍스트(sql_text: 첫 1,000자, sql_fulltext: 전체) |
| service | 세션을 오픈한 서비스 |
| parsing_schema_name, parsing_schema_id | 하드 파싱에 사용된 스키마 |
| module, action | 하드 파싱을 하는 동안 사용되었던 세션 속성 |
| program_id | SQL이 PL/SQL에서 실행된 경우 PL/SQL 프로그램의 ID |
| program_line# | SQL이 PL/SQL에서 실행된 경우 PL/SQL 프로그램에서 SQL이 위치한 라인 수 |
| loads | 발생한 하드 파싱의 횟수 |
| invalidations | 커서가 무효화된 횟수 |
| first_load_time | 하드 파싱이 최초로 일어난 시점 |
| last_load_time | 하드 파싱이 마지막으로 일어난 시점 |
| outline_category | 스토어드 아웃라인 카테고리 이름 |
| sql_profile | SQL 프로파일 |
| sql_patch | SQL 패치 |
| sql_plan_baseline | 실행 계획을 생성하는 동안 사용한 SQL 플랜 베이스라인 |
| plan_hash_value | 커서와 관련된 실행 계획의 해시 값 |
| parse_calls | 수행된 파스 횟수 |
| executions | 수행된 횟수 |
| fetches | 수행된 페치 횟수 |
| rows_processed | 처리된 로우 건수 |
| end_of_fetch_count | select 시 전체 로우를 페치한 횟수 |
| elapsed_time | 처리에 사용된 총 DB time |
| cpu_time | elapsed_time 중에서 CPU 처리 시간 |
| application_wait_time | 애플리케이션 대기 클래스에 속한 이벤트 대기 횟수 |
| concurrency_wait_time | Concurrency 대기 클래스에 속한 이벤트 대기 횟수 |
| cluster_wait_time | Cluster 대기 클래스에 속한 이벤트 대기 횟수 |
| user_io_wait_time | User I/O 대기 클래스에 속한 이벤트 대기 횟수 |
| plsql_exec_time | PL/SQL 엔진 수행 시간(마이크로초) |
| java_exec_time | 자바 가상 머신 수행 시간(마이크로초) |
| buffer_gets | 수행된 논리적 읽기 |
| disk_reads | 수행된 물리적 읽기 |
| direct_writes | 수행된 다이렉트 쓰기 |
| sorts | 수행된 정렬 횟수 |
- 실시간 모니터링
- 시간이 지남에 따라 커서 실행 정보를 보여줌
- 아래 3가지 경우만 실시간 모니터링 실시
- CPU와 디스크 I/O 시간을 합쳐서 5초 이상 실행되는 경우
- 병렬 처리를 사용하여 실행될 경우
- SQL에서 monitor 힌트를 사용한 경우
- v$sql_monitor, report_sql_monitor_list(dbms_sqltune)
- 진단 팩과 튜닝 팩을 이용한 분석
| 1 | 엔터프라이즈 관리자(EM)의 performance 페이지 |
| 2 | SQL*Plus에서 직접 동적 성능 뷰를 조회 |
| EM | performance Home 페이지 |
| SQL*Plus | v$metric_history |
SQL> @host_load_hist
DCBIS / 2017-11-10
BEGIN_TIME DURATION DB_FG_CPU DB_BG_CPU NON_DB_CPU OS_LOAD NUM_CPU
---------- -------- --------- --------- ---------- ------- -------
14:24:29 60.03 0.05 0.00 0.10 0.46 4
14:25:29 60.04 0.04 0.00 0.22 0.50 4
14:26:29 60.03 0.04 0.00 0.10 0.58 4
14:27:29 60.04 0.04 0.00 0.06 0.67 4
14:28:29 60.04 0.06 0.00 0.09 0.57 4
14:29:29 60.03 0.12 0.00 0.15 0.68 4
14:30:29 60.04 0.04 0.00 0.17 0.63 4
14:31:29 60.03 0.04 0.00 0.15 0.67 4
14:32:29 60.04 0.05 0.00 0.10 0.64 4
14:33:29 60.04 0.04 0.00 0.11 0.95 4
14:34:29 60.03 0.06 0.00 0.10 0.95 4
14:35:29 60.04 0.05 0.00 0.07 0.71 4
14:36:29 59.04 0.05 0.00 0.26 1.12 4
14:37:28 60.03 0.04 0.00 0.08 0.71 4
14:38:28 60.04 0.04 0.00 0.07 0.82 4
14:39:28 60.04 0.05 0.00 0.11 0.98 4
14:40:28 60.03 0.06 0.00 0.10 0.70 4
14:41:28 60.04 0.05 0.00 0.26 0.90 4
14:42:28 60.04 0.04 0.00 0.07 0.81 4
14:43:28 60.04 0.04 0.00 0.07 0.63 4
14:44:28 60.03 0.05 0.00 0.10 0.50 4
14:45:28 60.04 0.04 0.00 0.07 0.65 4
14:46:28 60.04 0.06 0.00 0.11 0.56 4
14:47:28 60.03 0.04 0.00 0.25 0.77 4
| EM | Top Activity 페이지 |
| SQL*Plus | v$active_session_history |
SQL> @ash_activity all all
DCBIS / 2017-11-10
TIME AvgActSes CPU% Sched% UsrIO% SysIO% Conc% Appl% Commit% Config% Admin% Net% Queue% Cluster% Other%
----- --------- ------ ------ ------ ------ ------ ------ ------- ------- ------ ------ ------ -------- ------
14:30 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:31 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:32 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:33 0.1 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:34 0.1 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:35 0.1 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:36 0.1 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:37 0.1 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:38 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:39 0.1 80.0 0.0 0.0 0.0 0.0 20.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:40 0.1 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:41 0.2 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:42 0.2 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:43 0.2 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:44 0.1 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:45 0.1 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:46 0.1 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:47 0.1 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:48 1.5 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:49 0.1 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:50 1.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:51 0.1 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:52 0.1 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:53 0.1 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:54 0.1 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
SQL> @ash_top_sqls 2017-11-10_14:55:00.000 2017-11-10_15:00:00.000 all all
Period Begin Period End Total Sample Count
------------------------------ ------------------------------ ------------------
2017-11-10_14:55:00.000000000 2017-11-10_15:00:00.000000000 17
Activity% DB Time CPU% UsrIO% Wait% SQL Id SQL Type
--------- ---------- ------ ------ ------ ------------- ----------------------------
58.8 10 100.0 0.0 0.0 968q4f33a89pd SELECT
11.8 2 100.0 0.0 0.0 2sb1y6m7w1s5t INSERT
11.8 2 100.0 0.0 0.0 71ux6xzn619ay SELECT
5.9 1 100.0 0.0 0.0 0pgs023t72bmb ALTER SESSION
5.9 1 100.0 0.0 0.0 0ws7ahf1d78qa SELECT
5.9 1 100.0 0.0 0.0 a0xbfqvcx3xfm INSERT
SQL> @ash_top_sessions 2017-11-10_14:55:00.000 2017-11-10_15:00:00.000
경 과: 00:00:00.00
Period Begin Period End Total Sample Count
------------------------------ ------------------------------ ------------------
2017-11-10_14:55:00.000000000 2017-11-10_15:00:00.000000000 21
Activity% DB Time CPU% UsrIO% Wait% Session Id Ses Serial User Name Program
--------- ---------- ------ ------ ------ ---------- ---------- -------------------- ----------------------------------
47.6 10 100.0 0.0 0.0 415 57699 DBMON JDBC Thin Client
9.5 2 100.0 0.0 0.0 1502 20179 KSAPP JDBC Thin Client
4.8 1 100.0 0.0 0.0 103 12933 KSAPP JDBC Thin Client
4.8 1 100.0 0.0 0.0 301 1 SYS oracle@kdcbdb1a (PSP0)
4.8 1 100.0 0.0 0.0 516 8201 ACSAPP JDBC Thin Client
4.8 1 100.0 0.0 0.0 516 8205 ACSAPP JDBC Thin Client
4.8 1 100.0 0.0 0.0 801 1 SYS oracle@kdcbdb1a (DIA0)
4.8 1 100.0 0.0 0.0 905 14595 ACSAPP JDBC Thin Client
4.8 1 0.0 0.0 100.0 1201 1 SYS oracle@kdcbdb1a (LGWR)
4.8 1 0.0 0.0 100.0 1202 1 SYS oracle@kdcbdb1a (CJQ0)
SQL> @ash_top_actions 2017-11-10_14:55:00.000 2017-11-10_15:00:00.000
Period Begin Period End Total Sample Count
------------------------------ ------------------------------ ------------------
2017-11-10_14:55:00.000000000 2017-11-10_15:00:00.000000000 21
Activity% DB Time CPU% UsrIO% Wait% Service Module Action
--------- ---------- ------ ------ ------ ---------------------- ---------------------- ---------------------
66.7 14 100.0 0.0 0.0 SYS$USERS JDBC Thin Client
19.0 4 50.0 0.0 50.0 SYS$BACKGROUND
9.5 2 100.0 0.0 0.0 DCBIS JDBC Thin Client
4.8 1 100.0 0.0 0.0 SYS$USERS
| EM | Search Session 메뉴 또는 Top Activity 페이지(Top session 테이블) |
| SQL*Plus | v$active_session_history |
SQL> @ash_activity 415 all
DCBIS / 2017-11-10
TIME AvgActSes CPU% Sched% UsrIO% SysIO% Conc% Appl% Commit% Config% Admin% Net% Queue% Cluster% Other%
----- --------- ------ ------ ------ ------ ------ ------ ------- ------- ------ ------ ------ -------- ------
14:48 0.5 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:51 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:52 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:53 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:54 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:55 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:56 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:57 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:58 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:59 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:00 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:01 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:02 0.0 50.0 0.0 50.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:03 0.1 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:04 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:05 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:06 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:07 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:08 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:18 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:19 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:20 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:21 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:22 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:23 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
| EM | Statistics 탭의 Summary 차트 또는 Search SQL |
| SQL*Plus | v$sqlarea, v$sql, v$sqlstats |
15:43:47 KSDBA@DCBIS[kdcbdb1a]> @ash_activity all 968q4f33a89pd
DCBIS / 2017-11-10
TIME AvgActSes CPU% Sched% UsrIO% SysIO% Conc% Appl% Commit% Config% Admin% Net% Queue% Cluster% Other%
----- --------- ------ ------ ------ ------ ------ ------ ------- ------- ------ ------ ------ -------- ------
14:51 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:52 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:53 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:54 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:55 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:56 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:57 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:58 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:59 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:00 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:01 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:02 0.0 50.0 0.0 50.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:03 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:04 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:05 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:06 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:07 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:08 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:18 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:19 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:20 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:21 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:22 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:23 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:24 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
SQL> @host_load 15
DCBIS / 2017-11-10
BEGIN_TIME DURATION DB_FG_CPU DB_BG_CPU NON_DB_CPU OS_LOAD NUM_CPU
---------- -------- --------- --------- ---------- ------- -------
16:25:29 60.03 0.04 0.00 0.23 1.08 4
16:26:29 59.04 0.07 0.00 0.11 0.44 4
16:27:28 60.04 0.05 0.00 0.09 0.88 4
16:28:28 60.04 0.05 0.00 0.09 1.28 4
16:29:28 60.03 0.06 0.00 0.10 1.07 4
16:30:28 60.04 0.05 0.00 0.09 0.46 4
16:31:28 60.04 0.07 0.00 0.26 0.58 4
16:32:28 60.04 0.08 0.00 0.09 0.68 4
16:33:28 60.04 0.05 0.00 0.11 0.61 4
16:34:28 60.03 0.06 0.00 0.12 0.57 4
16:35:28 60.03 0.05 0.00 0.09 0.63 4
16:36:28 60.04 0.06 0.00 0.26 0.70 4
16:37:29 60.04 0.07 0.00 0.10 0.80 4
16:38:29 60.04 0.07 0.00 0.11 0.78 4
16:39:29 60.04 0.06 0.00 0.11 0.72 4
16:24:12 KSDBA@DCBIS[kdcbdb1a]> @system_activity 15 20
DCBIS / 2017-11-10
Time AvgActSess Other% Queue% Net% Adm% Conf% Comm% Appl% Conc% Clust% SysIO% UsrIO% Sched% CPU%
-------- ---------- ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------
16:24:56 0.1 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 2.2 2.2 0.0 95.7
16:25:11 0.1 0.0 0.0 0.7 0.0 0.0 8.8 0.0 8.2 0.0 13.6 0.0 0.0 68.7
16:25:26 0.0 0.0 0.0 0.0 0.0 0.0 2.0 0.0 0.0 0.0 8.0 2.0 0.0 88.0
16:25:41 0.0 0.0 0.0 0.0 0.0 0.0 2.8 0.0 16.9 0.0 8.5 0.0 0.0 71.8
16:25:56 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 2.1 2.1 0.0 95.7
16:26:11 0.1 0.0 0.0 0.0 0.0 0.0 8.5 0.7 7.2 0.0 13.7 0.0 0.0 69.9
16:26:26 0.1 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 3.9 1.3 0.0 94.8
16:26:41 0.1 0.0 0.0 0.0 0.0 0.0 2.1 0.0 0.0 0.0 5.7 1.4 0.0 90.7
16:26:56 0.1 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 1.2 0.0 0.0 98.8
16:27:12 0.1 0.0 0.0 0.0 0.0 0.0 6.0 0.0 6.0 0.0 9.5 0.5 0.0 78.1
16:27:27 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 3.0 0.0 0.0 97.0
16:34:16 1.9 0.0 0.0 0.1 0.0 0.0 3.8 0.1 2.9 0.0 7.2 0.6 0.0 85.2
16:34:31 0.1 0.0 0.0 0.0 0.0 0.0 1.5 0.0 0.0 0.0 5.3 0.8 0.0 92.4
16:34:46 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 10.0 0.0 0.0 90.0
16:35:01 0.1 0.0 0.0 0.0 0.0 0.0 5.1 0.0 0.0 0.0 7.4 0.5 0.0 87.0
16:35:16 0.0 0.0 0.0 0.0 0.0 0.0 1.5 0.0 18.2 0.0 9.1 0.0 0.0 71.2
16:35:31 0.1 0.0 0.0 0.0 0.0 0.0 1.7 0.0 0.0 0.0 5.2 1.7 0.0 91.3
16:35:46 0.0 0.0 0.0 0.0 0.0 0.0 2.2 0.0 24.4 0.0 4.4 0.0 0.0 68.9
16:36:01 0.1 0.0 0.0 0.0 0.0 0.0 6.8 0.0 0.0 0.0 11.7 0.0 0.0 81.5
16:36:16 0.0 0.0 0.0 0.0 0.0 0.0 3.7 0.0 0.0 0.0 11.1 1.9 0.0 83.3
SQL> @time_model 15 2
DCBIS / 2017-11-10
Time Statistic AvgActSess Activity%
-------- -------------------------------------------------- ---------- ---------
16:29:07 DB time 0.1 88.6
.DB CPU 0.1 53.8
.sql execute elapsed time 0.1 77.7
background elapsed time 0.0 11.4
DCBIS / 2017-11-10
Time Statistic AvgActSess Activity%
-------- -------------------------------------------------- ---------- ---------
16:29:23 DB time 0.1 81.6
.DB CPU 0.0 52.1
.sql execute elapsed time 0.1 79.8
background elapsed time 0.0 18.4
SQL> @active_sessions 15 1 10
DCBIS / 2017-11-10
Time #Sessions #Logins SessionId Username Program Activity%
-------- --------- ------- --------------- -------------------- ---------------- ---------
16:31:13 203 6 1502 KSAPP JDBC Thin Client 22.2
1112 KSAPP JDBC Thin Client 21.6
1212 KSDBA sqlplus.exe 16.4
816 CPRBATCH JDBC Thin Client 8.5
1201 LGWR 7.0
103 KSAPP JDBC Thin Client 6.2
Unknown 4.8
301 PSP0 3.1
1109 KSAPP JDBC Thin Client 2.9
907 CPRBATCH JDBC Thin Client 1.8
Top-10 Total 94.4
SQL> @snapper ash=sql_id 15 1 all
Sampling SID all with interval 15 seconds, taking 1 snapshots...
-- Session Snapper v4.24 - by Tanel Poder ( http://blog.tanelpoder.com/snapper ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)
------------------------------------
ActSes %Thread | SQL_ID
------------------------------------
.05 (5%) | 968q4f33a89pd
.01 (1%) |
-- End of ASH snap 1, end=2017-11-10 16:43:53, seconds=15, samples_taken=85, AAS=.1
SQL> @snapper ash=module+action 15 1 all
Sampling SID all with interval 15 seconds, taking 1 snapshots...
-- Session Snapper v4.24 - by Tanel Poder ( http://blog.tanelpoder.com/snapper ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)
--------------------------------------------------------------------------
ActSes %Thread | MODULE | ACTION
--------------------------------------------------------------------------
.05 (5%) | JDBC Thin Client |
.01 (1%) | |
-- End of ASH snap 1, end=2017-11-10 16:52:52, seconds=15, samples_taken=87, AAS=.1
SQL>> @snapper ash=event 15 1 1414
Sampling SID 1414 with interval 15 seconds, taking 1 snapshots...
-- Session Snapper v4.24 - by Tanel Poder ( http://blog.tanelpoder.com/snapper ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)
--------------------------------------------------------
ActSes %Thread | EVENT
--------------------------------------------------------
.01 (1%) | db file sequential read
-- End of ASH snap 1, end=2017-11-10 16:52:02, seconds=15, samples_taken=95, AAS=0
SQL>> @snapper ash=sql_id+module+action 15 1 1414
Sampling SID 1414 with interval 15 seconds, taking 1 snapshots...
-- Session Snapper v4.24 - by Tanel Poder ( http://blog.tanelpoder.com/snapper ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)
--------------------------------------------------------------------------------------------
ActSes %Thread | SQL_ID | MODULE | ACTION
--------------------------------------------------------------------------------------------
.80 (80%) | 6gkdkq9wqg1tw | Orange for ORACLE DBA | 6.0.1 (Build:35,T)
-- End of ASH snap 1, end=2017-11-10 16:57:50, seconds=15, samples_taken=98, AAS=.8
SQL>> @sqlstats 6gkdkq9wqg1tw 15
DCBIS / 2017-11-10
경 과: 00:00:00.02
------------------------------------------------------------------------------------------
Interval (seconds) 15
Period 2017-11-10 16:59:01 - 2017-11-10 16:59:16
------------------------------------------------------------------------------------------
Identification
------------------------------------------------------------------------------------------
SQL Id 6gkdkq9wqg1tw
Execution Plan Hash Value 3701203732
------------------------------------------------------------------------------------------
Shared Cursors Statistics
------------------------------------------------------------------------------------------
Total Parses 1
Loads / Hard Parses 0
Invalidations 0
Cursor Size / Shared (bytes) 0
------------------------------------------------------------------------------------------
Activity by Time
------------------------------------------------------------------------------------------
Elapsed Time (seconds) 11.400
CPU Time (seconds) 1.390
Wait Time (seconds) 10.010
------------------------------------------------------------------------------------------
Activity by Waits
------------------------------------------------------------------------------------------
Application Waits (%) 0.064
Concurrency Waits (%) 0.000
Cluster Waits (%) 0.000
User I/O Waits (%) 60.928
Remaining Waits (%) 26.815
CPU (%) 12.193
------------------------------------------------------------------------------------------
Elapsed Time Breakdown
------------------------------------------------------------------------------------------
SQL Time (seconds) 11.400
PL/SQL Time (seconds) 0.000
Java Time (seconds) 0.000
------------------------------------------------------------------------------------------
Execution Statistics Total Per Execution Per Row
------------------------------------------------------------------------------------------
Elapsed Time (milliseconds) 11,400 11,400
CPU Time (milliseconds) 1,390 1,390
Executions 1 1
Buffer Gets 469,822 469,822
Disk Reads 469,863 469,863
Direct Writes 0 0
Rows 0 0
Fetches 0 0
Average Fetch Size
------------------------------------------------------------------------------------------
Other Statistics
------------------------------------------------------------------------------------------
Executions that Fetched All Rows (%) 0
Serializable Aborts 0
------------------------------------------------------------------------------------------