Posted by Riyaj Shamsudeen on November 12, 2013
It is easier to create one or two AWR reports quickly using OEM. But, what if you have to create AWR reports for many snapshots? For example, your Oracle support analyst wants you to supply 10 1-hour AWR reports from 10AM to 8PM in a 8 node cluster? That’s about 80 AWR reports to create! Okay, okay, I may(!) be overselling it, but you get the point. It is useful to have a script to create AWR report for all instances for a given range of snapshot IDs. Following scripts are handy:
|1. To create one AWR report per instance, for the last snap duration :
|2. Same as (1) but in html format :
|3. To create one AWR report per instance, for a range of snap IDs :
|4. To create one AWR report, per instance, per snap ID :
Zip file: awrrpt_scripts
These scripts do not modify anything in the database, just retrieves the data using dbms_workload_repository package. Test the scripts to understand further. Of course, you need access to dbms_workload_repository and access to gv$instance.
Posted in Oracle database internals, Performance tuning, RAC | Tagged: AWR reports, awrrpt.sql, awrrpt_all_gen.sql, awrrpt_all_range_gen.sql | 2 Comments »
Posted by Riyaj Shamsudeen on December 23, 2009
Global cache performance metrics are not correctly measured. It is not understood clearly either. There are even few blogs and web pages disseminating incorrect information. This blog entry is an attempt to offer few methods and scripts to understand global cache performance.
Always review all instances
It is very important to review the performance metrics from all instances in that RAC cluster, not just one instance that you are connected. If you have access to AWR reports, then it is critical to generate AWR reports (or statspack reports) from all instances. But, the problem is that, DBAs tend to generate AWR reports after logging in to each instance iteratively, enter couple of parameters and then reports are generated. Not exactly a convenient practice.
REM connect to each instance separately, type in the beginning snap_id and ending snap_id for each node etc..
There are few issues with this approach. It is a cumbersome practice if the instance count is higher. In addition to that, all of AWR reports are, in turn, accessing underlying AWR tables. Physically, rows from all instances are together in the same block and so, by executing these reports connecting to various instances, Global cache traffic is increased. If the database is suffering from Global cache (GC) performance issues then generating reports connecting to various instances is probably not a grand idea.
Posted in Oracle database internals, Performance tuning, RAC | Tagged: Average GC CR receive time, average gc CURRENT receive time, awrrpt.sql, gc cr block build time, gc cr block flush time, gc cr block receive time, oracle performance, RAC performance, RAC performance myths | 23 Comments »