recomendations

[Fredy Correa]

good night, I have to configure a server hpux 11.11 with oracle 9.2.0.5 enterprise, and I should leave working him in the way but quick possible, that value I should consider in the parameters of the hp and of the oracle so that the performance is it but good... thank you.. it is very urgent.. I hardly have time neither experience... the server is hpux 11.11, with 2 processors of 1.0 gb and 24 gb of memory.
The storage is sam..
Thank you...

[Eric Antunes]

Hi Fredy,

Do you want to start with HP-UX or with Oracle tuning?

Starting with Oracle (since this is the database forum), can you give me the following answers:

- Database type (OLTP, Datawarehouse, ...)?
- Database size (Gb)
- db_block_size, db_block_buffers (db_cache_size in 9i?), shared_pool_size, log_checkpoint_interval (should be twice the redo logs size in Kb), log_checkpoint_timeout, disk_asynch_io (do you have raw devices?), log_buffer, sort_area_size and db_file_multiblock_read_count

PS: after replying, please check this:

http://forums1.itrc.hp.com/service/forums/helptips.do?#22

Best Regards,

Eric Antunes

[Fredy Correa]

Do you want to start with HP-UX or with Oracle tuning?
R: with both

Starting with Oracle (since this is the database forum), can you give me the following answers:

- Database type (OLTP, Datawarehouse, ...)?
R: Datawarehouse

- Database size (Gb)
R: 950 gb

- db_block_size : 16k
- db_block_buffers (db_cache_size in 9i?):32 megas
shared_pool_size : 536870912
log_checkpoint_interval: 20000000
log_checkpoint_timeout : 0
disk_asynch_io : true
do you have raw devices?: yes
log_buffer : 32768000
sort_area_size : 1638400
db_file_multiblock_read_count : 128

Thanks..

[Eric Antunes]

Ok,

1- Run the following queries and save the results in a file:

select (bbc.total_waits*100/(cg.value+dbg.value)) "Buff busy ratio ind. <= 0,007"
from v$system_event bbc,
v$sysstat cg, v$sysstat dbg
where bbc.event='buffer busy waits'
and cg.name ='consistent gets'
and dbg.name='db block gets';

select round(((1-(sum(decode(name, 'physical reads', value,0)) /
(sum(decode(name,'db block gets', value,0))+
(sum(decode(name, 'consistent gets', value, 0))))))*100),2)
|| '%' "Buffer Cache Hit Ratio > 95%"
from v$sysstat;

select wast.count, wast.class, sum( syst.value), wast.count/sum( syst.value)*100 "Ratio < 0,01"
from v$sysstat syst, v$waitstat wast
where syst.name in ('db block gets', 'consistent gets') and
wast.count != 0
group by wast.count, wast.class
order by 4 desc

2- You may increase your db_block_buffers/db_cache_size to 64 Mb

3- log_checkpoint_interval should be TWICE the redolog size. Your redolog groups size is:

select group# "Group", bytes / 1024 "Size (Kb)", status from v$log;

log_checkpoint_interval = 2 * Size (Kb)

4- log_checkpoint_timeout should be different than 0 (recomended by Oracle). Set it to 7200 seconds for example (2 hours):

log_checkpoint_timeout = 7200

5- log_buffer = 1048576 (1Mb is enough. Sizing it above won't give better performance...)

6- Check for fragmentation in your segments:

select count(*), segment_name
from dba_extents
where owner not in ('SYS','SYSTEM')
group by segment_name
having count(*) > 10
order by 1 desc

Check for possible free extents to rebuild the indexes in each specific tablespace:

select file_id, block_id, blocks,
owner||'.'||segment_name "Name"
from sys.dba_extents
where tablespace_name = upper(:p1x)
union
select file_id, block_id, blocks,
'Free'
from sys.dba_free_space
where tablespace_name = upper(:p1x)
order by 1,2,3

And reduce fragmentation with:

alter index . rebuild
storage( initial M next M);

alter tablespace :p1x coalesce;

7- Be aware of chaining. Execute the following query results (you may have to run @$ORACLE_HOME/rdbms/admin/utlchain.sql):

select 'ANALYZE table '||owner||'.'||table_name||' LIST CHAINED ROWS;'
from all_tables
where owner not in ('SYS', 'SYSTEM')
order by owner;

After running the analyze commands, check system.chained_rows table for troubles:

select count(*), table_name
from system.chained_rows
group by table_name
having count(*) > 50
order by 1 desc

8- Run the (1) queries again tomorow and see if something gets better or worse.

9- Finally, you should (and I should ;) ) install statspack for better understanding where the RDBMS spends its time.

Best Regards,

Eric Antunes

[Fredy Correa]

this result of querys..

[Eric Antunes]

As I suspected, you have a low Buffer Cache Hit Ratio. It will get better after you increase db_block_buffers/db_cache_size as suggested in point 2.