Extended Statistics กับความสามารถที่เพิ่มขึ้นของ Optimizer

mac-glasses

สวัสดีครับวันนี้ผมจะพูดถึงเกี่ยวกับ Oracle Optimizer และความสามารถใหม่ของมัน ซึ่งทำให้การคำนวนเส้นทางของ Execution plan ดีขึ้นมากมาย และความสามารถในการคำนวนที่ถูกเพิ่มขึ้นมานั้น ทำให้เมื่อใดก็ตามที่ Optimizer คิดคำนวนเส้นทางของ SQL statement ใดๆ แล้ว ก็มักจะได้ Execution plan ที่ดีที่สุด สิ่งที่ผมจะกล่าวถึงวันนี้คือ Extended Statistics นั่นเองครับ

Extended Statistics ใน Oracle 11g

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

SQL> select owner,table_name,column_name,num_distinct 
 2 > from dba_tab_columns 
 3 > where table_name = 'T' and owner = 'TEST_USER';

OWNER      TABLE_NAME COLUMN_NAME                    NUM_DISTINCT
---------- ---------- ------------------------------ ------------
TEST_USER  T          ID                                   100000
TEST_USER  T          OWNER                                    12
TEST_USER  T          OBJECT_NAME                           10199
TEST_USER  T          SUBOBJECT_NAME                          254
TEST_USER  T          OBJECT_ID                             13656
TEST_USER  T          DATA_OBJECT_ID                         2776
TEST_USER  T          OBJECT_TYPE                              33
TEST_USER  T          CREATED                                1083
TEST_USER  T          LAST_DDL_TIME                          1662
TEST_USER  T          TIMESTAMP                              1344
TEST_USER  T          STATUS                                    2
TEST_USER  T          TEMPORARY                                 2
TEST_USER  T          GENERATED                                 2
TEST_USER  T          SECONDARY                                 1

14 rows selected.

ซึ่งใช้โค้ดด้านล่างนี้ Generate ขึ้นมาครับ

SQL> conn TEST_USER/TEST_USER
Connected.
SQL> create table T
  2  as
  3  select rownum id,
  4                 OWNER, OBJECT_NAME, SUBOBJECT_NAME,
  5                 OBJECT_ID, DATA_OBJECT_ID,
  6                 OBJECT_TYPE, CREATED, LAST_DDL_TIME,
  7                 TIMESTAMP, STATUS, TEMPORARY,
  8                 GENERATED, SECONDARY
  9    from all_objects a
 10  where 1=0
 11  /

Table created.

SQL> alter table T nologging;

Table altered.

SQL> declare
  2      l_cnt number;
  3      l_rows number := & 1;
  4  begin
  5      insert /*+ append */
  6      into T
  7      select rownum,
  8                 OWNER, OBJECT_NAME, SUBOBJECT_NAME,
  9                 OBJECT_ID, DATA_OBJECT_ID,
 10                 OBJECT_TYPE, CREATED, LAST_DDL_TIME,
 11                 TIMESTAMP, STATUS, TEMPORARY,
 12                 GENERATED, SECONDARY
 13        from all_objects a
 14       where rownum <= & 1;
 15
 16      l_cnt := sql% rowcount;
 17
 18      commit;
 19
 20      while (l_cnt < l_rows)
 21      loop
 22          insert /*+ APPEND */ into T
 23          select rownum+l_cnt,
 24                 OWNER, OBJECT_NAME, SUBOBJECT_NAME,
 25                 OBJECT_ID, DATA_OBJECT_ID,
 26                 OBJECT_TYPE, CREATED, LAST_DDL_TIME,
 27                 TIMESTAMP, STATUS, TEMPORARY,
 28                 GENERATED, SECONDARY
 29            from T
 30           where rownum <= l_rows-l_cnt;
 31          l_cnt := l_cnt + sql %rowcount;
 32          commit;
 33      end loop;
 34  end;
 35  /
Enter value for 1: 100000
old   3:     l_rows number := & 1;
new   3:     l_rows number := 100000;
Enter value for 1: 100000
old  14:      where rownum <= & 1;
new  14:      where rownum <= 100000; 

PL/SQL procedure successfully completed. 

SQL> alter table T add constraint
  2  PK_T primary key(id)
  3  /

Table altered.

สิ่งที่ผมจะอธิบายก็คือ OBJECT_TYPE บนตาราง T นั้นมีอยู่มากมายหลายค่าแต่สิ่งที่ผมต้องการก็คือเฉพาะ INDEX ที่ OWNER เป็น SYSTEM แต่ว่าตัว Optimizer เองไม่มีทางรู้ได้เลยเกี่ยวกับความสัมพันธ์ระหว่างสองคอลัมน์นี้นั่นอาจจะทำให้มีโอกาสที่ Optimizer จะคำนวนจำนวนบรรทัดของผลลัพธ์ผิดพลาดและเลือก Execution plan ที่แย่ก็เป็นได้ ดังนั้นถ้าเกิดมีการใช้งานหลายๆ คอลัมน์ใน WHERE clause ของคิวรี่ใดๆ และด้วยความสามารถของ Extended Statistics นี่คือโอกาสที่คุณจะได้บอกกับ Optimizer ถึงความสัมพันธ์ระหว่างคอลัมน์ทั้งสองนี้แล้วครับ

ด้วยความสามารถของ Extended Statistics ที่ถูกสร้างขึ้นบนกลุ่มของคอลัมน์นั้น มีผลทำให้การคำนวนจำนวนบรรทัดของผลลัพธ์มีความแม่นยำมากขึ้นเมื่อมีการใช้งานกลุ่มของคอลัมน์นั้นๆ บน WHERE clause ในคิวรี่ คุณจะต้องใช้คำสั่ง DBMS_STATS.CREATE_EXTENDED_STATS เพื่อเลือกสร้างกลุ่มคอลัมน์ที่คุณต้องการจะเก็บ Statistics และเมื่อมันถูกสร้างขึ้นมาแล้ว Oracle จะทำการเก็บรักษา Statistics ของกลุ่มคอลัมน์นั้นๆ เมื่อใดก็ตามที่มีการ Gather_stats บนตารางนั้นๆ เกิดขึ้น

และถ้าผมจะเริ่มต้นด้วยข้อมูลเริ่มต้นของตาราง T เมื่อค่าของ OWNER คือ SYSTEM และค่าของ OBJECT_TYPE คือ INDEX ด้วยข้อมูลบนสองคอลัมน์นี้มีความเบ้ของข้อมูล (Skew data) อยู่ และด้วยความสัมพันธ์และความเบ้ของข้อมูลชุดนี้มันเป็นการยากมากสำหรับ Optimizer ที่จะคำนวนจำนวนบรรทัดทีถูกต้องแม่นยำออกมาได้

SQL> select count(*) from T where owner = 'SYSTEM' and object_type = 'INDEX'
  2  ;

  COUNT(*)
----------
      1514

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID  gdfhu930s2bp5, child number 0
-------------------------------------
select count(*) from T where owner = 'SYSTEM' and object_type = 'INDEX'

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   263 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    28 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |    11 |   308 |   263   (2)| 00:00:04 |
---------------------------------------------------------------------------

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

   2 - filter(("OWNER"='SYSTEM' AND "OBJECT_TYPE"='INDEX'))

19 rows selected.

ด้วย Statistics เบื้องต้น เราจะเห็นว่า Optimizer คิดว่ามีเพียง 11 บรรทัดเท่านั้นที่เป็นผลลัพธ์ ซึ่งเป็นผลลัพธ์ที่ผิดและแตกต่างอย่างมากจากผลลัพธ์ที่แท้จริง (1514 บรรทัด) ดังนั้นเราจำเป็นต้องทำให้ Optimizer ได้รับรู้ Statistics ที่ดีกว่านี้ ซึ่งก่อนที่จะถึง Oracle 11g ก็มี Option นึงใน DBMS_STATS เพื่อทำให้ Optimizer รับรู้การเบ้ของข้อมูล ซึ่งเราสามารถทำอย่างนั้นได้ด้วยวิธีการ Gather statistics บนคอลัมน์ที่มีการเบ้ของข้อมูลครับ

SQL> exec dbms_stats.gather_table_stats(null,'T',method_opt=>'for all columns size skewonly');

PL/SQL procedure successfully completed.

SQL> select column_name,histogram from user_tab_col_statistics where table_name = 'T';

COLUMN_NAME                    HISTOGRAM
------------------------------ ---------------
ID                             NONE
OWNER                          FREQUENCY
OBJECT_NAME                    HEIGHT BALANCED
SUBOBJECT_NAME                 FREQUENCY
OBJECT_ID                      HEIGHT BALANCED
DATA_OBJECT_ID                 HEIGHT BALANCED
OBJECT_TYPE                    FREQUENCY
CREATED                        HEIGHT BALANCED
LAST_DDL_TIME                  HEIGHT BALANCED
TIMESTAMP                      HEIGHT BALANCED
STATUS                         FREQUENCY
TEMPORARY                      FREQUENCY
GENERATED                      FREQUENCY
SECONDARY                      FREQUENCY

14 rows selected.

และตอนนี้เราก็มี Histogram บนคอลัมน์ OWNER และ OBJECT_TYPE แล้วครับ ที่นี้เรามาดูกันว่า Optimizer จะคำนวนออกมาได้ใกล้เคียงกว่าเดิมหรือไม่ (ผลลัพธ์จริง 1514 บรรทัด)

SQL> select count(*) from T where owner = 'SYSTEM' and object_type = 'INDEX';

  COUNT(*)
----------
      1514

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID  4p89xgvz5pvyq, child number 0
-------------------------------------
select count(*) from T where owner = 'SYSTEM' and object_type = 'INDEX'

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   263 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |   315 |  4095 |   263   (2)| 00:00:04 |
---------------------------------------------------------------------------

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

   2 - filter(("OWNER"='SYSTEM' AND "OBJECT_TYPE"='INDEX'))

19 rows selected.

จากผลลัพธ์ข้างบนดูเหมือนการคำนวนจำนวนบรรทัดของผลลัพธ์ดีขึ้นเล็กน้อยตั้งแต่เรามีค่า Histogram ในแต่ละคอลัมน์ อย่างไรก็ตาม Optimizer ก็ยังคงไม่รู้อยู่ดีเกี่ยวกับความสัมพันธ์ของสองคอลัมน์นี้ (OWNER,OBJECT_TYPE) แต่ใน Oracle 11g บัดนี้เราสามารถบอก Optimizer ได้แล้วเกี่ยวกับความสัมพันธ์ของสองคอลัมน์นี้ด้วยการสร้าง Extended Statistics ขึ้นมาบนสองคอลัมน์นี้ให้เป็นกลุ่มคอลัมน์เดียวกัน และหลังจากที่สร้าง Extended Statistics ขึ้นมาแล้ว ครั้งต่อไปที่มีการ Gather stats บนตาราง T มันจะทำการเก็บ Statistics เพิ่มเติมสำหรับสองคอลัมน์นี้ ซึ่งเราสามารถใช้คำสั่งต่อไปนี้ในการสร้าง Extended Statistics ขึ้นมาครับ

SQL> select dbms_stats.create_extended_stats(null,'T','(OWNER,OBJECT_TYPE)')
  2  from dual;

DBMS_STATS.CREATE_EXTENDED_STATS(NULL,'T','(OWNER,OBJECT_TYPE)')
----------------------------------------------------------------
SYS_STUXJ8K0YTS_5QD1O0PEA514IY

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

PL/SQL procedure successfully completed.

และหลังจากที่เราได้สร้าง Extended Statistics ขึ้นแล้ว จะเห็นได้ว่าระบบได้ทำการสร้างคอลัมน์ใหม่ขึ้นมาในตาราง USER_TAB_COL_STATISTICS ซึ่งหมายถึงกลุ่มของคอลัมน์ OWNER และ OBJECT_TYPE นั่นเองครับ

SQL> select column_name,histogram from user_tab_col_statistics where table_name = 'T';

COLUMN_NAME                    HISTOGRAM
------------------------------ ---------------
ID                             NONE
OWNER                          FREQUENCY
OBJECT_NAME                    NONE
SUBOBJECT_NAME                 NONE
OBJECT_ID                      NONE
DATA_OBJECT_ID                 NONE
OBJECT_TYPE                    FREQUENCY
CREATED                        NONE
LAST_DDL_TIME                  NONE
TIMESTAMP                      NONE
STATUS                         NONE
TEMPORARY                      NONE
GENERATED                      NONE
SECONDARY                      NONE
SYS_STUXJ8K0YTS_5QD1O0PEA514IY NONE

15 rows selected.

ในที่สุดเราก็มี Statistics บนกลุ่มคอลัมน์ที่สร้างขึ้นมาแล้ว เรามาดูกันครับว่า Optimizer จะได้รับข้อมูลเพียงพอต่อการคำนวนแล้วหรือยัง

SQL>  select count(*) from T where owner = 'SYSTEM' and object_type = 'INDEX';

  COUNT(*)
----------
      1514

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID  91ysj4jsmr1qr, child number 0
-------------------------------------
 select count(*) from T where owner = 'SYSTEM' and object_type = 'INDEX'

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   263 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |   355 |  4615 |   263   (2)| 00:00:04 |
---------------------------------------------------------------------------

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

   2 - filter(("OWNER"='SYSTEM' AND "OBJECT_TYPE"='INDEX'))

19 rows selected.

อ้าวทำไมยังไม่ตรงหล่ะ? อ๋อออออ นั่นก็เพราะกลุ่มคอลัมน์ที่ถูกสร้างขึ้นมาใหม่มันยังไม่มี Histogram นั่นเองครับ ถ้าเราลองคิวรี่ USER_TAB_COL_STATISTICS ดู ตรงกลุ่มคอลัมน์ที่ระบบสร้างขึ้น ส่วนที่เป็น Histogram ยังเป็นค่า NONE อยู่นั่นเอง

และหลังจากที่เราคิวรี่ไปครั้งล่าสุด Oracle ก็ได้ทำการเก็บข้อมูลที่มีประโยชน์สำหรับ Extended Statistics ไว้เรียบร้อยแล้ว สิ่งที่ต้องการสิ่งสุดท้ายตอนนี้ก็คือ Gather stats อีกรอบนึงเป็นอันใช้ได้ครับ

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

PL/SQL procedure successfully completed.

SQL> select column_name,histogram from user_tab_col_statistics where table_name = 'T';

COLUMN_NAME                    HISTOGRAM
------------------------------ ---------------
ID                             NONE
OWNER                          FREQUENCY
OBJECT_NAME                    NONE
SUBOBJECT_NAME                 NONE
OBJECT_ID                      NONE
DATA_OBJECT_ID                 NONE
OBJECT_TYPE                    FREQUENCY
CREATED                        NONE
LAST_DDL_TIME                  NONE
TIMESTAMP                      NONE
STATUS                         NONE
TEMPORARY                      NONE
GENERATED                      NONE
SECONDARY                      NONE
SYS_STUXJ8K0YTS_5QD1O0PEA514IY FREQUENCY

15 rows selected.

SQL> select count(*) from T where owner = 'SYSTEM' and object_type = 'INDEX';

  COUNT(*)
----------
      1514

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID  4p89xgvz5pvyq, child number 0
-------------------------------------
select count(*) from T where owner = 'SYSTEM' and object_type = 'INDEX'

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   263 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |  1509 | 19617 |   263   (2)| 00:00:04 |
---------------------------------------------------------------------------

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

   2 - filter(("OWNER"='SYSTEM' AND "OBJECT_TYPE"='INDEX'))

19 rows selected.

สุดยอดไปเลย!!! Execution plan แสดงจำนวนบรรทัดที่คำนวนได้ 1509 จากผลลัพธ์จริง 1514 ใกล้เคียงกันมาก!!!

และนี่ก็เป็นเกร็ดเล็กๆ น้อยๆ ที่นำมาฝากกันครับ วันนี้ผมขอตัวก่อนครับ 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