Optimizing a SQL statement

From: Hameed, Amir <Amir.Hameed_at_xerox.com>
Date: Wed, 4 Dec 2019 17:05:15 +0000
Message-ID: <MWHPR11MB16467DDC79D77339B99139C4F45D0_at_MWHPR11MB1646.namprd11.prod.outlook.com>

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 Received on Wed Dec 04 2019 - 18:05:15 CET