asked on

Performance issue with case statement in oracle 11G

Hi Experts,

I am using below query which works fine but recently I found in prod it is taking much time as number of records are more. Looks like innocent query except the case statemet which I guess taking much time.

Is there a way to enahnce the performance of the query by changing case statement or any other way is also welcome ? I have run this query in my local having 110 records where it took 0.101 sec but in prod where we have around 10 m records it is taking around 40 sec which is quite huge. Please suggest.
Also enclosing execution plan i.e.

Query is:

select plan_table_output
from table(dbms_xplan.display('plan_table',null,'basic +predicate +cost'));

SELECT  (
  CASE
    WHEN cim.chtranstype             IN ('3173', '3178')
    AND CIM.DTORDERFULFILLEDDATE     IS NOT NULL
    AND InStr(cim.vcfsvreference,';') > 0
    THEN 
      (SELECT pr.VCCOURIERTRACKINGNUM 
      FROM Table1 pr 
      WHERE pr.nbcardid = SUBSTR(cim.vcfsvreference,InStr(cim.vcfsvreference,
        ';') + 1,10)
      ) 
    WHEN cim.chtranstype               = '3183'
    AND CIM.DTORDERFULFILLEDDATE      IS NOT NULL
    AND InStr(CIM.VCFSVREFERENCE, '.') > 0
    THEN 
      (SELECT t.vccouriertrackingnumber
      FROM owner.Table5 t 
      WHERE t.nbcimsequence     = cim.nbcimsequence
      AND T.VCRESPONSEFILENAME IN ( 
      SELECT ds.VCRESPONSEFILENAME
      FROM owner.Table3 ds 
      WHERE ds.nbcimsequence     = cim.nbcimsequence
      AND ds.vcrequestfilename   = CIM.VCFSVREFERENCE 
      AND ds.vcresponsefilename IS NOT NULL)
      AND rownum                 =1
      ) 
    ELSE  NULL 
  END) AS VCCOURIERTRACKINGNUMBER,
   CIM.VCREFERENCE
FROM Table7 cim
WHERE (cim.CHTRANSTYPE != '3178')
AND cim.NBCOMPANY      IN (67426349) 
AND cim.NBSUBCOMPANY   IN (67426349)

Open in new window

execution Plan is:

----------------------------------------------------------------------------------
| Id  | Operation                             | Name                     | Cost  |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                         |   712 |
|   1 |  TABLE ACCESS BY INDEX ROWID          | Table1          		|     2 |
|   2 |   INDEX UNIQUE SCAN                   | Table2       			|     1 |
|   3 |  COUNT STOPKEY                        |                         |       |
|   4 |   NESTED LOOPS                        |                         |     9 |
|   5 |    TABLE ACCESS BY INDEX ROWID        | Table3     				|     2 |
|   6 |     INDEX UNIQUE SCAN                 | Table4  				|     1 |
|   7 |    TABLE ACCESS BY INDEX ROWID BATCHED| Table5    				|     7 |
|   8 |     INDEX RANGE SCAN                  | Table6 					|     1 |
|   9 |  TABLE ACCESS BY INDEX ROWID BATCHED  | Table7     				|   132 |
|  10 |   INDEX SKIP SCAN                     | Table8 					|    76 |
----------------------------------------------------------------------------------

Open in new window

Geert G

you have a index skip scan on a table8 which isn't used in your query

very odd

slightwv (䄆 Netminder)

Aside from the missing tables, is that plan from production? If not can you post the plan from production?

ASKER CERTIFIED SOLUTION

Mark Geerlings

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

satmisha

ASKER

Thanks experts for your prompt reply. I am trying to get the execution plan from prod. Will post as soon as I get that.

SOLUTION

Raf

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Geerlings

An "explain plan" is one of the three pieces of information I like to have available to help solve performance problems. The other two pieces of information are:
1. a list of each table involved, with the number of rows, blocks and avg_row_len for each table.
2. a list of the indexes and the columns included in each index on each table.

Note: the information for #1 here is all available in the all_tables (or user tables or dba_tables) data dictionary view, assuming that your tables are analyzed regularly. If your tables are not analyzed regularly (like: weekly or at least monthly) that could explain slow performance.

satmisha

ASKER

Thanks guys for your prompt reply so fa I made changes against the application level code rather changing at db level . But thanks for your prompt reply.