بروزرسانی Materialized View(MV) به روش Out of Place ، قابلیت جدیدی بود که در اوراکل نسخه 12c ارائه شد، در این روش، در زمان بروزرسانی MV، به جای دستکاری جدول جاری MV، اوراکل جدول جدیدی را ایجاد می کند و حاصل اجرای متن MV را در این جدول درج خواهد کرد بعد از آنکه اطلاعات بروز شده به صورت کامل در جدول جدید درج شد، این جدول با جدول جاری MV جایگزین می شود.

با توجه به آنکه در مورد ویژگی Out Of Place Refresh قبلا مطلبی را ارائه کردیم از تکرار مجدد آن پرهیز کرده و در این مطلب به بررسی یکی مضرات پرفورمنسی این شیوه از بروزرسانی خواهیم پرداخت.

Out Of Place Refresh در کنار مزایایی که دارد ممکن در شرایطی برای دیتابیس سربار پرفورمنسی ایجاد کند. چرا که در این روش از بروزرسانی، با هر بار بروزرسانی MV، جدولی حذف و جدول جدیدی ایجاد خواهد شد و جدول جدید مشخصات مختص به خود را دارد(نظیر object_id و …) و برای دیتابیس یک object جدید محسوب می شود.

ایجاد جدول جدید سبب می شود تا فرمهای پارس شده همه پرس و جوهایی که به این MV رجوع کرده اند، نامعتبر شده و از حافظه خارج شوند و این پرس و جوها برای اجرای مجدد، باید یکبار دیگر پارس شوند.

در سناریوی زیر سعی کردیم تا این مسئله را به شکلی عملی نمایش دهیم. برای نمایش این مسئله، لازم است تا هزاران پرس و جو که به یک MV رجوع کرده اند را اجرا کنیم. این کار را از طریق پروسیجری انجام خواهیم داد:

create or replace procedure prc_select_MV as

  x number;

BEGIN

  FOR i IN 10 .. 13000 LOOP

    BEGIN

      EXECUTE IMMEDIATE ‘SELECT name FROM MV1 WHERE id = ”’ || i || ””

        INTO x;

    EXCEPTION

      WHEN others THEN

        NULL;

    END;

  END LOOP;

END;

/

قصد داریم پس از خالی کردن shared pool، پروسیجر فوق را اجرا کنیم این پروسیجر، حدود 13 هزار دستور select را اجرا خواهد کرد که در متن همه آنها به mv1 اشاره شده است:

SQL> alter system flush shared_pool;

System altered

SQL> exec prc_select_MV;

PL/SQL procedure successfully completed

Executed in 12.375 seconds

SQL> select count(*) from v$sql  where sql_text like ‘%SELECT name FROM MV1 WHERE id %’;

  COUNT(*)

———-

     12992

همانطور که مشاهده می کنید، همه دستورات اجرا شده در shared pool موجود هستند.

بروزرسانی MV1 به شیوه non-out-of-place هم اثر منفی ای در این زمینه ندارد و دستورات اجرا شده کماکان در shared pool باقی خواهند ماند:

SQL> exec DBMS_MVIEW.REFRESH(‘MV1’, method => ‘COMPLETE’);

PL/SQL procedure successfully completed

Executed in 1.087 seconds

SQL> select count(*) from v$sql  where sql_text like ‘%SELECT name FROM MV1 WHERE id %’;

  COUNT(*)

———-

     12992

بنابرین پروسیجر برای اجرای مجدد، نیازی به پارس این 13 هزار دستور ندارد و بنابرین با سرعت بسیار خوبی اجرا خواهد شد:

SQL> exec prc_select_MV;

PL/SQL procedure successfully completed

Executed in 3.409 seconds

SQL> exec DBMS_MVIEW.REFRESH(‘MV1’, method => ‘COMPLETE’);

PL/SQL procedure successfully completed

Executed in 0.137 seconds

SQL> exec prc_select_MV;

PL/SQL procedure successfully completed

Executed in 3.22 seconds

با تغییر روش بروزرسانی به out-of-place خواهیم دید که شرایط تغییر خواهد کرد:

SQL> exec DBMS_MVIEW.REFRESH(‘MV1’, method => ‘COMPLETE’, atomic_refresh => FALSE, out_of_place =>TRUE);

PL/SQL procedure successfully completed

Executed in 4.879 seconds

SQL> select count(*) from v$sql  where sql_text like ‘%SELECT name FROM MV1 WHERE id %’;

  COUNT(*)

———-

         1

SQL> exec prc_select_MV;

PL/SQL procedure successfully completed

Executed in 13.313 seconds

همانطور که می بینید، تغییر روش بروزرسانی، منجر به افزایش زمان اجرای پروسیجر از 3 ثانیه به 13 ثانیه شده است:

SQL> exec DBMS_MVIEW.REFRESH(‘MV1’, method => ‘COMPLETE’, atomic_refresh => FALSE, out_of_place =>TRUE);

PL/SQL procedure successfully completed

Executed in 4.879 seconds

SQL> exec prc_select_MV;

PL/SQL procedure successfully completed

Executed in 12.378 seconds

SQL> exec DBMS_MVIEW.REFRESH(‘MV1’, method => ‘COMPLETE’, atomic_refresh => FALSE, out_of_place =>TRUE);

PL/SQL procedure successfully completed

Executed in 4.743 seconds

SQL> exec prc_select_MV;

PL/SQL procedure successfully completed

Executed in 12.58 seconds

در پایان برای نمایش تفاوت این دو روش بروزرسانی، سعی کردیم در شرایط مشابه، با تکرار چندباره بروزرسانی Materialized View(MV) و اجرای پروسیجر، مقایسه ای را به لحاط پرفورمنسی انجام دهیم.

—atomic(non out of place):

—out of place: