Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from puck1183.startdedicated.com (localhost [127.0.0.1]) by puck1183.startdedicated.com (Postfix) with ESMTP id DCB131961015 for ; Tue, 11 Nov 2014 17:06:08 +0100 (CET) Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by puck1183.startdedicated.com (Postfix) with ESMTP for ; Tue, 11 Nov 2014 17:06:08 +0100 (CET) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4926333225; Tue, 11 Nov 2014 11:06:07 -0500 (EST) X-Virus-Scanned: Debian amavisd-new at turing.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 6bjmtD0ZHS3U; Tue, 11 Nov 2014 11:06:07 -0500 (EST) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7784C331CD; Tue, 11 Nov 2014 11:05:52 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 11 Nov 2014 11:04:30 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 633FF33128 for ; Tue, 11 Nov 2014 11:04:30 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id gOfnTAbSUp7n for ; Tue, 11 Nov 2014 11:04:30 -0500 (EST) Received: from mail1.bemta7.messagelabs.com (mail1.bemta7.messagelabs.com [216.82.254.101]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D55A233122 for ; Tue, 11 Nov 2014 11:04:10 -0500 (EST) Received: from [216.82.253.67] by server-5.bemta-7.messagelabs.com id 9A/3E-03132-97332645; Tue, 11 Nov 2014 16:04:09 +0000 X-Env-Sender: Abdul.Ebadi@Level3.com X-Msg-Ref: server-12.tower-158.messagelabs.com!1415721849!9586025!1 X-Originating-IP: [209.245.18.38] X-StarScan-Received: X-StarScan-Version: 6.12.4; banners=-,-,- X-VirusChecked: Checked Received: (qmail 2520 invoked from network); 11 Nov 2014 16:04:09 -0000 Received: from unknown.level3.net (HELO messagelabs2.level3.com) (209.245.18.38) by server-12.tower-158.messagelabs.com with DHE-RSA-AES256-SHA encrypted SMTP; 11 Nov 2014 16:04:09 -0000 Received: from USIDCWVEHT01.corp.global.level3.com (usidcwveht01.corp.global.level3.com [10.1.142.31]) (using TLSv1 with cipher RC4-SHA (128/128 bits)) (Client CN "USIDCWVEHT01.corp.global.level3.com", Issuer "VIDCCERT0001" (not verified)) by messagelabs2.level3.com (Postfix) with ESMTPS id 298802A764; Tue, 11 Nov 2014 16:04:18 +0000 (GMT) Received: from USADCWVEHT01.corp.global.level3.com (10.2.36.141) by USIDCWVEHT01.corp.global.level3.com (10.1.142.31) with Microsoft SMTP Server (TLS) id 14.3.195.1; Tue, 11 Nov 2014 09:04:08 -0700 Received: from USIDCWVEMBX09.corp.global.level3.com ([fe80::f03c:98e9:cfda:4d48]) by usadcwveht01.corp.global.level3.com ([::1]) with mapi id 14.03.0158.001; Tue, 11 Nov 2014 11:04:07 -0500 From: "Ebadi, Abdul" To: Iggy Fernandez , ORACLE-L Subject: RE: Exadata Tuning Question+ Thread-Topic: Exadata Tuning Question+ Date: Tue, 11 Nov 2014 16:04:06 +0000 Message-ID: <2B2D8E96233804478FA849965DF3B2C1B8FF5AF8@USIDCWVEMBX09.corp.global.level3.com> References: <545D3B5B.5060702@evdbt.com>,<2B2D8E96233804478FA849965DF3B2C1B8FEFCBD@USIDCWVEMBX09.corp.global.level3.com>,,,<2B2D8E96233804478FA849965DF3B2C1B8FF4CA2@USIDCWVEMBX09.corp.global.level3.com>, In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [10.1.196.207] Content-Type: multipart/alternative; boundary="_000_2B2D8E96233804478FA849965DF3B2C1B8FF5AF8USIDCWVEMBX09co_" MIME-Version: 1.0 X-archive-position: 57463 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: Abdul.Ebadi@Level3.com Precedence: normal Reply-To: Abdul.Ebadi@Level3.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l --_000_2B2D8E96233804478FA849965DF3B2C1B8FF5AF8USIDCWVEMBX09co_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable First one: ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------------------------------------- Plan hash value: 395663814 ---------------------------------------------------------------------------= ---------------------------------- | Id | Operation | Name | Rows | Bytes= |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------= ---------------------------------- | 0 | SELECT STATEMENT | | 231M| 9= G| | 2777K (1)| 00:01:49 | | 1 | SORT GROUP BY | | 231M| 9= G| 12G| 2777K (1)| 00:01:49 | |* 2 | HASH JOIN OUTER | | 231M| 9= G| 251M| 94000 (3)| 00:00:04 | |* 3 | HASH JOIN RIGHT OUTER | | 7331K| 167= M| 54M| 40013 (3)| 00:00:02 | | 4 | TABLE ACCESS STORAGE FULL| SERVICE_LOCATION | 3175K| 18= M| | 5150 (1)| 00:00:01 | | 5 | TABLE ACCESS STORAGE FULL| SERVICE_LOOKUP | 7331K| 125= M| | 21669 (4)| 00:00:01 | PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------------------------------------- |* 6 | TABLE ACCESS STORAGE FULL | SERVICE_RELATIONSHIP | 21M| 454= M| | 5421 (8)| 00:00:01 | Second one: Plan hash value: 3504593807 ---------------------------------------------------------------------------= ---------------------------------- | Id | Operation | Name | Rows | Bytes= |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------= ---------------------------------- | 0 | SELECT STATEMENT | | 7331K| 69= G| | 194K (2)| 00:00:08 | |* 1 | HASH JOIN OUTER | | 7331K| 69= G| 251M| 194K (2)| 00:00:08 | |* 2 | HASH JOIN RIGHT OUTER | | 7331K| 167= M| 54M| 40013 (3)| 00:00:02 | | 3 | TABLE ACCESS STORAGE FULL | SERVICE_LOCATION | 3175K| 18= M| | 5150 (1)| 00:00:01 | | 4 | TABLE ACCESS STORAGE FULL | SERVICE_LOOKUP | 7331K| 125= M| | 21669 (4)| 00:00:01 | | 5 | VIEW | | 176K| 1695= M| | 57660 (3)| 00:00:03 | PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------------------------------------- | 6 | SORT GROUP BY | | 176K| 3781= K| 663M| 57660 (3)| 00:00:03 | |* 7 | TABLE ACCESS STORAGE FULL| SERVICE_RELATIONSHIP | 21M| 454= M| | 5421 (8)| 00:00:01 | ---------------------------------------------------------------------------= ---------------------------------- Thanks, Abdul From: Iggy Fernandez [mailto:iggy_fernandez@hotmail.com] Sent: Tuesday, November 11, 2014 5:29 AM To: Ebadi, Abdul; ORACLE-L Subject: RE: Exadata Tuning Question+ Reposting since my reply was not posted Abdul, I cannot understand why the number of rows exploded from 21 million to 206 = million in line 2. Surely, the number of rows cannot be more 21M because SI= D is (or appears to be) the primary key of SERVICE_LOOKUP and has a foreign= key relationship to with SERVICE_RELATIONSHIP. Perhaps, primary key and fo= reign key constraints are not being enforced. I need to review the join con= ditions in the predicate section of the query plan. Because of the rows exp= losion, the required sort area size is 10G. You can try increasing the sort= _area_size to 10G. A better strategy might be to perform the group by befor= e the join using the WITH CLAUSE or the PLACE_GROUP_BY hint. Could you post the following two EXPLAIN PLAN (including the predicate sect= ions) EXPLAIN PLAN FOR SELECT slv.sid, slv.service_location_id, RTRIM(XMLSERIALIZE(CONTENT EXTRACT( XMLAGG(XMLELEMENT("e", DECODE(sr.rela= tionship_level, '1', sr.RELATED_SID || ',', NULL)) ORDER BY sr.RELATED_SID)= , '//text()' )) , ',' ) AS relatedserviceinstanceidlevel1, RTRIM(XMLSERIALIZE(CONTENT EXTRACT( XMLAGG(XMLELEMENT("e", DECODE(sr.rela= tionship_level, '2', sr.RELATED_SID || ',', NULL)) ORDER BY sr.RELATED_SID)= , '//text()' )) , ',' ) AS relatedserviceinstanceidlevel1, RTRIM(XMLSERIALIZE(CONTENT EXTRACT( XMLAGG(XMLELEMENT("e", DECODE(sr.rela= tionship_level, '3', sr.RELATED_SID || ',', NULL)) ORDER BY sr.RELATED_SID)= , '//text()' )) , ',' ) AS relatedserviceinstanceidlevel1, RTRIM(XMLSERIALIZE(CONTENT EXTRACT( XMLAGG(XMLELEMENT("e", DECODE(sr.rela= tionship_level, '4', sr.RELATED_SID || ',', NULL)) ORDER BY sr.RELATED_SID)= , '//text()' )) , ',' ) AS relatedserviceinstanceidlevel1, RTRIM(XMLSERIALIZE(CONTENT EXTRACT( XMLAGG(XMLELEMENT("e", DECODE(sr.rela= tionship_level, '5', sr.RELATED_SID || ',', NULL)) ORDER BY sr.RELATED_SID)= , '//text()' )) , ',' ) AS relatedserviceinstanceidlevel1 FROM service_lookup slv LEFT OUTER JOIN service_location sl ON sl.service_location_id =3D slv.ser= vice_location_id LEFT OUTER JOIN service_relationship sr ON sr.sid =3D slv.sid AND sr.rela= tionship_level IN ('1', '2', '3', '4', '5') GROUP BY slv.id, slv.service_location_id; EXPLAIN PLAN FOR WITH sr AS ( SELECT sr.id, LISTAGG(DECODE(sr.relationship_level, '1', sr.related_sid, NULL) ',') W= ITHIN GROUP (ORDER BY sr.related_sid) AS relatedserviceinstanceidlevel1, LISTAGG(DECODE(sr.relationship_level, '2', sr.related_sid, NULL) ',') W= ITHIN GROUP (ORDER BY sr.related_sid) AS relatedserviceinstanceidlevel2, LISTAGG(DECODE(sr.relationship_level, '3', sr.related_sid, NULL) ',') W= ITHIN GROUP (ORDER BY sr.related_sid) AS relatedserviceinstanceidlevel3, LISTAGG(DECODE(sr.relationship_level, '4', sr.related_sid, NULL) ',') W= ITHIN GROUP (ORDER BY sr.related_sid) AS relatedserviceinstanceidlevel4, LISTAGG(DECODE(sr.relationship_level, '5', sr.related_sid, NULL) ',') W= ITHIN GROUP (ORDER BY sr.related_sid) AS relatedserviceinstanceidlevel5 WHERE sr.relationship_level IN ('1', '2', '3', '4', '5') GROUP BY sr.id ) SELECT slv.sid, slv.service_location_id, sr.relatedserviceinstanceidlevel1, sr.relatedserviceinstanceidlevel2, sr.relatedserviceinstanceidlevel3, sr.relatedserviceinstanceidlevel4, sr.relatedserviceinstanceidlevel5 FROM service_lookup slv LEFT OUTER JOIN service_location sl ON sl.service_location_id =3D slv.ser= vice_location_id LEFT OUTER JOIN sr ON sr.sid =3D slv.sid; From: Abdul.Ebadi@Level3.com Thanks for the reply Iggy... Your second suggestion below runs faster, but according to our developer we= "cannot use LISTAGG as the string is larger than 4000 chars for some of th= e values, so will have to use XMLAGG" The first suggestion is OK and missing a group by, but when we added group = by it didn't finish even after long time. Here is the plan for your second option (even switching to SORT MERGE join = resulted in same thing - not finished): Plan hash value: 4110902776 ---------------------------------------------------------------------------= ---------------------------------- | Id | Operation | Name | Rows | Bytes= |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------= ---------------------------------- | 0 | SELECT STATEMENT | | 206M| 9067= M| | 151K (1)| 00:00:06 | | 1 | SORT GROUP BY | | 206M| 9067= M| 10G| 151K (1)| 00:00:06 | |* 2 | HASH JOIN OUTER | | 206M| 9067= M| | 2311 (7)| 00:00:01 | |* 3 | HASH JOIN | | 6539K| 149= M| | 1864 (4)| 00:00:01 | | 4 | TABLE ACCESS STORAGE FULL| SERVICE_LOCATION | 3175K| 18= M| | 357 (1)| 00:00:01 | |* 5 | TABLE ACCESS STORAGE FULL| SERVICE_LOOKUP | 6539K| 112= M| | 1503 (4)| 00:00:01 | PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------------------------------------- |* 6 | TABLE ACCESS STORAGE FULL | SERVICE_RELATIONSHIP | 21M| 454= M| | 376 (8)| 00:00:01 | ---------------------------------------------------------------------------= ---------------------------------- Thanks, Abdul --_000_2B2D8E96233804478FA849965DF3B2C1B8FF5AF8USIDCWVEMBX09co_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

First one:

 

-------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------

Plan hash value: 395663814=

 

-------------------------------------= ------------------------------------------------------------------------

| Id  | Operation  &nb= sp;            =      | Name       &n= bsp;         | Rows  | Bytes |= TempSpc| Cost (%CPU)| Time     |

-------------------------------------= ------------------------------------------------------------------------

|   0 | SELECT STATEMENT&nb= sp;            |&nbs= p;            &= nbsp;        |   231M| &n= bsp;   9G|       |  2777K = ; (1)| 00:01:49 |

|   1 |  SORT GROUP BY=             &nb= sp;  |          &nbs= p;           |  = ; 231M|     9G|    12G|  2777K = ; (1)| 00:01:49 |

|*  2 |   HASH JOIN OU= TER            |&nbs= p;            &= nbsp;        |   231M| &n= bsp;   9G|   251M| 94000   (3)| 00:00:04 |

|*  3 |    HASH J= OIN RIGHT OUTER     |     &nbs= p;            &= nbsp;   |  7331K|   167M|    54M| 4= 0013   (3)| 00:00:02 |

|   4 |   &n= bsp; TABLE ACCESS STORAGE FULL| SERVICE_LOCATION     |&= nbsp; 3175K|    18M|       |&n= bsp; 5150   (1)| 00:00:01 |

|   5 |   &n= bsp; TABLE ACCESS STORAGE FULL| SERVICE_LOOKUP     = ;  |  7331K|   125M|      = ; | 21669   (4)| 00:00:01 |

 

PLAN_TABLE_OUTPUT

-------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------

|*&= nbsp; 6 |    TABLE ACCESS STORAGE FULL | SERVICE_RELATIONSHI= P |    21M|   454M|     &= nbsp; |  5421   (8)| 00:00:01 |

 

 

Second one:

Plan hash value: 3504593807

 

-------------------------------------= ------------------------------------------------------------------------

| Id  | Operation  &nb= sp;            =      | Name       &n= bsp;         | Rows  | Bytes |= TempSpc| Cost (%CPU)| Time     |

-------------------------------------= ------------------------------------------------------------------------

|   0 | SELECT STATEMENT&nb= sp;            |&nbs= p;            &= nbsp;        |  7331K|  &= nbsp; 69G|       |   194K  (2)= | 00:00:08 |

|*  1 |  HASH JOIN OUTER&nb= sp;            |&nbs= p;             =         |  7331K|  &= nbsp; 69G|   251M|   194K  (2)| 00:00:08 |

|*  2 |   HASH JOIN RI= GHT OUTER      |     &nbs= p;            &= nbsp;   |  7331K|   167M|    54M| 4= 0013   (3)| 00:00:02 |

|   3 |    T= ABLE ACCESS STORAGE FULL | SERVICE_LOCATION     | = 3175K|    18M|       |  = 5150   (1)| 00:00:01 |

|   4 |    T= ABLE ACCESS STORAGE FULL | SERVICE_LOOKUP     &nbs= p; |  7331K|   125M|       | 2= 1669   (4)| 00:00:01 |

|   5 |   VIEW&nb= sp;            =           |   &= nbsp;           &nbs= p;      |   176K|  1695M| &nbs= p;     | 57660   (3)| 00:00:03 |

 

PLAN_TABLE_OUTPUT

-------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------

|   6 |    S= ORT GROUP BY          &nb= sp;  |           &nb= sp;          |  = ; 176K|  3781K|   663M| 57660   (3)| 00:00:03 |

|*  7 |     = TABLE ACCESS STORAGE FULL| SERVICE_RELATIONSHIP |    21M|&nb= sp;  454M|       |  5421  = ; (8)| 00:00:01 |

-------------------------------------= ------------------------------------------------------------------------

 

Thanks,

Abdul

From: Iggy Fernandez [mailto:iggy_fe= rnandez@hotmail.com]
Sent: Tuesday, November 11, 2014 5:29 AM
To: Ebadi, Abdul; ORACLE-L
Subject: RE: Exadata Tuning Question+

 

Reposting since my reply was not posted

 

Abdul,

 

I cannot understand why the number of rows exploded from 21 million = to 206 million in line 2. Surely, the number of rows cannot be more 21M bec= ause SID is (or appears to be) the primary key of SERVICE_LOOKUP and has a foreign key relationship to with SERVICE_RELAT= IONSHIP. Perhaps, primary key and foreign key constraints are not bein= g enforced. I need to review the join conditions in the predicate sect= ion of the query plan. Because of the rows explosion, the required sort area size is 10G. You can try increasing the = sort_area_size to 10G. A better strategy might be to perform the group= by before the join using the WITH CLAUSE or the PLACE_GROUP_BY hint.<= /o:p>

 

Could you post the following two EXPLAIN PLAN (including the predica= te sections)

 

EXPLAIN PLAN FOR

SELECT

  slv.sid,

  slv.service_location_id,

  RTRIM(XMLSERIALIZE(CONTENT EXTRACT( XMLAGG(XMLELEMENT("e= ", DECODE(sr.relationship_level, '1', sr.RELATED_SID || ',', NULL)) OR= DER BY sr.RELATED_SID), '//text()' )) , ',' ) AS relatedserviceinstanceidle= vel1,

  RTRIM(XMLSERIALIZE(CONTENT EXTRACT( XMLAGG(XMLELEMENT("e= ", DECODE(sr.relationship_level, '2', sr.RELATED_SID || ',', NULL)) OR= DER BY sr.RELATED_SID), '//text()' )) , ',' ) AS relatedserviceinstanceidle= vel1,

  RTRIM(XMLSERIALIZE(CONTENT EXTRACT( XMLAGG(XMLELEMENT("e= ", DECODE(sr.relationship_level, '3', sr.RELATED_SID || ',', NULL)) OR= DER BY sr.RELATED_SID), '//text()' )) , ',' ) AS relatedserviceinstanceidle= vel1,

  RTRIM(XMLSERIALIZE(CONTENT EXTRACT( XMLAGG(XMLELEMENT("e= ", DECODE(sr.relationship_level, '4', sr.RELATED_SID || ',', NULL)) OR= DER BY sr.RELATED_SID), '//text()' )) , ',' ) AS relatedserviceinstanceidle= vel1,

  RTRIM(XMLSERIALIZE(CONTENT EXTRACT( XMLAGG(XMLELEMENT("e= ", DECODE(sr.relationship_level, '5', sr.RELATED_SID || ',', NULL)) OR= DER BY sr.RELATED_SID), '//text()' )) , ',' ) AS relatedserviceinstanceidle= vel1

FROM

  service_lookup slv

  LEFT OUTER JOIN service_location sl ON sl.service_location_id= =3D slv.service_location_id

  LEFT OUTER JOIN service_relationship sr ON sr.sid =3D slv.sid= AND sr.relationship_level IN ('1', '2', '3', '4', '5')

GROUP BY slv.id, slv.service_location_id;

 

EXPLAIN PLAN FOR

WITH sr AS (

  SELECT

    sr.id,

    LISTAGG(DECODE(sr.relationship_level, '1', sr.related_= sid, NULL) ',') WITHIN GROUP (ORDER BY sr.related_sid) AS relatedserviceins= tanceidlevel1,

    LISTAGG(DECODE(sr.relationship_level, '2', sr.related_= sid, NULL) ',') WITHIN GROUP (ORDER BY sr.related_sid) AS relatedserviceins= tanceidlevel2,

    LISTAGG(DECODE(sr.relationship_level, '3', sr.related_= sid, NULL) ',') WITHIN GROUP (ORDER BY sr.related_sid) AS relatedserviceins= tanceidlevel3,

    LISTAGG(DECODE(sr.relationship_level, '4', sr.related_= sid, NULL) ',') WITHIN GROUP (ORDER BY sr.related_sid) AS relatedserviceins= tanceidlevel4,

    LISTAGG(DECODE(sr.relationship_level, '5', sr.related_= sid, NULL) ',') WITHIN GROUP (ORDER BY sr.related_sid) AS relatedserviceins= tanceidlevel5

  WHERE sr.relationship_level IN ('1', '2', '3', '4', '5')=

  GROUP BY sr.id

)

SELECT

  slv.sid,

  slv.service_location_id,

  sr.relatedserviceinstanceidlevel1,

  sr.relatedserviceinstanceidlevel2,

  sr.relatedserviceinstanceidlevel3,

  sr.relatedserviceinstanceidlevel4,

  sr.relatedserviceinstanceidlevel5

FROM

  service_lookup slv

  LEFT OUTER JOIN service_location sl ON sl.service_location_id= =3D slv.service_location_id

  LEFT OUTER JOIN sr ON sr.sid =3D slv.sid;

 

 

From: Abdul.Ebadi@Level3.com

Thanks for the reply Iggy…

 

Your second suggestion below runs fas= ter, but according to our developer we “cannot use LISTAGG as the string is larger than 4000 chars for some of the values, so will ha= ve to use XMLAGG”

The first suggestion is OK and missing a group by, but= when we added group by it didn’t finish even after long time.=

 

Here is the plan for your second option (even switchin= g to SORT MERGE join resulted in same thing – not finished):=

 

Plan hash value: 4110902776

 

------------------------------------------------------= -------------------------------------------------------

| Id  | Operation     &n= bsp;            = ;  | Name          &= nbsp;      | Rows  | Bytes |TempSpc| Cost (%C= PU)| Time     |

------------------------------------------------------= -------------------------------------------------------

|   0 | SELECT STATEMENT   &n= bsp;         |   &nb= sp;             = ;     |   206M|  9067M|  =      |   151K  (1)| 00:00:06 |<= /p>

|   1 |  SORT GROUP BY   = ;            | =             &nb= sp;        |   206M|  906= 7M|    10G|   151K  (1)| 00:00:06 |<= /p>

|*  2 |   HASH JOIN OUTER  &n= bsp;         |   &nb= sp;            =       |   206M|  9067M|  =      |  2311   (7)| 00:00:01 |<= /p>

|*  3 |    HASH JOIN  &n= bsp;            = ;  |           =            |  6539K|=    149M|       |  1864 &n= bsp; (4)| 00:00:01 |

|   4 |     TABLE ACCESS= STORAGE FULL| SERVICE_LOCATION     |  3175K| = ;   18M|       |   357&nb= sp;  (1)| 00:00:01 |

|*  5 |     TABLE ACCESS STOR= AGE FULL| SERVICE_LOOKUP       |  6539K|=    112M|       |  1503 &n= bsp; (4)| 00:00:01 |

 

PLAN_TABLE_OUTPUT

------------------------------------------------------= ---------------------------------------------------------------------------= -----------------------------------------------------------------------

|*  6 |    TABLE ACCESS STORAGE FU= LL | SERVICE_RELATIONSHIP |    21M|   454M| &= nbsp;     |   376   (8)| 00:00:01 |=

------------------------------------------------------= -------------------------------------------------------

 

Thanks,

Abdul

 

--_000_2B2D8E96233804478FA849965DF3B2C1B8FF5AF8USIDCWVEMBX09co_-- -- http://www.freelists.org/webpage/oracle-l