Re: Deletion slowness as day go on..

dominica_at_secondhat.com wrote:

> 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
>
>
>

I would try another aproch. First check the views v$system_event, v$session_event for the deleting sessions (at least) and then the v$session_wait, this way get a 3 mile look, a 100 yard look and the microscope look at the problem. Check v$sql/v$sql_area for executions/loads.

My gues is that you have severe buffer busy waits, hash_chain_latches, hash_buckets, freelist contention and similar. The db-writer isnt working fast enought. Your memory access has contention. And to many partly filled block wandering in and out of the buffer and freelists. Rewrite sql to use binds.

If that is close, set up at least 4 db-writers, db_writer * cpu for db_block_lru_latches, _db_block_hash_latches + _db_block_hash_buckets to a number so that there is between 8 and 32 blocks in each chain/bucket. Reduse the db_block_buffer 50%. Increase shared pool to 50-150M.

Alter table parameters, freelists 4?, storage bigger, pctused 0... and then target the table for a new tablespace with decent settings.

rgds

/svend jensen Received on Thu May 23 2002 - 13:10:22 CDT