RE: Why the huge unique sort?

From: Allen, Brandon <Brandon.Allen_at_OneNeck.com>
Date: Tue, 15 Aug 2006 17:12:18 -0700
Message-ID: <04DDF147ED3A0D42B48A48A18D574C45059E1CBE@NT15.oneneck.corp>

Sorry for the delayed response - I was out on paternity leave for a couple weeks.

It looks like you were right Amit, I added a USE_HASH hint on that view and it's a huge improvement as you can see below.

Thanks!
Brandon

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse 1 0.00 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 30 406.68 488.58 34479 117222 399 2855
------- ------ -------- ---------- ---------- ---------- ----------

total 32 406.68 488.60 34479 117222 399 2855

Rows Row Source Operation
------- ---------------------------------------------------

   2855 SORT ORDER BY
   2855 HASH JOIN
  29795 NESTED LOOPS

    144     TABLE ACCESS FULL CT_ADDR_CODE
  29795     TABLE ACCESS BY INDEX ROWID OE_LINE
  29938      INDEX RANGE SCAN (object id 9532)
  22763    VIEW HM_PROJECT_COST_INVOICE
  22763     SORT UNIQUE
  22763      UNION-ALL
   9812       SORT GROUP BY
  11621        NESTED LOOPS
  11622         NESTED LOOPS
  11622          NESTED LOOPS OUTER
  11622           TABLE ACCESS FULL PA_HISTORY
  11621           TABLE ACCESS BY INDEX ROWID OE_HDR
  23242            INDEX UNIQUE SCAN (object id 9518)
  23242          TABLE ACCESS BY INDEX ROWID PA_JOB
  23242           INDEX UNIQUE SCAN (object id 9667)
  11621         TABLE ACCESS BY INDEX ROWID PA_PROJECT_MASTER
  23242          INDEX UNIQUE SCAN (object id 9682)
  12951       SORT GROUP BY
  12954        HASH JOIN
      1         TABLE ACCESS FULL OE_CONTROL
  13392         MERGE JOIN OUTER
  13393          SORT JOIN
  13392           NESTED LOOPS
   2052            TABLE ACCESS BY INDEX ROWID AR_DOC_HDR
   2052             INDEX RANGE SCAN (object id 8895)
  13392            TABLE ACCESS BY INDEX ROWID AR_DOC_LINE
  15443             INDEX RANGE SCAN (object id 8898)
  13392          SORT JOIN
  37684           TABLE ACCESS FULL OE_HDR

-----Original Message-----

From: amit poddar [mailto:amit.poddar_at_yale.edu] Sent: Tuesday, July 25, 2006 5:52 PM
To: Allen, Brandon
Cc: oracle-l_at_freelists.org
Subject: Re: Why the huge unique sort?

So it looks like the view hm_project_cost_invoice is being instantiated 28977 times inside the nested loop marked as bold.

It would perform better if the Nested loops marked was an hash join

Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

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

Received on Tue Aug 15 2006 - 19:12:18 CDT