Re: Reducing idle time in Apex

Okay it appears that we're on the right track :)

That is some sort of process that executes and consumes the vast majority of time in the segment.

It is most likely a page process that fetches data, and the SQL is taking its time; if you look at the page definition of that page as a developer in the application builder, you should see sections for processes and what they execute.

It's been a while since I've used Apex 5, but IIRC there is a section called "Before Regions", which is commonly used to populate the page with data. Another good candidate section could be the page processes.

On Wed, Mar 16, 2022 at 11:49 AM Mark J. Bobak <mark_at_bobak.net> wrote:

> Stefan,
>
> Thanks! I logged in as a developer and ran the app. I turned on Debug
> and clicked the problem report. Then, when the report displayed, I clicked
> 'View Debug'.
>
> I found a line that says 'Show First', consuming 21 seconds. This is by
> far the largest consumer. So, I clicked the 'View Identifier' to drill
> down. In there, I see lots of activity, SQL and PL/SQL and other stuff,
> like clear cache, http header processing, etc. Here, I found a line where
> the graph on the far right is completely filled in. Most of the other
> lines have graphs that are empty and a few have small slivers of a bar
> displayed. So, the one I found that seems the most problematic is 'Collection
> - create_collection_from_query_b, Begin fetch rows, Collection Name:
> PIPELINE_REPORTING', but now I'm stuck again, as I don't know what that
> means.
>
> Help?
>
> -Mark
>
> On Tue, Mar 15, 2022 at 10:40 PM kris rice <kris.rice_at_jokr.net> wrote:
>
>> Typical 'idle' there means the browser has no work to do meaning it's
>> waiting/idle. I would instead look at the network tab and the Timing tab.
>>
>> This shows that this http call times that you want to track
>>
>> - queued in the browser from 44.82 -> 45.20 365ms
>> - stalled 1.38 waiting on sockets
>> - sent to ords 0.18ms
>> - waiting on TTFB 2.34 <<<<<<<<< HERE is where the plsql/sql is executing
>> <<<<<<<<<<
>> - 2.1ms to download the payload
>>
>>
>>
>>
>>
>> [image: image.png]
>>
>> On Tue, Mar 15, 2022 at 9:53 PM Stefan Knecht <knecht.stefan_at_gmail.com>
>> wrote:
>>
>>> When logged on as a developer (log on to the workspace itself, not just
>>> the application directly) you will see the developer toolbar at the bottom
>>> of the screen. From there you can enable debug mode, load the page, and
>>> then view the debug output. It will show you a detailed breakdown of where
>>> the time is being spent.
>>>
>>>
>>>
>>> On Wed, Mar 16, 2022 at 8:26 AM Mark J. Bobak <mark_at_bobak.net> wrote:
>>>
>>>> Thanks Mark
>>>>
>>>> Rich,
>>>>
>>>> We are using ORDS/Tomcat/Apache, with AJP connection for
>>>> Apache<-->Tomcat.
>>>>
>>>> -Mark
>>>>
>>>> On Tue, Mar 15, 2022 at 2:47 PM Rich J <rich242j_at_gmail.com> wrote:
>>>>
>>>>> What are you using to serve up the APEX pages? I believe APEX 5 still
>>>>> supported the embedded PL/SQL gateway. It's very slow for even a single
>>>>> user, although I don't recall where in the response time that slowness
>>>>> occurred. Once I converted to ORDS/Tomcat, the response was snappy...
>>>>>
>>>>> My $.02,
>>>>> Rich
>>>>>
>>>>> On Tue, Mar 15, 2022 at 12:35 PM Mark W. Farnham <mwf_at_rsiz.com> wrote:
>>>>>
>>>>>> I don’t know Apex much.
>>>>>>
>>>>>>
>>>>>>
>>>>>> The only reason I’m chiming in is that “idle” could be send more data
>>>>>> to client with some problem getting the “ack” to send more. The tools that
>>>>>> have a built-in gimme fifty rows and then wait for a keyboard entry to send
>>>>>> more crank up time similarly.
>>>>>>
>>>>>>
>>>>>>
>>>>>> IF that or something like it is the problem I bet some APEX expert
>>>>>> could tell you how to fix it unless it is a network eccentricity or the
>>>>>> display machine is messed up repainting graphics.
>>>>>>
>>>>>>
>>>>>>
>>>>>> Good luck Mark. One time in Salt Lake City Jerry and I tuned the
>>>>>> database query in question until the database non-idle time was a blink,
>>>>>> but we couldn’t get the output to load on a PC in another building any
>>>>>> faster than (drumroll)… 44K. Outputting the report output to a machine in
>>>>>> the database server room made the job run in about blink plus a tiny amount
>>>>>> proportional to the size of the output. And I only mention that in case
>>>>>> your 3 seconds is fast enough, there is likely nothing to remove elapsed
>>>>>> time on the database server.
>>>>>>
>>>>>>
>>>>>>
>>>>>> mwf
>>>>>>
>>>>>>
>>>>>>
>>>>>> *From:* oracle-l-bounce_at_freelists.org [mailto:
>>>>>> oracle-l-bounce_at_freelists.org] *On Behalf Of *Mark J. Bobak
>>>>>> *Sent:* Tuesday, March 15, 2022 12:55 PM
>>>>>> *To:* ORACLE-L
>>>>>> *Subject:* Reducing idle time in Apex
>>>>>>
>>>>>>
>>>>>>
>>>>>> Hi guys,
>>>>>>
>>>>>>
>>>>>>
>>>>>> I'm running an Apex app (5.0.3) on Oracle Database 12.1.0.2. (Yes, I
>>>>>> know these are old versions.)
>>>>>>
>>>>>>
>>>>>>
>>>>>> When running a specific report, I get a very slow response. If I do
>>>>>> a right-click on the page, and click 'Inspect' and then click the
>>>>>> performance tab, and reload, I see that almost all (over 30 seconds or a 33
>>>>>> second response time) is 'Idle' time.
>>>>>>
>>>>>>
>>>>>>
>>>>>> What do I do with that? Can someone help me understand this? I
>>>>>> assume this is from the client (i.e. web server) point-of-view?
>>>>>>
>>>>>>
>>>>>>
>>>>>> I've been doing databases for many (many!) years, but I'm not an Apex
>>>>>> or web developer type, and never have been. The last time I was a
>>>>>> developer it was
>>>>>>
>>>>>> all about C and Pro*C.
>>>>>>
>>>>>>
>>>>>>
>>>>>> Can someone give this old dinosaur a clue here?
>>>>>>
>>>>>>
>>>>>>
>>>>>> Thanks,
>>>>>>
>>>>>>
>>>>>>
>>>>>> -Mark
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>

--
http://www.freelists.org/webpage/oracle-l




image/png attachment: image.png