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

marothwell_at_yahoo.com (Michael R) wrote in message news:<b2b3ca26.0407271016.255c2f9c_at_posting.google.com>...
> I have a query:
>
> insert /*+ APPEND */ into spi_data NOLOGGING
> SELECT a.product_number, c.part_number,
> NVL( l.parent_spi_product, l.spi_product ) AS spi_product,
> k.category_name,
> j.keyword_name, h.part_type_name,
> SUBSTR( DECODE( b.description_flag,
> 'Y', DECODE( b.description_number,
> NULL, c.description,
> c.description||' -
> '||i.description),
> i.description ), 1, 255 ) AS description,
> b.sort_flag, c.intl_flag,
> c.restricted_sales_flag, b.crew_flag,
> b.common_flag, c.nls_flag,
> c.exchange_flag, c.accessory_flag,
> c.consumable_flag, c.service_flag,
> c.region_flag, c.part_photo_flag
> FROM product a, structure b, part c, product_spi_product d,
> part_type h,
> description i, keyword j, category_multi_view k, spi_product l
> WHERE a.product_number = b.product_number
> AND b.part_number = c.part_number
> AND a.product_number = d.product_number
> AND d.spi_product = l.spi_product
> AND c.keyword = j.keyword_id
> AND c.part_type = h.part_type_id
> AND b.description_number = i.description_number (+)
> AND b.part_number = i.part_number (+)
> AND a.release_flag = 'Y'
> AND b.release_flag = 'Y'
> AND c.setup_flag = 'Y'
> AND c.release_flag = 'Y'
> AND b.product_number = k.product_number
> AND b.part_number = k.part_number;
>
> The tables are, or seems to be, properly indexed.
> Some of these tables are fairly large.
> Same indexes on each box.
>
> 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.

The speed of the box may not be the most important factor here. You also have to consider the disk distribution and IO capacity.

If differences in data quantity and distribution are not responsible for the difference in plan and performance then you can add hints to the select to try to hint the same plan.

But first you should update the statistics using as large a sample size as practical and then relook at the plan.

If updating the stats does not have a positive effect and the data quantities are very nearly the same then I would try to apply hints to get the same plans.

One thing to check is the amount of sort and hash join resource provided to each system. With a plan that uses as many hash joins as yours the memory provided for hash could be a factor as could the concurrent use of temp space if the hash data has to be written to disk.

Good Luck
-- Mark D Powell -- Received on Tue Jul 27 2004 - 18:37:08 CDT