Re: Slow performance... urgent help required
Date: Thu, 4 Feb 2010 17:57:59 -0500
Message-ID: <b86ffce61002041457n3c8dd958v6f19f87d784276a6_at_mail.gmail.com>
These are just a snapshot of a moment in time. Enable a level 12 10046 Trace and run some of your test SQL. Once you get that, throw it into tkprof or use a free tool like http://www.oracledba.ru/orasrp/ or this " http://antognini.ch/downloads/tvdxtat_40beta9.zip"
It will tell you exactly what is slowing you down and chances are what is slowing you down just might be slowing down everyone else.
On Thu, Feb 4, 2010 at 5:31 PM, Saad Khan <saad4u_at_gmail.com> wrote:
> 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_at_gmail.com> 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_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
>>
>
>
-- Thomas Roach 813-404-6066 troach_at_gmail.com -- http://www.freelists.org/webpage/oracle-lReceived on Thu Feb 04 2010 - 16:57:59 CST