GATHER_PLAN_STATISTICS hint เครื่องมือช่วยเหลือสำหรับนัก Tuning SQL

GATHER_PLAN_STATISTICS

มันเป็นสิ่งที่เราต้องให้ความสนใจเป็นอย่างมากหลังจากที่เราทำการ Tuning SQL เพื่อเป็นการยืนยันผลลัพธ์ว่า Optimizer ทำงานได้อย่างถูกต้องหรือไม่? แต่เราจะรู้ได้ยังไงหล่ะ?

เราจะรู้ได้อย่างไรว่า Cardinality Estimates บน Execution plan นั้นแม่นยำเพียงพอหรือไม่?

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

SQL> select 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());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1nkq8zvfaghxt, child number 0
-------------------------------------
select 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     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |    24 (100)|          |
|*  1 |  HASH JOIN         |          |  1134 | 58968 |    24   (5)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| TEST_OBJ |   979 | 25454 |    10   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| TEST_SEG |  2961 | 76986 |    13   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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.

จาก Execution plan จะเห็นได้ว่าการคำนวนค่าผลลัพธ์ของ Optimizer ที่ปรากฏในคอลัมน์ Rows นั้นในแต่ละบรรทัดหมายถึงจำนวนบรรทัดที่ถูกคำนวนออกมาโดย Optimizer ของแต่ละลำดับการทำงานนั้นๆ จากวิธีการเดิมๆ ในการตรวจสอบว่าผลลัพธ์ที่ได้จะตรงกับ Execution plan หรือไม่นั้น เราจะใช้วิธีการ select count(*) จากแต่ละลำดับการทำงานใน Execution plan เพื่อให้แน่ใจว่าเราจะได้ผลลัพธ์ที่ถูกต้อง ในที่นี้ผมจะลองทำการทดลองคิวรี่เพื่อดูผลลัพธ์ของบรรทัดที่ 2 ใน Execution plan ดูครับ

SQL> select count(*) from TEST_OBJ where blocks > 1000;

  COUNT(*)
----------
        17

วิธีการข้างบนนี่จะเป็นการยากมากๆ ถ้าข้อมูลที่เราต้องคิวรี่อยู่นั้นมีจำนวนผลลัพธ์เยอะๆ แต่ชีวิตเราจะง่ายมากขึ้นเยอะใน Oracle 10g กับ GATHER_PLAN_STATISTICS hint เจ้า Hint ตัวนี้จะทำการสั่ง Oracle ให้ทำการเก็บ Statistics ของคิวรี่นั้นๆ และมันจะโชว์ค่าที่ได้อยู่ด้านข้าง Estimation เดิมใน Execution plan ถ้าคุณใช้ DBMS_XPLAN.DISPLAY_CURSOR ในการแสดงผล Execution plan ซึ่ง Estimation ปกติจะอยู่ในคอลัมน์ E-rows ในขณะที่ Statistics จริงจะอยู่ในคอลัมน์ A-rows ซึ่งคุณจำเป็นต้องใช้ FORMAT ดังนี้


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 0
-------------------------------------
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 |     104 |       |       |          |
|*  1 |  HASH JOIN         |          |      1 |   1134 |      9 |00:00:00.01 |     104 |   990K|   990K| 1005K (0)|
|*  2 |   TABLE ACCESS FULL| TEST_OBJ |      1 |    979 |     17 |00:00:00.01 |      43 |       |       |          |
|*  3 |   TABLE ACCESS FULL| TEST_SEG |      1 |   2961 |     26 |00:00:00.01 |      61 |       |       |          |
--------------------------------------------------------------------------------------------------------------------

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 ดูค่อนข้างต่างกันมากๆ ระหว่างค่าจริงและค่า Estimation ดังนั้นเราจำเป็นต้องหาสาเหตุและปรับแต่งคิวรี่กันต่อไปนะครับ

สำหรับวันนี้ก็เอามาฝากกันแค่นี้ละกันนะครับ 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