Re: Slow performance... urgent help required
- From: kyle Hailey <kylelf@xxxxxxxxx>
- To: saad4u@xxxxxxxxx
- Date: Thu, 4 Feb 2010 22:21:38 -0800
could you run DB Optimizer on the system and send me the saved output file?
I'll take a look at it. The data is similar to ash on Oracle 10 and 11.
https://downloads.embarcadero.com/free/db_optimizer
if you are curious how it works check out:
http://sites.google.com/site/embtdbo/wait-event-documentation
where I show examples under
http://sites.google.com/site/embtdbo/wait-event-documentation/database-tuning
as well as explanation of many of the major wait events and latch events.
For 9i, as well as 10 and 11 (and 8), it will translate the problem latches
in the intuitive easy to understand UI.(there all still "latch free" on 10
and 11 though the major latches have been translated into their own wait
events)
explanation of ASH
http://sites.google.com/site/embtdbo/wait-event-documentation/ash---active-session-history
How to collect ASH for free yourself with S-ASH
http://sites.google.com/site/embtdbo/wait-event-documentation/sash-1
or
http://ashmasters.com/ash-simulation/
Thanks
Kyle Hailey
http://db-optimizer.blogspot.com/
On Thu, Feb 4, 2010 at 12: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
>
>
>
Other related posts:
