| A Very Interesting query I came across today [message #334678] |
Thu, 17 July 2008 11:26  |
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
|
Senior Member |
|
|
Hello All,
I would like to see your expert opinion on this explain plan. I came across this explain plan on one of the forum which I visit regularly. To be honest, till date I have never every seen such a plan. I am wondering whether anybody have ever dealt with such a huge data volume. I just want to keep myself updated and educated. It's not that I am trying to find fault, it's just for my own personal benefit I am requesting my co-fellow forum members to share their opinion on this.
Below URL will give more details about what the actual problem is.
http://forums.oracle.com/forums/thread.jspa?threadID=682172&tstart=0
This is the plan after reading it I am completely stumped after reading the first line of the explain plan.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost | Inst |IN-OUT|
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 52T| 6476T| | 6729M| | |
|* 1 | HASH JOIN | | 52T| 6476T| 8272K| 6729M| | |
| 2 | REMOTE | | 148K| 6524K| | 242K| VDCEW~ | R->S |
| 3 | VIEW | | 3526G| 291T| | 2279M| | |
| 4 | SORT GROUP BY | | 3526G| 375T| | 2279M| | |
|* 5 | HASH JOIN | | 3526G| 375T| 768M| 79970 | | |
| 6 | VIEW | | 7821K| 678M| | 2169 | | |
| 7 | UNION-ALL | | | | | | | |
| 8 | REMOTE | | 7821K| 634M| | 2167 | VDCEW~ | R->S |
| 9 | REMOTE | | 1 | 87 | | 2 | VDC1W~ | R->S |
| 10 | REMOTE | | 45M| 1118M| | 46942 | VDC1W~ | R->S |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("BILLING"."CASE_NUM"="A"."CASE_NUM" AND
"BILLING"."LOCATION_CODE"=TO_NUMBER("A"."LOCATION_CODE"))
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
5 - access("CASEC"."ACCOUNT_NO"="BILL"."ACCOUNT_NO")
Note: cpu costing is off
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for Solaris: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production
Regards
Raj
P.S : It's not well-formatted. Bear with me.
[Mod-Edit: Frank had nothing better to do, so formatted the plan-table output]
[Updated on: Thu, 17 July 2008 15:59] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
