Re: Slow performance... urgent help required
Date: Thu, 4 Feb 2010 17:31:12 -0500
Message-ID: <76b3d4e31002041431v66043cetfd1e46d76d9b726e_at_mail.gmail.com>
The snapshot was taken in 5 minutes.
if its waiting for the latches, how can I resolve that?
Also one thing more, I saw "dbfile scattered reads" when I ran that query while the session was in "waiting" mode.
USERNAME
EVENT
SID
P1TEXT
P1
P2TEXT
P2 WAIT_TIME SECONDS_IN_WAITSTATE
- ---------------------- ----------------------
NIKU db file scattered read 204 file# 3 block# 2190 0 0 WAITING NIKU db file scattered read 325 file# 9 block# 38785 -1 0 WAITED KNOWN TIME NIKU db file sequential read 313 file# 12 block# 420154 -1 0 WAITED KNOWN TIME NIKU db file sequential read 322 file# 22 block# 598136 0 0 WAITING NIKU log file sync 24 buffer# 2727 0 0 0 WAITING
> How far apart were your snapshots? 2 minutes? 10 minutes? an hour?
>
> I see latch free at the top and you also mention High CPU.
>
> Maybe you are waiting on a latch on the library cache in order to parse
> some SQL? I don't know as you haven't provided enough information. (it's
> just a shot in the dark).
>
> On Thu, Feb 4, 2010 at 3: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
>>
>>
>>
>
>
> --
> Thomas Roach
> 813-404-6066
> troach_at_gmail.com
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Feb 04 2010 - 16:31:12 CST