RE: Optimizing a SQL statement

From: Hameed, Amir <Amir.Hameed_at_xerox.com>
Date: Fri, 6 Dec 2019 12:36:14 +0000
Message-ID: <MWHPR11MB1646F73DAE3D7027BF8941EFF45F0_at_MWHPR11MB1646.namprd11.prod.outlook.com>

Thank you everyone for your comments. This query belongs to standard code of an ERP package called IFS. I have found a few other similar statements that are written the same way. We have notified the vendor and asked to fix them.

Thanks
-----Original Message-----

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of Jonathan Lewis Sent: Friday, December 6, 2019 6:45 AM
To: oracle-l_at_freelists.org
Subject: Re: Optimizing a SQL statement

As Andy indicates - this looks like a "programmer-friendly" implementation of a search screen that allows the end user to supply a choice of search values. Instead of coding to produce SQL that gives the optimizer the best possible chance of finding a good plan it uses the same fixed SQL supplying nulls as input parameters where the end-user has not supplied a value.

Given the long list of predicates, and the need to produce a single plan that will ALWAYS give the correct result, the optimizer has only two options: drive through the product_id (which I assume is the PK so not null) to check every row in the table as this will produce the result in the correct order, or do a tablescan to check every row, then sort.

As Sayan says - the only other option you have is to ensure that EVERY column used in the WHERE clause has its own index so that the optimizer can choose to do concatenation (11g) or OR-expansion (12.2) (See: https://jonathanlewis.wordpress.com/2018/03/02/conditional-sql-5/ ). Then, given the number of OR'ed predicates and the way the optimizer treats all the "LIKE with bind" predicates you may still have to include a use_concat() hint to force concatenation.

If you do generate a complete concatenated path, check the OUTLINE section and copy the full use_concat() hint from there back to the code. Don't be surprised, by the way, by the number of LNNVL() predicates that appear if concatenation takes place.

You may also find that you can further improve performance by querying the product table only, sorting and limiting to 500 rows, before joining to the place table - but that would only make a difference in cases where the number of rows initially found in the product table was significantly greater than 500.

Regards
Jonathan Lewis

---

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Hameed, Amir <Amir.Hameed_at_xerox.com> Sent: 04 December 2019 17:05
To: oracle-l_at_freelists.org
Subject: Optimizing a SQL statement

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

SELECT * FROM ( SELECT place.name,place.place_id FROM product LEFT

OUTER JOIN place  ON  product.place_id_owned_by = place.place_id WHERE ((((((((((((((((((((( product.product_id LIKE :p1 OR product.model_id LIKE :p2) OR product.part_id LIKE :p3) OR product.serial_id LIKE :p4) OR product.serial_id LIKE :p5) OR product.serial_id LIKE :p6) OR product.serial_id LIKE :p7) OR product.place_id_owned_by LIKE :p8) OR product.place_id LIKE :p9) OR product.temporary_serial LIKE :p10) OR product.temporary_serial LIKE :p11) OR product.temporary_serial LIKE :p12) OR product.temporary_serial LIKE :p13) OR product.condition_code LIKE :p14) OR product.lot_id LIKE :p15) OR product.lot_id LIKE :p16) OR product.lot_id LIKE :p17) OR product.lot_id LIKE :p18) OR product.asset_id LIKE :p19) OR product.asset_id LIKE :p20) OR product.asset_id LIKE :p21) OR product.asset_id LIKE :p22) ORDER BY

product.product_id ASC )
WHERE ROWNUM <= 500
;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.01          0          0          0           0
Fetch        2     57.02      72.37     200413    4570892          0           1


------- ------  -------- ---------- ---------- ---------- ----------  ----------


total        4     57.04      72.39     200413    4570892          0           1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------

         1          1          1  COUNT STOPKEY (cr=4570892 pr=200413 pw=0 time=69235827 us)
         1          1          1   VIEW  (cr=4570892 pr=200413 pw=0 time=69235818 us cost=1752 size=82164 card=501)
         1          1          1    NESTED LOOPS OUTER (cr=4570892 pr=200413 pw=0 time=69235815 us cost=1752 size=148797 card=501)
         1          1          1     TABLE ACCESS BY INDEX ROWID PRODUCT (cr=4570888 pr=200411 pw=0 time=72376638 us cost=1003 size=511287210 card=2157330)
   4578439    4578439    4578439      INDEX FULL SCAN PRODUCTP1 (cr=20522 pr=19831 pw=0 time=18481568 us cost=7 size=0 card=1063)(object id 2080845)
         1          1          1     TABLE ACCESS BY INDEX ROWID PLACE (cr=4 pr=2 pw=0 time=243 us cost=2 size=60 card=1)
         1          1          1      INDEX UNIQUE SCAN PLACEP1 (cr=3 pr=1 pw=0 time=146 us cost=1 size=0 card=1)(object id 2080816)

--

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

--

http://www.freelists.org/webpage/oracle-l Received on Fri Dec 06 2019 - 13:36:14 CET