Re: Problem with Query Speed on different instances - different execution plan

From: Sybrand Bakker <sybrandb_at_hccnet.nl>
Date: Thu, 10 Feb 2005 19:29:43 +0100
Message-ID: <po9n01tof3gk014lio4iisch7vtj047u4k@4ax.com>

On Thu, 10 Feb 2005 14:26:01 +0100, Marc Eggenberger <marc.eggenberger_at_remove.itc.alstom.com> wrote:

>Hi there ...
>
>I have the following environment:
>
>Windows 2000 Adv Server with SP4, Dual CPU Pentium III 1.2GHz with 4GB
>RAM.
>
>Oracle 8.1.7.0.0
>
>I have 2 instances, one a productive and a test instance. Both instances
>have the same objects (tables, views, indexes etc). Statistics have been
>updated on both instances. The productive instance is not very busy. I
>also tested those queries on times when there is none to maybe very
>little activity, same results.
>
>
>The table TDM_TOOL has about 19k entries, TDM_TOOLVALUES about 62k rows.
>
>The application is done by an external partner. Just got a message that
>this query is fast on one instance, but very slow on the other:
>
>The query is:
>
>SELECT a.toolid, c1.valnum AS YS , c1.TOOLCLASSFIELDSPOS AS POS1,
>c1.EMPTYFLAG AS FLAG1,
> c2.valnum AS YS , c2.TOOLCLASSFIELDSPOS AS POS2,
>c2.EMPTYFLAG AS FLAG2,
> c3.valnum AS YS , c3.TOOLCLASSFIELDSPOS AS
>POS3, c3.EMPTYFLAG AS FLAG3,
> c4.valnum AS YS , c4.TOOLCLASSFIELDSPOS AS
>POS4, c4.EMPTYFLAG AS FLAG4,
> c5.valnum AS YS , c5.TOOLCLASSFIELDSPOS AS
>POS5, c5.EMPTYFLAG AS FLAG5
>
> FROM tms.TDM_TOOL a,
> tms.TDM_TOOLVALUES c1,
> tms.TDM_TOOLVALUES c2,
> tms.TDM_TOOLVALUES c3,
> tms.TDM_TOOLVALUES c4,
> tms.TDM_TOOLVALUES c5
>
>WHERE TOOLCLASSID IN
>('D01','D04','D06','D07','D08','M01','M02','M03','M04','M05','M06','M07'
>,'M08','M09','M10','M12','T01','T02','T03','T04','T05','T06','T07','T08'
>,'T09','T10')
>
> AND c1.toolid (+) = a.toolid
> AND c1.TOOLCLASSFIELDSPOS (+) = NVL((SELECT h.TOOLCLASSFIELDSPOS FROM
>tms.TDM_TOOLCLASSFIELDS h
> WHERE a.TOOLCLASSID =
>h.TOOLCLASSID
> AND h.functypeid =
>'YS'),0)
>
> AND c2.toolid (+) = a.toolid
> AND c2.TOOLCLASSFIELDSPOS (+) = NVL((SELECT h.TOOLCLASSFIELDSPOS FROM
>tms.TDM_TOOLCLASSFIELDS h
> WHERE a.TOOLCLASSID =
>h.TOOLCLASSID
> AND h.functypeid =
>'XS' ),0)
>
>
> AND c3.toolid (+) = a.toolid
> AND c3.TOOLCLASSFIELDSPOS (+) = NVL((SELECT h.TOOLCLASSFIELDSPOS FROM
>tms.TDM_TOOLCLASSFIELDS h
> WHERE a.TOOLCLASSID =
>h.TOOLCLASSID
> AND h.functypeid = 'R
>CORNER' ),0)
> AND c4.toolid (+) = a.toolid
> AND c4.TOOLCLASSFIELDSPOS (+) = NVL((SELECT h.TOOLCLASSFIELDSPOS FROM
>tms.TDM_TOOLCLASSFIELDS h
> WHERE a.TOOLCLASSID =
>h.TOOLCLASSID
> AND h.functypeid =
>'SPIN' ),0)
> AND c5.toolid (+) = a.toolid
> AND c5.TOOLCLASSFIELDSPOS (+) = NVL((SELECT h.TOOLCLASSFIELDSPOS FROM
>tms.TDM_TOOLCLASSFIELDS h
> WHERE a.TOOLCLASSID =
>h.TOOLCLASSID
> AND h.functypeid = 'A
>KAPPA' ),0)
>
>
>On the test instance I get the following explain path:
>
>Operation Object Name Rows Bytes Cost Object Node In/Out
> PStart PStop
>
>SELECT STATEMENT Optimizer Mode=CHOOSE 176 904
>
> NESTED LOOPS OUTER 176 26 K 904
>
> NESTED LOOPS OUTER 176 21 K 728
>
> NESTED LOOPS OUTER 176 17 K 552
>
> NESTED LOOPS OUTER 176 12 K 376
>
> NESTED LOOPS OUTER 176 7 K 200
>
> TABLE ACCESS FULL TMS.TDM_TOOL 176 3 K 24
>
> TABLE ACCESS BY INDEX ROWID TMS.TDM_TOOLVALUES 61 K 1 M
> 1
> INDEX UNIQUE SCAN TMS.TDM_TOOLVALUES_IDX 61 K
>
> TABLE ACCESS BY INDEX ROWID TMS.TDM_TOOLCLASSFIELDS
> 1 25 4
> INDEX RANGE SCAN TMS.TDM_TOOLCLASSFIELDS_IDX 1
> 2
> TABLE ACCESS BY INDEX ROWID TMS.TDM_TOOLVALUES 61 K 1 M
> 1
> INDEX UNIQUE SCAN TMS.TDM_TOOLVALUES_IDX 61 K
>
> TABLE ACCESS BY INDEX ROWID TMS.TDM_TOOLCLASSFIELDS 1
> 25 4
> INDEX RANGE SCAN TMS.TDM_TOOLCLASSFIELDS_IDX 1
> 2
> TABLE ACCESS BY INDEX ROWID TMS.TDM_TOOLVALUES 61 K 1 M
> 1
> INDEX UNIQUE SCAN TMS.TDM_TOOLVALUES_IDX 61 K
>
> TABLE ACCESS BY INDEX ROWID TMS.TDM_TOOLCLASSFIELDS 1
> 25 4
> INDEX RANGE SCAN TMS.TDM_TOOLCLASSFIELDS_IDX 1
> 2
> TABLE ACCESS BY INDEX ROWID TMS.TDM_TOOLVALUES 61 K 1 M 1
>
> INDEX UNIQUE SCAN TMS.TDM_TOOLVALUES_IDX 61 K
>
> TABLE ACCESS BY INDEX ROWID TMS.TDM_TOOLCLASSFIELDS 1
> 25 4
> INDEX RANGE SCAN TMS.TDM_TOOLCLASSFIELDS_IDX 1
> 2
> TABLE ACCESS BY INDEX ROWID TMS.TDM_TOOLVALUES 61 K 1 M 1
>
> INDEX UNIQUE SCAN TMS.TDM_TOOLVALUES_IDX 61 K
>
> TABLE ACCESS BY INDEX ROWID TMS.TDM_TOOLCLASSFIELDS 1
> 25 4
> INDEX RANGE SCAN TMS.TDM_TOOLCLASSFIELDS_IDX 1 2
>
>
>
>On the productive instance I get the following explain path:
>
>Operation Object Name Rows Bytes Cost Object Node In/Out
> PStart PStop
>
>SELECT STATEMENT Optimizer Mode=CHOOSE 9 K 4921
>
> FILTER
> MERGE JOIN OUTER 9 K 1 M 4921
>
> MERGE JOIN OUTER 9 K 1 M 3973
>
> MERGE JOIN OUTER 9 K 871 K 3025
>
> SORT JOIN 9 K 639 K 2077
>
> HASH JOIN OUTER 9 K 639 K 1826
>
> HASH JOIN OUTER 9 K 409 K 751
>
> TABLE ACCESS FULL TMS.TDM_TOOL 9 K 181 K 24
>
> TABLE ACCESS FULL TMS.TDM_TOOLVALUES 61 K 1 M 41
>
> TABLE ACCESS FULL TMS.TDM_TOOLVALUES 61 K 1 M 41
>
> SORT JOIN 61 K 1 M 948
>
> TABLE ACCESS FULL TMS.TDM_TOOLVALUES 61 K 1 M 41
>
> SORT JOIN 61 K 1 M 948
>
> TABLE ACCESS FULL TMS.TDM_TOOLVALUES 61 K 1 M 41
>
> SORT JOIN 61 K 1 M 948
>
> TABLE ACCESS FULL TMS.TDM_TOOLVALUES 61 K 1 M 41
>
> TABLE ACCESS BY INDEX ROWID TMS.TDM_TOOLCLASSFIELDS 1 13
> 5
> INDEX RANGE SCAN TMS.TDM_TOOLCLASSFIELDS_IDX 1 2
>
> TABLE ACCESS BY INDEX ROWID TMS.TDM_TOOLCLASSFIELDS 1 13
> 5
> INDEX RANGE SCAN TMS.TDM_TOOLCLASSFIELDS_IDX 1 2
>
> TABLE ACCESS BY INDEX ROWID TMS.TDM_TOOLCLASSFIELDS 1 13
> 5
> INDEX RANGE SCAN TMS.TDM_TOOLCLASSFIELDS_IDX 1 2
>
> TABLE ACCESS BY INDEX ROWID TMS.TDM_TOOLCLASSFIELDS 1 13
> 5
> INDEX RANGE SCAN TMS.TDM_TOOLCLASSFIELDS_IDX 1 2
>
> TABLE ACCESS BY INDEX ROWID TMS.TDM_TOOLCLASSFIELDS 1 13
> 5
> INDEX RANGE SCAN TMS.TDM_TOOLCLASSFIELDS_IDX 1 2
>
>
>
>I'm a bit confused .. can someone direct me to the right directions?
>What should I try, change? And why?
>I'm already taking snapshots but they couldnt help me yet ... and
>Oraperf.com is temporarly offline ...
>
>Thanks for any help, hints.

The test instance favors full table scans and hash joins. This might be caused by the data, but then again it might be caused by init.ora parameter settings you need to verify anyway.

The most important are
db_block_size
db_file_multiblock_read_count

sort_area_size
hash_join_enabled
hash_area_size
optimizer_features_enable
optimizer_index_cost_adj

optimizer_index_caching
optimizer_goal

If that doesn't resolve it you would need to explain the statement with event 10053 set to level 1.
This will produce a trace file, in which CBO explains it's stupid decisions.

--
Sybrand Bakker, Senior Oracle DBA

Received on Thu Feb 10 2005 - 12:29:43 CST