Optimizer_mode ค่าไหน? ควรใช้เมื่อใด?

บนฐานข้อมูล Oracle นั้น มีพารามิเตอร์บางตัวที่เป็นดั่งกระสุนเงิน ที่สามารถสร้าง Impact กับประสิทธิภาพบนฐานข้อมูลได้ในระดับกว้าง หนึ่งในกระสุนเงินเพื่อกำราบพวกคิวรี่ผีดิบคือ Optimizer_mode ครับ

ผลกระทบของ Optimizer_mode ที่ทำให้ Execution plan เปลี่ยน!

สำหรับ Optimizer_mode นั้นถือว่าเป็นพารามิเตอร์ที่มีอานุภาพสูงสุดตัวนึงบนฐานข้อมูล Oracle ในด้านของประสิทธิภาพของระบบ และการเปลี่ยนแปลงค่าของมันนั้นสามารถเปลี่ยนแปลงรูปแบบของ SQL execution plan ได้ ซึ่ง Oracle แนะนำว่าคุณควรกำหนดค่า Optimizer_mode ที่เหมาะสมที่สุดกับระบบฐานข้อมูลของคุณเอง โดยขึ้นอยู่กับ Optimizer goals (Performance หรือ Throughput) ความเร็วของผลลัพธ์ที่ต้องการหรือทรัพยากรที่จำเป็นสำหรับการใช้งานสำหรับ Workload ที่เหมาะสมของฐานข้อมูล โดยสำหรับ Optimizer_mode นั้นมีอยู่ด้วยกัน 4 ค่าสำหรับ Cost-based optimizer ทั้ง 4 ค่ามีอัลกอริทึ่มที่แตกต่างกัน แต่หลักการเดียวกันนั่นคือ ทำอย่างไรจึงจะใช้ทรัพยากรที่มีบนระบบให้น้อยที่สุดสำหรับผลลัพธ์ที่ต้องการ โดยในบทความนี้ผมจะมาแสดงให้เห็นว่าระหว่าง FIRST_ROWS และ ALL_ROWS แตกต่างกันอย่างไร? และแบบใด เหมาะสมกับฐานข้อมูลแบบใด?

โดยค่าที่สามารถกำหนดให้กับ Optimizer_mode ได้นั้นมีดังนี้ครับ CHOOSE, ALL_ROWS, FIRST_ROWS, FIRST_ROWS_N โดยค่าเริ่มต้นนั้นคือค่า CHOOSE ซึ่งโดยปกติคือ ALL_ROWS (ถ้าบนตารางนั้นๆ มีค่าสถิติถูกเก็บไว้บน Data dictionary) ส่วนอีกค่าที่เป็นได้ก็คือ RULE (ถ้าไม่มีค่าสถิติถูกเก็บไว้สำหรับตารางนั้นๆ) ซึ่งทาง Oracle เองได้เลิกทำการสนับสนุนและพัฒนาตัว Rule-based นี้ไปแล้วครับ ดังนั้นจึงเป็นเรื่องที่สำคัญมากที่คุณควรจะทำการ Gather statistics ของตารางบนฐานข้อมูลเป็นประจำ

ลองมาดูกันครับว่ามันทำงานอย่างไรสำหรับ ALL_ROWS

สร้างตารางและอินเด็กซ์ขึ้นมาเพื่อทดสอบครับ

SQL> CREATE TABLE TEST1 AS SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('TABLE','VIEW'); 

Table created.

SQL> CREATE INDEX TEST1_IDX ON TEST1(OBJECT_TYPE);

Index created.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS','TEST1')

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) FROM TEST1;

  COUNT(*)
----------
      4064

SQL> SELECT COUNT(*) FROM TEST1 WHERE OBJECT_TYPE = 'VIEW';

  COUNT(*)
----------
      3073

เราจะเห็นได้ว่า ข้อมูลทั้งหมดบนตาราง TEST1 มีทั้งหมด 4,064 บรรทัดและมี Object_type ที่เป็น View อยู่ทั้งหมด 3,073 บรรทัด โดยปกติแล้วถ้า Optimizer_mode ถูกกำหนดให้เป็น CHOOSE หรือ ALL_ROWS นั้น Optimizer จะเลือกที่จะไม่ใช้อินเด็กซ์เพราะผลลัพธ์ที่ต้องการจริงๆ แล้วมีจำนวนเกือบจะทั้งตาราง ต่อไปเราจะมาดูผลลัพธ์ของ Execution plan กันครับ

SQL> SET AUTOTRACE TRACEONLY EXP
SQL> SELECT * FROM TEST1 WHERE OBJECT_TYPE='VIEW';

Execution Plan
----------------------------------------------------------
Plan hash value: 4122059633

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |  2032 |   158K|    11   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST1 |  2032 |   158K|    11   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_TYPE"='VIEW')

จากผลลัพธ์จะเห็นได้ว่า Optimizer เลือกที่จะไม่ใช่อินเด็กซ์ที่เราสร้างขึ้นมาครับ ต่อไปเรามาลองใช้ Optimizer_mode = FIRST_ROWS กันครับ

FIRST_ROWS/FIRST_ROWS_N

เพื่อความเข้าใจง่ายๆ สำหรับค่าๆ นี้นั่นคือ ความเร็วของผลลัพธ์ที่ได้สำหรับผลลัพธ์บรรทัดต้นๆ ของผลลัพธ์ทั้งหมด (Best response times for First few rows) โดยค่านี้จะทำงานได้ดีกับระบบฐานข้อมูลแบบ Online Transaction Processing หรือ OLTP ที่ผู้ใช้ต้องการความรวดเร็วของผลลัพธ์มากๆ โดยการทำงานของค่านี้คือ เมื่อทำการรันคำสั่งคิวรี่นั้นมันจะทำการคืนผลลัพธ์ช่วงต้นๆ กลับมาให้ผู้ใช้ก่อน พร้อมกับทำการดึงข้อมูลในฉากหลังต่อจนกว่าจะเสร็จ ทำให้ผู้ใช้รู้สึกเหมือนกับว่าได้ข้อมูลมาแล้ว แต่จริงๆ แล้วมันกำลังดึงข้อมูลอยู่

SQL> SELECT /*+ FIRST_ROWS */ * FROM TEST1 WHERE OBJECT_TYPE='VIEW';

Execution Plan
----------------------------------------------------------
Plan hash value: 1885706448

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |  2032 |   158K|    42   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST1     |  2032 |   158K|    42   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST1_IDX |  2032 |       |     5   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_TYPE"='VIEW')

ในกรณีนี้ Optimizer เลือกที่จะใช้อินเด็กซ์ครับ คำถามคือทำไม?

คำตอบนั่นก็คือ เมื่อคุณต้องการผลลัพธ์ช่วงบรรทัดแรกๆ อย่างรวดเร็ว ดังนั้น Oracle จึงทำตามความต้องการของคุณซึ่งคือการกำหนดค่าสำหรับ Optimizer_mode = FIRST_ROWS โดยการคืนผลลัพธ์ส่วนบรรทัดแรกๆ กลับมาก่อนโดยการใช้อินเด็กซ์และทำการดึงข้อมูลส่วนที่เหลือตามมาทีหลัง

เมื่อเราดูจากความแตกต่างของ Cost ของทั้งสอง Execution plan ถึงแม้ว่าคิวรี่ในตัวอย่างที่ 2 นั้นจะเร็วกว่าแต่ทรัพยากรที่ถูกใช้งานนั้นย่อมมากกว่าคิวรี่ในตัวอย่างที่ 1 ด้วย แต่นั่นก็ไม่ได้หมายความว่า Optimizer_mode นั้นแย่ อย่างที่ผมได้บอกไปว่า FIRST_ROWS นั้นเหมาะกับฐานข้อมูลแบบ OLTP ที่ผู้ใช้ต้องการผลลัพธ์อย่างไวที่สุดบนหน้าจอ ซึ่ง FIRST_ROWS นั้นสามารถตอบโจทย์ในข้อนี้ได้ครับ

ข้อควรรู้สำหรับ FIRST_ROWS หรือหมายถึงความรวดเร็วของผลลัพธ์ (Response time)

1. มันจะเลือกใช้งาน Index scan มากกว่าจะใช้ Full table scan เสมอ ถึงแม้ว่าอินเด็กซ์จะแย่แค่ไหน
2. มันจะเลือกใช้ Nested loop มากกว่าจะใช้ Hash joins เนื่องจาก Nested loop จะทำการ Join บรรทัดต่อบรรทัดแล้วคืนผลลัพธ์แต่ Hash join ต้องทำการ join ตารางใน Hash table ก่อนซึ่งใช้เวลามากกว่า
3. Cost ไม่ใช่สิ่งเดียวที่ Optimizer ใช้เพื่อเลือก Execution plan แต่มันจะเลือก Execution plan ที่ช่วยให้ได้ผลลัพธ์อย่างเร็วที่สุดด้วย
4. มันอาจจะเป็นสิ่งที่ดีที่นำ FIRST_ROWS มาใช้บนระบบฐานข้อมูลแบบ OLTP ที่ผู้ใช้ต้องการข้อมูลอย่างรวดเร็ว

ข้อควรรู้สำหรับ ALL_ROWS หรือหมายถึงปริมาณของผลลัพธ์ (Throughput)

1. ALL_ROWS จะพิจารณาทั้ง Index scan และ Table scan โดยขึ้นอยู่กับคิวรี่นั้นๆ เป็นอย่างไร ถ้าผลลัพธ์ที่ต้องการนั้นน้อย Optimizer จะเลือกใช้อินเด็กซ์ แต่ถ้าผลลัพธ์ที่ต้องการมีจำนวนมากกว่าครึ่งหนึ่งของตาราง มันจะเลือก Table scan สำหรับ ALL_ROWS นั้น Optimizer มีอิสระมากกว่าที่จะเลือกเส้นทางที่คิดว่าดีที่สุด
2. ดีสำหรับระบบฐานข้อมูลแบบ Online Analytic Processing (OLAP) ที่ถูกใช้งานโดยงานประมวลผลขนาดใหญ่เช่น Batches หรือ Procedures
3. เลือกที่จะใช้งาน Hash join มากกว่าจะใช้ Nested loop สำหรับการ join ตารางที่มีขนาดใหญ่

สรุป

การปรับแต่ง Cost-based optimizer นั้นสามารถกำหนดได้ตามที่เราต้องการ ขึ้นอยู่กับว่าระบบฐานข้อมูลที่เราต้องการนั้นมีจุดมุ่งหมายคืออะไร? ความเร็ว(Response time) หรือปริมาณของผลลัพธ์(Throughput) อยู่ที่เราจะกำหนดเองครับ

ขอบคุณครับ

ขอบคุณข้อมูลประกอบจาก Sachin Arora’s blog: Optimizer_mode – ALL_ROWS or FIRST_ROWS?

Advertisements

ใส่ความเห็น

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  เปลี่ยนแปลง )

Google+ photo

You are commenting using your Google+ account. Log Out /  เปลี่ยนแปลง )

Twitter picture

You are commenting using your Twitter account. Log Out /  เปลี่ยนแปลง )

Facebook photo

You are commenting using your Facebook account. Log Out /  เปลี่ยนแปลง )

w

Connecting to %s