Are you sure the sort is happening on disk and not just in memory?
Mike
[Email Address Removed] Original Message -----
From: "dba" <[Email Address Removed] "Michael McMullen" <[Email Address Removed] "'LazyDBA.com Discussion'"
<[Email Address Removed] Wednesday, October 01, 2003 3:30 PM
Subject: Re: Long running query in 9.2.0.4


> Michael,
>
> All the indexes are ok (like the 8i DB) and analyze is updated. I run this
> select to estimate the optimal value of pga_aggregate_target :
> SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
>
> ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
>
> ESTD_OVERALLOC_COUNT
>
> FROM v$pga_target_advice;
>
> But I didnt test the sort_area_size.
>
>
>
> Clayton
>
>
>
> ----- Original Message -----
> From: "Michael McMullen" <[Email Address Removed] To: "dba" <[Email Address Removed] "'LazyDBA.com Discussion'"
> <[Email Address Removed] Sent: Wednesday, October 01, 2003 4:27 PM
> Subject: Re: Long running query in 9.2.0.4
>
>
> > If you take out the distinct, you're removing the sort. How do you know
> that
> > pga_aggregate_target is sized well? You can always go back to a
> > sort_area_size parameter and retest. Of course, everything is analyzed
and
> > all indexes are in place?
> > But everything else being equal, you might have to log a tar.
> >
> > Mike
> > [Email Address Removed] > ----- Original Message -----
> >
> > From: dba
> > To: LazyDBA.com Discussion
> > Sent: Wednesday, October 01, 2003 3:16 PM
> > Subject: Long running query in 9.2.0.4
> >
> >
> > Hi Oracle's fans,
> >
> > Some queries that run ok in 8.1.6 takes long time in 9.2.0.4, I have
> sized
> > pga_aggregate_target well, others params are the same in 8.1.6, both
> > databases are identicals. I observed that the queries have <, >
operators
> in
> > where clause, some of them have distinct reserved word, that get faster
> when
> > i remove-it.
> > Does anybody can help?
> >
> > Clayton Queiroz
> > Oracle DBA
>
>
Oracle LazyDBA home page