Using Google Chrome?

Download my
free Chrome Extension, Power Notes Searcher, to make searching for and evaluating SAP notes, much easier.

Recent Posts

Thursday, January 26, 2012

What's the Current Size of AWR in the Oracle Database?

Print Friendly and PDF
If you have to size a new Oracle 10g database, or you just want to increase the AWR snapshot interval, you may want to know how much database space is being used by the current AWR setup.

This is where V$SYSAUX_OCCUPANTS comes in.

SQL> set linesize 300
SQL> col OCCUPANT_NAME format A7
SQL> SELECT OCCUPANT_NAME,
           OCCUPANT_DESC,
           SPACE_USAGE_KBYTES
           FROM v$sysaux_occupants where OCCUPANT_NAME ='SM/AWR';

OCCUPAN OCCUPANT_DESC                                         SPACE_USAGE_KBYTES
------- ----------------------------------------------------- ------------------
SM/AWR  Server Manageability - Automatic Workload Repository  107008


The above was taken from a system with a default AWR setup (retention of 7 days, snapping every hour).
This is approximately 14MB per day.

If you were thinking you were going to keep 60 days, you would expect about 900MB of data in SYSAUX.

No comments: