Re: Slow performance... urgent help required

From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Thu, 4 Feb 2010 15:07:01 -0600
Message-ID: <ad3aa4c91002041307n12b6cae7xf4d1921ab1017cc_at_mail.gmail.com>

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-l

Received on Thu Feb 04 2010 - 15:07:01 CST