Q
Problem solve Get help with specific problems with your technologies, process and projects.

Database fully utilized with hang

Our database is Oracle 9.2.0.4 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.

Our database is Oracle 9.2.0.4 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?
Two suggestions:
  1. Run Statspack reports to see the top timed events in the instance.
  2. 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

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close