| 8i Inserts + Updates SLOW ! [message #65604] |
Thu, 04 November 2004 04:04  |
saustin
Messages: 43
Registered: June 2003
|
Member |
|
|
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:ORADATAVMFGctlfilesctl1VMFG.ora, D:ORADATAVMFGctlfilesctl2VMFG.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:ORADATAVMFGadminbdump
user_dump_dest = D:ORADATAVMFGadminudump
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:ORADATAVMFGLOGFILESLOG4A.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:ORADATAVMFGLOGFILESLOG5A.ORA
Any suggestions (besides going to linux which we are looking at) greatly appreciated !
Steve.
|
|
|
|
| Re: 8i Inserts + Updates SLOW ! [message #65606 is a reply to message #65604] |
Thu, 04 November 2004 04:23  |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
1. You can disable logging.
-> your RECOVERY/RESTORATION need be compromised in certain aspects.
2. You can disable the indexes during inserts.
-> But to enable the indexes again, it will take the some time and Overall time would be almost
-> the same.
-> Would be disastrous during updates!!.
insert peformance and indexes are inversely proportional.
updates (where there is a select statement embedded) and indexes MAY work good (in some cases, a full table scan is good).
First is there a measurable metric for performance involved here?
without any of those, we cannot guess anything.
ANALYZE the table and indexes regularly.
Compare the plans.
Run statspack or any such tool understand whats happening.
Try to reduce the LIO.
If there are concurrent inserts from many sessions, looking into using more FREELISTS.
and always use bind variables as possible.
>>Was looking to increase db_block_buffers as the buffer cache hit ratio is a poor 50%.
considering hitratio is flawed.
Please have a look here
http://www.orafaq.com/forum/t/23453/0/
[Updated on: Fri, 18 February 2005 23:32] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
