سلام به همه‌ی برنامه نویس های عزیز 👋
اگه تاحالا با کوئری‌هایی سروکله زدی که کند و اعصاب‌خردکن هستن و هر کاری می‌کنی Query Plan مثل لاک‌پشت جلو میره، باید بدونی وقت استفاده از پکیج جادویی DBMS_SQLTUNE هست!
این ابزار رسمی Oracle Database از نسخه‌ی ۱۰g معرفی شد و الان توی نسخه‌های ۱۹c و ۲۳ai هم جزو اجزای حیاتی Performance Tuning حساب میشه.
در ابن مقاله آموزش Oracle در بخش آموزش پکیج های اوراکلی بریم با یه مثال واقعی یاد بگیریم چطور باهاش یه کوئری کند رو درمان کنیم.
در دنیای امروزی که ارتباطات نرم‌افزارها از طریق شبکه حرف اول را می‌زند، اوراکل هم برای توسعه‌دهندگانی که به ارتباطات سطح پایین نیاز دارند، ابزار قدرتمندی در اختیار گذاشته: پکیجی به نام UTL_TCP.
پیشنهاد می کنم این مقاله زیر رو حتما مطالعه کنی.
در این مقاله شما می خوانید
فرآیندها و توابع پکیج DBMS_SQLTUNE در اوراکل
| عمل | تابع / پروسیجر | توضیح کوتاه |
|---|---|---|
| ایجاد Task | CREATE_TUNING_TASK |
ایجاد جلسه تحلیل SQL برای بررسی عملکرد. |
| اجرای Task | EXECUTE_TUNING_TASK |
اجرای تحلیل و شروع فرآیند Tuning. |
| گزارش | REPORT_TUNING_TASK |
مشاهده گزارش نهایی SQL Tuning Advisor. |
| حذف | DROP_TUNING_TASK |
حذف Taskهای قدیمی از SYSAUX Repository. |
| بررسی وضعیت | GET_TUNING_TASK_STATUS |
نمایش وضعیت فعلی Task (Completed, Running,...) |
| اعمال SQL Profile | ACCEPT_SQL_PROFILE |
اعمال توصیه SQL Profile برای Query بهینه‌تر. |
| اجرای سریع | TUNE_SQL |
ساخت و اجرای خودکار Tuning Task در یک مرحله. |
🎯 سناریوی واقعی: کوئری کند در سیستم بیمه
فرض کن توی سیستم بیمه یه گزارش داریم که اطلاعات از جدول CLAIM_HISTORY میاره و همیشه کند اجرا میشه.
ما فقط SQL ID اون رو داریم و قراره به کمک پکیج DBMS_SQLTUNE آنالیزش کنیم.
🔹 مرحله ۱: ساخت Task برای SQL Tuning
DECLARE
v_task VARCHAR2(100);
BEGIN
v_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => '۴۰q2m9p1z6dqw',
scope => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
time_limit => ۱۲۰,
task_name => 'claim_tune_task',
description => 'Tuning report for slow insurance claim query');
DBMS_OUTPUT.PUT_LINE('Created tuning task: ' || v_task);
END;
/
🔸 این کد درواقع یه پرونده‌ی «تحلیل عملکرد» ایجاد می‌کنه. پارامتر SCOPE_COMPREHENSIVE باعث میشه Advisor همه‌چیز از Statistics تا Index و Profile رو بررسی کنه.
🔹 مرحله ۲: اجرای Task
EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK('claim_tune_task');
این دستور تحلیل رو اجرا می‌کنه. معمولاً بسته به پیچیدگی کوئری، بین چند ثانیه تا چند دقیقه طول می‌کشه.
🔹 مرحله ۳: مشاهده گزارش SQL Tuning Advisor
SET LONG 10000
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('claim_tune_task') AS report FROM dual;
نمونه‌ی خلاصه خروجی:
۱. Statistics Finding:
Missing or stale optimizer statistics on CLAIM_HISTORY.
۲. Index Finding:
۳. SQL Profile Finding:
🔹 مرحله ۴: اعمال SQL Profile توصیه‌شده
EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE('claim_tune_task', 'CLAIM_SQL_PROFILE');
با اجرای این دستور، SQL Optimizer از این به بعد Plan بهینه‌تری انتخاب می‌کنه، بدون اینکه حتی یک خط از کد برنامه‌ات تغییر کنه.
🔹 مرحله ۵: حذف Task (مرحله پاکسازی)
EXEC DBMS_SQLTUNE.DROP_TUNING_TASK('claim_tune_task');
💡 بررسی SQL Profile جدید
SELECT NAME, STATUS FROM DBA_SQL_PROFILES WHERE NAME='CLAIM_SQL_PROFILE';
نتیجه:
NAME STATUS
------------------ --------
CLAIM_SQL_PROFILE ENABLED
به همین راحتی، اجرای کوئری از ۳۰ ثانیه رسیده به کمتر از ۵ ثانیه!
بدون تغییر در کد، فقط با قدرت DBMS_SQLTUNE 🔥
👨🔧 خلاصهٔ حرفه‌ای برای DBAها
CREATE_TUNING_TASK→ ساخت جلسه‌ی تحلیلEXECUTE_TUNING_TASK→ اجرای آنالیزREPORT_TUNING_TASK→ تولید گزارش توصیه‌هاACCEPT_SQL_PROFILE→ اعمال توصیه‌هاDROP_TUNING_TASK→ تمیزکاری و حذف Task
⚡ نکات طلایی تجربه‌ای
- از
TUNE_SQLبرای تست سریع Queryها در محیط تست استفاده کن. - همیشه قبل از اعمال SQL Profile در محیط Production، خروجی Plan جدید رو با
DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINEمقایسه کن. - فراموش نکن DBMS_SQLTUNE دیتاها رو در SYSAUX tablespace ذخیره می‌کنه؛ پس مراقب حجمش باش.
سوالات متداول درباره پکیج DBMS_SQLTUNE در اوراکل
پکیج DBMS_SQLTUNE در واقع رابط PL/SQL برای SQL Tuning Advisor است.
یعنی هر کاری که SQL Tuning Advisor از طریق Enterprise Manager انجام میدهد، با DBMS_SQLTUNE میتوان از طریق کدنویسی کنترل کرد.
با این پکیج میتوانی Task بسازی، آن را اجرا کنی، گزارش بگیری و حتی SQL Profile را مستقیم Apply کنی.
بنابراین DBMS_SQLTUNE ابزاری برای اتوماتسازی و اجرای دستوری SQL Tuning است، درحالیکه SQL Tuning Advisor رابط گرافیکی همان فرآیند محسوب میشود.
SQL Profile در اصل یک مجموعه Optimizer Hint نیمهخودکار است که رفتار Cost-Based Optimizer را اصلاح میکند.
یعنی به جای تغییر در ساختار پایگاه داده، به Optimizer کمک میکند تصمیم بهتری برای Execution Plan بگیرد.
اما SQL Profile جایگزین ایندکس نیست. اگر Bottleneck از نوع Access Path باشد، باید ایندکس فیزیکی بسازی.
بهترین روش این است که ابتدا خروجی DBMS_SQLTUNE را بررسی و اگر پیشنهاد ساخت Index داده بود، آن را اعمال کنی؛ سپس در مرحلهی بعد SQL Profile را فعال نگه داری.
پارامتر scope مشخص میکند Oracle چه سطحی از تحلیل را انجام دهد:
SCOPE_LIMITED: بررسی سادهی آمار و Plan فعلی. سریعتر ولی کمعمق.SCOPE_COMPREHENSIVE: تحلیل پیشرفته شامل پیشنهاد ایندکس، جمعآوری آمار جدید، تست SQL Profile و حتی شبیهسازی Planهای جایگزین.
اگر هدفت تحلیل عمیق یک Query مهم یا Production باشد، COMPREHENSIVE انتخاب بهتری است (البته با هزینه زمانی و CPU بیشتر).
برای بررسی اینکه آیا SQL Profile روی کوئری خاص فعال شده، میتوانی از این Query استفاده کنی:
SELECT sql_profile, sql_text
FROM v$sql
WHERE sql_id = ’40q2m9p1z6dqw’;
اگر ستون SQL_PROFILE مقدار داشت (مثلاً ‘CLAIM_SQL_PROFILE’)، یعنی به صورت موفق روی Plan فعلی اعمال شده.
همچنین جدول DBA_SQL_PROFILES تمام Profileهای فعال را نمایش میدهد و میتوانی ببینی آیا حالت ENABLED دارند یا نه.
جمع‌بندی
پکیج DBMS_SQLTUNE یه ابزار ساده ولی فوق‌العاده عمیقه. به جای حدس زدن در مورد ایندکس، Optimizer خودش بر اساس آمار و Cost واقعی تصمیم می‌گیره.
اگه یه DBA حرفه‌ای هستی یا تازه تو دنیای Performance Tuning وارد شدی، یاد گرفتن این پکیج مثل افزایش قدرت دید در Matrixه؛ همه‌چیز واضح‌تر میشه! 🎬
📥 اگر سوالی داری در مورد پکیج DBMS_SQLTUNE در اوراکل داری، در بخش کامنت‌ها بپرس.
دیدگاهتان را بنویسید