Performance issue because of sql query

Question

Below is a simple query running against oracle 9.2.0.7. The parameters come from an application designed for old Boreland drivers. The application has performance issues due to following query takes more than 1hr to run. Prior the data was less but after years the data has grown. The tables has all the required indexes but indexes are taking into effect which accurate values are passed, otherwise it doing a full table scan. How can I improve the performance of the query I want to force query to use index.Below i posted all the required information.Please help Below I also have explain plan

SELECT V.*,W.FIRSTNAME || ' ' || W.LASTNAME as personname,
       S.LOGINID as    SUPERVISOR,
       W.COMMROOMID
  FROM VOILOG V,LLSWORKER W,LLSWORKER S
 WHERE V.INTLLSID = W.LLSID(+) AND W.SUPERVISORID = S.LLSID(+)
   AND TRUNC(V.ENTRYDATE) BETWEEN '01-JAN-15' AND '04-MAR-15'
   AND W.COMMROOMID like '%9999%'
   AND V.VOISUBCATID like '%'
   AND S.LOGINID like '%'
   AND V.ENTEREDBYLLSID like '%'

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |   155 | 22785 |  1101 |
|*  1 |  FILTER              |             |       |       |       |
|*  2 |   HASH JOIN          |             |   155 | 22785 |  1101 |
|*  3 |    HASH JOIN         |             |   162 | 22680 |  1043 |
|*  4 |     TABLE ACCESS FULL| VOILOG      |   162 | 17496 |   985 |
|*  5 |     TABLE ACCESS FULL| LLSWORKER   |  1551 | 49632 |    57 |
|*  6 |    TABLE ACCESS FULL | LLSWORKER   |  1862 | 13034 |    57 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_DATE('01-JAN-15')<=TO_DATE('04-MAR-15'))
2 - access("W"."SUPERVISORID"="S"."LLSID")
3 - access("V"."INTLLSID"="W"."LLSID")
4 - filter(TRUNC("V"."ENTRYDATE")>='01-JAN-15' AND
         TRUNC("V"."ENTRYDATE")<='04-MAR-15' AND
         "V"."VOISUBCATID" LIKE    '%'
         AND TO_CHAR("V"."ENTEREDBYLLSID") LIKE '%')
5 - filter(TO_CHAR("W"."COMMROOMID") LIKE '%9999%')
6 - filter("S"."LOGINID" LIKE '%')

But when my query has exact values like below

SELECT V.*,W.FIRSTNAME || ' ' || W.LASTNAME as personname,
       S.LOGINID as    SUPERVISOR,
       W.COMMROOMID
  FROM VOILOG V,LLSWORKER W,LLSWORKER S
 WHERE V.INTLLSID = W.LLSID(+) AND W.SUPERVISORID = S.LLSID(+)
   AND TRUNC(V.ENTRYDATE) BETWEEN '01-JAN-15' AND '04-MAR-15'
   AND W.COMMROOMID like '9999'
   AND V.VOISUBCATID like '%'
   AND S.LOGINID like '%'
   AND V.ENTEREDBYLLSID like '%'

---------------------------------------------------------------------------------
| Id  | Operation                       |  Name         | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |               |     1 |   147 |   239 |
|*  1 |  FILTER                         |               |       |       |       |
|*  2 |   TABLE ACCESS BY INDEX ROWID   | VOILOG        |     1 |   108 |   181 |
|   3 |    NESTED LOOPS                 |               |     1 |   147 |   239 |
|   4 |     NESTED LOOPS                |               |     1 |    39 |    58 |
|*  5 |      TABLE ACCESS FULL          | LLSWORKER     |     1 |    32 |    57 |
|*  6 |      TABLE ACCESS BY INDEX ROWID| LLSWORKER     |     1 |     7 |     1 |
|*  7 |       INDEX UNIQUE SCAN         | XPKLLSWORKER  |     1 |       |       |
|*  8 |     INDEX RANGE SCAN            | XIEINTLLSID1  |   198 |       |     2 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(TO_DATE('01-JAN-15')<=TO_DATE('04-MAR-15'))
2 - filter(TRUNC("V"."ENTRYDATE")>='01-JAN-15' AND
           TRUNC("V"."ENTRYDATE")<='04-MAR-15' AND "V"."VOISUBCATID" LIKE '%'AND
           TO_CHAR("V"."ENTEREDBYLLSID") LIKE '%')
5 - filter(TO_CHAR("W"."COMMROOMID") LIKE '9999')
6 - filter("S"."LOGINID" LIKE '%')
7 - access("W"."SUPERVISORID"="S"."LLSID")
8 - access("V"."INTLLSID"="W"."LLSID")

what's the point of having those: AND V.VOISUBCATID like '%' AND S.LOGINID like '%' AND V.ENTEREDBYLLSID like '%' ??? — Nikola Drosakis
– Nikola Drosakis, Commented Mar 7, 2015 at 19:12
Rewrite: TRUNC(V.ENTRYDATE) BETWEEN '01-JAN-15' AND '04-MAR-15' into: V.ENTRYDATE BETWEEN to_date('01-JAN-15', 'dd-mon-yy' ) AND to_date('04-MAR-15', 'dd-mon-yy' ) + 1, this expresion prevents oracle from using an index on V.ENTRYDATE, if this index exists. — krokodilko
– krokodilko, Commented Mar 7, 2015 at 19:16
@Nikos, this is part of search parameters, where users may or may not enter parameters — user501307
– user501307, Commented Mar 8, 2015 at 1:19
@user501307, kordiko is suggesting that the use of TRUNC() is what is preventing the index's being used on ENTRYDATE (which it will unless there is a function-based index on that column). — David Faber
– David Faber, Commented Mar 8, 2015 at 10:43

David Faber · Accepted Answer · 2015-03-08 11:06:30Z

Couple things.

SELECT V.*,W.FIRSTNAME || ' ' || W.LASTNAME as personname,
       S.LOGINID as    SUPERVISOR,
       W.COMMROOMID
  FROM VOILOG V,LLSWORKER W,LLSWORKER S
 WHERE V.INTLLSID = W.LLSID(+) AND W.SUPERVISORID = S.LLSID(+)
   AND TRUNC(V.ENTRYDATE) BETWEEN '01-JAN-15' AND '04-MAR-15'
   AND W.COMMROOMID like '%9999%'
   AND V.VOISUBCATID like '%'
   AND S.LOGINID like '%'
   AND V.ENTEREDBYLLSID like '%'

One, if there is an index on V.ENTRYDATE then your use of TRUNC() will cause Oracle to not use that index. You want to rewrite that condition as follows:

 WHERE v.entrydate >= date'2015-01-01'
   AND v.entrydate < date'2015-03-04' + 1

The reason we use < in the AND condition is to exclude the case where v.entrydate is equal to 2015-03-05 (with no time portion). (Note: I am using ANSI date literals above, which are supported in Oracle 9 ... otherwise you might use TO_DATE('01-JAN-15', 'MM-DD-RR') to avoid the implicit conversion to date.)

Two, you are using outer joins even though they will never be used because of the conditions on the tables that are outer joined to VOILOG:

   AND w.commroomid LIKE '%9999%'
   ...
   AND s.loginid LIKE '%'

In other words, there is no reason to use outer joins here, you can use inner joins instead. Alternately, you can explicitly handle the NULL condition on w.commroomid and s.loginid:

   AND NVL(w.commroomid, '0') LIKE '%9999%'
   ...
   AND NVL(s.loginid, '0') LIKE '%'

Another way that could be handled is by including those conditions in an explicit ANSI LEFT JOIN -- but that would be only if you wanted to return NULLs:

  FROM voilog v LEFT JOIN llsworker w
    ON v.intllsid = w.llsid
   AND w.commroomid LIKE '%9999%'

Three, I don't know if w.commroomid etc., are indexed, but the use of leading wildcards in the LIKE condition will mean that the indexes aren't used.

Collectives™ on Stack Overflow

Performance issue because of sql query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related