Optimizer Hints อาวุธหนักในการ Tuning SQL

วันนี้เรามาทำความรู้จักกับ Optimizer hints กันครับ… เคยมั๊ยครับที่บางวันคิวรี่ก็ทำงานได้อย่างรวดเร็ว แต่บางวันก็ช้าเป็นเต่า? เคยมั๊ยครับที่เวลาคุณอัพเกรดระบบฐานข้อมูลจากเวอร์ชั่นเก่าเป็นเวอร์ชั่นใหม่แล้ว ทำไมคิวรี่มันถึงช้าลง? คุณเคยประสบปัญหาเหล่านี้หรือไม่ครับ? วันนี้ผมมีทางออกสำหรับปัญหาเหล่านี้ครับ นั่นก็คือพระเอกของเราวันนี้ “Optimizer Hints”

Optimizer Hints คืออะไร? ดียังไง? ใช้ยังไง? มาดูกันครับ

optimizer hints นั้นเป็นออฟชั่นๆ หนึ่งของ Oracle เพื่อใช้งานกับคิวรี่ที่เราต้องการเปลี่ยนแปลง Execution plan หรือบอกให้ Optimizer นั้นรู้ว่าควรใช้เส้นทางใดๆ ที่เรากำหนดให้ใน Hint โดยเราสาามารถกำหนดให้ Optimizer ใช้เส้นทางนั้นตลอดไปได้ ตราบใดที่ยังมี Hint ถูกกำหนดอยู่ในคิวรี่ สมมุติว่าคุณคือผู้ออกแบบโปรแกรมประยุกต์ (Application designer) คุณอาจจะเข้าใจโครงสร้างข้อมูลบนฐานข้อมูลดีในสิ่งที่บางที Optimizer ก็ไม่รู้ ยกตัวอย่างเช่น คุณอาจจะรู้ดีว่าอินเด็กซ์ตัวใดเหมาะสมที่สุดสำหรับคิวรี่นั้นๆ ด้วยข้อมูลที่คุณมีคุณอาจจะต้องการเรียกใช้ Execution plan ที่ดีกว่า Optimizer เลือก ในกรณีแบบนี้การเรียกใช้ Hint บนคิวรี่ เพื่อบังคับให้ Optimizer สามารถเลือกอินเด็กซ์อย่างที่คุณต้องการได้

และในจุดประสงค์ของบทความนี้นะครับ ผมจะกล่าวถึงการเลือกใช้ Hint เพื่อกำหนดให้ Optimizer เลือกเส้นทางที่เป็น Good-known execution plan สำหรับคุณเมื่อระบบของคุณมีการเปลี่ยนแปลงเกิดขึ้น เช่น มีการอัพเกรดระบบฐานข้อมูลจากเวอร์ชั่นเก่าเป็นเวอร์ชั่นใหม่แล้ว Execution plan เปลี่ยนเป็นต้น เรามาเริ่มกันเลยครับ 😀

พิจารณาจากคิวรี่ต่อไปนี้ครับ

SELECT COUNT('1')
  FROM TB_PRT_SRV_SALE_HDR TPSH, TB_PRT_SRV_SALE_DTL TPSD
 WHERE TPSD.TMP_LOC IS NOT NULL
   AND TPSD.ISSUE_DT IS NULL
   AND TPSD.BILL_NO = TPSH.BILL_NO
   AND TPSD.BRC_CD = TPSH.BRC_CD
   AND TPSD.DLR_CD = TPSH.DLR_CD
   AND TPSH.JOB_ORD_NO = REPLACE(:B3, '-', '')
   AND TPSH.BRC_CD = :B2
   AND TPSH.DLR_CD = :B1

โดย Execution plan หลังจากที่ทำการอัพเกรดระบบฐานข้อมูลเป็นดังนี้ครับ(Bad execution plan)

----------------------------------------------------------------------------------
| Operation                      | PHV/Object Name       |  Rows | Bytes|   Cost |
--------------------------------------------------------------------------------
|SELECT STATEMENT                |----- 2605925249 ------|       |      |     61 |	
|SORT AGGREGATE                  |                       |     1 |   51 |        |	
| NESTED LOOPS                   |                       |     3 |  153 |     61 |	
|  TABLE ACCESS BY INDEX ROWID   |TB_PRT_SRV_SALE_DTL    |    13 |  286 |     57 |
|   INDEX RANGE SCAN             |PK_TB_PRT_SRV_SALE_DTL |     1K|      |     31 |
|  TABLE ACCESS BY INDEX ROWID   |TB_PRT_SRV_SALE_HDR    |     1 |   29 |      2 |	
|   INDEX UNIQUE SCAN            |PK_TB_PRT_SRV_SALE_HDR |     1 |      |      3 |	
----------------------------------------------------------------------------------

และ Good-known execution plan ที่เราต้องการเป็นดังนี้ครับ

-----------------------------------------------------------------------------------
| Operation                      | PHV/Object Name        |  Rows | Bytes|   Cost |
--------------------------------------------------------------------------------
|SELECT STATEMENT                |------ 3279852556 ------|       |      |     12 |
|SORT AGGREGATE                  |                        |     1 |   51 |        |
| TABLE ACCESS BY INDEX ROWID    |TB_PRT_SRV_SALE_DTL     |     1 |   22 |      2 |
|  NESTED LOOPS                  |                        |     1 |   51 |     12 |
|   INDEX RANGE SCAN             |TB_PRT_SRV_SALE_HDR_IDX |     1 |   29 |    104 |
|   INDEX RANGE SCAN             |PK_TB_PRT_SRV_SALE_DTL  |     1 |      |      4 |
-----------------------------------------------------------------------------------

ขั้นตอนในการกำหนด Hint ให้กับคิวรี่มีดังนี้ครับ
1. กำหนดลำดับการ Join ตาราง
2. วิธีการ Join ตาราง
3. เรียกใช้ Index ที่ต้องการและส่วนอื่นๆ เพิ่มเติม

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

1. กำหนดลำดับการ Join ตารางให้กับคิวรี่
อันดับแรกเลยคือการกำหนดลำดับการ Join ตารางครับ เริ่มด้วยการดูจาก Good execution plan และกำหนดว่า Join กันอย่างไร ในกรณีนี้จาก Good execution plan เลือกให้ใช้ Nested Loops Join โดยที่มีการอ่านข้อมูลจาก TB_PRT_SRV_SALE_HDR ก่อนหรือเป็น Driving table ส่วน TB_PRT_SRV_SALE_DTL จะเป็น Join table ครับ โดยหลักการที่ควรจำนั่นก็คือ ก่อนที่เราจะกำหนดวิธีการ Join ตารางนั้น เราควรที่จะกำหนดลำดับการ Join ตารางก่อนเสมอ เหตุผลก็คือ Optimizer จะคำนวนหาเส้นทางที่ดีที่สุดโดยดูจากลำดับการ Join ตารางเป็นจุดเริ่มต้นเสมอ มันจะคำนวนทุกเส้นทางที่เป็นไปได้แล้วจะเลือก Execution plan ที่มี Cost น้อยที่สุดเสมอ ซึ่งถ้าเราไม่กำหนดลำดับให้กับคิวรี่แล้ว มันก็มีความเป็นไปได้ที่ Optimizer จะเลือกลำดับการ Join ตารางเองโดยใช้วิธีที่ได้กล่าวถึงไปนั้นคือการเลือกเส้นทางที่มี Cost น้อยที่สุดได้ครับ และนี้เป็นข้อควรจำที่เราจำเป็นต้องกำหนดลำดับการ Join ตารางเสมอเพื่อให้มั่นใจได้ว่าเมื่อเรากำหนดวิธีการ Join ให้กับคิวรี่แล้ว เราจะได้ Execution plan ที่ต้องการจริงๆ ครับ

ซึ่งจากคิวรี่นี้ ลำดับการ Join ตารางคือ TB_PRT_SRV_SALE_HDR ตามมาด้วย TB_PRT_SRV_SALE_DTL ซึ่งในคิวรี่ได้กำหนดไว้แล้วครับดังนี้

FROM TB_PRT_SRV_SALE_HDR TPSH, TB_PRT_SRV_SALE_DTL TPSD

จากคิวรี่นั้น จะเห็นได้ว่าลำดับการ Join ตารางนั้นอยู่ในลำดับที่ถูกต้องแล้ว ดังนั้นเราสามารถใช้ ORDERED hint บนคิวรี่นี้ได้ หรือ LEADING hint เพื่อบอกให้ Optimizer รู้ว่าเราต้องการให้ตารางใดเป็นตัวตั้งได้ครับ และหน้าตาของ Hint ที่เราต้องใส่ให้กับคิวรี่เป็นดังนี้ครับ

/*+ ORDERED */

หรือ

/*+ LEADING(TPSH) */

(เราจำเป็นต้องใช้ Alias ในการอ้างถึงตารางนั้นๆ ถ้ามีการกำหนด Alias ให้กับตารางครับ)

2. การกำหนดวิธีการ Join ตารางให้กับคิวรี่
เมื่อเราได้กำหนดลำดับการ Join ตารางแล้ว ขั้นต่อมาคือการกำหนดวิธีการ Join ให้กับคิวรี่ครับ ซึ่งในกรณีในนั้นคือ Nested Loops Join ซึ่งเราสามารถใช้ USE_NL hint ดังนี้ครับ

/*+ ORDERED USE_NL(TPSD) */

หรือ

/*+ LEADING(TPSH) USE_NL(TPSD) */

3. เรียกใช้ Index ที่ต้องการและส่วนอื่นๆ เพิ่มเติม
จากที่เรากำหนดไปเบื้องต้นจากข้อ 1 และข้อ 2 นั้น บางครั้งก็เพียงพอที่จะทำให้ Optimizer รู้ว่าควรเลือกเส้นทางใดในการรันคิวรี่แล้ว แต่เราสามารถกำหนด Index hint ให้กับคิวรี่เพื่อเพิ่มความมั่นใจได้ครับ

จาก Good execution plan นั้น การเลือกใช้อินเด็กซ์มีดังนี้ครับ
A. ตาราง TB_PRT_SRV_SALE_HDR จะเลือกใช้อินเด็กซ์ TB_PRT_SRV_SALE_HDR_IDX
B. ตาราง TB_PRT_SRV_SALE_DTL จะเลือกใช้อินเด็กซ์ PK_TB_PRT_SRV_SALE_DTL

และ Hint ที่เสร็จสมบูรณ์พร้อมจะเป็นอาวุธหนักพร้อมที่จะทำลายล้างทุกสิ่งให้กับคิวรี่นี้ (เว่อร์จริงๆ) เป็นดังนี้ครับ

/*+ ORDERED USE_NL(TPSD) INDEX(TPSH TB_PRT_SRV_SALE_HDR_IDX) INDEX(TPSD PK_TB_PRT_SRV_SALE_DTL) */

หรือ

/*+ LEADING(TPSH) USE_NL(TPSD) INDEX(TPSH TB_PRT_SRV_SALE_HDR_IDX) INDEX(TPSD PK_TB_PRT_SRV_SALE_DTL) */

และนี่ก็เป็นตัวอย่างง่ายๆ สำหรับการใช้ Optimizer hints เพื่อช่วยให้การจูนนิ่งคิวรี่หรือเป็นการบอกกับ Optimizer ว่าเราอยากให้มันเลือกเส้นทางใดๆ นั้นง่ายขึ้นครับ โดยที่เราไม่จำเป็นต้องไปแตะคิวรี่นั้นๆ เลยครับ 😀

ข้อมูลเพิ่มเติมเกี่ยวกับ Optimizer Hints
Oracle9i Database Performance Tuning Guide and Reference

 

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s