Forum Stats

  • 3,770,415 Users
  • 2,253,104 Discussions
  • 7,875,447 Comments

Discussions

Apex Listener responsible for high db-load - explanation?

KasparWalter
KasparWalter Member Posts: 7 Red Ribbon
edited Jul 3, 2017 6:00AM in APEX Discussions

In a STATSPACK Report we see a lot of DB-Time and elapsed time for statements in the module APEX Listener.

Given the top 5 statements of a snapshot for 15' below, we notice

1****) there are about 640 Page Requests (which corresponds to the monitoring of Apex itself, so that seems ok)

2****) .. 5****): there are a about 2000 other statements issued by Apex per one Page Request (259,225 + 305,786 + 281,029 + 281,121) / 638

These secondary statements sum up to a Elapsed Time of about 600" per 15', which gives a considerable load on the DB-Server (Actually, APEX is the biggest load on this DB-Server, though there is running a OLTP Application as well).

Are the execution count of the secondary Statements 2****) .. 5****) a bug, or is there an explanation for such high execution counts ?

-----------------------------------------------------------------------------------------

SQL ordered by CPU  DB/Inst: IBA/iba  Snaps: 25287-25297

-> Total DB CPU (s):           3,329

-> Captured SQL accounts for   27.0% of Total DB CPU

-> SQL reported below exceeded  1.0% of Total DB CPU

CPU CPU per Elapsd Old

  Time (s)   Executions  Exec (s)  %Total   Time (s)    Buffer Gets  Hash Value

---------- ------------ ---------- ------ ---------- --------------- ----------

304.33          638 1****)     0.48 9.1     360.42 22,740,202  922813023

Module: APEX Listener

begin f(p=>:1 ); commit;   end;

280.66      259,225 2****)       0.00 8.4     401.62      54,541,517 4091782418

Module: APEX Listener

SELECT ( SELECT 'USER' FROM SYS.ALL_USERS WHERE USERNAME = :B1 U

NION ALL SELECT 'ROLE' FROM SYS.DBA_ROLES WHERE ROLE = :B1 ) FRO

M SYS.DUAL

71.84      305,786 3****) 0.00    2.2 86.38       2,189,013 2667322177

Module: APEX Listener

SELECT /*+ result_cache */ ID, ALIAS, OWNER, NAME, CHARSET, WEBD

B_LOGGING, FLOW_LANGUAGE, FLOW_LANGUAGE_DERIVED_FROM, DATE_FORMA

T, DATE_TIME_FORMAT, TIMESTAMP_FORMAT, TIMESTAMP_TZ_FORMAT, NLS_

SORT, NLS_COMP, FLOW_IMAGE_PREFIX, AUTHENTICATION, LOGOUT_URL, P

57.30      281,029 4****) 0.00    1.7 67.37       2,521,822 4095312686

Module: APEX Listener

SELECT /*+ result_cache */ CASE WHEN IS_DEFAULT = 'Y' THEN -1 EL

SE DISPLAY_SEQ END AS DISPLAY_SEQ, HOME_URL, LOGIN_URL, THEME_ID

, GLOBAL_PAGE_ID, CONTENT_DELIVERY_NETWORK, JAVASCRIPT_FILE_URLS

, INCLUDE_LEGACY_JAVASCRIPT, INCLUDE_JQUERY_MIGRATE, CSS_FILE_UR

53.17      281,121  5****)     0.00    1.6 64.21 1,338,441 2760518003

Module: APEX Listener

SELECT /*+ result_cache */ SHORTCUT_NAME, ID FROM WWV_FLOW_SHORT

CUTS WHERE FLOW_ID = :B3 AND (BUILD_OPTION IS NULL OR (BUILD_OPT

ION > 0 AND (:B2 IS NULL OR INSTR(:B2 ,':'||TO_CHAR(BUILD_OPTION

)||':') = 0) ) OR (BUILD_OPTION < 0 AND (:B1 IS NOT NULL AND INS

Tagged:
Kiran Pawar

Answers

  • Kiran Pawar
    Kiran Pawar Member Posts: 2,951 Bronze Crown
    edited Jun 12, 2017 6:41AM

    Hi,

    user9183845 wrote:

    Please change your user handle from "user9183845" to something meaningful. Refer : Video tutorial how to change nickname available

    In a STATSPACK Report we see a lot of DB-Time and elapsed time for statements in the module APEX Listener. Given the top 5 statements of a snapshot for 15' below, we notice1****) there are about 640 Page Requests (which corresponds to the monitoring of Apex itself, so that seems ok)2****) .. 5****): there are a about 2000 other statements issued by Apex per one Page Request (259,225 + 305,786 + 281,029 + 281,121) / 638These secondary statements sum up to a Elapsed Time of about 600" per 15', which gives a considerable load on the DB-Server (Actually, APEX is the biggest load on this DB-Server, though there is running a OLTP Application as well).Are the execution count of the secondary Statements 2****) .. 5****) a bug, or is there an explanation for such high execution counts ?...

    Please provide the following info which will help pointing out the issue:

    • Detail Oracle APEX Version
    • Detail Oracle Database Version
    • Detail ORDS (formerly known as APEX Listener) Version
    • Detail Supported Java EE Application Server Version (if using one)
    • Detail JDK Version

         NOTE : Here "Detail" means the major+minor version. For e.g. Application Express 4.2.4.00.08

    Details related to JDBC configuration parameters for ORDS a.k.a APEX Listener.

    Also is the issue related to any particular page in application? If yes the details related page functionality.

    Is this issue only related to ORDS a.k.a APEX Listener? Have you tried configuring OHS (Oracle HTTP Server) and checking whether the issue still exists?

    Regards,

    Kiran

  • KasparWalter
    KasparWalter Member Posts: 7 Red Ribbon
    edited Jun 12, 2017 7:40AM

    Thanks for your answer

    • Detail Oracle APEX Version
      • 5.0.2.00.07
    • Detail Oracle Database Version
      • 12.1.0.2.0 SE
    • Detail ORDS (formerly known as APEX Listener) Version
      • WebLogic: 10.3.6.0
      • Fusion Middleware: 11.1.2.2.0
      • ORDS: 3.0.8
    • Detail Supported Java EE Application Server Version (if using one)
      • We are using weblogic
    • Detail JDK Version
      • 1.6.0_35

    Details related to JDBC configuration parameters for ORDS a.k.a APEX Listener.

    • Standard configuration by weblogic

    Also is the issue related to any particular page in application? If yes the details related page functionality.

    • Not as far as we know. There are several Applications in use and logging is activated. Like described, the page-events shown by the "monitor activity"-reports seem to match the 1****) statements. We have no logs of all the rest in "monitor activity".

    Is this issue only related to ORDS a.k.a APEX Listener? Have you tried configuring OHS (Oracle HTTP Server) and checking whether the issue still exists?

    • We don't know, we are just using weblogic
  • Kiran Pawar
    Kiran Pawar Member Posts: 2,951 Bronze Crown
    edited Jun 12, 2017 8:31AM

    Hi KasparWalter,

    KasparWalter wrote:Details related to JDBC configuration parameters for ORDS a.k.a APEX Listener.Standard configuration by weblogic

    So the issue seems to be the standard JDBC configuration you are using with ORDS.

    The ORDS side performance tuning includes setting the correct JDBC parameters in ORDS configuration files.

    Refer : http://krisrice.blogspot.in/2012/05/apex-listener-jdbcsecurity-setup.html

    So, how to tune your ORDS instance so that you will get the desired performance. For this you have to : modify JDBC parameters -> stress/load test and repeat this.

    Stress/Load testing is shown by Morten Braten in the following blog post in his blog series regarding ORDS.

    Refer : ORA-00001: Unique constraint violated: Installing Oracle XE, ORDS and Apex on CentOS - Part Four: Stress testing

    NOTE : This blog is for ORDS + Apache Tomcat, but the ORDS configuration parameters remain same in your case.

    Hope this helps!

    Regards,

    Kiran

  • KasparWalter
    KasparWalter Member Posts: 7 Red Ribbon
    edited Jul 3, 2017 6:00AM

    Thank you for your Help

    We found a problem on which we focused on for now;

    The SGA was to small. Due to this, the stats-package produced wrong data (that high Number of APEX-Statemens). This also meant that statements were thrown out of the shared pool which resolved into slow executions/bad performance.

    The size of the SGA gets regulated automatically. In the night it got smaller but did not grow fast enough in the morning... We set the minimum Size to 4GB and keep analyzing the performance..

    Kiran Pawar
This discussion has been closed.