Re: SQL Performance Problem

Hi Mark,

Thanks for the reply. This is version 9.2.0.6. I was also experimenting with the stats gathering. I finally by trial and error came up with this.

dbms_stats.gather_table_stats(

    ownname=>USER,
    tabname=>'NC_REFERENCES',
    method_opt=>'FOR COLUMNS SIZE 254 object_id, attr_id',     cascade=>TRUE);

It seems to have created a somewhat more complex plan but as well a much faster plan.

Oh yeah I also added a prallels setting of 5 I think it was.

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=465 Card=1 Bytes=35)    1 0 SORT (AGGREGATE)
   2 1 SORT* (AGGREGATE)
:Q107900

2

   3 2 HASH JOIN* (SEMI) (Cost=465 Card=65266 Bytes=2284310)
:Q107900

2

   4 3 INDEX* (FAST FULL SCAN) OF 'XIF12NC_PARAMS_EI' (NON-
:Q107900

          UNIQUE) (Cost=8 Card=65266 Bytes=1109522) 0

   5 3 INDEX* (FAST FULL SCAN) OF 'XIF12NC_PARAMS' (NON-UNI
:Q107900

          QUE) (Cost=457 Card=6050570 Bytes=108910260) 1

   2 PARALLEL_TO_SERIAL SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(*)) F

                                   ROM (SELECT /*+ ORDERED NO_EXPAND US

   3 PARALLEL_COMBINED_WITH_PARENT
   4 PARALLEL_TO_PARALLEL          SELECT /*+ INDEX_RRS(A1
"XIF12NC_PARAMS_EI")
                                    */ A1."OBJECT_ID" C0,A1."ATTR_ID" C

   5 PARALLEL_TO_PARALLEL          SELECT /*+ INDEX_RRS(A1
"XIF12NC_PARAMS") */
                                    A1."OBJECT_ID" C0,A1."ATTR_ID" C1 F