Re: How to determine size used by an Instance...

HARI OM wrote:
> I am using Oracle 9.2.0.4 on IBM AIX 5.1L
> I have 2 instances running on this Box.
> How do I determine how much memory and hard-disk space is being used by
> each instance? What is the best method or guesstimate?

Instances don't use disk space. That's what databases do.

There are a number of possibilities for sizing an instance in 9i. SGA_MAX_SIZE might be affecting things, for example. So might the traditional _SIZE and related parameters. And then there's the issue of knowing whether the memory we're talking about is 'real' or 'virtual'.

If, for example, I saw this:

LARGE_POOL_SIZE=100M
SHARED_POOL_SIZE=200M
DB_CACHE_SIZE=200M
LOG_BUFFER=3M
SGA_MAX_SIZE=800M ...then I'd say your SGA was 800M, because that's what the last line says, though you're actually only using 503MB of that total available allocation. The other 297MB might be allocated, allocated but paged, or just virtually available. In polite company, I'd say your instance was "up to 800M in size", and gloss over the subtleties!

In "traditional" Oracle practice, the SGA would simply have been the sum of the LARGE_POOL_SIZE, SHARED_POOL_SIZE, JAVA_POOL_SIZE, DB_CACHE_SIZE, DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE and any DB_nK_CACHE_SIZE parameters you might have set. Plus the LOG_BUFFER parameter. Putting it at its simplest, at least.

As for the size of your database:

select sum(bytes) from dba_data_files
+
select sum(bytes) from dba_temp_files
+
select sum(bytes) from v$log

You'll then need to add on the size of all your controlfile copies, because I can't think of a view that reports on the size of them from within the database itself.

Bear in mind that password files, spfiles and archive logs consume hard disk space, but don't actually constitute, technically, a part of an Oracle database... but you might nevertheless want to add those into your calculations.

Regards
HJR
> Any related informaiton on this is appreciated.
>
> Thanks!
>
> HARI OM
>
Received on Mon Dec 20 2004 - 13:59:16 CST