Sử dụng Undo Advisor trong Oracle Database

Undo Advisor sẽ giúp bạn sizing undo tablespace dựa trên tải của database, chú ý xác định thời điểm chiếm tải nhiều nhất để xác định undo tablespace, khi đã vượt qua được thời điểm này thì đương nhiên các thời điểm khác tải cũng đáp ứng.

TỔNG QUAN Từ phiên bản Oracle9i, rollback segment hay còn gọi là undo log. Thông tin undo của các giao dịch thông thường được lưu trữ trong Rollback Segment cho đến khi câu lệnh commit hoặc rollback. Tốt nhất, để undo được tự động DBA phải xác định được thời gian dài nhất mà câu lệnh commit không bị hết hạn, ngăn chặn tình trạng lỗi "snapshot too old" với truy vấn chạy lâu. Tham số quan trọng là UNDO_RETENTION, mặc định là 900 giây (15 phút), và bạn có thể thiết lập tham số này đảm bảo Oracle có thể giữ undo log trong thời gian đó. Nếu không đủ dung lượng undo tablespace thì không thể đảm bảo tiêu chí 900 giây được. Vấn đề mấu chốt là chúng ta phải xác định được dung lượng Undo tablespace phù hợp nhất và chế độ automatic undo management, khi tạo  undo tablespace chúng ta thiết lập UNDO_MANAGEMENT = AUTO Như vậy chúng ta cần phải đặt chú ý:  Đặt tham số UNDO_MANAGEMENT = AUTO    Cỡ UNDO tablespace Tham số UNDO_RETENTION  Tính toán UNDO_RETENTION  với dung lượng UNDO Tabespace Ban đầu khi chưa xác định được khả năng sử dụng Undo, chúng ta nên đặt 3-5 datafile cho undo tablespace với database thông thường, mỗi file fix 1GB, như vậy tối đa dược 96GB - 160GB undo tablespace (với DB lớn hơn có thể đặt 5-10 datafile auto extend max size). Sau đó đặt thời gian UNDO_RETENTION tùy vào nhu cầu, thường chúng ta đặt 12 giờ (43,200 giây) hoặc 24 giờ (86,400 giây). Công thức tính UNDO_RETENTION có thể đáp ứng với dung lượng tablespace đã được cấp: Undo Size thực tế SELECT SUM(a.bytes) "UNDO_SIZE"   FROM v$datafile a,        v$tablespace b,        dba_tablespaces c  WHERE c.contents = 'UNDO'    AND c.status = 'ONLINE'    AND b.name = c.tablespace_name    AND a.ts# = b.ts#;  UNDO_SIZE ----------   209715200  --> đơn vị byte Undo Blocks theo Second SELECT MAX(undoblks/((end_time-begin_time)*3600*24))       "UNDO_BLOCK_PER_SEC"   FROM v$undostat; UNDO_BLOCK_PER_SEC ------------------         3.12166667 DB Block Size SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"  FROM v$parameter WHERE name = 'db_block_size'; DB_BLOCK_SIZE [Byte] --------------------                 4096 Undo Retention tối ưu: 209'715'200 / (3.12166667 * 4'096) = 16'401 [Sec] Tips: Sử dụng câu lệnh sau, bạn có thể thực hiện chỉ trong 1 câu lệnh:  SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",        SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",        ROUND((d.undo_size / (to_number(f.value) *        g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"   FROM (        SELECT SUM(a.bytes) undo_size           FROM v$datafile a,                v$tablespace b,                dba_tablespaces c          WHERE c.contents = 'UNDO'            AND c.status = 'ONLINE'            AND b.name = c.tablespace_name            AND a.ts# = b.ts#        ) d,        v$parameter e,        v$parameter f,        (        SELECT MAX(undoblks/((end_time-begin_time)*3600*24))               undo_block_per_sec          FROM v$undostat        ) g WHERE e.name = 'undo_retention'   AND f.name = 'db_block_size' / ACTUAL UNDO SIZE [MByte] ------------------------ 200 UNDO RETENTION [Sec] -------------------- 10800 OPTIMAL UNDO RETENTION [Sec] ---------------------------- 16401 Tính toán UNDO Size cần cho hoạt động Database Nếu bạn có hữu hạn dung lượng do hết đĩa, bạn có thể chọn UNDO_RETENTION ngắn hơn (cho hoạt động FLASHBACK, etc.): Câu lệnh sau: SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]", SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]", (TO_NUMBER(e.value) * TO_NUMBER(f.value) * g.undo_block_per_sec) / (1024*1024) "NEEDED UNDO SIZE [MByte]" FROM ( SELECT SUM(a.bytes) undo_size FROM v$datafile a, v$tablespace b, dba_tablespaces c WHERE c.contents = 'UNDO' AND c.status = 'ONLINE' AND b.name = c.tablespace_name AND a.ts# = b.ts# ) d, v$parameter e, v$parameter f, ( SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec FROM v$undostat ) g WHERE e.name = 'undo_retention' AND f.name = 'db_block_size' / ACTUAL UNDO SIZE [MByte] ------------------------ 200 UNDO RETENTION [Sec] -------------------- 10800 NEEDED UNDO SIZE [MByte] ------------------------ 131.695313  Nếu "NEEDED UNDO SIZE" <= "ACTUAL UNDO SIZE", tức là chúng ta đang cấp phát lãng phí tài nguyên mất 200-131=69MB. Bạn thử resize datafile của UNDO tablespace hoặc tăng thời gian tham số UNDO_RETENTION dể sử dụng được tối đa undo tablespace. Còn muốn thu hồi dung lượng thì tạo lại undo tablespace.

SỬ DỤNG GIAO DIỆN TOAD, SQL DEVELOPER, EM

+ Undo Advisor trong Enterprise Manager (Home > Advisor Central > Undo Management > Undo Advisor) cung cấp các đề xuất cho cấu hình undo. 

+ Ngoài ra có thể dùng TOAD, SQL Developer, EM:

Undotablespace đang có dung lượng là 32GB, trong khoảng thời gian phân tích từ 1:42:13 ngày 15/04/2022 - 1:42:13 ngày 22/04/2022 đã bao chùm tất cả các thời điểm cao tải của nghiệp vụ rồi, Oracle khuyến nghị có thể đáp ứng với thời gian cho undo_retention là 168562 (s) tức 46.8 giờ (nếu  muốn tăng thêm thời gian thì phải thêm datafile cho undo tablespace, còn muốn giảm thời gian thì resize datafile undo tablespace hoặc tạo lại undo tablespace với kích thước nhỏ hơn)

SỬ DỤNG UNDO ADVISOR BẰNG PL/SQL

Undo Advisor cũng có thể truy cập được từ PL/SQL bằng cách sử dụng gói DBMS_ADVISOR, nhưng không có báo cáo tự động nào, vì vậy bạn phải lấy các đề xuất từ ​​view DBA_ADVISOR_% thủ công.

SELECT MIN(snap_id), MAX(snap_id) FROM dba_hist_snapshot;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
         884         1052

1 row selected.

DECLARE
  l_task_name  VARCHAR2(30) := '884_1052_AWR_SNAPSHOT_UNDO';
  l_object_id  NUMBER;
BEGIN

  -- Tạo 1 công việc Undo Advisor
  DBMS_ADVISOR.create_task (
    advisor_name      => 'Undo Advisor',
    task_name         => l_task_name,
    task_desc         => 'Undo Advisor Task');

  DBMS_ADVISOR.create_object (
    task_name   => l_task_name,
    object_type => 'UNDO_TBS',
    attr1       => NULL, 
    attr2       => NULL, 
    attr3       => NULL, 
    attr4       => 'null',
    attr5       => NULL,
    object_id   => l_object_id);

  -- Thiết lập target object.
  DBMS_ADVISOR.set_task_parameter (
    task_name => l_task_name,
    parameter => 'TARGET_OBJECTS',
    value     => l_object_id);

  -- Thiết lập snapshot bắt đầu và kết thúc
  DBMS_ADVISOR.set_task_parameter (
    task_name => l_task_name,
    parameter => 'START_SNAPSHOT',
    value     => 884);

  DBMS_ADVISOR.set_task_parameter (
    task_name => l_task_name,
    parameter => 'END_SNAPSHOT',
    value     => 1052);

  -- Thực hiện công việc
  DBMS_ADVISOR.execute_task(task_name => l_task_name);
END;
/

THAM KHẢO THÊM

Quản lý Undo data trong Oracle Database

Thủ tục tạo lại UNDO TABLESPACE trong Oracle Database

Managing Undo Data

=============================
* 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/Zalo: 0902912888
👨 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: https://www.youtube.com/@binhguru
👨 Tiktok: https://www.tiktok.com/@binhguru
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhguru
👨 Podcast: https://www.podbean.com/pu/pbblog-eskre-5f82d6
👨 Đị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

=============================
oracle tutorial, 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,sql tutorial, khóa học pl/sql tutorial, 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 RAC, ASM, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, postgresql tutorial, mysql tutorial, mariadb tutorial, sql server tutorial, nosql, mongodb tutorial, oci, cloud, middleware tutorial, hoc solaris tutorial, hoc linux tutorial, hoc aix tutorial, unix tutorial, securecrt, xshell, mobaxterm, putty