Top stuff JL.
"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
news:b16d7r$6ja$1$8302bc10_at_news.demon.co.uk...
>
> 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
>
>
Received on Wed Jan 29 2003 - 03:58:27 CST
