Re: 10G - editing SPFILE

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Thu, 21 Oct 2004 09:24:57 +1000
Message-Id: <4176f3c3$0$29780$afc38c87@news.optusnet.com.au>

Holger Baer wrote:

> In addition to what HJR said, lowering your sga_target changes nothing
> with regard to the memory that gets allocated on instance startup.

I don't think that's quite right, Holger. Not entirely, anyway. Here's a quick test:

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 138412032 bytes

Fixed Size                   777796 bytes
Variable Size             112206268 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes

Database mounted.
Database opened.

Note at this point my SGA is about 132M in size (I think!).

SQL> alter system reset sga_max_size scope=spfile sid='*'; alter system reset sga_max_size scope=spfile sid='*' *
ERROR at line 1:
ORA-32010: cannot find entry to delete in SPFILE

SQL> alter system reset db_cache_size scope=spfile sid='*'; System altered.

SQL> alter system reset shared_pool_size scope=spfile sid='*'; System altered.

SQL> alter system reset large_pool_size scope=spfile sid='*'; System altered.

SQL> alter system reset java_pool_size scope=spfile sid='*'; System altered.

SQL> alter system set sga_target=64M scope=spfile; System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 67108864 bytes

Fixed Size                   777456 bytes
Variable Size              49292048 bytes
Database Buffers           16777216 bytes
Redo Buffers                 262144 bytes

Database mounted.
Database opened.

And now my SGA is indeed approaching the 64MB I specified as the target. Setting the target *does*, therefore, affect the memory allocated at instance startup... provided, as all those 'alter system' commands I issued indicate, you don't have manual settings for those parameters so that the automatic targetting can kick in as intended. And that's a big proviso, as I'll demonstrate in just a second.

(It's also fair to say that SGA_TARGET does nothing for the size of the log buffer, the streams pool and an awful lot else).

Fair call, however, to say that merely setting a target and leaving all those other parameters unchanged would not achieve very much!

> You should try to lower sga_max_size (but be sensible about it), around
> 120M is the lower limit you can use to play around but it ain't gonna be
> fun!

This is true. If SGA_MAX_SIZE is set (and again, above, I made sure I didn't), then its setting is pinched from the O/S regardless of SGA_TARGET setting:

SQL> alter system set sga_max_size=132M scope=spfile;

System altered.

SQL> startup force
ORACLE instance started

Total System Global Area 138412032 bytes

Fixed Size                   777816 bytes
Variable Size             124789160 bytes
Database Buffers           12582912 bytes
Redo Buffers                 262144 bytes

Database mounted.
Database opened.

SQL> show parameter sga

NAME                    TYPE         VALUE
----------------------- ------------ ------------------------
lock_sga                boolean      FALSE
pre_page_sga            boolean      FALSE
sga_max_size            big integer  132M
sga_target              big integer  64M

So the "Total System Global Area" matches the 132M of the MAX_SIZE and not the 64M of the TARGET.

I find that confusing myself, since I would have thought TARGET was what you would originally get allocated, and MAX_SIZE just sets a ceiling on what you can expand to, dynamically, over time.

I've still to get my head around all this 10g automation, I guess!

It does mean that your original statement is likely to be practically true for the OP. But as well as lowering his SGA_MAX_SIZE, he could abolish it altogether to allow the TARGET to kick in.

Regards
HJR
>
> Regards,
> Holger
Received on Wed Oct 20 2004 - 18:24:57 CDT