จาก http://www.dba-oracle.com/t_dml_large_insert_update_hanging.htm
อาจจะเกิดปัญหาดังนี้
1.Insert aborts with a ORA-01555 snapshot too old
2.Insert hangs when your archive redo log directory becomes full.
วิธีต่างๆที่แนะนำ
1.Divide and Conquer
คำอธิบาย
จาก http://www.dba-oracle.com/t_dml_large_insert_update_hanging.htm
Commit job ทุกๆช่วงเวลา เพื่อให้ปลดปล่อยการใช้งาน (release hold) ของ Rollback Segments
2.Check space in archived redo log file system
คำอธิบาย
จาก http://www.dba-oracle.com/t_dml_large_insert_update_hanging.htm
ตรวจสอบพื้นที่ของ disk ให้เพียงพอที่จะเก็บ Archived redo log ที่เพิ่มขึ้น
3.Dedicated undo
คำอธิบาย
จาก http://www.dba-oracle.com/t_dml_large_insert_update_hanging.htm
ตรวจสอบพื้นที่ของ Undo หรือ Rollback Segment ให้เพียงพอกับ insert update Large Data และการทำงานอื่นๆที่ต้องการแก้ไข data พร้อมกันด้วย
4.Parallelize the insert job
คำอธิบาย
จาก http://www.dba-oracle.com/t_dml_large_insert_update_hanging.htm
การ insert โดยใช้ job แบบ parallel
5.Bulk DML
คำอธิบาย
จาก http://www.dba-oracle.com/t_dml_large_insert_update_hanging.htm
ใช้ PL/SQL bulk operators เพื่อเพิ่มความเร็วด้วยการ reducing context switching
6.NOLOGGING option
คำอธิบาย
จาก http://www.dba-oracle.com/t_dml_large_insert_update_hanging.htm
จาก http://dba-oracle.com/t_nologging_append.htm
เป็นวิธีการที่ดีสำหรับเพิ่มความเร็ว insert และการสร้าง index เพราะไม่ทำ redo log แต่ต้องระวังคือ ไม่มี Redo log ให้ใช้งาน ถ้าต้องทำ roll-forward สำหรับการ recovery
เมื่อใช้งาน NOLOGGING option จะต้องพิจารณาคือ
1.Backup data ทั้งหมด ก่อนและหลังดำเนินการ NOLOGGING
2.Only NOLOGGING operations during the NOLOGGING window
คือ ระหว่าง backup ก่อนและหลัง จะเป็นการดำเนินการ NOLOGGING เพียงอย่างเดียวเท่านั้น เรียกว่า backup sandwich
ขั้นตอนที่เกิดขึ้นคือ backup data (ทำก่อนจะดำเนินงาน NOLOGGING) ตามด้วย NOLOGGING operation ตามด้วย backup data (ทำหลังจากดำเนินงาน NOLOGGING เสร็จแล้ว)
คำสั่ง NOLOGGING ไม่ใช่ SQL Hint และ option NOLOGGING มีความซับซ้อนและขึ้นอยู่กับหลายปัจจัย
ถ้า Database คือ NOARCHIVELOG mode และไม่ใช้ APPEND hint สำหรับ inserts ระบบจะยังคงสร้าง redo log อยู่เสมอ (ประโยคนี้บอกถึงว่า การปิด Archivelog ไม่ได้ทำให้หยุดการสร้าง redo log)
ถ้า Database คือ ARCHIVELOG mode (หรือ NOARCHIVELOG mode ก็ได้) ส่วนของ table ต้อง alter เพื่อใช้ NOLOGGING mode และ SQL insert ต้องใช้ APPEND hint เท่านั้น จึงจะไม่สร้าง redo log ไม่เช่นนั้น redo log จะยังคงสร้างตามปกติอยู่
เราสามารถใช้ NOLOGGING สำหรับ batch inserts into tables ก็ได้ และสำหรับการสร้าง index ก็ได้
1.การ insert แบบ NOLOGGING คือ ใช้ APPEND hint และกำหนด table ด้วย NOLOGGING จะทำให้ไม่สร้าง redo log
SQL> alter table MyTable nologging;
SQL> insert /*+ append */ into MyTable ….;
SQL> insert /*+ append */ into MyTable ….;
SQL> insert /*+ append */ into MyTable ….;
….…….
………
2.การ create index แบบ NOLOGGING สามารถเพิ่มความเร็วในการสร้าง index ได้ถึง 30%
แต่มีสิ่งที่ต้องระวังคือ การสร้าง index แบบ re-run ใหม่ ขณะที่เรากำลังทำ roll-forward สำหรับงาน Database recovery
SQL> create index newidx . . . nologging;
3.การดำเนินการเหล่านี้สามารถใช้ NOLOGGING ได้
SQL> alter table...move partition
SQL> alter table...split partition
SQL> alter index...split partition
SQL> alter index...rebuild
SQL> alter index...rebuild partition
SQL> create table...as select
SQL> create index
SQL> direct load with SQL*Loader
SQL> direct load INSERT (using APPEND)
NOLOGGING clause warning
1. ระวังเมื่อใช้ UNRECOVERABLE และ NOLOGGING เมื่อดำเนินการ CREATE INDEX หรือ CREATE TABLE AS SELECT (CTAS)
2. การใช้ CTAS with NOLOGGING หรือ UNRECOVERABLE จะส่งคำสั่ง create ที่เกิดขึ้นจริง ไปยัง redo log (เพราะข้อมูลนี้จำเป็นต้องใช้ใน data dictionary) แต่ทุกแถวที่โหลดไปที่ตารางในระหว่างการดำเนินการจะไม่ถูกส่งไปยัง redo logs ด้วย
3.การใช้ NOLOGGING แม้ว่าจะสามารถตั้งค่าแอตทริบิวต์ NOLOGGING ได้
แต่สำหรับ table, partition, index, หรือ tablespace โหมด NOLOGGING จะไม่ดำเนินการกับทุก operation ของ schema object แม้ว่าจะ set NOLOGGING เอาไว้แล้ว
4.ไม่สามารถใช้ roll-forward ตามเวลาที่เจาะจงได้ (point in time) ถ้าใช้ NOLOGGING
เช่น CREATE INDEX NOLOGGING, CREATE TABLE AS SELECT NOLOGGING, หรือ an NOLOGGING table load
5.การใช้ NOLOGGING มักจะลดระยะเวลา run time ได้มากกว่าครึ่ง
แต่ต้องพิจารณาความเสี่ยงต่างๆด้วย
ตัวอย่าง
เมื่อต้อง reorganize ที่ large tables ที่เกิดจาก insert แบบ CTAS ที่ใช้ NOLOGGING (เป็นตัวอย่างที่ควรระวัง)
Create table ใหม่ จาก table เก่า ที่มีขนาดใหญ่
SQL> create table table_new tablespace new_ts NOLOGGING
as select * from table_old;
Drop table เก่า
SQL> drop table table_old;
Rename table ใหม่เป็นชื่อเก่า
SQL> rename table_new to table_old;
7.Hint /*+ APPEND */ and /*+ APPEND_VALUES*/
คำอธิบาย
จาก https://oracle-base.com/articles/misc/append-hint
จาก https://oracle-base.com/articles/11g/append-values-hint-11gr2
จาก https://sites.google.com/site/oraclepracticals/oracle-sql/append-hint
ให้ใช้ hint /*+ APPEND */ สำหรับกรณี insert as select แต่ถ้าใช้กับ insert values จะมีผลเหมือน insert ปกติ
ให้ใช้ hint /*+ APPEND_VALUES*/ สำหรับกรณี insert values แต่ถ้าใช้กับ insert as select จะมีผลเหมือน insert ปกติ
มีผลกับ Performance อย่างไร
เป็น direct-path insert คือ เขียน data ไปที่ data file หรือ disk โดยตรง โดยไม่ใช้ database buffer cache
โดยเพิ่ม data ไปที่ตำแหน่งสุดท้ายของ table ซึ่ง insert ตามปกติแล้วจะเพิ่ม data ไปที่พื้นที่ว่างภายใน table
มีผลกับ Table Size (High Water Mark) อย่างไร
เป็นการ direct-path insert data ไปที่ end of table ทำให้เป็นการเพิ่ม table high water mark แม้ว่าจะมีพื้นที่ว่างภายใน table ก็ตาม
การใช้ APPEND hint กับ table ที่มีการ delete data บ่อยๆ อาจจะทำให้ table มีขนาดใหญ่ โดยที่ block ถูกใช้เก็บ data เพียงจำนวนน้อย ไม่เต็มพื้นที่ของ table
ทำให้ต้องจัดการด้วยการ shrink operation ด้วยวิธีการดังต่อไปนี้ วิธีใดวิธีหนึ่งก้ได้
1.export data ออกไปก่อน แล้ว truncate table แล้วจึง import เข้ามาใหม่
2.CREATE TABLE tableใหม่ AS SELECT จาก tableเก่า เพื่อสร้าง table ใหม่ ให้ได้ data ที่วางเรียงติดกันภายใน table ใหม่นี้ แล้ว drop table เก่า และเปลี่ยนชื่อ table ใหม่เพื่อแทนที่ table เก่า
3.ใช้ online segment shrink operation สำหรับ compact the data เพื่อให้ data จัดเรียงติดกันมากขึ้น
มีผลกับ Redo Generation อย่างไร
ถ้า database คือ NOARCHIVELOG mode จะช่วยลดการสร้าง redo log
แต่ถ้า database คือ ARCHIVELOG mode จะไม่ลดการสร้าง redo log นอกจากจะกำหนด table เป็น NOLOGGING
Oracle แนะนำไม่ให้ใช้ APPEND hint กับ referential integrity constraints และ trigger เพราะทำให้ data logically corrupt ได้
ดังนั้นควรใช้ insert แบบปกติ หรือ disable referential integrity constraints และ trigger ก่อน
8.Drop or disable index and constraint
คำอธิบาย
จาก http://www.dba-oracle.com/t_insert_tuning.htm
การ drop/disable index และ constraint แล้วจึง insert Large Data แล้วจึง create/enable rebuild index จะทำงานได้เร็วกว่า การ enable index ขณะที่มีการ insert Large Data
นอกจากนี้ การ rebuild index ใน table ที่มี large block size ขนาดใหญ่ จะทำได้ไวมากขึ้น
9.Increasing the Log File Size
คำอธิบาย
จาก https://logicalread.com/oracle-11g-increase-log-checkpoint-interval-mc02/#.Wl7M9K6WbIU
การเพิ่มความเร็วให้กับการ insert, update และ delete ด้วยการเพิ่ม size ของ redo log file ควรใช้ร่วมกับ disk ที่ทำงานได้เร็วด้วย
ผลที่เกิดขึ้นคือ จะทำให้แต่ละจุดของ recovery มีช่วงเวลาที่ห่างกันมากขึ้น และประสิทธิภาพเพิ่มขึ้นเพราะทำให้ลดการ switch log และ checkpoint
สามารถทำงานร่วมกับ LOG_CHECKPOINT_INTERVAL โดย default = 0 คือ switch redo log เมื่อเก็บ data จนเต็มแล้ว สิ่งนี้สำหรับกำหนดระยะเวลาในการทำ checkpoints ซึ่งมีผลกระทบกับระยะห่างของแต่ละ checkpoint ให้น้อยลงหรือมากขึ้นตามค่าที่กำหนดได้เช่นกัน
การ uncommitted transactions (หรือเรียกว่า rollback) จะ generate redo entries เช่นกัน เพราะว่าสิ่งนี้จะมีการ generate undo records และ undo records เหล่านี้ก็จะต้อง write ไปที่ redo logs เช่นกัน
