Re: changing initialization parameters

In article <1017047956.323479_at_turtle.ru.ac.za>, "Tonia says...
>
>Thank you Howard.
>
>That helps a lot. I just have one more question. Can I change the
>SGA_MAX_SIZE as I see it is not in the init.ora file by default. If not then
>what determines its value?
>
>Tonia

Sure. From the Reference Guide for 9i:

Default value Initial size of SGA at startup, dependent on the sizes of different pools in the SGA, such as buffer cache, shared pool, large pool, and so on.  

Parameter class Static  

Range of values 0 to operating system-dependent  

The interesting thing here is the parameter class. Static means you can change the value but you have to bounce the instance to do it. Put it anywhere in your init.ora file (preferably you're using a SPFILE in 9i) and then restart the instance. If you are using an SPFILE, remember you can't edit that directly so from the SQL prompt issue:

ALTER SYSTEM SET SGA_MAX_SIZE=your_value SCOPE=SPFILE;

then restart the instance.
>
>"Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
>news:a7ir84$ns3$1_at_lust.ihug.co.nz...
>> The shared_pool_size is indeed dynamic: but the entire SGA must fit in the
>> SGA_MAX_SIZE, which isn't. In other words, sga_max_size sets a ceiling on
>> what the separate components of the SGA can grow to. Hence, if
>sga_max_size
>> was (say), 64Mb, and your buffer cache was 32Mb, your shared pool was 32Mb
>> (leave out other components such as log buffer and java pool for the sake
>of
>> simplifying the discussion for now!), then alter system set
>> shared_pool_size=48M would fail, because 48+32>64.
>>
>> As for not being able to create different block sizes....
>>
>> You must still create the database specifying one block size. That's then
>> known as the 'standard block size', and it's the one used by the system
>> tablespace, and any temporary tablespace you have. All other blocksizes
>(up
>> to 32K) are then permissible *provided you add in the appropriate bits to
>> the buffer cache*. Because all blocks are destined at some point to be
>read
>> into the buffer cache, you have to allow for a 2K, 4K etc cache before you
>> are permitted to create a non-standard block size tablespace.
>>
>> Hence:
>>
>> SQL> create tablespace nonstd datafile
>'d:\oracle\oradata\db9\nonstd01.dbf'
>> size 3m blocksize 4K;
>> create tablespace nonstd datafile 'd:\oracle\oradata\db9\nonstd01.dbf'
>size
>> 3m blocksize 4K
>> *
>> ERROR at line 1:
>> ORA-29339: tablespace block size 4096 does not match configured block
>sizes
>>
>> ===================
>> This attempt to create an odd-sized blocksize tablespace fails because I
>> haven't previously established a non-standard cache to handle 4K blocks.
>> ====================
>>
>> SQL> alter system set db_4K_cache_size=4M;
>> alter system set db_4K_cache_size=4M
>> *
>> ERROR at line 1:
>> ORA-02097: parameter cannot be modified because specified value is invalid
>> ORA-00384: Insufficient memory to grow cache
>>
>> ===================
>> This attempt to set up the non-standard cache fails because adding an
>extra
>> 4M to my SGA would take it over the sga_max_size limit. I should really
>fix
>> that in the init.ora (or spfile) and bounce the Instance. But there's a
>> quick and dirty workaround:
>> ===================
>>
>> SQL> show parameter shared_pool
>>
>> NAME TYPE VALUE
>> ------------------------------------ ----------- -------------------------
>--
>> ---
>> shared_pool_reserved_size big integer 1887436
>> shared_pool_size big integer 50331648
>> SQL> alter system set shared_pool_size=32M;
>>
>> System altered.
>>
>> ===================
>> Having discovered my shared pool was about 48M, I resized it downwards to
>> 32M. Thereby freeing up 16M of memory (and thus taking me down below the
>> max_size for the sga). (Actually it doesn't really free any memory up at
>> all: as far as the O/S is concerned, it's the sga_max_size which is
>reserved
>> for and in use by Oracle. But that's a different matter. For now:
>> ====================
>>
>> SQL> alter system set db_4K_cache_size=4M;
>>
>> System altered.
>>
>> ====================
>> Because I've now got16M of head room under the sga_max_size ceiling, the
>> creation of the 4K cache works. That now allows me to do this:
>> ====================
>>
>> SQL> create tablespace nonstd datafile
>'d:\oracle\oradata\db9\nonstd01.dbf'
>> size 3m blocksize 4K;
>>
>> Tablespace created.
>>
>> =====================
>> And the tablespace now gets created just fine.
>> =====================
>>
>> So there you have it. Just bear in mind that the minimum unit of memory
>> allocation and deallocation is 1 granule. And a granule is either 4Mb in
>> size if sga_max_size is set to less than 128Mb, or 16Mb if it's set to
>128Mb
>> or over. In most cases, though, Oracle will silently round up your
>> requested memory allocations and de-allocations to a granule boundary -if
>> I'd asked for a 1Mb 4k blocksize cache, I'd have been given a 4Mb one
>> anyway. And that would also have happened when shrinking my Shared
>Pool -if
>> I'd asked it to be sized to, say 46Mb, thinking I was going to get 2Mb
>back,
>> tough luck: that would have been rounded up to 48Mb, and the net result
>> would have been that the shared pool didn't shrink at all.... and hence my
>> subsequent attempt to create a bit of 4K blocksize cache would have failed
>> again.
>>
>> Regards
>> HJR
>> --
>> ----------------------------------------------
>> Resources for Oracle: http://www.hjrdba.com
>> ===============================
>>
>>
>> "Tonia Stakemire" <g00s3874_at_campus.ru.ac.za> wrote in message
>> news:1016880640.26669_at_turtle.ru.ac.za...
>> > Hello Everyone
>> >
>> > I have oracle 9 installed on windows 2000. I am trying to change the
>> > settings of the database such as block_size and the sizes of different
>> pools
>> > and caches that make up the SGA.
>> >
>> > I know that you can't change the block size once the database has been
>> > created but it said that you can create tablespaces of different block
>> size.
>> > It won't allow me to do that or to set the db_nK_cache_size.
>> >
>> > It also said that the SGA size and size of the pools such as buffer
>cache
>> > can be changed dynamically during a session but it won't allow me to do
>> this
>> > either.
>> >
>> > At the moment the SGA is set at 77.769516MB and the sort area size is
>> 512KB.
>> > I have 1Gig of Ram.
>> >
>> > Please help. What am I not doing that I should?
>> > Thank you
>> > Tonia
>> >
>> >
>>
>>
>
>

HTH. Additions and corrections welcome.

Pete

SELECT standard_disclaimer, witty_remark FROM company_requirements; Received on Mon Mar 25 2002 - 10:52:40 CST