Re: Slow performance... urgent help required

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



On Thu, Feb 4, 2010 at 4:34 PM, Thomas Roach <troach@xxxxxxxxx> wrote:

> 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@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
>>
>>
>>
>
>
> --
> Thomas Roach
> 813-404-6066
> troach@xxxxxxxxx
>

Other related posts: