Nội dung:
- Giới thiệu
- V$ACTIVE_SESSION_HISTORY
- DBA_HIST_ACTIVE_SESS_HISTORY
- Trang Hiệu suất của Enterprise Manager
- Báo cáo ASH
- SQL Developer and ASH Reports
- ASH Viewer
Giới thiệu
Các công cụ như Statspack , AWR , ADDM và SQL Trace đều rất hữu ích để thu thập thông tin sự kiện chờ trong quá trình tối ưu, nhưng chúng có xu hướng tập trung vào việc nhìn lại những gì đã xảy ra hơn là những gì hiện đang xảy ra.Các view [G]V$ cung cấp rất nhiều thông tin trong thời gian thực, nhưng có thể khó khăn đối với người mới bắt đầu và cả những người có kinh nghiệm để sử dụng tốt thông tin này.
Từ Oracle Database 10g đã giới thiệu Lịch sử phiên hoạt động (ASH) như một phần của gói Diagnostics và Tuning Pack. Nó lấy mẫu thông tin từ các view [G]V$ cho phép bạn xem thông tin hiện tại và lịch sử về các phiên hoạt động trên cơ sở dữ liệu.
Là một phần của Diagnostics and Tuning Pack có nghĩa là ASH chỉ có sẵn dưới dạng tùy chọn trả phí trên Oracle Database Enterprise Edition.
V$ACTIVE_SESSION_HISTORY
Các mẫu thông tin về sự kiện chờ đợi được lấy một lần mỗi giây và được cung cấp bằng view V$ACTIVE_SESSION_HISTORY . Phiên hoạt động là phiên đang chờ trên CPU hoặc bất kỳ sự kiện nào không thuộc lớp chờ "Idle" tại thời điểm lấy mẫu. Thông tin mẫu được ghi vào một bộ đệm xoay vòng trong SGA, do đó, hoạt động cơ sở dữ liệu càng lớn thì thời gian thông tin sẽ có sẵn càng ít.
View V$ACTIVE_SESSION_HISTORY về cơ bản là một bảng dữ kiện, có thể được liên kết với một dữ liệu khác để cung cấp thống kê cụ thể hơn bao gồm câu lệnh SQL, kế hoạch thực thi (excution plan), đối tượng, sự kiện chờ, phiên, mô-đun, hành động, số nhận dạng khách hàng, dịch vụ và người tiêu dùng nhóm, v.v. Điều này làm cho nó trở nên cực kỳ linh hoạt để xác định những phiên hoạt động đang làm hoặc đã thực hiện. Ví dụ: nếu tôi muốn xem hoạt động chính trên cơ sở dữ liệu trong 5 phút qua, tôi có thể sử dụng truy vấn sau.
SELECT NVL(a.event, 'ON CPU') AS event, COUNT(*) AS total_wait_time FROM v$active_session_history a WHERE a.sample_time > SYSDATE - 5/(24*60) -- 5 phút GROUP BY a.event ORDER BY total_wait_time DESC; EVENT TOTAL_WAIT_TIME ---------------------------------------------------------------- --------------- db file sequential read 750 log file parallel write 43 log file sync 42 db file parallel read 32 control file sequential read 22 ON CPU 21 db file parallel write 21 log file switch (private strand flush incomplete) 8 Disk file operations I/O 1 control file parallel write 1 buffer busy waits 1 11 rows selected.
WAIT_TIME hoặc TIME_WAITED. Tại sao điều này được thực hiện? Hãy nhớ rằng, đây là dữ liệu mẫu, vì vậy thời gian chờ được cộng dồn với mỗi mẫu. Chỉ tổng hợp chúng sẽ cho một giá trị cao không đúng. Để giải thích điều này, hãy tưởng tượng trường hợp đơn giản trong đó một phiên đang chờ "db file sequential read" trong 5 giây. Điều đó có nghĩa là chúng ta sẽ có 5 mẫu, có thể giống như thế này.EVENT SAMPLE_ID TIME_SEC
======================= ========= ========
db file sequential read 1 1
db file sequential read 2 2
db file sequential read 3 3
db file sequential read 4 4
db file sequential read 5 5Chúng ta biết tổng thời gian chờ là 5 giây. Nếu chúng ta đếm số lượng mẫu, chúng ta nhận được 5, tương đương với 5 giây. Nếu chúng ta tính tổng thời gian cho cả 5 mẫu thì được 15 giây.
Các cột thời gian trong dữ liệu ASH phức tạp hơn một chút so với lần đầu tiên chúng xuất hiện, vì vậy hãy kiểm tra tài liệu khi sử dụng chúng. Sử dụng sai các cột này có lẽ là lỗi phổ biến nhất mà mọi người mắc phải khi sử dụng các view liên quan đến ASH.
DBA_HIST_ACTIVE_SESS_HISTORY
Để cho phép truy cập lịch sử vào dữ liệu ASH, cứ mười mẫu thì có một mẫu được lưu vào đĩa và được cung cấp bằng chế độ xem DBA_HIST_ACTIVE_SESS_HISTORY . Sử dụng view này tương tự như sử dụng view V$ACTIVE_SESSION_HISTORY, nhưng hãy nhớ thời gian mẫu bây giờ là 10 giây, vì vậy hãy sử dụng (count* 10) để đo thời gian, thay vì chỉ count.
SELECT NVL(a.event, 'ON CPU') AS event, COUNT(*)*10 AS total_wait_time FROM dba_hist_active_sess_history a WHERE a.sample_time > SYSDATE - 1 GROUP BY a.event ORDER BY total_wait_time DESC; EVENT TOTAL_WAIT_TIME ---------------------------------------------------------------- --------------- db file sequential read 3860 ON CPU 1500 control file sequential read 990 direct path read temp 580 direct path read 560 log file parallel write 280 db file parallel write 270 Disk file operations I/O 240 log file switch completion 150 log file sync 130 db file parallel read 130 . . . 26 rows selected.
Trang Hiệu năng của Enterprise Manager
Truy cập trực tiếp thông tin ASH có thể rất hữu ích, nhưng có một số cách hiệu quả hơn để truy cập một số thông tin này. Các trang hiệu năng của Enterprise Manager (Grid Control and Cloud Control) rất tuyệt vời. Chúng dựa trên thông tin ASH, giúp bạn dễ dàng truy cập vào thông tin hiệu năng lịch sử và thời gian thực (mục View Date chúng ta có thể chọn Real Time hay view về lịch sử 1 vài ngày, 1 vài tháng trước)
Hình ảnh sau đây là một ví dụ về trang chủ hiệu suất của Trình quản lý doanh nghiệp.
Dưới đây là một ví dụ về trang Hoạt động hàng đầu (top activity)
Báo cáo ASH
Báo cáo ASH có thể được hiển thị bằng TOAD, SQL Developer, Enterprise Manager hoặc được tạo từ SQL * Plus.
Lấy ASH bằng SQL*Plus
Để tạo chúng theo cách thủ công bằng SQL*Plus, hãy chạy tập lệnh sau, trong khi đăng nhập với tư cách người dùng đặc quyền.
$ORACLE_HOME/rdbms/admin/ashrpt.sqlTập lệnh sẽ nhắc bạn về các chi tiết sau:
- Loại báo cáo: [html | text]
- Instance number: [tất cả | n] - Trên 1 instance, giá trị này mặc định là "1". Trên cơ sở dữ liệu RAC, bạn có thể báo cáo về một số phiên bản cụ thể hoặc "tất cả" phiên bản.
- Thời gian bắt đầu: Tập lệnh mô tả các định dạng cho giá trị này. Nó có thể là một chuỗi ngày rõ ràng hoặc một phần bù của ngày giờ hiện tại. Giá trị mặc định là -15 phút.
- Thời lượng: Số phút để báo cáo. Thời lượng mặc định là (SYSDATE - begin_time).
- Tên báo cáo: Tên mặc định được cung cấp. Thay đổi nếu cần thiết.
Tập lệnh tạo ra văn bản hoặc đầu ra HTML theo yêu cầu. Ví dụ về những điều này được hiển thị bên dưới.
- Text
- HTML
Tùy thuộc vào các tùy chọn đã chọn, các tập lệnh báo cáo ASH gọi một trong số các hàm bảng từ gói DBMS_WORKLOAD_REPOSITORY.
ASH_REPORT_TEXTASH_REPORT_HTMLASH_GLOBAL_REPORT_TEXTASH_GLOBAL_REPORT_HTML
Nếu bạn đang sử dụng SQL Developer 4 trở đi, bạn có thể xem báo cáo ASH trực tiếp từ SQL Developer. Nếu nó chưa hiển thị, hãy mở DBA "View > DBA", mở kết nối đến Instance, sau đó mở rộng nút "Performance". Các báo cáo ASH có sẵn từ nút "ASH Reports Viewer".
Công cụ đọc báo cáo ASH
Công cụ ASH Viewer cung cấp chế độ xem đồ họa của dữ liệu lịch sử phiên hoạt động trong phiên bản Oracle. Điều thú vị là nó hỗ trợ Oracle 8i trở đi. Trong các bản phát hành trước Oracle 10g hoặc nếu bạn không có giấy phép Diagnostic và Tuning Pack l, bạn có thể kết nối bằng kết nối "Standard" và công cụ sẽ bắt chước chức năng của ASH. Nếu bạn có các giấy phép cần thiết, bạn có thể tạo kết nối "Enterprise", sử dụng ASH để cung cấp dữ liệu.
CHI TIẾT BÁO CÁO ASH
Phần đầu tiên của báo cáo ASH với mọi database:
| DB Name | DB Id | Instance | Inst num | Release | RAC | Host |
|---|---|---|---|---|---|---|
| FGTST1 | 2330122768 | FGTST1 | 1 | 11.2.0.3.0 | NO | fgdb1t |
| CPUs | SGA Size | Buffer Cache | Shared Pool | ASH Buffer Size |
|---|---|---|---|---|
| 8 | 2,039M (100%) | 192M (9.4%) | 960M (47.1%) | 16.0M (0.8%) |
| Sample Time | Data Source | |
|---|---|---|
| Analysis Begin Time: | 04-Oct-13 10:02:32 | V$ACTIVE_SESSION_HISTORY |
| Analysis End Time: | 04-Oct-13 11:02:32 | V$ACTIVE_SESSION_HISTORY |
| Elapsed Time: | 60.0 (mins) | |
| Sample Count: | 4,415 | |
| Average Active Sessions: | 1.23 | |
| Avg. Active Session per CPU: | 0.15 | |
| Report Target: | None specified |
Top User Events:
Top User Events
| Event | Event Class | % Event | Avg Active Sessions |
|---|---|---|---|
| CPU + Wait for CPU | CPU | 92.98 | 1.14 |
| db file sequential read | User I/O | 1.59 | 0.02 |
| log file sync | Commit | 1.13 | 0.01 |
Top Background Events
| Event | Event Class | % Activity | Avg Active Sessions |
|---|---|---|---|
| CPU + Wait for CPU | CPU | 1.49 | 0.02 |
| log file parallel write | System I/O | 1.25 | 0.02 |
Top Event P1/P2/P3 Values
| Event | % Event | P1 Value, P2 Value, P3 Value | % Activity | Parameter 1 | Parameter 2 | Parameter 3 |
|---|---|---|---|---|---|---|
| db file sequential read | 1.59 | "5","1013701","1" | 0.02 | file# | block# | blocks |
| log file parallel write | 1.25 | "2","38","2" | 0.14 | files | blocks | requests |
| log file sync | 1.13 | "1439","1575570377","0" | 0.02 | buffer# | sync scn | NOT DEFINED |
Câu lệnh tìm ra các segment:
select owner, segment_name, segment_type
from dba_extents
where file_id = &p1
and &p2 between block_id and block_id + blocks -&p3;
OWNER SEGMENT_NAME SEGMENT_TYPE
------ ------------ ------------
USER1 PTBL1 TABLE
Top SQL Comment Types cho chúng ta loại câu lệnh sử dụng tài nguyên DB:
Top SQL Command Types
- '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
| SQL Command Type | Distinct SQLIDs | % Activity | Avg Active Sessions |
|---|---|---|---|
| UPDATE | 3 | 88.24 | 1.08 |
| SELECT | 28 | 6.34 | 0.08 |
Top SQL with Top Events hiển thị các câu lệnh TOP, chú ý cột % Activity cần tối ưu sớm (như câu bên dưới chiếm 88.11% tải DB):
Top SQL with Top Events
| SQL ID | Planhash | Sampled # of Executions | % Activity | Event | % Event | Top Row Source | % RwSrc | SQL Text |
|---|---|---|---|---|---|---|---|---|
| fb69fu8w7argp | 240554356 | 2 | 88.11 | CPU + Wait for CPU | 88.11 | HASH JOIN | 87.70 | UPDATE ACCOUNT SET ISCOMPLETE ... |
| 3c0dfgruf2sdj | 1317339463 | 1 | 2.76 | CPU + Wait for CPU | 2.51 | SORT - AGGREGATE | 1.52 | /* SQL Analyze(2) */ select /*... |
| dqtbktv8s7g6k | 3033207482 | 56 | 1.29 | db file sequential read | 1.18 | INDEX - RANGE SCAN | 1.13 | SELECT (SELECT COUNT(1) FROM T... |
Những thủ tục PL/SQL chiếm tải:
PLSQL Entry Subprogram % Activity
----------------------------------------------------------------- ----------
PLSQL Current Subprogram % Current
----------------------------------------------------------------- ----------
USER1.PROC_d 5.61
USER1.PROC_h 5.61
USER2.PROC_b 3.39
USER1.TRIGGER_a 1.69
Các câu lệnh hữu ích khi làm việc với ASH
--------------------------------------------
-- Top 10 session chiếm CPU > 5 phút trước
--------------------------------------------
select *
from (select session_id, session_serial#, count(*)
from v$active_session_history
where session_state= 'ON CPU'
and sample_time > sysdate - interval '5' minute
group by session_id, session_serial#
order by count(*) desc
)
where rownum <= 10;
SESSION_ID SESSION_SERIAL# COUNT(*)
---------- --------------- ----------
3 1 3
--------------------------------------------
-- Top 10 session chờ > 5 phút
--------------------------------------------
select *
from (select session_id, session_serial#,count(*)
from v$active_session_history
where session_state='WAITING'
and sample_time > sysdate - interval '5' minute
group by session_id, session_serial#
order by count(*) desc
)
where rownum <= 10;
2 câu lệnh trên tìm được số câu lệnh quét nhiều CPU, wait > 5 phút. Nhưng ai chạy và SQL nào đang chạy?
--------------------
-- Ai đang chạy SID?
--------------------
set lines 200
col username for a10
col osuser for a10
col machine for a10
col program for a10
col resource_consumer_group for a10
col client_info for a10
select serial#, username, osuser, machine, program, resource_consumer_group, client_info
from v$session
where sid=&sid;
-------------------------
-- Ai đang chạy SID?
-------------------------
select distinct sql_id, session_serial#
from v$active_session_history
where sample_time > sysdate - interval '5' minute
and session_id=&sid;
----------------------------------------------
-- Nhận SQL từ Library Cache:
----------------------------------------------
col sql_text for a80
SQL> select sql_text from v$sql where sql_id='&sqlid';
----------------------------------------------
-- ASH:Top SQL > 5 phút
----------------------------------------------
select NVL(sql_id,'NULL') as sql_id
,count(*) as DB_time
,ROUND(100*count(*) / SUM(count(*)) OVER (), 2) as Pct_load
from v$active_session_history
where sample_time > sysdate - 5/24/60
and session_type <> 'BACKGROUND'
group by sql_id
order by count(*) desc;
SQL_ID DB_TIME PCT_LOAD
------------- ---------------------- ----------------------
88v077cs94gak 136 43.17
4xvts5kvsf1w8 89 28.25
8pcw7z5vvhfj0 7 2.22
dbm33sd7kv9s3 5 1.59
572fbaj0fdw2b 5 1.59
----------------------------------------------
-- ASH: Top *anything* SQL
----------------------------------------------
select ash.sql_id,
(select distinct decode( count(distinct sql_text) over (),
1, substr(sql_text,1,80)||'...',
'more than one sql matched' )
from v$sql
where sql_id = ash.sql_id) sql,
count(*)
from v$active_session_history ash, v$event_name evt
where ash.sample_time > sysdate - 1/24
and ash.session_state = 'WAITING'
and ash.event_id = evt.event_id
and evt.wait_class = 'User I/O'
group by sql_id
order by count(*) desc;
--------------------------------------------------------------------------------------------
-- ASH: SQL bởi tổng CPU và wait time, theo tiêu chí CPU, IO wait và non-IO wait
--------------------------------------------------------------------------------------------
select sql_id,
(select distinct decode( count(distinct sql_text) over (),
1, substr(sql_text,1,80)||'...', 'more than one sql matched' )
from v$sql
where sql_id = ash.sql_id) sql,
cpu, non_io_wait, io_wait
from (
select ash.sql_id,
sum(case when ash.session_state = 'ON CPU' then 1 else 0 end ) cpu,
sum(case when ash.session_state='WAITING' and ash.event#<>ev.user_io then 1 else 0 end) non_io_wait,
sum(case when ash.session_state = 'WAITING' and ash.event# = ev.user_io then 1 else 0 end ) io_wait
from v$active_session_history ash,
(select event# user_io from v$event_name where wait_class = 'User I/O') ev
group by ash.sql_id
) ash
order by cpu+non_io_wait+io_wait desc;
--------------------------------------------------------------------------------------------
-- ASH: Trong 1 giờ trước, và mỗi event tìm số lần min/max sample
-- Thêm wait time cho event, và báo cáo wait lớn nhất bởi số lần Waits
--------------------------------------------------------------------------------------------
select ash.event,
min(sample_time) start_time,
max(sample_time)-min(sample_time) duration,
round(sum(ash.wait_time+ash.time_waited)/1000000,2) wait_secs
from v$active_session_history ash
where ash.sample_time between
systimestamp-numtodsinterval(1,'hour') and systimestamp
group by ash.event
order by wait_secs desc;
--------------------------------------------------------------------------------------------
-- ASH: Trong 1 giờ trước, và mỗi event tìm số lần min/max sample
-- Thêm wait time cho event, và báo cáo wait lớn nhất bởi số lần session
--------------------------------------------------------------------------------------------
select ash.session_id,
au.username,
round(sum(ash.wait_time+ash.time_waited)/1000000,2) wait_secs
from v$active_session_history ash,
all_users au
where ash.sample_time >= systimestamp-numtodsinterval(1,'hour')
and ash.user_id = au.user_id
group by ash.session_id, au.username
order by wait_secs DESC;
--------------------------------------------------------------------------------------------
-- ASH: Trong 1 giờ trước, và mỗi event tìm số lần min/max sample
-- Thêm wait time cho event, và báo cáo wait lớn nhất
Với mỗi session, sql statement của mỗi session
--------------------------------------------------------------------------------------------
select (select distinct decode( count(distinct sql_text) over (),
1, substr(sql_text,1,80)||'...', 'more than one sql matched' )
from v$sql
where sql_id = ash.sql_id) sql,
round(sum(ash.wait_time+ash.time_waited)/1000000,2) wait_secs
from v$active_session_history ash
where ash.session_id = 1065
group by ash.sql_id
order by wait_secs DESC
Để biết thêm thông tin, hãy xem:
- Active Session History
- V$ACTIVE_SESSION_HISTORY
- DBA_HIST_ACTIVE_SESS_HISTORY
- Generating Active Session History Reports
Hi vọng hữu ích với bạn.
* KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE trực tiếp từ tôi giúp bạn bước đầu trở thành những chuyên gia DBA, đủ kinh nghiệm đi thi chứng chỉ OA/OCP, đặc biệt là rất nhiều kinh nghiệm, bí kíp thực chiến trên các hệ thống Core tại VN chỉ sau 1 khoá học.
* CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
* Chi tiết tham khảo:
https://bit.ly/oaz_w
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile: 0902912888
⚡️ Skype: tranbinh48ca
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: http://bit.ly/ytb_binhoraclemaster
👨 Tiktok: https://www.tiktok.com/@binhoraclemaster?lang=vi
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhoracle
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội
=============================
ASH trong Oracle Database, Active Session History (ASH) , học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,khóa học pl/sql, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle multitenant, Container Databases (CDB), Pluggable Databases (PDB), oracle cloud, oracle security, oracle fga, audit_trail, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, middleware, hoc solaris, hoc linux, hoc aix, unix, securecrt, xshell, mobaxterm, putty
