Re: tuning question...

A 'consistent get' is a block read that requires a block to have an SCN which is not higher than the SCN of the query.

You can get some further statistics about consistent gets (in later versions of Oracle) from rows in v$sysstat which report:

    consistent gets with no work done
    consistent gets requiring rollback to be applied     consistent gets requiring block cleanouts to be applied     consistent gets requiring cleanout and rollback.

One reason why consistent gets goes very high (compared to your calculation of reasonable behaviour) is that the query is using an index that is not suitable. Another reason is that a large amount of rollback is being applied repeatedly for some reason. (Rollback blocks are read in consistent mode to be applied to cloned blocks to roll them back to the correct SCN, under certain circumstances, the rollback can be applied repeatedly within the same query to the same blocks).

As a VERY rough guideline, allow about 3 or 4 consistent reads gets for every row accessed during the course of the query, and see if the result tallies with expectation. NOTE this is a VERY rough guideline that is in the right ballpark for simple indexed nested loop joins.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



gdas_at_my-deja.com wrote in message <94dt74$r8i$1_at_nnrp1.deja.com>...


>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/