Re: Horrible Query Performance, Simple Query -- Performance Tuning Help Needed

> I ran the v$session_wait and found that the session is waiting for
> db_file_sequential_read, but the seconds_in_wait is always 0.

My mistake...I should have said V$SESSION_EVENT. I always get those two confused when I'm thinking off the top of my head and I seem to query the one of the two that I do not need. Then I say to myself, "Doh"!

V$SESSION_WAIT will show you what the session is waiting on at that moment. V$SESSION_EVENT will show you all the waits for that session's lifetime. I'd query V$SESSION_EVENT as well because you may always be catching when the session is in the 'db file sequential read' event and may be missing other events.

When you do query V$SESSION_WAIT, also look at P1TEXT, P1, P2TEXT and P2 as well. If you see the 'db file sequential read' event, the P1 and P2 values will tell you which file and which block you are reading. The block in that file (determined by DBA_EXTENTS) should belong to one of the tables in your query, or their indexes.

I'd also take good note of the advice provided by Jonathan Lewis in this thread. He always posts good stuff.

Cheers,
Brian

-- 
===================================================================

Brian Peasland
oracle_dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown