8i Inserts + Updates SLOW ! 2

[saustin]

Hi,
Am looking for any wisdom on improving the speed of an INSERT or UPDATE on an 8i W2k server with 3 gig of memory.
Unfortunately with raid 5 we are very limited BUT am curious to see what others might think of which could improve performance.
Here is some of the alert log with specifics. Was looking to increase db_block_buffers as the buffer cache hit ratio is a poor 50%.

Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Starting up ORACLE RDBMS Version: 8.1.7.4.1.
System parameters with non-default values:
processes = 300
shared_pool_size = 209715200
control_files = D:\ORADATA\VMFG\ctlfiles\ctl1VMFG.ora, D:\ORADATA\VMFG\ctlfiles\ctl2VMFG.ora
db_block_buffers = 38400
db_block_size = 8192
_db_handles_cached = 0
compatible = 8.0.5.2.1
log_buffer = 163840
log_checkpoint_interval = 10000
db_files = 100
db_file_multiblock_read_count= 32
rollback_segments = rb1, rb2, rb3, rb4, rb5, rb6, rb7, rb8, rb9, rb10, rb11, rb12, rb13, rb14, rb15, rb16
remote_login_passwordfile= SHARED
db_domain = world
global_names = TRUE
distributed_transactions = 5
service_names = VMFG.WORLD
open_links = 4
sort_area_size = 1000000
sort_area_retained_size = 100000
db_name = VMFG
open_cursors = 300
_sqlexec_progression_cost= 0
utl_file_dir = *
job_queue_interval = 10
parallel_max_servers = 8
background_dump_dest = D:\ORADATA\VMFG\admin\bdump
user_dump_dest = D:\ORADATA\VMFG\admin\udump
max_dump_file_size = 10240
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
Wed Nov 03 17:50:30 2004
alter database mount exclusive
Wed Nov 03 17:50:35 2004
Successful mount of redo thread 1, with mount id 3711276330.
Wed Nov 03 17:50:35 2004
Database mounted in Exclusive Mode.
Completed: alter database mount exclusive
Wed Nov 03 17:50:35 2004
alter database open
Wed Nov 03 17:50:35 2004
Thread 1 opened at log sequence 414
Current log# 4 seq# 414 mem# 0: D:\ORADATA\VMFG\LOGFILES\LOG4A.ORA
Successful open of redo thread 1.
Wed Nov 03 17:50:35 2004
SMON: enabling cache recovery
SMON: enabling tx recovery
Wed Nov 03 17:50:36 2004
Completed: alter database open
Thu Nov 04 06:24:55 2004
Thread 1 advanced to log sequence 415
Current log# 5 seq# 415 mem# 0: D:\ORADATA\VMFG\LOGFILES\LOG5A.ORA

Any suggestions (besides going to linux which we are looking at) greatly appreciated !

Steve.

 

[carp]

With 50% hit ratio, increasing block buffers will probably help quite a bit - particularly with updates. Inserts might also benefit as you are more likely to have less trouble finding a clean block to use.
Is this a problem across all tables, or just some? If the latter, you might also want to see if you have indexes that aren't really being used. Dropping unneeded indexes will cut down your DML overhead.

 

[carp]

You might also want to take a peek at your rollback segments to see if you have a contention problem there.

 

[sem]

Try to increase log_buffer. And first of all revise execution plans.

Regards, Dima

http://seminihin.narod.ru

 

[saustin]

Hi Dima,
Many thanks for the reply. Ran this to get a handle on the LOG_BUFFER.
SQL> SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME='redo buffer allocation retries';

NAME VALUE
---------------------------------------------------------------- ----------
redo buffer allocation retries 14

SQL>
SQL> SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME='redo entries';

NAME VALUE
---------------------------------------------------------------- ----------
redo entries 267175

Did not seem to the untrained eye that this was big problem here if one believes that LOG_BUFFER only needs to be increased if this is over 1 %.

Steve.

 

[saustin]

Many thanks for the reply Carp ! This is the current value.
SQL> select 1 - (phy.value / (cur.value + con.value)) "Cache Hit Ratio" from v$sysstat cur, v$syssta
t con, v$sysstat phy where cur.name = 'db block gets' and con.name = 'consistent gets' and phy.name
= 'physical reads';

Cache Hit Ratio
---------------
.581754813

Do not know of a good way of determining the ideal value of db_block_buffers. Originally had it at 200000 and we were saving in 2 seconds (unlike the 45 seconds now) but would get the ora-04301 error messages after a while. Steve.

 

[saustin]

Hi Again Dima,
Stand corrected. This is from the databse tuning site

http://www.cryer.co.uk/brian/oracle/tuning_tlb.htm

Select name, value from v$sysstat
Where name = 'redo log space requests';

The number of waits should always be zero. If not, increase the size of LOG_BUFFER, until the number returns to zero. Typically, there is no advantage in setting this beyond 1M.