Re: Query with buffer (sorts) taking age to complete.

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sun, 26 Mar 2006 09:34:00 +0100
Message-ID: <000f01c650b0$0803a2c0$0300a8c0@Primary>

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_at_freelists.org> To: "oracle-l digest users" <oracle-l_at_freelists.org> Sent: Sunday, March 26, 2006 8:03 AM Subject: oracle-l Digest V3 #89

> ----- Original Message -----
> From: "The Human Fly" <sjaffarhussain_at_gmail.com>
> To: "John Clarke" <jclarke_at_centroidsys.com>; "oracle-l"
> <oracle-l_at_freelists.org>
> 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.
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Mar 26 2006 - 02:34:00 CST