If the Order By is indeed the culprit, you could change your query to do:
select * from (your original query statement excluding the order by)
order by arcos_items_id
--
Terry Dykstra
Canadian Forest Oil Ltd.
"Ed Stevens" <nospam_at_noway.nohow> wrote in message
news:oqr2bvk9d1jegdmqio8obeitgar4u6fpd2_at_4ax.com...
> 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 - 17:33:57 CDT
