RE: Why the huge unique sort?
- From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
- To: "amit poddar" <amit.poddar@xxxxxxxx>
- Date: Tue, 15 Aug 2006 17:12:18 -0700
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@xxxxxxxx]
Sent: Tuesday, July 25, 2006 5:52 PM
To: Allen, Brandon
Cc: oracle-l@xxxxxxxxxxxxx
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
