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

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

چرا Query من در Oracle 19c کند اجرا می‌شود؟

تشخیص گام‌به‌گام Bottleneck واقعی

وقتی یه Query در Oracle 19c کند می‌شه، اولین چیزی که ممکنه به ذهنت برسه اینه که “حتماً دیتام زیاده” یا “سیستم ضعیفه!”.

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

بیاین با هم دقیق و فنی در این مقاله آموزش اوراکل از بخش آموزش بهینه سازی کوئری SQL Tuning بررسی کنیم 👇

در دنیای امروزی که ارتباطات نرم‌افزارها از طریق شبکه حرف اول را می‌زند، اوراکل هم برای توسعه‌دهندگانی که به ارتباطات سطح پایین نیاز دارند، ابزار قدرتمندی در اختیار گذاشته: پکیجی به نام UTL_TCP.

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

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

مقدمه‌ای کوتاه اما ضروری در مورد سرعت اجرای کوئری

اوراکل ۱۹c یه موتور هوشمنده. ابزارهایی داره مثل:

  • Adaptive Execution Plans
  • Automatic Indexing
  • SQL Plan Management

ولی هیچ ابزاری جای تحلیل دقیق یه آدم باتجربه رو نمی‌گیره.

اگه بدونی Query از کجا کند شده، اون‌وقته که می‌تونی واقعا بهینه‌سازی درست انجام بدی، نه فقط “سرعت ظاهری”.

عملکرد Query در Oracle چطور پیش می‌ره؟

هر کوئری در اوراکل از چند فاز اصلی عبور می‌کنه:

فاز توضیح
Parse تجزیه و انتخاب بهترین Execution Plan
Execute اجرای عملی و خواندن داده‌ها از حافظه یا دیسک
Fetch ارسال نتایج به کلاینت

اگر کندی در Parse باشه ⇒ مشکل احتمالاً از Bind Variable یا عدم ثبات Execution Planـه

اگر در Execute باشه ⇒ معمولاً پای I/O یا Join بد در میونه

اگر در Fetch باشه ⇒ دیتای خیلی زیاد داری یا فیلترهات ضعیفن

رایج‌ترین دلایل کندی Query در Oracle

  1. استفاده نکردن از Index مناسب
  2. استفاده از توابع روی ستون در WHERE (مثل TO_CHAR(date_col))
  3. Join اشتباه روی داده‌ی حجیم
  4. Statistics ناقص یا قدیمی
  5. حافظه‌ی ناکافی در SGA یا PGA
  6. Round Trip زیاد بین کلاینت و سرور
  7. Full Table Scan بی‌دلیل

تشخیص Bottleneck واقعی — قدم به قدم مثل یه حرفه‌ای

۱. بررسی تنظیمات Optimizer

				
					SELECT name, value 
FROM v$parameter 
WHERE name IN ('optimizer_mode','optimizer_features_enable');

🔹 این بخش نشون می‌ده اوراکل Query رو با چه استراتژی‌ای بهینه می‌کنه:

ALL_ROWS یا FIRST_ROWS_n فرق زیادی در انتخاب Plan دارن.

۲. گرفتن Execution Plan واقعی

				
					EXPLAIN PLAN FOR
SELECT c.customer_name, o.order_date, o.amount
FROM orders o 
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'SHIPPED' AND o.order_date >= SYSDATE - 30;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

اگر نتیجه‌اش مثل زیر بود:

				
					TABLE ACCESS FULL ON ORDERS

یعنی ایندکس روی status یا order_date وجود نداره، یا فرمولت غیرقابل بهین‌سازیه.

۳. بررسی اجرایی واقعی (Real-Time)

				
					SELECT * FROM v$sql_monitor WHERE sql_text LIKE '%orders%';

اینجا باید ستون‌های elapsed_time, cpu_time, buffer_gets و io_interconnect_bytes رو ببینی تا بفهمی وقت واقعی Query کجاها می‌سوزه.

۴. نگاه به Wait Events

				
					SELECT event, total_waits, time_waited/100 AS time_sec
FROM v$session_event
WHERE sid = (SELECT sid FROM v$session WHERE audsid = USERENV('SESSIONID'));

📊 مثال از خروجی:

Event معنی
db file sequential read خواندن ایندکس‌ها یا داده به‌صورت تکی (I/O ترتیبی)
db file scattered read Full Table Scan روی بلاک‌های متعدد
cursor: pin S wait درگیری در Library Cache هنگام اشتراک Cursor
enq: TX - row lock contention قفل رکورد توسط Session دیگر (مشکل DML concurrency)

۵. سراغ AWR در پایگاه داده Production برو

				
					SELECT * FROM dba_hist_sqlstat WHERE sql_id = 'YOUR_SQL_ID';

باهاش روند Query رو در بازه‌های زمانی مختلف می‌بینی و می‌تونی بفهمی آیا کندی تازه پیش اومده یا ذاتی بوده.

۶. بررسی محدودیت‌های سیستم

				
					SELECT name, value 
FROM v$sysmetric 
WHERE group_id = 2 AND name LIKE '%CPU%';

اگر CPU یا I/O در محدوده Warning باشن، Bottleneck از Query فراتره و در سطح سرور باید بررسی بشه.

مثال واقعی از یک Query کند و راه‌حلش

🔻 Query غیر بهینه:

				
					SELECT SUM(o.amount)
FROM orders o
WHERE TO_CHAR(o.order_date, 'YYYY-MM') = '2025-10';

چرا کند؟ چون تابع TO_CHAR() روی ستون قرار گرفته و ایندکس رو از کار انداخته.

✅ نسخه بهینه:

				
					SELECT SUM(o.amount)
FROM orders o
WHERE o.order_date >= DATE '2025-10-01'
  AND o.order_date <  DATE '2025-11-01';

با این روش ایندکس روی order_date فعال می‌مونه، Plan بهتر می‌شه و CPU Usage می‌افته پایین.

ابزارهای مهم عیب‌یابی و رفع کندی کوئری در Oracle

ابزار کاربرد
DBMS_XPLAN.DISPLAY_CURSOR نمایش Plan واقعی اجرای Query از Library Cache
SQL Monitor نمایش اجرای زنده‌ی Query
AWR Report نمایش آمار کل سیستم در بازه‌ی زمانی مشخص
ASH Report تحلیل لحظه‌ای فعالیت Sessionها و Eventها
TKPROF تبدیل Trace فایل‌ها به گزارش خوانا برای آنالیز دقیق

نکات طلایی برای جلوگیری از کندی Query

  • همیشه از Bind Variable استفاده کن.
  • به‌صورت منظم با DBMS_STATS آمارها رو به‌روز نگه دار:
				
					   EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME','ORDERS');
  • از SQL Plan Baseline برای جلوگیری از Plan ناپایدار استفاده کن:
				
					   EXEC DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => 'abc123');
  • Cardinality رو چک کن — تخمین اشتباه Optimizer از تعداد رکوردها گاهی کل Query رو از بین می‌بره.
  • Session Eventها رو مانیتور کن تا ببینی Query واقعاً در کجا گیر کرده.

جمع‌بندی: Bottleneck واقعی رو ببین، نه علائمش رو

خیلی وقتا DBAها فقط EXPLAIN PLAN نگاه می‌کنن و فکر می‌کنن پایانِ ماجراست. ولی واقعاً Plan فقط نقشه‌ی “قصد اجرای کوئری” هست، نه اجرای واقعی.

اگه می‌خوای بدونی واقعاً Query چرا کند شده، باید همزمان به Plan، Session Event، و I/O Profile نگاه کنی.

✅ چک‌لیست نهایی

بررسی هدف
EXPLAIN PLAN نمایش نقشه‌ی ذهنی Oracle Optimizer
v$sql_monitor مشاهده‌ی اجرای واقعی Query در حافظه
v$session_event تحلیل نوع Waitها و زمان مصرفی هرکدام
AWR Report بررسی کارایی کل سیستم و Query در بازه‌ی زمانی
Index / Stats اطمینان از بروز بودن آمار و ایندکس‌گذاری صحیح

سوالات متداول درباره Bottleneck و کندی کوئری در اوراکل

  • کندی کوئری در اوراکل معمولاً به خاطر یکی از این دلایل است:

    • استفاده نکردن از ایندکس مناسب یا وجود تابع روی ستون‌های فیلتر
    • Join نادرست بین جداول حجیم
    • نداشتن آمار (Statistics) بروز
    • Full Table Scan بی‌هدف

    برای پیدا کردن Bottleneck واقعی، بهتر است از Viewهایی مثل v$sql_monitor و v$session_event و ابزارهایی مانند AWR و DBMS_XPLAN استفاده کنید تا مشخص شود Query در چه مرحله‌ای (Parse، Execute یا Fetch) کند عمل می‌کند.

برای دیدن Plan واقعی در زمان اجرا (و نه فقط پیش‌بینی‌شده)، از دستور زیر استفاده کن:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, ‘ALLSTATS LAST’));

این خروجی نشون می‌ده که:

  • از کدوم ایندکس یا Join Method استفاده شده
  • چقدر I/O واقعی انجام گرفته
  • آیا Optimizer انتخاب درستی کرده یا نه

توجه کن که EXPLAIN PLAN فقط برنامه‌ی پیشنهادی رو نشون می‌ده، اما DISPLAY_CURSOR برنامه‌ی واقعی اجراشده رو.

بهترین روش یه برنامه‌ی سه مرحله‌ایه:

  1. تحلیل Plan فعلی با DBMS_XPLAN
  2. بررسی Wait Eventها و میزان مصرف CPU, I/O در v$sql_monitor
  3. اصلاح شرط‌های WHERE و ایندکس‌ها برای کاهش Full Scan

اگر مشکل از Plan ناپایدار بود، از SQL Plan Baseline استفاده کن تا اجرای کوئری همیشه از بهترین Plan ممکن انجام بشه.

🔹 Full Table Scan یعنی Oracle همه‌ی بلاک‌های جدول رو می‌خونه؛ مناسب وقتیه که درصد زیادی از رکوردها باید بررسی بشن (مثلاً بیش از ۲۰%).

🔹 Index Scan فقط بلاک‌های لازم رو از ایندکس می‌خونه؛ بسیار سریع‌تره ولی فقط وقتی مؤثره که فیلترهای WHERE دقیق و با Selectivity بالا باشن.

در اوراکل ۱۹c، Optimizer خودش تصمیم می‌گیره از کدوم نوع استفاده کنه، ولی وجود توابع روی ستون یا نبود Statistics باعث می‌شه Full Scan انتخاب بشه و کوئری کند بشه.

جمع‌بندی

اگر بخوام خلاصه بگم، کندی Query در Oracle تقریباً هیچ‌وقت «تصادفی» نیست.

هر کندی داره یه دلیل منطقی در Execution Path خودش.

کافیه مثل یه پزشک فنی با ابزارهای اوراکلی یکی‌یکی چک کنی تا برسی به نقطه‌ی واقعی گره — و اون‌وقته که Query از ثانیه‌ها برمی‌گرده به میلی‌ثانیه‌ها.

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

میثم راد

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

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

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