a. Cơ sở dữ liệu Oracle Enterprise hiện có.
b. Cần có giấy phép Oracle Diagnostics Pack và Oracle Tuning Pack để thực hiện tác vụ điều chỉnh hiệu năng.
c. Cài đặt tiện ích SQLTXPLAIN (SQLT) từ Oracle. Vì tập lệnh coe nằm trong thư mục SQLT …/sqlt/install/coe.
Tình huống: Chúng ta đang chạy một truy vấn DML ( câu lệnh SELECT ) trên một bảng có 5.000.000 hàng, trong đó truy vấn tiêu tốn rất nhiều bộ nhớ và mất nhiều thời gian để trả về kết quả.
Bước 1:
Lấy ID SQL cho truy vấn đó bằng cách sử dụng:
SELECT
s.sid,
s.serial#,
s.username,
s.sql_id,
q.sql_fulltext,
s.sql_child_number, s.status
,
s.event , s.wait_class, s.seconds_in_wait, s.machine, s.program FROM v$session s JOIN v$sql q ON s.sql_id = q.sql_id WHERE s.status = 'ACTIVE' AND s.sql_id IS NOT NULL AND s.username IS NOT NULL ORDER BY s.last_call_et DESC;
Bước 2:
Kiểm tra kế hoạch thực thi hiện tại của ID SQL đó.
SELECT *
FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR( '<SQL ID>' , NULL , 'ALLSTATS LAST' ));DBMS_XPLAN.DISPLAY_CURSOR → trả về kế hoạch thực thi từ bộ nhớ đệm con trỏ.
NULL → số thứ tự con (NULL = con mặc định)
ALLSTATS LAST → hiển thị số liệu thống kê thực thi thực tế
Bước 3:
Lấy lịch sử thực thi của ID SQL bằng cách sử dụng:
SELECT
ss.snap_id,
ss.instance_number AS node,
ss.begin_interval_time,
s.sql_id,
t.sql_text,
s.plan_hash_value,
NVL(s.executions_delta, 0 ) AS execs,
(s.elapsed_time_delta /
DECODE(NVL(s.executions_delta, 0 ), 0 , 1 ,s.executions_delta)) / 1000000
AS avg_etime,
(s.buffer_gets_delta /
DECODE(NVL(s.executions_delta, 0 ), 0 , 1 ,s.executions_delta))
AS avg_lio
FROM dba_hist_sqlstat s
JOIN dba_hist_snapshot ss
ON ss.snap_id = s.snap_id
AND ss.instance_number = s.instance_number
JOIN dba_hist_sqltext t
ON t.sql_id = s.sql_id
WHERE s.sql_id = '<SQL_ID>'
AND s.executions_delta > 0
ORDER BY
ss.begin_interval_time,
ss.instance_number;Ở đây, nó hiển thị lịch sử thực thi của ID SQL cùng với kế hoạch thực thi. Vì vậy, chúng ta có thể kiểm tra xem kế hoạch thực thi đã bị thay đổi hay chưa.
Bước 4:
4.1. Kiểm tra COE để tìm các kế hoạch tốt hơn đã có sẵn.
Để kiểm tra các kế hoạch, chúng tôi sử dụng tập lệnh coe_xfr_sql_profile.sql .
START coe_xfr_sql_profile.sql <SQL_ID>Đoạn mã này:
Đọc tất cả các mục PLAN_HASH_VALUE từ:
a. GV$SQL_PLAN (bộ nhớ)
b. DBA_HIST_SQL_PLAN (AWR)
Tính toán thời gian thực thi trung bình cho mỗi kế hoạch bằng cách sử dụng:
a. GV$SQL (thời gian chạy)
b. DBA_HIST_SQLSTAT (AWR)
Kết quả đầu ra sẽ là,
PLAN_HASH_VALUE AVG_ET_SECS
------------------ ------------
4009583451 12.438
9123498812 0.210 <-- BEST PLAN
3009112234 5.901Kết quả hiển thị các kế hoạch, trong đó chúng ta tìm ra kế hoạch tốt nhất.
4.2. Tạo hồ sơ SQL (SQL Profile)
Sử dụng giá trị PLAN_HASH_VALUE tốt nhất, hãy chạy lại COE cho SQL_ID + PLAN_HASH_VALUE.
START coe_xfr_sql_profile.sql <SQL_ID> 9123498812Điều này sẽ tạo ra,
a. Trích xuất văn bản SQL
b. Trích xuất OTHER_XML (gợi ý phác thảo)
c. Truy xuất kế hoạch từ bộ nhớ hoặc AWR
d. Tạo tệp kịch bản tạo hồ sơ SQL như sau:
coe_xfr_sql_profile_ < SQL_ID > _9123498812.sqlBên trong tập tin này chúng ta có,
a. SQL_TEXT đã được ghi lại
b. Tất cả các gợi ý OUTLINE giúp tái tạo kế hoạch tối ưu hơn
c. Lệnh DBMS_SQLTUNE.IMPORT_SQL_PROFILE
4.3. Xem lại tệp SQL_PRFILE và Áp dụng tệp SQL_PROFILE (Đẩy kế hoạch thực thi)
Kết nối với cơ sở dữ liệu và chạy tập tin.
@coe_xfr_sql_profile_ <SQL_ID>_9123498812.sql4.4. Kiểm tra xem gói cước đã được sử dụng chưa.
Chạy truy vấn bên dưới để tìm xem PLAN đã cập nhật đang chạy ở đâu.
SELECT sql_id, plan_hash_value
FROM v$ sql
WHERE sql_id = '<SQL ID>' ;Kết quả đầu ra:
PLAN_HASH_VALUE = 9123498812Nếu cần xóa cấu hình SQL, hãy chạy lệnh sau:
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE( 'coe_<SQL ID>_9123498812' );Thao tác này sẽ xóa cấu hình SQL PROFILE mà chúng ta đã cập nhật cho một SQL ID.
Bước 5:
Nếu tất cả các PLAN khả dụng đều chưa được tối ưu hóa, chúng ta sẽ thực hiện SQL TUNING , chạy
BEGIN
DBMS_SQLTUNE.create_tuning_task ( task_name => ' <TASK_NAME_HERE>' , sql_id => ' <SQL_ID_HERE>' , time_limit = > <TIME_LIMIT_SECONDS> ) ; DBMS_SQLTUNE.execute_tuning_task ( '<TASK_NAME_HERE> ' ) ; END ; /
Sau khi thực thi, chúng ta có thể nhận được báo cáo hoặc kết quả bằng cách:
SELECT DBMS_SQLTUNE.report_tuning_task( '<TASK_NAME_HERE>' )
FROM dual;Hãy kiểm tra các đề xuất và lưu lại để sử dụng sau, vì chúng ta cần kiểm tra COE để tìm ra các kế hoạch tốt hơn. Nếu các kế hoạch hiện tại không đáp ứng được các đề xuất, chúng ta có thể thực hiện theo các đề xuất đó.
Ví dụ,
Tên nhiệm vụ: TUNE_1ABC
Mã định danh SQL: 1abc234xyz
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TUNE_1ABC
Tuning Task Owner : SYS
Scope : COMPREHENSIVE
Time Limit(seconds) : 60
Completion Status : COMPLETED
Started at : 11-Dec-25 14:18:00
Completed at : 11-Dec-25 14:18:05
-------------------------------------------------------------------------------
SQL ID: 1abc234xyz
SQL Text:
SELECT e.employee_id, e.first_name, e.salary
FROM employees e
WHERE e.department_id = :dept
ORDER BY e.salary DESC
-------------------------------------------------------------------------------
FINDING 1: Index recommendation
-------------------------------------------------------------------------------
Estimated Benefit: 91.2%
Impact: HIGH
Oracle recommends creating the following index to significantly improve the
performance of this statement.
Recommendation (Estimated Execution Time Reduction: 91%):
---------------------------------------------------------
CREATE INDEX EMP_DEPT_IDX ON EMPLOYEES(DEPARTMENT_ID, SALARY);
Rationale:
----------
The workload shows that filtering on DEPARTMENT_ID and sorting by SALARY is
frequent. This index provides optimal access and avoids full table scans.
-------------------------------------------------------------------------------
FINDING 2: SQL Profile recommendation
-------------------------------------------------------------------------------
A SQL Profile can be created to improve cardinality estimates and optimizer
statistics.
Recommendation:
---------------
EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
task_name => 'TUNE_1ABC',
name => 'SQLPROFILE_1ABC');
Rationale:
-----------
Using the SQL Profile may reduce the expected elapsed time by up to 70%.
-------------------------------------------------------------------------------
FINDING 3: Statistics Finding
-------------------------------------------------------------------------------
The table EMPLOYEES has stale statistics.
Recommendation:
---------------
EXEC DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'HR',
tabname => 'EMPLOYEES',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
Rationale:
-----------
Optimizer estimations are inaccurate due to stale table statistics.
-------------------------------------------------------------------------------
FINDING 4: Alternative Plan
-------------------------------------------------------------------------------
The optimizer has identified a potentially better execution plan.
Original Plan:
--------------
- Full table scan on EMPLOYEES
- Sort order by SALARY
Recommended Plan:
-----------------
- Use index access on EMP_DEPT_IDX
- Avoid full table scan
- Reduce logical I/O by 85%
-------------------------------------------------------------------------------
SUMMARY OF RECOMMENDATIONS
-------------------------------------------------------------------------------
1. Create index EMP_DEPT_IDX
2. Collect table statistics on EMPLOYEES
3. Create SQL Profile SQLPROFILE_1ABC
-------------------------------------------------------------------------------
END OF REPORT
-------------------------------------------------------------------------------Chúng ta có thể chạy bất kỳ khuyến nghị nào được đưa ra bởi Trình tư vấn điều chỉnh SQL. Thao tác này sẽ tạo một Hồ sơ SQL cho ID SQL. Sử dụng truy vấn bên dưới để kiểm tra xem nó có đang sử dụng các khuyến nghị hay không.
SELECT *
FROM dba_sql_profiles
WHERE name LIKE '%<SQL_ID>%' ;Kết quả đầu ra:
NAME STATUS TYPE
------------------- ------- ------------
SYS_SQLPROF_34acdef ENABLED SQL PROFILELưu ý: Hiệu suất của đề xuất có thể được tìm thấy sau khi thực thi câu lệnh DML. Nếu đề xuất không tốt hơn, chúng ta có thể xóa SQL_PROFILE và đẩy kế hoạch tốt hơn bằng cách sử dụng COE.
TƯ VẤN: Click hoặc Hotline/Zalo 090.29.12.888
=============================
Website không chứa bất kỳ quảng cáo nào, mọi đóng góp để duy trì phát triển cho website (donation) xin vui lòng gửi về STK 90.2142.8888 - Ngân hàng Vietcombank Thăng Long - TRAN VAN BINH
=============================
Nếu bạn không muốn bị AI thay thế và tiết kiệm 3-5 NĂM trên con đường trở thành DBA chuyên nghiệp hay làm chủ Database thì hãy đăng ký ngay KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE, được Coaching trực tiếp từ tôi với toàn bộ bí kíp thực chiến, thủ tục, quy trình của gần 20 năm kinh nghiệm (mà bạn sẽ KHÔNG THỂ tìm kiếm trên Internet/Google) từ đó giúp bạn dễ dàng quản trị mọi hệ thống Core tại Việt Nam và trên thế giới, đỗ OCP.
- 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
=============================
2 khóa học online qua video giúp bạn nhanh chóng có những kiến thức nền tảng về Linux, Oracle, học mọi nơi, chỉ cần có Internet/4G:
- Oracle cơ bản: https://bit.ly/admin_1200
- Linux: https://bit.ly/linux_1200
=============================
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
=============================
cơ sở dữ liệu, cơ sở dữ liệu quốc gia, database, AI, trí tuệ nhân tạo, artificial intelligence, machine learning, deep learning, LLM, ChatGPT, DeepSeek, Grok, 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/21c/23c/23ai, 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, ms sql server tutorial, nosql, mongodb tutorial, oci, cloud, middleware tutorial, docker, k8s, micro service, hoc solaris tutorial, hoc linux tutorial, hoc aix tutorial, unix tutorial, securecrt, xshell, mobaxterm, putty
