۱۰ مرحله برای تجزیه و تحلیل گزارشات AWR در Oracle

پس از تولید گزارش AWR در Oracle، گام بعدی تجزیه و تحلیل این گزارشات است. می‌توان با مطالعه گزارش AWR مشکلات و ایراداتی از قبیل کندی و ضعف پایگاه‌ داده اوراکل ، مدت زمان زیاد انتظار برای event ها، کندی query ها و بسیاری مشکلات دیگر را به آسانی برطرف نمود. گرچه AWR به نظر گزارش طولانی و مفصلی می‌آید ولی مطالعه و بررسی بخش‌های مربوطه و منتخب از این گزارش روشی سریع و آسان برای خطایابی و برطرف نمودن مشکلات محیط پایگاه‌داده می‌باشد.

AWR مخفف عبارت Automatically Workload Repository است. به هر صورت احتمال وجود طیف مختلفی از مشکلات در پایگاه‌داده وجود دارد، ولی در هنگامی که کلیت پایگاه‌ داده دارای عملکرد کند و ضعیف می‌باشد، احتمال برقراری دو وضعیت محتمل است:

۱٫ ایرادات مرتبط با محیط سرور پایگاه‌داده. در این حالت ابزار OS Watcher گزینه مناسبی برای استفاده می‌باشد.
۲٫ مسائل مربوط به کارایی پایگاه‌داده (performance)، که در این صورت گزارش AWR انتخابی است که باید مورد توجه قرار گیرد. البته در موقعیتی که یک query خاص به خوبی اجرا نمی‌شود، توصیه می‌شود execution plan مربوط به آن query و وضعیت جداول اصلی آن مورد بررسی قرار گیرد.

توصیه‌های پیش از اخذ یک گزارش AWR

۱٫ اخذ چند مورد گزارش AWR:
وجود حداقل دو گزارش AWR بسیار مفید است، یکی در زمان صحت پایگاه‌داده دیگری مربوط به زمان ضعف و کندی آن. DBA می‌تواند به وسیله مقایسه این دو گزارش مشکل ایجاد شده را تشخیص دهد.
۲٫ عبارت “پایگاه‌داده داده کند و یا ضعیف است” هیچ کمکی به رفع مشکل کارایی پایگاه‌داده نمی‌نماید، بلکه باید یک بازه زمانی مشخص از ایجاد مشکلات در پایگاه‌داده در اختیار داشته باشیم. به عنوان مثال پایگاه‌داده از ساعت پنج بعدازظهر دیروز تاکنون دچار مشکل است در این صورت DBA می‌تواند گزارشی در خصوص این بازه زمانی تهیه نماید.
۳٫ تقسیم‌بندی یک گزارش طولانی AWR به چندین گزارش کوتاه:
در عوض وجود یک گزارش کلی AWR (به طور مثال برای چهار ساعت) بهتر است که چهار گزارش که هر کدام مربوط به یک ساعت است در اختیار داشته باشیم، این امر به محدود نمودن مشکل کمک شایانی می‌کند.
۴٫ در محیط RAC برای هر instance یک گزارش جداگانه تهیه نمایید.
به هر صورت شما گزارش AWR پایگاه‌داده را تهیه نموده‌اید، حالا نوبت به تجزیه و تحلیل گزارش می‌رسد. از آنجایی که گزارش AWR یک گزارش تفصیلی است باید بر اساس نوع مشکلات ایجاد شده بخش مورد بررسی در گزارش انتخاب گردد، در اینجا فهرستی از بخش‌های حائز اهمیت برای یک DBA به جهت دریافت یک تصویر واضح از مشکلات واقع شده بر پایگاه‌داده آورده می‌شود.
مراحل تجزیه و تحلیل گزارش AWR

۱٫ جزئیات پایگاه‌داده:
اولین و بالاترین بخش پس از اخذ گزارش AWR، بخش جزئیات پایگاه‌داده است. در این قسمت اطلاعات پایگاه‌داده و instance و نسخه پایگاه‌داده مورد ایراد را چک نمایید. در حالتی که پایگاه‌داده در محیط RAC باشد عبارت RAC=YES نمایش داده می‌شود.

۲٫ پیکربندی Host:
اطلاعاتی از قبیل name و platform CUP و socket و RAM. مورد قابل توجه تعداد هسته‌های سیستم است. در این مثال ۱۲ CUP در هسته پردازشگر وجود دارد.

۳٫ جزئیات snapshot:
اطلاعاتی در خصوص snapshot های گرفته شده شامل: زمان شروع و پایان snapshot ها. زمان سپری شده در طول فرآیند snapshot. در اینجا اصطلاح جدید DB Time وجود دارد.

مدت زمان session در پایگاه‌داده = DB Time
DB Time = CPU Time + Non idle wait time

شما مشاهده می کنید که Time DB در مقایسه با زمان سپری شده، بسیار بزرگتر است که این موضوع جای نگرانی ندارد. بررسی کنید که در زمان بروز مشکل اجرایی و کارایی، گزارش تهیه کرده باشید. اگر که تهیه کرده‌اید که بسیار خوب، در غیر این صورت یک گزارش برای زمان مشکل اجرا و عملکرد، تهیه نمایید.
مورد بعدی Cache size است، که تنها اطلاعاتی در مورد وضعیت SGA به ما می‌دهد.

۴٫ بخش Load:
در این بخش به اطلاعات با اهمیتی برای DBA برمی‌خوریم. در ابتدا DB CPU برحسب ثانیه. قبل از آن اجازه دهید در مورد چگونگی کارکرد DB CUP توضیح دهیم. فرض کنید ۱۲ هسته در سیستم شما وجود دارد پس بنابراین برحسب ثانیه‌های ساعت دیواری شما ۱۲ ثانیه زمان کاری بر روی CPU دارید.

اگر DB CPU بر حسب ثانیه در این گزارش بزرگ‌تر از تعداد هسته‌های سیستم باشد به این معنی است که CPU باعث محدودیت محیط پایگاه‌داده است و به تعداد بیشتری از هسته‌های CPU احتیاج دارد و نیاز به بررسی بیشتری دارد که آیا این شرایط همیشه بوده است یا فقط در یک برهه خاص زمانی. بر اساس تجربیات به دست آمده در موارد بسیار معدودی سیستم دارای این نوع محدودیت CPU است.
در این مورد ماشین دارای ۱۲ هسته است و DB CPU بر ثانیه برابر ۶٫۸ است پس شرایط نمی‌تواند یک مورد محدودیت CPU باشد.
اطلاعات دیگری که باید مورد توجه گیرد Parses و Hard parses می‌باشند. اگر نسبت Hard parse به Parse بالا باشد به این معنی است که پایگاه‌داده Hard parse زیادی را اجرا می‌نماید، پس نیاز به بررسی پارامترهای نظیر cursor_sharing و application level برای bind variables است.

۵٫ درصد کارایی instance:
در این گزارش باید به “% Non-Parse CPU” توجه شود. اگر مقدار آن نزدیک ۱۰۰% باشد به این معنی است که بیشتر منابع CPU برای عملیات‌های غیر از parsing مورد استفاده قرار می‌گیرد که برای صحت پایگاه‌داده مناسب است.

۶٫ پنج رویداد پیش‌زمینه‌ای به اتمام رسیده:
یکی دیگر از نقاط با اهمیت گزارش AWR در خصوص مشکلات عملکردی پایگاه‌داده، اطلاعات مرتبط با رویدادهای پیش‌زمینه‌ای منقضی شده می‌باشد، در ادامه فهرست پنج رویداد شاخص نشان داده شده است.

در اینجا ابتدا ستون wait class را بررسی نمایید اگر برابر User I/O یا System I/O یا Others و یا هر عبارت دیگری بود مشکلی وجود ندارد اما اگر برابر عبارت Concurrency باشد به احتمال زیاد مشکلی جدی وجود دارد. ستون مورد بررسی بعدی Time(s) است که نشان دهنده تعداد wait های پایگاه‌داده در آن class می‌باشد. ستون بعدی Avg Wait(ms) می‌باشد. اگر Time(s) مقدار بالایی باشد اما Avg Wait(ms) پایین باشد شرایط خوبی برقرار است. اگر هر دوی آن‌ها بالا باشند و یا Avg Wait(ms) مقدار بالایی باشد آنگاه نیاز به بررسی بیشتری است. در تصویر فوق اکثر منابع استفاده شده مطابق زیر است:
DB CPU = 64% DB time
که در این وضعیت استفاده منابع توسط DB CUP در حالت عادی قرار دارد.
برای مثال در رویداد “log file switch (checkpoint incomplete)” که دارای مقدار بالای waits، Time(s) زیاد و مقدار بالایی در Avg Wait(ms) و همچنین wait class پیکربندی شده است، پس در اینجا شما باید به بررسی و رفع مشکل log file switch (checkpoint incomplete) بپردازید.
Host CPU و instance CPU و آمار حافظه نیاز به توضیح بیشتری دارد. و در مورد آمار RAC، در اغلب اوقات مشکل خاصی در این مورد مشاهده نمی‌شود.

۷٫ آمار Time model:
اطلاعات و جزئیاتی در مورد نحوه استفاده از منابع سیستم می‌باشد. این آمار بر اساس Time(s) و % of DB Time مرتب شده است.

نکته جالبی وجود دارد که مجموع تمام % of DB Time کوچکتر از ۱۰۰% است. علت چیست؟
به این دلیل که این زمان از نوع تجمعی است، به عنوان مثال در شرایط فوق مدت زمان اجرا شدن SQL حدود ۸۹% از DB Time را می‌گیرد که شامل بخش‌های جزئی‌تری از قبیل parse time سپری شده و Hard parse time سپری شده و غیره می‌شود. اگر مشاهده نمودید که Hard parse time سپری شده درصد بیشتری گرفته است پس در این خصوص به بررسی موشکافانه‌تری بپردازید.
DBA موظف است به آماری که نشان دهنده درصدی غیرعادی از DB Time است توجه زیادی نشان دهد.

۸٫ آمار جزئیات سیستم‌عامل:
اطلاعاتی که با سیستم‌عامل مرتبط می‌باشد و وضعیت load سیستم در این بخش نشان داده می‌شود.

گزارش فوق نمایانگر ۶۲ و ۷۰ درصد اتلاف منابع سیستم در زمان تهیه این گزارش بوده است. پس در سطح سیستم اثری از کمبود منابع وجود ندارد ولی اگر مقادیر درصدی بالایی در user و sys و busy یافتید به تبع آن مقدار idle کاهش پیدا می‌کند. در مورد علت این امر به بررسی بپردازید. ابزار OS Watcher ابزاری مفید است که دراین زمینه می‌تواند به شما کمک نماید.
از بخش‌های حیاتی گزارش AWR برای یک DBA بخش آمار SQL است که شامل تمامی اطلاعات و جزئیات query اجرا شده SQL در بازه زمانی اخذ گزارش می‌باشد.

برای متوجه شدن از چگونگی بررسی این گزارشات تا حدودی به آن می‌پردازیم.

۹٫ SQL های مرتب شده براساس مدت زمان سپری شده از آنان:
همانطور که از نام آن مشخص است، این فهرست از query SQL ها براساس مدت زمان سپری شده از اجرای آنان در بازه زمانی تهیه گزارش مرتب شده‌اند.

در این گزارش به جستجوی درخواست‌هایی باشید که دارای executions پایین و Elapsed time per Exec (s) بالا باشد، این query می‌تواند برای رفع ایرادات احتمالی و بهینه‌سازی انتخاب شود. در گزارش فوق مشاهده می‌نمایید که اولین query دارای حداکثر زمان سپری شده است ولی هیچ execution وجود ندارد پس باید مورد بررسی قرار گیرد.
نکته مهمی که در این زمینه وجود دارد آن است که اگر execution برابر صفر باشد به معنای عدم اجرای query نمی‌باشد بلکه احتمالاً در این وضعیت query در حال اجرا بوده و شما مبادرت به اجرای عملیات تهیه گزارش AWR نموده‌اید و به این علت خاتمه اجرای query در گزارش قید نشده است.

۱۰٫ SQL های مرتب شده براساس CPU Time:
در این گزارش، SQL query ها براساس میزان اختصاص منابع CPU به query ها فهرست‌بندی شده‌اند. به عنوان مثال درخواست‌هایی که باعث حجم بارگذاری بیشتری روی سیستم می‌شوند، لذا query های بالاتر می‌توانند برای بهینه‌سازی مدنظر قرار گیرند.

در آمار بالا query هایی را که بیشترین مقدار CPU Time را دارند جستجو نمایید، اگر یک query دارای مقدار execution صفر است همانطور که گفته شد به معنی عدم اجرای آن نیست و احتمالاً در هنگام تهیه گزارش query در حال اجرا بوده است.
به هر حال آمار و اطلاعات بسیار زیاد دیگری نیز در گزارش AWR وجود دارد که نیاز به توجه و بررسی ویژه DBA به آن‌ها است.
تاکنون تنها ۱۰ مورد از مواردی مورد بحث قرار گرفت که بیشترین کاربرد را در مسائل مربوط به عملکرد و کارایی پایگاه‌داده را دارند.