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

Cause of statements running slowly

The statements we use most hang and complete very slowly. I created a new tablespace. In the beginning it was fine, but then it started to behave the same way. What could be the reason? I have very high CPU (98-99) and memory usage of 90-95. What could I do to improve the performance?

I'm running Oracle 10.1.0.4 in AIX 5.2. It's a dev box, pretty new. The biggest activity is to test new apps and the statements running the most are create user, create table, alter table add index, PK, FK, etc.

Since Monday all these statements hang and complete very slowly. I thought for fragmentation and I created a new tablespace. In the beginning it was fine, but then it started to behave the same way. What could be the reason? I have very high CPU (98-99) and memory usage of 90-95. What could I do to improve the performance?

Trace, using dbms_monitor.session_trace_enable, one of the processes that's running slowly. Run tkprof against the resulting trace file and see where the time is being spent.

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