Re: Why the monstrous SORT?
- From: Vlad Sadilovskiy <vlovsky@xxxxxxxxx>
- To: Brandon.Allen@xxxxxxxxxxx
- Date: Fri, 8 Jul 2005 11:45:19 -0400
Brandon,
From the responses you probably already know that the number of rows
sorted comes from number of times the view is used multiplied by
number of rows in the view.
If you still struggling with this query I have couple words to say. If
not can you please share how did you resolve the issue.
Let me try to help.
The situation looks like the CBO miscalculated the cost of using
nested loops over the in-line view. In spite the fact that it needs to
access AR_DOC_LINE using full scan and resort the results many times.
If 10053 trace is available verify why CBO preferred nested loops over
hash join. Run explain plan. Does it properly estimate cardinality of
AR_DOC_LINE and selectivity of OE_CONTROL. Verify what is estimated
cardinality of the in-line view.
If you want, I'd like to take a look at the 10053 trace and/or explain plan.
Otherwise, here are my suggestions in the order of the level of effectiveness.
1. In 9i and up you can materialize the view results using "with"
clause, that way execution should not be needing to resort the results
and do a full scan over and over again.
2. Try to force hash join or at least merge join on this view.
3. Hint CBO to put AR_DOC_LINE first in the join order with LEADING
hint inside the in-line view. That should decrease number of scans to
one for each time view is accessed.
4. In 9i putting AR_DOC_LINE table in keep cache might help.
5. Check if you can move OE_CONTROL out of the in-line view or rewrite
the query that won't need in-line view.
- Vladimir
On 7/7/05, Allen, Brandon <Brandon.Allen@xxxxxxxxxxx> wrote:
> Steve, yes you're right about adding the != and dropping the whole MINUS
> clause. That cuts the query and execution plan in half, but unfortunately it
> doesn't cut out that much work because most of the work (the huge SORT) was
> being done in the top half, so I still have to find a way to minimize that
> huge SORT operation. It does cut out a significant amount of work though so
> I will forward the recommendation to the developers.
>
> Thanks!
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
