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@xxxxxxxxxxxxxxxxxx> 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@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.
> >
> >
> >
>
>
> --
> 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-l


Other related posts: