اگر در دنیای مدیریت پایگاه داده اوراکل (Oracle DBA) فعالیت میکنید، حتماً بارها با این صحنه مواجه شدهاید: سرور قفل کرده، کاربران شاکی هستند.
در ۹۰ درصد مواقع، متهم اصلی این پرونده کسی نیست جز I/O (ورودی/خروجی دیسک).
دیسکها ذاتاً کندترین بخش یک سرور هستند. اگر اوراکل نتواند ارتباط خود را با دیسک به درستی مدیریت کند، کل سیستم به گل خواهد نشست.
در این مقاله آموزش اوراکل، از بخش آموزش مدیریت اوراکل میخواهیم بررسی کنیم که اوراکل چطور این غول سرکش (I/O) را مهار میکند و ما به عنوان متخصص، چطور میتوانیم افسار آن را به دست بگیریم.
در این مقاله شما می خوانید
نبرد ابدی در حافظه: Logical I/O در برابر Physical I/O
بیایید با یک تشبیه ساده شروع کنیم. فرض کنید در یک کتابخانه بزرگ کار میکنید.
- اگر کتابی که میخواهید روی میزتان باشد، در کمتر از یک ثانیه آن را باز میکنید. به این میگویند Logical I/O (خواندن از حافظه یا همان Database Buffer Cache).
- اما اگر کتاب روی میز نباشد و مجبور شوید به انتهای سالن بروید، نردبان بگذارید و آن را از قفسههای خاکگرفته پیدا کنید، کلی زمان از دست میدهید. این یعنی Physical I/O (خواندن از روی هارد دیسک یا SSD).
تفاوت سرعت بین این دو سناریو، تفاوت بین میکروثانیه و میلیثانیه است؛ یعنی هزاران برابر فاصله! هنر اوراکل این است که تا حد امکان، کارهای شما را روی «میز کار» (Buffer Cache) نگه دارد و نگذارد برای هر چیز کوچکی به «قفسههای دیسک» مراجعه کنید.
اوراکل چطور ترافیک I/O را مهندسی میکند؟
اوراکل ابزارهای هوشمندی برای مدیریت این رفتوآمدها دارد. بیایید با ۳ ابزار کلیدی آشنا شویم:
الف) نگهبان باهوش حافظه (الگوریتم LRU)
حافظه رم سرور محدود است و نمیتوان تمام دیتابیس را در آن جا داد. اوراکل از الگوریتمی به نام LRU (Least Recently Used) استفاده میکند.
این الگوریتم مثل یک نگهبان باهوش، دادههایی که کمتر مورد استفاده قرار گرفتهاند را شناسایی کرده و آنها را از رم بیرون میاندازد تا برای دادههای جدید و پرطرفدار جا باز شود.
در سیستمهای قدیمی، وقتی دیتابیس درخواستی به دیسک میفرستاد، پردازش منتظر میماند تا دیسک جواب بدهد.
اما با Asynchronous I/O، اوراکل درخواست را صادر میکند و فوراً به سراغ کارهای دیگرش میرود.
هر وقت دیسک کارش تمام شد، به اوراکل خبر میدهد. این یعنی صرفهجویی فوقالعاده در زمان!
گاهی اوقات یک کوئری سنگین و غیراستاندارد توسط یک کاربر تازه کار اجرا میشود و کل پهنای باند I/O دیسک را میبلعد.
اینجاست که شما به عنوان DBA باید وارد عمل شوید. با ابزار DBMS_RESOURCE_MANAGER میتوانید برای مصرف I/O گروههای کاربری مختلف سقف تعیین کنید.
نمونه کد برای محدود کردن I/O یک گروه کاربری:
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
PLAN => 'nightly_backup_plan',
GROUP_OR_SUBPLAN => 'REPORTING_USERS',
IO_MEGABYTES_LIMIT => ۵۰
);
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
چکلیست طلایی بهینهسازی I/O (چگونه مصرف دیسک را به حداقل برسانیم؟)
به عنوان یک متخصص، کار شما این است که جلوی رفتوآمدهای اضافه به دیسک را بگیرید.
در ادامه ترفندهایی را میبینید که معجزه میکنند:
بزرگترین قاتل سرعت دیتابیس، Full Table Scan (اسکن کل جدول) است.
وقتی ایندکس ندارید، اوراکل مجبور است تمام صفحات هارد را بخواند تا مثلاً یک رکورد خاص را پیدا کند.
با ساختن یک ایندکس مناسب روی ستونهای کلیدی، اوراکل مستقیماً به آدرس هدف میرود.
اگر جدولی با ۱۰۰ میلیون رکورد دارید، چرا اوراکل برای پیدا کردن تراکنشهای امروز، کل جدول را بگردد؟
با پارتیشنبندی جدول بر اساس تاریخ، اوراکل فقط به سراغ پارتیشن مربوط به همان روز میرود و بقیه بخشهای دیسک را اصلاً لمس نمیکند (تکنیک Partition Pruning).
وقتی دادهها را در سطح دیسک فشرده میکنید، شاید کمی CPU درگیر فشردهسازی شود، اما در عوض در هر بار خواندن از دیسک (I/O block read)، حجم بسیار بیشتری از دادهها با یک حرکت وارد رم میشوند.
این کار در دیتابیسهای تحلیلی (OLAP) معجزه میکند.
اگر هنوز دیتابیس خود را روی فایلسیستمهای معمولی سیستمعامل نگهداری میکنید، وقت تغییر است.
Automatic Storage Management (ASM) با پخش کردن متوازن دادهها روی تمام دیسکهای فیزیکی موجود (Striping)، بار I/O را به شدت توزیع کرده و از ایجاد گلوگاه روی یک دیسک خاص جلوگیری میکند.
راهاندازی لاین سرعت با Direct Path I/O
یک راز جالب در اوراکل وجود دارد: گاهی اوقات بهترین راه بهینهسازی حافظه، دور زدن آن است!
وقتی قرار است یک گزارش بسیار سنگین بگیرید یا حجم عظیمی از دادهها را بارگذاری کنید (Bulk Load)، اوراکل تصمیم میگیرد دادهها را اصلاً وارد Buffer Cache نکند.
به این مکانیزم Direct Path Read/Write میگویند. در این حالت دادهها مستقیم از دیسک به فضای خصوصی کاربر (PGA) منتقل میشوند تا رم اصلی دیتابیس بیهوده اشغال و آلوده نشود.
چگونه یک کوئری کند را نجات دادیم؟
قدم اول: ردیابی مشکل با SQL
ما متوجه کندی شدید شدهایم. ابتدا این دستور را در محیط SQL*Plus یا Developer اجرا میکنیم تا ببینیم کدام سشنها در حال بلعیدن پهنای باند دیسک هستند:
SELECT s.sid, s.serial#, s.username, n.name, v.value/1024/1024 AS MB_Consumed
FROM v$sesstat v, v$statname n, v$session s
WHERE v.statistic# = n.statistic#
AND s.sid = v.sid
AND n.name = 'physical read bytes'
AND v.value > ۰
ORDER BY v.value DESC;
خروجی نشان میدهد سشن شماره ۴۲ مربوط به بخش حسابداری، تا الان چندین گیگابایت داده از دیسک خوانده است.
قدم دوم: بررسی طرح اجرایی (Execution Plan)
با بررسی طرح اجرایی متوجه میشویم اوراکل در حال انجام عملیات سنگین زیر است:
TABLE ACCESS FULL ON TRANSACTIONS
قدم سوم: درمان قطعی
ما یک ایندکس روی ستون transaction_date ایجاد میکنیم:
CREATE INDEX idx_trans_date ON transactions (transaction_date);
نتیجه: کوئری بعدی به جای اسکن کل هارد، با یک INDEX RANGE SCAN سریع در کسری از ثانیه اجرا میشود.
میزان Physical I/O تقریباً به صفر میرسد و سشن آزاد میشود.
سوالات متداول درباره I/O در اوراکل
این تصور غلط که «سختافزار سریع یعنی دیتابیس سریع» بزرگترین دام برای مدیران دیتابیس است.
در اوراکل، مشکل اصلی معمولاً تعداد دفعات مراجعه به دیسک (I/O Frequency) است، نه سرعت انتقال دیتا.
اگر کوئری شما فاقد ایندکس مناسب باشد، اوراکل میلیونها بلوک داده را بیهوده میخواند.
حتی اگر سریعترین دیسک جهان را داشته باشید، باز هم این «ترافیکِ اضافه» باعث ایجاد صف (Wait Events) میشود.
برای حل این مشکل، به جای هزینه برای سختافزار، ابتدا سراغ Execution Plan بروید و با حذف Full Table Scans و بهینهسازی Joinها، تعداد درخواستهای I/O را به حداقل برسانید.
- Logical I/O: خواندن داده از
Database Buffer Cache(در رم). بسیار سریع (در حد نانوثانیه). - Physical I/O: خواندن از دیسک. بسیار کند (در حد میلیثانیه).
گلوگاه اصلی زمانی رخ میدهد که نسبت این دو به هم بهم بریزد. اگر Physical I/O در سیستم شما بالاست، یعنی دادههای داغ (Hot Data) به اندازه کافی در حافظه کش نمیشوند.
برای بهبود، باید SGA (System Global Area) را بهینه کنید تا «میز کار» دیتابیس بزرگتر شود و دادهها کمتر به حافظه جانبی (دیسک) منتقل شوند.
هدف نهایی هر DBA، نزدیک کردن «نسبت خواندن منطقی» به «کل خواندن» است.
اولین گام استفاده از قابلیت AWR Report (Automatic Workload Repository) است.در این گزارش، بخش
Top 10 Wait Eventsرا چک کنید. اگر با رویدادهایی مثلdb file sequential readیاdb file scattered readمواجه شدید، یعنی سیستم شما زیر بار فشار I/O است.برای رفع سریع:
- ایندکسهای غیرضروری را حذف کنید: ایندکس اضافه، نوشتن (Write I/O) را کند میکند.
- SQL Tuning Advisor را اجرا کنید: این ابزار قدرتمندِ خودِ اوراکل است که پیشنهادهای عملی برای تغییر ساختار پرسوجو به شما میدهد.
- پارتیشنبندی: با پارتیشنبندی جداول بزرگ، محدودهای که اوراکل باید برای جستجو بگردد را محدود کنید.
هم مدیریت را ساده میکند و هم سرعت را افزایش میدهد!
ASM با استفاده از قابلیت Striping، دادهها را به صورت بسیار ریز و یکنواخت بین تمام دیسکهای فیزیکی توزیع میکند.
در فایلسیستمهای معمولی، ممکن است یک فایل خاص فقط روی یک دیسک بیفتد و باعث ایجاد «نقطه داغ» (Hot Spot) شود.
ASM این بار را روی کل آرایه دیسکها پخش میکند، بنابراین در عملیاتهای سنگینِ خواندن و نوشتن موازی، شما از حداکثر پهنای باند تمامی دیسکهای خود بهرهمند میشوید که در عمل منجر به حذف گلوگاههای I/O میشود.
جمعبندی
مدیریت و کنترل I/O در اوراکل یک هنر است.
وظیفه شما به عنوان یک متخصص این نیست که دیسکهای سریعتری بخرید (هرچند سختافزار بیتاثیر نیست)، بلکه وظیفه شما این است که با ایندکسگذاری دقیق، پارتیشنبندی هوشمند، مدیریت حافظه رم (SGA/PGA) و ابزارهایی مانند Resource Manager، کاری کنید که دیتابیس کمترین نیاز را به مراجعه مستقیم به هارد دیسک داشته باشد.
سؤالی درباره این مقاله داری؟
اگر نکتهای در این مقاله برات مبهم بود یا خواستی بیشتر بدونی، همین حالا برام بنویس تا دقیق و صمیمی پاسخت رو بدم — مثل یه گفتوگوی واقعی 💬
برو به صفحه پرسش و پاسخ
دیدگاهتان را بنویسید