สร้างการเพิ่มข้อมูลขนาดใหญ่บน Database แบบง่ายสำหรับทดสอบการทำงาน
อธิบาย
- เมื่อ run คำสั่ง จะเกิดการใช้ทรัพยากรต่างๆดังนี้
- ที่ Database ใช้พื้นที่ของ temp Tablespace (เฉพาะใช้ /*+ append_values */ หรือ /*+ append */)
- ที่ Database เกิด Logical I/O (มีการอ่านเขียนที่ buffer)
- เกิด Disk I/O read และ write (write เกิดมากกว่า read ค่อนข้างแยอะ)
- เกิดการขยายขนาด data file เมื่อ transaction เริ่มทำงาน (โดยยังไม่ commit หรือ rollback)
- เกิดการเขียน redo log เมื่อ transaction เริ่มทำงาน (โดยยังไม่ commit หรือ rollback)
- User มีการใช้พื้นที่ tablespace นั่นคือ ขนาด tablespace เพิ่มขึ้น เมื่อ transaction เริ่มทำงาน (โดยยังไม่ commit หรือ rollback)
- Object มีการใช้พื้นที่ segment นั่นคือ ขนาด segment เพิ่มขึ้น เมื่อ transaction เริ่มทำงาน (โดยยังไม่ commit หรือ rollback)
- เกิดการใช้ memory เล็กน้อย
- การเพิ่มข้อมูลสามารถเกิดปัญหาดังนี้
- อาจทำให้เกิด error ORA-00257: archiver error. และถ้ายังไม่แก้ปัญหา จะเกิดการ stop execute transactions
ที่ alert log แสดง error
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim ขนาดพื้นที่ที่ต้องการเพิ่ม bytes disk space from ขนาดพื้นที่ที่จำกัดไว้ limit
ORA-19815: WARNING: db_recovery_file_dest_size of ขนาดพื้นที่ที่จำกัดไว้ bytes is 100.00% used, and has 0 remaining bytes available.
วิธีแก้ไขไม่ให้สร้าง redolog ให้ปิด log ของ table และใช้คำสั่ง insert value ด้วย /*+ append_values */ หรือ insert as select ด้วย /*+ append */
ตัวอย่าง ปิด log ของ table
SQL> alter table u1.tb_test_large nologging;
- ถ้าต้องการ add primary key ทำภายหลัง insert data เพราะถ้าทำก่อน insert data จะทำให้การ insert data ทำงานช้ามาก
ตัวอย่าง
SQL> alter table u1.tb_test_large add constraint
tb_test_large3_pk primary key(object_id);
SQL> begin
dbms_stats.gather_table_stats
( ownname => 'u1',
tabname => 'tb_test_large',
cascade => true );
end;
/
สร้าง insert large data โดยกำหนด data size ที่ต้องการ
จาก http://stackoverflow.com/questions/6674919/generate-and-insert-large-clob-1-mb-using-sqlplus
กำหนด
Create table
SQL> create table u1.test_tb_large1(id number
, large_text clob);
No logging กำหนดให้ไม่เก็บ redolog
SQL> alter table u1.test_tb_large1 nologging;
Insert data
CLOB 32767 byte or 32 KB is the largest string we can use in PL/SQL
ตัวอย่าง
ถ้าต้องการ data ขนาด 1 MB = 0.03125 MB * 30 = ใช้ for i in 1..30
ต้องการ data ขนาด 1024 MB = 1 GB = 0.03125 MB * 32768 = ใช้ for i in 1..32768
ต้องการ data ขนาด 10240 MB = 10 GB = 0.03125 MB * 327680 = ใช้ for i in 1..327680
RPAD คือ Store ใช้เพิ่ม characters ทางขวาของ string ในที่นี้คือ "X" โดย เพิ่ม "X" จำนวน 32767 ตัว มีค่าเท่ากับ 32767 byte หรือ 32 KB หรือ 0.03125 MB
แล้วจึง insert ลง table
SQL> declare
c clob;
i number;
begin
for i in 1..30
loop
-- 32767 byte or 32 KB or 0.03125 MB
c := c||rpad('X', 32767, 'X');
end loop;
insert /*+ append */ into u1.test_tb_large1 values(1, c)
;
end;
/
Check
SQL> set long 5000
SQL> set linesize 1000
SQL> set pagesize 1000
SQL> select * from u1.test_tb_large1;
ID LARGE_TEXT
--- ---------------------------------------------------------------------------------
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
...
.....
........
สร้าง insert large data โดยใช้ loop
อธิบาย
- ถ้าใช้ /*+ append */ หรือ /*+ append_values */ โดยไม่ commit จะเกิด error ORA-12838: cannot read/modify an object after modifying it in parallel
เพราะทุกครั้งที่ loop run คำสั่ง insert จะเปิด transaction ใหม่เสมอ จึงต้องจบ transaction เดิมก่อน
กำหนด
Create table
SQL> create table u1.tb_test_large2init(text varchar2(1000));
SQL> insert into u1.tb_test_large2init(text) values('XXX');
SQL> create table u1.tb_test_large2(id number,text varchar2(1000));
SQL> alter table u1.tb_test_large2 nologging;
Insert data
SQL> declare
i number;
begin
for i in 1..1000
loop
--insert /*+ append */ into u1.tb_test_large2(id,text) select i,text from u1.tb_test_large2init;
insert /*+ append_values */ into u1.tb_test_large2(id,text) values(i,'XXX');
commit;
end loop;
end;
/
ถ้าต้องการให้เป็นแบบ infinite
declare
i number:=1;
begin
loop
--insert /*+ append */ into u1.tb_test_large2(id,text) select :i,text from u1.tb_test_large2init;
insert /*+ append_values */ into u1.tb_test_large2(id,text) values(i,'XXX');
commit;
i:=i+1;
end loop;
end;
/
