Re: Inputs Needed to Optimizing a SQL Query

Hi Amit,

Time is spend in Line 66 and 67 almost exclusively. The result of the access on Line 67 on index MTL_CYCLE_COUNT_ENTRIES_N2  is 11 million rows.
The result of of the access on Line 66 using the rowids of line 67 is 6600 rows.
That case is clear. Index MTL_CYCLE_COUNT_ENTRIES_N2   is not ideal for that query.
You need to construct an new index. The conditions applied in line 66 might help you identify the columns missing. That is: (INTERNAL_FUNCTION("MCCE"."ENTRY_STATUS_CODE") AND NVL("MCCE"."EXPORT_FLAG",2)=2). The new index needs likely to include the following columns:

"ORGANIZATION_ID", "CYCLE_COUNT_HEADER_ID","SUBINVENTORY", NVL("MCCE"."EXPORT_FLAG",2), ENTRY_STATUS_CODE. I am missing some information here, therefore I can not give 100% advise and you might need to do some tests.

Thanks

Lothar

Am 12.08.2024 um 16:58 schrieb Amit Saroha:
> Hi All,
>
> We are using Oracle DB version 19.2 on-premises, and the enclosed
> query is giving us trouble. Our warehouse operations cannot handle the
> slowness of the query, which takes about 20 seconds to complete. If at
> all feasible, I would appreciate your feedback on how to shorten the
> execution time to two or three seconds.
>
> Please check the SQL query and monitoring that are contained, and let
> us know if there is any way to speed up the execution time.
>
> If you require any further information, just let me know and I would
> be pleased to provide it. I appreciate your assistance and backing in
> advance.
>
> Best Regards,
> AMIT

--
http://www.freelists.org/webpage/oracle-l