Deletion slowness as day go on..

Hi Everyone,

   This is a TUNING question about slowness in deletion. This is NOT my db, I do not create or touch it at all. I have a friend work in this startup and they hope I could help them.

This DB is 118 GB, 8 K block, 9 Meg shared_pool_size, but the db_block_buffer is very big, can't remember, but the  SGA become almost 4 Gig in size.
- 420 machine 4 CPU.

• OLTP db, 24x7 running Oracle 8.0.6 standard edition
• this one table has 800 + extents , and each extent's size is 100,000 very small.

When the DB is 75 Gig one week ago, the deletion of records per sec is 800 records per sec and
now when the DB grow 118 Gig, the deletion of records is 25 record per sec. The problem is the insertion rate is higher than the deletion rate, so it is RUNNING out of space soon. Also the hotbackup can't run anymore , since it affect the db's performance (this part, I forget why hotbackup can't run, need to find out more tomorrow, since I could go to their office tomorrow to work on it.)

I am planning to do the following:

1)
analyze index <ix> validate structure; This is not same as analyze index <ix> esimate statistics sample x percent.

The company is afraid of performance downgrade and don't allow this.

So I might have to do it in the hot backup db

2) I will check the child table 's foreign key to see whether there is index on the child's constraints

3) I might check the PCT_free or PCT_used.. this might have an effect on it too.

This is pretty urgent,
why other good ideas to check??

Thanks in advance,

Dominica Leung Received on Thu May 23 2002 - 09:32:09 CDT