Re: oracle sorting performance

It's times like these you might want to consider things like Materialized Views or Global Temporary Tables, but both of those are highly version-dependent, and you don't specify a version.

A Global Temporary table could be used to capture the (reportedly milisecond response-time) result-set, and then apply an order by to it.

A Materialized View would permit you to store the results of your milisecond un-ordered query, permanently, and then to stick indexes on the MV, such that the query returns in its ordered state much, much quicker than you are currently experiencing.

Both of those ideas assume that you have gone as far as you possibly can in tuning the original SQL, but since no explain plain is forthcoming, it's impossible to say whether that is indeed the case.

Regards
HJR "gary b" <bogachkov_at_hotmail.com> wrote in message news:4343ce5d.0305010942.7c5f6ff4_at_posting.google.com...
> We have a client with a basic oracle setup used in conjunctin with an
> enterprise java project
>
> We are experiencing a performance problem when issuing queries against
> a specific table which has approximately 12 million records in it. The
> problem is that, although the query does eventually return correct
> results, it takes a very long time for it to execute. (approx 90
> seconds, and often more than 10 minutes when run consecutively)
>
> 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.
>
>
> the primary query (without any of the optimizations that didn't work
> that i mentioned above):
> -------------------------------
>
> SELECT
> itm.arcos_items_id, itm.item_type, itm.arcos_reports_id,
> itm.arcos_registrant_id, itm.txn_code,
> itm.action_code, itm.ndc_num, itm.wic_num, itm.qty, itm.unit_code,
> itm.assoc_dea_num, itm.order_num, itm.txn_date,
> itm.correction_num, itm.arcos_strength, itm.txn_num, itm.product_desc,
> itm.control_code, itm.pkg_size, itm.units_pkg,
> itm.unit_of_measure, itm.arcos_entered_date, itm.rejected_reason,
> itm.assoc_loc_num, itm.receiving_report_num,
> itm.users_id, itm.dttm_stamp, itm.shipment_num, itm.manual_txn_id,
> rep.report_date, rep.frequency AS report_frequency,
> rep.reported AS report_reported, reg.reg_dea_num, reg.dc_num,
> reg.frequency AS registrant_frequency
> FROM arcos_items itm, arcos_reports rep, arcos_registrant reg
> WHERE itm.arcos_reports_id=rep.arcos_reports_id
> AND itm.arcos_registrant_id=reg.arcos_registrant_id
> AND itm.item_type = ?
> AND rep.arcos_reports_id = ?
> AND (itm.rejected_reason IS NULL)
> ORDER BY itm.arcos_items_id
>
>
> thanks, i really appreciate any help on this
> -Gary Bogachkov
>
>
>
>
>
>
>
> We have a client with a basic oracle setup used in conjunctin with an
> enterprise java project
>
> We are experiencing a performance problem when issuing queries against
> a specific table which has approximately 12 million records in it. The
> problem is that, although the query does eventually return correct
> results, it takes a very long time for it to execute. (We have
> verified that the execution time is the bottleneck 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.
>
> After various tweakings, we managed to naarow 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. However, we do
> have to have an ORDER BY in the query and it is not an option to have
> a seperate view for one of the possible fields that we could possibly
> be ordering by in this dynamically generated query.
>
> We have tried setting the sort_area_size to a larger value - but this
> did not result in any measurable performance improvement.
>
>
> the query we are trying to work with is:
Received on Sat May 03 2003 - 21:51:14 CDT