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

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

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

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

🧰 بخش ۷: ابزارهای SQL Tuning در Oracle

۲۹. SQL Tuning Advisor چیست و چگونه عمل می‌کند؟

SQL Tuning Advisor یکی از قوی‌ترین ابزارهای خودکار بررسی عملکرد Query در Oracle است.

این ابزار کوئری‌ها را می‌گیرد، Planهای متفاوت را بررسی می‌کند و پیشنهادهایی دقیق می‌دهد مثل:

  • ایجاد Index جدید
  • بازنویسی Query
  • جمع‌آوری آماری جدید
  • پیشنهاد SQL Profile

📘 مثال:

				
					EXEC DBMS_SQLTUNE.CREATE_TUNING_TASK(
  sql_text => 'SELECT * FROM SALES WHERE REGION=''IRAN'' AND AMOUNT>۱۰۰۰',
  user_name => 'HR',
  task_name => 'TUNE_SALES_QUERY'
);

EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK('TUNE_SALES_QUERY');
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNE_SALES_QUERY') FROM DUAL;

📋 خروجی: پیشنهادهای خودکار برای Index، Statistics یا حتی تغییر Plan.

✅ توصیه:

پیشنهادهای Advisor همیشه معتبرند اما قبل از اجرا در سیستم تولید (Production)، در محیط تست امتحانشان کن.

۳۰. SQL Access Advisor چه تفاوتی با SQL Tuning Advisor دارد؟

درحالی‌که Tuning Advisor تمرکزش روی یک Query است، Access Advisor نگاه سیستمی دارد؛ یعنی تمام Workload (کوئری‌های واقعی در یک بازه‌ی زمانی) را تحلیل می‌کند تا پیشنهاد بدهد:

  • کدام Indexها ساخته یا حذف شوند
  • کدام Materialized View ایجاد شود
  • و حتی کدام ایندکس‌ها بی‌استفاده‌اند

📘 مثال:

				
					EXEC DBMS_ADVISOR.CREATE_TASK('SQL Access Advisor','ACCESS_TASK');
EXEC DBMS_ADVISOR.EXECUTE_TASK('ACCESS_TASK');
SELECT DBMS_ADVISOR.GET_TASK_REPORT('ACCESS_TASK') FROM DUAL;

✅ نتیجه:

Access Advisor در سیستم‌های OLTP بزرگ زمان زیادی ذخیره می‌کند و برای طراحی ساختار ایندکس و MView حیاتی است.

۳۱. SQL Plan Baseline چیست و چه کمکی به پایداری اجرای Query می‌کند؟

SQL Plan Baseline ابزاری برای تثبیت Plan است. یعنی اگر Oracle در آینده Plan بهتری پیدا کند ولی آن، عملکرد قبلی را خراب کند، Baseline جلوی تغییر را می‌گیرد.

📘 مثال:

				
					EXEC DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
  sql_id => '۴f7n3s0abcxyz'
);

از این لحظه Oracle فقط از همون Plan ثابت‌شده استفاده می‌کند مگر اینکه Plan جدید به‌صورت Accepted mark بشود.

✅ کاربرد واقعی:

در محیط Production که اجرای یک Query نباید بین شب و روز تغییر کند.

۳۲. SQL Profile و SQL Patch چه هستند و چه تفاوتی دارند؟

SQL Patch SQL Profile Feature
تزریق Hint بدون تغییر Query اصلاح تخمین‌های Optimizer هدف
دستی توسط DBA خودکار توسط Tuning Advisor استفاده
اجبار مسیر اجرای خاص دقیق‌تر شدن Cardinality عملکرد

📘 مثال SQL Patch:

				
					BEGIN
  DBMS_SQLDIAG.CREATE_SQL_PATCH(
    sql_id => '۴abcdn6v0x2p',
    hint_text => 'USE_HASH(emp)',
    name => 'patch_emp_hash'
  );
END;

✅ نتیجه:

SQL Patch ابزاری رؤیایی است برای DBAها وقتی کد اپلیکیشن قابل ویرایش نیست.

۳۳. AWR (Automatic Workload Repository) چیست و چه کاربردی در SQL Tuning دارد؟

AWR درواقع مغز گزارش عملکرد سیستم است.

هر ساعت داده‌های عملکردی (CPU، Wait Event، I/O، SQLهای سنگین و …) را ذخیره می‌کند.

📘 دسترسی سریع:

				
					@$ORACLE_HOME/rdbms/admin/awrrpt.sql

📋 در خروجی:

  • Top SQL by Elapsed Time
  • Top SQL by Buffer Gets
  • Wait Class Statistics
  • I/O و Memory Profile

✅ هدف در SQL Tuning:

پیدا کردن Queryهایی که بیشترین منابع را می‌خورند، سپس تمرکز روی همان‌ها برای Tuning.

۳۴. ASH (Active Session History) چه تفاوتی با AWR دارد؟

در حالی‌که AWR وضعیت کلی را در بازه‌های ۱ ساعته ذخیره می‌کند، ASH لحظه‌به‌لحظه رفتار Sessionهای فعال را ثبت می‌کند (هر ثانیه چند Snapshot).

📘 مثال:

				
					SELECT * FROM V$ACTIVE_SESSION_HISTORY WHERE SQL_ID='f8s9x03a1na9';

✅ کاربرد:

وقتی یک Query اکنون کند شده و می‌خواهی بفهمی دقیقاً در همان لحظه چه اتفاقی برایش افتاده (در حال Wait، CPU یا I/O).

۳۵. Execution Monitor در Enterprise Manager چه امکاناتی می‌دهد؟

در Oracle Enterprise Manager (OEM)، ابزار SQL Monitor به صورت گرافیکی Plan واقعی، مصرف منابع، زمان Stepها و Percentage Each Step را در لحظه نشان می‌دهد.

📊 در داشبورد SQL Monitoring می‌بینی:

  • هر Node چند رکورد واقعی پردازش کرده
  • چقدر زمان برده
  • I/O و Memory هر Stage چقدر بوده

✅ نکته کاربردی:

اگر اختلاف بین E-Rows و A-Rows زیاد است، معمولاً آمار ناقص است یا نوع Join اشتباه انتخاب شده.

🧮 بخش ۸: AI و Tuning خودکار در Oracle 23ai

۳۶. AI-driven SQL Tuning در Oracle 23ai چیست؟

در Oracle 23ai، الگوریتم‌های machine learning رفتار اجرای کوئری‌ها را در طول زمان ثبت می‌کنند و به‌صورت خودکار پیشنهاد Index, Plan یا Rewrite می‌دهند.

📗 موتور AI Tuning با AWR و ASH مرتبط است و به جای پیشنهاد دستی، خودش اصلاحات را تست و validate می‌کند.

✅ مثال واقعی در ۲۳ai:

سیستم متوجه می‌شود Query با Full Scan زیاد تکرار می‌شود → خودکار پیشنهاد می‌دهد یک Bitmap Index بسازی.

۳۷. AutoIndexing چطور عمل می‌کند و آیا ایمن است؟

AutoIndex یکی از هوشمندترین ویژگی‌هاست. Oracle خودش با تحلیل کوئری‌ها ایندکس‌هایی آزمایشی (Invisible) می‌سازد، سپس بین چند روز آن‌ها را تست و در صورت کارایی بهتر، پایدار می‌کند.

📘 بررسی وضعیت AutoIndex:

				
					SELECT * FROM DBA_AUTO_INDEX_CONFIG;

✅ مزایا:

  • بر اساس workload واقعی عمل می‌کند
  • ایندکس‌های ناکارآمد حذف می‌شوند
  • بدون دخالت DBA

⚠️ نکته مهم:

در Data Warehouse فوق‌العاده مفید است، ولی در سیستم‌های OLTP با حجم بالای DML ممکن است هزینهٔ update بالا برود.

۳۸. Real-Time SQL Monitoring چطور به DBAها در تولید کمک می‌کند؟

RTSM در Oracle Enterprise Edition اجازه می‌دهد در همان لحظه اجرای Query، مصرف منابع را ببینی.

📘 مثال دستی:

				
					SELECT * FROM V$SQL_MONITOR WHERE STATUS='EXECUTING';

می‌توانی بفهمی کدام بخش در حال اجرای طولانی است (مثلاً Hash Join stage یا Sort operation).

✅ مناسب برای گزارش‌های طولانی (Batch) و مانیتور زنده در سیستم‌های مالی.

۳۹. SQL Quarantine Zone چیست و چه کاربردی دارد؟

ویژگی ۲۳c/23ai

Oracle وقتی یک SQL Plan بسیار بد اجرا شود (منجر به مصرف بیش از حد CPU یا Elapsed Time طولانی)، آن Plan را Quarantine می‌کند یعنی اجازه‌ی اجرا با آن Plan را نمی‌دهد.

📘 Query نمونه:

				
					SELECT * FROM DBA_SQL_QUARANTINE;

✅ نتیجه: جلوگیری خودکار از Planهای فاجعه‌بار؛ حفاظت هوشمند در برابر خطای انسانی یا آمار اشتباه.

🧩 بخش ۹: سناریوهای واقعی SQL Tuning (Case Studies)

۴۰. وقتی Query در محیط تست سریع ولی در Production کند اجرا می‌شود، چه کنیم؟

دلایل محتمل:

  1. تفاوت حجم داده
  2. آمار نابرابر (Statistics قدیمی)
  3. تفاوت DB_FILE_MULTIBLOCK_READ_COUNT یا OPTIMIZER_MODE
  4. ایندکس غیرفعال

✅ راه‌حل‌ها:

  • بررسی Statistics با DBMS_STATS.DIFF_TABLE_STATS
  • بررسی Plan تست با عملیاتی با DBMS_XPLAN.DISPLAY_CURSOR
  • بازسازی Plan Baseline در محیط Production

۴۱. وقتی Index جدید ساختیم و Query کند شد، چرا؟

گاهی Oracle Index جدید را بر اساس هزینه انتخاب می‌کند اما ریخت داده‌ها متفاوت است.

📘 سناریو:

Index جدید ساخته‌ای روی چند ستون ولی Selectivity پایینی دارد → باعث شده Optimizer از آن استفاده کند ولی نتیجه برعکس شده.

✅ راه‌حل:

  • بررسی Cost واقعی (A-Rows vs E-Rows)
  • در صورت نیاز Hint NO_INDEX
  • یا حذف ایندکس بی‌اثر

۴۲. وقتی Join سه یا بیش از سه جدول داریم، چگونه تشخیص دهیم ترتیب بهینه است؟

Oracle ممکن است ترتیب Join را اشتباهاً انتخاب کند (مثلاً جدول بزرگ اول).

📘 راهکار:

  • بررسی ترتیب واقعی در Plan
  • یا استفاده از Hint LEADING

📘 مثال:

				
					SELECT /*+ LEADING(a b c) */ * FROM a,b,c
WHERE a.id=b.id AND b.id=c.id;

✅ قانون کلی:

همیشه جدول کوچک‌تر را در ابتدا Join کن اگر Nested Loop استفاده می‌شود.

۴۳. وقتی Query با Subquery بسیار کند است، بهترین روش Rewrite چیست؟

راهکارهای Rewrite کلاسیک:

  1. تبدیل Subquery به Join (Unnesting)
  2. حذف DISTINCT تودرتو
  3. تبدیل Grouped Subquery به Inline View با Aggregation

📘 مثال:

کند:

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

سریع‌تر:

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

۴۴. بیشترین اشتباهات DBA‌ها در SQL Tuning چیست؟

۱. نداشتن Statistics به‌روز

۲. ساخت ایندکس بدون تحلیل کاربری

۳. تکیه زیاد بر Hintها

۴. رها کردن Queryهای تکرار‌شونده بدون Baseline

۵. نداشتن روال بررسی AWR منظم

✅ DBA حرفه‌ای هر هفته:

  • Top SQLهای AWR رو مرور می‌کنه
  • Diff Statistics می‌گیره
  • و Planهای تغییر یافته رو مقایسه می‌کنه

۴۵. استراتژی نهایی SQL Tuning در Oracle چیست؟

ترتیب طلایی بهینه‌سازی Query‌ها در Oracle:

  1. بررسی Execution Plan فعلی
  2. جمع‌آوری Statistics و بررسی Histograms
  3. تحلیل شاخص‌های موجود (Index Usage)
  4. بازنویسی Query ساده‌تر
  5. اعمال Hint در صورت نیاز واقعی
  6. مانیتور واقعی با DBMS_XPLAN یا SQL Monitor
  7. تثبیت Plan نهایی با SQL Baseline یا Profile
  8. ثبت در مستندات تیمی + تحلیل در AWR

📗 هدف نهایی:

تبدیل Tuning از یک واکنش لحظه‌ای به یک فرایند مداوم، علمی و خودایستا.

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

در Oracle 23ai، Optimizer از حالت صرفاً Rule/Cost‑Based کلاسیک عبور کرده و به یک AI‑Assisted Optimizer تبدیل شده است.

AI Optimizer با استفاده از:

  • الگوهای واقعی اجرای Queryها
  • رفتارهای گذشته (Execution History)
  • اختلاف Estimated Plan و Actual Plan

می‌تواند:

  • Execution Plan بهتر پیشنهاد دهد
  • Cardinality Estimation را اصلاح کند
  • Query Rewrite هوشمند انجام دهد

📌 نکته مهم:

این قابلیت‌ها داخل خود دیتابیس و کاملاً آفلاین اجرا می‌شوند و نیازی به سرویس خارجی ندارند.

AI در Oracle 23ai چند بخش اصلی SQL Tuning را خودکار یا نیمه‌خودکار انجام می‌دهد:

  • 🔍 AI Plan Analysis

    شناسایی Execution Planهای پرهزینه و غیرعادی

  • 🧠 AI Query Rewrite

    بازنویسی هوشمند Query بدون تغییر خروجی

  • 📊 AI Cardinality Correction

    اصلاح تخمین تعداد Rowها به‌صورت Adaptive

  • 🗝 AI Index Advice

    پیشنهاد Index مؤثر بر اساس Workload واقعی

  • 🧩 AI Hinting (داخلی)

    استفاده از Hintهای سیستمی بدون نیاز به Hint دستی

✅ نتیجه:

کاهش نیاز DBA به Tuning دستی برای Queryهای پرتکرار

❌ کاملاً نه

✅ ولی حجم کار DBA را به‌شدت کم می‌کند

AI در این موارد عالی عمل می‌کند:

  • OLTP Queryهای پرتکرار
  • Workloadهای پایدار
  • SQLهایی که مشکل Cardinality دارند

اما DBA هنوز لازم است برای:

  • معماری داده
  • Partitioning Strategy
  • SQLهای Business‑Critical
  • تحلیل AWR و ASH
  • تصمیم‌گیری نهایی روی Index و Hints

📌 جمع‌بندی:

Oracle 23ai DBA را حذف نمی‌کند،

بلکه DBA را از SQL Tuner دستی به SQL Tuning Architect تبدیل می‌کند.

✅ ابزارهای کلیدی:

  • DBMS_XPLAN (DISPLAY_CURSOR)

    تحلیل Execution Plan واقعی (Actual Plan)

  • SQL Tuning Advisor (DBMS_SQLTUNE)

    بررسی Query و پیشنهاد Index، Rewrite و Profile

  • SQL Plan Management (SPM)

    تثبیت Execution Planهای خوب

  • Automatic Indexing

    ایجاد و حذف خودکار Indexها با AI

  • AWR و ASH Reports

    تحلیل Workload و Bottleneck سیستم

  • AI Optimizer Features در ۲۳ai

    (Query Rewrite، Plan Correction، Index Advice)

✅ همه این‌ها با هم، یک SQL Tuning هوشمند و نیمه‌اتوماتیک می‌سازند.

جمع‌بندی

SQL Tuning یعنی توازن بین علم، تجربه و رفتار داده‌ها.

در Oracle هیچ کوئری «بد» وجود ندارد، فقط آمار نادرست یا مسیر اشتباه انتخاب‌شده وجود دارد.

🔹 اگر می‌خواهی Query همیشه سریع بماند:

Statistics را خودکار جمع‌آوری کن.

AWR و ASH را منظم مرور کن.

Planهای شخصی‌سازی‌شده را ثبت کن.

و از Oracle 23ai بخواه با هوش مصنوعی همراهت باشد.

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

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

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

میثم راد

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

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

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