Re: SQL Tuning

Hi,

As for me, your merge-sort join with full scans is the problem. Are there all necessary indexes on joining columns?

Andrew Protasov

> The version of Oracle is 7.3.4.0.1
>
> Below is an access path for the statement in question. Also, could you tell
> me in this example the order in which Oracle compares columns in the where
> clause. Thanks a lot for your help.
>
>
> Optimiser mode: ANALYZED
> Optimiser mode: CHOOSE
> SELECT STATEMENT
> SORT ORDER BY
> NESTED LOOPS
> NESTED LOOPS OUTER
> NESTED LOOPS OUTER
> MERGE JOIN
> SORT JOIN
> FILTER
> MERGE JOIN OUTER
> SORT JOIN
> NESTED LOOPS
> MERGE JOIN
> SORT JOIN
> TABLE ACCESS FULL TABLE_MOD_LEVEL
> SORT JOIN
> TABLE ACCESS FULL TABLE_PART_NUM
> TABLE ACCESS BY ROWID TABLE_SITE_PART
> BITMAP CONVERSION TO ROWIDS
> BITMAP MINUS
> BITMAP INDEX SINGLE VALUE
> IND_SITE_PART2PART_INFO
> BITMAP INDEX SINGLE VALUE IND_LEVEL_TO_BIN
> SORT JOIN
> TABLE ACCESS FULL TABLE_PRODUCTBIN
> SORT JOIN
> TABLE ACCESS FULL TABLE_SITE
> INDEX UNIQUE SCAN VENDOR_OBJINDEX
> INDEX UNIQUE SCAN VENDOR_OBJINDEX
> INDEX UNIQUE SCAN ADDRESS_OBJINDEX
>
> Dave Wotton wrote in message <71rrag$hgj$1_at_dns.camcnty.gov.uk>...
> >>Roman Gelfand <rgelfand_at_masmid.com> wrote:
> >>I am trying to tune an sql statement by manipulating initialization
> >>parameters, indexes... etc. These SQL statements are vendor supplied and
> I
> >>am not at liberty to change them.
> >
> >Hi,
> >
> >could you post the explain plan?
> >
> >What version of Oracle are you running?. Which optimiser?
> >
> >I think you may be stuck if you are not at liberty to change the SQL.
> >Changing parameters and indexes aren't always sufficient to fix all
> >performance problems.
> >
> >If you're using the Cost based analyser, you may need to add hints to
> >your query ( does this constitute changing it? ). It may turn out that
> >the view definition needs to be changed in order to speed it up. Does this
> >constitute "changing the SQL?". If you're using the rule based optimiser
> >you'll almost certainly have to tweak the query or the view.
> >
> >Dave.
> >--
> >Remove the no-spam bit from my email address to reply.
>
>
>
>
Received on Thu Nov 05 1998 - 23:39:40 CST