Re: Slow performance... urgent help required
Date: Thu, 4 Feb 2010 19:29:25 -0200
Message-ID: <172762181002041329u1db484d0r1a7edde11f891b0d_at_mail.gmail.com>
Just a hunch, what is optimizer_features_enable set to? have you changed anything lately? Is the optimizer set to choose or rules? This looks like an optimizer issue. Have you considered upgrading to 9.2.0.8.0 and opening a SR with Oracle?
Alan.-
On Thu, Feb 4, 2010 at 7:07 PM, Andrew Kerber <andrew.kerber_at_gmail.com>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_at_gmail.com> 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.'
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Feb 04 2010 - 15:29:25 CST