کند شدن دیتابیس Oracle یکی از مشکلات رایجی است که تقریباً هر DBA یا تیم فنی در مقطعی با آن مواجه میشود.
معمولاً کاربران اولین کسانی هستند که متوجه این مشکل میشوند؛ صفحاتی که دیر باز میشوند، گزارشهایی که زمان زیادی برای اجرا نیاز دارند و سیستمهایی که قبلاً سریع بودند اما حالا کند شدهاند.
اما واقعیت این است که کند شدن Oracle معمولاً یک دلیل واحد ندارد. در اغلب موارد مجموعهای از عوامل مثل کوئریهای غیربهینه، ایندکسهای نامناسب، تنظیمات حافظه، یا حتی مشکلات سختافزاری باعث افت Performance دیتابیس میشوند.
در این مقاله آموزش اوراکل در بخش آموزش بهیننه سازی کوئری (SQL Tuning) ، به صورت عملی و قابل فهم بررسی میکنیم چرا Oracle Database کند میشود و مهمتر از آن، چگونه میتوان سرعت آن را به شکل اصولی افزایش داد.
اگر با Oracle Database کار میکنی و تا حالا Execution Plan رو دیدهای اما دقیق نفهمیدهای چرا Oracle یک مسیر خاص را انتخاب کرده، این مقاله دقیقاً برای تو نوشته شده است.
پیشنهاد می کنم این مقاله زیر رو حتما مطالعه کنی.
در این مقاله شما می خوانید
۱. کوئریهای غیربهینه (Poor SQL Queries)
یکی از بزرگترین دلایل کند شدن Oracle، کوئریهایی هستند که بدون توجه به نحوه اجرای دیتابیس نوشته شدهاند.
فرض کنید جدول سفارشها شامل چند میلیون رکورد است.
SELECT *
FROM orders
WHERE order_date = '01-JAN-2024';
اگر روی ستون order_date ایندکس وجود نداشته باشد، Oracle مجبور میشود کل جدول را بررسی کند که به آن Full Table Scan گفته میشود.
این کار در جدولهای بزرگ میتواند بسیار زمانبر باشد.
راه حل
ایجاد ایندکس روی ستونهایی که در شرطها زیاد استفاده میشوند:
CREATE INDEX idx_orders_date
ON orders(order_date);
۲. نبود Index مناسب
ایندکس در دیتابیس مانند فهرست یک کتاب عمل میکند. اگر کتابی فهرست نداشته باشد، برای پیدا کردن یک موضوع باید تمام صفحات آن را بررسی کنید.
در Oracle هم اگر ایندکس مناسب وجود نداشته باشد، دیتابیس مجبور میشود دادهها را به صورت کامل اسکن کند.
مثال
SELECT *
FROM customers
WHERE national_id = '1234567890';
اگر ستون national_id ایندکس نداشته باشد، این Query در جدولهای بزرگ بسیار کند اجرا میشود.
راه حل
CREATE INDEX idx_customers_nationalid
ON customers(national_id);
البته باید توجه داشت که ایجاد ایندکس بیش از حد هم میتواند باعث کاهش سرعت عملیات Insert و Update شود. بنابراین ایندکسها باید هدفمند طراحی شوند.
۳. Full Table Scan بیش از حد
Full Table Scan زمانی اتفاق میافتد که Oracle مجبور شود کل جدول را برای پیدا کردن داده بررسی کند.
در بعضی شرایط این کار طبیعی است، اما اگر برای کوئریهای پرتکرار رخ دهد میتواند Performance دیتابیس را به شدت کاهش دهد.
بررسی نحوه اجرای Query
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE salary > ۱۰۰۰۰;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
اگر در خروجی عبارت TABLE ACCESS FULL مشاهده شود، احتمال دارد Query نیاز به بهینهسازی داشته باشد.
۴. قدیمی بودن Statistics
Oracle برای انتخاب بهترین روش اجرای Query از آمار دیتابیس (Statistics) استفاده میکند. اگر این اطلاعات بهروز نباشند، Optimizer ممکن است تصمیم اشتباه بگیرد.
مثلاً ممکن است تصور کند یک جدول کوچک است در حالی که میلیونها رکورد دارد.
راه حل
بهروزرسانی Statistics با استفاده از پکیج DBMS_STATS:
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR');
یا برای یک جدول خاص:
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR','EMPLOYEES');
بهروز بودن Statistics کمک میکند Oracle بهترین Execution Plan را انتخاب کند.
۵. Lock و Blocking در دیتابیس
یکی دیگر از دلایل کند شدن سیستم، قفل شدن رکوردها یا جدولها است.
وقتی یک Session در حال تغییر دادهها باشد، Sessionهای دیگر ممکن است مجبور شوند منتظر بمانند.
مشاهده Sessionهای قفل شده
SELECT blocking_session, sid, serial#, wait_class
FROM v$session
WHERE blocking_session IS NOT NULL;
اگر تعداد Sessionهای منتظر زیاد باشد، کاربران سیستم کندی شدیدی را تجربه خواهند کرد.
۶. کمبود منابع سختافزاری
گاهی مشکل اصلاً مربوط به Query یا تنظیمات Oracle نیست و سختافزار پاسخگوی حجم کار نیست.
مهمترین منابع تاثیرگذار در Performance دیتابیس عبارتند از:
- CPU
- RAM
- Disk I/O
- Network
اگر Disk I/O کند باشد، حتی بهترین Queryها هم دیر اجرا میشوند.
بررسی Wait Eventها
SELECT event, total_waits, time_waited
FROM v$system_event
ORDER BY time_waited DESC;
این گزارش نشان میدهد دیتابیس بیشتر منتظر چه منابعی است.
۷. تنظیم نبودن حافظه SGA و PGA
Oracle از حافظه برای Cache کردن دادهها و بهبود سرعت استفاده میکند.
دو بخش مهم حافظه در Oracle:
- SGA (System Global Area)
- PGA (Program Global Area)
اگر اندازه این بخشها مناسب نباشد، دیتابیس مجبور میشود بیشتر از دیسک بخواند که باعث کندی سیستم میشود.
بررسی تنظیمات حافظه
SHOW PARAMETER sga
SHOW PARAMETER pga
تنظیم درست حافظه میتواند تاثیر بسیار زیادی روی Performance داشته باشد.
۸. استفاده از SELECT *
استفاده از SELECT * یکی از اشتباهات رایج در طراحی Query است.
مثال
SHOW PARAMETER sga
SHOW PARAMETER pga
این Query تمام ستونهای جدول را برمیگرداند حتی اگر برنامه فقط به چند ستون نیاز داشته باشد.
روش بهتر
SELECT employee_id, first_name, salary
FROM employees;
این کار باعث کاهش حجم داده منتقل شده و افزایش سرعت میشود.
۹. Transactionهای طولانی
Transactionهای طولانی میتوانند باعث ایجاد Lock و مصرف زیاد منابع شوند.
مثال اشتباه
FOR i IN 1..100000 LOOP
INSERT INTO log_table VALUES (...);
COMMIT;
END LOOP;
این روش باعث افزایش شدید عملیات I/O میشود.
روش بهتر
Commit کردن به صورت دستهای مثلاً هر ۱۰۰۰ رکورد.
۱۰. استفاده از ابزارهای مانیتورینگ Oracle
Oracle ابزارهای قدرتمندی برای تحلیل Performance ارائه میدهد که استفاده از آنها برای هر DBA ضروری است.
مهمترین ابزارها:
- AWR (Automatic Workload Repository)
- ADDM (Automatic Database Diagnostic Monitor)
- ASH (Active Session History)
تولید گزارش AWR
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
گزارش AWR نشان میدهد:
- کدام Queryها بیشترین مصرف منابع را دارند
- بیشترین Wait Eventها کدام هستند
- کدام بخش سیستم باعث کندی شده است
اگر دیتابیس Oracle شما کند شده است، بهتر است این مراحل را به ترتیب بررسی کنید:
- بررسی Queryهای کند
- تحلیل Execution Plan
- ایجاد Index مناسب
- بهروزرسانی Statistics
- بررسی Lock و Blocking
- بررسی مصرف CPU و Disk
- تحلیل گزارش AWR
- تنظیم مناسب حافظه SGA و PGA
در بسیاری از موارد تنها با بهینهسازی چند Query اصلی میتوان سرعت سیستم را چند برابر افزایش داد.
سوالات متداول درباره کند شدن دیتابیس Oracle
کند شدن ناگهانی Oracle معمولاً به چند عامل رایج مرتبط است.
مهمترین آنها اجرای Queryهای سنگین، نبود Index مناسب، قدیمی بودن Statistics دیتابیس یا افزایش ناگهانی حجم دادهها است.
علاوه بر این، مشکلات سختافزاری مانند کمبود RAM، فشار زیاد روی CPU یا کند بودن Disk I/O نیز میتواند باعث افت شدید Performance شود.
برای تشخیص دقیق مشکل معمولاً DBAها از ابزارهایی مثل AWR Report و بررسی Wait Eventها استفاده میکنند.
برای افزایش سرعت Query در Oracle چند روش موثر وجود دارد.
اولین قدم بررسی Execution Plan برای فهمیدن نحوه اجرای Query است.
سپس باید از Index مناسب روی ستونهایی که در شرطهای WHERE یا JOIN استفاده میشوند بهره برد.
همچنین استفاده نکردن از SELECT *، بهروزرسانی Statistics و نوشتن Queryهای بهینه میتواند تاثیر زیادی در کاهش زمان اجرای Query داشته باشد.
Full Table Scan زمانی رخ میدهد که Oracle برای پیدا کردن داده مجبور شود تمام رکوردهای یک جدول را بررسی کند.
این اتفاق معمولاً زمانی رخ میدهد که ایندکس مناسبی روی ستون مورد نظر وجود نداشته باشد یا Optimizer تصمیم بگیرد استفاده از ایندکس بهینه نیست.
در جدولهای بزرگ، Full Table Scan میتواند باعث مصرف زیاد CPU و Disk I/O شود و در نتیجه سرعت سیستم کاهش پیدا کند.
یکی از بهترین روشها برای بررسی عملکرد Oracle استفاده از ابزارهای مانیتورینگ داخلی این دیتابیس است.
گزارشهای AWR، ابزار ASH و سیستم ADDM اطلاعات دقیقی درباره مصرف منابع، Queryهای سنگین و Wait Eventها ارائه میدهند.
با تحلیل این گزارشها میتوان به سرعت فهمید کدام بخش از سیستم باعث کندی شده و چه اقداماتی برای بهینهسازی باید انجام شود.
جمعبندی
کند شدن Oracle Database معمولاً نتیجه مجموعهای از عوامل است و نمیتوان آن را تنها به یک مشکل خاص نسبت داد. یک DBA حرفهای قبل از هر تغییری ابتدا وضعیت Queryها، Execution Plan، منابع سیستم و گزارشهای Performance را بررسی میکند.
بهینهسازی اصولی Oracle شامل طراحی درست Queryها، استفاده مناسب از Indexها، مدیریت منابع سختافزاری و مانیتورینگ مداوم سیستم است. اگر این موارد به درستی مدیریت شوند، Oracle میتواند حتی در سیستمهای بسیار بزرگ نیز عملکرد بسیار سریع و پایدار ارائه دهد.
سؤالی درباره این مقاله داری؟
اگر نکتهای در این مقاله برات مبهم بود یا خواستی بیشتر بدونی، همین حالا برام بنویس تا دقیق و صمیمی پاسخت رو بدم — مثل یه گفتوگوی واقعی 💬
برو به صفحه پرسش و پاسخ
دیدگاهتان را بنویسید