Re: Oracle8 panic! - lousy performance/high load

It may or may not be the disk. Consider that on 1 disk you have redo logs which are heavy in sequential writes and archive logs (I hope you have archiving turned on) which during a log switch (see alert.log for when these have occurred) the old redo log is heavy sequential read, the archive log is heavy sequential write (basically a copy) and the new redo which is a sequential write. In the midst of all that you are accessing the data and indexes which is mainly random IO - probably more reads than writes. It is quite possible to have disk Queuing in such a situation. At the very last separate the redo and achive logs on a separate disk from the data. The redo disk does not have to be large.

As for the bind variable question, bind variables or host variables are place holders in DML. By using bind variables you can reuse the same query (with different values) again and again by just rebinding and reexecuting the query. The advantage is that the database will use the same execution plan and not have to reparse the query (heavy CPU) and revalidate the whole query (what columns, what tables, does the user have rights to those etc.), redetermine the execution plan etc.. Instead that has been done once and just new data is sent over (or for insert or update, the command is just done - no reparsing necessary) . It dramatically lowers CPU usage and speeds up subsequent queries. Also if you can avoid closing the cursor and just reusing it with new bind values then you will get a further enhancement (lower soft parsing).

Also are you connecting each time or do you keep the connection around?

Since you are using Perl I don't know if bind variables (or host variables) can be used in Perl. Perl has been around long enough and seems to be very up-to-date and so it is very possible that it is.

Jim

"Tom Karlsson - Remove NOSPAM" <tk_at_babylon5.dkNOSPAM> wrote in message news:Pine.LNX.4.21.0111131904310.24342-100000_at_sith.ammulti.dk...
> 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 - 13:07:55 CST