Performance Tuning

Tính năng tự động tạo index trong Oracle database 19c

tự động tạo index

Bài viết này sẽ hướng dẫn các bạn sử dụng tính năng tự động tạo index rất hay trong phiên bản Oracle database 19c

1. Tính năng tự động tạo Index là gì ?

Một trong các yếu tố kinh điển nhất ảnh hưởng đến hiệu năng của cơ sở dữ liệu: Index thế nào cho hợp lý

Index không phải là tuyệt chiêu “thập toàn đại bổ”, khi bạn thực hiện tạo Index trong bảng :

  • Các câu lệnh SELECT nếu sử dụng Index thì có thể sẽ nhanh hơn việc quét FULL TABLE
  • Các câu lệnh SELECT không sử dụng Index vừa tạo thì sẽ chẳng nhanh hơn mà cũng không chậm đi
  • Các câu lệnh DML chắc chắn sẽ bị châm đi (nếu bảng mà có quá nhiều Index thì sẽ ảnh hưởng thấy rõ).

Chính vì thế ở các phiên bản trước đây, nếu không phải chuyên gia về Cơ sở dữ liệu bạn sẽ rất khó trong việc quyết dịnh sẽ tạo Index như thế nào cho hợp lý.

Từ phiên bản Oracle 19c, chúng ta có 1 công cụ đắc lực: các index có thể tự tạo ra bởi quyết đinh của hệ thống

  • Hệ thống sẽ tự động xem xét các table, các column trong tables và tự động ra quyết định Index cần tạo trên table nào, trên column nào.
  • Các Index tự động được tạo ra thường có tiền tố SYS_AI, chúng ta có thể nhận biết vô cùng dễ dàng
  • Mặc định Index khi tạo ra sẽ ở chế độ “tàng hình” (invisible). Để đảm bảo an toàn cho hệ thống, chúng ta cần cân nhắc xem có áp dụng index đó hay không. Tôi đặc biệt khuyến cáo các DBA nên tự mình đánh giá 1 lần nữa trước khi quyết định áp dụng Index vào hệ thống PRODUCTION.

2. Làm thế nào để sử dụng tính năng này 

Hiện nay tính năng này được hạn chế chỉ sử dụng trên phiên bản Enterprise của hệ thống Exadata.

Tuy nhiên các bạn có thể sử dụng mẹo sau để bật tính năng này trên Database của mình

sqlplus / as sysdba
alter system set "_exadata_feature_on"=true scope=spfile;
# Restart để nhận tham số vừa cài đặt
shutdown immediate;
startup;

Lưu ý: Do Oracle tuyên bố chỉ hỗ trợ trên hệ thống Exadata, do đó các hệ thống Production chạy trên máy chủ thông thường các bạn cần cân nhắc kỹ trước khi sử dụng “mẹo” trên nhé. Tôi chỉ khuyến cáo sử dụng cho việc lab.

3. Cách cấu hình tính năng tự động tạo Index

Sau khi đã bật tham số ẩn “_exadata_feature_on”, chúng ta có thể sử dụng package DBMS_AUTO_INDEX  để cấu hình tính năng mới đang được đề cập trong bài viết.

3.1. Làm thế nào để xem thông tin cấu hình hiện tại

Nếu đang sử dụng Database ở mô hình container, bạn có thể sử dụng lệnh sau:

column parameter_name format a40
column parameter_value format a15

select con_id, parameter_name, parameter_value 
from   cdb_auto_index_config
order by 1, 2;

    CON_ID PARAMETER_NAME                           PARAMETER_VALUE
---------- ---------------------------------------- ---------------
         1 AUTO_INDEX_COMPRESSION                   OFF
         1 AUTO_INDEX_DEFAULT_TABLESPACE
         1 AUTO_INDEX_MODE                          OFF
         1 AUTO_INDEX_REPORT_RETENTION              31
         1 AUTO_INDEX_RETENTION_FOR_AUTO            373
         1 AUTO_INDEX_RETENTION_FOR_MANUAL
         1 AUTO_INDEX_SCHEMA
         1 AUTO_INDEX_SPACE_BUDGET                  50
         3 AUTO_INDEX_COMPRESSION                   OFF
         3 AUTO_INDEX_DEFAULT_TABLESPACE
         3 AUTO_INDEX_MODE                          OFF
         3 AUTO_INDEX_REPORT_RETENTION              31
         3 AUTO_INDEX_RETENTION_FOR_AUTO            373
         3 AUTO_INDEX_RETENTION_FOR_MANUAL
         3 AUTO_INDEX_SCHEMA
         3 AUTO_INDEX_SPACE_BUDGET                  50

Trong trường hợp bạn chỉ muốn xem thông tin của một Pluggable Database thì dùng lệnh sau

alter session set container = pdb_ketoan;

SQL> select con_id, parameter_name, parameter_value from cdb_auto_index_config order by 1, 2;

    CON_ID PARAMETER_NAME                           PARAMETER_VALUE
---------- ---------------------------------------- ---------------
         3 AUTO_INDEX_COMPRESSION                   OFF
         3 AUTO_INDEX_DEFAULT_TABLESPACE
         3 AUTO_INDEX_MODE                          OFF
         3 AUTO_INDEX_REPORT_RETENTION              31
         3 AUTO_INDEX_RETENTION_FOR_AUTO            373
         3 AUTO_INDEX_RETENTION_FOR_MANUAL
         3 AUTO_INDEX_SCHEMA
         3 AUTO_INDEX_SPACE_BUDGET                  50

Thông tin ý nghĩa chi tiết của các tham số trên bạn có thể xem tại đây

3.2. Làm thế nào bật hoặc tắt tính năng tự động tạo index trong Oracle

Bạn để ý giá trị AUTO_INDEX_MODE mà tôi bôi đỏ ở phần trước.

Tham số này có thể nhận 3 giá trị

  • OFF: Tính năng bị tắt
  • REPORT ONLY: Tính năng đã được bật. Các index tự động tạo ra nhưng chỉ ở trạng thái “tàng hình” (invisible). Các câu lệnh trong hệ thống chưa nhận các index  mới này.
  • IMPLEMENT: Tính năng đã được bật. Các index được tự động tạo ra và có thể ảnh hưởng ngay đến hiệu năng của cơ sở dữ liệu. Việc ảnh hưởng theo chiều hướng tốt hay xấu thì phải tùy nhé anh em, vì thế tôi mới khuyên mọi người nên đánh giá trước.

Để chuyển trạng thái AUOT_INDEX_MODE chúng ta sử dụng lệnh sau

exec dbms_auto_index.configure('AUTO_INDEX_MODE','IMPLEMENT');
exec dbms_auto_index.configure('AUTO_INDEX_MODE','REPORT ONLY');
exec dbms_auto_index.configure('AUTO_INDEX_MODE','OFF');

3.3. Cấu hình tablespace lưu trữ các Index tự động tạo ra

Mặc định các index được tự động tạo sẽ chui hết vào DEFAULT TABLESPACE. Việc này chưa hẳn đã tốt, bình thường tôi có thói quen tách riêng các tablespace INDEX và tablespace DATA :).

Nếu bạn muốn các Index tự động tạo ra được đưa riêng vào một tablespace thì làm như sau

# Chuyển kết nối vào PDB 
alter session set container = pdb_ketoan;

#Tạo tablespace TBS_INDEX
create tablespace TBS_INDEX datafile size 100m autoextend on next 100m maxsize unlimited;

#Cấu hình các index tự tạo sẽ vào tablespace auto_indexes_ts 
exec dbms_auto_index.configure('AUTO_INDEX_DEFAULT_TABLESPACE','TBS_INDEX');

3.4. Nếu bạn chỉ muốn sử dụng tính năng  mới này trên  một số schema nhất định

Mặc định Oracle sẽ áp dụng tính năng mới này cho tất cả các schema.

  • Nếu như chúng ta muốn chỉ áp dụng chỉ cho 2 schema HUYTRAN và TALENT5 thì làm như sau
exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', 'HUYTRAN', allow => TRUE);
exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', 'TALENT5', allow => TRUE);

SQL> select con_id, parameter_name, parameter_value from cdb_auto_index_config order by 1, 2;

    CON_ID PARAMETER_NAME                           PARAMETER_VALUE
---------- ---------------------------------------- ----------------------------------------
         3 AUTO_INDEX_COMPRESSION                   OFF
         3 AUTO_INDEX_DEFAULT_TABLESPACE            AUTO_INDEXES_TS
         3 AUTO_INDEX_MODE                          IMPLEMENT
         3 AUTO_INDEX_REPORT_RETENTION              31
         3 AUTO_INDEX_RETENTION_FOR_AUTO            373
         3 AUTO_INDEX_RETENTION_FOR_MANUAL
         3 AUTO_INDEX_SCHEMA                        schema IN (HUYTRAN, TALENT5)
         3 AUTO_INDEX_SPACE_BUDGET                  50
  • Trong trường hợp database của chúng ta có quá nhiều schema, ta muốn áp dụng cho toàn bộ, ngoại trừ 2 user HUYTRAN và TALENT5 thì làm như sau
exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', 'HUYTRAN', allow => FALSE);
exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', 'TALENT5', allow => FALSE);

SQL> @auto_index_config.sql

    CON_ID PARAMETER_NAME                           PARAMETER_VALUE
---------- ---------------------------------------- ----------------------------------------
         3 AUTO_INDEX_COMPRESSION                   OFF
         3 AUTO_INDEX_DEFAULT_TABLESPACE            AUTO_INDEXES_TS
         3 AUTO_INDEX_MODE                          IMPLEMENT
         3 AUTO_INDEX_REPORT_RETENTION              31
         3 AUTO_INDEX_RETENTION_FOR_AUTO            373
         3 AUTO_INDEX_RETENTION_FOR_MANUAL
         3 AUTO_INDEX_SCHEMA                        schema NOT IN (HUYTRAN, TALENT5)
         3 AUTO_INDEX_SPACE_BUDGET                  50

3.5. Làm thế nào để xóa tất cả các index được tự động tạo ra

begin
  dbms_auto_index.drop_auto_indexes(
    owner          => 'HUYTRAN',
    index_name     => null,
    allow_recreate => true);
end;
/

Đoạn lệnh trên giúp bạn xóa tất cả các index được tự động tạo trong schema HUYTRAN, tuy nhiên vẫn chấp nhận việc tự động tạo lại trong tương lai.

4. Một số Views, Report  hữu ích cho DBA

4.1. Các view hay dùng của DBA

select view_name
from   dba_views
where  view_name like 'DBA_AUTO_INDEX%'
order by 1;

VIEW_NAME
--------------------------------------------------------------------------------
DBA_AUTO_INDEX_CONFIG
DBA_AUTO_INDEX_EXECUTIONS
DBA_AUTO_INDEX_IND_ACTIONS
DBA_AUTO_INDEX_SQL_ACTIONS
DBA_AUTO_INDEX_STATISTICS
DBA_AUTO_INDEX_VERIFICATIONS

Giả sử bạn muốn xem các index được tự động tạo:

column owner format a30
column index_name format a30
column table_owner format a30
column table_name format a30

select owner,
       index_name,
       index_type,
       table_owner,
       table_name
       table_type
from   dba_indexes
where  auto = 'YES'
order by owner, index_name;

4.2. Nếu bạn muốn có báo cáo về tình trạng các Index trong Cơ sở dữ liệu

Package DBMS_AUTO_INDEX có thể tạo các báo cáo cho DBA

# Cho biết thông tin trong một khoảng thời gian
DBMS_AUTO_INDEX.REPORT_ACTIVITY (
   activity_start  IN  TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP - 1,
   activity_end    IN  TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP,
   type            IN  VARCHAR2 DEFAULT 'TEXT',
   section         IN  VARCHAR2 DEFAULT 'ALL',
   level           IN  VARCHAR2 DEFAULT 'TYPICAL')
RETURN CLOB;

# Cho biết thông tin của lần tạo index tự động cuối cùng
DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY (
   type            IN  VARCHAR2 DEFAULT 'TEXT',
   section         IN  VARCHAR2 DEFAULT 'ALL',
   level           IN  VARCHAR2 DEFAULT 'TYPICAL')
RETURN CLOB;

Chi tiết cách sử dụng bạn xem tại đây

Ví dụ một mẫu báo cáo:

select dbms_auto_index.report_activity() from dual;

GENERAL INFORMATION
-------------------------------------------------------------------------------
 Activity start               : 03-JUN-2019 21:59:21
 Activity end                 : 04-JUN-2019 21:59:21
 Executions completed         : 2
 Executions interrupted       : 0
 Executions with fatal error  : 0
-------------------------------------------------------------------------------

SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
 Index candidates            : 0
 Indexes created             : 0
 Space used                  : 0 B
 Indexes dropped             : 0
 SQL statements verified     : 0
 SQL statements improved     : 0
 SQL plan baselines created  : 0
 Overall improvement factor  : 0x
-------------------------------------------------------------------------------

SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
 Unused indexes    : 0
 Space used        : 0 B
 Unusable indexes  : 0
-------------------------------------------------------------------------------

ERRORS
---------------------------------------------------------------------------------------------
No errors found.
---------------------------------------------------------------------------------------------

5. Liên hệ với tôi

Leave a Reply

avatar
  Subscribe  
Notify of