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

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

چگونه Execution Plan را در Oracle 23ai تحلیل کنیم و عوامل کندی را شناسایی کنیم؟

چگونه Execution Plan را در Oracle 23ai تحلیل کنیم

یکی از مهارت‌های حیاتی برای هر DBA یا توسعه‌دهنده Oracle، توانایی درک و تحلیل Execution Plan است.

این طرح در واقع نقشه راهی است که Optimizer تعیین می‌کند تا مشخص شود یک Query چگونه و با چه ترتیبی اجرا شود.

در نسخه جدید Oracle Database 23ai، با قابلیت‌های تازه‌ای مثل Adaptive Plans، AI-Assisted Tuning و نمایش‌های آماری پیشرفته، تحلیل و رفع کندی Queryها بسیار ساده‌تر و دقیق‌تر شده است.

در این مقاله آموزش Oracle در بخش آموزش بهینه سازی کوئری (SQL Tuning) یاد می‌گیریم:

  • چطور Execution Plan را تولید کنیم،
  • هر بخش از Plan را بخوانیم،
  • و با استفاده از آمار واقعی، گلوگاه‌های کندی (Performance Bottlenecks) را شناسایی کنیم.

وقتی یه Query در Oracle 19c کند می‌شه، اولین چیزی که ممکنه به ذهنت برسه اینه که “حتماً دیتام زیاده” یا “سیستم ضعیفه!”. اما واقعیت اینه که کندی همیشه از حجم داده یا سخت‌افزار نیست — معمولاً یه گره نامرئی، یعنی Bottleneck واقعی داره که باید قدم‌به‌قدم پیدا بشه.

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

در این مقاله شما می خوانید

تولید Execution Plan در Oracle 23ai

۱. دستور EXPLAIN PLAN

تولید Execution Plan در Oracle 23ai

				
					EXPLAIN PLAN FOR
SELECT e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE e.salary > ۱۰۰۰۰;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

نتیجه فقط یک Estimated Plan است، یعنی بر اساس آمار فعلی جدول، بدون اجرای واقعی.

۲. استفاده از AUTOTRACE

اگر بخواهیم علاوه بر Plan، آمار واقعی اجرا (Rows، Elapsed Time و غیره) را هم ببینیم:

				
					SET AUTOTRACE ON
SELECT /*+ gather_plan_statistics */ e.emp_name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.dept_id
AND e.salary > ۱۰۰۰۰;

AUTOTRACE خروجی‌ای ترکیبی تولید می‌کند: هم Execution Plan، هم آمار مصرف شده.

۳. مشاهده Plan واقعی با DBMS_XPLAN.DISPLAY_CURSOR

در Oracle 23ai، Optimizer می‌تواند Plan واقعی (همراه با آمار اجرا) را حتی در زمان اجرا نشان دهد:

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

این روش دقیق‌ترین راه مشاهده Plan است چون Actual Rows را هم نشان می‌دهد.

ساختار Execution Plan در اوراکل

هر Plan از چند مرحله تشکیل شده که از پایین‌ترین گره اجرا آغاز می‌شود. ستون‌های کلیدی جدول خروجی:

ستون توضیح
Operation نوع عملیات (مثل اسکن جدول، نوع Join و ...)
Options جزئیات نحوه‌ی اجرای عملیات مثل Full Scan یا Range Scan
Name نام جدول یا ایندکس
Rows تخمین تعداد رکوردهای خروجی از هر مرحله
Cost هزینه محاسبه‌شده توسط Optimizer

نمونه:

ID Operation Name Rows Cost
۰ SELECT STATEMENT – – ۱۲
۱ NESTED LOOPS – ۱۵ ۱۲
۲ TABLE ACCESS FULL EMPLOYEES ۱۵ ۸
۳ INDEX UNIQUE SCAN PK_DEPT ۱ ۱

چگونه Execution Plan را تحلیل کنیم؟

۱. بررسی نوع دسترسی (Access Method)

  • TABLE ACCESS FULL → اسکن کامل جدول؛ پرهزینه است.
  • INDEX RANGE SCAN → استفاده بهینه از ایندکس؛ معمولاً سریع‌تر.
  • NESTED LOOPS، HASH JOIN، MERGE JOIN → هر کدام مزایا و کاربرد خاصی دارند.

۲. بررسی اختلاف بین Estimated Rows و Actual Rows

اگر تفاوت زیاد باشد، نشان از آمار نادرست (Statistics Outdated) دارد.

برای دیدنشان:

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

به ستون‌های E-Rows (تخمینی) و A-Rows (واقعی) دقت کن.

۳. تحلیل مصرف منابع

سه شاخص حیاتی:

  • BUFFER_GETS → خواندن از حافظه.
  • DISK_READS → خواندن از دیسک؛ هرچه بیشتر، یعنی کندتر.
  • ELAPSED_TIME → زمان اجرای واقعی.

تشخیص دلایل کندی Query در اوراکل

علامت دلیل راهکار
TABLE ACCESS FULL نبود ایندکس یا شرط غیربهینه ایجاد ایندکس مناسب یا بازنویسی شرط
HASH JOIN با Cost بالا حافظه ناکافی در PGA افزایش پارامتر pga_aggregate_target
تفاوت زیاد A-Rows و E-Rows آمار قدیمی اجرای DBMS_STATS.GATHER_TABLE_STATS
Nested Loop تکراری Join Ordering نادرست بازنویسی Query یا استفاده از Hint مناسب

مثال کاربردی

Query زیر فرضاً کند اجرا می‌شود:

				
					SELECT /*+ gather_plan_statistics */
       c.customer_name, o.order_id, o.amount
FROM   customers c
JOIN   orders o ON c.customer_id = o.customer_id
WHERE  o.status = 'PENDING'
AND    o.order_date > SYSDATE - 30;

مشاهده‌ی Plan واقعی:

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

نتیجه:

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

راهکار اصلاحی:

				
					BEGIN
  DBMS_STATS.GATHER_TABLE_STATS('APP', 'ORDERS');
END;
/
CREATE INDEX idx_orders_date_status ON orders(order_date, status);

بعد از اجرا مجدد، Plan بهینه می‌شود و Cost تا حدود ۱۰ برابر کاهش می‌یابد.

ابزارهای تحلیلی جدید در Oracle 23ai

ابزار کاربرد
SQL Monitor نمایش بلادرنگ Query فعال
DBMS_XPLAN.DISPLAY_SQL_MONITOR نمایش وضعیت زنده اجرای SQL
AI-Powered SQL Tuning پیشنهاد خودکار ایندکس و Hint
SQL Plan Baselines نگهداری Planهای بهینه و جلوگیری از Regression

نکات طلایی برای بهینه‌سازی عملکرد کوئری در ارواکل

  • همیشه از /*+ GATHER_PLAN_STATISTICS */ برای بررسی دقیق‌تر استفاده کن.
  • آمارها را مرتب با DBMS_STATS به‌روزرسانی کن.
  • Queryهای تکراری را با Bind Variable اجرا کن.
  • پارامترهای PGA و Optimizer Mode را متناسب با حجم کاری تنظیم کن.
  • از SQL Monitor برای Sessionهای سنگین استفاده کن.

سوالات متداول درباره تحلیل Execution Plan در Oracle 23ai

Execution Plan تخمینی (Estimated Plan) فقط نحوه اجرای احتمالی Query را بر اساس آمار فعلی جداول نشان می‌دهد، بدون آن‌که Query واقعاً اجرا شود.

اما Execution Plan واقعی (Actual Plan) در زمان اجرای واقعی Query از طریق DBMS_XPLAN.DISPLAY_CURSOR('SQL_ID', NULL, 'ALLSTATS LAST') تولید می‌شود و شامل A-Rows، Elapsed Time، Cost و IO واقعی است.

در Oracle 23ai توصیه می‌شود همیشه Plan واقعی را بررسی کنی تا تفاوت بین تخمین Optimizer و اجرای واقعی را ببینی.

برای تشخیص کندی Query، باید مراحل Plan را از پایین به بالا بررسی کنی و دنبال مراحل با Cost بالا یا Row زیاد بگردی.

اگر TABLE ACCESS FULL روی جدول بزرگ دیده شود یعنی معمولاً ایندکس مؤثر وجود ندارد، و اگر اختلاف بین E-Rows و A-Rows بالا است، نشان از آمار نادرست دارد.

بهترین ابزار برای تحلیل در ۲۳ai، تابع DBMS_XPLAN.DISPLAY_CURSOR و داشبورد SQL Monitor است که اجرای زنده Query را نشان می‌دهد.

در Oracle، داشتن Execution Plan مشابه لزوماً به‌معنی زمان اجرای برابر نیست.

فاکتورهایی مانند بار سیستم (System Load)، میزان PGA و Cache در لحظه اجرا، و حتی Adaptive Execution باعث تفاوت سرعت می‌شوند.

در نسخه ۲۳ai، موتور Optimizer ممکن است Plan را حین اجرا (Adaptive) اصلاح کند تا سریع‌ترین مسیر را انتخاب کند، بنابراین بررسی آمار واقعی و بخش Execution Statistics همیشه اهمیت دارد.

روش توصیه‌شده اوراکل استفاده از SQL Plan Baseline است.

با Baseline می‌توانی طرح‌های اجرای بهینه را ذخیره و از تغییرات ناخواسته Optimizer در نسخه‌های جدید یا پس از Gather Stat جلوگیری کنی.

همچنین اجرای منظم DBMS_STATS.GATHER_TABLE_STATS باعث دقت بیشتر در Plan می‌شود.

در Oracle 23ai این فرآیند با کمک AI-Powered SQL Tuning می‌تواند حتی به‌صورت خودکار انجام شود.

جمع‌بندی

تحلیل Execution Plan در Oracle ۲۳ai، ابزار اصلی درک رفتار Optimizer و یافتن عوامل کندی است.

با بررسی دقیق Operation‌ها، آمار واقعی (Actual Rows)، و شاخص‌های IO و CPU، می‌توان هر Query را بهینه کرد.

ترکیب DBMS_XPLAN با AI SQL Tuning در ۲۳ai باعث می‌شود Tuning در نسل جدید Oracle دقیق‌تر، هوشمندتر و انسانی‌تر انجام شود.

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

میثم راد

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

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

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