ASH 보고서 출력하기
1. DB ID와 INSTANCE NUMBER 조회
SELECT 'DB_ID : ' || DBID AS COL1 FROM V$DATABASE
UNION ALL
SELECT 'INSTANCE_NUMBER : ' || INSTANCE_NUMBER FROM V$INSTANCE; /*
DB_ID : 2909839947
INSTANCE_NUMBER : 1
*/
2. 10분전 부터 현재까지 ASH 보고서 출력
SELECT * FROM TABLE( DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT
( 2909839947, 1, SYSDATE - 1/24/60*10, SYSDATE )) ;
* 관련 파라미터
1. _ash_sampling_interval
- Time interval between two successive Active Session samples in millisecs
- 값 : 1000 (= 1초 )
2. _ash_sample_all
- To enable or disable sampling every connected session including ones waiting for idle waits
- 값 : FALSE ( TRUE이면 모든 활동 세션의 정보 저장 )
3. _ash_size
- To set the size of the in-memory Active Session History buffers
- 1048618 ( 1MB )
4. _ash_eflush_trigger
- The percentage above which if the in-memory ASH is full the emergency flusher
will be triggered
- 값 : 66
5. _ash_disk_filter_ratio
- Ratio of the number of in-memory samples to the number of samples actually written to disk
- 값 : 10 ( 활동세션 이력 정보에서 10%만 디스크에 저장하기)
★ sga 전체크기 0.1%가 8MB 미만이면 ASH 버퍼를 8MB를 확보한다.
ASH 버퍼(8MB)의 66%(_ash_eflush_trigger)가 사용되면 저장된 샘플중 10%(_ash_disk_filter_ratio)
만큼 Disk에 기록함
필자의 테스트 DB 파라메터 _ash_size 값은 1MB이나 아래 ASH 보고서를 출력하면 8MB로 셋팅되어있음
2,048 MB * 0.1% = 2 MB
3. ASH 보고서 분석
3.1) Top User Events
3.2) Top Background Events
3.3) Top Event P1/P2/P3 Values
3.4) Top Containers
3.5) Top Service/Module
3.6) Top Client IDs
3.7) Top SQL Command Types
3.8) Top Phases of Execution
3.9) Top SQL with Top Events
3.10) Top SQL with Top Row
3.11) Top SQL using literals
3.12) Top Parsing Module/Action
3.13) Top PL/SQL Procedures
3.14) Top Java Workload
3.15) Top Call Types
3.16) Top Sessions
3.17) Top Blocking Sessions
3.18) Top Sessions running PQs
3.19) Top DB Objects
3.20) Top DB Files
3.21) Top Latches
ASH Report For ORCLCDB/ORCLCDB
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
ORCLCDB 2909839947 ORCLCDB 1 19.0.0.0.0 NO oracle-19.3
CPUs SGA Size Buffer Cache Shared Pool ASH Buffer Size
---- ------------------ ------------------ ------------------ ------------------
4 2,048M (100%) 592M (28.9%) 832M (40.6%) 8.0M (0.4%)
In-memory Area Size
------------------
0.0M (0.0%)
Analysis Begin Time: 10-3월 -25 13:57:01
Analysis End Time: 10-3월 -25 14:07:01
Elapsed Time: 10.0 (mins)
Begin Data Source: V$ACTIVE_SESSION_HISTORY
End Data Source: V$ACTIVE_SESSION_HISTORY
Sample Count: 38
Average Active Sessions: 0.06
Avg. Active Session per CPU: 0.02
Report Target: None specified
Top User Events DB/Inst: ORCLCDB/ORCLCDB (3월 10 13:57 to 14:07)
Avg Active
Event Event Class % Event Sessions
----------------------------------- --------------- ---------- ----------
CPU + Wait for CPU CPU 31.58 0.02
Failed Logon Delay Other 13.16 0.01
db file sequential read User I/O 2.63 0.00
-------------------------------------------------------------
Top Background Events DB/Inst: ORCLCDB/ORCLCDB (3월 10 13:57 to 14:07)
Avg Active
Event Event Class % Activity Sessions
----------------------------------- --------------- ---------- ----------
CPU + Wait for CPU CPU 28.95 0.02
db file async I/O submit System I/O 13.16 0.01
control file parallel write System I/O 5.26 0.00
db file scattered read User I/O 2.63 0.00
log file parallel write System I/O 2.63 0.00
-------------------------------------------------------------
Top Event P1/P2/P3 Values DB/Inst: ORCLCDB/ORCLCDB (3월 10 13:57 to 14:07)
Event % Event P1 Value, P2 Value, P3 Value % Activity
------------------------------ ------- ----------------------------- ----------
Parameter 1 Parameter 2 Parameter 3
-------------------------- -------------------------- --------------------------
db file async I/O submit 13.16 "4","0","0" 7.89
requests interrupt timeout
"5","0","0" 2.63
"6","0","0" 2.63
control file parallel write 5.26 "1","3","1" 5.26
files block# requests
db file scattered read 2.63 "10","40561","7" 2.63
file# block# blocks
db file sequential read 2.63 "1","302","1" 2.63
file# block# blocks
log file parallel write 2.63 "1","50","1" 2.63
files blocks requests
-------------------------------------------------------------
Top Containers DB/Inst: ORCLCDB/ORCLCDB (3월 10 13:57 to 14:07)
Container Name Count % Activity Avg Active
---------------------------------------- ---------- ---------- ----------
CDB$ROOT 37 97.37 0.06
ORCLPDB1 1 2.63 0.00
-------------------------------------------------------------
Top Service/Module DB/Inst: ORCLCDB/ORCLCDB (3월 10 13:57 to 14:07)
Service Module % Activity Action % Action
-------------- ------------------------ ---------- ------------------ ----------
SYS$BACKGROUND UNNAMED 34.21 UNNAMED 34.21
ORCLCDB Orange for ORACLE DBA 34.21 6.0.2 (Build:133,S 34.21
SYS$BACKGROUND MMON_SLAVE 18.42 Auto-Flush Slave A 5.26
Automatic Report F 5.26
Auto ADDM Slave Ac 2.63
ORCLCDB OrangeMain.exe 13.16 UNNAMED 13.16
-------------------------------------------------------------
Top Client IDs DB/Inst: ORCLCDB/ORCLCDB (3월 10 13:57 to 14:07)
No data exists for this section of the report.
-------------------------------------------------------------
Top SQL Command Types DB/Inst: ORCLCDB/ORCLCDB (3월 10 13:57 to 14:07)
-> '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 15 44.74 0.03
INSERT 1 2.63 0.00
PL/SQL EXECUTE 1 2.63 0.00
-------------------------------------------------------------
Top Phases of Execution DB/Inst: ORCLCDB/ORCLCDB (3월 10 13:57 to 14:07)
Avg Active
Phase of Execution % Activity Sessions
------------------------------ ---------- ----------
SQL Execution 31.58 0.02
Parse 28.95 0.02
Hard Parse 26.32 0.02
Connection Management 13.16 0.01
PLSQL Compilation 2.63 0.00
-------------------------------------------------------------
Top SQL with Top Events DB/Inst: ORCLCDB/ORCLCDB (3월 10 13:57 to 14:07)
SQL ID FullPlanhash Planhash
----------------------- -------------------- --------------------
Sampled #
of Executions % Activity Event
-------------------- -------------- ------------------------------
% Event Top Row Source % RwSrc
------- --------------------------------- -------
Container Name
-------------------------------------------
bcbbmfbk6202g 2196912140 101226116
1 5.26 CPU + Wait for CPU
2.63 FIXED TABLE - FULL 2.63
select object_name, object_type from all_objects where upper(owner) = upper(:
owner) and object_type in ('TABLE','VIEW')
CDB$ROOT
db file sequential read
2.63 ** Row Source Not Available ** 2.63
0z4v43cp176qw 3808068041 3726637190
0 2.63 CPU + Wait for CPU
2.63 ** Row Source Not Available ** 2.63
select value from v$mystat where statistic# in (9,159,163,172,313,1879,1880,1881
,1889,1890) order by statistic#
CDB$ROOT
1m9jy36ujw5p4 1345392032 320056772
1 2.63 CPU + Wait for CPU
2.63 FIXED TABLE - FULL 2.63
SELECT NVL(SUM(TO_NUMBER(VALUE)), 1000) FROM V$PARAMETER WHERE NAME = '_ash_samp
ling_interval'
CDB$ROOT
1mzd13t2rt257 713702979 991216238
0 2.63 CPU + Wait for CPU
2.63 ** Row Source Not Available ** 2.63
SELECT count(*), min(ash.sample_time), max(ash.sample_time) FROM (SELEC
T unified_ash.* FROM ( ( SELECT :local_awrdbid as dbid, inst.instance_
number, 0 as snap_id, a.con_id, a.con_dbid, nvl(p.name, 'UNK
NOWN DBID ' || a.con_dbid) as pdb_name, a.sample_id, a.sample_time,
CDB$ROOT
30p3mh3brvpt9 558795990 2244669142
1 2.63 CPU + Wait for CPU
2.63 TABLE ACCESS - BY INDEX ROWID BAT 2.63
SELECT snap_id , SQL_ID, PLAN_HASH_VALUE, CON_DBID FROM (SELECT /*+ use_hash(t
1 t2) */ t2.snap_id , t1.SQLID_KEWRSPE SQL_ID, t1.PLANHASH_KEWRSPE PLAN_HASH_V
ALUE, t1.CON_DBID_KEWRSPE CON_DBID FROM X$KEWRTSQLPLAN t1, WRH$_SQL_PLAN t2
WHERE t2.dbid(+) = :dbid AND t2.SQL_ID(+) = t1.SQLID_KEWRSPE AND t2.PLAN_HAS
CDB$ROOT
-------------------------------------------------------------
Top SQL with Top Row SourcesDB/Inst: ORCLCDB/ORCLCDB (3월 10 13:57 to 14:07)
SQL ID FullPlanHash PlanHash
----------------------- -------------------- --------------------
Sampled #
of Executions % Activity
-------------------- --------------
Row Source % RwSrc Top Event % Event
---------------------------------------- ------- ----------------------- -------
Container Name
-------------------------------------------
bcbbmfbk6202g 2196912140 101226116
1 5.26
FIXED TABLE - FULL 2.63 CPU + Wait for CPU 2.63
select object_name, object_type from all_objects where upper(owner) = upper(:
owner) and object_type in ('TABLE','VIEW')
CDB$ROOT
** Row Source Not Available ** 2.63 db file sequential read 2.63
0z4v43cp176qw 3808068041 3726637190
0 2.63
** Row Source Not Available ** 2.63 CPU + Wait for CPU 2.63
select value from v$mystat where statistic# in (9,159,163,172,313,1879,1880,1881
,1889,1890) order by statistic#
CDB$ROOT
1m9jy36ujw5p4 1345392032 320056772
1 2.63
FIXED TABLE - FULL 2.63 CPU + Wait for CPU 2.63
SELECT NVL(SUM(TO_NUMBER(VALUE)), 1000) FROM V$PARAMETER WHERE NAME = '_ash_samp
ling_interval'
CDB$ROOT
1mzd13t2rt257 713702979 991216238
0 2.63
** Row Source Not Available ** 2.63 CPU + Wait for CPU 2.63
SELECT count(*), min(ash.sample_time), max(ash.sample_time) FROM (SELEC
T unified_ash.* FROM ( ( SELECT :local_awrdbid as dbid, inst.instance_
number, 0 as snap_id, a.con_id, a.con_dbid, nvl(p.name, 'UNK
NOWN DBID ' || a.con_dbid) as pdb_name, a.sample_id, a.sample_time,
CDB$ROOT
30p3mh3brvpt9 558795990 2244669142
1 2.63
TABLE ACCESS - BY INDEX ROWID BATCHED 2.63 CPU + Wait for CPU 2.63
SELECT snap_id , SQL_ID, PLAN_HASH_VALUE, CON_DBID FROM (SELECT /*+ use_hash(t
1 t2) */ t2.snap_id , t1.SQLID_KEWRSPE SQL_ID, t1.PLANHASH_KEWRSPE PLAN_HASH_V
ALUE, t1.CON_DBID_KEWRSPE CON_DBID FROM X$KEWRTSQLPLAN t1, WRH$_SQL_PLAN t2
WHERE t2.dbid(+) = :dbid AND t2.SQL_ID(+) = t1.SQLID_KEWRSPE AND t2.PLAN_HAS
CDB$ROOT
-------------------------------------------------------------
Top SQL using literals DB/Inst: ORCLCDB/ORCLCDB (3월 10 13:57 to 14:07)
No data exists for this section of the report.
-------------------------------------------------------------
Top Parsing Module/Action DB/Inst: ORCLCDB/ORCLCDB (3월 10 13:57 to 14:07)
Module Action % Activ
------------------------------ -------------------------------- -------
Event % Event
------------------------------ ----------
Orange for ORACLE DBA 6.0.2 (Build:133,S) 28.95
CPU + Wait for CPU 26.32
6.0.2 (Build:133,S)
db file sequential read 2.63
-------------------------------------------------------------
Top PL/SQL Procedures DB/Inst: ORCLCDB/ORCLCDB (3월 10 13:57 to 14:07)
-> '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
----------------------------------------------------------------- ----------
Container Name
-------------------------------------------
SYS.DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT 23.68
SQL 23.68
CDB$ROOT
SYS.DBMS_AUTO_REPORT_INTERNAL.I_SAVE_REPORT 5.26
SQL 5.26
CDB$ROOT
SYS.PRVT_HDM.EXECUTE_SLAVE_ACTIONS 2.63
SQL 2.63
CDB$ROOT
-------------------------------------------------------------
Top Java Workload DB/Inst: ORCLCDB/ORCLCDB (3월 10 13:57 to 14:07)
No data exists for this section of the report.
-------------------------------------------------------------
Top Call Types DB/Inst: ORCLCDB/ORCLCDB (3월 10 13:57 to 14:07)
Call Type Count % Activity Avg Active
---------------------------------------- ---------- ---------- ----------
LOB/FILE operations 9 23.68 0.02
OAUTH 5 13.16 0.01
V8 Bundled Exec 2 5.26 0.00
LOGOFF 1 2.63 0.00
-------------------------------------------------------------
Top Sessions DB/Inst: ORCLCDB/ORCLCDB (3월 10 13:57 to 14:07)
-> '# 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
-------------------- ------------------------------ ------------------ --------
259,49039 26.32 CPU + Wait for CPU 26.32
SYS OrangeMain.exe 10/600 [ 2%] 0
360,62942 13.16 db file async I/O submit 13.16
SYS oracle@oracle-19.3 (DBW0) 5/600 [ 1%] 0
1,35667 7.89 CPU + Wait for CPU 7.89
SYS oracle@oracle-19.3 (PSP0) 3/600 [ 1%] 0
136,35039 7.89 CPU + Wait for CPU 7.89
SYS oracle@oracle-19.3 (M004) 3/600 [ 1%] 1
16,29399 5.26 CPU + Wait for CPU 5.26
SYS oracle@oracle-19.3 (CJQ0) 2/600 [ 0%] 0
-------------------------------------------------------------
Top Blocking Sessions DB/Inst: ORCLCDB/ORCLCDB (3월 10 13:57 to 14:07)
No data exists for this section of the report.
-------------------------------------------------------------
Top Sessions running PQs DB/Inst: ORCLCDB/ORCLCDB (3월 10 13:57 to 14:07)
-> This section aggregates all the work done by the PQ slaves into
the session issuing the parallel query.
Sid,Srl# (Inst) % Activity SQL ID Event % Event
--------------- ---------- ------------- ----------------------------- --------
User Program
-------------------- ------------------------------
259,49039(1) 2.63 aw34t4fshvrwm CPU + Wait for CPU 2.63
UserID:
SELECT PLS.OWNER, PLS.OBJECT_TYPE, PLS.OBJECT_NAME, PLS.PROCEDURE_NAME, PLS.OVER
LOAD FROM SYS.CDB_PROCEDURES PLS WHERE :B3 = OBJECT_ID AND :B2 = PLS.CON_ID AND
:B1 = SUBPROGRAM_ID
-------------------------------------------------------------
Top DB Objects DB/Inst: ORCLCDB/ORCLCDB (3월 10 13:57 to 14:07)
-> With respect to Application, Cluster, User I/O, buffer busy waits and
In-Memory DB events only.
-> Tablespace name is not available for reports generated from the root PDB
of a consolidated database.
-> When ** MISSING ** occurs, some of the object attributes may not be available
Object ID % Activity Event % Event
--------------- ---------- ------------------------------ ----------
Object Name (Type) Tablespace
----------------------------------------------------- --------------------------
Container Name
-------------------------------------------
524 2.63 db file sequential read 2.63
SYS.I_CODEAUTH1 (INDEX) N/A
CDB$ROOT
423 2.63 db file scattered read 2.63
SYS.SMON_SCN_TIME (TABLE) N/A
ORCLPDB1
-------------------------------------------------------------
Top DB Files DB/Inst: ORCLCDB/ORCLCDB (3월 10 13:57 to 14:07)
-> With respect to Cluster and User I/O events only.
File ID % Activity Event % Event
--------------- ---------- ------------------------------ ----------
File Name Tablespace
----------------------------------------------------- -------------------------
10 2.63 db file scattered read 2.63
/u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf SYSAUX
-------------------------------------------------------------
Top Latches DB/Inst: ORCLCDB/ORCLCDB (3월 10 13:57 to 14:07)
No data exists for this section of the report.
-------------------------------------------------------------
Activity Over Time DB/Inst: ORCLCDB/ORCLCDB (3월 10 13:57 to 14:07)
-> 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
-------------------- -------- ------------------------------ -------- -------
13:58:00 (1.0 min) 3 CPU + Wait for CPU 2 5.26
db file sequential read 1 2.63
14:00:00 (1.0 min) 5 CPU + Wait for CPU 5 13.16
14:01:00 (1.0 min) 1 control file parallel write 1 2.63
14:02:00 (1.0 min) 6 Failed Logon Delay 5 13.16
CPU + Wait for CPU 1 2.63
14:04:00 (1.0 min) 13 CPU + Wait for CPU 12 31.58
control file parallel write 1 2.63
14:05:00 (1.0 min) 9 db file async I/O submit 5 13.16
CPU + Wait for CPU 2 5.26
db file scattered read 1 2.63
14:06:00 (1.0 min) 1 CPU + Wait for CPU 1 2.63
-------------------------------------------------------------
End of Report
'ORACLE > DBA' 카테고리의 다른 글
| OBJECT INVALID 체크 (0) | 2024.11.26 |
|---|---|
| 소문자 테이블 문제 (0) | 2024.11.23 |
| 계정 프로파일 ( PROFILE ) (1) | 2024.11.19 |
| GLOBAL INDEX가 있는 PARTITION 삭제 or TRUNCATE 방법 (0) | 2024.11.17 |
| FUNCTION BASED(함수기반) INDEX 컬럼명 조회 (0) | 2024.10.24 |