Histogram statistics และผลกระทบจาก Bind variable

กรณีศึกษา: การรวบรวม Histogram statistics บนตารางและผลกระทบจากการใช้ Bind variables

สำหรับบทความในวันนี้ ผมจะพูดถึง Histogram statistics และผลกระทบของการใช้ Bind variables ต่อ Execution plan โดยผมจะกล่าวถึง บทความหนึ่งของท่านอาจารย์ของผมครับ

เมื่อเราทำการ Gather Statistics กับตารางที่ข้อมูลไม่ได้มีการปลี่ยนแปลงสามารถทำให้ Query Plan ที่เกิดกับคิวรีบนตารางเหล่านั้นเปลี่ยนไปได้ เช่นหากวันหนึ่งเราทำการ Gather Statistics รันคิวรี (และบันทึก Query Plan) เอาไว้ วันต่อมาเรา Gather Statistics อีก (และไม่ได้แก้ไขข้อมูลเดิมแต่อย่างใด) เมื่อเราคิวรีข้อมูลในตารางเหล่านั้นเราอาจจะพบว่า Query Plan ที่ได้ เปลี่ยนไป ซึ่ง Query Plan ที่ได้ ดูเหมือนวาจะดีกว่าตัวก่อน สาเหตุเป็นเพราะอะไร?

via Tanakorn Thai Oracle DBA: Query Plan เปี๊ยนไป๋!!?.

โดยสรุปแล้วเมื่อทำการรวบรวมสถิติบนตารางใดๆ โดยกำหนด METHOD_OPT เป็น AUTO (AUTO ปกติเป็นค่าดีฟอลต์) นั่นซึ่งเป็นสาเหตุที่ทำให้ Oracle รวบรวม Histogram statistics และรับรู้ความเบ้ (skew) ของข้อมูลบนตารางนั้นๆ นั่นเองครับ

แต่ในบทความนี้ผมจะพูดถึงผลกระทบเมื่อเรากำหนดให้คิวรี่ของเราใช้ Bind variables บนตารางที่มีการรวบรวมสถิติด้วยการใช้ METHOD_OPT=AUTO ครับ

(ขออนุญาตอ้างอิงจากบทความที่ผมเกริ่นนำไปในเบื้องต้นนะครับ ควรดูข้อมูลจากบทความด้านบนประกอบนะครับ)

SQL> select column_name, count(*)
  2  from user_tab_histograms
  3  where table_name = 'T'
  4  and column_name = 'ID'
  5  group by column_name;

COLUMN_NAME                    COUNT(*)
------------------------------ ----------
ID                             6

โดยในตอนนี้ Oracle นั้นสามารถเก็บรวบรวมสถิติบนตาราง T ได้ค่าที่แตกต่างกัน 6 ค่า นั่นคือ (0,1,2,3,4,99) ดังนี้ครับ

SQL> select id,count(*)
  2  from t
  3  group by id
  4  order by id;

ID         COUNT(*)
---------- ----------
0          2122
1          2122
2          2123
3          2122
4          2122
99         1

6 rows selected.

เราจะเห็นว่าข้อมูลบนตารางนั้นมีการเบ้ (skew) ของข้อมูลค่อนข้างมาก ทีนี้จะเกิดอะไรขึ้นถ้าผมทำการเขียนคิวรี่ขึ้นมาใหม่โดยมีการใช้ Bind variables แทนค่า Literal variable ครับ

SQL> alter system flush shared_pool; -- Clear shared_pool to parse all incoming sql

System altered.

SQL> var val1 number
SQL> set autotrace traceonly explain statistics
SQL> exec :val1 := 1;

PL/SQL procedure successfully completed.

SQL> select *
  2  from t
  3  where id = :val1;

2122 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)|  Time     |
--------------------------------------------------------------------------
| 0  | SELECT STATEMENT   |      |  1769|  152K | 28 (0)     | 00:00:01  |
|* 1 | TABLE ACCESS FULL  | T    |  1769|  152K | 28 (0)     | 00:00:01  |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
     1 - filter("ID"=TO_NUMBER(:VAL1))

Statistics
----------------------------------------------------------
     467 recursive calls
       0 db block gets
     353 consistent gets
     144 physical reads
       0 redo size
  135482 bytes sent via SQL*Net to client
    2039 bytes received via SQL*Net from client
     143 SQL*Net roundtrips to/from client
       5 sorts (memory)
       0 sorts (disk)
    2122 rows processed

เราจะเห็นว่าจากสถิติที่ได้มาคิวรี่ที่มี Bind variable นั้น (ID=1) ซึ่งมีจำนวนบรรทัดราวๆ 20% ของทั้งตาราง Optimizer จะเลือกการอ่านข้อมูลทั้งตาราง (Full table scan) ต่อไปผมจะทำการเปลี่ยนค่าของ Bind variable จาก 1 เป็น 99 แล้วมาดูผลกันครับ

SQL> exec :val1 := 99;

PL/SQL procedure successfully completed.

SQL> select *
  2  from t
  3  where id = :val1;

Execution Plan
--------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)|  Time     |
--------------------------------------------------------------------------
| 0  | SELECT STATEMENT   |      | 1769 | 152K | 28 (0)      | 00:00:01  |
|* 1 | TABLE ACCESS FULL  | T    | 1769 | 152K | 28 (0)      | 00:00:01  |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
--------------------------------------------------------------------------
     1 – filter("ID"=TO_NUMBER(:VAL1))

Statistics
--------------------------------------------------------------------------
       0 recursive calls
       0 db block gets
     144 consistent gets
       0 physical reads
       0 redo size
    1460 bytes sent via SQL*Net to client
     488 bytes received via SQL*Net from client
       2 SQL*Net roundtrips to/from client
       0 sorts (memory)
       0 sorts (disk)
       1 rows processed

เกิดอะไรขึ้นกับคิวรี่นี้ ถึงแม้ว่า ค่า ID = 99 จะมีเพียง 1 บรรทัด ทำไมถึงไม่เลือกใช้ Index scan? เรื่องมันเป็นดังนี้ครับ…

ในการแปลคิวรี่ที่ถูกเรียกใช้ Bind variable ครั้งแรกนั้น (Hard parse) Oracle จะรอจนกว่า Cursor ถูกเปิดขึ้นมาเพื่อทำการ Optimize ซึ่งนั่นหมายถึงมันจะรอจนกว่าค่าของ Bind variable นั้นๆ ถูกส่งเข้ามาโดยแอพพลิเคชั่นก่อนจะคำสั่งจะถูกแปลออกมาเป็น Execution plan ซึ่งหลักการนี้เราเรียกว่า “Bind variable peeking” ดังนั้นถึงแม้ว่า ID=99 จะมีเพียงแค่ 1 บรรทัด Oracle ก็จะเรียก Execution plan เดิมมาใช้นั่นคือการอ่านข้อมูลทั้งตารางหรือ Full scan table แทนที่จะเลือกใช้ Index scan และนี่เป็นผลกระทบจากการที่เราใช้ Bind variable ทำการคิวรี่ข้อมูลบนตารางที่มีการเก็บ Histogram ของข้อมูลบนตารางครับ

วิธีหลีกเลี่ยงปัญหาบนฐานข้อมูลที่มีการใช้ Bind variables บนตารางที่มีการเก็บ Histogram statistics
1. ปิดการใช้ Bind variable peeking โดยกำหนดค่า hidden parameter ที่ชื่อ _optim_peek_user_binds จาก TRUE (default) เป็น FALSE
2. ลบ Histogram statistics เดิมบนตารางนั้นๆ แล้วทำการ Gather stats ด้วย METHOD_OPT=’for all columns size 1′

ขอบคุณครับ
Author: MrNobita Namsutto

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