OraFAQ Forum: Community Hangout » A Very Interesting query I came across today

Home » Other » Community Hangout » A Very Interesting query I came across today

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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.

Code: [Select all] [Show/ hide]

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

Re: A Very Interesting query I came across today [message #334742 is a reply to message #334678]

Thu, 17 July 2008 16:00

S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom

Senior Member

Frank,

Thanks for spending your time in formatting the explain plan output. I am still trying hard to get out of the shock after seeing this plan.

Regards

Raj

Report message to a moderator

Previous Topic:	I'm deficient!
Next Topic:	wiki

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 10 18:24:06 CDT 2025