OCI · Autonomous Database · Performance Lab

Auto Indexing บน Oracle Autonomous Database
บันทึกผลการทดสอบบน OCI Free Tier (รีเจียนสิงคโปร์)

บทความนี้บันทึกกระบวนการ Auto Indexing ของ Oracle Autonomous Transaction Processing (ATP) และเปรียบเทียบตัวชี้วัดประสิทธิภาพก่อน-หลัง บนตารางทดสอบ TEST_SALES ขนาด 1,000,000 แถว

1,000,000

แถวในตาราง TEST_SALES

0.82 s → <0.002 s

Elapsed Time (ต่อคิวรี)

23,027,581 → 123

Buffer Gets (ต่อคิวรี)

1,483 → 102

Optimizer Cost

กลไกการทำงาน

วงจรการทำงานของ Auto Indexing (5 ขั้นตอน)

Oracle Autonomous Database ประเมินและสร้างดัชนีผ่านกระบวนการอัตโนมัติ 5 ขั้นตอน โดยไม่ต้องการการแทรกแซงจาก DBA

Step 1 · Identify Candidates

ตรวจจับ SQL ที่เหมาะสมกับการทำดัชนี

Auto Indexing Engine สแกน SQL Workload History (จาก V$SQL และ AWR) เพื่อค้นหาคำสั่ง SQL ที่รันซ้ำบ่อยและมีการอ้างอิงคอลัมน์ที่ยังไม่มีดัชนีรองรับ โดยพิจารณาจาก Elapsed Time, Buffer Gets และความถี่การเรียกใช้งาน

-- ตัวอย่าง: ค้นหา SQL ที่อ้างอิงตาราง TEST_SALES
-- และยังไม่มี Index รองรับคอลัมน์ที่ใช้กรอง
SELECT sql_id, executions, elapsed_time/executions elapsed_per_exec,
       buffer_gets/executions gets_per_exec,
       sql_text
FROM   v$sql
WHERE  sql_text LIKE '%TEST_SALES%'
  AND  sql_text NOT LIKE '%SYS_AI%'
ORDER BY gets_per_exec DESC;

Step 2 · Create Invisible Index

สร้างดัชนีในสถานะ INVISIBLE

ระบบสร้างดัชนีโครงสร้าง B-Tree บนคอลัมน์ที่เป็น candidate โดยกำหนดสถานะเป็น INVISIBLE ดัชนีชื่อขึ้นต้นด้วย SYS_AI_ จะไม่ถูก CBO นำไปใช้ในแผนการทำงาน (Execution Plan) ของผู้ใช้ทั่วไป จนกว่าจะผ่านการตรวจสอบ

-- ระบบรันคำสั่งนี้เบื้องหลังโดยอัตโนมัติ
CREATE INDEX SYS_AI_cr24sdqwm1kwp
ON TEST_SALES(CUSTOMER_ID)
METADATA(AUTO_INDEXING) INVISIBLE;

-- ตรวจสอบสถานะ
SELECT index_name, visibility, status
FROM   dba_indexes
WHERE  index_name = 'SYS_AI_CR24SDQWM1KWP';

Step 3 · Verify

ทดสอบประสิทธิภาพในระบบ Background

ระบบจำลองการรัน SQL โดยบังคับให้ CBO ใช้ดัชนีใหม่ (ผ่าน Hint หรือ SQL Plan Management) และเปรียบเทียบ Elapsed Time กับ Buffer Gets กับแผนเดิม หากดัชนีใหม่ให้ผลดีกว่าอย่างมีนัยสำคัญ จะผ่านเข้าสู่ขั้นตอนต่อไป

-- รายงานผลการตรวจสอบจาก Auto Indexing Log
SELECT idx_name, idx_key_list,
       error_msg, auto_index_improvement
FROM   dba_auto_index_verifications
WHERE  table_name = 'TEST_SALES'
ORDER BY last_updated DESC;

Step 4 · Implement (VISIBLE)

เปลี่ยนสถานะเป็น VISIBLE

เมื่อผ่านการตรวจสอบ ระบบจะเปลี่ยนสถานะดัชนีเป็น VISIBLE และ VALID CBO จะเริ่มพิจารณาดัชนีนี้ในการคำนวณแผนการทำงาน ผลลัพธ์คือ Execution Plan เปลี่ยนจาก TABLE ACCESS FULL ไปเป็น INDEX RANGE SCAN

-- ระบบดำเนินการเปลี่ยนสถานะ
ALTER INDEX SYS_AI_cr24sdqwm1kwp VISIBLE;

-- ยืนยันสถานะหลังเปิดใช้งาน
SELECT index_name, visibility, status
FROM   dba_indexes
WHERE  index_name = 'SYS_AI_CR24SDQWM1KWP';
-- VISIBILITY: VISIBLE | STATUS: VALID

Step 5 · Clean Up

ลบดัชนีที่ไม่ถูกใช้งาน

ระบบตรวจสอบสถิติการใช้งานดัชนีอย่างต่อเนื่อง ดัชนีที่ระบบสร้างขึ้นโดยอัตโนมัติและไม่มีการเรียกใช้เกินช่วงเวลาที่กำหนด (ค่า default คือ 373 วัน) จะถูกลบออกเพื่อป้องกันผลกระทบด้าน DML overhead

-- ตรวจสอบดัชนีที่ระบบสร้างและไม่ถูกใช้
SELECT index_name, last_used, 
       auto_index_type, visibility
FROM   dba_indexes
WHERE  auto                = 'YES'
  AND  (last_used IS NULL OR last_used < SYSDATE - 373)
ORDER BY last_used NULLS FIRST;

ผลการทดสอบ · Interactive

เปรียบเทียบตัวชี้วัดก่อน-หลัง

กดปุ่มด้านล่างเพื่อจำลองลำดับเหตุการณ์ของ Auto Indexing และดูการเปลี่ยนแปลงตัวชี้วัด

ATP SQL Console (OCI · ap-singapore-1)

Full Table Scan

0.820 s

Elapsed Time

23,027,581

Buffer Gets

1,483

Optimizer Cost

SYS@ATP_SG>

Execution Plan ก่อนมีดัชนี — Optimizer เลือก TABLE ACCESS FULL เนื่องจากไม่มีดัชนีบนคอลัมน์ CUSTOMER_ID

--------------------------------------------------------------------------
| Id | Operation           | Name       | Rows | Cost(%CPU)| Time     |
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT    |            |    1 |  1483  (1)| 00:00:01 |
|  1 |  SORT AGGREGATE     |            |    1 |           |          |
|* 2 |   TABLE ACCESS FULL | TEST_SALES |  100 |  1483  (1)| 00:00:01 |
--------------------------------------------------------------------------
Predicate: 2 - filter("CUSTOMER_ID"=:1)

Execution Plan หลังมีดัชนี — Optimizer เปลี่ยนเป็น INDEX RANGE SCAN บน SYS_AI_cr24sdqwm1kwp

--------------------------------------------------------------------------------------------
| Id | Operation                     | Name                 | Rows | Cost(%CPU)| Time     |
--------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT              |                      |    1 |  102   (0)| 00:00:01 |
|  1 |  SORT AGGREGATE               |                      |    1 |           |          |
|  2 |   TABLE ACCESS BY INDEX ROWID | TEST_SALES           |  100 |  102   (0)| 00:00:01 |
|* 3 |    INDEX RANGE SCAN            | SYS_AI_cr24sdqwm1kwp |  100 |    3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate: 3 - access("CUSTOMER_ID"=:1)

การสาธิต

วิดีโอบันทึกและภาพสรุปลำดับเหตุการณ์

คลิปบันทึกหน้าจอแสดงการเปลี่ยน Execution Plan และภาพสรุปขั้นตอนการทำงานของ Auto Indexing

01 — Baseline

Full Table Scan — ก่อนมีดัชนี

Buffer Gets 23,027,581, Elapsed Time ~0.82 วินาที, Optimizer Cost 1,483

02 — Background Process

สร้าง Invisible Index เพื่อทดสอบ

ระบบสร้าง SYS_AI_cr24sdqwm1kwp (INVISIBLE) และรัน SQL เปรียบเทียบประสิทธิภาพ

03 — Verification & Activation

ผ่านการตรวจสอบ — เปลี่ยนเป็น VISIBLE

สถานะดัชนี: VISIBLE / VALID — CBO เริ่มพิจารณาดัชนีนี้ในการสร้าง Execution Plan

04 — After Auto Indexing

Execution Plan: INDEX RANGE SCAN

Buffer Gets 123, Elapsed Time <0.002 วินาที, Optimizer Cost 102

Performance Tuning Autonomous Database สิงหาคม 2569

Auto Indexing บน Oracle Autonomous Database: วิเคราะห์กลไกและผลการทดสอบ

ในการบริหารจัดการระบบฐานข้อมูลเชิงสัมพันธ์ขนาดใหญ่ การกำหนดดัชนีที่เหมาะสม (Index Design) ถือเป็นปัจจัยหลักที่ส่งผลต่อประสิทธิภาพการสืบค้นข้อมูล หากคอลัมน์ที่ใช้กรองข้อมูลในเงื่อนไข WHERE ไม่มีดัชนีรองรับ Cost-Based Optimizer (CBO) จะเลือกแผน TABLE ACCESS FULL ส่งผลให้ I/O สูงตามสัดส่วนขนาดของตาราง

Oracle แก้ไขปัญหานี้ด้วยฟีเจอร์ Auto Indexing บน Oracle Autonomous Database ซึ่งรันบน Exadata Cloud Infrastructure โดย AI Engine ของระบบจะทำหน้าที่วิเคราะห์ Workload, สร้าง, ทดสอบ และบริหารดัชนีโดยอัตโนมัติ

สภาพแวดล้อมการทดสอบ

รายการ	ค่า
Platform	Oracle Autonomous Transaction Processing (ATP) — Free Tier
OCI Region	ap-singapore-1
ตาราง	TEST_SALES
จำนวนแถว	1,000,000
จำนวน Customer ID ที่ไม่ซ้ำกัน	10,000
SQL ที่ทดสอบ	`SELECT SUM(sale_amount) FROM TEST_SALES WHERE customer_id = :bind_val`

กระบวนการทำงานของ Auto Indexing

Auto Indexing ทำงานผ่านวงจร 5 ขั้นตอน ดังนี้:

1. Identify Candidates — สแกน SQL Workload History เพื่อระบุคอลัมน์ที่เป็น candidate สำหรับการทำดัชนี
2. Create Invisible Indexes — สร้างดัชนีในสถานะ INVISIBLE ชื่อขึ้นต้นด้วย SYS_AI_
3. Verify / Test Run — ทดสอบประสิทธิภาพ SQL แบบ Background เปรียบเทียบ Buffer Gets และ Elapsed Time
4. Implement — เปลี่ยนสถานะดัชนีเป็น VISIBLE หากผ่านเกณฑ์ประสิทธิภาพ
5. Clean Up — ลบดัชนีที่ไม่ถูกใช้งานเกิน 373 วัน (ค่าเริ่มต้น) เพื่อลด DML overhead

ผลการทดสอบ

ดัชนีที่ระบบสร้างขึ้นอัตโนมัติ

หลังจากรัน Workload ซ้ำบนคอลัมน์ CUSTOMER_ID ซึ่งยังไม่มีดัชนีรองรับ ระบบตรวจจับและสร้างดัชนีชื่อ SYS_AI_cr24sdqwm1kwp โดยอัตโนมัติ:

ประเภท: B-TREE
ตาราง: TEST_SALES
คอลัมน์: CUSTOMER_ID
สถานะ Visibility: VISIBLE
สถานะ Validation: VALID

ตัวชี้วัดก่อน-หลัง (ต่อคิวรี)

ตัวชี้วัด	ก่อน (Full Table Scan)	หลัง (Auto Index)	การเปลี่ยนแปลง
Elapsed Time (วินาที)	~0.82	<0.002	ลดลง ~99.8%
Buffer Gets	23,027,581	123	ลดลง 99.999%
CPU Time (วินาที)	58.7	2.2	ลดลง ~96%
Optimizer Cost	1,483	102	ลดลง ~93%

การเปลี่ยนแปลงของ Execution Plan

การเปลี่ยนแปลงของ Buffer Gets จาก 23,027,581 เป็น 123 เป็นผลโดยตรงจากการที่ CBO เปลี่ยน Operation จาก TABLE ACCESS FULL ไปเป็น INDEX RANGE SCAN บนดัชนี SYS_AI_cr24sdqwm1kwp

แทนที่จะอ่านทุก Data Block ในตาราง (~23 ล้าน Logical Read) Optimizer อ่านเฉพาะ Index Entry ที่ตรงกับค่า CUSTOMER_ID แล้วเข้าถึง Row แบบ ROWID โดยตรง ซึ่งกิน Logical Read เพียง 3 (Index) + 120 (Table Rows) = 123 ครั้ง

ข้อควรพิจารณา

Auto Indexing เหมาะกับ Workload ที่มี Pattern การสืบค้นซ้ำและคงที่ สำหรับ Workload ที่แปรผันบ่อย ผลลัพธ์อาจแตกต่างกัน
ดัชนีที่ระบบสร้างขึ้น (SYS_AI_) ไม่สามารถแก้ไขหรือ Rebuild ด้วยตนเองโดยตรง ต้องใช้ DBMS_AUTO_INDEX หากต้องการปรับค่าพฤติกรรม
การทดสอบนี้ใช้ตาราง TEST_SALES ที่สร้างขึ้นสำหรับวัตถุประสงค์นี้โดยเฉพาะ ค่าตัวเลขอาจแตกต่างไปตามโครงสร้างข้อมูลจริงในระบบงาน
Buffer Gets ในระดับ 23 ล้านบนตาราง 1 ล้านแถว ชี้ให้เห็นว่ามีการเรียกบล็อกซ้ำ (Multi-block Read หลายรอบ) ซึ่งเป็นลักษณะที่พบเมื่อไม่มี Cluster Factor ที่ดี

อ้างอิง

เอกสารอ้างอิง

Oracle Corporation. Autonomous Database Administrator's Guide: Using Auto Indexing. docs.oracle.com/autonomous-database
Oracle Corporation. DBMS_AUTO_INDEX Package Reference. Oracle Database PL/SQL Packages and Types Reference 19c / 21c.
Oracle Corporation. Oracle Database Performance Tuning Guide: Using Explain Plan. docs.oracle.com
Antognini, C. Troubleshooting Oracle Performance, 2nd edition. Apress, 2014.

Auto Indexing บน Oracle Autonomous Databaseบันทึกผลการทดสอบบน OCI Free Tier (รีเจียนสิงคโปร์)