Check the OACORE processes defined. Increase s_oacore_nprocs if needed. Also modify the Startup arguments to 2048M and permsize to 1024M
Recommendation is 100 Users per OACORE process.
Check Alert logfile for any errors related to Processes or Sessions.
Is it for all the users or only specific. Then check the Worklist or WF notifications for those specfic users.
Trace one login,post login and post results.
We have this setting.... not sure what causing very slow response for login page.
This setting restricts your service to use 1 process/cpu only. If your servers have multiple CPUs or cores, it usually is advisable to match that number but it will also depend on what else is running on the same server.
My suggestion would be to enable a user event trace before jumping to any conclusions on the cause of the issue.
This will allow you to trace the login process. If you do not see any sql taking a long time then move on to looking at other potential causes.
Its best in these situations to logically remove potential causes one at a time starting at the lowest layer. Ideally you should start from the lowest level (network) then work to the OS and so on.
You indicated that the other DB on the same server is fine. Is that other DB another 12.1 EBS instance?
Are you getting any error in Apache logs? As mentioned by many other colleagues here, you can trace the specific session to see what exactly is happening.
What type of user? sysadmin or normal user?
Yes all other databases are same database version 184.108.40.206 and EBS R12.1.3 and no traces are running.
But only some times we see login pages comes up very slow.
No errors in apache logs or alert logs.
having working at Oracle for 16 years where I diagnosed issues similar to yours I just that that you will need to follow a structured approach to diagnosing this issue.
See my previous update above.
What you may need to do is create a SR with the Oracle performance team to help guide you or choose someone knowelagble on this forum and follow their suggestions carefully.
If there are no errors, but it is just slow, then you need to perform some tuning. It involves collecting you current operational performance, resource usage and utilisation, and then analyzing them all one by one to determine where the bottleneck or slowness is. Once that is identified, then you can do something about it like allocating more resources to where more resources are required. There are tools to help you do the analysis, but it is a process that needs patience and dilligence as well as knowledge about the effects and impacts of changing certain settings.
Since you mentioned about multiple geographies it is better to check the network bandwidth too accessing the ERP from various locations.
Is every users across the globe facing the same issue? Where the ERP is hosted and so?
Yes. every user who ever wants to login to instance where slow login is having.
do we need to purge anything or cleanup anything in database like stats or something else
Since you said every user is facing the same issue , have you checked the resource usage, alert logs, awr reports, application logs for any diagnostic clues?