Re: oracle sorting performance

bogachkov_at_hotmail.com (gary b) wrote:
>
> We have verified that the oracle execution time is the bottleneck (as
> opposed to transport of large result sets over the network) by
> modifying the query to return only one result - it was still just
> about as slow. We have also verified that the problem isn't connected
> to a lack of indexes by modifying the query to not have any joins and
> to only order by columns that have indexes placed on them -
> performance was the same.
>
> After various tweakings, we managed to narrow down the behaviour to be
> caused by the ORDER BY part of the query. Thus if we don't have an
> ORDER BY clause - the query runs considerably faster. (in the
> milliseconds) However, we DO have to have an ORDER BY in the query and
> it is not an option to have a seperate view for each one of the
> possible fields that we could possibly be ordering by in this
> dynamically generated query (since there are many possible
> combinations).
>
> We have tried setting the sort_area_size to a large value - but this
> did not result in any measurable performance improvement.

Are you using workarea_size_policy and pga_aggregate_target?

>
> the primary query (without any of the optimizations that didn't work
> that i mentioned above):

Why on earth would you post that? It seems like you would post the simplest possible query that elicits the problem, not the most complex one.

What's the explain plan look like with and without the order by?

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service              New Rate! $9.95/Month 50GB