Our database is Oracle 18.104.22.168 running on Linux AS 3.0. Our database is around 110GB and has 16GB RAM. Even though we have set the parameters in SGA_MAX_SIZE to 12G, shared pool size as 2G, db_cache as 4G and pga_aggregate as 800m (changed to different combinations), we are still seeing the database as fully utilized and there is also some hang in the database during peak times. When viewing through the 'top' command, it's mostly showing 0% idle. Can you please tell me what all I need to check and what all may be the reasons?
- Run Statspack reports to see the top timed events in the instance.
- Trace a session to see what SQL it's executing and the breakdown, including wait events, of its elapsed time. Use DBMS_SUPPORT (install via $ORACLE_HOME/rdbms/admin/dbmssupp.sql and prvtsupp.plb) to collect extended trace data that includes wait event data.
Dig Deeper on Oracle database performance problems and tuning