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] "dba" <[Email Address Removed] "'LazyDBA.com Discussion'"
<[Email Address Removed] 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