اگر مدتی با Oracle کار کرده باشی، احتمالاً این سناریو برات آشناست:
«همین SQL دیروز عالی اجرا میشد،
امروز بدون هیچ تغییری افتضاح کُند شده!»
اینجاست که خیلیها اشتباه میکنند و فکر میکنند:
- باید SQL رو Rewrite کنیم
- یا حتماً Index کم داریم
در حالی که بیش از ۷۰٪ مشکلات Performance، ریشهشان جای دیگری است:
- Optimizer تصمیم اشتباه گرفته
- Statistics غلط یا قدیمی است
- Execution Plan عوض شده
- یا دیتابیس از نظر Memory و I/O تحت فشار است
SQL Tuning در Oracle یعنی دیدن این واقعیتها از طریق فایلها و ابزارهای درست.
در این مقاله آموزش اوراکل در بخش آموزش بهینه سازی کوئری SQL Tuning ،قراره دقیقاً همین فایلها رو عمیق، کاربردی و با مثال واقعی بررسی کنیم.
اگر در دنیای مدیریت پایگاه دادههای اوراکل فعالیت کرده باشی، حتماً نام AWR Report یا همان Automatic Workload Repository به گوشت خورده است. پیشنهاد می کنم این مقاله زیر رو حتما مطالعه کنی.
در این مقاله شما می خوانید
SQL Tuning یعنی:
پیدا کردن پایدارترین و کمهزینهترین Execution Plan
نه فقط سریع کردن موقت یک Query
یک DBA حرفهای:
- حدس نمیزند
- بر اساس داده تصمیم میگیرد
- و میداند کِی سراغ کدام فایل برود
Alert Log اولین جایی است که باید بررسی شود، حتی قبل از Execution Plan.
Alert Log چه اطلاعاتی میدهد؟
- خطاهای Optimizer
- کمبود Shared Pool
- شکست Gather Statistics
- Invalid شدن Cursorها
- Memory Resizeهای مشکوک
مثال واقعی
کاربر گزارش میدهد SQLها ناگهان کند شدهاند.
در Alert Log میبینیم:
ORA-04031: unable to allocate memory in shared pool
🔍 تحلیل:
- Cursorها مدام از Shared Pool خارج میشوند
- Hard Parse زیاد شده
- Execution Plan ناپایدار است
✅ نتیجه:
SQL مشکل ندارد؛ مشکل Memory و Shared Pool است.
Trace دقیقترین ابزار SQL Tuning است، چون Oracle دقیقاً میگوید چه کرده.
Trace 10046 (مهمترین Trace)
فعالسازی:
ALTER SESSION SET events '10046 trace name context forever, level 12';
Level 12 شامل:
- Wait Events
- Bind Variables
مثال
در Trace میبینیم:
WAIT: db file scattered read
🔍 تحلیل:
- Full Table Scan
- I/O سنگین
- Index استفاده نشده
✅ بدون حدس، با مدرک.
TKPROF Trace خام را به گزارشی قابلدرک تبدیل میکند.
چه چیزهایی در TKPROF Trace خیلی مهماند؟
- Elapsed Time
- CPU Time
- Disk Reads
- Rows Processed
- Execution Plan واقعی
مثال
Rows Returned: 20
Disk Reads: 150,000
🔍 یعنی:
- Oracle برای ۲۰ رکورد
- ۱۵۰ هزار بلاک خوانده
✅ مشکل Selectivity یا طراحی Index کاملاً واضح است.
AWR یکی از مهمترین ابزارهای SQL Tuning است، چون گذشته را نشان میدهد.
AWR چه چیزهایی را مشخص میکند؟
- Top SQLs
- SQL Regression
- Execution Plan History
- Bottleneckهای سیستم
مثال
SQL دیروز ۱ ثانیه اجرا میشده، امروز ۳۰ ثانیه.
در AWR:
- Plan Hash Value تغییر کرده
✅ یعنی Regression اتفاق افتاده و باید Plan قبلی بررسی یا Fix شود.
ADDM با استفاده از AWR، مشکلات اصلی دیتابیس را اولویتبندی میکند.
مثال
Finding: SQL statements consuming excessive I/O
Recommendation: Create index on ORDERS(CUST_ID)
✅ مناسب برای:
- دید مدیریتی
- تشخیص نقاط بحرانی
❌ ولی جای تحلیل DBA را نمیگیرد.
SQL Monitor برای SQLهای:
- سنگین
- طولانی
- Parallel
استفاده میشود.
مثال
در SQL Monitor میبینیم:
- Hash Join = ۸۰٪ زمان
- Table Driving اشتباه انتخاب شده
✅ دقیقاً مشخص میشود گلوگاه کجاست.
STS مجموعهای از:
- SQL Text
- Bind Values
- Execution Plan
- Runtime Statistics
کاربرد حیاتی
- قبل از Upgrade
- قبل از تغییر Optimizer
- مقایسه Performance قبل و بعد
Optimizer آزاد است Plan را تغییر دهد؛
Baseline میگوید:
«Plan خوب رو خراب نکن»
مثال
بعد از Gather Stats:
- Oracle میخواهد Full Scan بزند
- Baseline اجازه نمیدهد
✅ نجات محیط عملیاتی.
Optimizer Statistics | غذای مغز Optimizer
بدون آمار درست، Optimizer تصمیم درست نمیگیرد.
مثال
ستون STATUS:
- A = ۹۰٪
- I = ۱۰٪
بدون Histogram:
- Oracle تصور ۵۰/۵۰ دارد
✅ نتیجه: Join و Index انتخاب اشتباه.
Redo Log | قاتل خاموش Performance
Redo مستقیماً SQL نیست، ولی اثرش واضح است.
مثال
در AWR:
log file sync = 40% DB Time
✅ یعنی:
- Commit زیاد
- Bottleneck روی Redo
- SQL بیگناه است
Control File | ستون فقرات تحلیل
بدون Control File:
- AWR نداریم
- History نداریم
- SQL Tuning ناقص است
Diagnostic Files (XML / TRM)
اینها مخصوص:
- Bugهای Optimizer
- Incident Analysis
- ارتباط با Oracle Support
سوالات متداول درباره SQL Tuning در اوراکل
بهترین نقطه شروع SQL Tuning در Oracle، همیشه Alert Log و AWR Report است، نه Execution Plan.
Alert Log خیلی زود مشکلاتی مثل کمبود Shared Pool، خطاهای Optimizer یا شکست Gather Statistics را نشان میدهد.
بعد از آن، AWR کمک میکند بفهمیم:
- کدام SQL واقعاً مشکلساز است
- کندی از چه زمانی شروع شده
- آیا Execution Plan تغییر کرده یا نه
✅ اگر بدون دیدن این دو سراغ Rewrite SQL بروید، معمولاً فقط وقت تلف کردهاید.
AWR و SQL Trace مکمل هم هستند، نه جایگزین هم.
- AWR برای دید کلی دیتابیس و شناسایی SQLهای پرهزینه در بازه زمانی استفاده میشود.
- SQL Trace 10046 برای بررسی دقیق یک SQL خاص، شامل Wait Eventها، Bind Variableها و مسیر واقعی اجراست.
✅ به زبان ساده:
- اگر هنوز نمیدانید مشکل کجاست → AWR
- اگر دقیقاً میدانید کدام SQL مشکل دارد → Trace 10046 + TKPROF
تغییر Execution Plan معمولاً یکی از این دلایل را دارد:
- Gather Statistics جدید
- تغییر Data Volume جدولها
- تغییر Optimizer Parameters
- Upgrade دیتابیس
- Aging شدن Cursor در Shared Pool
در بسیاری از این موارد، Oracle فکر میکند Plan جدید بهینهتر است، ولی در Production نتیجه برعکس میشود.
✅ راهحل حرفهای جلوگیری از این مشکل، استفاده از SQL Plan Baseline است.
خیر؛ و این یکی از رایجترین اشتباههاست.
ایندکس ساختن همیشه راهحل نیست، چون:
- ممکن است مشکل از Statistics باشد
- یا SQL بهدلیل Bind Peeking اشتباه اجرا شود
- یا Bottleneck اصلاً از I/O و Redo باشد، نه Access Path
✅ DBA حرفهای قبل از ساخت Index:
- SQL Monitor
- Trace 10046
- و Execution Plan واقعی را بررسی میکند
جمعبندی | مسیر درست SQL Tuning
ترتیب منطقی SQL Tuning:
- Alert Log
- AWR
- SQL Monitor
- Trace + TKPROF
- Statistics و Execution Plan
- SQL Plan Baseline
SQL Tuning یعنی تحلیل با عقل و داده، نه حدس.
📥 اگر سوالی داری در مورد SQL Tuning در Oracle داری، در بخش کامنتها بپرس.
سؤالی درباره این مقاله داری؟
اگر نکتهای در این مقاله برات مبهم بود یا خواستی بیشتر بدونی، همین حالا برام بنویس تا دقیق و صمیمی پاسخت رو بدم — مثل یه گفتوگوی واقعی 💬
برو به صفحه پرسش و پاسخ
دیدگاهتان را بنویسید