Re: Oracle8 panic! - lousy performance/high load

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
> >
>
>
>
Received on Tue Nov 13 2001 - 12:18:35 CST