Logical and physical reads combined shows measure of how many IO’s (Physical and logical) that the database is performing. These two specific values can be observed in ‘Instance Activity Stats’ section. Therefore, a Logical read is calculated as = Total number of “Consistent Gets” + Total number of “db block gets”. ![]() To explain a bit further, if Oracle gets the data in a block which is consistent with a given point in time, then a counter name “Consistent Gets” increases to 1.īut if the data is found in current mode, that is, the most up-to-date copy of the data in that block, as it is right now or currently then it increases a different counter name “db block Gets”. If it does, then logical read increases to 1. Is using 1.9 seconds of CPU of the potential 8 seconds/second that it can use.We are not CPU BoundĬonsistent gets + db block gets = Logical ReadsĪs a process, Oracle will try to see if the data is available in Buffer cache i.e. In this case DB CPU (s) : 1.9 (per second) is reporting that the system We have 8 CORES and so we can potentially use 8 seconds of CPU time per second. Same as DB time it does not include background process. Its the amount of CPU time spent on user calls. Any unusual statements? Or usual statements processed more usual than often? Or produce more rows per execution than usual? Also, be sure to take a good look in the segments statistics section (segments by physical writes, segments by DB block changes etc.) to see if there are any clues there. Just keep an eye open for any suspicious DML activity. What do you do if you find that redo generation is too high (and there is no business reason for that)? Not much really - since there is no “SQL ordered by redo” in the AWR report. High redo figures mean that either lots of new data is being saved into the database, or existing data is undergoing lots of changes. The main sources of redo are (in roughly descending order): INSERT, UPDATE and DELETE. Important thing to notice is that the configuration like CPU and Memory has not changed when the performance is degraded. This contains information about the Database and environment. > us – microsecond – 1000000th of a second TIME UNITS USED IN VARIOUS SECTIONS OF AWR REPORTS Increase the retention period : Some instances where you get more performance issues you should increase the retention time so that you can have historical data to compare. Use ASH also : Use AWR to identify the troublesome areas and then use ASH to confirm those areas.Ħ. ![]() FOR RAC, take each instance’s individual report:For RAC environment, you need to do it separately of all the instances in the RAC to see if all the instances are balanced the way they should be.ĥ. it is better to have three reports each for one hour. Split Large AWR Report into Smaller Reports: Instead of having one report for long time like one report for 3 hrs. Stick to Particular Time: You must have a specific time when Database was slow so that you can choose a shorter timeframe to get a more precise report.ģ. Collect Multiple AWR Reports: It’s beneficial to have two AWR Reports, one for the good time and other when performance is poor or you can create three reports (Before/Meantime/After reports) during the time frame problem was experienced and compare it with the time frame before and after.Ģ. To start with let us mention some high level important tips regarding AWR:ġ. Note that this is not comprehensive information and goal is to help in giving an overview of few key sections to Junior DBAs as a primer and to encourage them to build further the knowledge in related fields. Please note that explaining all sections of AWR will not be possible so we will stick to some of the most frequently used sections. ![]() In this post we will try to explain some important sections of AWR, significance of those sections and also some important tips. The report generated by AWR is a big report and it can take years of experience to actually understand all aspects of this report. The Oracle’s Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection and self-tuning purposes.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |