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

> I'm not sure how to make the optimizer use another join type besides
> Nested Loops. Do you have any examples of what I might be able to do?
> I have tried taking the joins out and doing things like:

In order to use a different join method than the Optimizer is choosing, you'll want to employ a hint in your query. Check out Chapters 13 and 16 of the following doc:

http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14211/toc.htm

> Isn't the elapsed time so big because the database was waiting for the
> data to be brought back from disk? What kind of test could I do to
> determine what is the bottleneck?

Are you sure? There could be other I/O at play here too. For instance, you could be waiting for a buffer to clear before the data can be read from disk. You'll have to look at the wait events for your session. So try the following:

1. Sign on to SQL*Plus as your user
2. Issue the long running query
3. In another window, query V$SESSION to determine the SID of the session running the long query.
4. Once you know the SID, query V$SESSION_WAIT for that SID to see where the session spent most of its time waiting.

The wait events can lead you to something that may not be obvious at first.

HTH,
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