This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Mar 4, 2013 8:45 AM by tdsacilowski RSS

Scalability Issues - Too Many Active Sessions?

tdsacilowski Newbie
Currently Being Moderated
Hello,

I'm having an issue with an application I built for one of the campuses at the college I work at. The application is a queuing system where there are stations for students to check in, admin stations where staff can see these students and "call" them, and displays outside each employees office that shows the student that was called. There are about 20 of these last type of display panels. I have the following code in my page footer to poll the DB for the most recent called student for a specific room:
<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>
The OnDemand process, F_NEXT_STUDENT runs the following query and returns the result:
select a.FIRST_NAME || ' ' || a.LAST_NAME
into   full_name
from   ONESTOP_QUEUE a
where  a.WORKSTATION_ID_CALLED = in_workstation_id
and    a.STATUS = 'CALLED'
and    a.QUEUE_ID = (
   select min( c.QUEUE_ID )
   from   ONESTOP_QUEUE c
   where  c.WORKSTATION_ID_CALLED = in_workstation_id
 and    c.STATUS = 'CALLED');
However, when all of these display panels are turned on (and I use code like this in other pages for similar purposes) the application becomes sluggish and eventually unresponsive. At first we had the application running off a box with Oracle XE. We eventually migrated to a full blown 11g install with APEX Listener and GlassFish. My DBA says everything looks ok on the DB side so I've been trying to dig in other areas to see where the bottleneck may be. After inspecting the Active Sessions report in APEX, I saw that there's a ton of connections being generated (> 30,000). This doesn't seem like a good thing to me and I'm trying to figure out what I'm doing wrong.

At first I was using $.post() instead of $.()get. I was also using setInterval() instead of a setTimeout() loop. However, none of these changes seemed to really help the situation much. I'm at a loss for how else to improve the performance of this application. Any suggestions on what I can try?

Most of the app's functionality is on apex.oracle.com
WORKSPACE: SCCC_TEST
USER/PASS: TEST/test
Direct URL to the page (I pass in the worksation ID): http://apex.oracle.com/pls/apex/f?p=65890:7:0::::P7_WORKSTATION_IN:ADMISSIONS_1

Thanks in advance for any help.
  • 1. Re: Scalability Issues - Too Many Active Sessions?
    tdsacilowski Newbie
    Currently Being Moderated
    One other piece of information... I've set the application session timeout value to be 0 so that each display's session will persist without creating new ones (am I understanding what this parameter does correctly?)
  • 2. Re: Scalability Issues - Too Many Active Sessions?
    scott.wesley Guru
    Currently Being Moderated
    That vital piece of information is the cause of your problems.

    The help text for that field is as follows:
    Enter a positive integer to control how many seconds a session exists and is used by this application. Leave the value null in order to revert the value to the instance level setting. Enter 0 to have the session exist indefinitely. The session duration may be superseded by the operation of the job that runs every eight hours which deletes sessions older than 12 hours.
    APEX implements a form of connection pooling, just let it do it's thing. Leave this as null or whatever you deem an appropriate length - eg, 8 hours for business applications.

    Scott
  • 3. Re: Scalability Issues - Too Many Active Sessions?
    tdsacilowski Newbie
    Currently Being Moderated
    For some reason you post made it click, even though I've been looking at that tooltip all day. Its obvious now why the active connections are growing to such huge numbers... I'm not letting Apex clean them up after a specific amount of time because I made them exist indefinitely+...

    /facepalm
  • 4. Re: Scalability Issues - Too Many Active Sessions?
    Patrick Wolf Employee ACE
    Currently Being Moderated
    Hi,

    are you sure that each AJAX request is generating a new APEX session? Because based on your uploaded app everything is fine. You can check that by executing the following query. It will also show you how long it took APEX to fulfill your AJAX request (elapsed_time). I think the elapsed time will be the key to find out why your application is getting unresponsive if too many clients are polling. The goal should be that the AJAX request finishes as fast as possible. For example 1 sec would be to much, because if you have 20 parallel AJAX requests at the same time this could have an impact on your DB. Can you share your average elapsed time?
    select apex_session_id, to_char(view_date, 'DD.MM.YYYY HH24:MI:SS') as view_date, elapsed_time, application_info
      from apex_workspace_activity_log
     where application_id = 65890
       and page_id = 7
       and view_date >= sysdate - 0.1
     order by 1, 2, 3
    I don't know how much data is stored in the table ONESTOP_QUEUE, but it might be worthwhile to tune your statement used in F_NEXT_STUDENT. At least on apex.oracle.com, the explain plan shows that it's using twice a range scan on ONESTOP_QUEUE_IDX index. For the inner MIN statement that's fine, but the outer SQL should just do a PK lookup using QUEUE_ID. That's why I would remove
    a.WORKSTATION_ID_CALLED = in_workstation_id and a.STATUS = 'CALLED'
    to force the PK index lookup using QUEUE_iD.

    I would also suggest that you make your JavaScript code on page 5 and 7 more transparent. It currently hides in region or HTML footer. I would suggest to move you JS code to the page attributes "Function and Global Variable Declaration" and "Execute when Page Loads". BTW, there is no need to use $(document).ready in "Execute when Page Loads", because internally we already use $(document).ready.

    Or instead of using your own JavaScript code you could download the "Timer" plug-in from http://www.oracle.com/technetwork/developer-tools/apex/application-express/apex-plug-ins-182042.html#dynamic and use dynamic actions instead. On page 5 you could use the "Refresh" action to refresh your classic report and on page 7 you could use a "Set Value" action to execute your query. I think that would make your page easier to read for others.

    Regards
    Patrick
    -----------
    My Blog: http://www.inside-oracle-apex.com
    APEX Plug-Ins: http://apex.oracle.com/plugins
    Twitter: http://www.twitter.com/patrickwolf
  • 5. Re: Scalability Issues - Too Many Active Sessions?
    tdsacilowski Newbie
    Currently Being Moderated
    Hi Patrick, thanks for your reply.

    I'm fairly certain that each AJAX request is generating new sessions. For example, the query you provided returned 15,419 rows, and I purged most of the sessions out last night. Here is a breakdown of the average elapsed times:
    ELAPSED_TIME     COUNT(*)
    .062          1
    .437          399
    .436          92
    .624          2
    .5          7
    .577          2
    .983          1
    .015          3597
    .639          2
    .609          2
    .671          2
    .484          2
    .733          1
    .187          1
    .047          9
    .53          8
    .717          1
    .765          1
    .827          1
    .422          478
    .514          7
    .515          16
    .453          16
    .64          3
    .655          6
    .78          1
    .686          1
    .156          1
    .452          20
    .546          4
    .531          5
    .656          1
    .702          2
    906.561          1
    .499          21
    .016          5480
    .032          27
    .046          1
    .421          1685
    .406          285
    .67          3
    .562          1
    0          3336
    .031          101
    .405          208
    .468          5
    .608          1
    .687          1
    Also, I've made the suggested change to the query. Thank you for pointing that out... not sure why I had those conditions in both the outer and sub queries when it was only necessary in the latter. I will monitor today and see if there's any improvement.

    Some additional info: my DBA suggests that it's an issue with the archive/redo logs. He was up pretty late last night trying to isolate the issue so I'm not sure what led him to this conclusion but I will update as soon as I find out.
  • 6. Re: Scalability Issues - Too Many Active Sessions?
    Patrick Wolf Employee ACE
    Currently Being Moderated
    Hi,

    I'm just curious, can you run the following statement. It should show you the number of requests for each APEX session in the last hour. Do you see that most apex sessions do just have one request or do you see a small amount of APEX sessions but with a high number of requests?
    select apex_session_id, count(*)
      from apex_workspace_activity_log
     where application_id = 65890
       and page_id = 7
       and view_date >= sysdate - (1/24)
    group by apex_session_id
     order by 2 desc;
    Because of your comment about the archive/redo logs, you might want to disable activity logging for your application, because if you have 20 browsers, issuing an AJAX request every 5 seconds you will get up to 345,600 requests each day which are logged in our activity log table. To disable logging, go to "Edit Application Properties" and set Logging = No.

    But before you do that, you should have a look if the query changes bring down your elapsed time for your AJAX calls, because in the query I see quite a number of requests which took almost 0.5 seconds, which in my opinion is too long to execute a simple query. What is the average elapsed time? Can you run
    select avg(elapsed_time)
      from apex_workspace_activity_log
     where application_id = 65890
       and page_id = 7
       and view_date >= sysdate - (1/24);
    Regards
    Patrick
    -----------
    My Blog: http://www.inside-oracle-apex.com
    APEX Plug-Ins: http://apex.oracle.com/plugins
    Twitter: http://www.twitter.com/patrickwolf
  • 7. Re: Scalability Issues - Too Many Active Sessions?
    tdsacilowski Newbie
    Currently Being Moderated
    Hi Patrick,

    Your first query returned the following:
    APEX_SESSION_ID     COUNT(*)
    6752000975392     729
    12248717716271     729
    17402892059907     729
    11778997057015     728
    6312939043127     728
    14080198324128     728
    14369854970405     727
    6737433594605     726
    1847870863970     423
    12353186667214     422
    6432779066369     422
    The avg elapsed time is: 0.35300

    One other thing to note is that, at the moment, not all the stations are up and running. The admin at the other campus just turned on half of the systems today and plans on turning on a few more tomorrow each day this week until we start seeing the slow-downs again.
  • 8. Re: Scalability Issues - Too Many Active Sessions?
    Patrick Wolf Employee ACE
    Currently Being Moderated
    As you can see from the numbers, you don't really get a new APEX session for each AJAX request. So that seems to be fine. You should see a similar number of records if you query all APEX sessions of the current day.
    and view_date >= trunc(sysdate)
    What you really have to bring down is the avg elapsed time for the AJAX call, 0.35300 is too much for a simple lookup.

    Regards
    Patrick
    -----------
    My Blog: http://www.inside-oracle-apex.com
    APEX Plug-Ins: http://apex.oracle.com/plugins
    Twitter: http://www.twitter.com/patrickwolf
  • 9. Re: Scalability Issues - Too Many Active Sessions?
    tdsacilowski Newbie
    Currently Being Moderated
    So it looks like my DBA found the issue. There was a disk space mount issue on the server (Windows Server 2008) which prevented Oracle from archiving the redo logs. Once Oracle wasn't able to write to the redo logs it started hanging. He addressed this issue and everything seems to be working properly now. He did, however, mention that the redo logs were filling up pretty fast. Once I turned activity logging off this wasn't a problem.

    As for the APEX sessions, I think I was confusing a few different things: browser sessions (used to maintain session state) and APEX/DB sessions (are these the same thing?). Unfortunately, with activity logging turned off at the moment, I can't check these numbers but once everything has been stable for a while I may turn it back on just to be able to monitor.

    As per Scott's suggestion, I changed the maximum session length to be NULL. Right now I see 17,400 browser sessions (for the last hour or so) under Home > Administration > Manage Service > Session State > Purge Sessions.

    When I try to view the report under Home > Administration > Monitor Activity > Active Sessions, the report hangs and doesn't display. My guess is that it's querying the same data as above. Again, not sure how this relates to APEX sessions.

    I still have some SQL tuning to do to get my avg elapsed time for certain pages down. What would be a reasonable range to hope for?

    Thank you all for helping me with this application. Specifically, thank you Patrick for helping me gather the appropriate data needed to better tune my code.

    Edited by: tdsacilowski on Feb 21, 2013 10:50 AM
  • 10. Re: Scalability Issues - Too Many Active Sessions?
    Patrick Wolf Employee ACE
    Currently Being Moderated
    Hi,
    As for the APEX sessions, I think I was confusing a few different things: browser sessions (used to maintain session state) and APEX/DB sessions (are these the same thing?). Unfortunately, with activity logging turned off at the moment, I can't check these numbers but once everything has been stable for a while I may turn it back on just to be able to monitor.
    APEX session = when you first view a page of an application you will automatically be assigned an APEX session ID (visible in the URL). This is used to store your session state. As long as you don't log off or close your browser this session id will be used for subsequent calls.
    DB session = when a page is requested, APEX (actually mod_plsql, EPG or APEX Listener) will pick an available DB session from the connection pool to call the APEX engine to execute your page request. It's very likely that subsequent requests will pick a different DB session.
    As per Scott's suggestion, I changed the maximum session length to be NULL. Right now I see 17,400 browser sessions (for the last hour or so) under Home > Administration > Manage Service > Session State > Purge Sessions.
    Are we talking about page 4350:45 which shows the following information

    Total Sessions: 9
    Distinct Users over all sessions = 4
    Sessions older than 1 day(s) = 0

    Where does it show 17,400 sessions for you? It almost appears that your daily APEX jobs are not running which do normally purge old APEX sessions automatically. See http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35129/dbms_jobs001.htm
    I still have some SQL tuning to do to get my avg elapsed time for certain pages down. What would be a reasonable range to hope for?
    The faster the better :-) It really depends on the available CPU power and concurrent users you have. See http://www.oracle.com/technetwork/issue-archive/2007/07-jan/o17browser-083149.html for an algoritm to calculate an estimate on how many concurrent users will be supported by your DB.

    Regards
    Patrick
    -----------
    My Blog: http://www.inside-oracle-apex.com
    APEX Plug-Ins: http://apex.oracle.com/plugins
    Twitter: http://www.twitter.com/patrickwolf
  • 11. Re: Scalability Issues - Too Many Active Sessions?
    tdsacilowski Newbie
    Currently Being Moderated
    Hi Patrick,

    UPDATE as of 3PM Eastern:

    This afternoon all users lost the ability to connect to the application. My DBA is still reviewing logs but it seems that the error isn't on the DB side. The application came back up after he restarted the Apex listener. We found a bunch of the following error in the Glassfish server.log file:
    [#|2013-02-25T14:34:39.021-0500|WARNING|oracle-glassfish3.1.2|com.sun.grizzly.config.GrizzlyServiceListener|_ThreadID=11;_ThreadName=Thread-2;|GRIZZLY0023: Interrupting idle Thread: http-thread-pool-80(73).|#]
    The max threads is currently set to 100.

    After we came back up I went to page 4350:45 and cleared out all sessions. After a couple minutes I rechecked the number of sessions on this page:
    Total Sessions: 27,674
    Distinct Users over all sessions = 2
    Sessions older than 15 minute(s) = 4
    Seems like way too many sessions to have after just a couple minutes.

    End UPDATE
    -----

    Again, thank you for taking the time to reply. Everything seems to be working fine for the past couple days, but I figured I'd provide some current data, especially since I'm still curious about all these "sessions".
    Are we talking about page 4350:45 which shows the following information

    Total Sessions: 9
    Distinct Users over all sessions = 4
    Sessions older than 1 day(s) = 0

    Where does it show 17,400 sessions for you? It almost appears that your daily APEX jobs are not running which do normally purge old APEX sessions automatically. See http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35129/dbms_jobs001.htm
    Yes, this was the page I was referring to. I just checked it now and it showed me the following:
    Total Sessions: 10,236
    Distinct Users over all sessions = 2
    Sessions older than 1 day(s) = 0
    And it does appear that the APEX jobs are running since there are no sessions older than 1 day... unless I'm interpreting this information incorrectly.

    Also, I was able to get some more data regarding page loading using the Debug info:
    14763     7751818952614     nobody     101     7     show     46     4 seconds ago     0.0000
    14760     7751818952614     nobody     101     7     show     46     9 seconds ago     0.5300
    14757     7751818952614     nobody     101     7     show     46     14 seconds ago     0.0150
    14754     7751818952614     nobody     101     7     show     46     19 seconds ago     0.0160
    14751     7751818952614     nobody     101     7     show     46     24 seconds ago     0.0160
    14748     7751818952614     nobody     101     7     show     46     29 seconds ago     0.0160
    14745     7751818952614     nobody     101     7     show     46     34 seconds ago     0.0160
    14742     7751818952614     nobody     101     7     show     46     39 seconds ago     0.0160
    14739     7751818952614     nobody     101     7     show     46     44 seconds ago     0.0160
    14736     7751818952614     nobody     101     7     show     46     49 seconds ago     0.0160
    14733     7751818952614     nobody     101     7     show     46     54 seconds ago     0.0160
    14730     7751818952614     nobody     101     7     show     46     59 seconds ago     0.0000
    14727     7751818952614     nobody     101     7     show     46     64 seconds ago     0.0160
    14724     7751818952614     nobody     101     7     show     46     69 seconds ago     0.0160
    14721     7751818952614     nobody     101     7     show     46     74 seconds ago     0.0160
    14718     7751818952614     nobody     101     7     show     46     79 seconds ago     0.0160
    14715     7751818952614     nobody     101     7     show     46     84 seconds ago     0.0150
    14712     7751818952614     nobody     101     7     show     46     89 seconds ago     0.5300
    14709     7751818952614     nobody     101     7     show     46     94 seconds ago     0.0000
    14706     7751818952614     nobody     101     7     show     46     99 seconds ago     0.0150
    14703     7751818952614     nobody     101     7     show     46     104 seconds ago     0.0150
    14700     7751818952614     nobody     101     7     show     46     109 seconds ago     0.0150
    14697     7751818952614     nobody     101     7     show     46     114 seconds ago     0.0150
    14694     7751818952614     nobody     101     7     show     46     119 seconds ago     0.0160
    14691     7751818952614     nobody     101     7     show     46     2 minutes ago     0.5310
    14688     7751818952614     nobody     101     7     show     46     2 minutes ago     0.5300
    14685     7751818952614     nobody     101     7     show     46     2 minutes ago     0.5150
    14682     7751818952614     nobody     101     7     show     46     2 minutes ago     0.5300
    14679     7751818952614     nobody     101     7     show     46     2 minutes ago     0.5300
    14676     7751818952614     nobody     101     7     show     46     2 minutes ago     0.5300
    14673     7751818952614     nobody     101     7     show     46     3 minutes ago     0.0000
    14670     7751818952614     nobody     101     7     show     46     3 minutes ago     0.5930
    14667     7751818952614     nobody     101     7     show     46     3 minutes ago     0.5300
    14664     7751818952614     nobody     101     7     show     46     3 minutes ago     0.5460
    So I'm seeing a page load time of ~0.016 or ~0.53. When I click on the details for one of the longer page view, I get the following:
    0.00000     0.00000     S H O W: application="101" page="7" workspace="" request="APPLICATION_PROCESS=F_NEXT_STUDENT" session="7751818952614"     4
    0.00000     0.04700     Reset NLS settings     4
    0.04700     0.03100     alter session set NLS_LANGUAGE="AMERICAN"     4
    0.07800     0.03100     alter session set NLS_TERRITORY="AMERICA"     4
    0.10900     0.01600     alter session set NLS_CALENDAR="GREGORIAN"     4
    0.12500     0.03100     alter session set NLS_SORT="BINARY"     4
    0.15600     0.00000     alter session set NLS_COMP="BINARY"     4
    0.15600     0.00000     ...NLS: Set Decimal separator="."     4
    0.15600     0.00000     ...NLS: Set NLS Group separator=","     4
    0.15600     0.00000     ...NLS: Set g_nls_date_format="DD-MON-RR"     4
    0.15600     0.00000     ...NLS: Set g_nls_timestamp_format="DD-MON-RR HH.MI.SSXFF AM"     4
    0.15600     0.03100     ...NLS: Set g_nls_timestamp_tz_format="DD-MON-RR HH.MI.SSXFF AM TZR"     4
    0.18700     0.00000     NLS of database and client differs, characterset conversion needed     4
    0.18700     0.01600     ...Setting session time_zone to -05:00     4
    0.20300     0.03100     Reset NLS settings     4
    0.23400     0.03100     alter session set NLS_LANGUAGE="AMERICAN"     4
    0.26500     0.01600     alter session set NLS_TERRITORY="AMERICA"     4
    0.28100     0.03100     alter session set NLS_CALENDAR="GREGORIAN"     4
    0.31200     0.03100     alter session set NLS_SORT="BINARY"     4
    0.34300     0.00000     alter session set NLS_COMP="BINARY"     4
    0.34300     0.00000     ...NLS: Set Decimal separator="."     4
    0.34300     0.00000     ...NLS: Set NLS Group separator=","     4
    0.34300     0.00000     ...NLS: Set g_nls_date_format="DD-MON-RR"     4
    0.34300     0.00000     ...NLS: Set g_nls_timestamp_format="DD-MON-RR HH.MI.SSXFF AM"     4
    0.34300     0.01600     ...NLS: Set g_nls_timestamp_tz_format="DD-MON-RR HH.MI.SSXFF AM TZR"     4
    0.35900     0.03100     ...Setting session time_zone to -05:00     4
    0.39000     0.03100     Setting NLS_DATE_FORMAT to application date format: DD-MON-YYYY HH:MIPM     4
    0.42100     0.01600     Setting NLS_TIMESTAMP_FORMAT to application timestamp format: DD-MON-YYYY HH:MIPM     4
    0.43700     0.03100     Setting NLS_TIMESTAMP_TZ_FORMAT to application timestamp time zone format: DD-MON-YYYY HH:MIPM     4
    0.46800     0.00000     ...NLS: Set g_nls_date_format="DD-MON-YYYY HH:MIPM"     4
    0.46800     0.00000     ...NLS: Set g_nls_timestamp_format="DD-MON-YYYY HH:MIPM"     4
    0.46800     0.00000     ...NLS: Set g_nls_timestamp_tz_format="DD-MON-YYYY HH:MIPM"     4
    0.46800     0.00000     NLS: wwv_flow.g_flow_language_derived_from=0: wwv_flow.g_browser_language=en     4
    0.46800     0.00000     Application 101, Authentication: PLUGIN, Page Template: 61331314513900454147     4
    0.46800     0.00000     Authentication check: No Authentication (NATIVE_DAD)     4
    0.46800     0.00000     ...fetch session state from database     4
    0.46800     0.01600     fetch items (exact)     4
    0.48400     0.00000     ... sentry+verification success     4
    0.48400     0.00000     ...Session ID 7751818952614 can be used     4
    0.48400     0.01500     ...Application session: 7751818952614, user=nobody     4
    0.49900     0.03100     ...Setting session time_zone to -05:00     4
    0.53000     0.00000     Session: Fetch session header information     4
    0.53000     0.00000     Run APPLICATION_PROCESS= request     4
    0.53000     0.00000     ...Execute Statement: begin sys.htp.p( F_NEXT_STUDENT( in_workstation_id => apex_application.g_x01 ) ); end;     4
    0.53000     0.00000     Stop APEX Engine detected     4
    0.53000     -     Final commit     4
    Again, not sure if I'm reading this correctly but it seems that the steps that are taking the most time seem to be related to NLS settings... and I have translating turned off. This is consistent with all of the longer page views. As a side note, my DBA did turn archive log mode back on this weekend.

    Again, everything seems to be running smoothly at the moment so the above data is more to help satisfy my curiosity about the inner workings of Apex.

    Regards,
    Tadeusz

    Edited by: tdsacilowski on Feb 25, 2013 3:04 PM
  • 12. Re: Scalability Issues - Too Many Active Sessions?
    tdsacilowski Newbie
    Currently Being Moderated
    Prematurely marked question as answered.
  • 13. Re: Scalability Issues - Too Many Active Sessions?
    Patrick Wolf Employee ACE
    Currently Being Moderated
    Hi Tadeusz,
    Again, not sure if I'm reading this correctly but it seems that the steps that are taking the most time seem to be related to NLS settings... and I have translating turned off. This is consistent with all of the longer page views. As a side note, my DBA did turn archive log mode back on this weekend.
    I think the above debug output is misleading. Those NLS settings are simple "alter session set ..." statements which normally take no time. I'm pretty sure if you have a look at one of the other debug identifiers like 14757 which complete under < 0.0150 you will see that those pre-initialization steps normally do not take much time.

    This looks more like that at the time when the request which showed that behavior (which took 0.53000 instead of 0.015), the database was completely occupied by concurrent requests. It would be interesting if your DBA sees a correlation in the database monitoring when your response time is going up. The timing you see is the clock time and not the CPU time consumed, which is a little bit misleading to find actual bottlenecks.

    Regards
    Patrick
    -----------
    My Blog: http://www.inside-oracle-apex.com
    APEX Plug-Ins: http://apex.oracle.com/plugins
    Twitter: http://www.twitter.com/patrickwolf
  • 14. Re: Scalability Issues - Too Many Active Sessions?
    Patrick Wolf Employee ACE
    Currently Being Moderated
    Hi Tadeusz,
    After we came back up I went to page 4350:45 and cleared out all sessions. After a couple minutes I rechecked the number of sessions on this page:
    Total Sessions: 27,674
    Distinct Users over all sessions = 2
    Sessions older than 15 minute(s) = 4
    Seems like way too many sessions to have after just a couple minutes.
    Yes that is a lot! We should find out who is causing that. If it's really your application, or and if yes which page is causing those extensive new sessions. If we know the page, we could investigate if there is an AJAX call which doesn't correctly set the session id which would trigger a new apex session each time it's called.

    Please try the following SQL statement to get those application ids with the highest session count. It will return the data since midnight.
    Note: This script only works if activity logging is enabled!
    select application_id, count(apex_session_id)
      from apex_workspace_activity_log
     where view_date >= trunc(sysdate) - 1
     group by application_id
     order by 2 desc;
    As soon as you have an application id, drill into the page ids to find out which page generates the sessions.
    select page_id, count(apex_session_id)
      from apex_workspace_activity_log
     where application_id = <enter application id>
       and view_date >= trunc(sysdate) - 1
     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
1 2 Previous Next

Incoming Links

Legend

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