RE: Optimizing a SQL statement

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Fri, 6 Dec 2019 08:13:15 -0500
Message-ID: <06b101d5ac36$ed0c10a0$c72431e0$_at_rsiz.com>

1. brilliant
2. Whilst askew from how to tune what we've actually been presented with, the "correctness analyst" in me *suspects* the author of this code really doesn't mean OR product.place_id_owned_by LIKE :p8)

my suspicion would be that they are doing what Andy and JL indicated with all the other possible predicates, but the join logic screams out that the requestor really only is interested in rows where product.place_id_owned_by LIKE :p8.

IF that whopping big guess is true (please read that IF twice), then it seems likely that pruning the component tables to rows where that is true before all the other filters are applied is and the join is done could be a big winner. Of course whether or not it is a big winner depends each run on how selective :p8 is with respect to the actual data set. Yet the logic of the join *suggests* this might be a good thing. Unless (also of course) my whopping big guess is wrong.

A gtt (ctas, then append(s)) with just the rowid, name, place_id, and place_id_owned_by of product for each of the several OR filter columns intentionally done as FTS for just rowid, name, place_id_owned_by and the filter column *might* be useful if the code as presented indeed represents the "use case" and my whopping big guess is wrong. Then for the join rowsource in place of product you use select distinct rowid,name,place_id,place_id_owned_by order by rowid. Something like that. your mileage may vary. A union of all the OR-bits (orbits?) might be quite fast if concurrent or processing of the UNION is allowed and you have and don't mind completely consuming a large number of CPUs for the duration of the query.

yuck.

mwf

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Friday, December 06, 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 - 14:13:15 CET