Problem with Query Speed on different instances - different execution plan

Sybrand Bakker · *nix forums Guru Joined: 03 Apr 2005 Posts: 1766

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

Justin Cave · *nix forums beginner Joined: 10 Feb 2005 Posts: 1

Marc Eggenberger wrote:

Marc Eggenberger · *nix forums beginner Joined: 21 Apr 2005 Posts: 6

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.

--
mfg
Marc Eggenberger

Google