Shared Component - List of Values - slow to load on Oracle Cloud

User_HP5NA

We are getting really slow performance from an APEX environment on the Oracle Cloud when defining List of Values as shared components.

When we try to open a list of values that uses a SQL statement it is taking 30-40 seconds to open the page, if it's a static list then opens right away.

Is this a known issue?

AceSobe

You are correct. There seems to be an an issue on Oracle Cloud right now when editing a Dynamic List of Values via Shared Components. I have the same application in an onsite server and problem does not occur. It may not be Apex related, but probably performance related within the Oracle Cloud environment.

fac586

Please update your forum profile with a recognisable username instead of "User_HP5NA".

We are getting really slow performance from an APEX environment on the Oracle Cloud when defining List of Values as shared components.

When we try to open a list of values that uses a SQL statement it is taking 30-40 seconds to open the page, if it's a static list then opens right away.

Is this a known issue?

It depends entirely on what the "SQL statement" is doing...

Billy Verreynne

Is this a known issue?

Web and application developers being totally and dangerously ignorant of fundamental Oracle concepts, and designing and writing shoddy applications using shoddy code?

Yes. A known issue. For many year now.

But ignorance is bliss. Making the problems someone else's problem.

"Oh no, the fault is not with me being ignorant. It is with that darn software product or technology!!" <sigh>

User_HP5NA

This is part of the APEX Development environment. On a hosted environment this seems to be fine but on the Cloud just takes ages to open. Just wondering whether SQL is doing a parse before it opens the window which runs slower on the cloud and whether this is known about by the APEX Development team.

fac586

This is part of the APEX Development environment. On a hosted environment this seems to be fine but on the Cloud just takes ages to open. Just wondering whether SQL is doing a parse before it opens the window which runs slower on the cloud and whether this is known about by the APEX Development team.

What the APEX development team knows about this is probably summed up in the blog post by Joel Kallman linked to above.

If the same query exhibits different performance across two environments then the most likely explanations are that the optimizer is choosing a different execution plan, or that there is significantly more data in one database than the other.

Check that the LOV code is actually the same.

Compare everything relevant across the environments:

• tables
• views
• indexes
• partitions
• data volumes
• data distributions
• statistics
• database parameters

Billy Verreynne

https://community.oracle.com/tech/developers/discussion/comment/16778112#Comment_16778112

The VERY SAME SQL can have DIFFERENT performance on the SAME DATA AND DATABASE.

SQL> set timing on 
SQL> select count(*) from dba_objects; 
 
       COUNT(*) 
---------------- 
        766,205 
 
1 row selected. 
 
Elapsed: 00:00:01.52 
SQL> select count(*) from dba_objects; 
 
       COUNT(*) 
---------------- 
        766,205 
 
1 row selected.

Elapsed: 00:00:00.73

Second execution is 50% faster.

You are now talking about different databases, different servers - and blaming a difference in performance on something the APEX team did - or are unaware of.

Unacceptable.

First learn and understand HOW the architecture and technology works before starting to throw blame around.

I've been around h/w and s/w technologies for some years. When something goes wrong, there are performance issues, or stability issues and the like - my first response is that I got something wrong. Only after doing a RCA and determining I did not muck it up, I'll look for the error or bug in the product. Which is almost never the case.

Yet in your view, with seemingly no understanding of the Oracle products used, your problem is due to the product and the lack of knowledge from the product team.

Unacceptable.

User_HP5NA

Unfortunately, you haven't understood what I am asking.

From within APEX Developer. Go to Shared Components and then List of Values.

Click to open one of the defined LOVs:

A static LOV (definition) will open immediately

An LOV that has a simple SQL query takes 30-40 seconds to open to be able to edit the LOV. This is internal to the APEX development environment, NOT a defined application.

If it was my coding then I would have performance tested it in isolation first and gather appropriate statistics. I understand Oracle architecture! All I wanted to know is if this is a known issue within the APEX Developer environment!

Billy Verreynne

It is YOUR problem. Not APEX's problem.

Here is the performance measured from a client browser perspective, looking at client-server calls. The 1st call is to the APEX flow engine - which runs the SQL and PL/SQL code for rendering that page and returns the HTML of the page via HTTP to the browser to process (load additional resources like CSS and JS files), and render:

So the APEX flow engine constructed a 1.4MB page, that includes a page item (select list) containing 16,926 items. And this was done, and HTML page returned across the network, in 3.76 seconds.

The P1_OBJECT item definition:

Provide your evidence that APEX is at fault, or the cause is due to the APEX team's ignorance.... or accept that your ignorance is the cause of your APEX performance issue.

User_HP5NA

I have previously always found the APEX Community to be really friendly and helpful. Unfortunately, you are NOT!

Billy Verreynne

Your expectation about a friendly community seems to be as realistic as my expectation that s/w and the APEX team not be blamed without reason or a single shred of evidence.

User_HP5NA

I was definitely NOT blaming the APEX team. I wanted to understand if there was an issue with running this on the Cloud environment due to the performance issues and whether it had been logged already as it doesn't happen on a hosted instance.

I didn't expect to be abused.