Performance In Rendering Demantra Worksheet (Doc ID 1356384.1)

Last updated on OCTOBER 17, 2016

Applies to:

Oracle Demantra Predictive Trade Planning - Version 7.2.0.3 and later
Information in this document applies to any platform.
***Checked for relevance on 1-Jun-2015***

Symptoms



Customer measured time spent by a worksheet in querying up the database and then displaying the data on the front end.  They came up with the data for worksheet opened up at the highest level of the hierarchy (which pulls up all the aggregated information for that account) spent around 100 second on DB and another 200 sec on displaying data on the screen.  Customer noticed that maximum CPU utilization on server is not more than 8% anytime during the process and not more than 12-15% on the client machine. None of the thread dump during the process behaves abnormally.  Customer also set the java run time parameters (-Xms256 -Xmx512)on the on client machine, but it did not made any difference.  The customer wants to understand how the data rendering happens on the worksheet. If there is any room to increase the performance of the data rendering operation?

More specifically

This is a worksheet aggregating at the highest level.
What filters are being applied on the Worksheet? - None
How long does the worksheet take to run? - 492 seconds
How many data points are loaded at the lowest level? - 2305 (from mdp_matrix item/location )
How many combinations (worksheet combinations)? How many are included in the worksheet crosstab vs. page items? - 382
Have you collected AWRs? What the conclusions coming out of your AWR report investigation? - DB activity looks pretty healthy
How much time gets spent on the DB vs. AppServer vs. Client? - approx 25% DB; 75% rest (for worksheet in discussion)
At the DB level- is time spent on IO or CPU? - IO
How much time does it take to load the worksheet? - 492 seconds
How many data points are being aggregated from the lowest levels (this means- how many item/location in mdp_matrix and how many item/location/date in sales_data)? - MDP_MATRIX: - 2340; SALES_DATA: - 112195
How many combinations are loaded? - 382
How’s the worksheet structured, meaning- how many combinations are included in the crosstab? - 2 levels (promotion and Item) in cross tab
How many series included? - worsksheet have around 150 series, around 65 are on worksheet(on 6 tabs)
Which filters are applied on the worksheet? - Scenario(Live, Sandbox); pull 98% promotion for financial Yr.


The Weblogic team then got involved and asked the following:
1. What does this Threads named “Query_Execution” Do ? seems that they query the DB, but I want to know who invokes the execution of this threads ?
2. What does the Java Client (opened when we click on the Demantra webapp worksheet report link) Do ? How does it connects to the Demantra server app ? Seems is not using WLS Worker threads.

Customer does not see a Weblogic Worker threads doing anything but rather an inactivity condition (which may be normal)  Following is the thread condition:

"[ACTIVE] ExecuteThread: '14' for queue: 'weblogic.kernel.Default (self-tuning)'" daemon prio=10 tid=041f1490 nid=679 lwp_id=282539 in Object.wait() [58a00000..589ff4f8]
at java.lang.Object.wait(Native Method)
- waiting on <9013ff90> (a java.lang.Object)
at com.demantra.tunnel.server.controller.RequestDispatcher.processComet(RequestDispatcher.java:235)
- locked <9013ff90> (a java.lang.Object)
at com.demantra.tunnel.server.controller.RequestDispatcher.processRequest(RequestDispatcher.java:91)
at com.demantra.tunnel.server.RequestDispatcherServlet.processRequest(RequestDispatcherServlet.java:79)
at com.demantra.tunnel.server.RequestDispatcherServlet.doPost(RequestDispatcherServlet.java:139)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:763)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:227)
at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:125)
at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:283)
at weblogic.servlet.internal.TailFilter.doFilter(TailFilter.java:26)
at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:42)
at com.demantra.common.servlets.AuthorizationFilter.doFilter(AuthorizationFilter.java:66)
at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:42)
at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3242)
at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)
at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:121)
at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:2010)
at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:1916)
at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1366)
at weblogic.work.ExecuteThread.execute(ExecuteThread.java:209)
at weblogic.work.ExecuteThread.run(ExecuteThread.java:181)

This is present on several WLS Worker threads. Can anybody from Demantra answer why this thread is waiting on a lock that was locked by itself after processRequest?

---------------------------------------question from client machine thread dumps--------------------

Thread “CometThread” takes approximately 2-3 minutes to read from socket.
THREAD STACK TRACE shows that this thread is not moving forward from snapshot 2011-06-15 16:47:45 TO 2011-06-15 16:49:42.
Which I don’t think is huge time. It depends on what this report does. From WLS perspective seems all OK not performance issues.
I would like to know also what the Get_Available_Combination_Thread Does.

Development responded by :

1. Query execution thread is a thread responsible for running worksheets. So having this thread in the dump points to the fact that there is a worksheet open on client.
2. Demantra client connects to the server using "home made" communication layer called tunneling which is based on standard Socket connection and implemented using Apache HTTP Client library. On the server side it is served by RequestDispatcherServlet.
3. The results for tunneling communication layer are returned asynchronously by commet thread which also uses RequestDispatcherServlet in a mode that client always has open channel. This explains why customer sees thread that "always" waits. This is by design.

Analysis from Dev was helpful and threw some light. Though eProc analysis brough following question:-

Analysis shows that most of the time in rendering the worksheet is being spent on 2 thread

a) queryExecution (is taking lot of time to read response from DB)
b) dataRunner

Support created forums http://myforums.oracle.com/jive3/thread.jspa?threadID=813438 but the customer requests a more detailed analysis for release 7203.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms