Fragment และผลกระทบต่อ Full table scan

ผมว่าหลายๆ คนเคยเจอเหตุการณ์ประมาณว่า batch ที่ทำงานในตอนกลางคืนช้า โดยสิ่งที่พยายามแก้ไขไปนั่นคือการทำการลบข้อมูลเก่าๆ ทิ้งไปเพื่อทำให้ข้อมูลน้อยลง โดยหลักความคิดแล้วน่าจะช่วยทำให้เร็วขึ้นได้จริง… แต่มันไม่เป็นอย่างนั้นหนะซิ เพราะเมื่อเราทำการลบข้อมูลจากตารางไปแล้วแม้จะเยอะแค่ไหนก็ตาม แต่ในตัว Physical level ของฐานข้อมูลเองยังมองว่าพื้นที่ว่างที่เคยเก็บข้อมูลของตารางนั้นๆ ยังอยู่ เราเรียกพื้นที่ว่างเหล่านั้นว่า Fragment นั่นเองครับ

ลองมาดูตัวอย่างแรกนี้กันครับ ผมจะทำการสร้างตารางชื่อ T ขึ้นมาทำการเก็บข้อมูลจำนวน 1,000,000 บรรทัดแล้วเรามาดูจำนวน blocks ที่มันใช้กันก่อนครับ (อย่าลืม Gather stats หลังจากสร้างตารางนะครับ)

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: 1000000
old   3:     l_rows number := & 1;
new   3:     l_rows number := 1000000;
Enter value for 1: 1000000
old  14:      where rownum <= & 1;
new  14:      where rownum <= 1000000;

PL/SQL procedure successfully completed.

SQL> BEGIN
  2     DBMS_STATS.GATHER_TABLE_STATS
  3     ( OWNNAME    => USER,
  4       TABNAME    => 'T' ,
  5       CASCADE    => TRUE );
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> select blocks from user_tables where table_name = 'T';

    BLOCKS
----------
     13477

จากผลลัพธ์ด้านบนเราจะเห็นว่าจำนวน block ทั้งหมดบนตาราง T นั่นคือ 13,477 blocks ต่อไปผมจะลองทำการ select * from T เพื่อดูผลลัพธ์นะครับ

SQL> set autotrace traceonly stat
SQL> select * from T;

1000000 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      79111  consistent gets
      13325  physical reads
        752  redo size
   57821308  bytes sent via SQL*Net to client
     733814  bytes received via SQL*Net from client
      66668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

จากผลลัพธ์จะเห็นว่า Physical reads ได้ผลใกล้เคียงกับจำนวน blocks ที่เรา SELECT มาซึ่งนั้นหมายถึงการทำ Full table scan นั่นเอง

แล้วไอเจ้า Fragment มันมีผลต่อ Full table scan ยังไงอ่ะ???

ต่อไปผมจะทำการลบข้อมูลออกจากตาราง T ครึ่งหนึ่งครับ แล้วมาดูกันว่าจำนวน blocks ของตาราง T ยังเท่าเดิมอยู่ไหม

  
SQL> delete from T where rownum <= 500000;

500000 rows deleted.

SQL> commit;

Commit complete.

SQL> BEGIN
  2     DBMS_STATS.GATHER_TABLE_STATS
  3     ( OWNNAME    => USER,
  4       TABNAME    => 'T' ,
  5       CASCADE    => TRUE );
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> select blocks from user_tables where table_name = 'T';

    BLOCKS
----------
     13477

ไม่นะ! ให้ตายเถอะ ฉันลบข้อมูลแกออกตั้งครึ่งตารางเลยนะ มันต้องเร็วขึ้นซิ

SQL> select * from T;

500000 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      46199  consistent gets
      13319  physical reads
          0  redo size
   28918511  bytes sent via SQL*Net to client
     367151  bytes received via SQL*Net from client
      33335  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     500000  rows processed

เห็นผลลัพธ์หรือยังครับ ถึงแม้ว่าเราจะลบข้อมูลไปถึงครึ่งตาราง ตัวฐานข้อมูลก็ยังคงรับรู้ว่าพื้นทีว่างเหล่านั้นเป็นของมันอยู่ เนื่องจากข้อมูลที่เราทำการลบไปมันคือส่วนหัวตาราง แต่ส่วนท้ายยังอยู่เราเรียกส่วนนี้ว่า High water mark  ลองดูภาพประกอบด้านล่างนี้ครับ

high watermark

วิธีการแก้ไขปัญหา Fragment บนตารางอย่างง่ายๆ คือ การลด High water mark และทำการ Defragmentation table นั้นเองครับ ซึ่งในตอนหน้าผมจะสาธิตให้ดู 2 วิธีครับคือ
1. ALTER TABLE MOVE
2. ALTER TABLE SHRINK SPACE

ขอบคุณครับ
ขอบคุณข้อมูลเพิ่มเติมจาก
Oracle table fragmentation
ขอบคุณภาพสวยๆ จาก Markus Spiske / raumrot.com

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