RE: Slow performance... urgent help required

  • From: "Kenneth Naim" <kennaim@xxxxxxxxx>
  • To: "'Kenneth Naim'" <kennaim@xxxxxxxxx>, <saad4u@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 4 Feb 2010 16:35:11 -0500

I take my previous comment back, I should have read it more carefully. That
query should be doing a full scan with a stopkey of 1 row.  The waits on
latches and enqueues are consuming total over 50%, I'd be looking to see
what is causing those. Were the indexes rebuild online? Is it a pl/sql heavy
application? Can you see which queries are waiting on locks/latches?

 

ken

 

 

From: Kenneth Naim [mailto:kennaim@xxxxxxxxx] 
Sent: Thursday, February 04, 2010 4:22 PM
To: 'saad4u@xxxxxxxxx'; 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: Slow performance... urgent help required

 

If you are seeing explain plans that contain full table scans where a single
index lookup should be used, I'd start looking at the table and index stats;
checking to see when they were last run, how up to date they are, as far as
number of rows, unique values etc.

 

Ken

 

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Saad Khan
Sent: Thursday, February 04, 2010 3:58 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Slow performance... urgent help required

 

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: