Re: oracle sorting performance

On 1 May 2003 10:42:01 -0700, bogachkov_at_hotmail.com (gary b) wrote:

>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
>
>
>

<snip duplicate verbage>

I'm going to go out on a bit of limb (for me!) here . . .

First, you say that you 'verified' that the problem wasn't from lack of indexes. I'm not sure that your method actually verified anything. Have you done an explain plan to see exactly what access plan is being used?

Second, the big thing that jumps out at me is "AND (itm.rejected_reason IS NULL) " This will guarantee a full table scan on the referenced table. Again, go back and do an explain plan to verify. Received on Thu May 01 2003 - 14:07:17 CDT