Home » RDBMS Server » Performance Tuning » Very slow wildcard query
Very slow wildcard query [message #64854] Thu, 19 February 2004 15:54 Go to next message
Rohan
Messages: 6
Registered: February 2004
Junior Member
I have a query internal to an application that is taking an exceptionally long time to execute. It is a wildcard search in a complex query.

Following are execution time stats for various combinations of the criteria causing the problem.

The part of the query incorporating the wildcard is:

--AND UPPER(shp_flex11) LIKE UPPER('texu%') -- this takes approx 35-48 secs

If we change the query around slightly we get the following:

--AND UPPER('texu%') LIKE UPPER(shp_flex11) -- this takes approx <1 sec

--AND shp_flex11 LIKE UPPER('texu%') -- this takes approx 2 secs

--AND upper(shp_flex11) LIKE 'texu%' -- this takes approx 2 secs

--AND UPPER(shp_flex11) LIKE UPPER('texu') -- this takes pprox 2 secs

We have a functional index on UPPER(shp_flex11), but it does not seem to help this query.

As we cannot change the query, is anyone aware of any db parameters or anything else that may affect how quickly it is executed?

Thanks in advance.
Re: Very slow wildcard query [message #64861 is a reply to message #64854] Fri, 20 February 2004 09:53 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
"We have a functional index on UPPER(shp_flex11), but it does not seem to help this query"

Are you certain of this ? Have you gathered the statistics for the concerned tables? . Could you trace the session and post the execution plan/statistics,here ?

-Thiru
Re: Very slow wildcard query [message #64865 is a reply to message #64861] Fri, 20 February 2004 22:28 Go to previous messageGo to next message
Manish Thawani
Messages: 6
Registered: April 1998
Junior Member
After gathering stats as Thiru suggested , you can see the explain Plan , you can always use HINT option to force it to use the index. (if there is possibility of code change.)
Re: Very slow wildcard query [message #64867 is a reply to message #64861] Sun, 22 February 2004 14:19 Go to previous messageGo to next message
Rohan
Messages: 6
Registered: February 2004
Junior Member
Thanks very much for the replies.

Below is the execution plan. It shows that the UPPER(SHP_FLEX11) index is being used. The query joins to a table called VL_DELIVERY, and the plan shows that the major cost is a full table scan on that table. What is puzzling to me is that, as stated in the original posting, when the query criteria are changed (unrelated to the VL_DELIVERY table) the performance improves. Any thoughts?

SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1130 Card=1 Bytes=21799)
..COUNT (STOPKEY)
....VIEW (Cost=1130 Card=1 Bytes=21799)
......SORT (ORDER BY STOPKEY) (Cost=10 Card=1 Bytes=683)
........FILTER
..........NESTED LOOPS (OUTER) (Cost=8 Card=1 Bytes=683)
............NESTED LOOPS (Cost=8 Card=1 Bytes=516)
..............NESTED LOOPS (Cost=7 Card=1 Bytes=474)
................NESTED LOOPS (Cost=6 Card=1 Bytes=437)
..................NESTED LOOPS (OUTER) (Cost=5 Card=1 Bytes=379)
....................NESTED LOOPS (Cost=5 Card=1 Bytes=296)
......................NESTED LOOPS (Cost=4 Card=1 Bytes=254)
........................TABLE ACCESS (BY INDEX ROWID) OF VL_USER (Cost=1 Card=1 Bytes=45)
..........................INDEX (UNIQUE SCAN) OF USR_UK1 (UNIQUE)
........................TABLE ACCESS (BY INDEX ROWID) OF VL_SHIPMENT (Cost=3 Card=1 Bytes=209)
..........................INDEX (RANGE SCAN) OF SHP_FLEX11_UPPER_IDX (NON-UNIQUE) (Cost=1 Card=1)
......................TABLE ACCESS (BY INDEX ROWID) OF VL_ORGANIZATION (Cost=1 Card=1 Bytes=42)
........................INDEX (UNIQUE SCAN) OF ORG_PK (UNIQUE)
....................TABLE ACCESS (BY INDEX ROWID) OF VL_ORGANIZATION_ML
......................INDEX (UNIQUE SCAN) OF ORGML_UK1 (UNIQUE)
..................TABLE ACCESS (BY INDEX ROWID) OF VL_EVENT (Cost=1 Card=1 Bytes=58)
....................INDEX (UNIQUE SCAN) OF EVNT_PK (UNIQUE)
................TABLE ACCESS (BY INDEX ROWID) OF VL_LOCATION (Cost=1 Card=1 Bytes=37)
..................INDEX (UNIQUE SCAN) OF LOC_PK (UNIQUE)
..............TABLE ACCESS (BY INDEX ROWID) OF VL_EVENTCODE (Cost=1 Card=1 Bytes=42)
................INDEX (UNIQUE SCAN) OF EVNTC_PK (UNIQUE)
............TABLE ACCESS (BY INDEX ROWID) OF VL_EVENTCODE_ML
..............INDEX (UNIQUE SCAN) OF EVNTCML_PK (UNIQUE)
..........FILTER
............TABLE ACCESS (FULL) OF VL_DELIVERY (Cost=1120 Card=48340 Bytes=1836920)
............FILTER
..............CONNECT BY (WITH FILTERING)
................NESTED LOOPS
..................FILTER
....................INLIST ITERATOR
......................INDEX (RANGE SCAN) OF ORG_PK (UNIQUE) (Cost=1 Card=7 Bytes=140)
..................TABLE ACCESS (BY USER ROWID) OF VL_ORGANIZATION
................NESTED LOOPS
..................BUFFER (SORT)
....................CONNECT BY PUMP
..................TABLE ACCESS (BY INDEX ROWID) OF VL_ORGANIZATION (Cost=2 Card=1 Bytes=40)
....................INDEX (UNIQUE SCAN) OF ORG_PK (UNIQUE) (Cost=1 Card=12572)
..............SORT (UNIQUE) (Cost=6 Card=2 Bytes=49)
................UNION-ALL
..................TABLE ACCESS (BY INDEX ROWID) OF VL_USERAFFILIATE (Cost=1 Card=1 Bytes=29)
....................INDEX (RANGE SCAN) OF UAFF_UK1 (UNIQUE)
..................FILTER
....................INDEX (UNIQUE SCAN) OF ORG_PK (UNIQUE) (Cost=1 Card=1 Bytes=20)
............FILTER
..............TABLE ACCESS (BY INDEX ROWID) OF VL_SHIPMENTLEG (Cost=4 Card=1 Bytes=39)
................INDEX (RANGE SCAN) OF SHPL_SHP_OID (NON-UNIQUE) (Cost=3 Card=1)
..............FILTER
................CONNECT BY (WITH FILTERING)
..................NESTED LOOPS
....................INDEX (UNIQUE SCAN) OF ORG_PK (UNIQUE) (Cost=1 Card=1 Bytes=20)
....................TABLE ACCESS (BY USER ROWID) OF VL_ORGANIZATION
..................NESTED LOOPS
....................BUFFER (SORT)
......................CONNECT BY PUMP
....................TABLE ACCESS (BY INDEX ROWID) OF VL_ORGANIZATION (Cost=2 Card=1 Bytes=40)
......................INDEX (UNIQUE SCAN) OF ORG_PK (UNIQUE) (Cost=1 Card=12572)
................SORT (UNIQUE) (Cost=6 Card=2 Bytes=49)
..................UNION-ALL
....................TABLE ACCESS (BY INDEX ROWID) OF VL_USERAFFILIATE (Cost=1 Card=1 Bytes=29)
......................INDEX (RANGE SCAN) OF UAFF_UK1 (UNIQUE)
....................FILTER
......................INDEX (UNIQUE SCAN) OF ORG_PK (UNIQUE) (Cost=1 Card=1 Bytes=20)
............FILTER
..............CONNECT BY (WITH FILTERING)
................NESTED LOOPS
..................INDEX (UNIQUE SCAN) OF ORG_PK (UNIQUE) (Cost=1 Card=1 Bytes=20)
..................TABLE ACCESS (BY USER ROWID) OF VL_ORGANIZATION
................NESTED LOOPS
..................BUFFER (SORT)
....................CONNECT BY PUMP
..................TABLE ACCESS (BY INDEX ROWID) OF VL_ORGANIZATION (Cost=2 Card=1 Bytes=40)
....................INDEX (UNIQUE SCAN) OF ORG_PK (UNIQUE) (Cost=1 Card=12572)
..............SORT (UNIQUE) (Cost=6 Card=2 Bytes=49)
................UNION-ALL
..................TABLE ACCESS (BY INDEX ROWID) OF VL_USERAFFILIATE (Cost=1 Card=1 Bytes=29)
....................INDEX (RANGE SCAN) OF UAFF_UK1 (UNIQUE)
..................FILTER
....................INDEX (UNIQUE SCAN) OF ORG_PK (UNIQUE) (Cost=1 Card=1 Bytes=20)
Re: Very slow wildcard query [message #64869 is a reply to message #64867] Mon, 23 February 2004 04:58 Go to previous message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
oh Ok,I was under the impression that the function based index wasnt used when your query takes longer. So,when you modify the query a little bit, the index gets skipped ? and/or the whole plan changes ,yielding a much faster response? If thats the case, can you skip the index via NOINDEX hint or completely drop the index if it isnt used elsewhere ? Also,does the table VL_DELIVERY have a usable index on it ?
There are optimizer settings like optimizer_index_caching, optimizer_index_cost_adj that you can adjust to influence the optimizer behaviour(ie choosing/ignoring index) , in case you cant modify the query to ignore the index. For more info about these parameters Optimizer influence

Hope this helps
Thiru

[Updated on: Fri, 18 February 2005 23:32]

Report message to a moderator

Previous Topic: cost based optimizer
Next Topic: SQL*Net Message from client
Goto Forum:
  


Current Time: Tue Jan 19 21:45:49 CST 2021