CODEWITHSUNDEEP
sqloracle-databaseoracle11g
user501307
user501307

Reputation: 57

Performance issue because of sql query

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")

Upvotes: 0

Views: 132

Answers (1)

David Faber
David Faber

Reputation: 12486

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.

Upvotes: 1

Related Questions