SKEWONLY vs SIZE AUTO อะไรควรใช้ตอนไหน

เมื่อบทความที่แล้วผมได้พูดถึงตารางที่มีการเก็บ Histogram statistic และผลกระทบจากการใช้ Bind variable บนตารางนั้นๆ ไปแล้ว ถ้าใครมีคำถามสงสัยก็ลองถามเข้ามาได้ครับ แต่ในวันนี้ผมจะเล่าถึง Option นึงเกี่ยวกับการ Gather statistic ที่ส่วนใหญ่มักจะมองข้ามความสำคัญไป นั่นก็คือ METHOD_OPT ครับ โดยส่วนใหญ่มักจะถูกใช้เป็นค่า Default (เฮ้ยๆ ช่างมันเถอะ gather stats แล้วก็โอเคแล้วมั่ง) ซึ่ง Default ที่ผมพูดถึงนั่นก็คือ “FOR ALL COLUMNS SIZE AUTO” ครับ (ในบทความนี้ผมขอเรียกสั้นๆ ว่า AUTO นะครับ และ “FOR ALL COLUMNS SIZE SKEWONLY” ซึ่งเป็นพระเอกของเราในวันนี้ ผมขอเรียกสั้นๆ ว่า SKEWONLY ครับ)

ถ้าจำได้เมื่อบทความที่แล้วผมได้อ้างอิงถึงบทความหนึ่งจาก Blog ของท่านอาจารย์ของผม

นี่คือข้อเท็จจริงที่ทำให้ Query Plan อาจจะเปลี่ยนเมื่อเวลาผ่านไป ซึ่งมีผลมาจากวิธีการรวบรวมสถิติของ Oracle ที่เปลี่ยนแปลงไป เมื่อเรากำหนดค่าของพารามิเตอร์ METHOD_OPT ใน DBMS_STATS ให้เป็น AUTO (AUTO ปกติเป็นค่าดีฟอลต์)

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

โดยการทำงานของ “FOR ALL COLUMNS SIZE AUTO” คือ ตารางใดๆ นั้นจะไม่ถูกเก็บ Histogram บนคอลัมน์โดยทันทีจนกว่าจะมีการถูกเรียกใช้งานและถูกประมวลผลบนคิวรี่ นั่นจึงเป็นสาเหตุที่ทำให้เมื่อเราทำการใช้งานคิวรี่บนตารางใดๆ ไประยะนึง Execution plan จึงเปลี่ยนไปใช้ Plan ที่ดีขึ้นครับ

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

SQL> create table t
  2  as
  3  select mod(rownum,5) id, a.*
  4  from all_objects a;

Table created.

SQL> BEGIN
  2  DBMS_STATS.GATHER_TABLE_STATS(
  3       OWNNAME => 'SYS',
  4       TABNAME => 'T',
  5       METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO',
  6       CASCADE => TRUE);
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL> select table_name, column_name, data_type, num_distinct,
  2  sample_size,
  3  num_buckets buckets
  4  from dba_tab_columns
  5  where table_name = 'T'
  6  order by table_name, column_id;

TABLE_NAME COLUMN_NAME    DATA_TYPE NUM_DISTINCT SAMPLE_SIZE BUCKETS
---------- -------------- --------- ------------ ----------- -------
T          ID             NUMBER    6            10621       1
T          OWNER          VARCHAR2  11           10621       1
T          OBJECT_NAME    VARCHAR2  7939         10621       1
T          SUBOBJECT_NAME VARCHAR2  182          449         1
T          OBJECT_ID      NUMBER    10621        10621       1
T          DATA_OBJECT_ID NUMBER    2368         2404        1
T          OBJECT_TYPE    VARCHAR2  34           10621       1
T          CREATED        DATE      809          10621       1
T          LAST_DDL_TIME  DATE      822          10621       1
T          TIMESTAMP      VARCHAR2  853          10621       1
T          STATUS         VARCHAR2  2            10621       1
T          TEMPORARY      VARCHAR2  2            10621       1
T          GENERATED      VARCHAR2  2            10621       1
T          SECONDARY      VARCHAR2  1            10621       1

14 rows selected.

จากตัวอย่างด้านบนจะเห็นว่า เมื่อเราทำการสร้างตารางขึ้นมาแล้ว Gather stats ครั้งแรกด้วย AUTO นั้น Oracle จะยังไม่สามารถรับรู้ความเบ้ของข้อมูลได้ จนกว่าจะถูกประมวลผลบนคิวรี่ครับ ขั้นต่อไปผมจะลองคิวรี่ข้อมูลบนตารางแล้วจะทำการ Gather stats อีกรอบครับ

SQL> select count(*) from T where ID = 99;

COUNT(*)
----------
1

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

COUNT(*)
----------
432

SQL> select count(*) from T where object_type = 'TABLE';

COUNT(*)
----------
981

SQL> BEGIN
  2  DBMS_STATS.GATHER_TABLE_STATS(
  3       OWNNAME => 'SYS',
  4       TABNAME => 'T',
  5       METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO',
  6       CASCADE => TRUE);
  7  END;
  8  /

PL/SQL procedure successfully completed.

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

SQL> select table_name, column_name, data_type, num_distinct,
  2  sample_size,
  3  num_buckets buckets
  4  from dba_tab_columns
  5  where table_name = 'T'
  6  order by table_name, column_id;

TABLE_NAME COLUMN_NAME    DATA_TYPE NUM_DISTINCT SAMPLE_SIZE BUCKETS
---------- -------------- --------- ------------ ----------- -------
T          ID             NUMBER    6            10621       6
T          OWNER          VARCHAR2  11           10621       11
T          OBJECT_NAME    VARCHAR2  7939         10621       1
T          SUBOBJECT_NAME VARCHAR2  182          449         1
T          OBJECT_ID      NUMBER    10621        10621       1
T          DATA_OBJECT_ID NUMBER    2368         2404        1
T          OBJECT_TYPE    VARCHAR2  34           10621       34
T          CREATED        DATE      809          10621       1
T          LAST_DDL_TIME  DATE      822          10621       1
T          TIMESTAMP      VARCHAR2  853          10621       1
T          STATUS         VARCHAR2  2            10621       1
T          TEMPORARY      VARCHAR2  2            10621       1
T          GENERATED      VARCHAR2  2            10621       1
T          SECONDARY      VARCHAR2  1            10621       1

14 rows selected.

เห็นการเปลี่ยนแปลงมั๊ยครับ?… อย่างที่ผมได้บอกไปก่อนหน้าครับว่าการกำหนดรวบรวมสถิติเป็น AUTO นั้น Oracle จะเก็บ Histogram ก็ต่อเมื่อมีการประมวลผลบนคอลัมน์นั้นๆ บนคิวรี่ ซึ่งข้อเสียของมันก็คือหลังจากเราสร้างตารางขึ้นมาแล้ว Gather stats แบบ AUTO แล้ว อาจจะทำให้การคิวรี่ข้อมูลบนตารางนั้นๆ ได้ Execution plan ที่ไม่ดีก็ได้ เนื่องจาก ในเบื้องต้น Oracle ไม่รู้ว่าความเบ้ของข้อมูลบนตารางนั้นๆ เป็นอย่างไร

แล้วจะมีวิธีไหนแก้ปัญหานี่ได้หรือไม่ครับ? คำตอบนั้นก็คือ SKEWONLY ครับ

การทำงานของ SKEWONLY นั้นจะเก็บ Histogram statistic บนตารางนั้นๆ โดยไม่จำเป็นต้องมีการประมวลผลคอลัมน์บนคิวรี่เหมือน AUTO โดยการรวบรวมสถิติของ SKEWONLY นั้นจะทำการตรวจสอบความเบ้ของข้อมูลทุกคอลัมน์ ลองดูตัวอย่างข้างล่างนี้ครับ

SQL> BEGIN
  2  DBMS_STATS.DELETE_TABLE_STATS(
  3       OWNNAME => 'SYS',
  4       TABNAME => 'T',
  5       CASCADE_INDEXES => TRUE);
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> select table_name, column_name, data_type, num_distinct,
  2  sample_size,
  3  num_buckets buckets
  4  from dba_tab_columns
  5  where table_name = 'T'
  6  order by table_name, column_id;

TABLE_NAME COLUMN_NAME    DATA_TYPE NUM_DISTINCT SAMPLE_SIZE BUCKETS
---------- -------------- --------- ------------ ----------- -------
T          ID             NUMBER
T          OWNER          VARCHAR2
T          OBJECT_NAME    VARCHAR2
T          SUBOBJECT_NAME VARCHAR2
T          OBJECT_ID      NUMBER
T          DATA_OBJECT_ID NUMBER
T          OBJECT_TYPE    VARCHAR2
T          CREATED        DATE
T          LAST_DDL_TIME  DATE
T          TIMESTAMP      VARCHAR2
T          STATUS         VARCHAR2
T          TEMPORARY      VARCHAR2
T          GENERATED      VARCHAR2
T          SECONDARY      VARCHAR2

14 rows selected.

SQL> BEGIN
  2  DBMS_STATS.GATHER_TABLE_STATS(
  3       OWNNAME => 'SYS',
  4       TABNAME => 'T',
  5       METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY',
  6       CASCADE => TRUE);
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL> select table_name, column_name, data_type, num_distinct,
  2  sample_size,
  3  num_buckets buckets
  4  from dba_tab_columns
  5  where table_name = 'T'
  6  order by table_name, column_id;

TABLE_NAME COLUMN_NAME    DATA_TYPE NUM_DISTINCT SAMPLE_SIZE BUCKETS
---------- -------------- --------- ------------ ----------- -------
T          ID             NUMBER    6            10621       6
T          OWNER          VARCHAR2  11           10621       11
T          OBJECT_NAME    VARCHAR2  7939         10621       254
T          SUBOBJECT_NAME VARCHAR2  182          449         182
T          OBJECT_ID      NUMBER    10621        10621       1
T          DATA_OBJECT_ID NUMBER    2368         2404        254
T          OBJECT_TYPE    VARCHAR2  34           10621       34
T          CREATED        DATE      809          10621       254
T          LAST_DDL_TIME  DATE      822          10621       254
T          TIMESTAMP      VARCHAR2  853          10621       254
T          STATUS         VARCHAR2  2            10621       2
T          TEMPORARY      VARCHAR2  2            10621       2
T          GENERATED      VARCHAR2  2            10621       2
T          SECONDARY      VARCHAR2  1            10621       1

14 rows selected.

สรุป
การ Gather stats ด้วย SIZE AUTO นั้น ความเสี่ยงที่เห็นได้ชัดที่สุดนั้นคือ การคิวรี่บนตารางที่ถูกสร้างขึ้นใหม่แล้วรวบรวมสถิติด้วย SIZE AUTO นั้น อาจทำให้ Oracle เลือกใช้ Execution plan ที่แย่ก็เป็นได้ ซึ่งดูจากผลลัพธ์นั้น SKEWONLY อาจจะได้ผลลัพธ์ที่ดีกว่าเมื่อเราจำเป็นต้องสร้างตารางขึ้นใหม่บนฐานข้อมูลแล้วทำให้ Oracle รับรู้ความเบ้ของข้อมูลตั้งแต่เริ่ม โดยเริ่มต้นเราอาจจะเลือกใช้ SKEWONLY ในครั้งแรกของการสร้างตาราง แล้วหลังจากนั้นจึงเปลี่ยนมาใช้ AUTO แทนในภายหลังในการรวบรวมสถิติได้ครับ แต่การจะใช้ SKEWONLY นั้นต้องพิจารณาด้วยนะครับว่า ฐานข้อมูลของคุณมีการใช้ Bind variables หรือไม่ อย่างที่ผมได้กล่าวถึงไปในบทความที่แล้ว เพราะไม่เช่นนั้นพระเอกสุดหล่อของเรา SKEWONLY อาจจะกลายเป็นผู้ร้ายขึ้นมาในทันทีครับ…

ศึกษาเพิ่มเติมได้ที่นี่ครับ: Using histograms with dbms_stats by Burleson

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

Google+ photo

You are commenting using your Google+ 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 /  เปลี่ยนแปลง )

w

Connecting to %s