Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Query with buffer (sorts) taking age to complete.

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

From: jaromir nemec <jaromir_at_db-nemec.com>
Date: Sun, 26 Mar 2006 04:19:39 +0200
Message-ID: <014301c6507b$bce40f20$3c02a8c0@JARAWIN>


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

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 Sat Mar 25 2006 - 20:19:39 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US