Troubleshoot Oracle database running slow issues

How to Solve/minimize/reduce below errors in Oracle Database:

1. ORA-00020: maximum number of processes exceeded
2. ORA-12519: no appropriate service handler found
3. Instance is slow
4. ORA 4031 : unable to allocate xxx bytes of shared memory
5. ORA 00020 : maximum number of processes exceeded
6. ORA 12519: TNS No appropriate service handler found

Errors/Root Cause:
Usually its due to Oracle memory and parameters sizing done incorrectly.

Workaround:
1. Check ADDM Report for the following
* Wait time analysis for where the system is spending most of its time – this will narrow it down to memory or CPU
* Any SQLs that are being run frequently and are consuming large percentage of memory or CPU – tune those SQLs
* PGA, SGA sizing recommendations – size the instance based on these recommendations
2. Check the AWR report for the following:
* SQLs tuning recommendations in the report
* Memory tuning recommendations in the report
3. Oracle on a 32 bit Windows Server can address only up to 2G of physical memory. ie. SGA + PGA can only address up to 2G of physical memory. If the combined size exceeds this, TNS errors will start. Workaround is to implement the /3G switch in the Windows server (Metalink Note 371983.1), so that Oracle can address up to 3G of physical memory. The other option is to migrate to a 64 bit Windows Server.
4. Check the optimizer_index_cost_adj parameter. This should be set to a value lower than 100 (recommended 40). Having this parameter at 100 can lead to a lot of full table scans inspite of having indexes

In case of any ©Copyright or missing credits issue please check CopyRights page for faster resolutions.