Bulk Update Perfomans Testi

Herkese Selam,

Bu yazıyı son zamanlarda sıkça gelen sorulara istinaden yazıyorum. Umarım farkındalık anlamında faydalı bir yazı olur.

Problem anlaşılması açısından oldukça basit. Amacımız büyük bir update işlemini nasıl daha hızlı yapabiliriz?

Bu sorunun çözümü ile ilgili örnek bir case yaratıp çalıştıracağımız yöntemlerin performanslarını tek tek inceleyelim.

İlk olarak içerisinde 1M’lik data olan iki tablo yaratmak ile işe başlayalım. Bu tablonun birinde salary kolonu 0(sıfır) olsun, diğerinde ise kendi belirlediğimiz değerler ile ilgili kolonu dolduralım. Daha sonra’da salary nin sıfır olduğu tablodaki değerleri, dolu olan tablodaki değerler ile update edelim.

CREATE TABLE hr.emp_bulk_main
AS
       SELECT LEVEL AS id, 0 salary
         FROM DUAL
   CONNECT BY LEVEL <= 1000000;
   
   
 CREATE TABLE hr.emp_bulk_ref
AS
       SELECT LEVEL AS id, MOD (LEVEL, 1000) + 1 salary
         FROM DUAL
   CONNECT BY LEVEL <= 1000000;  

ALTER TABLE  hr.emp_bulk_ref ADD PRIMARY KEY(id); 

2 tablomuzu yarattık. Şimdi sırayla yöntemlerimizi implemente edip sürelerine bakalım.

Yöntem 1 : Standart Update

SET TIMING ON   
UPDATE hr.emp_bulk_main bm
   SET bm.salary =
          (SELECT salary
             FROM hr.emp_bulk_ref bf
            WHERE bf.id = bm.id);

/* 1000000 rows updated.
Elapsed: 00:00:42.68 */ 

1

 

 

 

 

 

 

Yöntem 2 : MERGE Clause

SET TIMING ON   
MERGE INTO hr.emp_bulk_main bm
     USING hr.emp_bulk_ref bf
        ON (bm.id = bf.id)
WHEN MATCHED
THEN
   UPDATE SET bm.salary = bf.salary;

/* Merge successfully completed.
Elapsed: 00:00:23.58   */

2

 

 

 

 

 

 

Yöntem 3 : View Update

SET TIMING ON   
UPDATE (SELECT bm.salary msalary, bf.salary rsalary
        FROM hr.emp_bulk_main bm, hr.emp_bulk_ref bf
         WHERE bm.id = bf.id) dx
   SET dx.msalary = dx.rsalary;
   
 /*1000000 rows updated.
Elapsed: 00:00:21.98*/

3

 

 

 

 

Yöntem 4 : Array Processing 

SET TIMING ON

DECLARE
   TYPE t_arr IS TABLE OF HR.EMP_BULK_REF%ROWTYPE
                    INDEX BY PLS_INTEGER;

   marr   t_arr;
BEGIN
   SELECT *
     BULK COLLECT INTO marr
     FROM hr.EMP_BULK_REF;

   FORALL i IN 1 .. marr.COUNT
      UPDATE HR.EMP_BULK_MAIN
         SET salary = marr (i).salary
       WHERE id = marr (i).id;
END;
 /* Update islemi 30 dk dan fazla surdugu icin sonlandirildi.*/

Yöntem 5 : DBMS_PARALLEL_EXECUTE Paketi ile Paralel Update

BEGIN
   dbms_parallel_execute.drop_task ('deneme_parallel');
   dbms_parallel_execute.create_task ('deneme_parallel');
   dbms_parallel_execute.create_chunks_by_rowid (
      task_name     => 'deneme_parallel',
      table_owner   => 'HR',
      table_name    => 'EMP_BULK_MAIN',
      by_row        =>  FALSE,
      chunk_size    => 1000);
END;

BEGIN
   dbms_parallel_execute.run_task (
      task_name           => 'deneme_parallel',
      sql_stmt              => 'UPDATE (SELECT bm.salary msalary, bf.salary rsalary
                                      FROM hr.emp_bulk_main bm, hr.emp_bulk_ref bf
                                      WHERE bm.rowid between :start_id and :end_id and bm.id = bf.id) dx
                                      SET dx.msalary = dx.rsalary',
      language_flag       => DBMS_SQL.native,
      parallel_level      => 4);
END;

SELECT MAX (END_TS) - MIN (START_TS) TOTAL_TIME
  FROM user_parallel_execute_chunks
 WHERE task_name = 'deneme_parallel';

TOTAL_TIME                                        
--------------------------------------------------
+00 00:00:19.716000                               
1 row selected.

Test Ortamı:
Sanal Makine:
OS     : WinXP
DB     : Oracle 11g R2 Enterprise Edition
RAM : 1695 MB
CPU    : Intel i5 (Sanal Makinaya 3 Fiziksel çekirdek atanmis durumda)

Evet yaptığım testleri detaylı incelediğinizde hangi yöntemin yapacağınız bulk update operasyonuna daha uygun olacağını seçebilirsiniz. Yöntem 5 ile anlatmış olduğum case olası test veya production DB’lerinde paralellik seviyesi dahada arttırılarak çok daha hızlı sonuçların elde edilebileceği kanaatindeyim.

Daha detaylı testler için referanslar kısımındaki linkleri takip edebilirsiniz.

REFEREANSLAR

http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52plsql-1709862.html

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:455207300346812151

http://www.orafaq.com/node/2450

http://stackoverflow.com/questions/19151288/update-vs-merge-in-oracle-for-a-list-of-record-not-for-bulk-record-set-performa

 

Advertisements

About ... from Emrah METE

Bilgisayar Mühendisi
This entry was posted in Oracle, Uncategorized and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

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 / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s