Home -> Mailing Lists -> Oracle-L -> RE: Exadata Tuning Question+

RE: Exadata Tuning Question+

From: Ebadi, Abdul <Abdul.Ebadi_at_Level3.com>
Date: Tue, 11 Nov 2014 16:04:06 +0000
Message-ID: <2B2D8E96233804478FA849965DF3B2C1B8FF5AF8_at_USIDCWVEMBX09.corp.global.level3.com> 





First one:





Plan hash value: 395663814





| Id  | Operation                    | Name                 | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |





|   0 | SELECT STATEMENT             |                      |   231M|     9G|       |  2777K  (1)| 00:01:49 |


|   1 |  SORT GROUP BY               |                      |   231M|     9G|    12G|  2777K  (1)| 00:01:49 |



|*  2 |   HASH JOIN OUTER            |                      |   231M|     9G|   251M| 94000   (3)| 00:00:04 |
|*  3 |    HASH JOIN RIGHT OUTER     |                      |  7331K|   167M|    54M| 40013   (3)| 00:00:02 |


|   4 |     TABLE ACCESS STORAGE FULL| SERVICE_LOCATION     |  3175K|    18M|       |  5150   (1)| 00:00:01 |

|   5 |     TABLE ACCESS STORAGE FULL| SERVICE_LOOKUP       |  7331K|   125M|       | 21669   (4)| 00:00:01 |






PLAN_TABLE_OUTPUT





|*  6 |    TABLE ACCESS STORAGE FULL | SERVICE_RELATIONSHIP |    21M|   454M|       |  5421   (8)| 00:00:01 |



Second one:


Plan hash value: 3504593807





| Id  | Operation                    | Name                 | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |





|   0 | SELECT STATEMENT             |                      |  7331K|    69G|       |   194K  (2)| 00:00:08 |



|*  1 |  HASH JOIN OUTER             |                      |  7331K|    69G|   251M|   194K  (2)| 00:00:08 |
|*  2 |   HASH JOIN RIGHT OUTER      |                      |  7331K|   167M|    54M| 40013   (3)| 00:00:02 |


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

|   4 |    TABLE ACCESS STORAGE FULL | SERVICE_LOOKUP       |  7331K|   125M|       | 21669   (4)| 00:00:01 |

|   5 |   VIEW                       |                      |   176K|  1695M|       | 57660   (3)| 00:00:03 |






PLAN_TABLE_OUTPUT





|   6 |    SORT GROUP BY             |                      |   176K|  3781K|   663M| 57660   (3)| 00:00:03 |


|*  7 |     TABLE ACCESS STORAGE FULL| SERVICE_RELATIONSHIP |    21M|   454M|       |  5421   (8)| 00:00:01 |






Thanks,


Abdul


From: Iggy Fernandez [mailto:iggy_fernandez_at_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 SID 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 foreign key constraints are not being enforced. I need to review the join conditions in the predicate section 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.



Could you post the following two EXPLAIN PLAN (including the predicate 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)) ORDER BY sr.RELATED_SID), '//text()' )) , ',' ) AS relatedserviceinstanceidlevel1,
  RTRIM(XMLSERIALIZE(CONTENT EXTRACT( XMLAGG(XMLELEMENT("e", DECODE(sr.relationship_level, '2', sr.RELATED_SID || ',', NULL)) ORDER BY sr.RELATED_SID), '//text()' )) , ',' ) AS relatedserviceinstanceidlevel1,
  RTRIM(XMLSERIALIZE(CONTENT EXTRACT( XMLAGG(XMLELEMENT("e", DECODE(sr.relationship_level, '3', sr.RELATED_SID || ',', NULL)) ORDER BY sr.RELATED_SID), '//text()' )) , ',' ) AS relatedserviceinstanceidlevel1,
  RTRIM(XMLSERIALIZE(CONTENT EXTRACT( XMLAGG(XMLELEMENT("e", DECODE(sr.relationship_level, '4', sr.RELATED_SID || ',', NULL)) ORDER BY sr.RELATED_SID), '//text()' )) , ',' ) AS relatedserviceinstanceidlevel1,
  RTRIM(XMLSERIALIZE(CONTENT EXTRACT( XMLAGG(XMLELEMENT("e", DECODE(sr.relationship_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 = slv.service_location_id
  LEFT OUTER JOIN service_relationship sr ON sr.sid = 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 relatedserviceinstanceidlevel1,
    LISTAGG(DECODE(sr.relationship_level, '2', sr.related_sid, NULL) ',') WITHIN GROUP (ORDER BY sr.related_sid) AS relatedserviceinstanceidlevel2,
    LISTAGG(DECODE(sr.relationship_level, '3', sr.related_sid, NULL) ',') WITHIN GROUP (ORDER BY sr.related_sid) AS relatedserviceinstanceidlevel3,
    LISTAGG(DECODE(sr.relationship_level, '4', sr.related_sid, NULL) ',') WITHIN GROUP (ORDER BY sr.related_sid) AS relatedserviceinstanceidlevel4,
    LISTAGG(DECODE(sr.relationship_level, '5', sr.related_sid, NULL) ',') WITHIN 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 = slv.service_location_id
  LEFT OUTER JOIN sr ON sr.sid = slv.sid;




From: Abdul.Ebadi_at_Level3.com<mailto:Abdul.Ebadi_at_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 the 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|  9067M|       |   151K  (1)| 00:00:06 |


|   1 |  SORT GROUP BY               |                      |   206M|  9067M|    10G|   151K  (1)| 00:00:06 |



|*  2 |   HASH JOIN OUTER            |                      |   206M|  9067M|       |  2311   (7)| 00:00:01 |
|*  3 |    HASH JOIN                 |                      |  6539K|   149M|       |  1864   (4)| 00:00:01 |


|   4 |     TABLE ACCESS STORAGE FULL| SERVICE_LOCATION     |  3175K|    18M|       |   357   (1)| 00:00:01 |

|*  5 |     TABLE ACCESS STORAGE FULL| SERVICE_LOOKUP       |  6539K|   112M|       |  1503   (4)| 00:00:01 |





PLAN_TABLE_OUTPUT





|*  6 |    TABLE ACCESS STORAGE FULL | SERVICE_RELATIONSHIP |    21M|   454M|       |   376   (8)| 00:00:01 |






Thanks,


Abdul



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


Received on Tue Nov 11 2014 - 17:04:06 CET












Original text of this message