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

Hi Syed,

> parameter 'query_rewirte_enabled' was set to FALS on HPSUPERDOM. I

> changed it to TRUE and the sql runs like as its was.

I can reproduce the change in the plan dependent on the parameter query_rewrite_enabled in 9.2.0.5.0.

In 10.2 it is no more an issue. The execution plan in 10g for this query is IMO also much better than in 9.

In 9i you may try to rewrite the query using analytic function instead of a correlated subquery. As I assume that there are plenty of records per acct_no (due to the performance of Cartesian join) the maximal record(s) found via MAX analytic function will probably perform better then equi joining all records of a particular account and filtering those with max date.

something like this (no syntax guarantee)

SELECT max(as_of_date), max(cust_code), nvl(abs(sum(run_offs_sar)), 0),

       nvl(abs(sum(inst_amt_sar)), 0), nvl(abs(sum(bal_sar)), 0)

    FROM (select

max(case when run_offs_sar <> 0 then as_of_date end) over (partition by acct_no) as max_date, a.*

from ofdm_ods.a_account a

WHERE acct_no = '00100100120')

where as_of_date = max_date;

From the same reason as mentioned above you may verify the parameters of gathering statistics on ofdm_ods as I expect issuing

select count(*)

from ofdm_ods.a_account a

WHERE acct_no = '00100100120';

you will get much more rows than the cardinality of 167 resp, 513 estimated in the execution plans.

Regards,

Jaromir D.B. Nemec

• 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