Tools to monitor Oracle Database Performance on Standard Edition

Question

Here is what I am looking for:

• Works on Standard Edition i.e. No Diagnostic Pack.
• Provides both current and historical performance data.
• Good visuals for easy spotting of trends and presentation to management.

If you have used the tool, I would be interested in pros and cons you have discovered. I can come up with a list, but the insight of actually having used the tool is worth far more.

Answer 1

Foglight Perf.Analysis for Oracle is a great performance workload tool which digs very deep into Oracle via Stealthcollect technologie direct from server memory areas, so no much overhead load in the database, also possible to review historical data, change tracking, advisories and much more...

StealthCollect – Delivers deep SQL-level collection and analysis at a fraction of the overhead of running a trace or executing direct SQL against production.

Performance Advisories – Reviews workload data and produces custom reports to maximize performance through built-in analytics and business intelligence.

IntelliProfile – Tracks database behavior and identifies abnormal activity with a sophisticated baselining algorithm.

Statement-Level Wait-Event Analysis – Reveals wait-event data down to the statement level, speeding resolution of resource-related performance problems.

QuickCompare – Provides a shortcut for identifying the root causes and symptoms of performance deviations in application environments.

Integrated Reporting – Provides targeted, built-in reports. With easy-to-use wizards, you can define, customize, generate, schedule, and deliver reports on system health, performance, trends, changes, and a variety of other topics and data points.

Long-Term Performance Repository – Delivers unlimited performance and environmental data storage and dimensional analysis in an Oracle database.

Automated Change Tracking – Identifies and sends notifications of changes to server, instance, database, schema environments, and application SQL degradations.

Please also take a look at Spotlight on Oracle (RAC) is real time diagnostics from an Oracle architecture level.

We use these tools with Oracle healthchecks and performance investigations.

Pros:

• Response time analysis
• Server Based
• RAC Aware

Cons:

• Cost

Answer 2

Note: I've never used this, so please edit this answer if you have.

Lab128

Lab128 provides additional savings by implementing ASH functions and not requiring the purchase of ODP.

Pros:

• Response Time Analysis
• RAC support
• Agentless
• Only $299 for a named user connecting to two instances and only $499 for a named user connecting to an unlimited number of instances.

Cons:

• Interface isn't very polished.
• Lack of alerts.
• One writer/maintainer.
• No web interface.
• Not server based.

Answer 3

Ignite by Confio Softwar.e From their website:

Ignite for Oracle is the one Oracle database performance monitoring solution that identifies, analyzes and resolves all of the performance problems affecting applications and users depending on an Oracle database.

Ignite doesn’t just show health metrics, it correlates the most important performance data to build a complete picture of Oracle database performance for the DBA, Developer and DBA manager. Response time, queries, sessions and server resources, are all analyzed by Ignite Performance Intelligence to show both historical and real-time conditions affecting database applications.

Pros:

• Response Time Analysis
• Agentless
• Server Based
• Web interface
• Free Version for basic info
• Can be RAC and/or VMWare aware

Cons:

• Drilldowns not as in depth as OEM
• Users must be maintained independently
• Cost

Answer 4

Note: I've never used this, so please edit this answer if you have.

WISE (Workload Interface Statistical Engine) uses Statspack to show graphical performance trend information.

Pros:

• Response Time Analysis
• Enterprise Edition - $349/database or $3,900/unlimited.
• Server based.
• Alerts.

Cons:

• Interface isn't very polished.
• Web interface is extra cost and doesn't have all the features.
• Not RAC Aware

Answer 5

ASHViewer is a free alternative. Description from the readme:

ASH Viewer provides graphical Top Activity, similar Top Activity analysis and Drilldown of Oracle Enterprise Manager performance page. ASH Viewer store ASH data locally using embedded database Oracle Berkeley DB Java Edition.

It may seem that an application called ASH Viewer wouldn't work with Standard Edition because it does not have the Diagnostic pack and therefore does not have v$Active_Session_History, but it does. When a new connection is created it asks whether the target is Standard Edition or Enterprise Edition. If Standard Edition is selected it does not use v$Active_Session_History. This can be demonstrated by querying the view with control_management_pack_access set to NONE, which among other things causes the view to return no records.

Pros:

• Free
• Allows a specific time range selection (OEM can't do this)

Cons:

• Not server based (Statistics collection is repeated for each user)
• No web interface
• Requires the equivalent of System level privileges
• Interface isn't very polished (Font stretching, OS integration, etc.)
• Not RAC aware (must run separately for each instance)