I'm surprised
CJ, December 03, 2002 - 8:04 am UTC
I'm a little surprised that you didn't just point to the documentation.
December 03, 2002 - 9:31 am UTC
I've done so quite a few times but this keeps coming up so I answered it and published it.
I've even had people tell me "you don't document this" -- even after being pointed to the exact piece of documentation.
I don't get it -- I think people have been trained that "all documentation stinks, don't even bother with it" or "Oracle documentation stinks, forget about it". How many questions begin with "don't point me to the documentation...." that is for sure the fastest way to get me to point you to the documentation ;)
ORA-00384: Insufficient memory to grow cache
A reader, May 03, 2003 - 12:21 am UTC
tom,
i got this error when i tried to set 16k cache size.
SQL> alter system set db_16k_cache_size=11m;
alter system set db_16k_cache_size=11m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache
should i have to increase my sga? - if so how?
or
should i have to increase my RAM? - (only)this i know :)
thanks for your terrific job
May 03, 2003 - 11:52 am UTC
you did not set an SGA_MAX_SIZE -- hence it will default to the size of the allocated SGA -- meaning if you want to set the 16k cache size to X, you'll need to decrease some other component by X first -- eg, shrink the db_CACHE_SIZE, then you can increase the db_16k cach.
SGA_MAX_SIZE
reader, May 03, 2003 - 12:37 pm UTC
SQL> alter system set db_cache_size=16m;
System altered.
SQL> alter system set db_16k_cache_size=16m;
System altered.
SQL> select id,name,block_size, current_size,buffers from v$buffer_pool;
ID NAME BLOCK_SIZE CURRENT_SIZE BUFFERS
---------- -------------------- ---------- ------------ ----------
3 DEFAULT 4096 16 3916
7 DEFAULT 16384 16 1012
Now i could set it. - Thank you!
one more question, if i dont set sga_max_size what is the default size, is that the sga size equals sga_max_size ?
SQL> sho parameters SGA_MAX_SIZE
NAME TYPE VALUE
------------------------------------ ----------- --------------------
sga_max_size big integer 122755896
SQL> sho sga
Total System Global Area 122755896 bytes
.
.
thanks
May 03, 2003 - 7:36 pm UTC
sga max size defaults to the size of the sga.
v$buffer_pool
Parag Jayant Patankar, March 05, 2005 - 4:53 am UTC
Hi Tom,
Following is select statment from v$buffer_pool of a system which had been recently migrated from 8.1.7 to 9.2 and still using db_block_buffer parameter
SQL> select * from v$buffer_pool;
ID NAME BLOCK_SIZE RESIZE_STA CURRENT_SIZE BUFFERS
---------- -------------------- ---------- ---------- ------------ ----------
TARGET_SIZE TARGET_BUFFERS PREV_SIZE PREV_BUFFERS LO_BNUM HI_BNUM
----------- -------------- ---------- ------------ ---------- ----------
LO_SETID HI_SETID SET_COUNT
---------- ---------- ----------
3 DEFAULT 4096 STATIC 624 150000
624 150228 0 0 0 0
3 3 1
SQL> show parameters db_block
NAME TYPE VALUE
------------------------------------ ----------- -------
db_block_buffers integer 150000
db_block_checking boolean FALSE
db_block_checksum boolean TRUE
db_block_size integer 4096
According to my understanding current_size is a size of subcache in MBs. But if I do 15000*4096/(1024*1024)=586 mb(approx), which is not matching with the value 624 MB under current_size. Can you tell me why ?
regards & thanks
pjp
March 05, 2005 - 7:29 am UTC
let's see your init.ora
select name, value
from v$parameter
where isdefault = 'FALSE'
/
make sure to format that nicely please.
Details
Parag Jayant Patankar, March 05, 2005 - 7:51 am UTC
Hi Tom,
Pl find details below
18:17:53 SQL> l
1 select name, value
2 from v$parameter
3* where isdefault = 'FALSE'
18:18:05 SQL> /
NAME VALUE
-------------------------------------------------- -------------------------------------------------
processes 100
event 10181 trace name context forever, level 1
shared_pool_size 20971520
control_files
/apps/oradbf/atpp01/ctl1/ctl1atpp01.ctl,
/apps/oradbf/atpp01/ctl2/ctl2atpp01.ctl,
/apps/oradbf/atpp01/ctl3/ctl3atpp01.ctl
db_block_buffers 150000
compatible 9.2.0
log_archive_dest /apps/oradbf/atpp01/archive
log_buffer 2097152
log_checkpoint_interval 1000000
db_file_multiblock_read_count 16
dml_locks 10000
rollback_segments rbs_large
remote_os_authent TRUE
global_names FALSE
parallel_min_servers 4
parallel_max_servers 4
background_dump_dest /apps/oracle/admin/atpp01/bdump
user_dump_dest /apps/oracle/admin/atpp01/udump
max_dump_file_size 102400
core_dump_dest /apps/oracle/admin/atpp01/cdump
sort_area_size 2621440
sort_area_retained_size 0
db_name atpp01
ifile /apps/oracle/admin/atpp01/config/configatpp01.ora
optimizer_mode CHOOSE
25 rows selected.
regards & thanks
pjp
March 05, 2005 - 7:56 am UTC
why the event?
and output from
select * from v$sga_dynamic_components;
Parag Jayant Patankar, March 10, 2005 - 9:20 am UTC
Hi Tom,
Pl find details below
19:46:28 SQL> l
1 select substr(component,1,15), current_size, min_size, max_size,
2 OPER_COUNT,
3 LAST_OPER_TYPE,
4 LAST_OPER_MODE,
5 LAST_OPER_TIME,
6 GRANULE_SIZE
7* from v$sga_dynamic_components
19:46:38 SQL> /
X CURRENT_SIZE MIN_SIZE MAX_SIZE OPER_COUNT LAST_O LAST_O LAST_OPER GRANULE_SIZE
--------------- ------------ ---------- ---------- ---------- ------ ------ --------- ------------
shared pool 20971520 20971520 20971520 0 4194304
large pool 0 0 0 0 4194304
buffer cache 50331648 50331648 50331648 0 4194304
Sorry for my late reply.
regards & thanks
pjp
March 10, 2005 - 7:05 pm UTC
it is all about granules -- search the 9i docs for that word, or this site and you'll see information about it.
things are allocated in granules.