ESC را فشار دهید تا بسته شود

زمیوس آموزش، یادگیری و سرگرمی

رازهای پنهان Optimizer Oracle که هیچکس به شما نمی‌گوید!

رازهای پنهان Optimizer Oracle که هیچکس به شما نمی‌گوید

اگر فکر می‌کنید اوراکل فقط داده‌ها را ذخیره و واکشی می‌کند، سخت در اشتباهید.

در دل این غول دیتابیس، مغزی تمام‌عیار به نام Oracle Optimizer نشسته که تصمیم می‌گیرد چطور دستور SQL شما اجرا شود.

اما نکته اینجاست: این مغز قانون‌های پنهانی دارد که حتی خیلی از DBAها هم از آن خبر ندارند!

در این مقاله آموزش Oracle در بخش بهینه سازی کوئری (SQL Tuning) می خواهیم در مورد این غول بی شاخ و دم در اوراکل صحبت کنیم. 

اگر با پایگاه داده Oracle کار کرده باشی، حتماً با این صحنه مواجه شدی:
یه کوئری ساده می‌نویسی، ولی اجراش از باز شدن درب قطارهای بین‌شهری هم بیشتر طول می‌کشه! 😅

پیشنهاد می شود نوشته زیر را مطالعه کنید:

در این نوشته شما می خوانید

Optimizer چیست و چرا مهم است؟

Oracle Optimizer همان چیزی است که بین کد SQL شما و داده‌های موجود در جدول‌ها می‌ایستد و تصمیم می‌گیرد سریع‌ترین و کم‌هزینه‌ترین مسیر را انتخاب کند.

بیایید واضح بگوییم: بهترین Query بدون یک پلن خوب، فقط یک تکه کد زیباست که کند اجرا می‌شود.

مدل‌های مختلف Optimizer Oracle

  1. Rule-Based Optimizer (RBO) – نسل منقرض‌شده، قبل از Oracle 10g
  2. Cost-Based Optimizer (CBO) – تحلیل هزینه و انتخاب بهترین پلن
  3. Adaptive Query Optimizer – هوشمند و واکنش‌گرا، از ۱۲c به بعد

رازهای کمتر گفته‌شده Optimizer Oracle

۱. Optimizer فقط به SQL نگاه نمی‌کند، به داده هم نگاه می‌کند

یک تغییر کوچک در حجم یا توزیع داده می‌تواند کل Execution Plan را عوض کند، حتی اگر کد SQL شما تغییری نکرده باشد.

				
					CREATE TABLE sales AS
SELECT rownum id, MOD(rownum,
۱۰) region_id, DBMS_RANDOM.value(1,1000) amount
FROM dual CONNECT BY LEVEL <= 100000;

CREATE INDEX idx_sales_region ON sales(region_id);

BEGIN
  DBMS_STATS.gather_table_stats(ownname => USER, tabname => 'SALES');
END;
/

SELECT *
FROM sales
WHERE region_id = 1;

۲. Cardinality: شاه‌کلید مخفی

Cardinality همان پیش‌بینی Optimizer از تعداد ردیف‌های خروجی است.

اگر اشتباه محاسبه شود، پلن بهینه نخواهد بود.

				
					BEGIN
  DBMS_STATS.gather_table_stats(ownname => USER, tabname => 'SALES',
    method_opt => 'FOR COLUMNS region_id SIZE 10');
END;
/

۳. Hints معجزه دائمی نیستند

Hints شبیه این هستند که به راننده تاکسی بگویید مسیر خاصی را بروید.

امروز شاید بهترین باشد، ولی فردا با تغییر ترافیک (داده) شاید بدترین انتخاب باشد.

				
					SELECT /*+ INDEX(sales idx_sales_region) */ *
FROM sales
WHERE region_id = 5;

۴. Bind Peeking و Adaptive Cursor Sharing

با Bind Variable، اوراکل بار اول مقدار را نگاه می‌کند و پلن را براساس آن می‌سازد.

ولی از نسخه ۱۱g به بعد، Adaptive Cursor Sharing برای مقادیر متفاوت پلن متفاوت می‌سازد.

ترفندهای Performance Tuning Optimizer

  • آمار به‌روز بگیرید (DBMS_STATS)
  • برای داده‌های skew از Histogram استفاده کنید
  • پلن‌ها را با DBMS_XPLAN.DISPLAY_CURSOR بررسی کنید
  • Hint را فقط در شرایط خیلی ثابت بدهید
  • Adaptive Optimizer را در نسخه‌های جدید بشناسید

سناریوی واقعی Optimizer Oracle

در یک پروژه گزارش‌گیری روزانه، Query که همیشه در ۵ ثانیه اجرا می‌شد، ناگهان به ۴۰ ثانیه رسید.

مشکل؟

  • جدول ۳ برابر بزرگ‌تر شده بود.
  • Stats دو ماه به‌روز نشده بود.
  • Optimizer به دلیل فقدان Histogram برای ستون فیلتر، تصمیم به Full Table Scan گرفت.

راه‌حل:

				
					BEGIN
  DBMS_STATS.gather_table_stats('REPORT_USER', 'TXN_TABLE',
    method_opt => 'FOR COLUMNS transaction_type SIZE 10');
END;

بعد از این تغییر، پلن به Index Range Scan برگشت و Query زمان به ۶ ثانیه کاهش یافت.

سوالات متداول درباره Optimizer Oracle

Oracle Optimizer بخشی از موتور پایگاه داده است که بهترین روش اجرا برای دستور SQL را با کمترین هزینه انتخاب می‌کند.

با به‌روز کردن Statistics، استفاده از Histogram برای داده‌های skew، و بررسی Execution Plan می‌توان عملکرد Optimizer را بهبود داد.

Cardinality یعنی پیش‌بینی Optimizer از تعداد ردیف‌هایی که یک Query بازخواهد گرداند.

پیش‌بینی دقیق باعث انتخاب پلن بهینه می‌شود.

در Oracle، بهترین روش‌ها شامل به‌روزرسانی منظم Statistics با DBMS_STATS، استفاده صحیح از Indexها، ساخت Histogram برای داده‌های skew و بررسی مداوم پلن‌ها با DBMS_XPLAN.DISPLAY_CURSOR است.

این کار باعث می‌شود Optimizer بهترین مسیر اجرا را انتخاب کند.

جمع‌بندی

Optimizer قلب تصمیم‌گیری اوراکل است. اگر قوانین نانوشته‌اش را یاد بگیرید:

  • زمان اجرای Queryها کوتاه‌تر می‌شود.
  • منابع سیستم بهینه مصرف می‌شود.
  • و می‌توانید حتی بدون تغییر کد برنامه، عملکرد را چند برابر کنید.

📥 اگر سوالی داری در Optimizer Oracle داری، در بخش کامنت‌ها بپرس.

میثم راد

من یه برنامه نویسم که حسابی با دیتابیس اوراکل رفیقم! از اونایی ام که تا چیزی رو کامل نفهمم،ول کن نیستم، یادگرفتن برام مثل بازیه، و نوشتن اینجا کمک می کنه تا چیزایی که یاد گرفتم رو با بقیه به شریک بشم، با هم پیشرفت کنیم.

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *