Re: Optimizing a SQL statement

Hello Amir,
unfortunately one of the most important information (access/filter predicates and column projections) is missing in your case but let's do a little bit of guess work here.

> Almost all of the columns in the statement are indexed. Tables PRODUCT and PLACE have 4,576,690 and 1,892,243 rows respectively.

Your statement is CPU driven which is possibly caused by the amount of LIOs which are mainly driven by the PRODUCT table access (4.570.888). Your index access/filter predicates are almost returning all rows (4.578.439) which are then additionally filtered down to 1 by one of the predicates by PRODUCT table access.

So the question is - how does the access/filter predicates and column projections look like - if you can avoid the PRODUCT table access at all (or filter more early in the index) it should be way faster :)

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website: http://www.soocs.de
Twitter: _at_OracleSK

> "Hameed, Amir" <Amir.Hameed_at_xerox.com> hat am 4. Dezember 2019 um 18:05 geschrieben:
>
>
> Hi,
>
>
> The following statement does over 4.5 million LIOs per execution. It runs at an average of 3-4 times per minute. Almost all of the columns in the statement are indexed. Tables PRODUCT and PLACE have 4,576,690 and 1,892,243 rows respectively. The only way I have been able to optimize it is to add a hint to scan the PRODUCT table in PARALLEL, which does help with the elapsed time and the statement finishes within 10 seconds. However, because the statement runs quite frequently, I don’t believe using the parallel hint would be a good idea. What would be a good approach I should look into to help optimize it. The DB version is 11.2.0.4.
>
>
> Thanks

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