Re: changing initialization parameters

From: Howard J. Rogers <dba_at_hjrdba.com>
Date: Sun, 24 Mar 2002 08:13:50 +1100
Message-ID: <a7ir84$ns3$1@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
>
>

Received on Sat Mar 23 2002 - 15:13:50 CST