مقدمه
تا حالا شده یک کوئری SQL بنویسی و بعدش ببینی سرعت اجرای اون فاجعهست؟ 😫
یا مثلاً ندونی چرا Oracle از ایندکس استفاده نکرده؟ یا چرا بعضی JOINها کند اجرا میشن؟
اینجاست که Execution Plan به دادت میرسه!
در این مقاله آموزش اوراکل در بخش SQL Tuning و بهینه سازی کوئری، قراره یاد بگیری چطور Execution Plan رو در Oracle بخونی، مثل یک کارآگاه حرفهای دیتابیس تحلیلش کنی و ازش برای بهینهسازی کوئریها استفاده کنی. بزن بریم! 💪
اگر می خواهید با Shared Pool و Library Cache در اوراکل بیشتر آشنا بشید نوشته زیر را مطالعه کنید:
در این نوشته شما می خوانید
Execution Plan در Oracle یعنی چی؟
به زبان ساده، Execution Plan یا همون پلن اجرایی کوئری، مسیریه که Oracle تصمیم میگیره برای اجرای کوئری شما طی کنه.
تصور کن یه کوئری نوشتی. Oracle نمیاد همه جدولها رو همینطوری بخونه! بلکه میاد حساب میکنه که از چه راهی سریعتر به جواب میرسه. این تصمیمها رو توی Execution Plan میتونی ببینی:
- آیا جدول رو کامل اسکن کرده؟
- آیا از Index استفاده شده؟
- ترتیب اجرای JOINها چطوره؟
- و کلی اطلاعات مهم دیگه برای تحلیل عملکرد SQL.
چطور Execution Plan رو در Oracle ببینیم؟
روش ۱: دستور EXPLAIN PLAN
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE department_id = 10;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
روش ۲: استفاده از AUTOTRACE در SQL Developer
SET AUTOTRACE ON;
SELECT * FROM employees WHERE department_id = 10;
روش ۳: روش حرفهای با DBMS_XPLAN.DISPLAY_CURSOR
بعد از اجرای کوئری، بنویس:
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
این روش دقیقترین هست چون پلن واقعی اجرا شده توسط Oracle رو نشون میده، نه فقط پیشبینی.
چطور پلن اجرایی SQL رو بخونیم؟ (تحلیل خطبهخط)
فرض کن این Execution Plan رو داری:
------------------------------------------------------------
| Id | Operation | Name | Rows | Cost |
------------------------------------------------------------
| ۰ | SELECT STATEMENT | | | 3 |
| ۱ | TABLE ACCESS FULL| EMPLOYEES | 1000 | 3 |
------------------------------------------------------------
حالا هر ستون چی میگه؟
| ستون | توضیح |
|---|---|
| Id | شناسه مرحله، اجرای درختی |
| Operation | نوع عملیات (مثل TABLE ACCESS, NESTED LOOPS) |
| Name | نام جدول یا ایندکس درگیر |
| Rows | تعداد ردیفهای تخمینی |
| Cost | هزینه اجرای مرحله |
🔎 اگر دنبال آموزش نمایش پلن اجرایی در Oracle هستی، DBMS_XPLAN بهترین روشه.
ترفندهای تحلیل Execution Plan در Oracle
۱. از پایین به بالا بخون
Oracle از پایینترین ردیفها شروع میکنه و به بالا میره.
۲. نوع دسترسی به جدول رو چک کن
چند مدل مهم داریم:
| عملیات | معنی |
|---|---|
| TABLE ACCESS FULL | اسکن کامل جدول → کند و پرهزینه |
| TABLE ACCESS BY INDEX ROWID | ابتدا ایندکس، سپس خواندن رکورد |
| INDEX UNIQUE SCAN | استفاده بهینه از ایندکس (مقدار خاص) |
| INDEX RANGE SCAN | استفاده از رنج دادهها با ایندکس |
| INDEX FULL SCAN | اسکن کامل ایندکس (در برخی موارد سریعتر) |
🔎 اگر دیدی از Full Table Scan استفاده شده، ببین آیا ایندکس مناسب وجود داره یا نه.
۳. نوع JOIN مهمه!
| JOIN Type | توضیح |
|---|---|
| NESTED LOOPS | سریع در جدولهای کوچک، کند در جدولهای بزرگ |
| HASH JOIN | مناسب جدولهای بزرگ، نیازمند حافظه بیشتر |
| MERGE JOIN | مناسب دادههای مرتب شده |
🔎 اگر میبینی Oracle از
NESTED LOOPSبرای دیتای بزرگ استفاده کرده، وقتشه وارد عمل شی و به Optimizer کمک کنی (مثلاً با HINT).
۴. تخمین ردیفها رو با واقعیت مقایسه کن
اگر تعداد Rows پیشبینیشده با تعداد واقعی فرق داره، باید آمار جدول رو آپدیت کنی:
BEGIN
DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');
END;
ابزارهای مهم برای بهینهسازی کوئری در Oracle
| ابزار / مفهوم | کاربرد |
|---|---|
| Index | سرعت در جستوجو |
| Materialized Views | ذخیره نتایج محاسبات سنگین |
| Hints | هدایت Optimizer مثل `/*+ USE_NL */` |
| Optimizer Statistics | آمار بهروز برای تصمیم بهتر |
| Bind Variables | جلوگیری از Hard Parsing و افزایش سرعت |
مثال واقعی برای تحلیل
EXPLAIN PLAN FOR
SELECT first_name FROM employees WHERE department_id = 10;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
خروجی:
----------------------------------------------------------
| Id | Operation | Name | Rows | Cost |
----------------------------------------------------------
| ۰ | SELECT STATEMENT | | | 2 |
| ۱ | TABLE ACCESS FULL| EMPLOYEES | 107 | 2 |
----------------------------------------------------------
✅ در اینجا Oracle تصمیم گرفته کل جدول رو اسکن کنه. چرا؟
چون احتمالاً فیلتر department_id = 10 برای تعداد زیادی رکورد صدق میکنه و استفاده از ایندکس مفید نیست.
سوالات متداول درباره تحلیل Execution Plan در اوراکل
دلایل زیادی وجود داره که Oracle از ایندکس استفاده نکنه، حتی اگه ایندکس تعریف شده باشه:
- مقدار فیلتر خیلی رایجه و Oracle تشخیص داده Full Table Scan سریعتره
- ایندکس چندستونهست ولی ستون اول در شرط نیست
- توی شرط از تابع استفاده کردی (مثلاً
UPPER(column_name)) و ایندکس قابل استفاده نیست - آمار جدول بهروز نیست → از
DBMS_STATS.GATHER_TABLE_STATSاستفاده کن
EXPLAIN PLANفقط پلن تخمینی قبل از اجرا رو نشون میده. ممکنه با پلن واقعی فرق داشته باشه.DBMS_XPLAN.DISPLAY_CURSORپلنی رو نشون میده که واقعاً اجرا شده (Real Plan) با آمار دقیق. این روش دقیقتر و قابل اطمینانتره، مخصوصاً برای بررسی عملکرد واقعی.
مقدار COST یک معیار نسبی برای مقایسه پلنهای مختلفه. Oracle ازش استفاده میکنه تا بین مسیرهای ممکن بهترین رو انتخاب کنه.
- واحد مشخصی نداره (نه زمانه، نه CPU)
- فقط برای مقایسه داخل همون Execution Plan استفاده میشه
- COST پایینتر ممکنه بهتر باشه، اما نه همیشه؛ باید به context توجه کرد (مثلاً استفاده از ایندکس ممکنه هزینه بیشتر داشته باشه ولی خیلی سریعتر اجرا بشه چون داده کمتری خونده میشه)
انتخاب نوع JOIN بستگی به اندازه جدولها، نوع شرطها و میزان مرتبسازی داره:
| نوع JOIN | استفاده پیشنهادی |
|---|---|
NESTED LOOPS | وقتی یکی از جدولها خیلی کوچیکه و ایندکس مناسبی هست |
HASH JOIN | وقتی هر دو جدول بزرگ هستن و مرتب نیستن |
MERGE JOIN | وقتی هر دو جدول مرتب هستن و JOIN با ترتیب ممکنه سریعتر بشه |
اگر دیدی Oracle JOIN مناسبی انتخاب نکرده، میتونی با HINT کمکش کنی:
SELECT /*+ USE_HASH(t1 t2) */ …
جمعبندی
📌 اگه میخوای یک کارآگاه واقعی در بهینهسازی کوئریها باشی، این مراحل رو یادت نره:
- Execution Plan رو از پایین به بالا بخون
- بررسی کن چه نوع دسترسی و چه نوع JOIN استفاده شده
- ایندکسها رو بشناس و به کار بگیر
- تفاوت Rows تخمینی و واقعی رو بررسی کن
- از ابزارهایی مثل HINT و DBMS_STATS استفاده کن
📢 نظر شما چیست؟ اگر شما هم اطلاعات و تجربه خوبی در تحلیل کوئری ها و خواندن اطلاعات از Execution Plan دارید خوشحال میشم در بخش نظرات، تجربه های ارزشمندتان را با ما به اشتراک بگذارید! 🚀
سؤالی درباره این مقاله داری؟
اگر نکتهای در این مقاله برات مبهم بود یا خواستی بیشتر بدونی، همین حالا برام بنویس تا دقیق و صمیمی پاسخت رو بدم — مثل یه گفتوگوی واقعی 💬
برو به صفحه پرسش و پاسخ
دیدگاهتان را بنویسید