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

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

راهنمای کامل SQL Tuning و تکنیک های افزایش سرعت کوئری در Oracle — بخش دوم

راهنمای کامل SQL Tuning و تکنیک های افزایش سرعت کوئری در Oracle
در این مقاله شما می خوانید

🧩 بخش ۴: Execution Plan و تحلیل برنامه اجرا در Oracle

۱۷. چطور Execution Plan را بخوانیم و Bottleneck را تشخیص دهیم؟

Execution Plan به‌ترتیب از پایین به بالا (از داخلی‌ترین عملیات) باید خوانده شود.

هر خط نشان‌دهنده یک مرحله از اجرای Query است.

ساختار اصلی Plan:

  • Operation: نوع کاری که انجام می‌دهد (مثل INDEX SCAN، HASH JOIN)
  • Object Name: نام جدول یا ایندکس
  • Cost / Card / Bytes: تخمین هزینه، کاردینالیتی و حجم داده
  • Predicate Information: شرط‌ها (WHERE) و فیلترها

📘 مثال واقعی:

				
					SELECT e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.location_id = 1200;

📊 خروجی Plan:

				
					| Id | Operation                | Name            | Rows | Cost |
|----|--------------------------|-----------------|------|------|
|  ۰ | SELECT STATEMENT         |                 |  30  |   10 |
|  ۱ |  NESTED LOOPS            |                 |  30  |   10 |
|  ۲ |   TABLE ACCESS FULL      | DEPARTMENTS     |  5   |   3  |
|* ۳ |   INDEX RANGE SCAN       | EMP_DEPT_IX     |  30  |   7  |

🔹 اینجا مرحله مشکل‌زا (Bottleneck) معمولاً بخشی‌ست که Cost یا Cardinality غیرمنطقی دارد.

اگر مثلاً TABLE ACCESS FULL روی جدولی با میلیون‌ها رکورد باشد، علامت هشدار است.

۱۸. چطور عملیات‌های COST زیاد مثل NESTED LOOP یا HASH JOIN را بهینه کنیم؟

هر Join روش خاص خودش دارد و انتخاب اشتباه می‌تواند عملکرد را نابود کند.

انواع Join در Oracle:

توضیح مناسب برای نوع
هر رکورد از A → جستجو در B (با Index بهتر) داده کم Nested Loop
ساخت هش از یک جدول و مقایسه کلیدها داده زیاد Hash Join
ترکیب داده‌ها به‌صورت ترتیبی هر دو جدول Sort شده‌اند Merge Join

📘 مثال:

				
					SELECT * FROM SALES S JOIN CUSTOMERS C
  ON S.CUST_ID = C.CUST_ID;

اگر CUSTOMERS بزرگ است، باید Hash Join استفاده شود:

				
					SELECT /*+ USE_HASH(C) */ * FROM SALES S JOIN CUSTOMERS C
  ON S.CUST_ID = C.CUST_ID;

✅ نکته DBAها:

اگر می‌خواهی Join تغییر نکند بین اجراها، از SQL Plan Baseline استفاده کن (در انتهای آموزش کامل توضیح داده می‌شود).

۱۹. چطور از DBMS_XPLAN برای مقایسه هزینه Planها استفاده کنیم؟

DBMS_XPLAN.DISPLAY_CURSOR دقیق‌ترین گزارش Execution Plan را می‌دهد، مخصوصاً وقتی PARALLEL یا DYNAMIC Sampling فعال است.

📘 مثال گام‌به‌گام:

				
					SELECT * FROM SALES WHERE AMOUNT > ۱۰۰۰۰;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null, null, 'ALLSTATS LAST'));

📊 خروجی:

				
					| Id | Operation | Name | E-Rows | A-Rows | A-Time |
|----|------------|------|--------|--------|--------|
|  ۱ | TABLE ACCESS BY INDEX ROWID | SALES | 100 | 95 | 00:00:01 |
|  ۲ | INDEX RANGE SCAN | IDX_AMOUNT | 100 | 95 | 00:00:00 |

🔍 E-Rows vs A-Rows (Estimated vs Actual)

اگر اختلاف زیاد است (مثلاً تخمین ۱۰۰ ولی واقعی ۱۰۰۰۰)، پس Statistics دقیق نیست و باید Gather شود.

۲۰. Adaptive Execution Plan چیست و از نسخه ۱۲c به بعد چه تفاوتی دارد؟

از Oracle 12c به بعد، Oracle می‌تواند در حین اجرای Query مسیر خود را عوض کند!

یعنی مثلاً اگر حدس می‌زد داده کم است (پس Nested Loop انتخاب کرد) اما موقع اجرا متوجه شد زیاد است، سریع Hash Join را انتخاب می‌کند.

📘 مثال واقعی:

				
					SELECT /*+ ADAPTIVE_PLAN */ * FROM ORDERS O
JOIN CUSTOMERS C ON O.CUST_ID = C.CUST_ID;

🔹 در Plan می‌بینی:

				
					(Nested Loop) -> *adaptive*
(Hash Join) -> *possible*

✅ نتیجه: Queryهایی که بار داده غیرقابل پیش‌بینی دارند (مثل داده‌های فصلی یا ETL) با Adaptive Plan ثابت‌تر و سریع‌تر می‌شوند.

⚡ بخش ۵: SQL Hints – کنترل مستقیم Optimizer

۲۱. SQL Hints دقیقاً چه هستند و چه اثری دارند؟

Hintها دستوراتی درون Query هستند که به Oracle می‌گویند “چطور” کوئری را اجرا کند.

اما فقط پیشنهادند، نه اجبار مطلق — مگر مانع منطقی وجود نداشته باشد.

📘 مثال:

				
					SELECT /*+ INDEX(emp emp_name_idx) */ name FROM emp WHERE name='ALI';

Oracle مجبور می‌شود از ایندکس EMP_NAME_IDX استفاده کند.

✅ کاربرد:

  • تغییر نوع Join
  • اجبار در استفاده از Index
  • فعال‌سازی اجرای موازی (Parallel)
  • کنترل Sort و Full Scan

۲۲. معروف‌ترین Hintها که بیشترین تأثیر را در Performance دارند؟

کاربرد Hint
اجبار استفاده از Index خاص INDEX(tab index)
استفاده از Hash Join USE_HASH(tab)
استفاده از Nested Loop USE_NL(tab)
فعال کردن پردازش موازی PARALLEL(tab n)
تعیین ترتیب Joinها LEADING(tab)
اجبار Full Table Scan FULL(tab)
جلوگیری از ترکیب Subqueryها NO_MERGE

📘 مثال حرفه‌ای:

				
					SELECT /*+ LEADING(c o) USE_HASH(o) PARALLEL(o 8) */
FROM customers c JOIN orders o ON c.cust_id=o.cust_id;

اینجا Oracle با موازی‌سازی روی ۸ CPU، Hash Join را با ترتیب خاص اجرا می‌کند.

۲۳. در چه مواردی استفاده از Hint خطرناک است؟

Hintها قدرت زیادی دارند اما اگر با آمار جدید جور نباشند، می‌توانند فاجعه درست کنند!

🔹 خطرات:

  • با تغییر داده‌ها Hint دیگر بهترین مسیر نیست.
  • ممکن است در نسخه بعدی Oracle باطل شود.
  • مانع از Adaptive Plan می‌شود.

📘 مثال:

اگر بنویسی:

				
					SELECT /*+ FULL(EMP) */ * FROM EMP WHERE EMP_ID=10;

Oracle مجبور می‌شود Full Scan بزند، حتی اگر Index وجود داشته باشد!

در نتیجه زمان اجرا از میلی‌ثانیه به چند ثانیه می‌رسد.

✅ قانون طلایی DBAها:

«اول علت Plan بد را بفهم، بعد Hint بزن.»

۲۴. چطور Oracle AI Optimizer می‌تواند Hints را به‌صورت خودکار پیشنهاد دهد؟ (در Oracle 23ai)

در Oracle 23ai، سیستم AI Optimizer رفتار Queryهای گذشته را تحلیل می‌کند و خودش پیشنهاد Hint مناسب می‌دهد.

این الگوریتم بر اساس Patternهای تکراری Plan ساخته می‌شود و هدفش کم‌کردن نیاز به Tuning دستی است.

📘 مثال:

اگر همیشه در Query مشابه، Oracle می‌بیند USE_HASH سریع‌تر است، پیشنهاد می‌دهد که Plan آینده با Hash Join اجرا شود، حتی اگر Hint ننوشته باشی.

✅ نتیجه:

AI Optimizer عملاً DBA را از کارهای دستی روزمره مثل تست Plan‌های تکراری نجات می‌دهد.

🚀 بخش ۶: Query Rewrite و Optimizer Transformations

۲۵. Query Rewrite چیست و چگونه Oracle خودش Query را بازنویسی می‌کند؟

Oracle قبل از اجرا، Query تو را تحلیل و در صورت امکان آن را rewrite می‌کند تا ساده‌تر اجرا شود، بدون تغییر در نتیجه نهایی.

📘 مثال:

				
					SELECT COUNT(*) FROM (SELECT DISTINCT dept_id FROM emp);

Optimizer این Query را rewrite می‌کند به:

				
					SELECT COUNT(DISTINCT dept_id) FROM emp;

حجم کار نصف می‌شود ولی خروجی یکی است.

✅ Query Rewrite در Data Warehouseها بسیار مفید است چون Materialized Viewها را جایگزین کوئری اصلی می‌کند.

۲۶. Materialized View Rewrite چگونه اجرا می‌شود؟

اگر MViewی داری که همان Aggregation Query را دارد، Oracle به جای اجرای Query اصلی، از آن MView استفاده می‌کند.

📘 مثال:

				
					CREATE MATERIALIZED VIEW sales_mv AS
SELECT region, SUM(amount) AS total_sales FROM sales GROUP BY region;

-- Query user:
SELECT region, SUM(amount) FROM sales GROUP BY region;

Oracle خودش تشخیص می‌دهد که می‌تواند از sales_mv استفاده کند و به داده اصلی سر نزند — یعنی سرعت از ثانیه‌ها به میلی‌ثانیه می‌رسد.

۲۷. Predicate Pushdown و Subquery Unnesting یعنی چه؟

Predicate Pushdown:

Oracle سعی می‌کند شرط‌ها را تا پایین‌ترین لایه ممکن هل دهد تا داده‌های غیرضروری زودتر فیلتر شوند.

📘 مثال:

				
					SELECT * FROM (
   SELECT * FROM SALES WHERE REGION='IRAN'
) WHERE AMOUNT > ۱۰۰۰;

Oracle شرط AMOUNT>1000 را هم به لایه داخلی push می‌کند تا حجم داده کمتر شود.

Subquery Unnesting:

وقتی Subquery در WHERE وجود دارد، Oracle آن را به Join تبدیل می‌کند تا اجرا سریع‌تر شود.

📘 مثال:

				
					SELECT * FROM EMP
WHERE DEPT_ID IN (SELECT DEPT_ID FROM DEPT WHERE LOCATION='TEHRAN');

Rewrite می‌شود به:

				
					SELECT * FROM EMP E JOIN DEPT D ON E.DEPT_ID=D.DEPT_ID AND D.LOCATION='TEHRAN';

۲۸. Parallel Execution چطور روی SQL Performance تأثیر می‌گذارد؟

Parallel Execution یعنی Oracle Query را در چندین Slave پردازش تقسیم می‌کند. CPUها همزمان اجرا می‌شوند، مخصوصاً برای گزارش‌گیری‌های سنگین یا ETL.

📘 مثال:

				
					SELECT /*+ PARALLEL(4) */ COUNT(*) FROM SALES;

Oracle Query را بین ۴ CPU تقسیم می‌کند، هرکدام بخشی از جدول را می‌خوانند و بعد نتایج ترکیب می‌شود.

✅ نکات حرفه‌ای:

  • فایل‌سیستم باید موازی (ASM یا Exadata) باشد.
  • برای کوئری‌های کوچک parallel بی‌فایده است.
  • ترافیک زیاد می‌تواند باعث contention شود.

سوالات متداول درباره تکنیک های SQL Tuning در اوراکل

Execution Plan نقشه‌ای است که Optimizer اوراکل برای اجرای یک Query انتخاب می‌کند و نشان می‌دهد داده‌ها چگونه، از کجا و با چه روشی خوانده شوند (Index Scan، Full Scan، Join Method، Sort و …).

✅ چرا حیاتی است؟

  • دو Query با خروجی یکسان می‌توانند هزینه‌ای کاملاً متفاوت داشته باشند
  • کندی SQL معمولاً ناشی از Execution Plan نامناسب است نه خود Query

✅ ابزارهای مهم تحلیل Execution Plan:

  • EXPLAIN PLAN
  • DBMS_XPLAN.DISPLAY
  • DBMS_XPLAN.DISPLAY_CURSO

📌 نکته حرفه‌ای:

همیشه Execution Plan واقعی (Actual Plan) را بررسی کن نه فقط Plan تخمینی.

  • Estimated Plan بر اساس Statistics محاسبه می‌شود
  • Actual Plan بر اساس اجرای واقعی Query و Rowهای بازگشتی است

✅ چرا ممکن است متفاوت باشند؟

  • Statistics قدیمی یا ناقص
  • Skew داده‌ها
  • Bind Variable Peeking
  • Cardinality Estimation اشتباه

✅ چرا Actual Plan مهم‌تر است؟

  • نشان می‌دهد Optimizer چه فکری کرده و Query چه کاری واقعاً انجام داده
  • اختلاف زیاد بین Estimated Rows و Actual Rows = زنگ خطر جدی 🚨

✅ تعریف ساده:

SQL Hint روشی برای کنترل مستقیم Optimizer و مجبور کردن آن به انتخاب مسیر خاص اجراست.

✅ مثال رایج:

SELECT /*+ INDEX(emp emp_idx1) */ * FROM emp WHERE empno = 100;

✅ مزایای Hint:

  • کنترل دقیق Execution Plan
  • حل سریع Performance Issue در شرایط بحرانی

✅ معایب Hint:

  • وابستگی Query به ساختار فعلی دیتا
  • با تغییر حجم داده یا نسخه Oracle ممکن است Plan بدتر شود
  • ضد‑الگوی Tuning اگر زیاد استفاده شود

📌 بهترین Practice:

✔ اول Statistics و Model داده را درست کن

✔ اگر نشد → Hint به‌عنوان آخرین راه‌حل

✅ Query Rewrite چیست؟

فرآیندی که Oracle بدون اطلاع شما Query را بازنویسی منطقی می‌کند تا هزینه اجرا کمتر شود.

✅ نمونه Optimizer Transformations:

  • Predicate Pushing
  • Subquery Unnesting
  • View Merging
  • Join Elimination
  • OR Expansion

✅ چرا مهم است؟

  • ظاهر Query با چیزی که اجرا می‌شود متفاوت است
  • Execution Plan تنها راه دیدن Rewrite واقعی است

جمع‌بندی

  • Execution Plan قلب SQL Tuning در Oracle است
  • همیشه Actual Plan را بررسی کن، نه فقط Estimated
  • SQL Hint ابزار قدرتمند اما خطرناک است؛ با احتیاط استفاده شود
  • Query Rewrite و Transformations باعث می‌شوند Query شما دقیقاً همان چیزی نباشد که نوشته‌اید
  • بدون دیدن Execution Plan، هیچ تحلیلی معتبر نیست

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

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

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

میثم راد

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

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

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