Strange cyclic lags while Oracle delete from database on HPUX 11.0

[GloomDaemon]

Hi!
We have L3000 server with 2gb RAM and Swap about 8gb. There is Oracle online database installed.

Last time, possibly becouse our database size constantly raise. we meet with a strange problem. It was detected in time while Oracle attempt to delete strings from database. Usially we see that after deleting 5000-10000 strings in 10-20 seconds after we have lag about 20-60 seconds after work fine same interval of time after lag again. this lag affect all oracle applications at all. Lag time depend from size of table. but lag time usially is almoust constant and number of strings deleted by oracle in one step before next lag almoust constant to.

I check all disk sybsistems for problems but not detect any problems at all. problem present on different vg and types of storages where i move test database. and no depend from server load. We had inaff RAM avaiable and not in swap when these events begin.

I need advice to search this problem source. plz.

P.S. Sorry about my english plz.

[Jean-Luc Oudart]

Hi

could you run statspack snapshot before and after your deletion job, then run a stats pack report ?
If you have glance you could monitor the system at that time.

Also, which oracle version do you use ?

Regards
Jean-Luc

[GloomDaemon]

oracle version 8.1.7.4

I have no glance. but had sar -d , top, swapinfo iostat statistic.

[Jean-Luc Oudart]

OK

could you run the statspack report ?
Also, could you describe the storage / storage layout for the database ?
how many databases (instances) on the server ?

How are the rows deleted : is this one transaction / multiple transactions ?
how often do you commit ?

Does anything else run on same server ? (apart from oracle)

Regards
Jean-Luc

[Peter Godron]

Hi,
I assume you have frequent commit points, as you say that 10000 strings are deleted within 20 sec, then you have the lag of 60 sec, before the next lot is deleted.
Are you sure the commit of the 10000 deletes is not just taking 20 sec and the search for the next 10000 records takes 60 secs?
Are you archiveing and are your rollback segements/redo logs big enough?
Regards

[GloomDaemon]

>could you run the statspack report ?
yep. today evening we will continue test and i will make statspack to you before and in situation.

>Also, could you describe the storage / >storage layout for the database ?
We had 2 node cluster. All data for cluster packages stored in fiberchannel HP surestore disk system 2405. We had one unit in each server. linked between servers and together. so i have 2 links to each disk in storage field from each server. Package had own VG
5 logical volumes. 4had 2 mirrors and one 3.
disks5,6,7 for data 8,9 for online logs
Approximate data size 35-40gb
Root@mmc2://#>vgdisplay -v vgbo | pg
--- Volume groups ---
VG Name /dev/vgbo
VG Write Access read/write
VG Status available, exclusive
Max LV 255
Cur LV 5
Open LV 5
Max PV 16
Cur PV 5
Act PV 5
Max PE per PV 30000
VGDA 10
PE Size (Mbytes) 4
Total PE 52222
Alloc PE 52214
Free PE 8
Total PVG 0
Total Spare PVs 0
Total Spare PVs in use 0

--- Logical volumes ---
LV Name /dev/vgbo/disk5
LV Status available/syncd
LV Size (Mbytes) 23320
Current LE 5830
Allocated PE 11660
Used PV 2

LV Name /dev/vgbo/disk6
LV Status available/syncd
LV Size (Mbytes) 23320
Current LE 5830
Allocated PE 17490
Used PV 3

LV Name /dev/vgbo/disk7
LV Status available/syncd
LV Size (Mbytes) 23320
Current LE 5830
Allocated PE 11660
Used PV 2

LV Name /dev/vgbo/disk8
LV Status available/syncd
LV Size (Mbytes) 11404
Current LE 2851
Allocated PE 5702
Used PV 2

LV Name /dev/vgbo/disk9
LV Status available/syncd
LV Size (Mbytes) 11404
Current LE 2851
Allocated PE 5702
Used PV 2


--- Physical volumes ---
PV Name /dev/dsk/c6t2d0
PV Name /dev/dsk/c7t2d0 Alternate Link
PV Status available
Total PE 8681
Free PE 0
Autoswitch On

PV Name /dev/dsk/c8t1d0
PV Name /dev/dsk/c9t1d0 Alternate Link
PV Status available
Total PE 8681
Free PE 0
Autoswitch On

PV Name /dev/dsk/c8t2d0
PV Name /dev/dsk/c9t2d0 Alternate Link
PV Status available
Total PE 8681
Free PE 0
Autoswitch On

PV Name /dev/dsk/c7t1d0
PV Name /dev/dsk/c6t1d0 Alternate Link
PV Status available
Total PE 8681
Free PE 0
Autoswitch On

PV Name /dev/dsk/c7t3d0
PV Name /dev/dsk/c6t3d0 Alternate Link
PV Status available
Total PE 17498
Free PE 8
Autoswitch On

how many databases (instances) on the server ?
on this server working one package. and he had 2 database open at this time

>How are the rows deleted : is this one ?>transaction / multiple transactions ?
>how often do you commit ?
We deleting by rows. one row delete/commit
there the small statistic how many rows was deleted in second. on our test base of 500000 rows. maked last day
11-JAN-2005 21:38:25 1
11-JAN-2005 21:38:29 369
11-JAN-2005 21:38:30 650
11-JAN-2005 21:38:31 513
11-JAN-2005 21:38:32 244
11-JAN-2005 21:38:33 526
11-JAN-2005 21:38:34 212
11-JAN-2005 21:38:42 355
11-JAN-2005 21:38:43 396
11-JAN-2005 21:38:44 353
11-JAN-2005 21:38:45 348
11-JAN-2005 21:38:46 263
11-JAN-2005 21:38:47 287
11-JAN-2005 21:38:48 330
11-JAN-2005 21:38:56 252
11-JAN-2005 21:38:57 339
11-JAN-2005 21:38:58 449
11-JAN-2005 21:38:59 506
11-JAN-2005 21:39:00 41
11-JAN-2005 21:39:01 283
11-JAN-2005 21:39:02 238
11-JAN-2005 21:39:10 381
11-JAN-2005 21:39:11 567
11-JAN-2005 21:39:12 597
11-JAN-2005 21:39:13 121
11-JAN-2005 21:39:14 219
11-JAN-2005 21:39:15 377
11-JAN-2005 21:39:23 160
11-JAN-2005 21:39:24 616
11-JAN-2005 21:39:25 597
11-JAN-2005 21:39:26 186
11-JAN-2005 21:39:27 13
11-JAN-2005 21:39:28 227
11-JAN-2005 21:39:29 304
11-JAN-2005 21:39:37 319
11-JAN-2005 21:39:38 540
11-JAN-2005 21:39:39 582
11-JAN-2005 21:39:40 198
11-JAN-2005 21:39:41 110
11-JAN-2005 21:39:42 508
11-JAN-2005 21:39:50 232
11-JAN-2005 21:39:51 537
11-JAN-2005 21:39:52 543
11-JAN-2005 21:39:53 291
11-JAN-2005 21:39:55 367
11-JAN-2005 21:39:56 164
11-JAN-2005 21:40:23 384
11-JAN-2005 21:40:24 440
11-JAN-2005 21:40:25 410
11-JAN-2005 21:40:26 484
11-JAN-2005 21:40:27 4
11-JAN-2005 21:40:30 199
11-JAN-2005 21:40:31 371
11-JAN-2005 21:40:52 11
11-JAN-2005 21:40:53 344
11-JAN-2005 21:40:54 362
11-JAN-2005 21:40:55 399
11-JAN-2005 21:40:56 465
11-JAN-2005 21:40:57 5
11-JAN-2005 21:41:00 143
11-JAN-2005 21:41:01 353
11-JAN-2005 21:41:21 286
11-JAN-2005 21:41:22 497
11-JAN-2005 21:41:23 504
11-JAN-2005 21:41:24 366
11-JAN-2005 21:41:25 7
11-JAN-2005 21:41:27 155
11-JAN-2005 21:41:28 423
11-JAN-2005 21:41:44 369
11-JAN-2005 21:41:45 442
11-JAN-2005 21:41:46 491
11-JAN-2005 21:41:47 293
11-JAN-2005 21:41:53 212
11-JAN-2005 21:41:54 305
11-JAN-2005 21:42:04 72
11-JAN-2005 21:42:05 595
11-JAN-2005 21:42:06 552
11-JAN-2005 21:42:07 450
11-JAN-2005 21:42:08 6
11-JAN-2005 21:42:09 367
11-JAN-2005 21:42:10 213
11-JAN-2005 21:42:18 394
11-JAN-2005 21:42:19 537
11-JAN-2005 21:42:20 561
11-JAN-2005 21:42:21 93
11-JAN-2005 21:42:22 8
11-JAN-2005 21:42:23 308
11-JAN-2005 21:42:24 203
11-JAN-2005 21:42:32 468
11-JAN-2005 21:42:33 369
11-JAN-2005 21:42:34 408


Does anything else run on same server ? (apart from oracle)
no at all exept system background and cron servises

[Peter Godron]

Hi,
based on your latest update I can't see the problem. Your log of records deleted starts at 21:38:25 and you seem to delete records at a steady rate. I can't see your lag of 20-60 seconds. Can you please help pinpoint?!

[Thierry Poels_1]

hi,

Is it a business rule to commit after each delete? Do you really need to commit after each deleted row? Because this is the bottleneck.

The most performant would probably be one commit after all deletes, but this will only work if your rollback/undo can handle it. An other option is to group the deletes: commit after 1000-10000 deletes or so.

regards,
Thierry.

[Hein van den Heuvel]

Please consider performing mutliple
deletes/commit as indicated.

I suspect that the 'lag' is caused by logfile switched which in turn cause a checkpoint.

How frequently do you switch logfiles? What it should be depends on your recovery requirements, but anything more frequent than 5 minutes will often be excessive overhead.
- Check v$log/v$logfile for indicators.
- tail (-f) alert.log
(may need SQL> alter system set checkpoints_to_alert = true; (check spelling))
- How many mb/sec to redo disk. How big it the redolog file (too often I see < 50 mb where > 500 mb is desirable for the app).
- also check checkpoint params (time & blocks) in init.ora.

hth,
Hein.