Re: Deletion slowness as day go on..

rebuild w/ partitions

On 23 May 2002 14:32:09 GMT, <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
>
>

.......
We use Oracle 8.1.7.3 on Solaris 2.7 boxes remove NSPAM to email Received on Thu May 23 2002 - 11:00:33 CDT