APPEND hint เคล็ด(ไม่)ลับสำหรับการ INSERT ข้อมูล

ไม่ได้เขียนบทความซะนานเลยครับ เนื่องจากช่วงหลังๆ มานี้งานค่อนข้างเยอะ วันนี้เลยเข้าเขียนบทความต่อจากคราวที่แล้วที่เราพูดถึงการใช้งาน NOLOGGING กันไป ซึ่งมันมีความเกี่ยวข้องกับ APPEND hint ที่ผมจะกล่าวถึงในวันนี้ครับ และจะมากล่าวคร่าวๆ กับวิธีการใช้งานด้วยครับ

APPEND hint มีผลต่อประสิทธิภาพอย่างไร?

โดยวิธีการทำงานของ APPEND hint นั้นมันจะทำการ insert ข้อมูลโดยตรงลงบน data files ซึ่งจะทำให้ประสิทธิภาพในการ INSERT … SELECT ดีขึ้นดังนี้

– ข้อมูลจะถูกเขียนลงในส่วนท้ายของตาราง แทนที่จะถูกเขียนลงในส่วนที่ว่างๆ บนตาราง

– ข้อมูลจะถูกเขียนลง Data files โดยตรงโดยไม่ผ่าน Buffer cache
– Referential integrity constraints จะไม่มีผลกับการ INSERT ข้อมูลด้วย APPEND hint

ด้วยทุกข้อด้านบนที่เกิดขึ้นทำให้การ INSERT ข้อมูลเร็วมากขึ้นกว่าการ INSERT แบบธรรมดาครับ

APPEND hint มีผลต่อขนาดของตารางอย่างไร (High Water Mark)?

อย่างที่ได้กล่าวไปก่อนหน้านี้ว่าข้อมูลจะถูกลงที่ส่วนท้ายสุดของตาราง ซึ่งนั่นไปมีผลทำให้ High Water Mark บนตารางมีค่ามากขึ้น ถึงแม้ว่าจะมีพื้นที่เหลืออยู่ในตารางมากพอที่จะ INSERT ข้อมูล ซึ่งข้อเสียข้อนี้สามารถแก้ไขได้ด้วยวิธีดังนี้

– Export ข้อมูลบนตารางมาเก็บไว้ จากนั้น TRUNCATE TABLE และ Import ข้อมูลกลับลงไปใหม่
– ใช้วิธี CREATE TABLE AS SELECT (CTAS) เพื่อสร้างตารางใหม่ จากนั้น Drop ตารางเก่าแล้วเปลี่ยนชื่อตารางที่สร้างขึ้นใหม่
– ใช้วิธีการ Online Table Redifinition เพื่อสร้างตารางขึ้นมาใหม่
– ใช้วิธีการ Online Segment Shrink เพื่อบีบอัดข้อมูลและจัดเรียงข้อมูลบนตาราง

APPEND hint มีผลต่อการใช้ Redo บนฐานข้อมูลอย่างไร?

ถ้าฐานข้อมูลไม่ได้เปิดใช้งาน ARCHIVELOG การใช้เพียง APPEND hint จะช่วยลดการ Generate Redo ได้ ซึ่งในความเป็นจริงแล้วเป็นไปได้ยากมากที่ฐานข้อมูลของคุณจะกำหนดให้ใช้งานบนระบบ NOARCHIVELOG… แล้วจะเกิดอะไรขึ้นถ้าฐานข้อมูลเปิดใช้งาน ARCHIVELOG? การใช้ APPEND hint จะไม่ช่วยลดการ Generate Redo นอกเสียจากว่าตารางนั้นๆ ถูกกำหนดให้เป็น NOLOGGING ลองมาดูตัวอย่างกันครับ

ตัวอย่างแรกนี้ฐานข้อมูลของผมอยู่ในโหมด NOARCHIVELOG

SQL> CREATE TABLE t1 AS SELECT * FROM all_objects WHERE 1=2;

Table created.

SQL> SET AUTOTRACE ON STATISTICS
SQL> INSERT INTO t1 SELECT * FROM all_objects;

10661 rows created.

Statistics
----------------------------------------------------------
       1104  recursive calls
       1017  db block gets
       6654  consistent gets
          3  physical reads
    1136236  redo size
        824  bytes sent via SQL*Net to client
        729  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
         64  sorts (memory)
          0  sorts (disk)
      10661  rows processed

SQL> TRUNCATE TABLE t1;

Table truncated.

SQL> INSERT /*+ APPEND */ INTO t1 SELECT * FROM all_objects;

10661 rows created.

Statistics
----------------------------------------------------------
        520  recursive calls
        347  db block gets
       6229  consistent gets
          0  physical reads
      28300  redo size
        810  bytes sent via SQL*Net to client
        743  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
         64  sorts (memory)
          0  sorts (disk)
      10661  rows processed

SQL> COMMIT;

Commit complete.

SQL>

อย่างที่กล่าวไปข้างบนครับว่า ถ้าฐานข้อมูลนั้นๆ ทำการเปิดโหมด NOARCHIVELOG การใช้งาน APPEND hint จะทำให้ช่วยลดการ Generate Redo ตัวอย่างต่อไปผมจะทำตามตัวอย่างแรกนะครับ แต่ครั้งนี้เราจะรันคำสั่งบนฐานข้อมูลที่เปิดโหมด ARCHIVELOG

SQL> CREATE TABLE t1 AS SELECT * FROM all_objects WHERE 1=2;

Table created.

SQL> SET AUTOTRACE ON STATISTICS
SQL> INSERT INTO t1 SELECT * FROM all_objects;

10661 rows created.

Statistics
----------------------------------------------------------
       1117  recursive calls
       1010  db block gets
       6668  consistent gets
         13  physical reads
    1136136  redo size
        824  bytes sent via SQL*Net to client
        729  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
         64  sorts (memory)
          0  sorts (disk)
      10661  rows processed

SQL> TRUNCATE TABLE t1;

Table truncated.

SQL> INSERT /*+ APPEND */ INTO t1 SELECT * FROM all_objects;

10661 rows created.

Statistics
----------------------------------------------------------
        520  recursive calls
        346  db block gets
       6229  consistent gets
          0  physical reads
    1138544  redo size
        810  bytes sent via SQL*Net to client
        743  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
         64  sorts (memory)
          0  sorts (disk)
      10661  rows processed

SQL> COMMIT;

Commit complete.

SQL>

จะเห็นได้ว่าแม้เราจะใส่ APPEND hint เข้าไปแต่มันก็ไม่ช่วยลดการ Generate redo ลงเลย สำหรับฐานข้อมูลที่ใช้งานโหมด ARCHIVELOG เพื่อจะทำให้ hint มีผลกับฐานข้อมูล เราต้องทำการเปลี่ยนค่าของตารางให้เป็น NOLOGGING เสียก่อนครับ

SQL> ALTER TABLE t1 NOLOGGING;

Table altered.

SQL> TRUNCATE TABLE t1;

Table truncated.

SQL> SET AUTOTRACE ON STATISTICS
SQL> INSERT INTO t1 SELECT * FROM all_objects;

10661 rows created.

Statistics
----------------------------------------------------------
        504  recursive calls
       1011  db block gets
       6505  consistent gets
          1  physical reads
    1137000  redo size
        827  bytes sent via SQL*Net to client
        729  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
         64  sorts (memory)
          0  sorts (disk)
      10661  rows processed

SQL> TRUNCATE TABLE t1;

Table truncated.

SQL> INSERT /*+ APPEND */ INTO t1 SELECT * FROM all_objects;

10661 rows created.

Statistics
----------------------------------------------------------
        520  recursive calls
        347  db block gets
       6229  consistent gets
          0  physical reads
      28300  redo size
        811  bytes sent via SQL*Net to client
        743  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
         64  sorts (memory)
          0  sorts (disk)
      10661  rows processed

SQL> COMMIT;

Commit complete.

SQL> DROP TABLE t1 PURGE;

Table dropped.

SQL>

สรุป

เราสามารถช่วยทำให้ประสิทธิภาพในการ INSERT ข้อมูลเพิ่มขึ้นได้ด้วยการใช้ APPEND hint ซึ่งสำหรับฐานข้อมูลที่เปิดโหมด NOARCHIVELOG นั้นสามารถใช้ได้ปกติ นอกเสียจากว่าถ้าฐานข้อมูลของคุณเป็น ARCHIVELOG คุณต้องทำการเปลี่ยนค่าของตารางนั้นๆ ให้เป็น NOLOGGING เสียก่อนนะครับ เมื่อนั้นเคล็บวิชา APPEND hint ของเราก็จะสามารถแสดงศักยภาพออกมาได้อย่างเต็มที่ครับ ขอบคุณครับ

ข้อมูลเพิ่มเติมจาก
ORACLE-BASE: APPEND Hint

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

Connecting to %s