tuning question...

I've got a query that I'm trying to tune on 8.1.6 and when I run a trace on it, the execution plan seems to be ok (no full table scans, all the proper indexes are being used), but the stats for the query show an extremely high number of consistent gets (all other stats are very low)... and of course the reason i'm trying to tune this in the first place is because it's slow.

I'm reading through the oracle documentation and it simply says that a consistent get is "The number of times a consistent read was requested for a block"

Now that makes sense in a theoretical sort of way, but I don't know what this means in terms of query performance and what I can or should do to lower the number of consistent gets especially when I see no problems in the explain plan.

The particular query in question is complicated and I know that one approach is to try to get the same answer by breaking it out into smaller queries somehow... but I would like to try to tune this first since that would probably be easier to fit into our current product architecture at the moment.

(The query involves 5 tables,1 of which is 500k rows, one table is outer joined, the query contains a decode in the select (not in the 'Where') and then it nests everything inside of an inline view and then performs a rownum contraint in the outer select to facilitate a TOP N report)

That's just some background on the type of query I'm trying to tune. My question here is simply to try to get a better understanding of the consistent get statistic and any tuning implications or guidelines that come from it. I'd appreciate it if anyone could clue me in.

Thanks,
Gavin

Sent via Deja.com
http://www.deja.com/ Received on Sat Jan 20 2001 - 23:48:21 CST