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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> On Behalf
Of Jonathan Lewis
Sent: Friday, December 6, 2019 6:45 AM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Hameed, Amir <Amir.Hameed@xxxxxxxxx>
Sent: 04 December 2019 17:05
To: oracle-l@xxxxxxxxxxxxx
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)
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
