Thứ Ba, 11 tháng 10, 2022

Active Session History (ASH) trong Oracle Database

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.
Lưu ý số lượng mẫu được sử dụng để xác định thời gian đã chờ, không phải 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        5

Chú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.

Thực tế ASH sử dụng các mẫu tất nhiên có thể có nghĩa là một số bit thông tin nhất định lọt qua mạng, nhưng đây không thực sự là một vấn đề. Rất có thể mối quan tâm chính của bạn sẽ là những phiên hoặc những câu lệnh chiếm nhiều thời gian. Thời gian chạy càng lâu, chúng càng có nhiều khả năng được chọn. Không có khả năng bất kỳ điều gì đáng lo ngại sẽ hoàn toàn không đăng ký trong view ASH, nhưng điều đó không có nghĩa là bạn có thể bỏ qua các hạn chế của nó.

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.sql

Tậ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_TEXT
  • ASH_REPORT_HTML
  • ASH_GLOBAL_REPORT_TEXT
  • ASH_GLOBAL_REPORT_HTML
Lấy ASH Report bằng SQL Developer

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 NameDB IdInstanceInst numReleaseRACHost
FGTST12330122768FGTST1111.2.0.3.0NOfgdb1t
CPUsSGA SizeBuffer CacheShared PoolASH Buffer Size
82,039M (100%)192M (9.4%)960M (47.1%)16.0M (0.8%)

Sample TimeData Source
Analysis Begin Time:04-Oct-13 10:02:32V$ACTIVE_SESSION_HISTORY
Analysis End Time:04-Oct-13 11:02:32V$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

EventEvent Class% EventAvg Active Sessions
CPU + Wait for CPUCPU92.981.14
db file sequential readUser I/O1.590.02
log file syncCommit1.130.01


Top Background Events

EventEvent Class% ActivityAvg Active Sessions
CPU + Wait for CPUCPU1.490.02
log file parallel writeSystem I/O1.250.02


Top Event P1/P2/P3 Values

Event% EventP1 Value, P2 Value, P3 Value% ActivityParameter 1Parameter 2Parameter 3
db file sequential read1.59"5","1013701","1"0.02file#block#blocks
log file parallel write1.25"2","38","2"0.14filesblocksrequests
log file sync1.13"1439","1575570377","0"0.02buffer#sync scnNOT 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 TypeDistinct SQLIDs% ActivityAvg Active Sessions
UPDATE388.241.08
SELECT286.340.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 IDPlanhashSampled # of Executions% ActivityEvent% EventTop Row Source% RwSrcSQL Text
fb69fu8w7argp240554356288.11CPU + Wait for CPU88.11HASH JOIN87.70UPDATE ACCOUNT SET ISCOMPLETE ...
3c0dfgruf2sdj131733946312.76CPU + Wait for CPU2.51SORT - AGGREGATE1.52/* SQL Analyze(2) */ select /*...
dqtbktv8s7g6k3033207482561.29db file sequential read1.18INDEX - RANGE SCAN1.13SELECT (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:

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
By lúc
Labels: , ,

ĐỌC NHIỀU
Trần Văn Bình - Oracle Database Master