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

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

Cardinality Misestimate: دشمن پنهان Performance در Oracle

Cardinality Misestimate در Oracle

تا حالا شده یه کوئری ساده توی محیط Oracle چند ثانیه طول بکشه، ولی همون کوئری، با یه تغییر جزئی، در کمتر از یک ثانیه اجرا بشه؟
اگر جوابت مثبته، احتمالاً درگیر یکی از مهم‌ترین و البته پنهان‌ترین مشکلات Performance در Oracle شدی:

📌 Cardinality Misestimate – تخمین اشتباه تعداد ردیف‌ها در Plan اجرایی.

خیلی وقت‌ها ما دنبال Bottleneckهای واضحی می‌گردیم: ایندکس نیست؟ شاید مشکل IO داریم؟ ولی واقعیت اینه که اگر Optimizer نتونه تعداد واقعی ردیف‌هایی که از یه مرحله کوئری قراره برگرده رو درست تخمین بزنه، کل تصمیم‌گیری‌هاش برای اجرای اون کوئری ممکنه اشتباه باشه. و نتیجه؟

یه کوئری به ظاهر ساده، ممکنه تبدیل بشه به یک بلای Performance.

در این مقاله آموزش اوراکل در بخش آموزش بهینه سازی کوئری SQL Tuning در مورد این موضوع جذاب صحبت می کنیم.

اگر کوئری‌هات در Oracle کند اجرا می‌شن و چندتا جدول رو JOIN کردی ، این مقاله دقیقاً برای توئه. پیشنهاد می شود نوشته زیر را مطالعه کنید:

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

Cardinality دقیقاً یعنی چی؟

در ساده‌ترین تعریف:

Cardinality یعنی تعداد ردیف‌هایی که Optimizer انتظار داره از یک عملیات خاص مثل فیلتر، join، یا اسکن برگرده.

مثال:

				
					SELECT * FROM customers WHERE country = 'IRAN';

اگر Optimizer فکر کنه فقط ۱۰۰ ردیف از ۱۰ میلیون ردیف قراره برگرده، مسیر اجرایی (Execution Plan) متفاوت خواهد بود نسبت به حالتی که فکر کنه یک میلیون ردیف فیلتر می‌شن.

اینجاست که دقت تخمین، حیاتی میشه.

چرا تخمین Cardinality اشتباه اتفاق می‌افته؟ (دلایل Misestimate)

🔸 ۱. آمار ناقص یا قدیمی روی جداول

اگر آمار جدول‌ها (DBMS_STATS) به‌روز نباشن، Optimizer براساس اطلاعات ناقص تصمیم‌گیری می‌کنه.

🔸 ۲. نبود یا ضعف در هیستوگرام (Histogram)

برای ستون‌هایی با توزیع داده نامتوازن (Skewed)، Oracle نیاز به هیستوگرام داره. اگر نباشه، فرض می‌کنه توزیع یکنواخت داریم.

هیستوگرام (Histogram) یعنی چی؟

در Oracle، هیستوگرام یک ساختار آماریه که نحوه توزیع مقادیر داخل یک ستون رو به Optimizer نشون می‌ده.

 

Skewed Distribution یعنی چی؟

 

Skewed (اسکیو) یعنی «نامتوازن» یا «غیر یکنواخت».
در دیتابیس یعنی بعضی مقادیر توی یه ستون خیلی زیادن و بعضی خیلی کم.

 

🔸 ۳. Bind Variables

وقتی به جای مقادیر ثابت از bind variable استفاده می‌کنی (WHERE city = :cityName)

Optimizer نمی‌دونه مقدار واقعی چیه، پس برآوردش مبهم می‌شه.

🔸 ۴. توابع روی ستون‌ها در WHERE

مثلاً:

				
					WHERE TO_CHAR(birth_date, 'YYYY') = '1990'

اینجا Optimizer نمی‌تونه از ایندکس استفاده کنه و کاردینالیتی هم دقیق تخمین زده نمی‌شه.

🔸 ۵. همبستگی بین جداول در Join

وقتی شرط‌های join به‌صورت غیرمستقیم با هم مرتبط باشن، کار برای تخمین سخت‌تر می‌شه.

تاثیرات منفی Cardinality Misestimate روی Performance

این مشکل ممکنه به تنهایی باعث بشه یه کوئری که باید در یک ثانیه اجرا شه، چند دقیقه طول بکشه.

👇 موارد رایج:

  • Join Order اشتباه: به‌جای Join منطقی، Nested Loop یا Merge Join انتخاب میشه که برای حجم بالا مناسب نیست.
  • استفاده نادرست از ایندکس‌ها یا Full Table Scan
  • اختلال در استفاده از parallel execution
  • اختصاص حافظه اشتباه (PGA/Temp) به Hash Join یا Sort

چطور Misestimate رو تشخیص بدیم؟

📊 با استفاده از Plan اجرایی:

از این دو مرحله استفاده کن:

				
					SELECT /*+ gather_plan_statistics */ ...

و سپس:

				
					SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL,
'ALLSTATS LAST'));

مقایسه کن:

  • E-Rows: تخمین Optimizer
  • A-Rows: تعداد واقعی ردیف‌هایی که برگشتن

اگر اختلاف زیادی دارن، یعنی با Misestimate طرفی.

راهکارهای رفع Misestimate

🔸 ۱. جمع‌آوری دقیق Statistics

				
					BEGIN
  DBMS_STATS.GATHER_TABLE_STATS('MY_SCHEMA', 
  'MY_TABLE', cascade => TRUE);
END;

🔸 ۲. ساخت هیستوگرام برای ستون‌های کلیدی

				
					METHOD_OPT => 'FOR COLUMNS SIZE 254 col_name'

یا:

				
					METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO'

🔸 ۳. استفاده از Literal values در کوئری‌های حیاتی

اگر مشکل از bind variableهاست، برای کوئری‌های حساس از literal استفاده کن یا Adaptive Cursor Sharing رو فعال کن.

				
					ALTER SESSION SET cursor_sharing = EXACT;

🔸 ۴. بازنویسی کوئری

از توابع روی ستون پرهیز کن.
مثلاً:

❌ اشتباه:

				
					WHERE TO_CHAR(birth_date, 'YYYY') = '1990'

✅ درست:

				
					WHERE birth_date BETWEEN TO_DATE('1990-01-01') AND TO_DATE('1990-12-31')

🔸 ۵. استفاده از SQL Plan Directive (ویژه Oracle 12c به بالا)

Oracle از اجرای‌های قبلی یاد می‌گیره و به کمک SQL Plan Directive می‌تونه برای اجرای بعدی کاردینالیتی رو بهتر تخمین بزنه.

سناریوی واقعی: Misestimate در Join

فرض کن:

				
					SELECT * 
FROM employees e 
JOIN salaries s ON e.id = s.emp_id 
WHERE s.status = 'BONUS';

اگر فقط ۰.۵٪ ردیف‌ها ‘status = ‘BONUS باشه ولی Optimizer فکر کنه ۵۰٪ هست، ممکنه Nested Loop رو انتخاب کنه و نتیجه بشه عملکرد ضعیف.

سوالات متداول درباره Cardinality Misestimate در Oracle

چند دلیل محتمل هست:

  • توزیع داده‌ها skewed هست ولی هیستوگرام مناسبی روی ستون ساخته نشده.
  • Optimizer به‌جای مقدار واقعی، با bind variable مواجهه (مثل :value) و مقدار رو نمی‌دونه.
  • Query شامل توابع یا expressions روی ستون‌هاست (مثل UPPER(col)).
    حتی با آمار به‌روز، بدون این ملاحظات تخمین ممکنه اشتباه باشه.

زمانی که ستون مورد استفاده در کوئری، توزیع نابرابر (Skewed Distribution) داره.
مثلاً اگر در یک ستون ۹۰٪ داده‌ها مقدار ACTIVE دارن و فقط ۱۰٪ INACTIVE، Oracle برای بهینه‌سازی دقیق نیاز داره بدونه این تفاوت وجود داره.

هیستوگرام این تفاوت رو به Optimizer اطلاع می‌ده.

نه لزوماً.
Bind variable برای جلوگیری از SQL Injection، صرفه‌جویی در حافظه (Shared Pool) و افزایش Reusability بسیار مفید هستند.
اما در کوئری‌هایی که حساس به مقدار پارامتر هستن (مثلاً در ستون‌های skewed)، ممکنه باعث Cardinality Misestimate بشن. در این موارد:

  • از Adaptive Cursor Sharing در Oracle 11g به بعد استفاده کن.
  • یا برای کوئری‌های حیاتی از literal استفاده کن.

با استفاده از ترکیب GATHER_PLAN_STATISTICS و DBMS_XPLAN.DISPLAY_CURSOR می‌تونی execution plan رو ببینی و ستون‌های زیر رو بررسی کنی:

  • E-Rows (Estimated Rows): تعداد ردیف‌های تخمینی
  • A-Rows (Actual Rows): تعداد واقعی ردیف‌هایی که در زمان اجرا برگشتن
    اگر اختلاف فاحشی بین این دو مقدار وجود داشته باشه (مثلاً ۱۰۰۰ در مقابل ۵)، به احتمال زیاد Optimizer دچار Misestimate شده.

📦 جمع‌بندی

Cardinality Misestimate مشکلیه که به‌راحتی از دید پنهان می‌مونه. ممکنه همه‌چیز درست به‌نظر بیاد، ولی کوئری کند اجرا شه و هر کسی مقصر باشه جز خود Optimizer که تخمین اشتباه زده.

👇 اگر Performance برات مهمه، همیشه این نکات رو چک کن:

  • آمار جداول رو به‌روز نگه دار
  • برای ستون‌های skewed هیستوگرام بساز
  • کوئری‌هات رو طوری بنویس که Optimizer بتونه پیش‌بینی دقیق انجام بده
  • از ابزارهای Oracle برای بررسی A-Rows / E-Rows استفاده کن

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

سؤالی درباره این مقاله داری؟

اگر نکته‌ای در این مقاله برات مبهم بود یا خواستی بیشتر بدونی، همین حالا برام بنویس تا دقیق و صمیمی پاسخت رو بدم — مثل یه گفت‌وگوی واقعی 💬

برو به صفحه پرسش و پاسخ

میثم راد

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

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

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