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

> The sizes of each table are:
> EVENT: 15,690,395 rows
> DOCUMENT: 5,661,104 rows

Joining a table with 15.6 million rows to a table with 5.6 million rows can take a bit of time. You might want to look at a Materialized View as a last resort if you cannot get this query optimized further.

> Here is the TKProf output:
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.03 0.03 1 1 0
> 0
> Execute 1 0.00 0.00 0 0 0
> 0
> Fetch 62 0.57 27.17 3856 8166 0
> 907
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 64 0.60 27.20 3857 8167 0
> 907

The thing that jumps out at me is the big difference between the CPU time and the ELAPSED time. This means you spent a lot of time waiting for *something*. You will have to look at the wait events incurred when running this query to see what you are waiting for to happen.

> Misses in library cache during parse: 1
> Optimizer mode: FIRST_ROWS

Since this is a web application (ASP.Net) then you might want to consider the ALL_ROWS optimizer mode instead. Also, your query is doing a Nested Loops join. Have you tried the other join methods to see if they return the data faster? They might or might not, but its worth a look.

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