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

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

آموزش پکیج DBMS_SQLPA در Oracle: تحلیل و مقایسه عملکرد SQL در Performance Analyzer

آموزش پکیج DBMS_SQLPA در Oracle

پکیج DBMS_SQLPA که با نام کامل SQL Performance Analyzer شناخته می‌شود یکی از ابزارهای قدرتمند و کمتر شناخته‌شده در Oracle Database است.

هدف آن تحلیل و مقایسه عملکرد کوئری‌ها قبل و بعد از ایجاد تغییر در سیستم است تا بتوانی مطمئن شوی هیچ SQL مهمی بعد از آپدیت یا تغییرات ساختاری کند نشده است.

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

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

پیشنهاد می کنم این مقاله زیر رو حتما مطالعه کنی.

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

معرفی پکیج DBMS_SQLPA

پکیج DBMS_SQLPA که مخفف SQL Performance Analyzer است، یکی از قدرتمندترین ابزارهای Oracle برای بررسی تغییرات در عملکرد SQLها پس از تغییرات سیستمی محسوب می‌شود.

وقتی شما سیستم را ارتقا می‌دهید (مثلاً از Oracle 19c به ۲۳c)، یا آمارها (Statistics) و ایندکس‌ها را تغییر می‌دهید، این ابزار کمک می‌کند بفهمید آیا اجرای کوئری‌ها سریع‌تر شده یا کندتر.

DBMS_SQLPA بخشی از SQL Tuning Infrastructure اوراکل است و برای مقایسه دقیق عملکرد نیاز به SQL Tuning Set (STS) دارد.

هدف از استفاده پکیج DBMS_SQLPA

  • تحلیل اثر تغییرات در نسخه‌، پارامترهای بهینه‌ساز (Optimizer Parameters) یا ساختار داده‌ها
  • شناسایی SQLهایی که پس از تغییرات کند شده‌اند
  • اطمینان از عدم افت Performance در Migration یا Upgrade
  • کمک به DBAها برای تصمیم‌گیری قبل از اعمال نهایی تغییرات

پیش‌نیاز: SQL Tuning Set (STS)

DBMS_SQLPA روی یک SQL Tuning Set کار می‌کند.

STS مجموعه‌ای از کوئری‌ها، Bindها و آمار اجرایی آن‌هاست که می‌توان آن را از حافظه (Cursor Cache) گرفت.

مثال ساخت STS:

				
					BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET('SALES_STS', 'Performance SQL Set for Sales');
  DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(
    sqlset_name => 'SALES_STS',
    time_limit  => ۶۰,
    repeat_interval => ۱۰
  );
END;
/

مراحل اجرای SQL Performance Analyzer

پکیج DBMS_SQLPA در سه فاز عمل می‌کند:

  1. CREATE_ANALYSIS_TASK → ساخت Task تحلیل
  2. EXECUTE_ANALYSIS_TASK → اجرای تحلیل در حالت قبل و بعد از تغییر
  3. REPORT_ANALYSIS_TASK → تولید گزارش مقایسه عملکرد

گام به گام آموزش DBMS_SQLPA

مرحله ۱: ایجاد SQL Tuning Set

				
					BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET('SALES_STS');
  DBMS_SQLTUNE.LOAD_SQLSET(
    sqlset_name     => 'SALES_STS',
    populate_cursor => NULL,
    sqlset_owner    => USER,
    source_cursor   => (SELECT VALUE(p)
                        FROM TABLE(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('parsing_schema_name=''SALES''')) p)
  );
END;
/

مرحله ۲: ایجاد Task تحلیل

				
					DECLARE
  v_task VARCHAR2(100);
BEGIN
  v_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK(
              sqlset_name => 'SALES_STS',
              task_name   => 'SALES_PERF_TEST',
              description => 'Performance Benchmark Before and After Index Rebuild');
END;
/

مرحله ۳: اجرای تحلیل قبل از تغییر

				
					BEGIN
  DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
    task_name      => 'SALES_PERF_TEST',
    execution_type => 'TEST EXECUTE',
    execution_name => 'BEFORE_INDEX_REBUILD');
END;
/

مرحله ۴: انجام تغییر در سیستم

				
					ALTER INDEX SALES_IDX REBUILD;

مرحله ۵: اجرای مجدد تحلیل بعد از تغییر

				
					BEGIN
  DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
    task_name      => 'SALES_PERF_TEST',
    execution_type => 'TEST EXECUTE',
    execution_name => 'AFTER_INDEX_REBUILD');
END;
/

مرحله ۶: مقایسه نتایج دو اجرا

				
					BEGIN
  DBMS_SQLPA.COMPARE_ANALYSIS_TASK(
    task_name       => 'SALES_PERF_TEST',
    execution_name1 => 'BEFORE_INDEX_REBUILD',
    execution_name2 => 'AFTER_INDEX_REBUILD');
END;
/

مرحله ۷: تولید گزارش نهایی

				
					SET LONG 1000000
SET LINESIZE 200
COLUMN report FORMAT A200
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK(
  'SALES_PERF_TEST',
  'TEXT',
  'ALL',
  'SUMMARY') AS report
FROM DUAL;

گزارش نهایی شامل تمام SQLهایی است که عملکرد آن‌ها تغییر کرده، با تفکیک بین SQL بهبود یافته، بدون تغییر و SQLهایی که کندتر شده‌اند.

پارامترهای مهم در تنظیم تسک‌ها

نام پارامتر توضیح
execution_name نام اجرای تسک برای تفکیک بین اجراهای مختلف (مثلاً قبل و بعد از تغییر)
comparison_metric مشخص‌کردن شاخص مقایسه مانند ELAPSED_TIME یا BUFFER_GETS
report_level سطح جزئیات گزارش (BASIC، TYPICAL، یا ALL)
execution_mode حالت اجرای تسک: COMPILE، TEST EXECUTE یا TEST COMPARE
sql_trial_name نام مجزای هر SQL Trial هنگام اجرای تحلیل

مثال:

				
					BEGIN
  DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(
    'SALES_PERF_TEST',
    'comparison_metric',
    'ELAPSED_TIME');
END;
/

مدیریت و پاک‌سازی Taskها

حذف تسک:

				
					BEGIN
  DBMS_SQLPA.DROP_ANALYSIS_TASK('SALES_PERF_TEST');
END;
/

حذف تسک:

				
					BEGIN
  DBMS_SQLPA.RESET_ANALYSIS_TASK('SALES_PERF_TEST');
END;
/

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

  • همیشه STS را از محیط Production گرفته و در محیط Test اجرا کنید تا نتیجه دقیق باشد.
  • در سیستم‌های CPU-bound از فیلد CPU_TIME استفاده کنید و در سیستم‌های IO-bound از BUFFER_GETS.
  • خروجی HTML را می‌توانید در گزارش‌های APEX، Grafana یا Oracle BI برای مصورسازی استفاده کنید:
				
					SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('SALES_PERF_TEST','HTML','ALL','ALL')
   FROM DUAL;
  • در صورت نیاز می‌توانید اطلاعات Taskها را از Viewهای سیستمی مانند DBA_ADVISOR_TASKS، DBA_ADVISOR_FINDINGS و DBA_SQLSET مشاهده کنید.

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

پکیج DBMS_SQLPA (SQL Performance Analyzer) تغییر عملکرد کوئری‌ها را قبل و بعد از هر تغییر در سیستم مثل ارتقای نسخه، تغییر آمار، یا بازسازی ایندکس‌ها بررسی می‌کند.

این ابزار به DBA کمک می‌کند مطمئن شود هیچ SQL حیاتی در محیط جدید کندتر نشده است.

به‌عبارتی SQLPA ابزار اصلی Oracle برای مقایسه دقیق Performance SQLها در مراحل Pre و Post-Change است.

DBMS_SQLTUNE برای بهینه‌سازی یک کوئری خاص استفاده می‌شود (مثلاً پیشنهاد ایندکس یا Plan بهتر)،

اما DBMS_SQLPA یک ابزار تحلیلی کلی است که عملکرد مجموعه‌ای از SQLها را در دو وضعیت مختلف مقایسه می‌کند.

به‌طور خلاصه:

  • SQLTUNE → برای بهبود یک SQL
  • SQLPA → برای مقایسه گروهی SQLها در دو وضعیت

بله! در واقع DBMS_SQLPA دقیقاً برای چنین سناریوهایی طراحی شده است.

کافی است در نسخه فعلی (۱۹c) یک SQL Tuning Set بسازید و آن را Export کنید.

سپس همان STS را در محیط Oracle ۲۳c Import کرده و با پکیج DBMS_SQLPA تحلیل کنید تا مشخص شود کدام SQLها در نسخه جدید کند یا سریع‌تر شده‌اند. این روش به DBA دید عمیقی در Performance Migration می‌دهد.

تابع REPORT_ANALYSIS_TASK گزارشی متنی یا HTML از نتایج مقایسه تولید می‌کند که شامل موارد زیر است:

  • تعداد SQLهایی که بهبود، افت یا بدون تغییر داشته‌اند
  • مقادیر متوسط Elapsed Time، CPU Time و Buffer Gets
  • جزئیات Plan هر SQL قبل و بعد از تغییر

این گزارش را می‌توان در فرمت HTML برای نمایش در APEX یا ابزارهای مانیتورینگ بارگذاری کرد و یکی از مهم‌ترین ابزارهای تصمیم‌گیری پیش از اجرایی کردن تغییرات سیستم است.

جمع‌بندی

پکیج DBMS_SQLPA یکی از ابزارهای حیاتی برای DBAها و معماران Performance در Oracle است.

این ابزار به شما کمک می‌کند قبل از اعمال تغییرات بزرگ مثل Upgrade یا Migration، تاثیر واقعی آن بر عملکرد SQLها را بسنجید و جلوی افت شدید Performance را بگیرید.

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

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

میثم راد

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

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

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