Re: acceptable buffer reads per execution..

For non-aggregate queries I've always
use the value:

    number of rows returned *
    number of tables in query *
    4

as a guideline, on the basis that

(a) we query because we want data, so
we don't do a lot of work to eliminate data so we visit each table to add value to the base data. (this is of course questionable in many ways - but it gives us the multiplier of 'number of rows')

(b) each table is visited once per returned row to contribute to that row or confirm the validity of that row. (again questionable but it gives us the multiplier 'number of tables')

(c) for good use of typical sized tables, we visit a root block, branch block and
leaf block of and index before visiting
the table block that contains the row
we want. (this is of course questionable on several counts, most significantly
that we rarely spend all our time on
getting one row through a unique index - but it gives us the multiplier 4).

Obviously your knowledge of the application will help you decide to allow for larger values when you know (for example) that in a key table you visit 10 table rows via the index but discard 9 of them because of some
value in a non-index column of the table.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March
____USA_(FL)_May


Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


NetComrade wrote in message
<3e36acfe.3607044285_at_nyc.news.speakeasy.net>...


>is there are a 'rule of thumb' for the maximum buffer reads per

>execution?

>

>I have a number of queries and procedures that range from 200 to 500

>buffer reads per execution and execute 1000's to 100000's times per

>day. Some of the queries just require a lot of data to execute, but I

>was curious if it was worth starting to look into 'materialized

views'


>and 'function based indexes' to try to minimize CPU usage by those

>queries.

>

>Thanx.

>.......

>We use Oracle 8.1.7.3 on Solaris 2.7 boxes

>remove NSPAM to email