آیا تا به حال برایتان پیش آمده که یک کوئری در دیتابیس اوراکل روزها با سرعت عالی اجرا شود و ناگهان بدون هیچ تغییری در کد، سرعت آن افت کند؟
این کابوس بسیاری از مدیران دیتابیس (DBA) است که معمولاً به دلیل تغییر در پلن اجرایی توسط Optimizer رخ میدهد. خبر خوب این است که اوراکل با پکیج DBMS_SPM راهکاری قدرتمند برای پایان دادن به این بیثباتیها در اختیار ما قرار داده است.
در این مقاله آموزش Oracle در بخش آموزش پکیج های اوراکلی، به زبان ساده و کاربردی یاد میگیریم چگونه با استفاده از SQL Plan Management (SPM)، پلنهای اجرایی مطلوب را “قفل” کنیم تا عملکرد دیتابیس همیشه در نقطه اوج باقی بماند.
اوراکل در نسخه جدید پایگاه داده خود یعنی Oracle Database 26ai (بهروزرسانی ۲۳.۷) مجموعهای از ابزارهای پیشرفته برای توسعهدهندگان PL/SQL ارائه کرده است.
یکی از مهمترین آنها بسته DBMS_DEVELOPER است؛ ابزاری هوشمند که با استفاده از قابلیتهای ۲۶ai تجربه توسعه، اشکالزدایی و تولید کد را متحول میکند.
پیشنهاد می کنم این مقاله زیر رو حتما مطالعه کنی.
در این مقاله شما می خوانید
پکیج SQL Plan Management چیست؟
به زبان ساده، SPM قابلیتی است که به Optimizer اجازه میدهد فقط از پلنهایی استفاده کند که قبلاً عملکرد آنها ثابت شده و توسط شما تایید شدهاند.
اگر اوراکل پلن جدیدی پیدا کند که ممکن است ریسکساز باشد، آن را در حالت “معلق” نگه میدارد تا شما کیفیت آن را بررسی کنید.
۱. شناسایی و ثبت (Capture)
برای شروع، باید به اوراکل بگویید کدام پلنها “خوب” هستند. اگر کوئریتان در حال حاضر در کش حافظه به خوبی اجرا میشود، میتوانید آن را به Baseline تبدیل کنید:
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => 'YOUR_SQL_ID_HERE');
DBMS_OUTPUT.PUT_LINE('plans uploaded: ' || l_plans_loaded);
END;
۲. بررسی و تکامل (Evolution)
دیتابیس در طول زمان پلنهای جدیدی پیشنهاد میدهد.
برای اینکه بفهمید آیا این پلنهای جدید واقعاً سریعتر هستند یا خیر، از پکیج تکامل استفاده میکنیم:
SELECT DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle => 'SYS_SQL_..._HANDLE')
FROM DUAL;
این دستور، پلنهای جدید را با پلنهای قدیمی مقایسه کرده و در صورت بهبود عملکرد، آنها را به لیست پلنهای مورد اعتماد اضافه میکند.
۳. تثبیت نهایی (Fixing)
اگر کوئری حساسی دارید که تحت هیچ شرایطی نباید پلن اجرای آن تغییر کند، آن را “Fixed” کنید.
با این کار، Optimizer مجبور میشود همیشه از همان پلنِ انتخابشده استفاده کند.
چرا استفاده از پکیج DBMS_SPM برای سازمانها حیاتی است؟
- پایداری عملکرد: جلوگیری از تغییر ناگهانی پلنها پس از آپدیت آمار (Statistics) یا تغییر نسخه دیتابیس.
- پیشگیری از Regression: قبل از اینکه یک پلن بد به سیستم آسیب بزند، آن را در محیط تست بررسی میکنید.
- مدیریت آسان: کاهش نیاز به تغییر در کد برنامهنویسی برای بهبود سرعت.
سوالات متداول درباره پکیج DBMS_SPM در اوراکل
این یکی از پرتکرارترین سوالات متخصصان است. SQL Profile ابزاری است که به Optimizer کمک میکند با تصحیح آمارِ ناقص (Cardinality Feedback)، پلن بهتری انتخاب کند (ماهیت توصیهای دارد).
اما SQL Plan Baseline یک “تعهد” است؛ یعنی شما به دیتابیس دستور میدهید که دقیقاً از همان پلنِ تایید شده استفاده کند و به هیچ وجه پلن دیگری (حتی اگر اوراکل فکر کند بهتر است) را بدون تایید شما جایگزین نکند.
به عبارت دیگر، Profile برای “بهبودِ هوشمندی” است و Baseline برای “تضمینِ ثبات”.
خیر، تاثیر منفی محسوسی بر عملکرد ندارد.
وقتی این پارامتر را TRUE میکنید، اوراکل به طور خودکار فقط ساختار پلنها (نه خودِ پلن کامل) را در SQL Management Base ثبت میکند. این کار به شما اجازه میدهد در آینده، پلنهای تاریخی را برای مقایسه و عیبیابی در اختیار داشته باشید.
این قابلیت، یک سپر دفاعی است که امنیتِ اجرای کوئریهای شما را بدون هزینه اضافی تامین میکند.
سادهترین راه، بررسی ستون LAST_EXECUTED و PLAN_HASH_VALUE در نمای V$SQL است. اگر ناگهان PLAN_HASH_VALUE تغییر کرد و زمان پاسخدهی (Elapsed Time) افزایش یافت، یعنی دچار “Plan Regression” شدهاید.
در این مرحله، با استفاده از DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE میتوانید پلنهای قبلی و جدید را مقایسه کنید تا متوجه شوید کدامیک کارایی بهتری دارد و بر اساس آن، پلن بهتر را Accepted کنید.
بله! این یکی از حرفهایترین روشها برای جلوگیری از بروز مشکل بعد از تغییرات است. شما میتوانید با استفاده از پکیج DBMS_SPM.PACK_STGTAB_BASELINE پلنهای تستشده در محیط Dev را بستهبندی کنید، آن را به دیتابیس Production منتقل کنید و سپس با DBMS_SPM.UNPACK_STGTAB_BASELINE آن را فعال کنید.
این یعنی شما میتوانید قبل از اینکه تغییرات اصلی را در دیتابیسِ اصلی اعمال کنید، از عملکردِ پلنِ بهینه در محیط واقعی اطمینان حاصل کنید.
جمعبندی
- همه کوئریها نیاز به Baseline ندارند. فقط روی کوئریهای پرمصرف (Top SQLs) تمرکز کنید.
- همیشه جدول
DBA_SQL_PLAN_BASELINESرا چک کنید تا ببینید کدام پلنها فعال (Accepted) هستند. - فراموش نکنید که پلنهای منقضی شده را با
DBMS_SPM.DROP_SQL_PLAN_BASELINEحذف کنید تا حجم سیستمتان بیهوده اشغال نشود.
سؤالی درباره این مقاله داری؟
اگر نکتهای در این مقاله برات مبهم بود یا خواستی بیشتر بدونی، همین حالا برام بنویس تا دقیق و صمیمی پاسخت رو بدم — مثل یه گفتوگوی واقعی 💬
برو به صفحه پرسش و پاسخ
دیدگاهتان را بنویسید