Home » SQL & PL/SQL » SQL & PL/SQL » Different explain plan in a similar DBs
Different explain plan in a similar DBs [message #212548] Fri, 05 January 2007 15:44 Go to next message
misha603
Messages: 20
Registered: July 2005
Location: India
Junior Member

Quote:
Hi,
Can someone please tell me why do I get a different explain plan when I run the same query in 2 similar DBs. I have 2 DBs PROD and QA (Refreshed from PROD just a week ago so not must of a difference between QA and PROD) I have all the same indexes in PROD and QA, but still I get the different explain plan
I have a query which runs in 1 min in QA and 10 mins in production. When I took the trace file I saw that the number of rows is too large (15552000) in production. I don't think there is a much of the difference between data in PROD and QA, but still I don't understand why should this query take 10 mins in PROD and 1 min in QA.
Please find below the trace file for your reference
PROD
====
Rows Row Source Operation
------- ---------------------------------------------------
6 SORT UNIQUE
6 UNION-ALL
0 MINUS
120 SORT UNIQUE
720 FILTER
38880 NESTED LOOPS
15552000 MERGE JOIN CARTESIAN
129600 MERGE JOIN CARTESIAN
1080 TABLE ACCESS BY INDEX ROWID BILL_GROUP_BILL
1101 NESTED LOOPS
20 MERGE JOIN CARTESIAN
20 NESTED LOOPS
1 NESTED LOOPS
1 INDEX UNIQUE SCAN UICASE (object id 48973)
1 TABLE ACCESS BY INDEX ROWID BILL_GROUP
1 INDEX UNIQUE SCAN UIBILLGROUP (object id 48967)
20 TABLE ACCESS BY INDEX ROWID EXPRNCGRP_BLLGRP_ASSOC
20 INDEX RANGE SCAN IEXPBLLGRPASSOC (object id 48943)
20 BUFFER SORT
1 TABLE ACCESS BY INDEX ROWID CASE_ADMIN_PROVISION
1 INDEX RANGE SCAN UICASEADMINPROV (object id 48974)
1 SORT AGGREGATE
1 TABLE ACCESS BY INDEX ROWID CASE_ADMIN_PROVISION
1 INDEX RANGE SCAN UICASEADMINPROV (object id 48974)
1080 INDEX RANGE SCAN IBLLGRPBLL1 (object id 48535)
129600 BUFFER SORT
120 TABLE ACCESS BY INDEX ROWID PLAN_EMP_GROUP_ASSOC
120 INDEX RANGE SCAN IPLANEMPGRPASSOCA1 (object id 48951)
15552000 BUFFER SORT
120 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
120 INDEX RANGE SCAN IPEGAEXPGRPASSOC (object id 48949)
38880 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
15552000 INDEX UNIQUE SCAN UIPEGAEXPGRPASSOC (object id 49019)
26016 SORT AGGREGATE
26016 TABLE ACCESS BY INDEX ROWID PLAN_EMP_GROUP_ASSOC
78048 NESTED LOOPS
26016 NESTED LOOPS
26016 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
26016 INDEX RANGE SCAN UIPEGAEXPGRPASSOC (object id 49019)
26016 TABLE ACCESS BY INDEX ROWID EXPRNCGRP_BLLGRP_ASSOC
520320 INDEX RANGE SCAN IEXPBLLGRPASSOC (object id 48943)
26016 INDEX RANGE SCAN UIPLANEMPGRPASSOC (object id 49021)
1 SORT AGGREGATE
54 TABLE ACCESS BY INDEX ROWID BILL_GROUP_BILL
54 INDEX RANGE SCAN IBLLGRPBLL1 (object id 48535)
126 SORT UNIQUE
756 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
883 NESTED LOOPS
126 MERGE JOIN CARTESIAN
126 NESTED LOOPS
2646 MERGE JOIN CARTESIAN
21 NESTED LOOPS
1 NESTED LOOPS
1 INDEX UNIQUE SCAN UICASE (object id 48973)
1 TABLE ACCESS BY INDEX ROWID BILL_GROUP
1 INDEX UNIQUE SCAN UIBILLGROUP (object id 48967)
21 TABLE ACCESS BY INDEX ROWID EXPRNCGRP_BLLGRP_ASSOC
21 INDEX RANGE SCAN IEXPBLLGRPASSOC (object id 48943)
2646 BUFFER SORT
126 TABLE ACCESS BY INDEX ROWID PLAN_EMP_GROUP_ASSOC
126 INDEX RANGE SCAN IPLANEMPGRPASSOCA1 (object id 48951)
126 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
2646 INDEX UNIQUE SCAN UIPEGAEXPGRPASSOC (object id 49019)
546 SORT AGGREGATE
546 TABLE ACCESS BY INDEX ROWID PLAN_EMP_GROUP_ASSOC
1638 NESTED LOOPS
546 NESTED LOOPS
546 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
546 INDEX RANGE SCAN UIPEGAEXPGRPASSOC (object id 49019)
546 TABLE ACCESS BY INDEX ROWID EXPRNCGRP_BLLGRP_ASSOC
11466 INDEX RANGE SCAN IEXPBLLGRPASSOC (object id 48943)
546 INDEX RANGE SCAN UIPLANEMPGRPASSOC (object id 49021)
126 BUFFER SORT
1 TABLE ACCESS BY INDEX ROWID CASE_ADMIN_PROVISION
1 INDEX RANGE SCAN UICASEADMINPROV (object id 48974)
1 SORT AGGREGATE
1 TABLE ACCESS BY INDEX ROWID CASE_ADMIN_PROVISION
1 INDEX RANGE SCAN UICASEADMINPROV (object id 48974)
756 INDEX RANGE SCAN IPEGAEXPGRPASSOC (object id 48949)
6 MINUS
126 SORT UNIQUE
756 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
883 NESTED LOOPS
126 MERGE JOIN CARTESIAN
126 NESTED LOOPS
2646 MERGE JOIN CARTESIAN
21 NESTED LOOPS
1 NESTED LOOPS
1 INDEX UNIQUE SCAN UICASE (object id 48973)
1 TABLE ACCESS BY INDEX ROWID BILL_GROUP
1 INDEX UNIQUE SCAN UIBILLGROUP (object id 48967)
21 TABLE ACCESS BY INDEX ROWID EXPRNCGRP_BLLGRP_ASSOC
21 INDEX RANGE SCAN IEXPBLLGRPASSOC (object id 48943)
2646 BUFFER SORT
126 TABLE ACCESS BY INDEX ROWID PLAN_EMP_GROUP_ASSOC
126 INDEX RANGE SCAN IPLANEMPGRPASSOCA1 (object id 48951)
126 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
2646 INDEX UNIQUE SCAN UIPEGAEXPGRPASSOC (object id 49019)
546 SORT AGGREGATE
546 TABLE ACCESS BY INDEX ROWID PLAN_EMP_GROUP_ASSOC
1638 NESTED LOOPS
546 NESTED LOOPS
546 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
546 INDEX RANGE SCAN UIPEGAEXPGRPASSOC (object id 49019)
546 TABLE ACCESS BY INDEX ROWID EXPRNCGRP_BLLGRP_ASSOC
11466 INDEX RANGE SCAN IEXPBLLGRPASSOC (object id 48943)
546 INDEX RANGE SCAN UIPLANEMPGRPASSOC (object id 49021)
126 BUFFER SORT
1 TABLE ACCESS BY INDEX ROWID CASE_ADMIN_PROVISION
1 INDEX RANGE SCAN UICASEADMINPROV (object id 48974)
1 SORT AGGREGATE
1 TABLE ACCESS BY INDEX ROWID CASE_ADMIN_PROVISION
1 INDEX RANGE SCAN UICASEADMINPROV (object id 48974)
756 INDEX RANGE SCAN IPEGAEXPGRPASSOC (object id 48949)
120 SORT UNIQUE
720 FILTER
38880 NESTED LOOPS
15552000 MERGE JOIN CARTESIAN
129600 MERGE JOIN CARTESIAN
1080 TABLE ACCESS BY INDEX ROWID BILL_GROUP_BILL
1101 NESTED LOOPS
20 MERGE JOIN CARTESIAN
20 NESTED LOOPS
1 NESTED LOOPS
1 INDEX UNIQUE SCAN UICASE (object id 48973)
1 TABLE ACCESS BY INDEX ROWID BILL_GROUP
1 INDEX UNIQUE SCAN UIBILLGROUP (object id 48967)
20 TABLE ACCESS BY INDEX ROWID EXPRNCGRP_BLLGRP_ASSOC
20 INDEX RANGE SCAN IEXPBLLGRPASSOC (object id 48943)
20 BUFFER SORT
1 TABLE ACCESS BY INDEX ROWID CASE_ADMIN_PROVISION
1 INDEX RANGE SCAN UICASEADMINPROV (object id 48974)
1 SORT AGGREGATE
1 TABLE ACCESS BY INDEX ROWID CASE_ADMIN_PROVISION
1 INDEX RANGE SCAN UICASEADMINPROV (object id 48974)
1080 INDEX RANGE SCAN IBLLGRPBLL1 (object id 48535)
129600 BUFFER SORT
120 TABLE ACCESS BY INDEX ROWID PLAN_EMP_GROUP_ASSOC
120 INDEX RANGE SCAN IPLANEMPGRPASSOCA1 (object id 48951)
15552000 BUFFER SORT
120 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
120 INDEX RANGE SCAN IPEGAEXPGRPASSOC (object id 48949)
38880 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
15552000 INDEX UNIQUE SCAN UIPEGAEXPGRPASSOC (object id 49019)
26016 SORT AGGREGATE
26016 TABLE ACCESS BY INDEX ROWID PLAN_EMP_GROUP_ASSOC
78048 NESTED LOOPS
26016 NESTED LOOPS
26016 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
26016 INDEX RANGE SCAN UIPEGAEXPGRPASSOC (object id 49019)
26016 TABLE ACCESS BY INDEX ROWID EXPRNCGRP_BLLGRP_ASSOC
520320 INDEX RANGE SCAN IEXPBLLGRPASSOC (object id 48943)
26016 INDEX RANGE SCAN UIPLANEMPGRPASSOC (object id 49021)
1 SORT AGGREGATE
54 TABLE ACCESS BY INDEX ROWID BILL_GROUP_BILL
54 INDEX RANGE SCAN IBLLGRPBLL1 (object id 48535)

QA
==
Rows Row Source Operation
------- ---------------------------------------------------
126 SORT UNIQUE
126 UNION-ALL
0 MINUS
0 SORT UNIQUE
0 FILTER
0 NESTED LOOPS
0 MERGE JOIN CARTESIAN
0 MERGE JOIN CARTESIAN
0 TABLE ACCESS BY INDEX ROWID BILL_GROUP_BILL
21 NESTED LOOPS
20 MERGE JOIN CARTESIAN
20 NESTED LOOPS
1 NESTED LOOPS
1 INDEX UNIQUE SCAN UICASE (object id 57260)
1 TABLE ACCESS BY INDEX ROWID BILL_GROUP
1 INDEX UNIQUE SCAN UIBILLGROUP (object id 57253)
20 TABLE ACCESS BY INDEX ROWID EXPRNCGRP_BLLGRP_ASSOC
20 INDEX RANGE SCAN IEXPBLLGRPASSOC (object id 57214)
20 BUFFER SORT
1 TABLE ACCESS BY INDEX ROWID CASE_ADMIN_PROVISION
1 INDEX RANGE SCAN UICASEADMINPROV (object id 57262)
1 SORT AGGREGATE
1 TABLE ACCESS BY INDEX ROWID CASE_ADMIN_PROVISION
1 INDEX RANGE SCAN UICASEADMINPROV (object id 57262)
0 INDEX RANGE SCAN IBLLGRPBLL1 (object id 57250)
0 BUFFER SORT
0 TABLE ACCESS BY INDEX ROWID PLAN_EMP_GROUP_ASSOC
0 INDEX RANGE SCAN IPLANEMPGRPASSOCA1 (object id 57223)
0 BUFFER SORT
0 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
0 INDEX RANGE SCAN IPEGAEXPGRPASSOC (object id 57221)
0 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
0 INDEX UNIQUE SCAN UIPEGAEXPGRPASSOC (object id 57318)
0 SORT AGGREGATE
0 TABLE ACCESS BY INDEX ROWID PLAN_EMP_GROUP_ASSOC
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
0 INDEX RANGE SCAN UIPEGAEXPGRPASSOC (object id 57318)
0 TABLE ACCESS BY INDEX ROWID EXPRNCGRP_BLLGRP_ASSOC
0 INDEX RANGE SCAN IEXPBLLGRPASSOC (object id 57214)
0 INDEX RANGE SCAN UIPLANEMPGRPASSOC (object id 57320)
0 SORT AGGREGATE
0 TABLE ACCESS BY INDEX ROWID BILL_GROUP_BILL
0 INDEX RANGE SCAN IBLLGRPBLL1 (object id 57250)
126 SORT UNIQUE
756 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
883 NESTED LOOPS
126 MERGE JOIN CARTESIAN
126 NESTED LOOPS
2646 MERGE JOIN CARTESIAN
21 NESTED LOOPS
1 NESTED LOOPS
1 INDEX UNIQUE SCAN UICASE (object id 57260)
1 TABLE ACCESS BY INDEX ROWID BILL_GROUP
1 INDEX UNIQUE SCAN UIBILLGROUP (object id 57253)
21 TABLE ACCESS BY INDEX ROWID EXPRNCGRP_BLLGRP_ASSOC
21 INDEX RANGE SCAN IEXPBLLGRPASSOC (object id 57214)
2646 BUFFER SORT
126 TABLE ACCESS BY INDEX ROWID PLAN_EMP_GROUP_ASSOC
126 INDEX RANGE SCAN IPLANEMPGRPASSOCA1 (object id 57223)
126 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
2646 INDEX UNIQUE SCAN UIPEGAEXPGRPASSOC (object id 57318)
546 SORT AGGREGATE
546 TABLE ACCESS BY INDEX ROWID PLAN_EMP_GROUP_ASSOC
1638 NESTED LOOPS
546 NESTED LOOPS
546 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
546 INDEX RANGE SCAN UIPEGAEXPGRPASSOC (object id 57318)
546 TABLE ACCESS BY INDEX ROWID EXPRNCGRP_BLLGRP_ASSOC
11466 INDEX RANGE SCAN IEXPBLLGRPASSOC (object id 57214)
546 INDEX RANGE SCAN UIPLANEMPGRPASSOC (object id 57320)
126 BUFFER SORT
1 TABLE ACCESS BY INDEX ROWID CASE_ADMIN_PROVISION
1 INDEX RANGE SCAN UICASEADMINPROV (object id 57262)
1 SORT AGGREGATE
1 TABLE ACCESS BY INDEX ROWID CASE_ADMIN_PROVISION
1 INDEX RANGE SCAN UICASEADMINPROV (object id 57262)
756 INDEX RANGE SCAN IPEGAEXPGRPASSOC (object id 57221)
126 MINUS
126 SORT UNIQUE
756 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
883 NESTED LOOPS
126 MERGE JOIN CARTESIAN
126 NESTED LOOPS
2646 MERGE JOIN CARTESIAN
21 NESTED LOOPS
1 NESTED LOOPS
1 INDEX UNIQUE SCAN UICASE (object id 57260)
1 TABLE ACCESS BY INDEX ROWID BILL_GROUP
1 INDEX UNIQUE SCAN UIBILLGROUP (object id 57253)
21 TABLE ACCESS BY INDEX ROWID EXPRNCGRP_BLLGRP_ASSOC
21 INDEX RANGE SCAN IEXPBLLGRPASSOC (object id 57214)
2646 BUFFER SORT
126 TABLE ACCESS BY INDEX ROWID PLAN_EMP_GROUP_ASSOC
126 INDEX RANGE SCAN IPLANEMPGRPASSOCA1 (object id 57223)
126 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
2646 INDEX UNIQUE SCAN UIPEGAEXPGRPASSOC (object id 57318)
546 SORT AGGREGATE
546 TABLE ACCESS BY INDEX ROWID PLAN_EMP_GROUP_ASSOC
1638 NESTED LOOPS
546 NESTED LOOPS
546 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
546 INDEX RANGE SCAN UIPEGAEXPGRPASSOC (object id 57318)
546 TABLE ACCESS BY INDEX ROWID EXPRNCGRP_BLLGRP_ASSOC
11466 INDEX RANGE SCAN IEXPBLLGRPASSOC (object id 57214)
546 INDEX RANGE SCAN UIPLANEMPGRPASSOC (object id 57320)
126 BUFFER SORT
1 TABLE ACCESS BY INDEX ROWID CASE_ADMIN_PROVISION
1 INDEX RANGE SCAN UICASEADMINPROV (object id 57262)
1 SORT AGGREGATE
1 TABLE ACCESS BY INDEX ROWID CASE_ADMIN_PROVISION
1 INDEX RANGE SCAN UICASEADMINPROV (object id 57262)
756 INDEX RANGE SCAN IPEGAEXPGRPASSOC (object id 57221)
0 SORT UNIQUE
0 FILTER
0 NESTED LOOPS
0 MERGE JOIN CARTESIAN
0 MERGE JOIN CARTESIAN
0 TABLE ACCESS BY INDEX ROWID BILL_GROUP_BILL
21 NESTED LOOPS
20 MERGE JOIN CARTESIAN
20 NESTED LOOPS
1 NESTED LOOPS
1 INDEX UNIQUE SCAN UICASE (object id 57260)
1 TABLE ACCESS BY INDEX ROWID BILL_GROUP
1 INDEX UNIQUE SCAN UIBILLGROUP (object id 57253)
20 TABLE ACCESS BY INDEX ROWID EXPRNCGRP_BLLGRP_ASSOC
20 INDEX RANGE SCAN IEXPBLLGRPASSOC (object id 57214)
20 BUFFER SORT
1 TABLE ACCESS BY INDEX ROWID CASE_ADMIN_PROVISION
1 INDEX RANGE SCAN UICASEADMINPROV (object id 57262)
1 SORT AGGREGATE
1 TABLE ACCESS BY INDEX ROWID CASE_ADMIN_PROVISION
1 INDEX RANGE SCAN UICASEADMINPROV (object id 57262)
0 INDEX RANGE SCAN IBLLGRPBLL1 (object id 57250)
0 BUFFER SORT
0 TABLE ACCESS BY INDEX ROWID PLAN_EMP_GROUP_ASSOC
0 INDEX RANGE SCAN IPLANEMPGRPASSOCA1 (object id 57223)
0 BUFFER SORT
0 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
0 INDEX RANGE SCAN IPEGAEXPGRPASSOC (object id 57221)
0 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
0 INDEX UNIQUE SCAN UIPEGAEXPGRPASSOC (object id 57318)
0 SORT AGGREGATE
0 TABLE ACCESS BY INDEX ROWID PLAN_EMP_GROUP_ASSOC
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
0 INDEX RANGE SCAN UIPEGAEXPGRPASSOC (object id 57318)
0 TABLE ACCESS BY INDEX ROWID EXPRNCGRP_BLLGRP_ASSOC
0 INDEX RANGE SCAN IEXPBLLGRPASSOC (object id 57214)
0 INDEX RANGE SCAN UIPLANEMPGRPASSOC (object id 57320)
0 SORT AGGREGATE
0 TABLE ACCESS BY INDEX ROWID BILL_GROUP_BILL
0 INDEX RANGE SCAN IBLLGRPBLL1 (object id 57250)

Please help me

Re: Different explain plan in a similar DBs [message #212560 is a reply to message #212548] Fri, 05 January 2007 17:25 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
Are statistics gathered in the same way on both DB's?
(and are they current?)

Best Regards,
Martijn
Re: Different explain plan in a similar DBs [message #212822 is a reply to message #212548] Mon, 08 January 2007 08:10 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
This is impossible to read.
Previous Topic: Open.Recordset problems
Next Topic: Error
Goto Forum:
  


Current Time: Fri Dec 02 16:21:37 CST 2016

Total time taken to generate the page: 0.25112 seconds