Re: Query with buffer (sorts) taking age to complete.
- From: "The Human Fly" <sjaffarhussain@xxxxxxxxx>
- To: "John Clarke" <jclarke@xxxxxxxxxxxxxxx>, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 25 Mar 2006 16:14:04 +0300
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.
Thanks once again to all.
On 3/25/06, John Clarke <jclarke@xxxxxxxxxxxxxxx> wrote:
> More specific to LiShan's reply, do you have system statistics in place, and
> by chance do they reflect values captured while on the old server? I've seen
> issues like these.
>
> Othet things that would come to mind are any direct or asynch IO parameters
> that may no longer be appropriate. But I doubt it's this or any other
> massive host or SAN issue in light of the fact that execution plans have
> changed.
>
>
> -----Original Message-----
> From: "The Human Fly" <sjaffarhussain@xxxxxxxxx>
> To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
> Sent: 3/25/06 2:55 AM
> Subject: Query with buffer (sorts) taking age to complete.
>
> Hello list,
>
> We have recently moved our datawarehouse oracle database 9i Rel.2 from
> AIX to HP-SUPERDOM, using export and import utility.
>
> Well, I have used the same init file for both database, i.e. they are
> identical.
>
> Today, while running a batch we are facing very wired problem. The job
> which runs in 25 mins. now it is finished for 1.5 days. The execution
> plan is diffeent, the problem execution plan shows buffer (sorts) and
> MERGE JOIN (CARTESIAN) taking too much time.
>
> Following is the query and its execution plan, can anyone shed some
> lime light on the issue :
>
> 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 ofdm_ods.a_account a
> WHERE acct_no = '00100100120'
> AND as_of_date = (SELECT max(as_of_date)
> FROM ofdm_ods.a_account b
> WHERE b.acct_no = a.acct_no
> AND b.run_offs_sar <> 0)
>
> AIX
> Execution Plan
>
>
--
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."
--
//www.freelists.org/webpage/oracle-l
Other related posts:
