Improved 10g management with OEM

May 2004
Donald K. Burleson

Just a few years ago, many senior Oracle DBAs detested Oracle Enterprise Manager (OEM). Viewed as a crutch for beginners who could not memorize the command syntax, OEM was largely ignored by the veteran DBA who preferred the ease and certainty of the SQL*Plus command-line interface. This is about to change.

If you want to fully understand OEM monitoring, I recommend my book "Oracle Tuning: The Definitive Reference", over 900 pages of my best tuning tips & scripts.  Oracle OEM provides alert thresholds for all of the common metrics, but until 10g r2 you could not program automatic corrective actions.  See Oracle 10g adaptive threshold monitoring  for details.

Enterprise Manager (OEM) has undergone a major overhaul in Oracle 10g. From its humble beginnings as a GUI-based statement generator, Oracle has invested millions of dollars to make 10g EM a robust full-function product.

Oracle 10g OEM now offers far more than simple command generation and schema viewing. The 10g OEM now allows the DBA to easily manage every aspect of the 10g database, even non-traditional tasks like applying patches and scheduling jobs.

The most important new areas of EM include:

Web-accessible OEM - You can now make OEM available over the Internet with full password security and access from any Internet-enabled web browser, anywhere in the world.

Server Management - OEM now allows you to manage your 'ecosystem' of server and instances, allowing you to manage external components such as Oracle HTTP servers, Oracle web cache servers, app servers and other external database components. You can even use OEM agents to incorporate third-party tools such as Tivoli and EM has built-interfaces to global monitoring tools such as Remedy. Best of all, you can now justify getting 'root' access because OEM now gives you the ability to perform all server management functions including kernel changes and reboots. Your Systems administrator will be thrilled about this new feature.

Instant Trend Statistics - Building upon the foundation of the Oracloe8i STATSPACK utility, Oracle10g OEM interfaces with the new Workload Repository History (WRH$) tables to provide unparalleled time-series analysis. Instead of slogging through plain-text time-series STATSPACK output, you can now view exception reports in an easy-to-use GUI.

Automated Monitoring - The 10g OEM reads data from the Automatic Workload Repository (AWR) and interfaces with the Automatic Database Diagnostic Monitor (ADDM) to produce alerts and automated recommendations. If you concur with a recommendation, you can simply direct EM to issues the commands to implement the change.

PDA Support - The new 10g EM2go product allows you to use you web-enabled PDA to administer any 10g database. You can monitor Oracle, receive exception reports and even make database changes anytime, anywhere. There is nothing in the world like the felling you get when you can make a critical database change while sitting on a toilet in a seedy Tijuana bar.

External script Support - Using the incredibly stupid name of 'fetchlets', 10g OEM now allows you to embed your own SQL*Plus, UNIX shell scripts, or even the Cobol program your Grandpa wrote in 1962, can now be incorporated into OEM.

Unified Task Management - Before the advent of the dbms_job packages the DBA had to use cumbersome UNIX cron jobs (or the MS-Windows AT command) to schedule repeating Oracle task. Oracle now takes these internal and external job scheduling techniques and melds them into a single interface. The DBA can now use OEM to manage tasked scheduled at either the database level (dbms_job) or the external server level.

Better Utilities Interface - The 10g EM now has far superior interfaces into the standard Oracle utilities, especially export/import and SQL*Loader.

Environmentally-aware - Like any good citizen, 10g EM care about the environment. EM will automatically detect new hardware and place it into the pool of available resources. This feature is sure to piss-off your Systems Administrator who will not like the DBA taking-charge of the servers.

For the more sophisticated DBA, Oracle10g OEM has full support for Grid computing. Using EM, you can manage pools of ready-to-user blade servers, moving them into, and out of the Oracle architecture on an as-needed basis. Dubbed "re-provisioning", EM allows the 10g Grid DBA to manage hardware resources through a single interface.

Interface to AWR

Oracle10g introduced the Automated Workload Repository (AWR) as a built-in kernel component to collect time-series performance information for the DBA. The AWR automatically collects performance data and stores it inside a special database of Workload Repository History (WRH$) tables.

Figure 1 - ER Model for some 10g AWT views

When we use EM to drill-down into the metric list, EM displays hundreds of individual tuning metrics from the AWR and provides you with the ability to set personalized alert 'thresholds' (Figure 2). Note that EM allows you to specify numeric criteria (greater then, less than), and has full pattern matching capabilities for text-based metrics such as alert log messages.

Figure 2 - Setting alert thresholds within EM

Once set, EM will send you alerts (via e-mail, pager, telephone or EM2GO on your PDA) to alert you about a pending problem before it effects your performance.

OEM and ASH

Oracle10g OEM also interfaces with the Active Session History (ASH) component to track details on all Oracle sessions (Figure 3).

Figure 3 - A partial listing of the Oracle ASH structures

The EM interface to ASH metrics makes it easy for the DBA to drill-down and see the exact wait components of an Oracle session (Figure 4),

Figure 4 - The EM drill-down for session wait details over time

Conclusion

As a senior DBA, my favorite features are the time-series performance monitoring where I can quickly look at how important events (such as buffer busy waits, latch misses) behave over time. Although I'll probably never issue create table commands via EM, I'm starting to appreciate the display and graphical capabilities of this powerful new tool, especially as a replacement for STATSPACK and an easy-to-use tool for proactive time-series tuning.