Adaptive direct path read บน Oracle 11g คืออะไร?

สวัสดีครับ! ก็ไม่ได้เขียนบทความซะนานต้องขออภัยจริงๆ เพราะว่าช่วงนี้งานโถมเข้าใส่แบบไม่ยั้ง ฮ่าๆ วันนี้เลยบอกตัวเองว่างานหนักยังไงก็ต้องเขียนบทความใหม่ให้ได้ โดยส่วนตัวถือเป็นความรู้ใหม่ของตัวเองด้วย เลยอยากให้ทุกคน(ที่เผลอเข้ามาอ่าน) ได้รับรู้ไปกับผมด้วยครับ และในวันนี้ผมจะพูดถึง Adaptive direct path read บน Oracle 11g มันคืออะไร? ดีหรือไม่อย่างไร? ลองอ่านกันดูครับ:)

ก่อนจะมาเป็น Adaptive direct path read

ต้องเกริ่นก่อนครับว่าก่อนหน้าจะมีเจ้าตัว Adaptive direct path read ตัวนี้ มันเกิดจากว่าถ้าเราทำการ Full table scan โดยใช้ Parallel นั้น Oracle จะทำการเปลี่ยนวิธีคิดโดยปกติจะต้อง Fetch ข้อมูลขึ้นมาบน Buffer cache ก่อนจึงทำการคืนผลลัพธ์ มาเป็นการ Fetch ข้อมูลตรงๆ จาก Datafile ซึ่งกระบวนการดังกล่าวนี้เรียกว่า “Direct path read” ลองดูตัวอย่างด้านล่างนี้ครับ

บน Oracle 11g

SQL> CREATE TABLE T TABLESPACE TBSP_INFRA00 AS SELECT * FROM DBA_SOURCE WHERE 1=2;

Table created.

SQL> INSERT /*+ APPEND */ INTO T SELECT * FROM DBA_SOURCE;

146662 rows created.

SQL> COMMIT;

Commit complete.

SQL> INSERT /*+ APPEND */ INTO T SELECT * FROM DBA_SOURCE;

146662 rows created.

SQL> COMMIT;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('SYS','T');

PL/SQL procedure successfully completed.

SQL> SELECT blocks FROM user_tables WHERE table_name = 'T';

    BLOCKS
----------
      5938

SQL> select ksppstvl
  2  from x$ksppi x, x$ksppcv y
  3  where (x.indx = y.indx)
  4  and ksppinm='_small_table_threshold';

KSPPSTVL
--------------------------------------------------------------------------------
1119

SQL>

ขั้นแรกผมทำการสร้างตารางใหม่ขึ้นมาให้มีจำนวน Blocks มากกว่า Hidden parameter ที่ชื่อ _small_table_threshold ซึ่งพารามิเตอร์ตัวนี้นั้นจะเป็นตัวกำหนด Baseline ว่า เมื่อใดที่คิวรี่จะทำการ Direct path read หรือเมื่อใดจะใช้วิธีการเดิมคือ DB file scattered read โดยจำนวน Block ของตารางจำเป็นต้องมากกว่า _small_table_threshold ราวๆ 5 เท่าขึ้นไปครับ

จำนวน Block บนตาราง T = 5,938 Blocks
_small_table_threshold = 1,119 Blocks

ดังนั้นเมื่อใดที่จำนวน Block ของตารางนั้นๆ มีจำนวนมากกว่า 1,119*5 = 5,595 Blocks เมื่อนั้น Oracle จะตัดสินใจที่จะเลือกใช้ Direct path read ครับ ดังนั้นเรามาดูผลกันครับ (ในขั้นตอนนี้ผมใช้ SQL trace เข้ามาช่วยจับ Event ของคิวรี่ครับ)

SQL> ALTER SESSION SET MAX_DUMP_FILE_SIZE = UNLIMITED;

Session altered.

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

Session altered.

SQL> SELECT COUNT(*) FROM T;

  COUNT(*)
----------
    293324

SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

Session altered.

มาดูผลจาก SQL trace กันครับ

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  Disk file operations I/O                        3        0.00          0.00
  db file sequential read                         3        0.00          0.01
  direct path read                             1303        0.02          0.76
  SQL*Net message from client                     2        6.70          6.70
********************************************************************************

จะสังเกตเห็นว่า Oracle ทำการเลือกใช้ Direct path read แทนที่ DB file scattered เนื่องจากเหตุผลด้านบนที่กล่าวถึงไปครับ ที่นี้เรามาลองปรับจำนวน Block ที่น้อยลงแล้วลองทดสอบอีกรอบครับว่า Oracle จะเลือกเส้นทางไหน?

SQL> CREATE TABLE T2 TABLESPACE TBSP_INFRA00 AS SELECT * FROM DBA_SOURCE WHERE 1=2;

Table created.

SQL> INSERT /*+ APPEND */ INTO T2 SELECT * FROM DBA_SOURCE;

146662 rows created.

SQL> COMMIT;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('SYS','T2');

PL/SQL procedure successfully completed.

SQL> SELECT blocks FROM user_tables WHERE table_name = 'T2';

    BLOCKS
----------
      2974

SQL>

คราวนีผมทำการสร้างตารางที่เล็กกว่าเมื่อกี้ซึ่งมีจำนวน Block อยู่ทั้งหมด 2,974 block (ใช้ SQL trace เหมือนเดิมครับ)

SQL> ALTER SESSION SET MAX_DUMP_FILE_SIZE = UNLIMITED;

Session altered.

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

Session altered.

SQL> SELECT COUNT(*) FROM T2;

  COUNT(*)
----------
    146662

SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

เรามาดูผลกันครับ

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  db file sequential read                         2        0.00          0.00
  db file scattered read                        742        0.00          0.44
  SQL*Net message from client                     2        5.02          5.02
********************************************************************************

จากผลลัพธ์จะเห็นว่า Oracle เลือกที่จะใช้วิธีเก่านั่นคือ DB file scattered read เหมือนเดิมครับ เนื่องจากจำนวน Block ของตาราง T2 นั้นมีค่าไม่ถึง 1,119*5 = 5,595 Blocks ทำให้ Oracle เลือกเส้นทางเดิมครับและนี้ก็คือ Feature ใหม่บน Oracle 11g ที่มีชื่อว่า Adaptive direct path read นั่นเองครับ

สรุป

สำหรับตัว Adaptive direct path read นั้นจะให้ผลลัพธ์ที่รวดเร็วกว่า DB file scattered read และมีผลกระทบต่อ Process อื่นๆ น้อยกว่าเนื่องจากมันจะไม่เกิด Latch บน Buffer cache ขึ้น อาจจะเหมาะสมกับระบบงาน Online ที่นานๆ ครั้งมีการ Full table scan ครับ แต่ข้อเสียของมันนั่นคือ ถ้าเปิดใช้บนระบบที่ใหญ่มีการ Full table scan เยอะๆ อาจจะทำให้เกิดคอขวดบน Physical I/O ได้ครับ

วิธีการหลีกเลี่ยง Adaptive direct path read

สำหรับวิธีการหลีกเลี่ยงนั้นมีอยู่ด้วย 3 วิธีครับ
1. หลีกเลี่ยงการใช้ Full table scan ปัญหามาจากไหนต้องต้องเลี่ยงมันซะครับ
2. ปิดการใช้งาน Adaptive direct path read ด้วยการ set event บนฐานข้อมูลด้วยคำสั่งนี้ครับ

SQL> alter system set events '10949 trace name context forever';

ขอบคุณครับ
ขอขอบคุณข้อมูลเพิ่มเติมจาก

Dion Cho – Oracle Performance Storyteller
TANEL PODER blog

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 /  เปลี่ยนแปลง )

Connecting to %s