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

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

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

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

🧩 بخش ۱: مفاهیم پایه SQL Tuning در Oracle

۱. SQL Tuning در Oracle چیست و چرا برای Performance حیاتی است؟

SQL Tuning یا بهینه‌سازی SQL فرآیند اصلاح Queryها و ساختار دیتابیس است تا اجرای آن‌ها سریع‌تر و بهینه‌تر شود.

در محیط‌های بزرگ مثل ERP یا Data Warehouse، یک Query کند می‌تواند کل سیستم را متوقف کند.

🔹 هدف تیونینگ:

  • کم کردن زمان پاسخ‌دهی (Response Time)
  • کاهش مصرف CPU و I/O
  • بهینه‌سازی Plan اجرایی کوئری‌ها
  • جلوگیری از Lock و Wait غیرضروری

📘 مثال واقعی:

فرض کن Query‌ زیر داریم که کند اجرا می‌شود:

				
					SELECT * FROM SALES WHERE TO_CHAR(SALE_DATE, 'YYYY-MM-DD') = '2025-12-01';

در ظاهر ساده است، اما چون تابع TO_CHAR روی ستون اصلی (SALE_DATE) استفاده شده، Oracle نمی‌تواند از Index استفاده کند و مجبور می‌شود Full Table Scan بزند.

✅ راه‌حل بهینه‌سازی:

				
					SELECT * FROM SALES WHERE SALE_DATE = TO_DATE('2025-12-01','YYYY-MM-DD');

اینطوری Index فعال می‌مونه و Query چندین برابر سریع‌تر اجرا میشه.

۲. Oracle SQL Execution Plan چیست و چطور تفسیر می‌شود؟

Execution Plan نقشه‌ای است که Oracle برای اجرای Queryت می‌سازد؛ یعنی دقیقاً مشخص می‌کند از چه Index، join، sort و مسیرهایی استفاده می‌کند تا داده را از دیسک بخواند.

با دستور زیر قبل از اجرای Query می‌توانی Plan را ببینی:

				
					EXPLAIN PLAN FOR
SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 50;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

📄 خروجی:

				
					-----------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
|   ۰ | SELECT STATEMENT            |               |   10  |   530 |     3   (0)|
|   ۱ |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |   10  |   530 |     3   (0)|
|*  ۲ |   INDEX RANGE SCAN          | IDX_DEPT_ID   |   10  |       |     2   (0)|
-----------------------------------------------------------------------------------

🔍 توضیح:

اینجا Oracle فهمیده شرط روی ستون DEPARTMENT_ID هست و Indexی به نام IDX_DEPT_ID وجود دارد، پس فقط همان بخش‌های ضروری جدول را می‌خواند.

📘 مثال کاربردی:

اگر همان Query را بدون شرط اجرا کنی SELECT * FROM EMPLOYEES;، Plan به FULL TABLE SCAN تغییر می‌کند که معمولاً کندتر است.

۳. Cost-Based Optimizer در Oracle چطور کار می‌کند؟

CBO یا Cost-Based Optimizer مغز Oracle است؛ تصمیم می‌گیرد چگونه Query را اجرا کند تا هزینه‌ی آن (Cost) کم‌ترین مقدار ممکن باشد.

این Cost بر اساس عوامل زیر محاسبه می‌شود:

  • تعداد رکوردهای جدول
  • درصد رکوردهای قابل فیلتر (Selectivity)
  • وجود یا عدم وجود Index
  • اندازه‌ی بلوک‌های داده در دیسک
  • کارایی CPU و حافظه

CBO از آمار (Statistics) که با DBMS_STATS جمع‌آوری می‌شود، برای تصمیم‌گیری استفاده می‌کند.

📘 مثال:

دو جدول داریم:

تعداد ردیف TABLE
۱۰,۰۰۰,۰۰۰ CUSTOMERS
۵ REGIONS

اگر Query زیر اجرا شود:

				
					SELECT * FROM CUSTOMERS C JOIN REGIONS R ON C.REGION_ID = R.ID;

CBO تصمیم می‌گیرد از Nested Loop یا Hash Join استفاده کند.

چون REGIONS کوچک است، Nested Loop سریع‌تر خواهد بود.

۴. Rule-Based Optimizer (RBO) چیست و آیا هنوز کاربرد دارد؟

RBO الگوریتم قدیمی (قبل از Oracle 10g) است که براساس قوانین ثابت تصمیم می‌گرفت، نه بر اساس آمار واقعی.

در نسخه‌های جدید حذف شده، ولی در بعضی اپلیکیشن‌های Legacy هنوز آثارش دیده می‌شود.

🔸 تصمیم‌گیری در RBO ساده بود:

  • اگر Index هست → Index Scan
  • اگر شرط روی ستون ایندکس‌دار نبود → Full Scan

📘 مثال:

در RBO، Query زیر همیشه Index می‌زد حتی اگر کارایی پایین‌تر داشت:

				
					SELECT * FROM EMP WHERE SALARY < 10000;

اما CBO ممکن است بفهمد که ۹۰٪ کارمندان زیر ۱۰۰۰۰ هستند، پس Full Scan را انتخاب کند که سریع‌تر است.

۵. Cardinality و Selectivity یعنی چه و چرا اهمیت دارند؟

Cardinality: تعداد رکوردهایی که Oracle پیش‌بینی می‌کند Query برمی‌گرداند.

Selectivity: درصد رکوردهایی که بعد از فیلتر باقی می‌مانند.

فرمول ساده‌اش:

Estimated Rows​ / Total Rows = Selectivity

هرچه Selectivity کمتر باشد (یعنی داده محدودتر باشد)، احتمال استفاده از Index بیشتر است.

📘 مثال:

جدول CUSTOMERS با ۱,۰۰۰,۰۰۰ رکورد:

Selectivity Cardinality شرط
۰.۱ ۱۰۰,۰۰۰ COUNTRY=‘IRAN’
۰.۵ ۵۰۰,۰۰۰ GENDER=‘M’
۰.۹۹ ۹۹۰,۰۰۰ STATUS=‘ACTIVE’

Optimzer فقط برای شرط اول احتمالاً از Index استفاده می‌کند.

۶. آیا همیشه باید Query را بازنویسی کنیم یا راه‌حل‌های دیگر هم هست؟

بازنویسی مفید است، اما همیشه آخرین گزینه است. قبل از آن باید:

  1. آمار (Statistics) را به‌روزرسانی کنی،
  2. Index مناسب بسازی،
  3. پارامترهای Optimizer را بررسی کنی.

📘 مثال:

Query کند:

				
					SELECT * FROM EMP WHERE SALARY < 10000;

✅ راه‌حل بهتر:

				
					ALTER TABLE SALES ADD AMOUNT_TOTAL AS (AMOUNT + TAX);
CREATE INDEX IDX_TOTAL ON SALES(AMOUNT_TOTAL);

SELECT * FROM SALES WHERE AMOUNT_TOTAL > ۱۰۰۰;

اینجا Query چند برابر سریع‌تر می‌شود بدون آن‌که ساختارش عوض شود.

⚙️ بخش ۲: Statistics در SQL Tuning اوراکل

۷. چرا Statistics در SQL Tuning حیاتی است و چه زمانی باید Gather شوند؟

Statistics (آمار جدول‌ها و ایندکس‌ها) چشم و گوش Optimizer هست. بدون آن، Optimizer مثل راننده‌ای است که شب بدون چراغ حرکت می‌کند — مسیر را حدس می‌زند، نه تحلیل.

🔹 شامل مواردی مثل:

  • تعداد ردیف‌ها (row count)
  • توزیع داده‌ها در ستون‌ها
  • تعداد distinct value ها
  • درصد null
  • حجم جدول و ایندکس

اگر این آمار قدیمی یا ناقص باشد، Oracle ممکن است Execution Plan اشتباه انتخاب کند.

📘 مثال:

فرض کن جدول ORDERS تا هفته قبل ۱ میلیون ردیف داشته ولی بعد از import جدید شده ۱۰ میلیون.

Optimzer هنوز فکر می‌کند حجمش کم است و Nested Loop Join انتخاب می‌کند در حالی که باید Hash Join می‌زد.

✅ راه‌حل:

بعد از تغییرات عمده‌ی داده:

				
					EXEC DBMS_STATS.GATHER_TABLE_STATS('SALES', 'ORDERS');

اگر دیتابیس بزرگ داری، جمع‌آوری آمار را زمان‌بندی کن تا شب انجام شود:

				
					EXEC DBMS_STATS.GATHER_DATABASE_STATS(DBMS_STATS.AUTO_SAMPLE_SIZE);

۸. DBMS_STATS چگونه کار می‌کند و بهترین استراتژی اجرای آن چیست؟

پکیج DBMS_STATS دقت زیادی دارد چون برخلاف ANALYZE قدیمی، از Sampling هوشمند استفاده می‌کند.

یعنی لازم نیست همه‌ی ردیف‌ها را بخواند؛ فقط درصد اندکی که آماری معتبر بسازد.

🔸 اصول اجرا:

  • برای جدول‌های بزرگ از AUTO_SAMPLE_SIZE
  • برای جدول‌های کوچک از ESTIMATE_PERCENT=>100
  • اگر حجم تغییر داده زیاد باشد، از گزینه‌ی CASCADE=>TRUE استفاده کن تا ایندکس‌ها هم بروزرسانی شوند.

📘 مثال کاربردی:

				
					BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    OWNNAME => 'HR',
    TABNAME => 'EMPLOYEES',
    ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,
    CASCADE => TRUE
  );
END;

✅ توصیه حرفه‌ای:

  • هیچوقت Statistics را دستی وارد نکن مگر دقیقاً بدان چه‌کاری می‌کنی.
  • جدول‌های پویا را حداقل هفته‌ای یک بار Gather کن.

۹. Histograms چیستند و چه زمانی باید استفاده کرد؟

Histogram‌ توزیع غیر یکنواخت داده را توصیف می‌کند.

اگر ستون جدولی مقادیر محدود زیادی دارد، Histogram به Optimizer کمک می‌کند تخمین Cardinality دقیق‌تر بدهد.

مثلاً وقتی ۹۰٪ کاربرها status='ACTIVE' هستند ولی بقیه مقادیر بسیار کم‌اند، بدون Histogram Optimizer نمی‌داند فعال بودن خیلی پر کاربرد است و شاید مسیر اشتباه انتخاب کند.

📘 مثال:

				
					EXEC DBMS_STATS.GATHER_TABLE_STATS(
  'HR', 'EMPLOYEES',
  METHOD_OPT => 'FOR COLUMNS SIZE 10 STATUS'
);

این دستور ۱۰ bucket برای ستون STATUS می‌سازد.

🔹 زمان مناسب برای استفاده از Histogram:

  • ستون‌هایی که توزیع غیر یکنواخت دارند.
  • ستون‌هایی که در شرط‌های WHERE زیاد استفاده می‌شوند.
  • ولی: نباید روی ستون‌هایی با مقادیر زیاد و پویایی بالا بسازی (زیاد تغییر می‌کنند)، چون Plan ناپایدار می‌شود.

📘 مثال عملکرد:

‌بدون Histogram:

				
					WHERE STATUS='ACTIVE'

ممکن است Full Scan انتخاب شود.

اما با Histogram، Oracle می‌فهمد ACTIVE خیلی زیاد است و شاید مسیر متفاوتی برود.

۱۰. Dynamic Sampling دقیقاً چه کمکی به بهینه‌سازی Queryها می‌کند؟

Dynamic Sampling یعنی Oracle در زمان اجرای Query، خودش نمونه‌ی کوچکی از داده می‌گیرد تا تخمین‌های بهتری از Cardinality داشته باشد.

مخصوصاً وقتی آمار (Statistics) وجود ندارد یا ناقص است.

🔹 سطح Sampling از ۰ تا ۱۰ قابل تنظیم است. سطح بالاتر یعنی نمونه‌گیری بیشتر.

📘 مثال:

				
					SELECT /*+ dynamic_sampling(4) */ * FROM SALES WHERE REGION='ASIA';

با Hint بالا، Optimizer قبل از اجرای کامل Query، چند بلوک را می‌خواند تا توزیع داده‌ها را بهتر تخمین بزند.

✅ استفاده در عمل:

  • برای Queryهای ad-hoc و پیچیده مفید است.
  • برای برنامه‌های روزمره‌ای که Statistics بروز دارند معمولاً لازم نیست.

۱۱. چطور بفهمیم Statistics اشتباه است؟

روش حرفه‌ای:

  1. بررسی تفاوت بین Estimated Rows و Actual Rows در Plan.اگر اختلاف زیاد است، آمار قدیمی یا نادرست است.
  2. بررسی ALL_TAB_STATS_HISTORY برای زمان آخرین Gather.

📘 مثال:

🔹 اگر آمار بیش از چند روز قدیمی است و داده تغییر کرده، باید بروزرسانی شود.

🧠 بخش ۳: Index و Access Path در SQL Tuning

۱۲. کدام نوع Index در Oracle سریع‌تر است؛ B-Tree یا Bitmap؟

هر Index برای هدف خاص طراحی شده:

سرعت کاربرد نوع ایندکس
بسیار سریع داده‌های یکتا / مرتب مثل ID, ACCOUNT_NO100,000 B-Tree
به‌صرفه در خواندن‌های گروهی داده‌های با تکرار زیاد مثل GENDER, STATUS Bitmap

📘 مثال:

برای جدول CUSTOMERS:

				
					CREATE INDEX IDX_ID ON CUSTOMERS(ID); ← B-Tree
CREATE BITMAP INDEX IDX_STATUS ON CUSTOMERS(STATUS); ← Bitmap

🔹 اگر Query شامل شرط‌های چندتایی مثل:

				
					SELECT * FROM CUSTOMERS WHERE STATUS='ACTIVE' AND GENDER='M';

Oracle می‌تواند Bitmapها را با هم ترکیب کند (Bitwise AND) و فقط رکوردهای دقیق را بخواند، بدون نیاز به Full Scan.

۱۳. چطور بفهمیم یک Index استفاده می‌شود یا نه؟

راه ساده: Execution Plan را نگاه کن.

اگر INDEX RANGE SCAN، INDEX UNIQUE SCAN یا INDEX FAST FULL SCAN دیدی یعنی ایندکس فعال شده.

📘 مثال:

				
					SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = 105;

در Plan این خط ظاهر می‌شود:

				
					TABLE ACCESS BY INDEX ROWID EMPLOYEES
INDEX UNIQUE SCAN EMPLOYEE_PK

🔹 روش دیگر:

				
					SELECT USED, OBJECT_NAME FROM V$OBJECT_USAGE WHERE TABLE_NAME='EMPLOYEES';

با دستور بالا می‌بینی از هر Index چه‌قدر استفاده شده.

✅ نکته طلایی:

Indexهای بلااستفاده را حذف کن چون فضای زیاد و زمان update اضافی مصرف می‌کنند.

۱۴. Index Scan یا Full Table Scan؟ کدام بهتر است؟

هیچ‌کدام همیشه بهتر نیستند! بستگی به میزان داده‌ی فیلتر شده دارد.

  • اگر شرط WHERE فقط بخش کوچکی از جدول را برمی‌گرداند → Index Scan بهتر.
  • اگر بیش از ۲۰–۳۰٪ جدول را نیاز داری → Full Scan سریع‌تر می‌شود.
  • اگر جدول خیلی کوچک است (زیر ۱۰۰۰ رکورد) → Full Scan همیشه بهتر است چون overhead ایندکس زیاد می‌شود.

📘 مثال:

جدول EMPLOYEES با ۱۰۰ رکورد:

				
					SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID IN (1,2,3,4);

Index Scan overhead دارد؛ Full Scan مستقیم و سریع‌تر خواهد بود.

۱۵. Index Skip Scan چیست و در چه شرایطی فعال می‌شود؟

وقتی Query فقط روی بخش اول Composite Index شرط ندارد ولی روی بخش دوم دارد، Oracle از تکنیک Skip Scan استفاده می‌کند.

📘 مثال:

Index ساخته‌ای روی (GENDER, SALARY)

Query زیر اجرا می‌کنی:

				
					SELECT * FROM EMP WHERE SALARY > ۱۰۰۰۰;

Oracle از Skip Scan استفاده می‌کند، یعنی Gender را ثابت می‌گیرد (“M” یا “F”) و برای هرکدام Salary را اسکن می‌کند.

این روش کندتر از Index روی Salary است ولی بهتر از Full Scan.

🔹 می‌توان Hint اضافه کرد:

				
					SELECT /*+ INDEX_SS(emp emp_gender_salary_idx) */ * FROM EMP WHERE SALARY > ۱۰۰۰۰;

۱۶. Composite Index چگونه باعث افزایش کارایی می‌شود؟

Composite Index ترکیبی از چند ستون است.

Oracle برای جستجوهای پیچیده، وقتی چند شرط در WHERE داری، می‌تواند با یک Index همه‌ی ستون‌ها را پوشش دهد.

📘 مثال:

				
					CREATE INDEX IDX_ORDER_CUST_DATE ON ORDERS(CUSTOMER_ID, ORDER_DATE);
SELECT * FROM ORDERS WHERE CUSTOMER_ID=500 AND ORDER_DATE='2025-12-09';

در این حالت Oracle دقیقاً چند رکورد مورد نظر را با یک Range Scan پیدا می‌کند.

✅ نکات طراحی:

  • ترتیب ستون‌ها مهم است: ستون انتخابی‌تر (Selective) را اول بگذار.
  • اگر شرط فقط روی ستون دوم یا سوم است، ممکن است بخش زیادی از Index خوانده شود (و Skip Scan رخ دهد).

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

در Oracle، وجود ایندکس به‌تنهایی تضمین استفاده از آن نیست. Optimizer بر اساس Cost تصمیم می‌گیرد که استفاده از ایندکس سودمند است یا نه.

اگر Cardinality (تعداد رکوردهای واجد شرایط) زیاد باشد، یا آمار (Statistics) نامناسب یا منقضی باشد، Optimizer ممکن است مسیر Full Table Scan را انتخاب کند چون هزینه‌ی خواندن متوالی کمتر از پرش‌های زیاد در Index است.

💡 برای اصلاح این وضعیت:

  • دستور DBMS_STATS.GATHER_TABLE_STATS روی جدول هدف اجرا کنید.
  • از Hint INDEX() برای اجبار استفاده از اندکس بهره ببرید.
  • بررسی کنید شرط WHERE قابلیت انتخاب (Selectivity) کافی دارد یا خیر.

Access Path روشیه که Oracle از طریق آن رکوردها رو پیدا می‌کنه.

  • INDEX UNIQUE SCAN: زمانی استفاده می‌شه که کوئری دقیقاً یک مقدار یکتا رو جستجو کنه (مثل Primary Key یا Unique Index).
  • INDEX RANGE SCAN: برای جستجوهای بازه‌ای (مثلاً WHERE salary > 10000) یا با رشته‌ای از مقادیر استفاده می‌شه.

RANGE SCAN معمولاً بیش از یک رکورد رو برمی‌گردونه و هزینه‌ی I/O بالاتری نسبت به UNIQUE SCAN داره.

🔸 نکته کلیدی: اگر در ستون‌های ایندکس شرط برابر استفاده کردی ولی مقدار یکتا نیست، Optimizer ممکنه به بازه‌ای بره نه یکتا.

Statistics قلب تصمیم‌گیری Optimizer است. بدون آمار دقیق:

  • Cardinality اشتباه برآورد می‌شود.
  • مسیر اشتباه (مثلاً Full Scan به‌جای Index Scan) انتخاب می‌شود.

Oracle از Statistics برای محاسبه‌ی Cost استفاده می‌کند تا کم‌هزینه‌ترین مسیر خواندن داده‌ها را برگزیند.

📊 توصیه حرفه‌ای:

  • بعد از حجم زیاد عملیات DML، با DBMS_STATS.GATHER_SCHEMA_STATS آمار کل اسکیمای خود را به‌روزرسانی کنید.
  • از پارامتر METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO' استفاده کنید تا هیستوگرام‌های هوشمند بسازد.
  • همیشه نسخه‌ی Statistics را در AWR یا Execution Plan مقایسه کنید.

سه مفهوم اصلی SQL Tuning مثل سه چرخ‌دنده به‌هم وصل‌اند:

  • Index تأثیر مستقیم بر نحوه‌ی اجرای کوئری دارد (راه میان‌بر داده‌ها).
  • Access Path به Optimizer می‌گوید از کدام مسیر وارد داده شود (INDEX RANGE، FULL SCAN، NESTED LOOP و …).
  • Statistics اطلاعات پایه‌ای است که بر اساس آن هزینه‌ی هر مسیر محاسبه می‌شود.

در نهایت در Execution Plan همه‌ی این تصمیم‌ها ثبت می‌شود. بنابراین تحلیل پلن اجرایی با EXPLAIN PLAN یا DBMS_XPLAN.DISPLAY_CURSOR دقیق‌ترین ابزار برای بررسی انتخاب‌های Optimizer است.

جمع‌بندی

SQL Tuning یعنی درک رفتار Optimizer، خواندن Execution Plan و اصلاح شرایطی که باعث Full Scan یا Access Path اشتباه می‌شود.

درک مفاهیمی مثل Cost، Cardinality و Statistics پایه‌ی تمام مباحث بعدی است.

Statistics پایه‌ی تمام تصمیم‌های Optimizer هستند.

اگر Statistics دقیق نباشد، هیچ Index یا Hintی نمی‌تواند کارایی واقعی بیاورد.

SQL Tuning بدون شناخت درست از Index یعنی تلاش کورکورانه.

B-Tree برای lookup‌های یکتا، Bitmap برای تحلیل‌های آماری، Composite برای کوئری‌های چندشرطی – و مهم‌تر از همه، Index بدون Statistics دقیق فایده ندارد.

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

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

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

میثم راد

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

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

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