ORA-1652 TEMP เต็ม! ทำไงดีอ่ะ?

สวัสดีครับ เดียวเริ่มตั้งแต่ช่วงเดือนหน้าผมอาจจะมีเรื่องมาแชร์ทุกคนมากขึ้นครับ เนื่องจากจะเริ่มทำการเตรียมตัวสอบเพื่อเอา Oracle 11g Performance Tuning Certified Expert ถ้าเจอเรื่องอะไรน่าสนใจก็จะเอามาลงแบ่งปันกันเหมือนเดิมครับและสำหรับวันนี้ผมขอเกริ่นนำกันก่อนละกันนะครับ เราจะทำอย่างไรดีถ้า Temporary tablespace เต็ม? ซึ่งเราอาจจะทราบว่ามันจะเกิด ORA-1652: unable to extend temp segment ใน Alert log ขึ้น แล้วทำไงดีหละ? มันเต็มไปแล้วอ่ะ… วันนี้ผมเอาความรู้เกี่ยวกับ Temporary tablespace วิธีการมอนิเตอร์และรับมือกับปัญหา ORA-1652 มาฝากครับ

เมื่อใดก็ตามที่การจัดเรียงข้อมูลนั้นใหญ่เกินไปที่จะทำบนหน่วยความจำได้ Oracle จะทำการจัดสรรพื้นที่บน Temporary tablespace เพื่อที่จะรองรับการจัดเรียงข้อมูลนั้นๆ บนดิสก์แทน สำหรับ Temporary tablespace นั้นถือว่าเป็นทรัพยากรที่ถูกจัดสรรให้ใช้ร่วมกันสำหรับทุกๆ เซสชั่นบนฐานข้อมูลและไม่สามารถตั้งค่าโควต้าสำหรับแต่ละเซสชั่นได้ ถ้าผู้ใช้ทำการดึงข้อมูลที่มีคิวรี่แย่ๆ ที่อาจทำให้เกิด Cartesion product (Bad join operation) นั่นอาจหมายถึงคิวรี่นั้นๆ อาจจะต้องการพื้นที่ขนาดใหญ่มากบน Temporary tablespace และอาจหมายถึงการเกิดผลกระทบกับผู้ใช้คนอื่นๆ บนระบบด้วย และสุดท้ายเมื่อ Temporary tablespace เต็ม แต่ SQL statement ตัวอื่นๆ ที่ยังต้องการใช้พื้นที่บน tablespace เพื่อทำการจัดเรียงข้อมูลนั่นจะถูกยกเลิกพร้อมกับ “ORA-1652: unable to extend temp segment” นั่นเองครับ

หลักการเบื้องต้นในการจัดเรียงข้อมูลบน Oracle

มีอยู่หลายสถานการณ์ที่จะทำให้เกิดการจัดเรียงข้อมูลบน Oracle ยกตัวอย่างเช่น Oracle จะทำการจัดเรียงข้อมูลเมื่อเราทำการสร้างอินเด็กซ์หรือสำหรับคิวรี่ที่มีการใช้ ORDER BY หรือ GROUP BY เซสชั่นนั้นๆ จะเริ่มทำการจัดเรียงข้อมูลบนหน่วยความจำ ถ้าขนาดของข้อมูลที่ถูกจัดเรียงมีขนาดไม่ใหญ่มากนัก ข้อมูลทั้งหมดจะสามารถจัดเรียงให้เสร็จได้บนหน่วยความจำซึ่งเป็นเช่นเดียวกันเมื่อ Oracle ต้องการเก็บข้อมูลบน Global temporary table หรือสร้าง Hash table สำหรับ Hash join แต่เมื่อใดก็ตามที่การจัดเรียงข้อมูลเกินกว่า Threshold ที่กำหนดสำหรับการจัดเรียงข้อมูลบนหน่วยความจำ เมื่อนั้น Oracle จะทำการแตกส่วนในการจัดเริยงข้อมูลเป็นส่วนเล็กๆ เพื่อทำให้สามารถจัดเรียงข้อมูลบนหน่วยความจำได้และจะทำการเขียนข้อมูลแต่ละส่วนเล็กๆ ลงบน Temporary tablespace สำหรับ Threshold ที่กำหนดว่าสามารถใช้หน่วยความจำได้มากแค่ไหนในการจัดเรียงข้อมูลแต่ละเซสชั่นจะถูกกำหนดจาก Instance parameter ถ้า workarea_size_policy ถูกกำหนดเป็น AUTO แล้ว pga_aggregate_target จะเป็นตัวกำหนดว่าหน่วยความจำจะสามารถใช้ได้มากที่สุดแค่ไหนสำหรับทุกๆ เซสชั่นในการจัดเรียงข้อมูลและการทำ Hashing แต่ถ้า workarea_size_policy ถุกกำหนดให้เป็น MANUAL เมื่อนั้น sort_area_size, hash_area_size และ bitmap_merge_area_size จะเป็นพารามิเตอร์ที่เป็นตัวกำหนดว่าแต่ละเซสชั่นจะสามารถใช้หน่วยความจำได้มากแค่ไหนในแต่ละการทำงาน (Sorting, Hashing หรือ Merging)

บน Temporary tablespace นั้นประกอบด้วย Temporary segment ซึ่งเป็นของ SYS ไม่ใช่ของผู้ใช้ที่เข้ามาทำการจัดเรียงข้อมูลและมีเพียง 1 Segment เท่านั้นต่อ 1 Temporary tablespace เนื่องจากว่าหลายๆ เซสชั่นสามารถแชร์พื้นที่ใช้ร่วมกันได้ใน Segment นั้นๆ และ Temporary tablespace สามารถรองรับได้เฉพาะ Temporary segment เท่านั้น ยกตัวอย่างเช่น การเขียนข้อมูลบน Temporary tablespace จะไม่มีการ Generate redo/undo และเช่นกันกับการจัดสรรพื้นที่บน Temporary segment block ให้กับแต่ละเซสชั่นนั้นจะไม่ถูกบันทึกลงบน Data dictionary และเมื่อใดก็ตามที่ผลลัพธ์บน Temporary tablespace ถูกใช้เสร็จแล้วหรือไม่จำเป็นอีก มันจะถูกเปลี่ยนสถานะแล้วสามารถถูกแทนที่โดยการจัดเรียงข้อมูลที่เข้ามาใหม่ได้

การจัดเรียงข้อมูลนั้นจะถูกยกเลิกก็ต่อเมื่อ
1) ไม่มี unused block บน Temporary segment
2) ไม่มีพื้นที่เหลือพอบน Temporary tablespace สำหรับการรองรับการจัดเรียงข้อมูลใหม่ และจะเกิด ORA-1652 ขึ้น

และอีกสิ่งที่ต้องพึ่งจำเอาไว้นั่นคือ ORA-1652 ไม่จำเป็นต้องเกิดปัญหากับเฉพาะ Temporary tablespace เท่านั้น ยกตัวอย่างเช่น การย้ายตารางจาก Tablespace นึงไปยังอีก Tablespace นึงก็สามารถเกิด ORA-1652 ได้ถ้า Tablespace ปลายทางมีพื้นที่เหลือไม่เพียงพอ

วิธีการหาว่า SQL Statement ไหนที่ถูกยกเลิกเนื่องจาก Temporary tablespace ไม่พอ (ORA-1652)

มันมีประโยชน์มากที่ Oracle แจ้งเตือนเราว่ามีปัญหาเกิดขึ้น ORA-1652 จะแสดงอยู่ใน Alert log แต่โชคร้ายที่ Error message นั้นไม่ได้บอกมาด้วยว่า SQL Statement ตัวไหนที่ถูกยกเลิก แต่อย่างไรก็ตาม Oracle มีตัวช่วยที่ทำให้เราสามารถเก็บข้อมูลเพิ่มเติมเกี่ยวกับ ORA-1652 ได้ครับ เมื่อใดก็ตามที่เกิดข้อผิดพลาดขึ้น Oracle จะทำการเขียน Trace file ขึ้นซึ่งมันจะบอกข้อมูลต่างๆ เกี่ยวกับ ORA-1652 รวมถึง SQL Statement ที่กำลังประมวลผลอยู่ในจังหวะที่เกิด ORA-1652 ขึ้นและที่สำคัญคือ Overhead ค่อนข้างน้อยครับ เนื่องจาก Trace file จะถูกเขียนเมื่อเจอ ORA-1652 เท่านั้น ด้วยคำสั่งนี้ครับ

ALTER SESSION SET EVENTS '1652 trace name errorstack';

หรือจะตั้งค่าในระดับ Instance ก็ได้ครับ

ALTER SYSTEM SET EVENTS '1652 trace name errorstack';

หรือจะตั้งค่าในระดับ SPFILE เลยก็ยังได้ครับ

ALTER SYSTEM SET EVENT = '1652 trace name errorstack' SCOPE = SPFILE;

และเช่นกันเดียวกับวิธีการปิด Diagnostic event นี้ครับ

ALTER SESSION SET EVENTS '1652 trace name context off';

ALTER SYSTEM SET EVENTS '1652 trace name context off';

ALTER SYSTEM RESET EVENT SCOPE = SPFILE SID = '*';

โดยในส่วนของตัว Trace file จะถูกเขียนลงบน user_dump_dest ครับ แต่ต้องจำไว้นะครับว่า Trace file ที่ได้มาไม่ได้บอกว่า SQL Statement นั้นๆ เป็นสาเหตุของปัญหา มันสามารถบอกได้แค่ว่า SQL Statement ใดที่ถูกยกเลิก ยกตัวอย่างเช่น คิวรี่ตัวที่หนึ่งสามารถประมวลผลได้สำเร็จแต่กินพื้นที่บน Temporary tablespace ไป 99% ในขณะที่คิวรี่ตัวที่ 2 ที่พยายามเข้ามาใช้ Temporary tablespace แต่ถูกยกเลิกเนื่องจากพื้นที่ไม่พอ ถึงแม้มันจะต้องการพื้นที่ไม่มากเท่าคิวรี่แรก เห็นได้ชัดเลยใช่ไหมครับว่าสาเหตุจริงๆ มาจากคิวรี่ไหน?

และเหมือนๆ กันกับเครื่องมือ Debug อื่นๆ คุณควรใช้มันเมื่อจำเป็นจริงๆ เท่านั้นครับ

การตรวจสอบการใช้งาน Temporary tablespace

แทนที่เราจะรอให้ Temporary tablespace เต็มและเกิดปัญหาขึ้น เราสามารถตรวจสอบการทำงานของมันได้แบบนาทีต่อนาที ชั่วโมงต่อชั่วโมงได้เลยครับ ซึ่งข้อมูลทั้งหมดที่เราจำเป็นต้องใช้อยู่ที่ไหนหละ? ทั้งหมดอยู่ใน V$ views นั่นเองครับด้วยวิธีง่ายๆ คือการคิวรี่นั่นเองครับ

SQL> SELECT     A.tablespace_name tablespace, D.mb_total,
  2     SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
  3     D.mb_total - SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_free
  4  FROM       v$sort_segment A,
  5     (
  6     SELECT  B.name, C.block_size, SUM(C.bytes) / 1024 / 1024 mb_total
  7     FROM    v$tablespace B, v$tempfile C
  8     WHERE   B.ts# = C.ts#
  9     GROUP BY B.name, C.block_size
 10     ) D
 11  WHERE      A.tablespace_name = D.name
 12  GROUP BY A.tablespace_name, D.mb_total;

TABLESPACE                        MB_TOTAL    MB_USED    MB_FREE
------------------------------- ---------- ---------- ----------
TEMP                                 1024          0       1024

SQL>

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

SQL> SELECT     S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
  2     S.program, SUM(T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
  3     COUNT(*) sort_ops
  4  FROM       v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
  5  WHERE      T.session_addr = S.saddr
  6  AND        S.paddr = P.addr
  7  AND        T.tablespace = TBS.tablespace_name
  8  GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
  9       S.program, TBS.block_size, T.tablespace
 10  ORDER BY  sid_serial;

SID_SERIAL USERNAME   OSUSER     SPID         MODULE                         PROGRAM                           MB_USED TABLESPACE             SORT_OPS
---------- ---------- ---------- ------------ ------------------------------ ------------------------------ ---------- -------------------- ----------
252,12604  TOPSUSER00 was        21027        JDBC Thin Client               JDBC Thin Client                        1 TBSP_TEMP00                   1

SQL>

จากคิวรี่ด้านบนจะแสดง SID และ Serial# ของเซสชั่นที่มีการใช้ Temporary tablespace อยู่ และข้อมูลต่างๆ เกี่ยวกับเซสชั่นนั้นๆ รวมถึงพื้นที่ที่ใช้อยู่และจำนวนครั้งในการจัดเรียงข้อมูลด้วย

SQL> SELECT     S.sid || ',' || S.serial# sid_serial, S.username,
  2     T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
  3     T.sqladdr address, Q.hash_value, substr(Q.sql_text,1,30) sql_text
  4  FROM       v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
  5  WHERE      T.session_addr = S.saddr
  6  AND        T.sqladdr = Q.address (+)
  7  AND        T.tablespace = TBS.tablespace_name
  8  ORDER BY S.sid;

SID_SERIAL USERNAME                          MB_USED TABLESPACE                      ADDRESS          HASH_VALUE SQL_TEXT
---------- ------------------------------ ---------- ------------------------------- ---------------- ---------- ------------------------------
60,23235   ORAUSER                              1 TBSP_TEMP00                     0000000CAA7ABB50  793519342 WITH BASE AS ( SELECT A.

SQL>

และสุดท้ายครับ จากคิวรี่ด้านบนจะแสดงข้อมูลคล้ายๆ ตัวก่อนหน้านี้แตกต่างกันตรงที่คิวรี่ตัวนี้จะโชว์ SQL Statement ที่ใช้ Temporary tablespace อยู่ครับ (ผลลัพธ์ที่ดีที่สุดคือใช้ร่วมกันทั้ง 3 คิวรี่เลยครับ)

สรุป

DBA อย่างเราๆ ต้องเข้าใจและประยุกต์ใช้ความรู้ที่มีในการจัดการ ดูแล Temporary tablespace ให้พร้อมใช้งานเสมอด้วยนะครับ เนื่องจาก ORA-1652 เป็น Common error ซึ่งเราจะเจอกันได้บ่อยๆ บนฐานข้อมูลแบบ OLTP ทางที่ดีเราควรวิเคราะห์ดูด้วยว่ามันเล็กเกินไปหรือไม่? ถ้ามันมีขนาดใหญ่เพียงพอแล้ว มีทางไหนบ้างไหมที่จะสามารถลดการใช้งานบน Temporary tablespace ลงได้? ยกตัวอย่างเช่น การเพิ่ม PGA memory หรือแม้กระทั่งการ Tuning SQL Statement เพื่อลดการจัดเรียงข้อมูลลงครับ สุดท้ายก็แล้วแต่ทรัพยากรบนระบบของท่านเองนะครับ ว่าวิธีใดเหมาะสมที่สุด สวัสดีครับ

ขอขอบคุณข้อมูลเพิ่มเติมจาก http://www.dbspecialists.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