Re: Optimizing a SQL statement

  • From: Andy Sayer <andysayer@xxxxxxxxx>
  • To: Amir.Hameed@xxxxxxxxx
  • Date: Wed, 4 Dec 2019 17:20:16 +0000

I suggest you take a step back and rewrite that massive list of OR filters.
Do you really want one statement to rule them all or will it be the case
that your user is going to be using a reasonably selective filter?

You might be using an index on product to drive this query but it can’t
actually be used as a filter unless every one of those columns is included
in the index - I bet it’s just on the column you are ordering by so Oracle
will go to every row in the table in that order eliminating rows that don’t
match the rest of the filters as it goes. This is going to be very slow if
your filters are actually decently selective - you might have to read the
entire index and table a block at a time (sometimes reading the same table
block multiple times) if less than 500 rows matches your OR list.

There is no way for Oracle to use an index on serial_id here (for example)
as you can’t eliminate rows based on that filter - you would need to AND
the filters together to achieve that.

Rewrite the query so that only the filters that are actually needed are
used. Consider that not all of these filters should be LIKE filters.
Consider that you probably want to AND the filters together.

For the query you’ve shared, I would suggest a full table scan of product
would probably be the most efficient way to execute the query under most
reasonable inputs.

Hope this helps you get started,
Andy

On Wed, 4 Dec 2019 at 17:06, Hameed, Amir <Amir.Hameed@xxxxxxxxx> wrote:

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)



Other related posts: