Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Tuning query

Re: Tuning query

From: damorgan <damorgan_at_exesolutions.com>
Date: Mon, 02 Dec 2002 16:12:58 GMT
Message-ID: <3DEB8682.2F2299C1@exesolutions.com>


Niall Litchfield wrote:

> 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,
> 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 = '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
>
> ******************************************

Love your column names. ;-)

Daniel Morgan Received on Mon Dec 02 2002 - 10:12:58 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US