Oracle Lite Onboard Monitor (LTOM)
Oracle Database Tips by Donald Burleson-
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:
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:
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
on your browser at this point and DO NOT continue reading this
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
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.
The new Oracle LTOM tool has the following
features, centered around the concept of threshold-based data
recording (trace files):
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.
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
consider CPU by setting the (_optimizer_cost_model=cpu
these notes for turning-on Oracle CPU SQL costing.
LTOM Automatic Session
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
You can buy it
direct from the publisher for 30% off at the link, and
download a comprehensive code depot of AWR scripts.