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

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

آموزش پکیج DBMS_SQLDIAG در اوراکل – تشخیص و رفع مشکلات عملکرد SQL به صورت حرفه‌ای

آموزش-پکیج-DBMS_SQLDIAG-در-اوراکل

در پایگاه داده Oracle، ابزارهای متنوعی برای بهبود عملکرد کوئری‌ها وجود دارد.

اما یکی از کمتر شناخته‌شده‌ترین و در عین حال قدرتمندترین ابزارها، پکیج DBMS_SQLDIAG است.

این پکیج مثل یک پزشک هوشمند برای SQL عمل می‌کند؛ مشکل را تشخیص می‌دهد، گزارش دقیق تولید می‌کند و حتی می‌تواند سناریوی کندی را به شکل تست کیس بازسازی کند.

در این مقاله آموزش Oracle در بخش آموزش پکیج های اوراکلی در این پکیج DBMS_SQLDIAG صحبت خواهیم کرد. 

پکیج DBMS_XPLAN ابزاری رسمی و بسیار توانمند در Oracle برای نمایش و تحلیل Execution Plan محسوب می‌شود. پیشنهاد می کنم این مقاله زیر رو حتما مطالعه کنی.

در این مقاله شما می خوانید

🎯 هدف و کاربرد اصلی پکیج DBMS_SQLDIAG

در واقع DBMS_SQLDIAG بخش مهمی از SQL Diagnostic Framework در اوراکل است و چند قابلیت اصلی دارد:

قابلیت توضیح کاربردی
تحلیل SQL شناسایی علت کند بودن یا رفتار غیرمنتظره دستور SQL
تولید گزارش ارائه توصیه‌ها برای رفع مشکلات عملکردی
ساخت تست کیس بازسازی سناریوی واقعی برای بررسی در محیط دیگر
پاکسازی Taskها حذف تسک‌های قبلی جهت مدیریت فضای Advisor
پیوند با SQL Tuning اتصال مستقیم به DBMS_SQLTUNE جهت بهینه‌سازی خودکار

اگر بخواهیم خلاصه بگوییم، این پکیج ابزار اصلی DBAها برای شناسایی ریشه مشکلات Performance است.

⚙️ پیش‌نیاز اجرا پکیج DBMS_SQLDIAG

برای استفاده از این پکیج باید مجوزهای زیر را به کاربر مورد نظر بدهی:

				
					GRANT ADVISOR TO user_name;
GRANT ADMINISTER SQL TUNING SET TO user_name;

این دستورات، دسترسی لازم برای ساخت و اجرای تسک‌های تشخیص SQL را فعال می‌کنند.

🔍 نحوه تحلیل کندی یک کوئری با پکیج DBMS_SQLDIAG

فرض کن در جدول EMPLOYEES یک کوئری کند داری:

				
					SELECT employee_id, first_name, last_name, department_id
FROM employees
WHERE department_id = 50;

حالا با مراحل زیر می‌فهمی دقیقاً مشکل از کجاست:

۱. ساخت Task تشخیص

				
					DECLARE
  l_task VARCHAR2(64);
BEGIN
  l_task := DBMS_SQLDIAG.CREATE_DIAGNOSIS_TASK(
    sql_text  => 'SELECT employee_id, first_name, last_name, department_id FROM employees WHERE department_id = 50',
    task_name => 'emp_diag_task1'
  );
  DBMS_OUTPUT.PUT_LINE('Task created: ' || l_task);
END;
/

۲. اجرای Task

				
					BEGIN
  DBMS_SQLDIAG.EXECUTE_DIAGNOSIS_TASK('emp_diag_task1');
END;
/

۳. دریافت گزارش

				
					SET LONG 100000
SELECT DBMS_SQLDIAG.GET_DIAGNOSIS_REPORT('emp_diag_task1') AS report
FROM dual;

📈 خروجی نمونه:

				
					Problem detected: Full Table Scan on EMPLOYEES
Recommendation: Gather statistics or create an index on DEPARTMENT_ID

بدون آنکه از ابزار خارجی استفاده کنی، اوراکل خودش مشکل را تشخیص می‌دهد و راه‌حل بهینه را پیشنهاد می‌کند.

📦 بازتولید خطا با SQL Test Case

گاهی لازم است مشکل را در محیط تست بازسازی کنی.

تابع CREATE_SQL_TESTCASE دقیقاً برای همین کاربرد ساخته شده است:

				
					DECLARE
  l_tc VARCHAR2(64);
BEGIN
  l_tc := DBMS_SQLDIAG.CREATE_SQL_TESTCASE(
    sql_text     => 'SELECT * FROM employees WHERE department_id = 50',
    test_case_name => 'emp_test_case_50',
    export       => TRUE,
    directory    => 'DATA_PUMP_DIR'
  );
  DBMS_OUTPUT.PUT_LINE('Test case created: ' || l_tc);
END;
/

✅ با اجرا، اوراکل کل بستر اجرای کوئری را در یک فایل فشرده ذخیره می‌کند، شامل داده‌ها، آمار (Statistics) و Bindها.

می‌توان این فایل را با IMPDP در محیط دیگر ایمپورت کرد تا همان شرایط کندی بازتولید شود.

🧹 حذف Taskها برای جلوگیری از تجمع در سیستم

بعد از تحلیل، همیشه تسک‌ها را حذف کن تا فضای Advisor cache تمیز بماند:

				
					BEGIN
  DBMS_SQLDIAG.DROP_DIAGNOSIS_TASK('emp_diag_task1');
END;
/

🔍 بررسی وضعیت Taskها

تمام تسک‌های فعال را می‌توان از View سیستمی زیر دید:

				
					SELECT task_name, status, created, last_modified
FROM dba_advisor_tasks
WHERE advisor_name = 'SQL Diagnosis Advisor';

یا برای مشاهده یافته‌ها و پیشنهادها:

				
					SELECT task_name, finding, recommendation
FROM dba_advisor_findings
WHERE task_name = 'emp_diag_task1';

🔗 پیوند پکیج DBMS_SQLDIAG با SQL Tuning Advisor

بعد از تشخیص مشکل، می‌توان همان SQL را برای بهینه‌سازی خودکار به پکیج DBMS_SQLTUNE سپرد:

				
					DECLARE
  l_tune_task VARCHAR2(64);
BEGIN
  l_tune_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_text  => 'SELECT employee_id, first_name, last_name, department_id FROM employees WHERE department_id = 50',
    task_name => 'tune_emp_task',
    user_name => 'HR'
  );
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK('tune_emp_task');
END;
/

بدین ترتیب، یک چرخه کامل از تشخیص، تحلیل و بهینه‌سازی خودکار شکل می‌گیرد.

💬 نکات حرفه‌ای DBAها

  • قبل از Diagnosis، مطمئن شو آمار جداول به‌روز هستند
  • نام تسک‌ها را با تاریخ بساز (مثلاً diag_emp_2025_11)
  • در محیط‌های Multi-Tenant diagnosis باید در CDB$ROOT انجام شود
  • گزارش خروجی مناسب مستندسازی برای تیم مدیریت یا Change Requests است

سوالات متداول درباره پکیج DBMS_SQLDIAG در اوراکل

پکیج DBMS_SQLDIAG یکی از ابزارهای تخصصی اوراکل برای تشخیص مشکلات عملکردی SQL است.

این پکیج رفتار کوئری‌ها را بررسی می‌کند، علت کندی یا تفاوت در Execution Plan را مشخص کرده و گزارش دقیق به همراه پیشنهاد اصلاح تولید می‌کند.

از این پکیج معمولاً DBAها برای عیب‌یابی، بررسی Full Table Scan، و ایجاد SQL Test Case استفاده می‌کنند.

DBMS_SQLDIAG وظیفه دارد خطا یا مشکل موجود در SQL را تشخیص دهد (Diagnosis)، در حالی که DBMS_SQLTUNE مرحله‌ی بعد یعنی بهینه‌سازی (Tuning) را انجام می‌دهد.

به‌بیان ساده:

  • DBMS_SQLDIAG → می‌گوید مشکل از کجاست.
  • DBMS_SQLTUNE → پیشنهاد می‌دهد چطور رفعش کنی.

برای تحلیل عملکرد یک SQL خاص می‌توان با تابع زیر تسک تشخیص ساخت:

DECLARE
   l_task VARCHAR2(64);
     BEGIN
l_task := DBMS_SQLDIAG.CREATE_DIAGNOSIS_TASK(
sql_text => ‘SELECT * FROM employees WHERE department_id = 50’,
task_name => ’emp_diag_task1′
);
   DBMS_SQLDIAG.EXECUTE_DIAGNOSIS_TASK(’emp_diag_task1′);
     END;

سپس خروجی با DBMS_SQLDIAG.GET_DIAGNOSIS_REPORT دریافت می‌شود. این گزارش علت دقیق کندی، آمار جدول، و پیشنهاد ایجاد ایندکس را نشان می‌دهد.

SQL Test Case مجموعه‌ای از داده‌ها و تنظیمات سیستم است که شرایط اجرای یک Query را بازسازی می‌کند.

با استفاده از تابع CREATE_SQL_TESTCASE می‌توان فایل فشرده‌ای از محیط واقعی ساخت و آن را به سرور تست ارسال کرد:

DBMS_SQLDIAG.CREATE_SQL_TESTCASE(
sql_text => ‘SELECT * FROM employees WHERE department_id=50’,
export => TRUE,
directory => ‘DATA_PUMP_DIR’
);

این فایل در مسیر DATA_PUMP_DIR ذخیره می‌شود و برای تیم پشتیبانی (Support) قابل اجراست.

جمع‌بندی

پکیج DBMS_SQLDIAG ابزار قدرتمند اوراکل برای تشخیص دقیق مشکلات SQL است.

از بررسی Full Scanها تا تولید تست کیس، همه‌چیز را در اختیار DBA قرار می‌دهد تا به جای حدس و آزمایش، با تحلیل دقیق تصمیم بگیرد.

اگر دنبال راهی هستی که کوئری‌هایت مثل موتور اسپرت اجرا شوند، DBMS_SQLDIAG کلید آن است — ابزاری هوشمند، دقیق و ویژه متخصصان Performance Tuning.

📥 اگر سوالی داری در مورد پکیج DBMS_SQLDIAG در اوراکل داری، در بخش کامنت‌ها بپرس.

میثم راد

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

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

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