Please Help with SGA and Share Pool Size

[vbeer]

Dear guys,

My company is running Oracle 8.0.5 server and OAS, we have encoutered some problem with OS loading and Oracle process. For example, our CPU loading suddenly become in high usage for 10+ mins and suddenly dropped to normal.. we aslo found that our end users have slow response or request failed from accessing our server through OAS (WEB SERVER). I have done some testing while the CPU loading is high, and I found that "unallocated shared library ... etc" .. something like that..


So that, I tried to find the dead lock session which is still running at backgroup.. but I think it is not a good way to solve the problem.....I found one of the script on the web.. the report said... my shared pool size is too low... Please advice how I can solve the problem... or I really need to do some tunning for our SGA...


Thank you in Advance..

Yours Sincerely
Anders



======================Script=========================
DECLARE
libcac number(10,2);
rowcac number(10,2);
bufcac number(10,2);
redlog number(10,2);
spsize number;
blkbuf number;
logbuf number;
total number;
BEGIN
select value into redlog from v$sysstat
where name = 'redo log space requests';
select 100*(sum(pins)-sum(reloads))/sum(pins) into libcac from v$librarycache;
select 100*(sum(gets)-sum(getmisses))/sum(gets) into rowcac from v$rowcache;
select 100*(cur.value + con.value - phys.value)/(cur.value + con.value) into bufcac
from v$sysstat cur,v$sysstat con,v$sysstat phys,v$statname ncu,v$statname nco,v$statname nph
where cur.statistic# = ncu.statistic#
and ncu.name = 'db block gets'
and con.statistic# = nco.statistic#
and nco.name = 'consistent gets'
and phys.statistic# = nph.statistic#
and nph.name = 'physical reads';
select value into spsize from v$parameter where name = 'shared_pool_size';
select value into blkbuf from v$parameter where name = 'db_block_buffers';
select value into logbuf from v$parameter where name = 'log_buffer';
total := libcac + rowcac + bufcac + redlog;
dbms_output.put_line('> SGA CACHE STATISTICS');
dbms_output.put_line('> ********************');
dbms_output.put_line('> SQL Cache Hit rate = '||libcac);
dbms_output.put_line('> Dict Cache Hit rate = '||rowcac);
dbms_output.put_line('> Buffer Cache Hit rate = '||bufcac);
dbms_output.put_line('> Redo Log space requests = '||redlog);
dbms_output.put_line('> Total SGA = '||total);
dbms_output.put_line('> ');
dbms_output.put_line('> INIT.ORA SETTING');
dbms_output.put_line('> ****************');
dbms_output.put_line('> Shared Pool Size = '||spsize||' Bytes');
dbms_output.put_line('> DB Block Buffer = '||blkbuf||' Blocks');
dbms_output.put_line('> Log Buffer = '||logbuf||' Bytes');
dbms_output.put_line('> ');
if
libcac < 99 then dbms_output.put_line('*** HINT: Library Cache too low! Increase the Shared Pool Size.');
END IF;
if
rowcac < 85 then dbms_output.put_line('*** HINT: Row Cache too low! Increase the Shared Pool Size.');
END IF;
if
bufcac < 90 then dbms_output.put_line('*** HINT: Buffer Cache too low! Increase the DB Block Buffer value.');
END IF;
if
redlog > 100 then dbms_output.put_line('*** HINT: Log Buffer value is rather low!');
END IF;
END;
=====================OUTPUT OF MY ORACLE SGA=====================
> SGA CACHE STATISTICS
> ********************
> SQL Cache Hit rate = 96.14
> Dict Cache Hit rate = 95.71
> Buffer Cache Hit rate = 93.19
> Redo Log space requests = 11
> Total SGA = 296.04
>
> INIT.ORA SETTING
> ****************
> Shared Pool Size = 8000000 Bytes
> DB Block Buffer = 550 Blocks
> Log Buffer = 32768 Bytes
>
*** HINT: Library Cache too low! Increase the Shared Pool Size.

[SANJAY_G]

8MB Shared pool is too low specially when you have lots of PL/SQL code. Also you db_buffers is 550 only which will give you approximately 4 MB db buffer cache (with 8K block size). Which is also too low.
Is this application running in production??

[vbeer]

Hi... SANJAY_G

yes. .it is production server.. I was surprised with that as well.. coz. .I have never touch this machine before.... I just get a task to tune our Database... so please help and advice...


Thank you.. wait for you reply....

[adewri]

Better start reading some Performance Tuning Manuls and for the time being increase the shared_pool_size and db_buffers.

http://otn.oracle.com/documentation/oas.html

http://download-east.oracle.com/docs.../doc/index.htm

[vbeer]

Hi Guys.. I have just changed the shared_pool_size to 100MB and db_block_buffers = 3200..


========OS and Oracle Config======
oracle version 8.0.5
Sun Solaris 2.6
Phsyical Mem : 800+ MB..
=====================================

Can anyone help to check whether it is ok or not? Thankx..


################New Moddified INIT.ORA#########


# tuning parameters
# db_files = 80
db_files = 120

# db_file_multiblock_read_count = 8 # SMALL
# db_file_multiblock_read_count = 32 # LARGE
db_file_multiblock_read_count = 16 # MEDIUM


#db_block_buffers = 200 # SMALL
#db_block_buffers = 550 # MEDIUM
#db_block_buffers = 550 # CUSTOM
#changed by Anders Wong 24-June-2003
db_block_buffers = 3200 # LARGE

#shared_pool_size = 3500000 # SMALL
#shared_pool_size = 6000000 # MEDIUM
#shared_pool_size = 9000000 # LARGE
#shared_pool_size = 8000000 # CUSTOM
#SET TO 100MB By Anders Wong 24-June-2003
shared_pool_size = 100000000

log_checkpoint_interval = 10000

# processes = 50 # SMALL
processes = 120 # MEDIUM
# processes = 200 # LARGE

#dml_locks = 100 # SMALL
dml_locks = 200 # MEDIUM
#dml_locks = 500 # LARGE

#log_buffer = 8192 # SMALL
log_buffer = 32768 # MEDIUM
# log_buffer = 163840 # LARGE

#sequence_cache_entries = 10 # SMALL
sequence_cache_entries = 30 # MEDIUM
# sequence_cache_entries = 100 # LARGE


#sequence_cache_hash_buckets = 10 # SMALL
sequence_cache_hash_buckets = 23 # MEDIUM
# sequence_cache_hash_buckets = 89 # LARGE


# audit_trail = true # if you want auditing
# timed_statistics = true # if you want timed statistics
max_dump_file_size = 10240 # limit trace file size to 5 Meg each

#log_archive_start = true # if you want automatic archiving
#log_archive_dest = /arch/ORP1/
#log_archive_format = %t_%s.arch
log_archive_start = true # if you want automatic archiving
log_archive_dest = /arch/ORP1/
log_archive_format = %t_%s.arch


# global_names = TRUE

# mts_dispatchers="ipc,1"
# mts_max_dispatchers=10
# mts_servers=1
# mts_max_servers=10
# mts_service=ORP1
# mts_listener_address="(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))"

#
# needed if running OPS
#
# PARALLEL_SERVER=TRUE

control_files = (/dbs01/sys/ORP1/control01.ctl,
/dbs02/sys/ORP1/control02.ctl,
/dbs03/sys/ORP1/control03.ctl)
# Below for possible future use...
#init_sql_files = (?/dbs/sql.bsq,
# ?/rdbms/admin/catalog.sql,
# ?/rdbms/admin/expvew.sql)
background_dump_dest = /oracle/admin/ORP1/bdump
core_dump_dest = /oracle/admin/ORP1/cdump
user_dump_dest = /oracle/admin/ORP1/udump
#log_archive_dest = /oracle/admin/ORP1/arch/arch.log
db_block_size = 8192

db_name = ORP1

nls_date_format = "dd-mon-yyyy"

UTL_FILE_DIR = *



######################
# Add Job Queue at 4-Sep-2001

job_queue_processes = 4

######################
# Increase open cursors at 27-nov-2001

open_cursors = 300