| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Query with buffer (sorts) taking age to complete.
Hi Jonathan,
You are right in your pridiction. I have done the test with dbms_xplan
and I found out that when I put query_rewirte_enabled=false, the
optimizer dropped a few join conditions, when I set to TRUE, it does
correct joins. Below are the outputs of join from the dbms_xplan.
<<
with query_rewirte_enabledFALSE
3 - filter("A"."AS_OF_DATE"=MAX("B"."AS_OF_DATE"))
7 - filter("B"."RUN_OFFS_SAR"<>0)
8 - access("B"."ACCT_NO"='00100100120')
filter("B"."ACCT_NO"='00100100120')
11 - filter("A"."ACCT_NO"='00100100120')
with query_rewrite_enabled=TRUE
3 - filter("A"."AS_OF_DATE"=MAX("B"."AS_OF_DATE"))
5 - access("B"."ACCT_NO"="A"."ACCT_NO") ====
7 - filter("B"."RUN_OFFS_SAR"<>0)
8 - access("B"."ACCT_NO"='00100100120')
filter("B"."ACCT_NO"='00100100120')
11 - access("A"."ACCT_NO"='00100100120')
filter("A"."ACCT_NO"='00100100120')
Thanks for your valuable time.
On 3/26/06, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:
>
> 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
>
>
>
-- Best Regards, Syed Jaffar Hussain 8i,9i & 10g, OCP DBA Banque Saudi Fransi, Saudi Arabia http://jaffardba.blogspot.com/ ---------------------------------------------------------------------------------- "Winners don't do different things. They do things differently." -- http://www.freelists.org/webpage/oracle-lReceived on Sun Mar 26 2006 - 03:12:03 CST
 |
 |