DBA Tools to Discover What’s Happening Inside the Oracle Database

There are important tools found within the database realm that can be used to determine what is happening with the database. These tools are well documented, free, and reasonably easy to learn to use. Gaining familiarity with these tools is not only the responsibility of the DBA, but also database developers, user interface developers, application server experts, and even network engineers and web service architects. Only the collaboration of all the involved parties permits the following chain of events to happen:

1. System administrators notice a high volume of I/O.
2. DBAs drill down to a number of suspicious servlet calls.
3. Application server developers review their code together with server-side development experts and      find a number of issues with the way they have been treating CLOB datatypes.
4. Code is adjusted according to best practices and redeployed.
5. I/O volume drops by half.

As you can see, database monitoring is not only about the database per se. By looking at the database requests received, the types of requests, and the intervals between the requests, you can infer many of the activities that are going on outside of the database, which is useful when trying to pinpoint the possible source(s) of performance problems.

The important points to keep in mind regarding performance-related tools and features are as follows:

• Several different tools can help to optimize the database. You need to be familiar with all of the tools discussed in this chapter in order to be able to make the best choice for your particular situation.
• Oracle provides an extremely extended set of instrumentation mechanisms. Logging, tracing, and profiling, together with static and dynamic data dictionary views, allow you to create a complete picture of the internal database life. Proper interpretation of the information received from these utilities may be challenging because some of the available tools are more oriented toward helping Oracle engineers than toward helping Oracle customers.
• The RUNSTATS package by Tom Kyte provides a clear and simple way to compare coding alternatives within the same environment.
• You need to be aware of PL/SQL language environment settings. They can significantly impact different aspects of your software.
• Native compilation of PL/SQL code can help you only if your performance issue is totally within PL/SQL. It can do nothing about SQL calls.
• PL/SQL warnings are very underutilized, but they can materially improve the quality of your code.
•  Compiler flags provide a way to impact the code at compilation time. They can be very convenient for debugging and installation purposes.
• PL/Scope is another underutilized feature that can provide many opportunities to not only better understand your own code, but also enforce coding and naming standards.