Re: Query with buffer (sorts) taking age to complete.
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 26 Mar 2006 09:34:00 +0100
See Chapter 6 - Transitive closure.
When you have a problem like this, use dbms_xplan()
to get the more detailed exeuction plan than you don't
get from autotrace (until 10.2). In this case I think you
would find that you have a join predicate that disappears
(to produce the cartesian join) because transitive closure
has been used to copy a filter predicate from one side of
the join to the other.
As it says in chapter 6 (summarised in Appendix A)
============
In the case where the constant predicate is an equality the
optimizer will generate a second constant predicate, but
lose the join predicate if query_rewrite_enabled = false, hence ...
On the other hand, if query_rewrite_enabled = true, then
8i and 9i will keep the join predicate even in this case, hence ...
============
Regards
Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
----- Original Message -----
From: "FreeLists Mailing List Manager" <ecartis@xxxxxxxxxxxxx>
To: "oracle-l digest users" <oracle-l@xxxxxxxxxxxxx>
Sent: Sunday, March 26, 2006 8:03 AM
Subject: oracle-l Digest V3 #89
----- Original Message -----
From: "The Human Fly" <sjaffarhussain@xxxxxxxxx>
To: "John Clarke" <jclarke@xxxxxxxxxxxxxxx>; "oracle-l"
<oracle-l@xxxxxxxxxxxxx>
Sent: Saturday, March 25, 2006 3:14 PM
Subject: Re: Query with buffer (sorts) taking age to complete.
Thanks list for your interest in solving the issue.
However, I have resolved the issues. I took trace event 10053 with
level 1 on AIX as well HP-SUPERDOM and quick to spot that the
parameter 'query_rewirte_enabled' was set to FALS on HPSUPERDOM. I
changed it to TRUE and the sql runs like as its was.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
