Cardinality Feedback คิดผิดกันดีนัก เดียวพี่คิดเอง!!!

Cardinality Feedback

ช่วงนี้รัวๆ บทความให้เลยละกันครับ คันไม้คันมือมากกกกกก ก็วันนี้จัดให้อีกซักหนึ่งเรื่องแล้วกันนะครับเกี่ยวกับอีกหนึ่งความสามารถใน Oracle 11gR2 นั่นคือ Cardinality Feedback นั่นเองครับผม

Cardinality Feedback คืออะไร? ทำงานอย่างไร?

Cardinality Feedback ถูกนำมาใช้ครั้งแรกใน Oracle 11gR2 จุดประสงค์ก็เพื่อช่วยในเรื่องการ Generate execution plan ให้ดีขึ้นในคิวรี่ที่มีการรันบ่อยๆ และสำหรับคิวรี่ที่มีการคำนวนผลลัพธ์ที่ไม่ดี ซึ่งนั่นทำให้ Optimizer อาจจะคำนวนผิดพลาดได้ในหลายๆ สาเหตุ เช่น Statistics ที่ผิดพลาด, เส้นทางของคิวรี่ที่ซับซ้อนเกินความจำเป็น ไม่ว่าจะด้วยเหตุผลใดที่ทำให้เกิดการคำนวนผิดพลาด Cardinality Feedback นี่เองจะเข้ามาช่วยแก้ปัญหาตรงนี้

ระหว่างการ Execute SQL statement ครั้งแรกนั้น การ Generate execution plan จะเกิดขึ้นตามปกติ และในระหว่างที่ Optimizer ทำงาน การคำนวนผลลัพธ์ที่คุณภาพต่ำๆ (ในที่นี้หมายถึงตารางที่ไม่มี Statistics หรือตารางที่มีความซับซ้อน) จะเริ่มถูกเก็บข้อมูล
และในขณะที่คิวรี่กำลังจะทำงานเสร็จ (ผ่านกระบวนการของ Optimizer มาแล้ว) Cardinality Feedback จะเริ่มทำการเปรียบเทียบผลลัพธ์ที่ประมาณการกับผลลัพธ์ที่ได้จริงในขณะที่ Execute ถ้ามันตรวจสอบแล้วพบว่าผลลัพธ์แตกต่างกันอย่างมีนัยสำคัญจากผลลัพธ์จริงมันก็จะทำการเก็บผลลัพธ์ที่ประมาณการได้ถูกต้องไว้ใช้ในครั้งต่อไป และเมื่อคิวรี่เดิมทำการ Execute อีกครั้ง มันก็จะผ่านการทำงาน Optimizer อีกครั้งแต่ครั้งนี้มันจะผลลัพธ์ที่ถูกต้องในการคำนวนหาเส้นทางที่ดีสุดออกมาได้

ยกตัวอย่างง่ายๆ ต่อไปนี้ครับ

SQL> create table test_seg as select * from dba_segments;

Table created.

SQL> create table test_obj as select * from dba_tables;

Table created.

เริ่มด้วยผมสร้างตารางใหม่ขึ้นมา 2 ตัวตามตัวอย่างด้านบนครับ จากนั้นผมจะทำการคิวรี่ข้อมูลดังข้างล่างนี้ครับ

SQL> select /*+ GATHER_PLAN_STATISTICS */ t.owner,t.table_name,t.blocks from
  2  test_obj t, test_seg s where t.table_name  = s.segment_name and
  3  t.blocks > 1000 and s.bytes > 10240000;

OWNER                          TABLE_NAME                         BLOCKS
------------------------------ ------------------------------ ----------
TEST_USER                      T                                    1340
SYS                            IDL_UB2$                             1994
SYS                            IDL_UB1$                             4371
SYS                            SOURCE$                              2546
SYS                            WRM$_SNAPSHOT_DETAILS                2512
SYS                            WRH$_SYSMETRIC_HISTORY               1882
SYS                            WRI$_OPTSTAT_HISTGRM_HISTORY         3520
SOX_RPT                        SOX_CLIENT_ACCESS                   15197
SOX_RPT                        SOX_ALL_ACCESS                      15197

9 rows selected.

SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8dp9acd1d80x3, child number 1
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ t.owner,t.table_name,t.blocks from
test_obj t, test_seg s where t.table_name  = s.segment_name and
t.blocks > 1000 and s.bytes > 10240000

Plan hash value: 1391165377

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |      1 |        |      9 |00:00:00.01 |     106 |       |       |          |
|*  1 |  HASH JOIN         |          |      1 |    169 |      9 |00:00:00.01 |     106 |   990K|   990K| 1018K (0)|
|*  2 |   TABLE ACCESS FULL| TEST_OBJ |      1 |    167 |     17 |00:00:00.01 |      44 |       |       |          |
|*  3 |   TABLE ACCESS FULL| TEST_SEG |      1 |    237 |     26 |00:00:00.01 |      62 |       |       |          |
--------------------------------------------------------------------------------------------------------------------

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

   1 - access("T"."TABLE_NAME"="S"."SEGMENT_NAME")
   2 - filter("T"."BLOCKS">1000)
   3 - filter("S"."BYTES">10240000)

24 rows selected.

จากผลลัพธ์ข้างบนจะเห็นว่าค่าที่ Optimizer ทำการ Estimate ออกมานั้น (E-Rows) ค่อนข้างแตกต่างมากทีเดียวกับค่าที่ได้จริง (A-Rows) แต่จากที่เราเข้าใจกันนั่นคือ Cardinality Feedback น่าจะเก็บค่า Actual rows ไว้แล้ว สิ่งที่เราต้องการก็คือลอง Execute อีกหนึ่งรอบครับ

SQL> select /*+ GATHER_PLAN_STATISTICS */ t.owner,t.table_name,t.blocks from
  2  test_obj t, test_seg s where t.table_name  = s.segment_name and
  3  t.blocks > 1000 and s.bytes > 10240000;

OWNER                          TABLE_NAME                         BLOCKS
------------------------------ ------------------------------ ----------
TEST_USER                      T                                    1340
SYS                            IDL_UB2$                             1994
SYS                            IDL_UB1$                             4371
SYS                            SOURCE$                              2546
SYS                            WRM$_SNAPSHOT_DETAILS                2512
SYS                            WRH$_SYSMETRIC_HISTORY               1882
SYS                            WRI$_OPTSTAT_HISTGRM_HISTORY         3520
SOX_RPT                        SOX_CLIENT_ACCESS                   15197
SOX_RPT                        SOX_ALL_ACCESS                      15197

9 rows selected.

SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8dp9acd1d80x3, child number 2
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ t.owner,t.table_name,t.blocks from
test_obj t, test_seg s where t.table_name  = s.segment_name and
t.blocks > 1000 and s.bytes > 10240000

Plan hash value: 1391165377

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |      1 |        |      9 |00:00:00.01 |     106 |       |       |          |
|*  1 |  HASH JOIN         |          |      1 |     17 |      9 |00:00:00.01 |     106 |   990K|   990K|  999K (0)|
|*  2 |   TABLE ACCESS FULL| TEST_OBJ |      1 |     17 |     17 |00:00:00.01 |      44 |       |       |          |
|*  3 |   TABLE ACCESS FULL| TEST_SEG |      1 |     26 |     26 |00:00:00.01 |      62 |       |       |          |
--------------------------------------------------------------------------------------------------------------------

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

   1 - access("T"."TABLE_NAME"="S"."SEGMENT_NAME")
   2 - filter("T"."BLOCKS">1000)
   3 - filter("S"."BYTES">10240000)

Note
-----
   - cardinality feedback used for this statement

28 rows selected.

โอ้วเห็นผลทันตาเลยครับ ค่า E-Rows และ A-Rows เท่ากันเป๊ะ สุดยอดจริงๆ!

บทสรุป
Cardinality Feedback จะถูกใช้งานก็ต่อเมื่อเกิดปัจจัยต่างๆ ขึ้น เช่น ตารางที่ไม่มี Statistics หรือการที่มีความซับซ้อนของตารางหรือคิวรี่มากๆ จนทำให้ Optimizer ไม่สามารถคำนวนค่าที่ใกล้เคียงได้ (Complex predicated) ในบางเคสก็ยังมีฟีเจอร์อื่นๆ เข้ามาเพื่อช่วยในการคำนวนของ Optimizer เช่น Dynamic Sampling หรือ Multi-column statistics ที่เข้ามาช่วยทำให้งานของ Optimizer ง่ายขึ้น

Cardinality Feedback จะมีประโยชน์มากๆ สำหรับคิวรี่ที่ไม่มีการเปลี่ยนแปลงของจำนวนข้อมูลคงที่หรือเปลี่ยนน้อยมากเป็นระยะเวลานานๆ แต่ก็ยังมีข้อจำกัดเกิดขึ้นอีกมากมายสำหรับฟีเจอร์ตัวนี้ เนื่องจากมันจะไม่มีผลกับคิวรี่ที่มีการใช้งาน Bind variable ใน WHERE Clause แต่มันก็ยังถูกใช้งานในส่วนอื่นได้เหมือนเดิม ตราบใดที่คิวรี่ในส่วนนั้นไม่ได้ถูกใช้งานด้วย Bind variable

แต่ถ้าใครไม่อยากใช้งานฟีเจอร์ตรงนี้ของ Oracle เราก็สามารถปิดมันได้นะครับด้วยการเปลี่ยน Parameter ที่ชื่อ _optimizer_use_feedback ให้เป็น FALSE นั่นเองครับ

SQL> ALTER SYSTEM SET "_optimizer_use_feedback" = FALSE SCOPE BOTH;
SQL> ALTER SESSION SET "_optimizer_use_feedback" = FALSE;

ก็เลือกใช้ที่เหมาะสมกับฐานข้อมูลของคุณได้เลยนะครับ สำหรับวันนี้พอแค่นี้ละกันครับ Enjoy your DBA life นะครับ สวัสดี

Credit: Oracle Optimizer

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