sizing large pool

[bookouri]

Can anyone give me a rule of thumb for sizing the Large Pool in 9i? Im having connection failures at peak user periods, and most of the references I find suggest reducing the size of the SGA. Oracle Enterprise manager reports that I currently have these settings:
shared pool 232
buffer cache 888
large pool 144
java pool 8
total sga 1272.935
sga max size 1305.072

This is on a Windows 2000 Server with 4gb RAM. Performance is great and the database is running without problems except for those peak periods when users cant log on.

 

[HimanB]

Hi,
Please refer to Note:1012046.6, 105004.1 & 30487.1 at Metalink.

Also following is the Script for Current Shared Pool Size, Sum
of Shared Pool Objects, Sum of SQL Size, Sum of User Size and the Minumum suggested Shared Pool Size for this Instance.

spool minshpool.lst
set numwidth 15
column shared_pool_size format 999,999,999
column sum_obj_size format 999,999,999
column sum_sql_size format 999,999,999
column sum_user_size format 999,999,999
column min_shared_pool format 999,999,999
select to_number(value) shared_pool_size, 
                         sum_obj_size,
                         sum_sql_size, 
                         sum_user_size, 
(sum_obj_size + sum_sql_size+sum_user_size)* 1.3 min_shared_pool
  from (select sum(sharable_mem) sum_obj_size 
          from v$db_object_cache where type <> 'CURSOR'),
               (select sum(sharable_mem) sum_sql_size
          from v$sqlarea),
               (select sum(250 * users_opening) sum_user_size
          from v$sqlarea), v$parameter
 where name = 'shared_pool_size';
spool off

HTH
Regards
Himanshu

 

[sem]

Do you work with MTS or dedicated server? Do users get any error or just hang?

Regards, Dima

http://seminihin.narod.ru

 

[bookouri]

Dedicated Server

The users just get a tns failure message. In the listener.log you see something like:
TNS-12500: TNS:listener failed to start a dedicated server process
TNS-12540: TNS:internal limit restriction exceeded
TNS-12560: TNSrotocol adapter error
TNS-00510: Internal limit restriction exceeded
32-bit Windows Error: 8: Exec format error

Ive researched this on metalink and what answers I found were usually 8i related and/or didnt work for me when i tried them. But everything points to a memory problem of some kind. Ive increased the processes to 550 so that's not a problem.

If the users keep trying they will eventually get a connection, there just seems to be a maximum number of connections that can be sustained at one time.