ODP.NET

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...

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>

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.

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

undefined (0 Bytes)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.

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!

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.

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

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.

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.

He is not blaming anyone. He is asking a question about the performance of Apex on Oracle Cloud. You may not be experiencing this but the issue is not about the code he is writing, but the performance of the environment itself. Instead of lecturing him about development abilities, you should try to understand the question.

AceSobe
Many thanks for your considered response. I thought there must be an issue somewhere.

So Cloud Infrastructure is intelligent - the rain drops are like synapses firing neurons in a large neural network??
The cloud database infrastructure identifies a SQL that happens not only to be an APEX SQL, but also a SQL specifically for building an APEX LoV. And slows this down. Only LoVs and nothing else.
The cloud network on the other hand detects the TCP packets containing the LoV payload, and either slows these packets down, or drop them causing retransmission. Only LoVs and nothing else.
So there you are. Your assumption that APEX LoVs are specifically slow on cloud infrastructure, validated. And in a Trumpian world of today, this is okay as stupidity and ignorance rule.
Or you can read @fac586's posting again where he supplied a reference to Joel's comments of his experience about APEX performance issues. Who is Joel? Oracle senior development director and product manager for APEX. And understand that APEX performance is DIRECTLY a result of YOUR code and YOUR database. And not an APEX specific issue that only happens on cloud infrastructure. AS I HAVE SHOWN ABOVE.
I used a very basic and simplistic method to isolate a performance related issue with web-based client-server. The 1st step in troubleshooting a performance issue. Subsequent steps will include looking at APEX debug traces, SQL execution plans, database session wait states and so on.
So you have a very basic choice. Investigate your performance issue as shown, and at the same time educate yourselves and gain real world experience. Or feel abused by the truth and stark realities presented, and keep wearing them ~MAGA~ silly hats.

Some things I would explore
get some peer review on the 'simple' sql statement. There could be a parsing thing going on, as validation does get performed on these queries in the form of interpreted SQL. Share it here, sanitised if necessary.
Try move your SQL to a view, and have the dynamic LOV query the view, and see if the problem still exists. Perhaps compile time on the view will resolve whatever parsing problems occur.
Is it also a problem on a truly simple query on emp-dept? I've had data dictionary performance issues on shared environments before, especially when there's a large number of applications - so I might have thought this problem would be reversed. And this has been my own usage, not the builder. That said, I find the advanced shared LOV page in apex.oracle.com a bit slower than it used to be.
Are there APEX version difference differences between these instances that could be at play? I heard at one point they have been working on dictionary performance.
Sorry, Billy, I think you're off-point on this one. I'm pretty sure the OP relates to page 4000:4111, and this seems like a fair question to task, though I agree the LOV SQL should be shared.

Scott,
An example typical query is driven off a table APPLICATION_STATUS that has and ID, NAME, DESCRIPTION and audit columns there are only 6 rows. We have similar query based LOVs that aren't any more complex with similar number of rows, i.e. less than a block of data, that are all slow to load in the APEX Developer pages. The LOVs with Static values could have 20+ rows and are instantaneous to load.
I have been running on 20.1 both in the Cloud and hosted.

My issue is that cloud is blamed, APEX LoV specifically, and the expectation that this should be a known issue by the APEX team, as if it is a problem they own.
The expectation is unreasonable. Cloud and APEX LoV are symptoms of the problem. No evidence is provided that these are the actual cause.
Elementary error analysis has not been performed. As the error occurs over a complex client-server architecture, there are a number of moving parts. Anyone if which could be contributing to the problem.
Elementary problem definition is to isolate the error. Determine just where it occurs.
The WHAT needs to be known of a problem before the WHY can be addressed.
The WHAT is still unknown. Thus the error has NOT been identified. Only more unsubstantiated claims that this is an APEX LoV issue on cloud infrastructure as similar symptoms were encountered. And somehow this should be a known APEX team issue.
Making a diagnosis as to what the problem's root cause is based on these symptoms is plainly ridiculous.

For the ones still not believing there is an issue with editing LOVs in Oracle Cloud Apex:
Just create the following LOV:
select 'Y' as RETURN_COLUMN,'Yes' as DISPLAY_COLUMN from dual
After created, post your time when you want to edit the same LOV, both in Oracle Cloud vs any On-Premise Apex. My times are 40+ seconds vs 1 second. If you don't have the ability to perform this simple test, please avoid posting.

I'm not sure does it relate to APEX 20.2. I have ATP where is APEX 20.1, and get editing shared components LOV is fast.
Different ATP with APEX 20.2, it takes sometime before LOV editing pages renders.

Thanks for pointing that out. I only have 20.2 in my ATP. Same version on-premise.

We are facing same problems using Oracle Cloud, Apex 20.2. The first time we came across this problem was on "bare metal" custom installation. While searching for the cause, we switched to Autonomous Transaction Processing database which is available for Free Tier users as well.
The problem is related not just to dynamic LOVs, but also to static Lists (not LOVs). After creating empty application, go to Shared Components -> Lists -> Desktop Navigation Bar -> any list entry (even "separator"). This opens list entry "Create / Edit" form (page 4052 of app 4000). There is no SQL to parse. Still, the form takes over 30s to open:
We did not encounter the issue in apex.oracle.com environment. We have no access to Apex 20.2 on-premise installation.
While the ATP issue is just annoying "bug?", since you have to wait a bit longer to load the page, the issue on bare metal (with custom installed Tomcat) resulted in error we need to get rid of. Sometimes it falls retively quickly on this error:
Sometimes this error:
Any help with this issue would be appreciated.