Well, here I sit at 30,000 feet over the Atlantic heading for London and then on to Abu Dhabi. It seems like just yesterday I was doing a webcast for the Performance Allstars series on Instant Replay and the use of history to find problems, oh wait, that was yesterday!

 

During the webcast many excellent questions where asked concerning historical data capture and usage so I thought I would answer some of the more prevalent ones in this blog entry. The star of the webcast (no it wasn’t me) was the Performance Analysis tool that Quest provides, the tool supplied virtually all of the screen shots used to demonstrate the topics discussed.

 

Performance Analysis for Oracle (PAO for short) can either execute on the same host and in the same database (not recommended) or in a different database on the same host (better) or on a separate host and instance (the best option). PAO uses a multi-tier architecture using agents to gather and report statistics back to the PAO repository. Data in the PAO environment is gathered through a low impact memory peeking methodology and is throttled at 3% of the CPU on the host on which the agent is running, though usually it runs at less than 1 percent.

 

Data in the PAO repository can be kept as long as you wish, for comparison, data in the Oracle OEM is generally kept for only 7 days unless you specifically alter the parameters that control collection of statistics. PAO only uses the stealth collection technology mentioned while OEM uses a combination of technology but mostly brute force queries against underlying V$ and base data tables. In OEM you would never run with the performance repository in the same database as your production database (unless all that you where running was Database control, which must run n the same database.) In my limited laptop environment I have tried it and under high load data collection will halt, leaving gaps in OEM statistics, not to mention the high overhead. Usually OEM also uses a multi-tier structure consisting of an agent based technology and separate repository. Both PAO and OEM can monitor multiple databases.

 

PAO consists of both statistical and performance data as well as change control tracking data such as index and table alterations, statistics collections or changes, database parameter changes and changes in execution plans. Each time change control is run (by default once every day) it examines the entire database and reports on these changes. By default OEM will track and report statistics usually retaining seven days worth, however, where the boundaries between licenseware and safe use run is never quite clear. In OEM change tracking is a cost plus with the change control pack. Detailed analysis using AWR, ADDM and ASH are also considered cost plus with the Database Performance pack. The general user of OEM needs to be aware of the many license landmines waiting to trip up the casual user as the only warning concerning what is licensed and what is not is a single screen that shows up during the install process never to seen or heard of again.

 

In general here is what is in some of  the various packs for OEM, if you are using any of these features, watch out!

 

According to Oracle 75% of you don’t buy management packs, but I’ll bet you use them! Many of the questions concerned the cost of PAO verses the cost of OEM with the needed licenses to meet the same functionality, I am afraid I will have to defer such questions to the customer service representatives, however the standard license costs of Oracle add-on packs is $3000/pack/CPU so for a single CPU monitoring instance we are talking at least several thousand dollars.

 

One interesting note is that you can actually utilize the same instance to store the OEM, PAO, and RMAN (Oracle recovery manager) repositories, I usually set things up that way when I was doing DBA work, it didn’t make much sense to have several instances for these management repositories.

 

One item demonstrated was the ability to compare two time slices, for example a time slice for a process set that is performing badly verses one where the system was performing properly. This capability allows you to see exactly what changed in the statistics making problem resolution much easier. As far as I know, OEM doesn’t have this capability, if anyone has seen it OEM please tell me.

 

OEM uses a web-based display while PAO uses a GUI client. The GUI client allows a richer graphics set to be utilized providing better graphs and data representations. PAO provides many reports, including management high-level performance overviews and detailed performance history reports including the capability to compare two time-slices. The reports can be printed, emailed or posted to a shared folder.