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

From: Michael R <marothwell_at_yahoo.com>
Date: 27 Jul 2004 11:16:28 -0700
Message-ID: <b2b3ca26.0407271016.255c2f9c@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. Received on Tue Jul 27 2004 - 13:16:28 CDT