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

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

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

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

مقدمه : بهینه‌سازی کوئری در اوراکل چه اهمیتی دارد؟

بهینه‌سازی کوئری‌ها در پایگاه داده اوراکل یکی از مهم‌ترین مهارت‌هایی است که هر توسعه‌دهنده یا مدیر پایگاه داده (DBA) باید به آن مسلط باشد.

اجرای بهینه کوئری‌ها باعث افزایش کارایی سیستم، کاهش زمان پاسخ‌گویی و استفاده بهینه از منابع سخت‌افزاری می‌شود.

در این مقاله، به بررسی مفاهیم اولیه بهینه‌سازی کوئری‌ها در اوراکل می‌پردازیم و تکنیک‌های مهمی مانند استفاده از ایندکس‌ها، بررسی پلن اجرایی (Execution Plan)، استفاده از Hintها و بهینه‌سازی جوین‌ها را بررسی خواهیم کرد.

اگر می خواهید با مبانی و اصول SQL Tuning در اوراکل بیشتر آشنا بشید نوشته زیر را مطالعه کنید:

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

بهینه‌سازی کوئری در اوراکل چیست؟

بهینه‌سازی کوئری در اوراکل به مجموعه‌ای از تکنیک‌ها برای اجرای سریع‌تر SQL Queries گفته می‌شود که باعث کاهش مصرف CPU، حافظه و I/O می‌شود.

✅ چرا باید کوئری‌ها را بهینه کنیم؟

🔹 کاهش زمان پاسخ‌گویی به درخواست‌ها
🔹 استفاده بهینه از منابع سرور
🔹 افزایش کارایی برنامه‌های متصل به پایگاه داده

چگونه Execution Plan را بررسی کنیم؟

اولین قدم در بهینه‌سازی، بررسی Execution Plan است.

این پلن نشان می‌دهد اوراکل چگونه کوئری را اجرا می‌کند.

📌 روش مشاهده Execution Plan در اوراکل

				
					EXPLAIN PLAN FOR 
SELECT * FROM employees WHERE department_id = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

🔍 اگر “TABLE ACCESS FULL” را مشاهده کردید، یعنی کوئری Full Table Scan انجام می‌دهد که می‌تواند کند باشد.

✅ راه‌حل: ایجاد ایندکس روی ستون مورد جستجو

				
					CREATE INDEX emp_lastname_idx ON employees (UPPER(last_name));

نقش ایندکس‌ها در بهینه‌سازی کوئری

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

📌 انواع ایندکس‌ها در اوراکل

✔ B-Tree Index: برای جستجوهای معمولی (Default)
✔ Bitmap Index: مناسب برای داده‌های کم‌تنوع (مثلاً جنسیت)
✔ Function-Based Index: برای جستجو روی توابع
✔ Composite Index: برای جستجو روی چند ستون

📌 نحوه ایجاد ایندکس در اوراکل

				
					CREATE INDEX emp_name_idx ON employees(last_name);

بهترین روش‌ها برای بهینه‌سازی WHERE

✅ از ایندکس استفاده کنید
✅ در WHERE از توابع روی ستون‌های ایندکس‌شده استفاده نکنید
✅ از EXISTS به جای IN در Subquery استفاده کنید

❌ اشتباه:

				
					SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';

✔ اصلاح‌شده (با Function-Based Index):

				
					CREATE INDEX idx_emp_name ON employees(UPPER(last_name));

بهینه‌سازی جوین‌ها (JOINs)

جوین‌ها می‌توانند عملکرد کوئری را به شدت تحت تأثیر قرار دهند.

📌 انواع JOIN در اوراکل و کاربرد آن‌ها

✔ Nested Loops Join: برای جداول کوچک
✔ Hash Join: برای جداول بزرگ
✔ Sort Merge Join: زمانی که هر دو جدول ایندکس داشته باشند

📌 چگونه نوع JOIN را مشخص کنیم؟

با اجرای EXPLAIN PLAN می‌توانید ببینید که کوئری شما از کدام روش استفاده می‌کند.

✅ افزایش سرعت جوین‌ها:
🔹 ایندکس روی کلیدهای JOIN
🔹 تغییر ترتیب جداول در JOIN
🔹 استفاده از Hintها مانند USE_HASH

				
					SELECT /*+ USE_HASH(e d) */ e.first_name, d.department_name 
FROM employees e JOIN
departments d ON e.department_id = d.department_id;

افزایش سرعت کوئری با Parallel Execution

برای جداول بزرگ، اوراکل می‌تواند کوئری را روی چندین CPU اجرا کند تا عملکرد افزایش یابد.

				
					SELECT /*+ PARALLEL(employees, 4) */ * 
FROM employees WHERE department_id = 10;

✔ نکته: اگر تعداد رکوردها کم باشد، اجرای موازی ممکن است مفید نباشد.

استفاده از Hints برای کنترل بهینه‌ساز

Hints به شما اجازه می‌دهد بهینه‌ساز اوراکل را به شیوه‌ای خاص راهنمایی کنید.

✔ اجبار به استفاده از ایندکس:

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

✔ اجبار به استفاده از Hash Join:

				
					SELECT /*+ USE_HASH(e d) */ * FROM employees e 
JOIN departments d ON e.department_id = d.department_id;

بهینه‌سازی Subqueries

✅ به جای IN از EXISTS استفاده کنید
❌ اشتباه:

				
					SELECT * FROM employees 
WHERE department_id IN 
(SELECT department_id FROM departments WHERE location_id = 1000);

✔ بهینه‌شده:

				
					SELECT * FROM employees 
WHERE EXISTS (SELECT 1 FROM departments 
WHERE employees.department_id =departments.department_id 
AND location_id = 1000);

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

📌 برای پیدا کردن کوئری‌های کند در سیستم، از این کوئری استفاده کنید:

				
					SELECT sql_text, executions, elapsed_time 
FROM V$SQLAREA ORDER BY elapsed_time DESC;

چرا کوئری من در اوراکل کند اجرا می‌شود و چطور می‌توانم علت را پیدا کنم؟

دلایل زیادی برای کند بودن کوئری وجود دارد، از جمله عدم استفاده از ایندکس، قفل شدن جداول، حجم بالای داده‌ها یا اجرای نامناسب جوین‌ها. برای تحلیل مشکل، این مراحل را دنبال کنید:
✔ بررسی Execution Plan با EXPLAIN PLAN
✔ بررسی SQL Monitoring با V$SQLAREA برای مشاهده مصرف منابع
✔ استفاده از AUTOTRACE برای تحلیل مصرف CPU و I/O:

				
					SET AUTOTRACE ON;
SELECT * FROM employees WHERE department_id = 10;

✔ بررسی اینکه آیا قفل شدن (Lock) روی جداول رخ داده است:

				
					SELECT * FROM V$LOCK WHERE BLOCKING_OTHERS = 'YES';

اگر Full Table Scan انجام شده، احتمالاً نیاز به ایجاد ایندکس دارید.

بهترین روش برای بهینه‌سازی WHERE در کوئری‌های اوراکل چیست؟

✔ استفاده از ایندکس در شرط‌های WHERE:
به جای SELECT * فقط ستون‌های ضروری را انتخاب کنید و اطمینان حاصل کنید که ستون‌های مورد جستجو ایندکس دارند.
✔ اجتناب از توابع روی ستون‌های ایندکس‌شده:
❌ اشتباه:

				
					SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';

✔ راه‌حل: ایجاد Function-Based Index:

				
					CREATE INDEX idx_emp_name ON employees(UPPER(last_name));

✔ استفاده از BIND Variables برای جلوگیری از بازکامپایل شدن کوئری‌ها:

				
					SELECT * FROM employees WHERE department_id = :dept_id;

✔ استفاده از EXISTS به جای IN برای کوئری‌های وابسته (Subqueries):
❌ اشتباه:

				
					SELECT * FROM employees 
WHERE department_id IN 
(SELECT department_id FROM departments WHERE location_id = 1000);

✔ بهینه:

				
					SELECT * FROM employees 
WHERE EXISTS (SELECT 1 FROM departments 
WHERE employees.department_id =
departments.department_id AND location_id = 1000);

چه تفاوتی بین Full Table Scan و Index Scan در اوراکل وجود دارد؟

🔹 Full Table Scan: یعنی اوراکل تمام رکوردهای جدول را خوانده و فیلتر می‌کند. این روش معمولاً کندتر است، مگر در شرایطی که بیشتر رکوردهای جدول مورد نیاز باشند.
🔹 Index Scan: یعنی اوراکل از ایندکس برای پیدا کردن رکوردهای موردنظر استفاده می‌کند که معمولاً سریع‌تر از Full Table Scan است.

🔍 چطور بفهمیم که کوئری از Full Table Scan استفاده می‌کند؟

با اجرای EXPLAIN PLAN، اگر در خروجی TABLE ACCESS FULL مشاهده شد، یعنی کوئری کل جدول را اسکن کرده است.

				
					EXPLAIN PLAN FOR SELECT * FROM employees WHERE department_id = 10;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

✔ راه‌حل: ایجاد ایندکس روی ستون موردنظر:

				
					CREATE INDEX idx_emp_dept ON employees(department_id);

پس از ایجاد ایندکس، دوباره Execution Plan را بررسی کنید و مطمئن شوید که کوئری INDEX RANGE SCAN انجام می‌دهد.

چگونه می‌توان از Parallel Execution برای افزایش سرعت کوئری در اوراکل استفاده کرد؟

Parallel Execution در اوراکل به شما امکان می‌دهد یک کوئری را روی چندین CPU اجرا کنید تا سرعت پردازش افزایش یابد. این تکنیک برای کوئری‌های سنگین و جداول بزرگ بسیار مفید است.

✔ برای اجرای کوئری به‌صورت موازی، از Hint PARALLEL استفاده کنید:

				
					SELECT /*+ PARALLEL(employees, 4) */ * 
FROM employees WHERE department_id = 10;

✔ برای تنظیم سطح موازی‌سازی روی یک جدول:

				
					ALTER TABLE employees PARALLEL 4;

✔ برای غیرفعال کردن اجرای موازی:

				
					ALTER SESSION DISABLE PARALLEL QUERY;

🚀 نکته: اجرای موازی برای همه کوئری‌ها مناسب نیست.

اگر جدول کوچک باشد یا سیستم دارای منابع محدودی باشد، ممکن است اجرای موازی باعث افزایش بار CPU شود.

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

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

EXPLAIN PLAN FOR
SELECT * FROM employees WHERE department_id = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

این خروجی نشان می‌دهد که اوراکل چگونه کوئری شما را اجرا می‌کند (مثلاً Full Table Scan یا Index Scan)، و کمک می‌کند تا گلوگاه‌های عملکردی را شناسایی کنید.

اگر Full Table Scan مشاهده کردید، احتمالاً نیاز به ایجاد ایندکس دارید.

✔ استفاده از ایندکس‌ها (Indexes) روی ستون‌های پرکاربرد در WHERE و JOIN
✔ استفاده از Execution Plan برای شناسایی مشکلات عملکردی
✔ استفاده از Hints مانند /*+ INDEX(table index_name) */ برای اجبار استفاده از ایندکس
✔ کاهش تعداد ردیف‌های بازیابی‌شده با FETCH FIRST یا ROWNUM
✔ استفاده از Materialized Views برای کش کردن داده‌های پرتکرار

اگر کوئری شما به جای استفاده از ایندکس، Full Table Scan انجام می‌دهد، دلایل احتمالی آن عبارتند از:
🔹 فیلد مورد جستجو در تابعی مانند UPPER() قرار گرفته است (راه‌حل: Function-Based Index بسازید).
🔹 مقدار مورد جستجو دارای نوع داده‌ی ناسازگار با ستون ایندکس‌شده است.
🔹 کوئری دارای عملگرهایی مانند !=، NOT IN، یا LIKE ‘%value%’ است که مانع استفاده از ایندکس می‌شود.
🔹 تعداد ردیف‌های انتخاب‌شده زیاد است و اوراکل تصمیم گرفته Full Table Scan بهینه‌تر باشد.

ویدیو آموزش بهینه‌سازی کوئری‌ها در اوراکل

نتیجه‌گیری

✅ بررسی Execution Plan برای شناسایی نقاط ضعف
✅ استفاده از ایندکس‌های مناسب برای بهبود سرعت جستجو
✅ بهینه‌سازی JOINها و Subqueries برای پردازش سریع‌تر
✅ استفاده از Hints و Parallel Execution برای افزایش عملکرد

✅ استفاده صحیح از ایندکس‌ها، Hintها، جوین‌های بهینه و Materialized Views می‌تواند بهبود چشمگیری در عملکرد سیستم ایجاد کند.

✅ مانیتورینگ مداوم عملکرد کوئری‌ها یکی از بهترین راه‌ها برای جلوگیری از افت کارایی سیستم است.

با رعایت این تکنیک‌ها، می‌توانید اجرای کوئری‌های خود را تا چندین برابر سریع‌تر کنید و کارایی پایگاه داده اوراکل را بهینه کنید. 🚀

آیا شما هم تجربه‌ای در بهینه‌سازی کوئری دارید؟ چه روش‌هایی برای بهبود عملکرد کوئری‌ها پیشنهاد می‌کنید؟ نظرات خود را در کامنت‌ها بنویسید! 🚀

میثم راد

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

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

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