Re: tuning question...

First, be sure to define your tuning goals for the query, and that it is worthwhile to tune it...

How long should it take to return a result set ? How often will this query be run ?
Is this query impacting database performance for others ?

Be realistic about how much real "work" the query is going to have to perform... for example, scanning a million rows is not going to outperform retrieval of a single row by ROWID.

In general, the approach I take to tuning a complex query is by working from the innermost query outward, breaking the problem into smaller problems, tuning each SELECT.

Make sure that you have collected statistics on all of the tables and related indexes, and compare the explain plans of the cost based optimizer vs. rule. If statistics do not exist, then each execution of the query (under CBO) will cause statistics to be collected, which can be a real drain on performance.)

Otherwise, in general, look for ways to rewrite the SQL:

Check for unnecessary datatype conversions, either explicit or implicit, especially those that may be preventing use of an appropriate index.

Avoid unnecessary sorts, especially on large result sets. For example, i have a complex query that performs 7 sorts, but thankfully, these are all on small (<100 rows) result sets.

Look for ways to eliminate as many rows as possible from the result set of each part of the query. For example, places that it might be possible to add criteria to filter out rows in a query that are going to be eliminated later, or places where you can move a "group by" deeper into the query.

Look for places where a join can be deferred until later in the query plan. For example, rather than joining a lookup table to a huge "detail" result set, and then aggregating sums and counts... instead, aggregate the detail first, and then join to the lookup table.

Consider applying any "group by" conditions elsewhere in the query, as these imply sorts.

Take a look at the possibility of replacing any " IN (subquery)" constructs with correlated subqueries, or "EXISTS" or use a subquery as part of the FROM clause and implement a join.

Try adding hints to nudge the optimizer into using different join paths (which can significantly impact performance (i.e. hash vs. sort vs. nested loops), and access paths.

Lastly, consider reorganizing tables, rebuilding indexes, and/or altering or creating new indexes (be sure to collect statistics !)

Sometimes all it takes is a little tweak to get it to scream, other times it takes a major rewrite, sometimes its not worth the bother.

HTH <gdas_at_my-deja.com> wrote in message news: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/
>
Received on Sun Jan 21 2001 - 15:51:34 CST