Re: Oracle8 panic! - lousy performance/high load

Tom Karlsson - Remove NOSPAM wrote:
>
> Hi Jim :)
>
> On Tue, 13 Nov 2001, Jim Kennedy wrote:
>
> > You have 1 disk drive for data. Your IO is highly limited.(probably about
> > 60 to 70 io's /sec) You need to spead the load.
>
> Actually, disk IO seems quite healthy. atsar shows this:
>
> Linux foobar 2.4.5 #2 SMP Thu May 31 12:45:22 CEST 2001 i686
> 11/13/2001
>
> 00:00:00 device read/s rdKb/s write/s wrKb/s rdwr/s
> _disk_
> 01:00:00 disk003-000 0.00 0.00 0.24 1.13 0.24
> disk008-000 0.02 0.07 0.40 3.73 0.42
> 02:00:00 disk003-000 0.00 0.00 0.21 0.97 0.21
> disk008-000 0.06 0.23 0.61 9.48 0.66
> 03:00:00 disk003-000 0.03 0.30 0.30 10.25 0.32
> disk008-000 0.15 3.25 0.30 1.98 0.46
> 04:00:01 disk003-000 14.32 554.30 0.35 1.90 14.67
> disk008-000 0.14 0.57 0.30 2.56 0.44
> 05:00:00 disk003-000 22.76 567.50 0.66 4.68 23.41
> disk008-000 0.05 0.21 0.22 1.04 0.27
> 06:00:00 disk003-000 16.01 185.37 0.48 2.70 16.49
> disk008-000 0.60 5.67 0.67 5.40 1.26
> 07:00:00 disk003-000 0.00 0.00 0.18 0.83 0.18
> disk008-000 0.01 0.17 0.18 0.71 0.19
> 08:00:00 disk003-000 0.02 0.36 0.27 2.02 0.29
> disk008-000 0.58 4.41 0.26 3.02 0.85
> 08:20:01 disk003-000 0.09 0.96 0.24 1.12 0.33
>
> Amount of reads and writes are generally low.
>
> > Also is the application
> > using bind variables? If not that will drive CPU usage way up.
>
> Could you briefly explain what bind variables is? (it's mostly perl and
> ASP CGI scripts utilizing the database).
>
> Thanks :)
>
> /T
>
> > "Tom Karlsson - Remove NOSPAM" <tk_at_babylon5.dkNOSPAM> wrote in message
> > news:Pine.LNX.4.21.0111131616000.24342-100000_at_sith.ammulti.dk...
> > > Hey all,
> > >
> > > I'm having severe load problems with an Oracle 8.0.5 for linux. Load
> > > average climbs above 25.00 in peak hours. It's pretty much a standard
> > > install running on a dual PIII-800, 640MB RAM, 1 system disk, 1 data scsi
> > > datadisk.
> > >
> > > Unfortunately I don't have the rocket scientist abilities to tune
> > > Oracle. As a last act of desperation I'll post my Oracle setup here,
> > > hoping some friendly soul will point out some obivious flaws, hints,
> > > anything is appreciated.
> > >
> > > init file settings:
> > >
> > > file =
> > > /u01/app/oracle/admin/orcl/pfile/configorcl.ora
> > >
> > > rollback_segments = (r01,r02,r03,r04)
> > >
> > > # tuning parameters
> > >
> > > # tuning parameters
> > >
> > > db_files = 80
> > >
> > > db_file_multiblock_read_count = 32 #
> > > LARGE
> > >
> > > db_block_buffers = 3200 #
> > > LARGE
> > >
> > > shared_pool_size = 9000000 #
> > > LARGE
> > >
> > > log_checkpoint_interval = 10000
> > >
> > > processes = 400
> > >
> > > dml_locks = 500 #
> > > LARGE
> > >
> > > log_buffer = 163840 #
> > > LARGE
> > >
> > > sequence_cache_entries = 100 #
> > > LARGE
> > >
> > > sequence_cache_hash_buckets = 10 #
> > > SMALL
> > >
> > > max_dump_file_size = 10240 # limit trace file size to 5 Meg each
> > >
> > > global_names = TRUE
> > >
> > > ----//----
> > >
> > > free command during high load:
> > >
> > > total used free shared buffers cached
> > > Mem: 642216 584456 57760 0 9272 336352
> > > -/+ buffers/cache: 238832 403384
> > > Swap: 1028120 8672 1019448
> > >
> > > ----//----
> > >
> > > w command during high load:
> > >
> > > 3:52pm up 15 days, 11:59, 3 users, load average: 23.00, 21.57, 18.79
> > > USER TTY FROM LOGIN@ IDLE JCPU PCPU WHAT
> > >
> > > ----//----
> > >
> > > there's over a hundred of the oracleorcl processes showing in a top:
> > >
> > > 000 501 21357 1 9 0 35668 3660 semop S ? 1:08
> > > ora_pmon_or
> > > 000 501 21359 1 16 0 36568 11244 - R ? 60:30
> > > ora_dbw0_or
> > > 000 501 21361 1 9 0 35428 3092 semop S ? 5:07
> > > ora_lgwr_or
> > > 000 501 21363 1 9 0 35416 3516 semop S ? 1:16
> > > ora_ckpt_or
> > > 000 501 21365 1 9 0 35544 20284 semop S ? 0:09
> > > ora_smon_or
> > > 000 501 21367 1 9 0 35452 19220 semop S ? 0:04
> > > ora_reco_or
> > > 000 501 21370 1 9 0 4996 1692 do_sel S ? 1:49
> > > /u01/app/or
> > > 000 501 32120 1 9 0 35816 20528 tcp_da S ? 0:01
> > > oracleorcl
> > > 000 501 32242 1 9 0 35892 20680 tcp_da S ? 0:02
> > > oracleorcl
> > > 000 501 32246 1 9 0 35812 19504 tcp_da S ? 0:01
> > > oracleorcl
> > > 000 501 32248 1 9 0 35592 20324 tcp_da S ? 0:01
> > > oracleorcl
> > > 000 501 32250 1 9 0 35884 21108 tcp_da S ? 0:02
> > > oracleorcl
> > > 000 501 32252 1 9 0 35588 20088 tcp_da S ? 0:00
> > > oracleorcl
> > > 000 501 32257 1 9 0 35820 20408 tcp_da S ? 0:02
> > > oracleorcl
> > > 000 501 32264 1 9 0 35820 21004 tcp_da S ? 0:02
> > > oracleorcl
> > > 000 501 32279 1 9 0 35620 20552 tcp_da S ? 0:01
> > > oracleorcl
> > >
> > >
> > > ----//----
> > >
> > > Anyway help, hints, tips, what so ever is appreciated.
> > >
> > > Thanks.
> > >
> > > Friendly Regards
> > > /Tom
> > >
> >
> >
> >

If you've got timed_statistics = true, how about posting a

select *
from v$system_event
order by 4

for us.

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."