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

Application user request waits and slow database

I often see application user request waits at the client side, but the database server side is almost idle, only a few active sessions with little CPU and memory consumed each time, while users complain it takes too long to get data, or the database is too slow. What might be the problem here?

I often see application user request waits at the client side, but the database server side is almost idle, only a few active sessions with little CPU and memory consumed each time, while users complain it takes too long to get data, or the database is too slow (actually, not all user requests are sent to the database). I asked the system administrator to check any network-related issues like switch or bandwidth; all looks OK. We use our customized C++ software to talk with the database, which wraps Oracle OLEDB 9.2.0.4.12 and Microsoft MTS on Windows. My initial guess is that a communication layer is broken in middleware due to using OLEDB, but I could not prove it. What might be the problem here? Any ways to trace or track it from Oracle server or client side?
You'll want to execute and time a user operation, and trace the user's database session. You'll then know how much of the elapsed time is attributable to database activity. The next step might be to add some instrumentation to the middle tier to determine the timing of operations there. I've done a very similar analysis recently in response to concerns that the database server was a bottleneck; it turned out that a test scenario that consumed 1 minute 23 seconds of elapsed time caused only 10 seconds of database activity. The focus then shifted to improving the performance of the client (middle tier).

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