This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Mar 4, 2013 8:45 AM by tdsacilowski Go to original post RSS
  • 15. Re: Scalability Issues - Too Many Active Sessions?
    tdsacilowski Newbie
    Currently Being Moderated
    Hi Patrick,

    I just turned Activity Logging on temporarily. Here is the data after just a couple minutes with it on:
    APPLICATION_ID     COUNT(APEX_SESSION_ID)
    101          1068
    4000          44
    4500          37
    4350          30
    4550          7
    
    PAGE_ID     COUNT(APEX_SESSION_ID)
    7     544
    8     323
    1     133
    5     53
    12     50
    2     4
    So it does, indeed seem to be page 7 causing most of the issue here. Just to explain a little further how the application is set up:

    Page 7 simply shows the next student in the queue for a specific workstation (passed in through the URL). It does this by using an AJAX call to poll the DB every 5 seconds. This page represents most of the devices running the application. There are some other admin and display stations but there are far fewer of those or they're not polling the DB. This is the code I'm using (some modifications since I last posted it at the beginning of the thread):
    //Poll the DB for the next student at current station
    (function next_student(workstation_in, div_in) {
        try {
            //Call APEX OnDemand process
            apex.server.process(
                'F_NEXT_STUDENT',
                { x01: workstation_in },
                { type: 'GET',
                  dataType: 'text',
                  success: function(pData) { $(div_in).html(pData); }
                });
        }
        catch(e) {
            //Suppress error messages
        }
        finally {
            //Loop back into next_student() every 5000 ms (5 seconds)
            setTimeout(
                function() { next_student(workstation_in, div_in) },
                5000
            );
        }
    })('&P7_WORKSTATION_IN.', '#next_student_div');
    I was previously using the following syntax but switched to apex.server.process to see if it would help (I'm wondering now if I need to pass in p_flow_id, p_flow_step_id, and/or p_instance or if apex.server.process handles that automatically?):
    <script type="text/javascript">
    <!--
    var refresh_region = function( workstation_in, div_in ) {
        $.get(
            'wwv_flow.show', 
            {"p_request"      : 'APPLICATION_PROCESS=F_NEXT_STUDENT',
             "p_flow_id"      : $v('pFlowId'),      //app id
             "p_flow_step_id" : $v('pFlowStepId'),  //page id
             "p_instance"     : $v('pInstance'),    //session id
             "x01"            : workstation_in
            },
            function(data) {
                $(div_in).html(data);
            }
        );
        setTimeout(function() { refresh_region( workstation_in, div_in ) }, 5000);
    }
     
    refresh_region( '&P7_WORKSTATION_IN.', '#next_student_div' );
    //-->
    </script>
    Incidentally, page 8 uses a similar AJAX call as the above, *$.get*, and it's also producing a lot of sessions (though less because there are only two workstations displaying this page). Also, I added the try/catch/finally very recently as I was occasionally being presented with an error dialog from IE stating "Error: error - unknown", and I wanted to suppress this for now.

    Regards,
    Tadeusz
  • 16. Re: Scalability Issues - Too Many Active Sessions?
    Patrick Wolf Employee ACE
    Currently Being Moderated
    Hi,

    apologies, my SQL statements where wrong. They where missing a DISTINCT. It should have been
    select application_id, count(distinct apex_session_id)
      from apex_workspace_activity_log
     where view_date >= trunc(sysdate)
     group by application_id
     order by 2 desc;
    
    select page_id, count(distinct apex_session_id)
      from apex_workspace_activity_log
     where application_id = <enter application id>
       and view_date >= trunc(sysdate)
     group by page_id
     order by 2 desc;
    Regards
    Patrick
    -----------
    My Blog: http://www.inside-oracle-apex.com
    APEX Plug-Ins: http://apex.oracle.com/plugins
    Twitter: http://www.twitter.com/patrickwolf
  • 17. Re: Scalability Issues - Too Many Active Sessions?
    tdsacilowski Newbie
    Currently Being Moderated
    Hi Patrick,

    Here are updated numbers as per your query modifications:
    APPLICATION_ID     COUNT(DISTINCTAPEX_SESSION_ID)
    101     92
    4550     1
    4500     1
    4000     1
    4350     1
    
    PAGE_ID     COUNT(DISTINCTAPEX_SESSION_ID)
    12     55
    7     20
    1     18
    8     2
    5     2
    This looks a lot better. So it looks like there's only one Apex session for each individual machine. I know what's going on with page 12 so no need to worry about that... I'm fixing that currently. I'm still concerned about the number on page 4350:45
    Total Sessions: 19,295
    Distinct Users over all sessions = 2
    Sessions older than 1 day(s) = 0
    Regards,
    Tadeusz
  • 18. Re: Scalability Issues - Too Many Active Sessions?
    tdsacilowski Newbie
    Currently Being Moderated
    Looks like I may have to retract my statement about page 12 in the application. I left activity logging on for most of the day and these are the numbers that I get when I run your query for the last hour (SYSDATE-1/24):
    PAGE_ID     COUNT(DISTINCTAPEX_SESSION_ID)
    12     1445
    1     26
    7     20
    2     5
    8     2
    5     2
    10     1
    Up until a few hours ago I was using a meta tag in the header to refresh the page every 10 seconds. I know that's a no-no so I changed the code to the following:
    //Poll the DB for the next student at current station
    (function refresh_region( region_in ) {
        try {
            jQuery( region_in ).trigger('apexrefresh');
        }
        catch(e) {
            //Suppress error messages
        }
        finally {
            setTimeout( function() { refresh_region( region_in ) }, 5000);
        }
    })('#CALLED_STUDENTS');
    Can this be a result of using the apexrefresh trigger? The page is supposed to show a list of students that need to head to the left/right of the queue waiting area. So I'm essentially refreshing that report every 5 seconds. Would it be a better idea to create a stored procedure to directly output the necessary HTML and use *$.ajax* to call it or should this not make too much of a difference?
  • 19. Re: Scalability Issues - Too Many Active Sessions?
    tdsacilowski Newbie
    Currently Being Moderated
    So something strange is going on. I ran the query to determine distinct apex sessions just a few minutes ago:
    PAGE_ID     COUNT(DISTINCTAPEX_SESSION_ID)
    12     1439
    7     20
    8     2
    This seemed fine, at least for page 7 and 8 as this represents exactly how many individual machines are displaying that page. As a test, I went to page 4350:45 and purged all sessions older than 10 seconds and ran the query again right after. The results were... odd:
    PAGE_ID     COUNT(DISTINCTAPEX_SESSION_ID)
    8     3278
    12     1440
    7     240
    I noticed this behavior earlier today. All the sessions for each page seems to stabilize after some time. If I purge all old sessions there seems to be a huge spike in the sessions created.

    Any clues as to what's going on here?
  • 20. Re: Scalability Issues - Too Many Active Sessions?
    tdsacilowski Newbie
    Currently Being Moderated
    Some more updated numbers:
    @4pm
    PAGE_ID     COUNT(DISTINCTAPEX_SESSION_ID)
    12     1445
    1     26
    7     20
    2     5
    8     2
    5     2
    10     1
    
    Cleared all sessions > 15 mins
    
    @4:45pm
    PAGE_ID     COUNT(DISTINCTAPEX_SESSION_ID)
    8     12476
    12     1713
    7     240
    5     163
    1     62
    2     16
    9     2
    
    @7:45am
    PAGE_ID     COUNT(DISTINCTAPEX_SESSION_ID)
    12     1439
    7     16
    8     2
    
    @10:30pm
    PAGE_ID     COUNT(DISTINCTAPEX_SESSION_ID)
    12     1445
    1     22
    7     22
    2     15
    8     2
    9     2
    10     2
    5     2
    For some reason, purging sessions through page 4350:45 causes a surge in sessions created but after a while this stabilizes. Not sure why that's happening. As for why page 12 seems to be creating so many sessions... after examining page 7 and 12 through Chrome's web console I've determined that jQuery( region_in ).trigger('apexrefresh'); is refreshing the report using POST. Page 7, on the other hand is using apex.server.process to call an on demand process and I'm explicitly telling it to use GET instead. After examining the URL parameters that are being sent in the latter, I can see that it's using the same session ID.

    I can only assume that since apexrefresh uses POST, this is causing it to create a new session each time the function is triggered. Although I have another page that uses apexrefresh, page 1, and that doesn't seem to be creating a problem with sessions. Below is a comparison of how I'm refreshing the report:

    Page 1 (declared in the region footer):
    <script type="text/javascript">  
    <!--  
    var refresh_region = function( region_in ) {
        if( $('#apexir_ACTIONSMENU').css('display') == 'none') {
            jQuery( region_in ).trigger('apexrefresh');
        }
        setTimeout( function() { refresh_region( region_in ) }, 30000);
    }
    
    refresh_region( '##REGION_STATIC_ID#' );
    //-->  
    </script>
    Page 12 (declared in "Execute when page loads"):
    //Poll the DB for the next student at current station
    (function refresh_region( region_in ) {
        try {
            jQuery( region_in ).trigger('apexrefresh');
        }
        catch(e) {
            //Suppress error messages
        }
        finally {
            setTimeout( function() { refresh_region( region_in ) }, 5000);
        }
    })('#CALLED_STUDENTS');
    The differences are where the function is being called, how the function is being called (creating a function object vs. self-executing function), and the interval at which its being called (30 seconds vs. 5 seconds).

    Edited by: tdsacilowski on Feb 28, 2013 10:56 AM
  • 21. Re: Scalability Issues - Too Many Active Sessions?
    tdsacilowski Newbie
    Currently Being Moderated
    Just some more data... It looks as if the report refresh that I'm using (see previous post) is creating a new APEX session every time it fires (every 5 seconds):
    PAGE_ID     COUNT(DISTINCTAPEX_SESSION_ID)
    12     3421
    1     24
    7     19
    2     17
    9     3
    10     3
    8     2
    5     1
    
    APEX_SESSION_ID     COUNT(*)
    13495058099821     3
    14678950177334     3
    7931725681897     3
    13873140121382     3
    2748730558451     3
    14317551468963     3
    20155751696219     3
    932559059255     3
    12880452503703     3
    42911077146     3
    903395443261     3
    2840363375468     3
    1668872259804     3
    6960812807807     3
    2315322604653     3
    9632114645181     3
    4951238553907     3
    8504555945590     3
    17269975437104     3
    2320274002277     3
    More than 20 rows available. Increase rows selector to view more rows.
    Is this the expected functionality? Should I be overly concerned about this? The application is running fine regardless of this.
1 2 Previous Next

Incoming Links

Legend

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