Oracle Lite Onboard Monitor (LTOM) Oracle Database Tips by Donald Burleson- April 2006

Oracle has just introduced LTOM, the Oracle Lite Onboard Monitor (written by Carl Davis of the Oracle Center Of Expertise) as an important new proactive performance monitor tool for the senior Oracle DBA.  LTOM is free and can be downloaded from MOSC at this link.

LTOM joins the list of supplemental monitors that provide external server-side information about disk, RAM, network, and CPU influences on Oracle performance:

• This Ion tool

• The Oracle OS Watcher utility

• Writing your own extension tables to capture server-side vmstat information.

Oracle LTOM is unlike the "reactive" Oracle tuning tools that alert you only after your database has already experienced a slowdown. Rather, LTOM is a proactive tool, collecting real-time Data (as well as data from vmstat) and enabling a detailed trace mechanism.

LTOM differs from other support tools, as it is proactive rather than reactive. LTOM provides real-time automatic problem detection and data collection.

WARNING - LTOM is not for beginners:

The MOSC Note 352363.1 says that LTOM is an "Embedded Real-Time Data Collection and Diagnostics Platform" and explicitly notes that LTOM is only for use by experienced Oracle database administrators: "WARNING - This feature should only be used at the direction of Oracle Support or by experienced dba's". If you are not an experienced Oracle DBA, please hit the  button on your browser at this point and DO NOT continue reading this article ;).

So, What is LTOM?

Oracle LTOM is described as an OS independent (Java front-end) tool that works to trigger detailed trace collection whenever a LTOM user-defined threshold event (non-idle wait event and/or CPU usage) occurs:

The Lite Onboard Monitor (LTOM) is a java program designed as a real-time diagnostic platform for deployment to a customer site.  . .

LTOM runs on the customer's UNIX server, is tightly integrated with the host operating system and provides an integrated solution for detecting and collecting trace files for system performance issues.

The ability of LTOM to detect problems and collect data in real-time will hopefully reduce the amount of time it takes to solve problems and reduce customer downtime.

LTOM Features

The new Oracle LTOM tool has the following features, centered around the concept of threshold-based data recording (trace files):

• Automatic Hang Detection

• Manual Data Recording

• Automatic Data Recording

• Automatic Session Tracing

LTOM creates no footprint on the database. All data is written to ascii text files either oracle session trace files located in the udump or to a specific log file associated with the respective service you are using, i.e. manual recorder, auto recorder, hang detection or session recorder. The manual recorder writes vmstat, mpstat and top command info to an ascii log file.

The session recorder uses an in-memory trace buffer for the 10046 trace. Sessions are traced in-memory until they violate either a cpu or wait event rule and at that time the contents of the memory buffer is dumped to disk.
 

LTOM Wait Event Rules

LTOM implements a rule-based approach to allow the DBA to specify collection-triggering "threshold rules", based on the scalar values for Oracle non-idle wait events.

LTOM External Data Recording

LTOM notes the major shortcoming of STATSPACK and it's inability to gather data about the external server environment (disk enqueues, CPU enqueues, RAM paging, etc.). 

One of the problems with relying solely on statspack, is the inability to look at performance from a holistic point of view. Information about non-Oracle processes and the health of the operating system in terms of memory, cpu and io for example, is not collected.

LTOM also notes the issue with deriving high-detail from hourly STATSPACK snapshots, when more frequent elapsed-time metrics are needed

Further, all static data collectors are problematic in that single sample snapshots or multiple snapshots taken at 15 or 30 minute intervals can miss problems which can occur briefly during a snapshot interval and will be averaged out over the duration of the snapshot.

The data for the LTOM in-RAM data repository includes data from both the UNIX/Linux "top" and "vmstat" commands.  Note that many Oracle professionals have implemented external scripts to capture UNIX/Linux vmstat information.

LTOM Automatic Data Recording

LTOM has a rule definition components called "automatic data recording" that allows you to set thresholds by providing specific values for non-idle wait events.  When the LTOM thresholds are triggered, data collection is enabled.

LTOM allows you to define rules for external CPU thresholds.  This is important because many 64-bit databases become CPU-bound with large RAM regions.  This CPU tracing (recording amount of CPU used) is also important if you have changed the SQL optimizer (CBO) to consider CPU by setting the (_optimizer_cost_model=cpu parameter).  See these notes for turning-on Oracle CPU SQL costing.

LTOM Automatic Session Tracing

LTOM has a method to collect the session_id for offending SQL statements and a method to fire a 10046 SQL trace dump.  LTOM uses the Oracle extended SQL*Trace utility, turning on a 10046 (super-detailed) trace on a target SQL statement.

Automatic Session Tracing uses a set of rules to determine when to turn on SQL trace for individual oracle sessions, using event 10046 level 12 trace.

In sum, LTOM is an exciting new proactive Oracle utility that overcomes many of the problems with existing "reactive" database monitors.  For other proactive Oracle tools, see Ion (Workload Interface Statistics Engine).

I have exhaustively documented the OS monitoring on Oracle 10g OEM and AWR in my new book "Oracle Tuning, the Definitive Reference", highly recommended.  You can buy it direct from the publisher for 30% off at the link, and download a comprehensive code depot of AWR scripts.