Home » RDBMS Server » Performance Tuning » Diff Execution Plan for Same Query?Pl:help
Diff Execution Plan for Same Query?Pl:help [message #65993] Thu, 17 February 2005 20:56 Go to next message
BhavinShah
Messages: 105
Registered: February 2004
Senior Member
Hi, Guys

I have a strange result for my query.. I am getting two diffn execution plan for the same query for two diffn database.

my both database is oracle 9.2 on windows 2k server.

my query :
select
c.trans_id,c.cop_id, c.parent_id, c.entry_date, c.user_id,c.l1e1_id,c.l1e1_name,c.l2e1_id,
c.l2e1_name, c.l1e2_id,c.l1e2_name,c.l2e2_id,c.l2e2_name,c.l1e3_id,c.l1e3_name, c.l2e3_id,c.l2e3_name,c.l1e4_id,
c.l1e4_name,c.l2e4_id,c.l2e4_name, c.l1e5_id,c.l1e5_name,c.l2e5_id,c.l2e5_name,c.l1e7_id,c.l1e6_id, c.l1e6_name,
c.l1e8_id,c.l1e8_name,c.logentry,c.user_id as assignedTo
from (
select
rownum r, main_trn1.trans_id,main_trn1.cop_id,
main_trn1.parent_id,to_char(main_trn1.entry_date,'MM/DD/YYYY HH:MI:SS') as entry_date, main_trn1.user_id, main_trn1.l1e1_id,
main_trn1.l1e1_name,main_trn1.l2e1_id,main_trn1.l2e1_name, main_trn1.l1e2_id,main_trn1.l1e2_name,main_trn1.l2e2_id,
main_trn1.l2e2_name, main_trn1.l1e3_id,main_trn1.l1e3_name,main_trn1.l2e3_id,main_trn1.l2e3_name, main_trn1.l1e4_id,
main_trn1.l1e4_name,main_trn1.l2e4_id,main_trn1.l2e4_name, main_trn1.l1e5_id,main_trn1.l1e5_name,main_trn1.l2e5_id,main_trn1.l2e5_name,
main_trn1.l1e7_id, main_trn1.l1e6_id,main_trn1.l1e6_name, main_trn1.l1e8_id,main_trn1.l1e8_name, main_trn2.logentry,
main_trn2.user_id as assignedTo
from
main_trn1,main_trn2
where
main_trn1.trans_id = main_trn2.trans_id and main_trn1.cop_id =1
and trunc(main_trn1.entry_date) between to_date('11/01/2003','MM/DD/RRRR') and to_date('11/26/2004','MM/DD/RRRR')
connect by prior main_trn1.trans_id = main_trn1.parent_id start with main_trn1.parent_id is null
ORDER SIBLINGS BY entry_date
) c
where c.r between 1 and 10

Execution Plan on database A:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1050 Card=3762 Bytes
=9036324)

1 0 VIEW (Cost=1050 Card=3762 Bytes=9036324)
2 1 COUNT
3 2 FILTER
4 3 CONNECT BY (WITH FILTERING)
5 4 FILTER
6 5 COUNT
7 6 MERGE JOIN (Cost=572 Card=3762 Bytes=1583802)
8 7 SORT (JOIN) (Cost=473 Card=3762 Bytes=134303
4)

9 8 TABLE ACCESS (FULL) OF 'MAIN_TRN2' (Cost=6
6 Card=3762 Bytes=1343034)

10 7 SORT (JOIN) (Cost=99 Card=3763 Bytes=240832)
11 10 TABLE ACCESS (FULL) OF 'MAIN_TRN1' (Cost=1
7 Card=3763 Bytes=240832)

12 4 HASH JOIN
13 12 CONNECT BY PUMP
14 12 COUNT
15 14 MERGE JOIN (Cost=572 Card=3762 Bytes=1583802)
16 15 SORT (JOIN) (Cost=473 Card=3762 Bytes=134303
4)

17 16 TABLE ACCESS (FULL) OF 'MAIN_TRN2' (Cost=6
6 Card=3762 Bytes=1343034)

18 15 SORT (JOIN) (Cost=99 Card=3763 Bytes=240832)
19 18 TABLE ACCESS (FULL) OF 'MAIN_TRN1' (Cost=1
7 Card=3763 Bytes=240832)

Execution Plan on Database B:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=378 Card=3759 Bytes=
9029118)

1 0 VIEW (Cost=378 Card=3759 Bytes=9029118)
2 1 COUNT
3 2 FILTER
4 3 CONNECT BY (WITH FILTERING)
5 4 FILTER
6 5 COUNT
7 6 HASH JOIN (Cost=81 Card=3759 Bytes=1721622)
8 7 TABLE ACCESS (FULL) OF 'MAIN_TRN1' (Cost=12
Card=3760 Bytes=240640)

9 7 TABLE ACCESS (FULL) OF 'MAIN_TRN2' (Cost=43
Card=3759 Bytes=1481046)

10 4 HASH JOIN
11 10 CONNECT BY PUMP
12 10 COUNT
13 12 HASH JOIN (Cost=81 Card=3759 Bytes=1721622)
14 13 TABLE ACCESS (FULL) OF 'MAIN_TRN1' (Cost=12
Card=3760 Bytes=240640)

15 13 TABLE ACCESS (FULL) OF 'MAIN_TRN2' (Cost=43
Card=3759 Bytes=1481046)

So, Diffn Between A and B is sort join . my query perform unnecessary sort join for database A which will not happen for database B. my pga_aggregate_target size is same for A AND B.I am unable to find answer of this question. Please help to get me out of this puzzle

Bhavin .................
Re: Diff Execution Plan for Same Query?Pl:help [message #109304 is a reply to message #65993] Wed, 23 February 2005 12:14 Go to previous message
Mack Sundar
Messages: 20
Registered: July 2002
Junior Member
I would ensure that the HASH_JOIN_ENABLED is set to TRUE and HASH_AREA_SIZE is large enough on A.

Also ensure that the tables and indexes have been analyzed / rebuilt.
Previous Topic: Pass parameter to DML or new procedure?
Next Topic: Invalid Object in sys schema
Goto Forum:
  


Current Time: Fri Mar 29 10:18:15 CDT 2024