در محیط‌های Enterprise، پایگاه داده Oracle یکی از قدرتمندترین و در عین حال پیچیده‌ترین سیستم‌های مدیریت داده است.
در این فضا، درک دقیق Execution Plan یا نقشه‌ی اجرای کوئری حیاتی است؛ زیرا نشان میدهد Oracle چگونه کوئری شما را اجرا میکند و چه منابعی را مصرف می‌نماید.
پکیج DBMS_XPLAN ابزاری رسمی و بسیار توانمند در Oracle برای نمایش و تحلیل Execution Plan محسوب میشود.
با این پکیج می‌توان فهمید چرا یک Query کند است، کدام ایندکس به‌کار گرفته شده، هزینه‌ی هر عملیات چقدر بوده، و تفاوت بین پلان واقعی و تخمینی چیست.
در این مقاله آموزش Oracle در بخش آموزش پکیج های اوراکلی در این پکیج DBMS_XPLAN صحبت خواهیم کرد.
پکیج DBMS_SQLPA که با نام کامل SQL Performance Analyzer شناخته میشود یکی از ابزارهای قدرتمند و کمتر شناخته‌شده در Oracle Database است. پیشنهاد می کنم این مقاله زیر رو حتما مطالعه کنی.
در این مقاله شما می خوانید
هدف و کاربرد پکیج DBMS_XPLAN
DBMS_XPLAN در اصل برای استخراج و نمایش پلانهای اجرایی طراحی شده تا رفتار Optimizer را شفاف کند.
کاربردهای کلیدی آن شامل موارد زیر است:
- نمایش پلان تخمینی با دستور
EXPLAIN PLAN - مقایسه پلانهای واقعی با تخمینی
- تحلیل عملکرد کوئریهای در حال اجرا (Real-Time)
- استخراج پلانهای ذخیرهشده از AWR Repository
- مشاهده SQL Plan Baseline برای بررسی تغییرات در تصمیمات Optimizer
به بیان ساده، این ابزار به DBA کمک میکند تا استراتژی اجرای SQL را تحلیل و بهینه کند.
🔧 توابع اصلی پکیج DBMS_XPLAN
| Function | Description |
|---|---|
| DISPLAY | نمایش پلان از جدول PLAN_TABLE — معمولاً برای کوئری‌هایی که با EXPLAIN PLAN ایجاد شده‌اند. |
| DISPLAY_CURSOR | نمایش پلان واقعی کوئری اجراشده در حافظه؛ شامل اطلاعات دقیق منابع مصرفی. |
| DISPLAY_AWR | استخراج پلانهای تاریخی از مخزن AWR برای تحلیل عملکرد کوئریهای گذشته. |
| DISPLAY_SQL_PLAN_BASELINE | مشاهده پلان مربوط به SQL Plan Baseline سیستم برای بررسی تغییرات Optimizer. |
مشاهده پلان تخمینی با EXPLAIN PLAN و DISPLAY
ابتدا باید پلان تخمینی را ایجاد کنیم:
EXPLAIN PLAN FOR
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.location_id = 1700;
اگر جدول PLAN_TABLE وجود نداشت، دستور زیر اجرا شود:
@?/rdbms/admin/utlxplan.sql
سپس نمایش پلان:
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
خروجی نمونه:
----------------------------------------------------------
| Id | Operation | Name | Rows | Cost |
----------------------------------------------------------
| ۰ | SELECT STATEMENT | | 10 | 15 |
| ۱ | HASH JOIN | | 10 | 15 |
| ۲ | TABLE ACCESS FULL | DEPARTMENTS | 27 | 3 |
| ۳ | TABLE ACCESS FULL | EMPLOYEES | 107 | 12 |
----------------------------------------------------------
تفسیر ستون‌ها:
- ID = ترتیب اجرای گام‌ها
- Operation = نوع عمل (Scan، Join…)
- Name = نام جدول یا ایندکس
- Rows / Cost = تخمین Cardinality و هزینه اجرای مرحله
نمایش پلان واقعی اجرای کوئری با DISPLAY_CURSOR
برای نمایش پلان واقعی اجرای آخرین Query:
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
خروجی:
---------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers|
---------------------------------------------------------------------------------
| ۰ | SELECT STATEMENT | | 1 | 10 | 10 | 31|
| ۱ | HASH JOIN | | 1 | 10 | 10 | 31|
| ۲ | TABLE ACCESS FULL | DEPARTMENTS | 1 | 7 | 7 | 3|
| ۳ | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 10 | 28|
---------------------------------------------------------------------------------
تحلیل ستونی:
- E-Rows = تخمین تعداد ردیف‌ها
- A-Rows = تعداد واقعی رکوردها
- Buffers = بلوک‌های داده خوانده‌شده از حافظه
📊 اختلاف چشم‌گیر بین E-Rows و A-Rows نشان‌دهنده ضعف در Statistics است.
✅ در این صورت باید آمار جدول را بازسازی کنیم:
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');
تحلیل Planهای ذخیرهشده در AWR
برای بررسی کوئریهای گذشته در محیط Production:
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('sql_id_Query'));
نمونه:
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('7xa2b1u4kjs32'));
این متد برای بررسی پلان تاریخی و نقاط ضعف Performance در دوره‌های زمانی مختلف فوق‌العاده کاربردی است.
⚙️ تنظیم Display Mode در پکیج DBMS_XPLAN
| Mode | Application |
|---|---|
| BASIC | نمایش خلاصه و سبک برای مرور سریع. |
| TYPICAL | نمایش معمولی همراه با COST و Rows تخمینی. |
| ALLSTATS LAST | نمایش آمار واقعی از آخرین اجرای کوئری (تفاوت Estimated/Actual). |
| ADVANCED | مد نمایشی برای تحلیل Parallel Execution و Partitioning. |
مثال کاربردی واقعی در سیستم عملیاتی
فرض کنید در Schema ZAMIUX کوئری زیر کند اجرا میشود:
SELECT * FROM zamiux.orders WHERE customer_id = 84215;
یافتن SQL_ID:
SELECT sql_id FROM v$sql WHERE sql_text LIKE '%zamiux.orders%';
نمایش پلان واقعی:
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('2q1fsd9g5n6ap', NULL, 'ALLSTATS LAST'));
خروجی قبل از بهینه‌سازی:
| Id | Operation | Name | Cost | Rows | A-Rows |
|----|------------------|--------|------|------|--------|
| ۰ | SELECT STATEMENT | | 120 | 12 | 12 |
| ۱ | TABLE ACCESS FULL| ORDERS | 120 | 12K | 12 |
یعنی Full Scan رخ میدهد و ایندکسی وجود ندارد.
ساخت ایندکس برای بهبود:
CREATE INDEX idx_orders_customer_id ON zamiux.orders(customer_id);
بعد از اجرای مجدد:
| Id | Operation | Name | Cost | Rows |
|----|------------------|------------------------|------|------|
| ۰ | SELECT STATEMENT | | 3 | 12 |
| ۱ | INDEX RANGE SCAN | IDX_ORDERS_CUSTOMER_ID | 3 | 12 |
✅ هزینه از ۱۲۰ به ۳ کاهش یافته — بهینه‌سازی کامل انجام شد.
💡 نکات مهم برای DBAها و توسعه‌دهندگان
- تفاوت میان EXPLAIN PLAN (تخمینی) و DISPLAY_CURSOR (واقعی) را درک کنید.
- همیشه اختلاف بین E-Rows و A-Rows را بررسی کنید تا نقاط ضعف Statistics مشخص شود.
- از
'ADVANCED'برای تحلیل‌های Parallel Execution استفاده کنید. - پلانهای تاریخی از AWR برای تحلیل Performance بلندمدت ایده‌آل هستند.
- در Oracle 19c و بالاتر، DBMS_XPLAN از پاراللیزم و جزئیات Thread نشاندهندهی عمق پردازش نیز پشتیبانی میکند.
سوالات متداول درباره پکیج DBMS_XPLAN در اوراکل
دستور EXPLAIN PLAN فقط پلان تخمینی Optimizer را نشان میدهد و ممکن است با اجرا تفاوت داشته باشد؛
اما تابع DBMS_XPLAN.DISPLAY_CURSOR پلان واقعی اجرای کوئری را از حافظه کش استخراج میکند، شامل مقدار دقیق Actual Rows و مصرف Buffers. بنابراین برای تحلیل واقعگرای Performance همیشه از DISPLAY_CURSOR استفاده میشود.
اگر در خروجی DBMS_XPLAN.DISPLAY_CURSOR اختلاف زیادی بین E-Rows و A-Rows مشاهده شود، نشاندهندهی عدم دقت Statistics است.
در این حالت باید از دستور زیر استفاده کنید:
EXEC DBMS_STATS.GATHER_TABLE_STATS('schema','table_name');
بدون آمار دقیق، Optimizer ممکن است مسیر اشتباهی را برای اجرای کوئری انتخاب کند.
پلانهای ذخیرهشده در مخزن AWR برای تحلیل کوئریهای گذشته بسیار مفیدند. کافی است دستور زیر را اجرا کنید:
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('sql_id'));
این روش زمانی کاربرد دارد که Session بسته شده باشد و بخواهید رفتار کوئری را در بازههای زمانی مختلف بررسی کنید.
ستون Cost تخمین تقریبی میزان منابع مورد نیاز است نه زمان واقعی اجرا. اگر با ایجاد ایندکس یا تغییر Join روش اجرای کوئری از TABLE ACCESS FULL به INDEX RANGE SCAN تغییر کند و عدد Cost کاهش یابد، یعنی Optimizer مسیر سریعتر و کمهزینهتری پیدا کرده است.
همواره مقایسه قبل و بعد از اصلاح ایندکس مهمترین ابزار DBA برای ارزیابی موفقیت Tuning است.
جمع‌بندی
پکیج DBMS_XPLAN ستون اصلی در مباحث Performance Tuning در Oracle است.
این ابزار دید دقیق از رفتار Optimizer، نحوه استفاده از ایندکس‌ها، و تفاوت میان هزینه تخمینی و واقعی را ارائه میدهد.
با تسلط بر آن، هر DBA می‌تواند سیستم را برای حداقل هزینه و حداکثر سرعت اجرای کوئری‌ها تنظیم کند.
📥 اگر سوالی داری در مورد پکیج DBMS_XPLAN در اوراکل داری، در بخش کامنت‌ها بپرس.
دیدگاهتان را بنویسید