SQL Tuning Question

From: <mitarupa_at_my-deja.com>
Date: 2000/06/13
Message-ID: <8i68d1$ibr$1@nnrp1.deja.com>#1/1

Here are two SQL statements which are very similar but are providing radically different performance results.

Query #1

---

set autotrace traceonly explain

select distinct a.sales_territory,a.region,a.country_name from ods_facts_old b,ods_countries a
where b.iteration_id = 2097
and a.country_id = b.country_id
/

Execution Plan

---

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4608 Card=4025 Bytes
          =51169779)

   1    0   SORT (UNIQUE) (Cost=4608 Card=4025 Bytes=51169779)
   2    1     HASH JOIN (Cost=734 Card=460989 Bytes=51169779)
   3    2       TABLE ACCESS (FULL) OF 'ODS_COUNTRIES' (Cost=1 Card=47
           Bytes=3995)

   4    2       TABLE ACCESS (FULL) OF 'ODS_FACTS_OLD' (Cost=732 Card=
          460989 Bytes=11985714)

Query #2

---

set autotrace traceonly explain

select distinct a.sales_territory,a.region,a.country_name from ods_facts b,ods_countries a
where b.iteration_id = 2097
and a.country_id = b.country_id
/

Execution Plan

---

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=269 Card=4025 Bytes=
          25739901)

   1 0 SORT* (UNIQUE) (Cost=269 Card=4025 Bytes=25739901)
:Q600000

                                                                       3

   2    1     HASH JOIN* (Cost=70 Card=231891 Bytes=25739901)


:Q600000

                                                                       2

   3    2       TABLE ACCESS* (FULL) OF 'ODS_COUNTRIES' (Cost=1 Card=4


:Q600000

          7 Bytes=3995)                                                0

   4    2       PARTITION* (CONCATENATED)


:Q600000

                                                                       2

   5    4         TABLE ACCESS* (FULL) OF 'ODS_FACTS' (Cost=68 Card=23


:Q600000

          1891 Bytes=6029166)                                          1



   1 PARALLEL_TO_SERIAL            SELECT DISTINCT C0,C1,C2 FROM


:Q6000002

   2 PARALLEL_TO_PARALLEL          SELECT /*+ ORDERED NO_EXPAND
USE_HASH(A2) SW
                                   AP_JOIN_INPUTS(A2) */ A2.C3 C0,A2.C1

   3 PARALLEL_FROM_SERIAL
   4 PARALLEL_COMBINED_WITH_PARENT
   5 PARALLEL_TO_PARALLEL          SELECT /*+ ROWID(A1) */
A1."COUNTRY_ID" C0 F
                                   ROM "ODS_FACTS" PARTITION(:B1)  A1 W

Query #2 uses ods_facts table instead of ods_facts_old (in query #1). ods_facts & ods_facts_old are same tables (schema & data-wise) each with ~10 million rows. ods_facts is partitioned on iteration_id,period_id while ods_facts is partitioned only on iteration_id. I was expecting performance of query running against ods_facts to be better which is supported by explain plan output because query now refers to multiple partitions (each iteration contains more than one period) of ods_facts which can be processed in parallel.
But I noticed few things. Even to get the explain plan for query #2 is taking quite sometime as opposed to plan for query #1. Runtime performance of query #1 turns out to be far better compared to query #2. This surprises me. I could not analyze ods_facts table by a single analyze command but did it for all partitions individually. There are 476 partitions of ODS_FACTS as opposed to 167 for ODS_FACTS_OLD.

Another difference between these two tables - ODS_FACTS_OLD contains a local prefixed index on iteration_id and another local non-prefixed index on period id while ODS_FACTS contains one local prefixed index on (iteration_id,period_id). Both of them have non-prefixed indexes on country_id. I will appreciate any insight. Thanks
Abhijit Bhattacharya

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Jun 13 2000 - 00:00:00 CDT