sizing Archives » Musings of an IT Implementor

As an example, a non-Unicode SAP table (DBTABLOG) in an Oracle 11gR2 database (character set ALE32UTF16) without any Oracle compression, was ~80GB in total size (sum of table segments, excluding indexes).

Once this was exported to disk using R3load export, in preparation for a conversion to Unicode, it occupied ~70GB in the data files (DBTABLOG.00*).

Once this was re-imported into a new Oracle 11gR2 database with character set UTF8 (again, no compression) it occupied ~90GB (sum of segments, excluding indexes).

You must remember that this table is specific in it’s usage. It doesn’t have any rows deleted from it, it’s append only, so it should grow in a nice uniform manner and not be fragmented. There may be other tables where you could save space.
If you notice that your R3load export files are significantly different in size compared to the Oracle size, then you could have some serious fragmentation inside your Oracle database.

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.

Tag: sizing

SAP Unicode Conversion Sample DB Table Size

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