RE: Why the monstrous SORT?
- From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 7 Jul 2005 11:37:07 -0700
Thank you all for the suggestions! I haven't solved the problem yet, but will
take some time today to try your suggestions and will let you know if I find a
solution. To address a few of the questions you all brought up:
Jonathan Lewis, re: row source operation - Yes, the Row Source Operation is in
agreement with the Execution Plan section (see bottom of this email)
Jared Still, I don't see how the merge cartesian would have any effect on the
large sort, it looks like the sort is only operating on the NL join of the
OE_CONTROL and AR_DOC_LINE tables.
Steve Rospo, the "Rows" values are coming from actual executions traced with
SQL_TRACE=true and then tkprofd, so these are the actual execution plans and
rows processed at the time of execution. Yes, when I run the sub query by
itself, it returns 236,457 rows w/ the GROUP BY and 237,040 w/o it.
Stephane Faroult, I'm not too concerned about the hard-coded customer number.
The application uses bind variables in most queries and has a good
parse/execute ratio, but in this case I think it might be a good thing that the
customer number is a literal, because the data is heavily skewed and I'm using
histograms to help Oracle choose the best execution plan, which brings up
another point:
This query executes very well when I run it for certain customers that only
have a *few* records, it runs very poorly for customers with a *moderate*
number of records, and it runs very well for customers with a *lot* of records,
but when it runs for those customers, it recognizes them as popular values and
changes the plan accordingly to use full table scans and hash joins instead of
nested loops - see below:
## Row Source Operation for customer_no=202 (this is a "popular" value w/ 3000+
records in oe_hdr)
## This query runs in about 2-3 minutes
## Notice this execution plan uses hash joins and FTS on OE_HDR & OE_LINE
Rows Row Source Operation
------- ---------------------------------------------------
249 MINUS
851 SORT UNIQUE
851 FILTER
3026 SORT GROUP BY
29974 HASH JOIN OUTER
29974 HASH JOIN OUTER
29974 HASH JOIN
29974 HASH JOIN
29974 HASH JOIN
262798 TABLE ACCESS FULL OE_LINE
3065 HASH JOIN
197 MERGE JOIN CARTESIAN
2 TABLE ACCESS BY INDEX ROWID AR_CUSTOMER_MASTER
2 INDEX RANGE SCAN (object id 9857)
197 SORT JOIN
197 VIEW AR_SALESPERSON_CODE
197 SORT UNIQUE
197 SORT GROUP BY
197 HASH JOIN
212 TABLE ACCESS FULL CT_TEAM_MEMBER
5637 TABLE ACCESS FULL CT_ADD_NAMES
3065 TABLE ACCESS FULL OE_HDR
1 TABLE ACCESS FULL OE_CONTROL
27 TABLE ACCESS FULL OE_SUB_ENTITY
26 TABLE ACCESS FULL HM_INVOICE_SUB_ENTITY_CONTROL
236437 VIEW
236437 SORT GROUP BY
237020 NESTED LOOPS
2 TABLE ACCESS FULL OE_CONTROL
237020 TABLE ACCESS FULL AR_DOC_LINE
602 SORT UNIQUE
602 FILTER
603 SORT GROUP BY
881 HASH JOIN OUTER
881 NESTED LOOPS OUTER
882 NESTED LOOPS
882 NESTED LOOPS
609 HASH JOIN
197 VIEW AR_SALESPERSON_CODE
197 SORT UNIQUE
197 SORT GROUP BY
197 HASH JOIN
212 TABLE ACCESS FULL CT_TEAM_MEMBER
5637 TABLE ACCESS FULL CT_ADD_NAMES
608 NESTED LOOPS
2 MERGE JOIN CARTESIAN
2 TABLE ACCESS BY INDEX ROWID AR_CUSTOMER_MASTER
2 INDEX RANGE SCAN (object id 9857)
2 SORT JOIN
1 TABLE ACCESS FULL OE_CONTROL
608 TABLE ACCESS FULL OE_HDR
1489 TABLE ACCESS BY INDEX ROWID OE_LINE
1489 INDEX RANGE SCAN (object id 10514)
1762 TABLE ACCESS BY INDEX ROWID OE_SUB_ENTITY
1762 INDEX UNIQUE SCAN (object id 10595)
881 TABLE ACCESS BY INDEX ROWID HM_INVOICE_SUB_ENTITY_CONTROL
1762 INDEX UNIQUE SCAN (object id 11027)
236437 VIEW
236437 SORT GROUP BY
237020 NESTED LOOPS
2 TABLE ACCESS FULL OE_CONTROL
237020 TABLE ACCESS FULL AR_DOC_LINE
## Row Source Operation for customer_no=74 (there is only 1 record for this
customer in oe_hdr)
## This query runs in about 1 minute, using NL joins and index scans
Rows Row Source Operation
------- ---------------------------------------------------
0 MINUS
0 SORT UNIQUE
0 FILTER
2 SORT GROUP BY
2 NESTED LOOPS OUTER
3 NESTED LOOPS OUTER
3 NESTED LOOPS
3 NESTED LOOPS
2 HASH JOIN
1 NESTED LOOPS
2 MERGE JOIN CARTESIAN
2 TABLE ACCESS BY INDEX ROWID AR_CUSTOMER_MASTER
2 INDEX RANGE SCAN (object id 9857)
2 SORT JOIN
1 TABLE ACCESS FULL OE_CONTROL
1 TABLE ACCESS BY INDEX ROWID OE_HDR
2 INDEX RANGE SCAN (object id 10503)
197 VIEW AR_SALESPERSON_CODE
197 SORT UNIQUE
197 SORT GROUP BY
197 HASH JOIN
212 TABLE ACCESS FULL CT_TEAM_MEMBER
5641 TABLE ACCESS FULL CT_ADD_NAMES
3 TABLE ACCESS BY INDEX ROWID OE_LINE
3 INDEX RANGE SCAN (object id 10514)
4 TABLE ACCESS BY INDEX ROWID OE_SUB_ENTITY
4 INDEX UNIQUE SCAN (object id 10595)
2 TABLE ACCESS BY INDEX ROWID HM_INVOICE_SUB_ENTITY_CONTROL
4 INDEX UNIQUE SCAN (object id 11027)
2 VIEW
472914 SORT GROUP BY
237040 NESTED LOOPS
2 TABLE ACCESS FULL OE_CONTROL
237040 TABLE ACCESS FULL AR_DOC_LINE
0 SORT UNIQUE
0 FILTER
1 SORT GROUP BY
0 NESTED LOOPS OUTER
1 NESTED LOOPS OUTER
1 NESTED LOOPS
1 NESTED LOOPS
1 HASH JOIN
0 NESTED LOOPS
2 MERGE JOIN CARTESIAN
2 TABLE ACCESS BY INDEX ROWID AR_CUSTOMER_MASTER
2 INDEX RANGE SCAN (object id 9857)
2 SORT JOIN
1 TABLE ACCESS FULL OE_CONTROL
0 TABLE ACCESS BY INDEX ROWID OE_HDR
2 INDEX RANGE SCAN (object id 10503)
0 VIEW AR_SALESPERSON_CODE
0 SORT UNIQUE
0 SORT GROUP BY
0 HASH JOIN
0 TABLE ACCESS FULL CT_TEAM_MEMBER
0 TABLE ACCESS FULL CT_ADD_NAMES
0 TABLE ACCESS BY INDEX ROWID OE_LINE
0 INDEX RANGE SCAN (object id 10514)
0 TABLE ACCESS BY INDEX ROWID OE_SUB_ENTITY
0 INDEX UNIQUE SCAN (object id 10595)
0 TABLE ACCESS BY INDEX ROWID HM_INVOICE_SUB_ENTITY_CONTROL
0 INDEX UNIQUE SCAN (object id 11027)
0 VIEW
0 SORT GROUP BY
0 NESTED LOOPS
0 TABLE ACCESS FULL OE_CONTROL
0 TABLE ACCESS FULL AR_DOC_LINE
### Row Source Operation for customer_no=1958 (there are about 300 records for
this customer in oe_hdr)
### This is the LONG running query/execution plan (1-2 hours)
Rows Row Source Operation
------- ---------------------------------------------------
11 MINUS
14 SORT UNIQUE
14 FILTER
285 SORT GROUP BY
4768 NESTED LOOPS OUTER
4769 NESTED LOOPS OUTER
4769 NESTED LOOPS
4769 NESTED LOOPS
285 HASH JOIN
197 VIEW AR_SALESPERSON_CODE
197 SORT UNIQUE
197 SORT GROUP BY
197 HASH JOIN
212 TABLE ACCESS FULL CT_TEAM_MEMBER
5637 TABLE ACCESS FULL CT_ADD_NAMES
284 NESTED LOOPS
2 MERGE JOIN CARTESIAN
2 TABLE ACCESS BY INDEX ROWID AR_CUSTOMER_MASTER
2 INDEX RANGE SCAN (object id 9857)
2 SORT JOIN
1 TABLE ACCESS FULL OE_CONTROL
284 TABLE ACCESS BY INDEX ROWID OE_HDR
285 INDEX RANGE SCAN (object id 10503)
5052 TABLE ACCESS BY INDEX ROWID OE_LINE
5052 INDEX RANGE SCAN (object id 10514)
9536 TABLE ACCESS BY INDEX ROWID OE_SUB_ENTITY
9536 INDEX UNIQUE SCAN (object id 10595)
4768 TABLE ACCESS BY INDEX ROWID HM_INVOICE_SUB_ENTITY_CONTROL
9536 INDEX UNIQUE SCAN (object id 11027)
4334 VIEW
1127322080 SORT GROUP BY
237018 NESTED LOOPS
2 TABLE ACCESS FULL OE_CONTROL
237018 TABLE ACCESS FULL AR_DOC_LINE
3 SORT UNIQUE
3 FILTER
4 SORT GROUP BY
8 NESTED LOOPS OUTER
9 NESTED LOOPS OUTER
9 NESTED LOOPS
9 NESTED LOOPS
4 HASH JOIN
3 NESTED LOOPS
2 MERGE JOIN CARTESIAN
2 TABLE ACCESS BY INDEX ROWID AR_CUSTOMER_MASTER
2 INDEX RANGE SCAN (object id 9857)
2 SORT JOIN
1 TABLE ACCESS FULL OE_CONTROL
3 TABLE ACCESS BY INDEX ROWID OE_HDR
285 INDEX RANGE SCAN (object id 10503)
197 VIEW AR_SALESPERSON_CODE
197 SORT UNIQUE
197 SORT GROUP BY
197 HASH JOIN
212 TABLE ACCESS FULL CT_TEAM_MEMBER
5637 TABLE ACCESS FULL CT_ADD_NAMES
11 TABLE ACCESS BY INDEX ROWID OE_LINE
11 INDEX RANGE SCAN (object id 10514)
16 TABLE ACCESS BY INDEX ROWID OE_SUB_ENTITY
16 INDEX UNIQUE SCAN (object id 10595)
8 TABLE ACCESS BY INDEX ROWID HM_INVOICE_SUB_ENTITY_CONTROL
16 INDEX UNIQUE SCAN (object id 11027)
0 VIEW
1891480 SORT GROUP BY
237018 NESTED LOOPS
2 TABLE ACCESS FULL OE_CONTROL
237018 TABLE ACCESS FULL AR_DOC_LINE
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Jonathan Lewis
Sent: Wednesday, July 06, 2005 11:51 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Why the monstrous SORT?
I see you've printed the 'execution plan' lines from
the tkprof output - can you confirm that the
'row source operation' lines show exactly
the same plan. If they differ, this is the one
that is telling lies.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated June 22nd 2005
----- Original Message -----
From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, July 07, 2005 12:29 AM
Subject: Why the monstrous SORT?
Hi,
I've got an application query that is taking hours to run. After tracing
and running tkprof, I see the execution plan below. Yes, it's a monster of
a query, but Oracle seems to handle it pretty well except for the huge
amount of rows being returned by the SORT (GROUP BY) step. How can it
possibly have to sort so many rows (1.1 Billion!), when it is only getting
237,018 rows from the previous NESTED LOOP step? Any idea how to prevent or
minimize this sort?
Thanks!
Brandon
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
11 MINUS
14 SORT (UNIQUE)
--
//www.freelists.org/webpage/oracle-l
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:
