Re: How to get 8i to perform as well as 9i

"Michael R" <marothwell_at_yahoo.com> wrote in message news:b2b3ca26.0407271016.255c2f9c_at_posting.google.com...
> I have a query:
>

--snip--
> In 9i this will finish in about 20 minutes.
> In 8i (on a much faster box) it finishes in about 5 hours.
>
> Here are the query plans for each box:
>
> 8i:
>
> SELECT STATEMENT
> HASH JOIN
> INDEX (FAST FULL SCAN), CATEGORY_IDX (SPI)
> HASH JOIN
> INDEX (FULL SCAN), PART_TYPE_IDX (SPI)
> HASH JOIN
> INDEX (FAST FULL SCAN), KEYWORD_IDX (SPI)
> HASH JOIN (OUTER)
> HASH JOIN
> INDEX (FAST FULL SCAN), SPI_PRODUCT_PARENT_IDX (SPI)
> HASH JOIN
> INDEX (FAST FULL SCAN), PRODUCT_SPI_PRODUCT_PK (SPI)
> HASH JOIN
> TABLE ACCESS (FULL), PART (SPI)
> MERGE JOIN
> SORT (JOIN)
> HASH JOIN
> INDEX (FAST FULL SCAN), PRODUCT_RELEASE_IDX
> (SPI)
> TABLE ACCESS (FULL), STRUCTURE (SPI)
> SORT (JOIN)
> INDEX (FAST FULL SCAN), STRUCTURE_CATEGORY_PK
> (SPI)
> INDEX (FAST FULL SCAN), DESCRIPTION_IDX (SPI)
>
>
> 9i:
>
> SELECT STATEMENT
> HASH JOIN
> INDEX (FAST FULL SCAN), SPI_PRODUCT_PARENT_IDX (SPI)
> HASH JOIN
> INDEX (FAST FULL SCAN), PRODUCT_SPI_PRODUCT_PK (SPI)
> HASH JOIN
> INDEX (FAST FULL SCAN), CATEGORY_IDX (SPI)
> HASH JOIN
> INDEX (FAST FULL SCAN), KEYWORD_IDX (SPI)
> HASH JOIN (OUTER)
> HASH JOIN
> HASH JOIN
> TABLE ACCESS (FULL), PRODUCT (SPI)
> HASH JOIN
> HASH JOIN
> INDEX (FULL SCAN), PART_TYPE_IDX (SPI)
> TABLE ACCESS (FULL), PART (SPI)
> TABLE ACCESS (FULL), STRUCTURE (SPI)
> INDEX (FAST FULL SCAN), STRUCTURE_CATEGORY_PK (SPI)
> INDEX (FAST FULL SCAN), DESCRIPTION_IDX (SPI)
>
> Is there anything I can do to get the 8i to use the same query plan as
> 9i? Or anything I can do to speed this up. We will be migrating to
> 9i soon, but in the mean time would like to get this query performing
> a little better. I've tried a few hints, but nothing has helped.
>
> Thank you.

You can try hinting the 8i query to make it behave like the 9i query.

ordered use_hash and index_ffs hints come to mind.

Anurag Received on Tue Jul 27 2004 - 15:44:11 CDT