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 |
-- 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
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의 코디네이터에 대한 정보 |
--업무 시간에 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
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
---
- 강좌 URL : http://www.gurubee.net/lecture/4341
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.