Solving Performance Problems in Oracle Databases

By Michael Rosenblum, Paul Dorsey on June 1, 2015

Solving the performance problems in each of nine web application process steps requires different approaches, depending upon the location of the problem.

Solving Client Machine Performance Problems (Steps 1 and 9)

Performance degradations in the client machine are usually caused by page bloat burdening the client with rich UI components that could be eliminated. Determine whether all functionality is needed in the client machine. Can some processing be moved to the application server, moved to the database, or eliminated entirely?

Resolving Performance Issues Between the Client Machine and Application Server (Step 2)

If the performance slowdown occurs during the transmission of information from the client machine to the application server, you need to decide whether any unnecessary information is being sent. To improve performance, either decrease the amount of information being transmitted or divide that information into two or more smaller requests. This will reduce the perceived performance degradation. Making web pages smaller or creating a larger number of smaller web pages is also a possible solution. If the issue is delays or bottlenecks within the network, you need to identify the component that is causing the delay.

Solving Performance Problems in the Application Server (Steps 3 and 7)

If the application server is identified as a bottleneck, examine the code carefully and/or move some logic to the database. If too many round-trips are being made between the application server and the database, are Getters/Setters being overused? Is one record being retrieved with a single query when a set can be retrieved? If performance cannot be improved because the program logic requires bundles (or thousands of round-trips), rewrite the Java code in PL/SQL and move more code into the database.

Solving Performance Problems in the Client Machine (Step 9)

If too much information is being moved to the client machine, the only solution is to reduce the amount of information. Changing architectures, making web pages smaller, and removing or reducing the number of images may help. Analyze each web page to determine whether it is too large and, if it is, reduce its memory size or divide it into multiple smaller pages.

Lessons Learned

There is much more to tuning a web application than simply identifying slow database queries. Changing database and operating system parameters will only go so far. The most common causes of slow performance are as follows:

  • Excessive round-trips from the application server to the database. Ideally, each UI operation should require exactly one round-trip to the database. Sometimes, the framework will require additional round-trips to retrieve and make session data persistent. Any UI operation requiring more than a few round-trips should be carefully investigated.
  • Large pages sent to the client. Developers often assume that all of the system users have high-speed Internet connections. Everyone has encountered slow-opening web pages that take multiple seconds to load. Occasionally, these delays are acceptable in situations with graphic-intense web browsing. However, this type of performance degradation (for example, waiting 3 seconds for each page refresh) in a production application (such as a data entry–intensive payroll application) is unacceptable. From the initial design phase, the application architecture should take into account the slowest possible network that it will need to support.
  • Performing operations in the application server that should be done in the database. For large, advanced systems with sufficient data volumes, complete database independence is very difficult to achieve. The more complex and data intensive a routine is, the greater the likelihood that it will perform much better in the database than in the application server. For example, the authors encountered a middle-tier Java routine that required 20 minutes to run. This same routine ran in 2/10 of a second when refactored in PL/SQL and moved to the database. In some organizations, this may be the primary reason why web applications perform slowly. This situation often occurs when Java programmers are also expected to write a lot of SQL code. In most cases, the performance degradation is not caused by a single slow-running routine, but by a tendency to fire off more queries than are needed.

Related Posts

Leave a Reply