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

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

راهنمای جامع استفاده از Oracle Hints برای بهینه‌سازی کوئری‌ها

راهنمای جامع استفاده از Oracle Hints

مقدمه : استفاده از Hints در اوراکل چه اهمیتی دارد؟

در دنیای پایگاه داده، عملکرد کوئری‌ها نقش بسیار مهمی در کارایی کلی سیستم دارد.

Oracle SQL Optimizer معمولاً به صورت خودکار بهترین Execution Plan را برای اجرای یک کوئری انتخاب می‌کند، اما در برخی موارد، این انتخاب بهینه‌ترین حالت ممکن نیست.

در چنین شرایطی، می‌توان از Oracle Hints استفاده کرد تا مستقیماً به Optimizer بگوییم که از چه روش‌هایی برای اجرای کوئری استفاده کند.

در این مقاله، یک راهنمای جامع درباره Oracle Hints ارائه خواهیم داد و به روش‌های بهینه‌سازی کوئری‌ها در Oracle SQL خواهیم پرداخت.

با مطالعه این مقاله، شما قادر خواهید بود عملکرد کوئری‌های خود را بهبود دهید و زمان پاسخ‌دهی سیستم را کاهش دهید.

اگر می خواهید با انواع ترفندهای حذف رکوردهای تکراری در اوراکل بیشتر آشنا بشید نوشته زیر را مطالعه کنید:

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

Oracle Hints چیست؟

Oracle Hints دستورالعمل‌هایی هستند که درون کامنت‌های خاص SQL قرار می‌گیرند و به Optimizer راهنمایی می‌دهند که از چه استراتژی‌هایی برای اجرای کوئری استفاده کند.

فرمت کلی Oracle Hints: 

				
					SELECT /*+ HINT_NAME(parameters) */ column_list  
FROM table_name  
WHERE conditions;

در این ساختار، /*+ HINT_NAME */ همان Hint است که به Oracle Optimizer می‌گوید از چه روشی برای اجرای کوئری استفاده کند.

مثال:

				
					SELECT /*+ FULL(emp) */ * FROM employees emp;

🔹 این Hint به Oracle Optimizer دستور می‌دهد که برای خواندن داده‌های جدول employees از Full Table Scan استفاده کند.

انواع Oracle Hints و کاربردهای آن‌ها

Oracle Hints به چند دسته تقسیم می‌شوند که در ادامه به بررسی آن‌ها می‌پردازیم:

۱. Hints مربوط به روش‌های دسترسی به داده‌ها (Access Methods)

این Hints مشخص می‌کنند که داده‌ها چگونه از جداول خوانده شوند.

Hint توضیح
FULL(table_name) استفاده از **Full Table Scan**
INDEX(table_name index_name) استفاده از **Index Scan**
INDEX_FFS(table_name index_name) **Full Index Scan** بدون مراجعه به جدول
TABLE_ACCESS_BY_INDEX_RO(table_name) خواندن داده‌ها فقط از طریق ایندکس

🔹 مثال استفاده از INDEX:

				
					SELECT /*+ INDEX(emp emp_idx) */ * FROM employees emp WHERE department_id = 10;

📌 بهترین زمان برای استفاده: زمانی که یک ایندکس مناسب وجود دارد و می‌خواهید Optimizer را مجبور به استفاده از آن کنید.

۲. Hints مربوط به Join Methods (بهینه‌سازی JOINها)

اگر کوئری شما شامل JOIN باشد، می‌توانید با Hints مشخص کنید که چه روشی برای ترکیب داده‌ها استفاده شود.

Hint توضیح
USE_NL(table1 table2) استفاده از **Nested Loop Join**
USE_HASH(table1 table2) استفاده از **Hash Join**
USE_MERGE(table1 table2) استفاده از **Sort Merge Join**

🔹 مثال استفاده از Nested Loop Join:

				
					SELECT /*+ USE_NL(emp dept) */ emp.*, dept.*  
FROM employees emp  
JOIN departments dept ON emp.department_id = dept.department_id;

📌 زمان مناسب برای استفاده: وقتی که یک جدول کوچک است و جدول دیگر دارای ایندکس مناسب می‌باشد.

🔹 مثال استفاده از Hash Join:

				
					SELECT /*+ USE_HASH(emp dept) */ emp.*, dept.*  
FROM employees emp  
JOIN departments dept ON emp.department_id = dept.department_id;

📌 زمان مناسب برای استفاده: وقتی که هر دو جدول بزرگ هستند و پردازش بهینه‌تری نیاز است.

۳. Hints مربوط به Parallel Execution (اجرای موازی کوئری‌ها)

با این Hints می‌توان کوئری‌ها را به صورت موازی اجرا کرد و سرعت پردازش را افزایش داد.

Hint توضیح
PARALLEL(table_name degree) تنظیم درجه پردازش موازی
NOPARALLEL(table_name) غیرفعال کردن اجرای موازی

مثال اجرای کوئری به‌صورت موازی با ۴ پردازشگر:

				
					SELECT /*+ PARALLEL(employees, 4) */ * FROM employees;

📌 مناسب برای: جداول بزرگ و کوئری‌هایی که نیاز به پردازش سنگین دارند.

۴. Hints مربوط به کنترل Execution Plan

این Hints مشخص می‌کنند که Oracle Optimizer چگونه Execution Plan را ایجاد کند.

Hint توضیح
LEADING(table_name1 table_name2 ...) تعیین ترتیب اجرای جداول
ORDERED اجرای Join بر اساس ترتیب نوشته‌شده در کوئری
CARDINALITY(table_name estimated_rows) تعیین تعداد تقریبی ردیف‌های جدول

🔹 مثال تعیین ترتیب اجرای جداول در Join:

				
					SELECT /*+ LEADING(emp dept) */ emp.*, dept.*  
FROM employees emp  
JOIN departments dept ON emp.department_id = dept.department_id;

📌 این Hint به Optimizer می‌گوید که ابتدا جدول employees و سپس departments را پردازش کند.

بهترین روش‌های استفاده از Oracle Hints

✅ ۱. قبل از استفاده از Hints، Execution Plan را بررسی کنید.

می‌توانید از دستورات زیر برای مشاهده Execution Plan استفاده کنید:

				
					EXPLAIN PLAN FOR 
SELECT * FROM employees; 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

✅ ۲. از ترکیب Hints مختلف استفاده کنید.

گاهی ترکیب چند Hint به بهینه‌تر شدن کوئری کمک می‌کند:

				
					SELECT /*+ USE_HASH(emp dept) PARALLEL(emp, 4) */ emp.*, dept.*  
FROM employees emp  
JOIN departments dept ON emp.department_id = dept.department_id;
				
			
				
					SELECT /*+ FULL(emp) */ * FROM employees emp;

✅ ۳. اگر کوئری شما بهینه نیست، ابتدا ایندکس‌ها و آمارهای آماری را بررسی کنید.

قبل از استفاده از Hints، مطمئن شوید که ایندکس‌های مناسبی تعریف شده‌اند و آمارها به‌روز هستند:

				
					EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');

✅ ۴. استفاده بیش‌ازحد از Hints می‌تواند نتیجه معکوس داشته باشد.

Oracle Optimizer در بیشتر مواقع Execution Plan بهینه‌ای تولید می‌کند، پس فقط زمانی که عملکرد کوئری نامطلوب است، از Hints استفاده کنید.

چه زمانی باید از Oracle Hints استفاده کنیم؟ آیا همیشه مفید هستند؟

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

در این شرایط، استفاده از Hints می‌تواند سودمند باشد.

برخی از مواردی که می‌توان از Hints استفاده کرد:
✅ وقتی Optimizer به‌درستی از Index استفاده نمی‌کند.
✅ وقتی یک روش Join خاص (Nested Loop، Hash Join) کارایی بهتری دارد.
✅ برای کنترل اجرای موازی و بهینه‌سازی پردازش داده‌های حجیم.
✅ در کوئری‌هایی که حجم بالایی از داده‌ها را پردازش می‌کنند و نیاز به بهینه‌سازی دستی دارند.

📌 اما دقت کنید:
🔹 استفاده نامناسب از Hints می‌تواند عملکرد کوئری را کاهش دهد.
🔹 اگر آمارهای پایگاه داده به‌روز نباشند، حتی با Hints هم ممکن است بهینه‌سازی به درستی انجام نشود.
🔹 بهتر است ابتدا Execution Plan را بررسی کنید و فقط در موارد ضروری از Hints استفاده کنید.

تفاوت بین FULL SCAN و INDEX SCAN در Oracle چیست و چه زمانی باید از هرکدام استفاده کنیم؟

✅ FULL SCAN – FULL(table_name):

  • در این روش، کل جدول سطر به سطر اسکن می‌شود.
  • مناسب برای زمانی که شرایط WHERE انتخاب محدودی ایجاد نمی‌کند یا جدول کوچک است.

مثال:

				
					SELECT /*+ FULL(employees) */ * FROM employees;

✅ INDEX SCAN  – INDEX(table_name index_name):

  • به جای اسکن کل جدول، داده‌ها از روی ایندکس خوانده می‌شوند.
  • زمانی مناسب است که شرایط WHERE شامل فیلد ایندکس‌شده باشد و تعداد ردیف‌های فیلترشده کم باشد.

مثال:

				
					SELECT /*+ INDEX(employees emp_idx) */ * FROM employees WHERE department_id = 10;

📌 نکته مهم:
🔹 اگر کوئری تعداد زیادی از ردیف‌های جدول را برمی‌گرداند، FULL SCAN کارایی بهتری دارد.
🔹 اگر فقط تعداد کمی از ردیف‌ها مورد نیاز است، INDEX SCAN بهتر است.
🔹 Execution Plan را بررسی کنید تا ببینید Oracle کدام روش را انتخاب کرده است و در صورت نیاز، با Hints آن را تغییر دهید.

چه زمانی باید از Hash Join به‌جای Nested Loop Join استفاده کنیم؟

🔹 Nested Loop Join  – USE_NL زمانی کارایی بهتری دارد که:
✅ یک جدول کوچک باشد و جدول دیگر دارای ایندکس مناسب باشد.
✅ شرط WHERE باعث فیلترشدن بخش بزرگی از داده‌ها شود.
✅ حجم داده‌ها کم باشد و پردازش سریع لازم باشد.

🔹 مثال استفاده از Nested Loop Join:

				
					SELECT /*+ USE_NL(emp dept) */ emp.*, dept.*  
FROM employees emp  
JOIN departments dept ON emp.department_id = dept.department_id;

🔹 Hash Join  – USE_HASH زمانی بهتر است که:
✅ هر دو جدول حجم بالایی از داده‌ها دارند.
✅ ایندکس مناسب در دسترس نیست یا مقایسه روی مقادیر زیادی از داده‌ها انجام می‌شود.
✅ Oracle Optimizer پیش‌بینی می‌کند که Hash Join سریع‌تر خواهد بود.

🔹 مثال استفاده از Hash Join:

				
					SELECT /*+ USE_HASH(emp dept) */ emp.*, dept.*  
FROM employees emp  
JOIN departments dept ON emp.department_id = dept.department_id;

📌 نکته مهم:
🔹 برای بررسی اینکه کدام روش بهتر است، از EXPLAIN PLAN استفاده کنید.
🔹 اگر یک جدول کوچک و جدول دیگر بزرگ باشد، معمولاً Nested Loop بهتر است.
🔹 اگر هر دو جدول بزرگ باشند، Hash Join بهینه‌تر خواهد بود.

چگونه می‌توان فهمید که یک Hint واقعاً باعث بهبود عملکرد کوئری شده است؟

برای بررسی عملکرد کوئری قبل و بعد از استفاده از Hints، می‌توان از ابزارهای زیر استفاده کرد:

✅ ۱. بررسی Execution Plan با EXPLAIN PLAN

				
					EXPLAIN PLAN FOR 
SELECT * FROM employees;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

🔹 این روش نشان می‌دهد که Oracle Optimizer چگونه کوئری را اجرا می‌کند.

✅ ۲. استفاده از V$SQL_PLAN برای مشاهده کوئری‌های اجراشده

				
					SELECT * FROM V$SQL_PLAN WHERE SQL_ID = 'shenase_query';

🔹 این جدول نشان می‌دهد که Oracle در واقع از چه Execution Planی استفاده کرده است.

✅ ۳. استفاده از AUTOTRACE برای مقایسه هزینه پردازش کوئری

				
					SET AUTOTRACE ON;
SELECT * FROM employees;

🔹 این دستور Execution Plan و هزینه‌های مربوط به I/O و CPU را نمایش می‌دهد.

✅ ۴. استفاده از SQL Monitor در Enterprise Manager

🔹 در Oracle Enterprise Manager، ابزار SQL Monitor جزئیات اجرای کوئری را نمایش می‌دهد و کمک می‌کند که ببینید Hint موردنظر تأثیر مثبتی داشته یا خیر.

📌 نکته مهم:
🔹 اگر بعد از اضافه کردن Hint، تعداد I/O یا زمان اجرای کوئری کاهش پیدا نکرد، ممکن است Hint اشتباه باشد یا Oracle آن را نادیده گرفته باشد.
🔹 Oracle همیشه تمام Hints را اجرا نمی‌کند! اگر تشخیص دهد که یک Hint عملکرد را کاهش می‌دهد، ممکن است از آن چشم‌پوشی کند.
🔹 برای مشاهده اینکه آیا Hint اجرا شده است یا خیر، از V$SQL_PLAN استفاده کنید.

سوالات متداول درباره Oracle Hints

Oracle Optimizer همیشه بهترین Execution Plan ممکن را برای اجرای کوئری انتخاب می‌کند.

در برخی موارد، حتی اگر شما یک Hint خاص را مشخص کنید، Oracle ممکن است آن را نادیده بگیرد.

🔹 دلایل احتمالی:

  • Hint انتخاب‌شده با دیگر عوامل بهینه‌سازی همخوانی ندارد. اگر Oracle تشخیص دهد که روش پیشنهادی شما باعث کاهش عملکرد می‌شود، ممکن است آن را نادیده بگیرد.
  • آمارهای پایگاه داده به‌روز نیستند. Optimizer برای تصمیم‌گیری به آمارهای دقیق از حجم داده‌ها و ایندکس‌ها نیاز دارد. اگر این آمار قدیمی باشد، Oracle ممکن است Hint شما را غیرضروری بداند.
  • ایندکس یا داده‌های لازم برای اجرای Hint وجود ندارد. به عنوان مثال، اگر از INDEX() استفاده کنید اما ایندکس مناسبی در جدول وجود نداشته باشد، Oracle آن را نادیده می‌گیرد.
  • وجود Hints متناقض. اگر چندین Hint مختلف استفاده شود که با هم ناسازگار باشند، Oracle ممکن است برخی از آن‌ها را نادیده بگیرد.

📌 راه‌حل:
🔹 بررسی Execution Plan برای اطمینان از اعمال شدن Hint
🔹 به‌روزرسانی آمارهای پایگاه داده
🔹 اطمینان از وجود ایندکس‌های مناسب
🔹 آزمایش Hintهای مختلف برای یافتن بهترین عملکرد

هر دو Hint برای کنترل ترتیب اجرای جداول در Joinها استفاده می‌شوند، اما تفاوت‌هایی با هم دارند:

🔹 Ordered Hint (ORDERED):

  • ترتیب اجرای جداول را دقیقاً همان‌طور که در FROM نوشته شده است اجباری می‌کند.
  • مناسب برای زمانی که Optimizer ترتیب اشتباهی را برای Join انتخاب می‌کند.
  • در کوئری‌هایی که شامل چندین جدول هستند، با اجبار به استفاده از ترتیب خاص، می‌تواند عملکرد را بهتر کند.

🔹 Leading Hint (LEADING):

  • به Optimizer می‌گوید که از یک جدول خاص به‌عنوان اولین جدول در Join استفاده کند.
  • نسبت به ORDERED انعطاف‌پذیرتر است، زیرا به Oracle اجازه می‌دهد که ترتیب باقی‌مانده را خودش انتخاب کند.
  • وقتی یک جدول کلیدی داریم که بهتر است پردازش از آن شروع شود، استفاده از LEADING می‌تواند بهینه‌تر باشد.

📌 کدام‌یک بهتر است؟
🔹 اگر می‌خواهید کنترل کاملی بر ترتیب اجرای جداول داشته باشید، از ORDERED استفاده کنید.
🔹 اگر فقط می‌خواهید Oracle از یک جدول خاص شروع کند اما ترتیب بقیه جداول را خودش تعیین کند، LEADING انتخاب بهتری است.

اجرای موازی (Parallel Execution) می‌تواند در بسیاری از موارد عملکرد کوئری را بهبود بخشد، اما همیشه بهترین راه‌حل نیست.

🔹 زمان‌هایی که Parallel Execution مفید است:

  • هنگامی که حجم داده‌ها بسیار زیاد است و به پردازش موازی نیاز دارد.
  • زمانی که سخت‌افزار دارای CPUهای چندگانه و توان پردازشی بالا باشد.
  • در گزارش‌گیری‌های پیچیده و پردازش‌های تحلیلی (OLAP) که پردازش موازی می‌تواند سرعت را افزایش دهد.

🔹 زمان‌هایی که Parallel Execution ممکن است کارایی را کاهش دهد:

  • وقتی تعداد پردازنده‌های موجود کم است. اجرای موازی نیاز به منابع دارد و اگر سیستم از قبل مشغول باشد، ممکن است باعث کندی شود.
  • زمانی که حجم داده‌ها کم است. برای جداول کوچک، اجرای سریالی ممکن است سریع‌تر باشد.
  • وقتی که پردازش موازی باعث افزایش Overhead (هزینه اضافی) می‌شود، مخصوصاً اگر مقدار زیادی Synchronization بین پردازش‌ها موردنیاز باشد.
  • در محیط‌هایی که بار کاری بالا است و پردازش‌های دیگر در حال اجرا هستند.

📌 نکته مهم:
🔹 همیشه قبل از اعمال Parallel Execution، بار کاری سرور را بررسی کنید.
🔹 بهتر است مقدار Parallel Degree را بهینه انتخاب کنید و به جای مقدار خیلی بالا، مقدار مناسبی را تنظیم کنید.
🔹 از Execution Plan و SQL Monitoring برای بررسی عملکرد استفاده کنید و ببینید آیا پردازش موازی باعث بهبود شده است یا خیر.

ویدیو آموزش استفاده از Oracle Hints

نتیجه‌گیری

Oracle Hints یکی از روش‌های قدرتمند برای بهینه‌سازی کوئری‌ها در Oracle SQL است.

اما استفاده نادرست از آن‌ها می‌تواند عملکرد را کاهش دهد.

بنابراین، قبل از استفاده، Execution Plan را بررسی کنید و فقط در موارد ضروری، از Hints مناسب استفاده کنید.

✅ Oracle Hints زمانی مفید هستند که Optimizer مسیر بهینه را تشخیص ندهد.
✅ استفاده نامناسب از Hints ممکن است عملکرد را کاهش دهد.
✅ برای داده‌های بزرگ، Hash Join مناسب‌تر است، اما برای داده‌های کم و جدول‌های کوچک، Nested Loop Join بهتر عمل می‌کند.
✅ برای بررسی تأثیر Hints، از EXPLAIN PLAN، AUTOTRACE و SQL Monitor استفاده کنید.

✅ Oracle ممکن است در برخی موارد Hints را نادیده بگیرد، مخصوصاً اگر با استراتژی‌های بهینه‌سازی خودش در تضاد باشد.
✅ ORDERED ترتیب Joinها را اجباری می‌کند، درحالی‌که LEADING فقط اولویت اجرای یک جدول خاص را تعیین می‌کند.
✅ Parallel Execution می‌تواند سرعت کوئری را افزایش دهد، اما در برخی موارد ممکن است عملکرد را بدتر کند.

اگر سؤالات دیگری درباره بهینه‌سازی کوئری‌های Oracle SQL دارید، بپرسید تا به شما کمک کنم! 🚀🔥

میثم راد

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

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

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