Re: Oracle RAC and AWR

wagen123_at_yahoo.com wrote:
> Oracle10g 10.2.0.2 (64 bit)
> RHEL AS 4.3 (64 bit)
> 4 Node RAC cluster
>
> We execute AWR snapshots every 15 minutes during stress/load testing in
> our QA environment. To identify the resource intensive SQL's currently
> I have to look into each AWR report generated on EACH node of the RAC
> cluster.
>
> Is there any way to get a consolidated view across all nodes of the RAC
> cluster? I tried 10g Grid Control, couldn't find a way out.
>
> Thanks for any help/pointers.
>
> wagen
>

Have a look at ?@/rdbms/admin/awrrpt.sql script, which can be used in SQL*Plus to generate the AWR report. This script calls the awrrpti.sql script, which does give us some insight in to how the report is generated. From awrrpti.sql, we can see a call to the DBMS_WORKLOAD_REPOSITORY supplied package. Depending on your options, you are probably running the AWR_REPORT_HTML or AWR_REPORT_TEXT procedure. Looking at the Oracle docs, we find that these procedures require the instance number as a parameter. Since OEM and the awrrpt.sql script both use the DBMS_WORKLOAD_REPOSITORY supplied package, and the package requires an instance number, I would say that on first glance, you cannot get an aggregate report using the tools that Oracle has given you.

The @?/rdbms/admin/prvtawr.plb script shows the wrapped contents of this package. Pete Finnigan has a method to unencrypt the wrapped package on his web site. This will let you see how the report is generated. Armed with this knowledge, you can probably generate your own routine to create some sort of aggregated report. After all, the data is just sitting there in tables, so you can write your own queries against it should you choose.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown