Re: Slow performance... urgent help required

I reviewed alert logs and see the following message while a insert job is
running:

*ARC0: Unable to archive log 1 thread 1 sequence 380227
      Log actively being archived by another process*


On Thu, Feb 4, 2010 at 4:07 PM, Andrew Kerber <andrew.kerber@xxxxxxxxx>wrote:

> It is unlikely to be frragmentation, but the quickest way to defrag a table
> is to run an alter table move, then rebuild the indexes.  Did you check your
> alert log for any error messages?
>
>
> On Thu, Feb 4, 2010 at 2:58 PM, Saad Khan <saad4u@xxxxxxxxx> wrote:
>
>> Hi Gurus,
>>
>> I need urgent help.
>>
>> This is oracle 9.2.0.7 on AIX 5.3
>>
>> The application support persons and business had been complaining of the
>> extremely slow performance for last three days. They could not run anything.
>> I decided to run the index rebuild and magically they started cheering that
>> things are coming up.
>> But since today, things went down again. I checked the CPU at server which
>> was as high as 99%. I asked them to stop the app. They did that and the CPU
>> started going down where it should be.
>> However, problem reappeared in some time again when the CPU was around
>> 70-80% while only few queries were running.
>> Just to check, I ran the following queries at the tables having as much as
>> 34 million rows:
>>
>> select * from <tablename> where rownum=1;
>>
>> and it took around 2 min to come up with the result. WTH! It should bring
>> the result in less than a sec just like it does in all other similar
>> environments. Its execution plan shows  the cost of 51000. When I got its
>> trace, the trace file clearly shows that even though it is only fetching
>> a single row, it's essentially performing a full table scan (456,000 disk
>> operations).  The cost of this query should be 1 or 2 at most.
>>
>>  The latest statspack report shows the following database ratios:
>>
>> Instance Efficiency Percentages (Target 100%)
>> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>>             Buffer Nowait %:  100.00       Redo NoWait %:    100.00
>>             Buffer  Hit   %:   99.36    In-memory Sort %:    100.00
>>             Library Hit   %:   97.99        Soft Parse %:     92.72
>>          Execute to Parse %:   37.54         Latch Hit %:     99.94
>> Parse CPU to Parse Elapsd %:   10.51     % Non-Parse CPU:     89.50
>>
>>  Shared Pool Statistics        Begin   End
>>                                ------  ------
>>              Memory Usage %:   94.97   94.74
>>     % SQL with executions>1:    3.79    3.95
>>   % Memory for SQL w/exec>1:   13.20   13.73
>>
>> Top 5 Timed Events
>> ~~~~~~~~~~~~~~~~~~                                                     %
>> Total
>> Event                                               Waits    Time (s) Ela
>> Time
>> -------------------------------------------- ------------ -----------
>> --------
>> latch free                                         80,839       1,286
>> 31.82
>> db file sequential read                           402,316         906
>> 22.43
>> enqueue                                               286         835
>> 20.66
>> db file scattered read                            267,029         358
>> 8.86
>> CPU time                                                          323
>> 7.98
>>           -------------------------------------------------------------
>>
>> Something is wrong with those tables. Either they are too fragmented or I
>> dont know. Can a database recycle help? What can be the best ways the get
>> those tables defragmented in case these are. I need help urgently please.
>>
>> Thanks in advance.
>>
>> Sid
>>
>>
>>
>
>
> --
> Andrew W. Kerber
>
> 'If at first you dont succeed, dont take up skydiving.'
>

Other related posts: