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 >