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  |
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 .................
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Jan 03 15:24:11 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
