oracle sorting performance

From: gary b <bogachkov_at_hotmail.com>
Date: 1 May 2003 10:42:01 -0700
Message-ID: <4343ce5d.0305010942.7c5f6ff4@posting.google.com>

We have a client with a basic oracle setup used in conjunctin with an enterprise java project

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 Thu May 01 2003 - 12:42:01 CDT