This discussion is archived
5 Replies Latest reply: Sep 12, 2013 2:28 PM by Jeff E RSS

Query running long in APEX

Pam Newbie
Currently Being Moderated

Hi ,

I am using Apex Version 4.1.1.00.23.  I am running an Interactive report in Apex that is running about15- 20 seconds . I take the query out of the report and run the query  in sqldeveloper and it runs in 4 seconds. Why does it run so much slower in APEX. It is a basic

Interactive report with one query.  I will send query. Is there a way I can tune through APEX and see why it is taking so MUCH longer ?

 

 

select c.rcn

  ,case when logical_level- (select logical_level from cd_customer where rcn = :P132_RCN) = 1 then '. '

       when logical_level - (select logical_level from cd_customer where  rcn = :P132_RCN) = 2 then '. . '

       when logical_level - (select logical_level from cd_customer where  rcn = :P132_RCN) = 3 then '. . . '

       when logical_level - (select logical_level from cd_customer where  rcn = :P132_RCN) = 4 then '. . . . '

       when logical_level - (select logical_level from cd_customer where  rcn = :P132_RCN) = 5 then '. . . . . '

   end || (logical_level - (select logical_level from cd_customer where  rcn = :P132_RCN)) ||

      ' ' || get_name(c.rcn,'D','1') DName

,PHYSICAL_LEVEL - (select physical_level from cd_customer where  RCN = :P132_RCN) "LEVEL"

, nvl(sumpgpv(c.rcn, :P132_START_PERIOD, :P132_END_PERIOD,c.rank),0) PGPV

, countd(c.rcn,1, :P132_START_PERIOD, :P132_END_PERIOD)   DistCnt

, countd(c.rcn,5, :P132_START_PERIOD, :P132_END_PERIOD) MACnt,  logical_lbound,c.rank,

(select wr.abbreviation from  wd_ranknames wr where wr.rank = c.rank and wr.status=c.status) "rnk_abbrv"

,&P132_START_PERIOD,&P132_END_PERIOD ,&P132_RCN

from cd_customer c

  where :P132_END_PERIOD > (select commission_closed from cd_parameters)  and logical_lbound > 0

and logical_lbound between (select logical_lbound from cd_customer where rcn = :P132_RCN)

                        and (select logical_rbound from cd_customer where  rcn = :P132_RCN)

and (logical_level - (select logical_level from cd_customer where rcn = :P132_RCN)) <=:P132_LEVELS                 

union all

select c.rcn

  ,case when logical_level- (select logical_level from wd_customer where pvperiod = :P132_END_PERIOD and rcn = :P132_RCN) = 1 then '. '

       when logical_level - (select logical_level from wd_customer where pvperiod = :P132_END_PERIOD and rcn = :P132_RCN) = 2 then '. . '

       when logical_level - (select logical_level from wd_customer where pvperiod = :P132_END_PERIOD and rcn = :P132_RCN) = 3 then '. . . '

       when logical_level - (select logical_level from wd_customer where pvperiod = :P132_END_PERIOD and rcn = :P132_RCN) = 4 then '. . . . '

       when logical_level - (select logical_level from wd_customer where pvperiod = :P132_END_PERIOD and rcn = :P132_RCN) = 5 then '. . . . . '

   end || (logical_level - (select logical_level from wd_customer where pvperiod = :P132_END_PERIOD and rcn = :P132_RCN)) ||

      ' ' || get_name(c.rcn,'D','1') DName

,PHYSICAL_LEVEL - (select physical_level from wd_customer where pvperiod = :P132_END_PERIOD AND RCN = :P132_RCN) "LEVEL"

, sumpgpv(c.rcn, :P132_START_PERIOD, :P132_END_PERIOD,c.rank) PGPV

,countd(c.rcn,1, :P132_START_PERIOD, :P132_END_PERIOD) DistCnt

,countd(c.rcn,5, :P132_START_PERIOD, :P132_END_PERIOD) MACnt

,logical_lbound,c.rank,(select wr.abbreviation from

wd_ranknames wr where wr.rank = c.rank and wr.status=c.status) "rnk_abbrv"

,&P132_START_PERIOD,&P132_END_PERIOD ,&p132_RCN

from wd_customer c

  where pvperiod = :P132_END_PERIOD and logical_lbound > 0

and logical_lbound between (select logical_lbound from wd_customer where pvperiod = :P132_END_PERIOD and rcn = :P132_RCN)

                        and (select logical_rbound from wd_customer where pvperiod = :P132_END_PERIOD and rcn = :P132_RCN)

and (logical_level - (select logical_level from wd_customer where pvperiod = :P132_END_PERIOD and rcn = :P132_RCN)) <=:P132_LEVELS   

  • 1. Re: Query running long in APEX
    Howard (... in Training) Pro
    Currently Being Moderated

    Hi,

     

    Please run your query in APEX's SQL Workshop SQL Commands window.  Does it run longer or faster than in the report?  You got a few different WHERE clauses / CASE WHEN statements -- try removing some of them and see which ones are taking a lot of time.   It should be easier / faster to do this in the SQL Commands window.   As a first go, run each part of the query -- the parts before / after the UNION ALL -- to see if one is taking significantly more time than the other.

     

    Regards,

    Howard

  • 2. Re: Query running long in APEX
    Pam Newbie
    Currently Being Moderated

    Hi Howard,

    When I run the query in SQL Command window it runs within 4 seconds.  Why does it take the Interactive Report longer?

     

    Thanks,

    Pam

  • 3. Re: Query running long in APEX
    Howard (... in Training) Pro
    Currently Being Moderated

    The query surely gets "wrapped" in some other SQL / PL/SQL as a part of APEX running the report.  I conjecture this wrapping changes the execution plan causing it to take longer.  What is the size (# of rows) of the data?  Perhaps some index(es) could help.

     

    Howard

  • 4. Re: Query running long in APEX
    InoL Guru
    Currently Being Moderated

    I had a situation like yours too. In the end, I skipped some functionality I wanted in the query (without loosing the complete functionality of the report, of course).

     

    The query ran fast in SQLWorkshop and tools like TOAD, but not in my application.

    Apex indeed wraps your query (something like a "select * from (select ...)), but it still didn't explain why it got so much slower.

    I could see the query being executed via v$session. Using TOAD, I copied this query, ran an explain plan, ran the query in SQLWorkshop and in TOAD. Everything was fast, except in the actual application.

     

    So, there are some tools to use when trying to get to the bottom of the problem

    - Use SQLDeveloper or TOAD to see which query is actually executing in the database (this is your query, transformed by Apex into something a bit different).

    - Run a trace on the session if that still doesn't give you an idea. I never got this far, and just altered the query a bit

  • 5. Re: Query running long in APEX
    Jeff E Expert
    Currently Being Moderated

    Sorry, my OCD must have kicked in.  Try removing all those in-line queries, although not an answer to why the report takes longer than elsewhere, it might help.

     

     

    SELECT c.rcn ,
      CASE
        WHEN c.logical_level - cv.logical_level = 1  THEN '. '
        WHEN c.logical_level - cv.logical_level = 2  THEN '. . '
        WHEN c.logical_level - cv.logical_level = 3  THEN '. . . '
        WHEN c.logical_level - cv.logical_level = 4  THEN '. . . . '
        WHEN c.logical_level - cv.logical_level = 5  THEN '. . . . . '
      END
      || (c.logical_level - cv.logical_level)
      || ' '
      || get_name(c.rcn,'D','1') DName ,
      c.PHYSICAL_LEVEL - cv.physical_level "LEVEL" ,
      NVL(sumpgpv(c.rcn, :P132_START_PERIOD, :P132_END_PERIOD,c.rank),0) PGPV ,
      countd(c.rcn,1, :P132_START_PERIOD, :P132_END_PERIOD) DistCnt ,
      countd(c.rcn,5, :P132_START_PERIOD, :P132_END_PERIOD) MACnt,
      c.logical_lbound,
      c.rank,
      (SELECT wr.abbreviation
        FROM wd_ranknames wr
        WHERE wr.rank = c.rank
          AND wr.status =c.status ) "rnk_abbrv" ,
      &P132_START_PERIOD,
      &P132_END_PERIOD ,
      &P132_RCN
    FROM cd_customer c,
        cd_customer cv
    WHERE cv.rcn = :P132_RCN
      AND :P132_END_PERIOD > (SELECT commission_closed FROM cd_parameters )
      AND c.logical_lbound > 0
      AND c.logical_lbound BETWEEN cv.logical_lbound AND cv.logical_rbound
      AND (clogical_level - cv.logical_level) <=:P132_LEVELS
    UNION ALL
    SELECT c.rcn ,
      CASE
        WHEN c.logical_level - cv.logical_level = 1 THEN '. '
        WHEN c.logical_level - cv.logical_level = 2 THEN '. . '
        WHEN c.logical_level - cv.logical_level = 3 THEN '. . . '
        WHEN c.logical_level - cv.logical_level = 4 THEN '. . . . '
        WHEN c.logical_level - cv.logical_level = 5 THEN '. . . . . '
      END
      || (c.logical_level - cv.logical_level )
      || ' '
      || get_name(c.rcn,'D','1') DName ,
      PHYSICAL_LEVEL - cv.physical_level "LEVEL" ,
      sumpgpv(c.rcn, :P132_START_PERIOD, :P132_END_PERIOD,c.rank) PGPV ,
      countd(c.rcn,1, :P132_START_PERIOD, :P132_END_PERIOD) DistCnt ,
      countd(c.rcn,5, :P132_START_PERIOD, :P132_END_PERIOD) MACnt ,
      c.logical_lbound,
      c.rank,
      (SELECT wr.abbreviation
      FROM wd_ranknames wr
      WHERE wr.rank = c.rank
      AND wr.status =c.status
      ) "rnk_abbrv" ,
      &P132_START_PERIOD,
      &P132_END_PERIOD ,
      &p132_RCN
    FROM wd_customer c,
      wd_customer cv
    WHERE cv.pvperiod    = :P132_END_PERIOD
    AND cv.rcn          = :P132_RCN
    AND c.pvperiod      = :P132_END_PERIOD
    AND c.logical_lbound > 0
    AND c.logical_lbound BETWEEN cv.logical_lbound AND cv.logical_rbound
    AND (logical_level - cv.logical_level ) <= :P132_LEVELS

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points