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 ASSELECT owner,object_name,object_idFROM all_objects;در ادامه زمان اجرای کوئری با و بدون Bulk Collect Into را در یک ساختار plsql بررسی می کنیم.SET SERVEROUTPUT ONDECLARETYPE 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)LOOPl_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_tabFROM bulk_collect_test;DBMS_OUTPUT.put_line('Bulk (' || l_tab.count || ' rows): ' ||(DBMS_UTILITY.get_time - l_start));END;/Regular (42578 rows): 66Bulk (42578 rows): 4PL/SQL procedure successfully completed.SQL>همانطور که مشاهده می کنید bulk collect into با استفاده از collection ها، کارایی را بهبود بخشیده.لازم به توضیح می باشد bulk collect برای اجرا احتیاج به collection ها دارد ( در این مقاله می توانید با collectionها بیشتر آشنا شوید) و از نظر ساختار باید مشابه هم باشند.همانطور که می دانید collection ها در حافظه موقت ایجاد و نگهداری می شوند، پس ایجاد یک collection حجیم با دستور bulk collect می تواند بسیار مخرب باشد به همین دلیل می توان با استفاده از LIMIT حجم واکشی داده ها و قراردادن آن در collection را کنترل کرد. به مثال زیر توجه کنید.SET SERVEROUTPUT ONDECLARETYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;l_tab t_bulk_collect_test_tab;CURSOR c_data ISSELECT *FROM bulk_collect_test;BEGINOPEN c_data;LOOPFETCH c_dataBULK 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 rows10000 rows10000 rows10000 rows2578 rowsPL/SQL procedure successfully completed.SQL>از نسخه 10gعبارت for loop معادل bulk collect به همراه limit 100 در نظر گرفته می شود . ( کامپایلر خودکار این کار را انجام می دهد)SET SERVEROUTPUT ONDECLARETYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;l_tab t_bulk_collect_test_tab;CURSOR c_data ISSELECT *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)LOOPNULL;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;LOOPFETCH c_dataBULK 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;LOOPFETCH c_dataBULK 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;LOOPFETCH c_dataBULK 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 : 18LIMIT 10 : 80LIMIT 100: 15LIMIT 1000: 10PL/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 ONDECLARETYPE 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 LOOPl_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 LOOPINSERT 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.lastINSERT INTO forall_test VALUES l_tab(i);DBMS_OUTPUT.put_line('Bulk Inserts : ' ||(DBMS_UTILITY.get_time - l_start));COMMIT;END;/Normal Inserts: 305Bulk Inserts : 14PL/SQL procedure successfully completed.SQL>همانطور که مشاهده می کنید با کاهش contex switch، کارایی بهبود می یابد.به مثال زیر نیز توجه کنید.SET SERVEROUTPUT ONDECLARETYPE 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 LOOPl_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 LOOPUPDATE forall_testSET 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.lastUPDATE forall_testSET 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 : 235Bulk Updates : 20PL/SQL procedure successfully completed.SQL>SQL%BULK_ROWCOUNTSQL%BULK_ROWCOUNT ویژگی که تعداد رکوردهایی که در هر مرتبه دچار تغییر می شوند را در خود نگهداری می کند.به مثال زیر توجه کنید.CREATE TABLE bulk_rowcount_test ASSELECT *FROM all_users;SET SERVEROUTPUT ONDECLARETYPE 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.lastDELETE FROM bulk_rowcount_testWHERE username = l_array(i);-- Report affected rows.FOR i IN l_array.first .. l_array.last LOOPDBMS_OUTPUT.put_line('Element: ' || RPAD(l_array(i), 15, ' ') ||' Rows affected: ' || SQL%BULK_ROWCOUNT(i));END LOOP;END;/Element: SCOTT Rows affected: 1Element: SYS Rows affected: 1Element: SYSTEM Rows affected: 1Element: DBSNMP Rows affected: 1Element: BANANA Rows affected: 0PL/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 ONDECLARETYPE 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 LOOPl_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.BEGINFORALL i IN l_tab.first .. l_tab.last SAVE EXCEPTIONSINSERT INTO exception_testVALUES l_tab(i);EXCEPTIONWHEN ex_dml_errors THENl_error_count := SQL%BULK_EXCEPTIONS.count;DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);FOR i IN 1 .. l_error_count LOOPDBMS_OUTPUT.put_line('Error: ' || i ||' Array Index: ' || SQL%BULK_EXCEPTIONS(i).error_index ||' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));END LOOP;END;END;/Number of failures: 2Error: 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(*)----------981 row selected.SQL>برای مطالعه مقالات بیشتر در حوزه پایگاه داده اوراکل به سایت آرتاراد مراجعه فرمایید.