Subscribe to Posts by Email

Subscriber Count

    696

Disclaimer

All information is offered in good faith and in the hope that it may be of use for educational purpose and for Database community purpose, but is not guaranteed to be correct, up to date or suitable for any particular purpose. db.geeksinsight.com accepts no liability in respect of this information or its use. This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content and if notify any such I am happy to remove. Product and company names mentioned in this website may be the trademarks of their respective owners and published here for informational purpose only. This is my personal blog. The views expressed on these pages are mine and learnt from other blogs and bloggers and to enhance and support the DBA community and this web blog does not represent the thoughts, intentions, plans or strategies of my current employer nor the Oracle and its affiliates or any other companies. And this website does not offer or take profit for providing these content and this is purely non-profit and for educational purpose only. If you see any issues with Content and copy write issues, I am happy to remove if you notify me. Contact Geek DBA Team, via geeksinsights@gmail.com

Pages

Performance Troubleshooting Series : Understanding your Oracle Database Load

Hello,

Very long back, Karlarao has written a wonderful script which delivers a exhaustive report for database performance. The script extracts the data from AWR repository tables. 

Where in I have took liberty to tweak the same script for those who does not have AWR licensing and having statspack only.

Here is the script that can be run on statspack which generates similar report for database load representation.  If you want to identify the database load from AWR report , you will need to generate multiple reports compare etc , but this report give you a glance of each snap and its respective load in terms of CPU/MEM/IO etc. 

Script for Statspack Version:-  statpack_gen_wl.sql

Script for AWR Version:- awr_gen_wl.sql

Report Screenshot (Click to enlarge)

Screenshot:- http://db.geeksinsight.com/wp-content/uploads/2015/10/AWR_Load_Report.jpg

Alongside, here is the notes, Lets try to understand the report too. I have highlighted the important sections so that you can also look at your database performance.

1. Looking at 1,2,3,4, you can understand where is the most DB time utilized.

2. Looking at 5,4,6,7, you can understand that your database has more I/O utilization at that time.

3. Looking at 8,9,10, about redo , how much redo the database has generated. With this you can understand easily is there any DML activity happened at that time.

4. Looking at 11,12,13, about executions, we can cleverly know that database is doing more executions at what time also this tells you if you experience any memory contention in shared pool. Like ora-4031 errors just in case reporting or more cursors opened etc.

5. Looking at 14, the big box, we can divide the CPU load whether its RMAN, Oracle, OS CPU utilization and also in percentages of User or SYS user, which you can determine any backups running during that time etc or any SYS level activity to rule out maintenance stuff.

5. Looking at 15,16,17,18, you can understand OS load too

Well isnt it this report is cooler (except this will not tell you about ) than AWR (2 hours snap) to look at glance of database over period. 

Hope this is useful.

6 comments to Performance Troubleshooting Series : Understanding your Oracle Database Load