Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Why the monstrous SORT?
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Jonathan Lewis
Sent: Wednesday, July 06, 2005 11:51 PM
To: oracle-l_at_freelists.org
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
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)
-- http://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. -- http://www.freelists.org/webpage/oracle-lReceived on Thu Jul 07 2005 - 13:38:31 CDT