Automatic Workload Repository (AWR) trong Oracle database

Automatic Workload Repository (AWR) là một công cụ mạnh mẽ trong Oracle Database, giúp thu thập, duy trì và phân tích hiệu suất của cơ sở dữ liệu. AWR là một thành phần cốt lõi của quản lý hiệu suất tự động trong Oracle Database và cung cấp các báo cáo chi tiết về hoạt động của hệ thống, giúp các DBA xác định và giải quyết các vấn đề về hiệu suất.

Dưới đây là các bước cơ bản để cấu hình và quản trị AWR trong Oracle Database:

1. Kích Hoạt AWR

AWR được tự động kích hoạt khi bạn cài đặt Oracle Database. Tuy nhiên, bạn có thể kiểm tra và cấu hình lại các tham số của AWR nếu cần.

Kiểm Tra Tình Trạng AWR

SELECT * FROM dba_advisor_definitions;

Sử dụng SQL dev để kiểm tra:

Kích Hoạt AWR (Nếu chưa được kích hoạt)

sqlplus sys/oracle as sysdba

EXEC dbms_workload_repository.modify_snapshot_settings(interval => 60, retention => 14400);

## 60 phút snapshot 1 lần

## retention là số ngày giữ lại bản ghi snapshot => ví dụ là 10 ngày (con số này không được thấp hơn số tối thiểu là 8 (tám) ngày)

2. Tạo Snapshot

Snapshot được tạo tự động bởi Oracle theo chu kỳ đã định, nhưng bạn cũng có thể tạo snapshot thủ công.

Tạo Snapshot Thủ Công

EXEC dbms_workload_repository.create_snapshot();

3. Tạo Báo Cáo AWR

Bạn có thể tạo báo cáo AWR để phân tích hiệu suất cơ sở dữ liệu. Các báo cáo này thường được tạo cho một khoảng thời gian cụ thể và chứa thông tin chi tiết về hiệu suất của cơ sở dữ liệu.

Tạo Báo Cáo AWR Bằng SQL*Plus

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

Khi chạy lệnh trên, bạn sẽ được yêu cầu nhập:

1. Định dạng đầu ra (TEXT hoặc HTML).
2. Khoảng thời gian bắt đầu và kết thúc cho báo cáo.

4. Quản Lý Snapshot

Bạn có thể xem các snapshot đã được tạo và quản lý chúng nếu cần.

Xem Các Snapshot Hiện Có

SELECT SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME
FROM DBA_HIST_SNAPSHOT
ORDER BY SNAP_ID;

Xóa Snapshot: xoá từ số bao nhiêu đến bao nhiêu

EXEC dbms_workload_repository.drop_snapshot_range(low_snap_id => 100, high_snap_id => 110);

PHÂN TÍCH AWR:

Dưới đây là các chỉ số chính trong báo cáo AWR và ý nghĩa của chúng:

1. Load Profile

Load Profile cung cấp một cái nhìn tổng quan về hoạt động của cơ sở dữ liệu trong khoảng thời gian giữa các snapshot. Các chỉ số chính bao gồm:

• DB Time: Tổng thời gian sử dụng bởi cơ sở dữ liệu cho các tác vụ.
• DB CPU: Thời gian CPU được sử dụng bởi cơ sở dữ liệu.
• Redo Size: Kích thước của redo logs được tạo ra.
• Logical Reads: Số lượng lần đọc từ bộ nhớ (cache).
• Physical Reads: Số lượng lần đọc từ đĩa.
• User Calls: Số lượng yêu cầu từ người dùng.
• Parses: Số lượng lần phân tích cú pháp SQL.
• Hard Parses: Số lượng lần phân tích cú pháp mà không thể sử dụng lại các kế hoạch thực thi hiện có.
• Sorts: Số lượng sắp xếp được thực hiện.

2. Instance Efficiency Percentages

Phần này cung cấp các chỉ số về hiệu suất tổng thể của hệ thống:

• Buffer Nowait %: tỷ lệ dữ liệu không phải chờ đợi được tải từ bộ nhớ đệm vào bộ nhớ chính.
• Buffer Cache Hit Ratio: Tỷ lệ phần trăm các lần đọc tìm thấy dữ liệu trong bộ nhớ cache.
• Library Cache Hit Ratio: Tỷ lệ phần trăm các lần tìm thấy kế hoạch thực thi SQL trong cache.
• Execute to Parse %: Tỷ lệ giữa số lần thực thi và số lần phân tích cú pháp.
• Parse CPU to Parse Elapsd %: Tỷ lệ phần trăm thời gian CPU sử dụng cho việc phân tích cú pháp so với tổng thời gian phân tích cú pháp.
• Redo NoWait %: Tỷ lệ phần trăm các lần ghi redo logs mà không gặp phải sự chờ đợi.
• In-memory Sort %: Tỷ lệ phần trăm các lần sắp xếp được thực hiện trong bộ nhớ.
• Soft Parse %: Tỷ lệ phần trăm các lần phân tích cú pháp mềm (không cần phải xây dựng lại kế hoạch thực thi).
• Latch Hit %: Tỷ lệ phần trăm các lần truy cập latch thành công.
• % Non-Parse CPU: Tỷ lệ phần trăm thời gian CPU sử dụng cho các tác vụ không phải phân tích cú pháp.

3. Top Timed Events

Phần này liệt kê các sự kiện chờ đợi tiêu tốn nhiều thời gian nhất:

• Event: Tên sự kiện chờ đợi.
• Waits: Số lượng lần chờ đợi xảy ra.
• Time(s): Tổng thời gian chờ đợi (tính bằng giây).
• Avg Wait(ms): Thời gian chờ đợi trung bình cho mỗi lần chờ đợi (tính bằng mili giây).
• % DB time: Tỷ lệ phần trăm thời gian cơ sở dữ liệu dành cho sự kiện chờ đợi này.

4. SQL Statistics

Phần này cung cấp thông tin về các câu lệnh SQL tiêu tốn nhiều tài nguyên nhất:

• SQL ordered by Elapsed Time: Các câu lệnh SQL được sắp xếp theo thời gian thực hiện.
• SQL ordered by CPU Time: Các câu lệnh SQL được sắp xếp theo thời gian CPU sử dụng.
• SQL ordered by Gets: Các câu lệnh SQL được sắp xếp theo số lần đọc logic.
• SQL ordered by Reads: Các câu lệnh SQL được sắp xếp theo số lần đọc vật lý.
• SQL ordered by Executions: Các câu lệnh SQL được sắp xếp theo số lần thực thi.
• SQL ordered by Cluster Wait Time: Các câu lệnh SQL được sắp xếp theo thời gian chờ đợi của cluster.

5. Instance Activity Stats

Phần này cung cấp các chỉ số hoạt động của instance:

• User Commits: Số lượng giao dịch người dùng được commit.
• User Rollbacks: Số lượng giao dịch người dùng bị rollback.
• Physical Reads: Số lượng lần đọc vật lý từ đĩa.
• Physical Writes: Số lượng lần ghi vật lý vào đĩa.
• Redo Entries: Số lượng các mục ghi vào redo logs.
• Logical Reads: Số lượng lần đọc logic từ bộ nhớ cache.
• Block Changes: Số lượng thay đổi khối.

6. Segment Statistics

Phần này cung cấp thông tin về các đoạn (segments) tiêu tốn nhiều tài nguyên nhất:

• Segments by Logical Reads: Các đoạn được sắp xếp theo số lần đọc logic.
• Segments by Physical Reads: Các đoạn được sắp xếp theo số lần đọc vật lý.
• Segments by Buffer Busy Waits: Các đoạn được sắp xếp theo số lần chờ đợi buffer busy.
• Segments by Row Lock Waits: Các đoạn được sắp xếp theo số lần chờ đợi khóa dòng.

7. Advisory Statistics

Phần này cung cấp thông tin từ các cố vấn hiệu suất, chẳng hạn như:

• Buffer Cache Advisory: Đề xuất về kích thước bộ nhớ đệm.
• PGA Memory Advisory: Đề xuất về kích thước bộ nhớ PGA.
• Shared Pool Advisory: Đề xuất về kích thước vùng nhớ chia sẻ.

=> hệ thống sử dụng phù hợp với bộ nhớ hiện tại, nếu có tăng bộ nhớ thêm thì cũng không có lợi gì vì cũng không sử dụng hết.

Tương tự vậy SGA cũng phù hợp với hiệu năng và công suất sử dụng hiện tại

Thêm thông tin:

DB TIME:

• Định nghĩa: DB TIME là tổng thời gian thực hiện các thao tác truy vấn và xử lý dữ liệu trong Oracle Database.
• Cách đo lường: DB TIME được đo bằng đơn vị thời gian (thường là miligiây hoặc giây).
• Phân tích: DB TIME cao cho thấy hệ thống đang dành nhiều thời gian cho việc xử lý các truy vấn và thao tác dữ liệu. Điều này có thể do nhiều yếu tố gây ra, như truy vấn phức tạp, dữ liệu lớn, cấu trúc bảng không tối ưu, hoặc thiếu chỉ mục hiệu quả.

DB CPU:

• Định nghĩa: DB CPU là tổng thời gian CPU được sử dụng bởi các tiến trình Oracle Database.
• Cách đo lường: DB CPU được đo bằng tỷ lệ phần trăm (%) thời gian CPU được sử dụng.
• Phân tích: DB CPU cao cho thấy hệ thống đang sử dụng nhiều tài nguyên CPU cho các hoạt động của Oracle Database. Điều này có thể do nhiều yếu tố gây ra, như truy vấn tốn kém, xử lý dữ liệu nặng, hoặc nhiều phiên kết nối đồng thời.

Điểm khác biệt chính:

Mối quan hệ:

DB TIME và DB CPU có mối liên hệ mật thiết với nhau. Nói chung, DB TIME cao sẽ dẫn đến DB CPU cao. Tuy nhiên, cũng có trường hợp DB TIME cao nhưng DB CPU thấp, ví dụ như khi hệ thống đang chờ I/O.

Các Views check AWR:

V$ACTIVE_SESSION_HISTORY – Hiển thị active session history (ASH), mẫu lấy theo từng giây

V$METRIC – Hiển thị thông tin metric.

V$METRICNAME – Hiển thị the metrics kết hợp với group

V$METRIC_HISTORY – Hiển thị lịch sử metric

V$METRICGROUP – Hiển thị mọi metrics group

DBA_HIST_ACTIVE_SESS_HISTORY – Hiển thị lịch sử của active session history.

DBA_HIST_BASELINE – Hiển thị thông tin baseline.

DBA_HIST_DATABASE_INSTANCE – Hiển thị thông tin môi trường database.

DBA_HIST_SNAPSHOT – Hiển thị thông tin snapshot.

DBA_HIST_SQL_PLAN – Hiển thị SQL execution plans.

DBA_HIST_WR_CONTROL – Hiển thị thiết lập AWR.

ADDM view

DBA_ADVISOR_TASKS – Thông tin cơ bản về các nhiệm vụ hiện có.
DBA_ADVISOR_LOG – Thông tin trạng thái về các nhiệm vụ hiện có.
DBA_ADVISOR_FINDINGS – Các phát hiện được xác định cho một nhiệm vụ hiện có.
DBA_ADVISOR_RECOMMENDATIONS – Khuyến nghị cho các vấn đề được xác