Re: changing initialization parameters

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

"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

> >

> >

>

>