Tuning query

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Mon, 2 Dec 2002 15:00:12 -0000
Message-ID: <3deb7b2a$0$711$ed9e5944@reading.news.pipex.net>

I have two (very) similar queries which execute in radically different timescales.

Query 1.

SELECT
'A' AS tab,

t.dim1,
rr3dim3.rel_value AS r3dim3,
t.dim3,
rr0dim4.rel_value AS r0dim4,

t.dim4,rr0dim5.rel_value AS r0dim5,
t.dim5,
t.dim6,
t.period,

rr0dim2.rel_value AS r0dim2,
t.dim2,
SUM(t.value_1) AS value_1,
SUM(t.amount) AS amount,
t.client
FROM
aagdaenq3 t ,

aglrelvalue rr3dim3,
aglrelvalue rr0dim4,
aglrelvalue rr0dim5,

aglrelvalue rr0dim2
WHERE 1=1
AND t.client = rr3dim3.client
AND rr3dim3.rel_attr_id = 'C0'
AND rr3dim3.attribute_id = 'B0'
AND t.dim3=rr3dim3.att_value
AND t.client = rr0dim4.client
AND rr0dim4.rel_attr_id = 'SM'
AND rr0dim4.attribute_id = 'BF'
AND t.dim4=rr0dim4.att_value
AND t.client = rr0dim5.client
AND rr0dim5.rel_attr_id = 'RA'
AND rr0dim5.attribute_id = 'B1'
AND t.dim5 BETWEEN rr0dim5.att_val_from AND rr0dim5.att_val_to AND t.client = rr0dim2.client
AND rr0dim2.rel_attr_id = 'R0'
AND rr0dim2.attribute_id = 'A0'
AND t.dim2=rr0dim2.att_value
AND rr3dim3.rel_value = '002277'

AND t.dim2 IN  ('1010','3010','3110')
AND t.period >= 200301 AND t.period <= 200301
AND t.client IN ('B1','DE','AC')

GROUP BY t.dim1,
rr3dim3.rel_value,
t.dim3,
rr0dim4.rel_value,
t.dim4,
rr0dim5.rel_value,

t.dim5,
t.dim6,
t.period,

rr0dim2.rel_value,
t.dim2,
t.client
HAVING
(SUM(t.value_1) <> 0 OR SUM(t.amount) <> 0) ORDER BY
1,

4 asc,
6 asc,
7 asc,

12 asc,
10 asc,
9 asc

takes 11 secs to execute and has the execution plan

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1696 Card=10 Bytes=1
          300)

   1    0   SORT (ORDER BY) (Cost=1696 Card=10 Bytes=1300)
   2    1     FILTER
   3    2       SORT (GROUP BY) (Cost=1696 Card=10 Bytes=1300)
   4    3         HASH JOIN (Cost=1690 Card=101 Bytes=13130)
   5    4           NESTED LOOPS (Cost=55 Card=4 Bytes=416)
   6    5             HASH JOIN (Cost=40 Card=1 Bytes=85)
   7    6               INDEX (RANGE SCAN) OF 'AIAGLRELVALUE1' (UNIQUE
          ) (Cost=3 Card=63 Bytes=1197)

   8    6               NESTED LOOPS (Cost=36 Card=106 Bytes=6996)
   9    8                 INLIST ITERATOR
  10    9                   TABLE ACCESS (BY INDEX ROWID) OF 'AGLRELVA
          LUE' (Cost=6 Card=1 Bytes=19)

  11   10                     INDEX (RANGE SCAN) OF 'AIAGLRELVALUE2' (
          NON-UNIQUE) (Cost=3 Card=1)

  12    8                 INLIST ITERATOR
  13   12                   TABLE ACCESS (BY INDEX ROWID) OF 'AAGDAENQ
          3' (Cost=30 Card=7785 Bytes=365895)

  14   13                     INDEX (RANGE SCAN) OF 'I_AAGDAENQ3' (NON
          -UNIQUE) (Cost=25 Card=7785)

  15    5             TABLE ACCESS (BY INDEX ROWID) OF 'AGLRELVALUE' (
          Cost=15 Card=27836 Bytes=528884)

  16   15               INDEX (RANGE SCAN) OF 'AIAGLRELVALUE2' (NON-UN
          IQUE) (Cost=2 Card=27836)

  17    4           TABLE ACCESS (FULL) OF 'AGLRELVALUE' (Cost=1634 Ca
          rd=10187 Bytes=264862)

meanwhile

query2

SELECT
'A' AS tab,

t.dim1,
rr3dim3.rel_value AS r3dim3,
t.dim3,
rr0dim4.rel_value AS r0dim4,
t.dim4,
rr0dim5.rel_value AS r0dim5,

t.dim5,
t.dim6,
t.period,

rr0dim2.rel_value AS r0dim2,
t.dim2,
SUM(t.value_1) AS value_1,
SUM(t.amount) AS amount,
t.client
FROM aagdaenq3 t ,

aglrelvalue rr3dim3,
aglrelvalue rr0dim4,
aglrelvalue rr0dim5,

AND t.dim2 IN  ('1010','3010','3110')
AND t.period >= 200301 AND t.period <= 200301
AND t.client = 'AC'

GROUP BY t.dim1,
rr3dim3.rel_value,
t.dim3,
rr0dim4.rel_value,
t.dim4,
rr0dim5.rel_value,

t.dim5,
t.dim6,
t.period,

rr0dim2.rel_value,

t.dim2,
t.client,
t.client

HAVING
(SUM(t.value_1) <> 0 OR SUM(t.amount) <> 0) ORDER BY
1,

4 asc,
6 asc,
7 asc,

12 asc,
10 asc,
9 asc

takes 25 minutes to run and has the execution plan

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1693 Card=3 Bytes=39
          0)

   1    0   SORT (ORDER BY) (Cost=1693 Card=3 Bytes=390)
   2    1     FILTER
   3    2       SORT (GROUP BY) (Cost=1693 Card=3 Bytes=390)
   4    3         NESTED LOOPS (Cost=1689 Card=25 Bytes=3250)
   5    4           NESTED LOOPS (Cost=55 Card=1 Bytes=104)
   6    5             HASH JOIN (Cost=40 Card=1 Bytes=85)
   7    6               INDEX (RANGE SCAN) OF 'AIAGLRELVALUE1' (UNIQUE
          ) (Cost=3 Card=63 Bytes=1197)

   8    6               NESTED LOOPS (Cost=36 Card=107 Bytes=7062)
   9    8                 INLIST ITERATOR
  10    9                   TABLE ACCESS (BY INDEX ROWID) OF 'AGLRELVA
          LUE' (Cost=6 Card=1 Bytes=19)

  11   10                     INDEX (RANGE SCAN) OF 'AIAGLRELVALUE2' (
          NON-UNIQUE) (Cost=3 Card=1)

  12    8                 INLIST ITERATOR
  13   12                   TABLE ACCESS (BY INDEX ROWID) OF 'AAGDAENQ
          3' (Cost=30 Card=7785 Bytes=365895)

  14   13                     INDEX (RANGE SCAN) OF 'I_AAGDAENQ3' (NON
          -UNIQUE) (Cost=25 Card=7785)

  15    5             TABLE ACCESS (BY INDEX ROWID) OF 'AGLRELVALUE' (
          Cost=15 Card=27835 Bytes=528865)

  16   15               INDEX (RANGE SCAN) OF 'AIAGLRELVALUE2' (NON-UN
          IQUE) (Cost=2 Card=27835)

  17    4           TABLE ACCESS (FULL) OF 'AGLRELVALUE' (Cost=1634 Ca
          rd=10187 Bytes=264862)

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************

Received on Mon Dec 02 2002 - 09:00:12 CST