Bulk Binds در اوراکل plsql

مقدمه :

موقعی که از دستورات plsql استفاده می کنیم و آنها را به سمت پایگاه داده برای اجرا ارسال می کنیم، پایگاه داده اوراکل آنها را ابتدا در موتور مربوط plsql بررسی و اجرا می کند و قسمت های مربوط به دستورات sql را به موتور sql ارسال می کند تا در آنجا اجرا شوند ودر نهایت فعالیت خود را پایان می دهد.

یکی از مشکلات کارایی، حرکت زیاد بین این دو موتور برای اجرای یک دستور plsql می باشد که اصطلاحا به این حرکت Contex Switch می گویند. هر چقدر بتوان تعداد Contex Switch ها را کم کرد، قطعا دستور plsql با کارایی بهتری اجرا خواهد شد.

استفاده از Bulk Binds ها در راستای کم کردن Contex Switch ها می باشد که در ادامه آنها را بررسی خواهیم کرد.

BULK COLLECT

دستور Bulk Collect Into، خروجی یک کوئری را به Collection متصل می کند. برای اجرا ابتدا جدول زیر را می سازیم.

CREATE TABLE bulk_collect_test AS

SELECT owner,

object_name,

object_id

FROM all_objects;

در ادامه زمان اجرای کوئری با و بدون Bulk Collect Into را در یک ساختار plsql بررسی می کنیم.

SET SERVEROUTPUT ON

DECLARE

TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;

l_tab t_bulk_collect_test_tab := t_bulk_collect_test_tab();

l_start NUMBER;

BEGIN

— Time a regular population.

l_start := DBMS_UTILITY.get_time;

FOR cur_rec IN (SELECT *

FROM bulk_collect_test)

LOOP

l_tab.extend;

l_tab(l_tab.last) := cur_rec;

END LOOP;

DBMS_OUTPUT.put_line(‘Regular (‘ || l_tab.count || ‘ rows): ‘ ||

(DBMS_UTILITY.get_time – l_start));

— Time bulk population.

l_start := DBMS_UTILITY.get_time;

SELECT *

BULK COLLECT INTO l_tab

FROM bulk_collect_test;

DBMS_OUTPUT.put_line(‘Bulk (‘ || l_tab.count || ‘ rows): ‘ ||

(DBMS_UTILITY.get_time – l_start));

END;

Regular (42578 rows): 66

Bulk (42578 rows): 4

PL/SQL procedure successfully completed.

SQL>

همانطور که مشاهده می کنید bulk collect into با استفاده از collection ها، کارایی را بهبود بخشیده.

لازم به توضیح می باشد bulk collect برای اجرا احتیاج به collection ها دارد ( در این مقاله می توانید با collection ها بیشتر آشنا شوید) و از نظر ساختار باید مشابه هم باشند.

همانطور که می دانید collection ها در حافظه موقت ایجاد و نگهداری می شوند، پس ایجاد یک collection حجیم با دستور bulk collect می تواند بسیار مخرب باشد به همین دلیل می توان با استفاده از LIMIT حجم واکشی داده ها و قراردادن آن در collection را کنترل کرد. به مثال زیر توجه کنید.

SET SERVEROUTPUT ON

DECLARE

TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;

l_tab t_bulk_collect_test_tab;

CURSOR c_data IS

SELECT *

FROM bulk_collect_test;

BEGIN

OPEN c_data;

LOOP

FETCH c_data

BULK COLLECT INTO l_tab LIMIT 10000;

EXIT WHEN l_tab.count = 0;

— Process contents of collection here.

DBMS_OUTPUT.put_line(l_tab.count || ‘ rows’);

END LOOP;

CLOSE c_data;

END;

10000 rows

2578 rows

PL/SQL procedure successfully completed.

SQL>

از نسخه 10gعبارت for loop معادل bulk collect به همراه limit 100 در نظر گرفته می شود . ( کامپایلر خودکار این کار را انجام می دهد)

SET SERVEROUTPUT ON

DECLARE

TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;

l_tab t_bulk_collect_test_tab;

CURSOR c_data IS

SELECT *

FROM bulk_collect_test;

l_start NUMBER;

BEGIN

— Time a regular cursor for loop.

l_start := DBMS_UTILITY.get_time;

FOR cur_rec IN (SELECT *

FROM bulk_collect_test)

LOOP

NULL;

END LOOP;

DBMS_OUTPUT.put_line(‘Regular : ‘ ||

(DBMS_UTILITY.get_time – l_start));

— Time bulk with LIMIT 10.

l_start := DBMS_UTILITY.get_time;

OPEN c_data;

LOOP

FETCH c_data

BULK COLLECT INTO l_tab LIMIT 10;

EXIT WHEN l_tab.count = 0;

END LOOP;

CLOSE c_data;

DBMS_OUTPUT.put_line(‘LIMIT 10 : ‘ ||

(DBMS_UTILITY.get_time – l_start));

— Time bulk with LIMIT 100.

l_start := DBMS_UTILITY.get_time;

OPEN c_data;

LOOP

FETCH c_data

BULK COLLECT INTO l_tab LIMIT 100;

EXIT WHEN l_tab.count = 0;

END LOOP;

CLOSE c_data;

DBMS_OUTPUT.put_line(‘LIMIT 100: ‘ ||

(DBMS_UTILITY.get_time – l_start));

— Time bulk with LIMIT 1000.

l_start := DBMS_UTILITY.get_time;

OPEN c_data;

LOOP

FETCH c_data

BULK COLLECT INTO l_tab LIMIT 1000;

EXIT WHEN l_tab.count = 0;

END LOOP;

CLOSE c_data;

DBMS_OUTPUT.put_line(‘LIMIT 1000: ‘ ||

(DBMS_UTILITY.get_time – l_start));

END;

Regular : 18

LIMIT 10 : 80

LIMIT 100: 15

LIMIT 1000: 10

PL/SQL procedure successfully completed.

SQL>

FORALL

از دستور forall برای اعمال دستورات DML بر روی collectionها استفاده می شود که به این ترتیب contex Switch کاهش می یابد. برای انجام آزمایش ابتدا جدول زیر را می سازیم.

CREATE TABLE forall_test (

id NUMBER(10),

code VARCHAR2(10),

description VARCHAR2(50));

ALTER TABLE forall_test ADD (

CONSTRAINT forall_test_pk PRIMARY KEY (id));

ALTER TABLE forall_test ADD (

CONSTRAINT forall_test_uk UNIQUE (code));

حال دستور زیر را که مقاسه insert با و بدون forall می باشد را بررسی می کنیم.

SET SERVEROUTPUT ON

DECLARE

TYPE t_forall_test_tab IS TABLE OF forall_test%ROWTYPE;

l_tab t_forall_test_tab := t_forall_test_tab();

l_start NUMBER;

l_size NUMBER := 10000;

BEGIN

— Populate collection.

FOR i IN 1 .. l_size LOOP

l_tab.extend;

l_tab(l_tab.last).id := i;

l_tab(l_tab.last).code := TO_CHAR(i);

l_tab(l_tab.last).description := ‘Description: ‘ || TO_CHAR(i);

END LOOP;

EXECUTE IMMEDIATE ‘TRUNCATE TABLE forall_test’;

— Time regular inserts.

l_start := DBMS_UTILITY.get_time;

FOR i IN l_tab.first .. l_tab.last LOOP

INSERT INTO forall_test (id, code, description)

VALUES (l_tab(i).id, l_tab(i).code, l_tab(i).description);

END LOOP;

DBMS_OUTPUT.put_line(‘Normal Inserts: ‘ ||

(DBMS_UTILITY.get_time – l_start));

EXECUTE IMMEDIATE ‘TRUNCATE TABLE forall_test’;

— Time bulk inserts.

l_start := DBMS_UTILITY.get_time;

FORALL i IN l_tab.first .. l_tab.last

INSERT INTO forall_test VALUES l_tab(i);

DBMS_OUTPUT.put_line(‘Bulk Inserts : ‘ ||

(DBMS_UTILITY.get_time – l_start));

COMMIT;

END;

Normal Inserts: 305

Bulk Inserts : 14

PL/SQL procedure successfully completed.

SQL>

همانطور که مشاهده می کنید با کاهش contex switch، کارایی بهبود می یابد.

به مثال زیر نیز توجه کنید.

SET SERVEROUTPUT ON

DECLARE

TYPE t_id_tab IS TABLE OF forall_test.id%TYPE;

TYPE t_forall_test_tab IS TABLE OF forall_test%ROWTYPE;

l_id_tab t_id_tab := t_id_tab();

l_tab t_forall_test_tab := t_forall_test_tab ();

l_start NUMBER;

l_size NUMBER := 10000;

BEGIN

— Populate collections.

FOR i IN 1 .. l_size LOOP

l_id_tab.extend;

l_tab.extend;

l_id_tab(l_id_tab.last) := i;

l_tab(l_tab.last).id := i;

l_tab(l_tab.last).code := TO_CHAR(i);

l_tab(l_tab.last).description := ‘Description: ‘ || TO_CHAR(i);

END LOOP;

— Time regular updates.

l_start := DBMS_UTILITY.get_time;

FOR i IN l_tab.first .. l_tab.last LOOP

UPDATE forall_test

SET ROW = l_tab(i)

WHERE id = l_tab(i).id;

END LOOP;

DBMS_OUTPUT.put_line(‘Normal Updates : ‘ ||

(DBMS_UTILITY.get_time – l_start));

l_start := DBMS_UTILITY.get_time;

— Time bulk updates.

FORALL i IN l_tab.first .. l_tab.last

UPDATE forall_test

SET ROW = l_tab(i)

WHERE id = l_id_tab(i);

DBMS_OUTPUT.put_line(‘Bulk Updates : ‘ ||

(DBMS_UTILITY.get_time – l_start));

COMMIT;

END;

Normal Updates : 235

Bulk Updates : 20

PL/SQL procedure successfully completed.

SQL>

SQL%BULK_ROWCOUNT

SQL%BULK_ROWCOUNT ویژگی که تعداد رکوردهایی که در هر مرتبه دچار تغییر می شوند را در خود نگهداری می کند.

به مثال زیر توجه کنید.

CREATE TABLE bulk_rowcount_test AS

SELECT *

FROM all_users;

SET SERVEROUTPUT ON

DECLARE

TYPE t_array_tab IS TABLE OF VARCHAR2(30);

l_array t_array_tab := t_array_tab(‘SCOTT’, ‘SYS’,

‘SYSTEM’, ‘DBSNMP’, ‘BANANA’);

BEGIN

— Perform bulk delete operation.

FORALL i IN l_array.first .. l_array.last

DELETE FROM bulk_rowcount_test

WHERE username = l_array(i);

— Report affected rows.

FOR i IN l_array.first .. l_array.last LOOP

DBMS_OUTPUT.put_line(‘Element: ‘ || RPAD(l_array(i), 15, ‘ ‘) ||

‘ Rows affected: ‘ || SQL%BULK_ROWCOUNT(i));

END LOOP;

END;

Element: SCOTT Rows affected: 1

Element: SYS Rows affected: 1

Element: SYSTEM Rows affected: 1

Element: DBSNMP Rows affected: 1

Element: BANANA Rows affected: 0

PL/SQL procedure successfully completed.

SQL>

استفاده از SAVE EXCEPTIONS و SQL%BULK_EXCEPTION

در صورت استفاده از forall، هنگام ایجاد خطا در هریک از دستورات DML، اگر handler برای بر خورد با خطا وجود نداشته باشد همه عملیات rollback خواهد شد و اگر handler وجود داشته، عملیات تا قبل از بروز خطا، حفظ خواهد شد و اما با وقوع خطا، از ادامه عملیات جلوگیری خواهد شد که هر دو سناریو مناسب نمی باشند.

می توان از دستور SAVE EXCEPTIONS استفاده کرد که در مواجه با خطا آن را نگهداری کند و عملیات ادامه یابد و بعداز اتمام عملیات، با SQL%BULK_EXCEPTION به خطاهای رخ داده دسترسی پیدا کرد و تصمیم گیری کرد. این مورد با اجرای یک مثال کاملا شفاف خواهد شد. برای اجرا مثال ابتدا جدول زیر را خواهیم ساخت .

CREATE TABLE exception_test (

id NUMBER(10) NOT NULL

);

در ادامه کد زیر را اجرا خواهیم کرد. در این مثال سعی می شود در هنگام ورود 100 رکورد در این جدول 2 رکورد که مقدار آنها null می باشد را وارد این جدول کرد که با توجه به محدودیتی که برای جدول در نظر گرفته شده با توقع ایجاد خطا را داشته باشیم

SET SERVEROUTPUT ON

DECLARE

TYPE t_tab IS TABLE OF exception_test%ROWTYPE;

l_tab t_tab := t_tab();

l_error_count NUMBER;

ex_dml_errors EXCEPTION;

PRAGMA EXCEPTION_INIT(ex_dml_errors, -24381);

BEGIN

— Fill the collection.

FOR i IN 1 .. 100 LOOP

l_tab.extend;

l_tab(l_tab.last).id := i;

END LOOP;

— Cause a failure.

l_tab(50).id := NULL;

l_tab(51).id := NULL;

EXECUTE IMMEDIATE ‘TRUNCATE TABLE exception_test’;

— Perform a bulk operation.

BEGIN

FORALL i IN l_tab.first .. l_tab.last SAVE EXCEPTIONS

INSERT INTO exception_test

VALUES l_tab(i);

EXCEPTION

WHEN ex_dml_errors THEN

l_error_count := SQL%BULK_EXCEPTIONS.count;

DBMS_OUTPUT.put_line(‘Number of failures: ‘ || l_error_count);

FOR i IN 1 .. l_error_count LOOP

DBMS_OUTPUT.put_line(‘Error: ‘ || i ||

‘ Array Index: ‘ || SQL%BULK_EXCEPTIONS(i).error_index ||

‘ Message: ‘ || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));

END LOOP;

END;

Number of failures: 2

Error: 1 Array Index: 50 Message: ORA-01400: cannot insert NULL into ()

Error: 2 Array Index: 51 Message: ORA-01400: cannot insert NULL into ()

PL/SQL procedure successfully completed.

SQL>

SELECT COUNT(*)

FROM exception_test;

COUNT(*)

———-

1 row selected.

SQL>

عضویت در خبرنامه

تماس با ما

راهکارها

تماس با ما

مقالات مرتبط:

عضویت در خبرنامه

تماس با ما

راهکارها

تماس با ما