Chapter 21: Tools and Utilities

---

There are a multitude of tools available to both monitor and tune Oracle installations. This chapter will simply introduce some of those tools and categorize what each can do. It is best to explain some of the various tools graphically. Let's begin with Oracle Enterprise Manager. In Chapter 10 of Part II we looked at the SQL code tuning aspects of Oracle Enterprise Manager; this chapter attempts to focus on the physical tuning aspects.

21.1 Oracle Enterprise Manager

Oracle Enterprise Manager in relation to physical tuning can be divided into two parts. These two parts contain diagnostic and tuning tools. Diagnosis usually implies monitoring for potential problems and sometimes repairing them; tuning and analysis can even automatically repair possible performance problems.

21.1.1 Diagnostics Pack

With respect to physical tuning the interesting parts of the diagnostics pack are as follows:

• Event Monitoring.   Allows definition, automated detection of and potential automated "FixIt" jobs, which can automatically correct problems.

• Lock Monitoring.   Allows monitoring and resolution of locks in a database.

• TopSessions.   Detects and displays the top best- or worst-performing sessions ordered by a large number of different possible sorting parameters.

• TopSQL.   Detects and displays the top best- and worst-performing SQL statements, again ordered by a large number of different possible sorting parameters.

• Performance Manager.   Performance Manager wraps everything together such as details contained in the TopSessions and TopSQL tools, with extra functionality and GUI usability.

  Tip 

  The ultimate of Oracle Enterprise Manager is a GUI window interface into the Oracle Database Wait Event Interface.

Event Monitoring

Event monitoring covers areas such as performance, resource usage, how space is used, amongst many other areas. There are an absolute of plethora of events which can be included for automated detection and potential "FixIt" action to resolve problems. Figure 21.1 shows a very small subsection of the fully user definable event detection tool. The event detection construction tool is accessible from the Event menu in the Oracle Enterprise Manager Console.

Figure 21.1: Defining Events for Detection and Resolution

Note 

 Oracle Database 10 Grid   Oracle Enterprise Manager repository, events and jobs are automatically configured.

Lock Monitoring

Figure 21.2 shows lists of locks on a very busy database. Note the drilldown menu with various options including the option to kill off a session. Sometimes it is necessary to kill a session causing potentially damaging problems.

Figure 21.2: Monitoring and Managing Locking

TopSessions

The TopSessions tool allows ordered visualization of statistical performance information where the best or worst sessions can be displayed first. Figure 21.3 shows the first 10 sessions executing the most physical reads.

Figure 21.3: TopSessions Monitoring in a Specified Order

TopSQL

TopSQL is similar to the TopSessions tool where the TopSQL tool detects and displays the best- or worst-performing SQL statements in a large number of different possible orders. An example of TopSQL is shown in Figure 21.4.

Figure 21.4: TopSQL Statements

Note 

 Oracle Database 10 Grid   TopSQL is much improved in relation to more usable reporting capabilities.

Performance Manager

The Performance Manager is a very comprehensive tool. It allows an intensive GUI picture of an environment with every possible monitoring and performance metric imaginable. The Performance Manager appears complex but it is comprehensive and very easily usable.

Figure 21.5 shows the Performance Overview screen. This screen is accessible from the Oracle Enterprise Manager Console on the Tools menu. Select the Diagnostic Pack submenu. The Performance Overview interface gives an overall picture of database performance and is highly adaptable depending on requirements and what you want to monitor.

Figure 21.5: The Performance Overview Tool

Note 

 Oracle Database 10 Grid   Performance Overview charts are much improved allowing monitoring and diagnostics checking from a central point using an HTML interface.

The Performance Manager allows simple drill-down further into the separate subset areas of those shown in Performance Overview interface, to the point and beyond of drilling down into the Oracle Database Wait Event Interface. The Oracle Database Wait Event Interface will be covered in detail in the next chapter. Figure 21.6 shows a small part of the possible types of information which can be monitored with the Performance Manager.

Figure 21.6: The Performance Manager Main Screen

Figure 21.7 shows a drilldown into latch get/miss rates.

Figure 21.7: Latch Get/Miss Rates Drilldown

Finally Figure 21.8 shows a detailed analysis of latches.

Figure 21.8: Latch Analysis

21.1.2 Tuning Pack

As far as physical tuning is concerned only the Tablespace Map and Reorg Wizard tools are of interest. Other parts of the Oracle Enterprise Manager tuning pack were covered in Chapter 10 of Part II of this book.

Tablespace Map and the Reorg Wizard

The tablespace map presents a graphical representation of physical data distribution within tablespaces. After executing a tablespace analysis in the INDX tablespace of my database, two indexes are recommended for reorganization, as shown in Figure 21.9.

Figure 21.9: After Execution of Tablespace Analysis

As can be seen in the drop-down menu in Figure 21.9 various options are available:

• Tablespace Analysis.   Analyze a tablespace for problems such as fragmentation.

• Reorganize Tablespace.   Execute the Reorg Wizard.

• Reorganize Selected Segments.   Execute reorganization on selected indexes (segments) only.

• Coalesce Free Extents.   Coalescence attempts to merge empty adjacent physical areas into single reusable chunks of disk storage space. In the past, I have not found coalescence to be particularly useful.

Figure 21.10 simply shows the same screen as in Figure 21.9 except with the Tablespace Analysis Report tab selected. The table- space analysis report describes exactly why the table analysis process considers these indexes the cause for reorganization, namely fragmentation.

Figure 21.10: What Should be Reorganized and Why

The result of execution of the Reorg Wizard and tablespace coalescence on the INDX tablespace is shown in Figure 21.11. The actual reorganization process is created as an instantly executed or future scheduled job, to be executed inside the job control scheduling system, visible in the Oracle Enterprise Manager Console job scheduling screen.

Figure 21.11: After Reorganization and Coalescence on the INDX Tablespace

That's enough about Oracle Enterprise Manager. The best use of Oracle Enterprise Manager for physical and configuration tuning is diagnosis and analysis of potential bottlenecks using the GUI drill- down access paths into the Oracle Database Wait Event Interface. The Oracle Database Wait Event Interface will be covered in detail in the next chapter. There are many other non-Oracle Corporation tools for monitoring and tuning Oracle databases. Spotlight is one of these tools.

---