Re: Tuning query

Niall Litchfield wrote:

> I have two (very) similar queries which execute in radically different
> timescales.

<snipped>

The only difference I noticed from the EXPLAIN PLANs are the number of HASH JOINs vs. NL JOINs.

Both can be problem - depending on the amount of data to be joined and the available index paths.

No easy answer to this one Niall. I would try the following.

Get an idea what is the performance problem by looking at wait stats and wait events for the session. Is it doing a lot of db reads? If so, what is it reading? You can use the P1 and P2 columns from v$session_wait to find the object name and onwer of the segment being read. (just shout if you want the SQL).

I would guess that you will find it is hitting either a table or index pretty hard with reads.

Then I would look at the explain plan and try to figure out why the CBO wants to use that index. Index range scans are sometimesa problem when dealing with a VLT with massive indexes.

I often break these large queries up into smaller units. Start with a single join. Explain plan. Check. Add the next join. Explain plan. Check. And so on.

When doing warehouse type queries where I have to hit a large fact table, I usually try to use an index to limit what I get from the fact table (partition index is ideal). Then I hit the dimensions via joins. I often opt for an /*+ ORDERED USE_NL() ... */ approach - using NL joins to the dims.

But I have not really find something that works all the time.. Something like a /*+ DO_MACIG_SUB_SECOND_RESPONSE */ :-)

Usually a case of breaking the problem into smaller pieces and tackling it that way. And making a lot of notes trying to understand the CBO's reasoning. :-)

--
Billy