Transcript OT4O, Operational Tools for Oracle in Practice

OT4O – Operational Tool for Oracle
OT4O IN PRACTICE
Vince PÁLDI
Emerald Software Consulting
[email protected]
Agenda
•
•
•
•
•
Introduction
OT4O structure and functions
Data Collector
Alert Monitor
Case study - The role of OT4O in systems
management
• Version 2 is on the way
All products or name brands referred in the presentation are trademarks of their respective holders.
ES-Con, the company
Operation
Products
Programs
(Business logic,
Utilities,
Individual solutions)
Services
Applications
(Banks, Insurance,
governmental,
etc.)
Operation
support
Operating environment
(Operating systems,
Database managers,
MQSeries, etc.)
Performance
control
OT4O – What is this?
• Software assistant for
– operators
– DBAs
– sysprogs
• Major tasks
– information services
– operation tasks
– performance control
• Runs on z/OS
– no need for network connection
– ISPF – the natural z/OS user interface
Info for the operation staff
Information
about z/OS:
TMON, RMF,
SDSF, SMF,
...
Operational
Tool
for
Oracle
(OT4O)
Information
about Oracle:
Client, TOAD,
User scripts,
...
OT4O structure and functions
Alert Monitor
Data Collector
Query Manager
Operator
Interface
DBA
Utilities
Activity Screens
OT4O structure and functions
ORA2
ORA3
ORA1
ISPF
SQLplus
shell
Central
ORA
OT4O
OT4O AS
-Data Collector
-Alert Monitor
Data Collector - Components
• OT4OSTC: The OT4O Data Collector and Alert
Monitor is controlled by an MVS started task or
batch job. The main program behind is C with
assembler subroutines.
• OT4OCOLL: the data collector package written in
plsql and running in the Collector Instance from a
dedicated schema, which contains the collector data
and control tables as well.
• Observed instances: OT4O collects performance
data from them using OT4OCOLL via dblink. There
are no OT4O objects in the observed instances.
Data Collector - Operation
• OT4OSTC in every minute
– collects operating system statistics
– collects address space statistics for configured Oracle services
– calls OT4OCOLL package in the Collector Database
• OT4OCOLL then does the following for each Observed
Instances over the dblink:
– reads performance values from V$ tables (samples)
– counts differences between samples
– stores the results in the Collector Schema data tables
• At the end of the collector cycle the Alert Monitor section is
started (part of OT4OCOLL)
– runs scripts to check thresholds in the collected data
– runs free-format scripts over the dblink
– generate alert if necessary
Data Collector - Components
Observed Instance
OT4OSTC
Collector Instance
batch/STC
AS
written in
C and
Assembler
with the
OT4OCOLL plsql
package and
collector tables
Observed Instance
Observed Instance
z/OS data
collector
(RMF interface)
Observed Instance
EXEC SQL
DBlink
Data Collector – data groups
Table group
Data
Notes
MVSConfig
CPU type, number of CPU-s, storage size, SU coefficient, OS version,
system & SysPlex name
at initialization time
snapshot
MVSStats
number of active address spaces and Oracle services, global CPU usage
and I/O
1 minute
interval
MVSASStats
CPU, storage and I/O data for each address spaces of the configured
Oracle services
1 minute
interval
SysStat
Global Oracle statistics: logons, sessions, I/Os, Net traffic, number of
executed SQL-s, committs and rollbacks, CPU usage
1 minute
snapshot + interval
SGAStat
SGA size, hit ratio for buffer cache, library cache, dictionary cache,
rollback nowait, memory sort
1 minute
snapshot
LibraryCache
major statistics of the namespace: gets and gethits, pins and pinhits,
invalidations and reloads
1 minute
snapshot + interval
TabSpaceFrag
table space name, number of files, total size, total free space, number of
fragments
30 minutes
snapshot + interval
FileStat
data set name, number of reads and write, time required for I/O
operations
1 minute
snapshot + interval
Data Collector - aggregation
• Regular (daily) job to build interval records:
– 30 minutes
– 1 day
– 1 month
• Base statistics for appropriate performance data:
– interval average
– interval highest
– interval lowest
• Easy reporting from aggregated data
• Different data levels (1 minute, 30 minutes, 1 day, 1
month) might have different retention period
– expired records are deleted from the Collector Database
Short demo
navigation in
collector data tables
Alert Monitor - Components
• Alert Monitor: runs in every minute after the data collection
cycle.
• Alert Definition: a single alert condition with the alerting
actions.
– assistance for alert conditions based on OT4O collected data
– free-format queries (actually plsql scripts)
• Alert Configuration: group of alert definitions. One alert
configuration may be active at a time.
• Alert Package: The active alert configuration is translated
into the Collector Database. There are no OT4O objects in
the observed instances.
• Alert Log: a table in the Collector Database with all alert
events
Alert Monitor – Alert Definition
Filters:
•number of consecutive events
•elapsed time between two alerts
Alert actions:
Alert Definition:
Timing:
•alert id.(msgid)
•cycle time
•TOD value
•triggered (event driven)
•message text
Alert Conditions:
•assisted queries
•free-format scripts
•console message
•send an e-mail
•run plsql proc
•write to alert log
OT4O Implementation at
Allianz Hungaria Insurance
a case study
Case study • OT4O in production since 2002
• Installed on production and development systems
• Primary users are:
– Oracle DBA group
– Operation Performance Control
– Application Development Quality Assurance
• Main usage areas:
– OT4O Data Collector is a main component of the
Performance Data Base
– OT4O Alert Monitor is integrated into the global alerting
process
– OT4O Query Manager is the tool to develop, test and
document DBA and oparation support scripts
Case study - OT4O in a PDB concept
TMON
extract
s
SMF
extracts
Control/M
job info
extracts
OT4O
Data
Collector
TEC
TMWI
(Tivoli
Enterprise
Console)
(Tivoli
Monitoring
for Web
Infrastructure)
TMTP
(Tivoli
Monitoring
for
Transaction
Performance
PDB (Performance Data Base)
Oracle 9i on z/OS
Common database for application performance analysis
Case study - Alert Monitor
implementation
z/OS SYSLOG
Tivoli alert
TEC
BMC AutoOPERATOR
Start
ALERT !
e-mail
Recovery
actions
whenever
possible
Case study – Alert Monitor
dynamic operation
..............
..............
..............
..............
........
Control/M (job scheduler)
activates the appropriate configuration
at the start of an operation period,
e.g. on-line, batch, volume test,
DB maintenance
Prepared alert
configurations
alert actions
OT4OSTC
Alert log
Batch jobs may
trigger the check
of specific alert
conditions
Data Collector
and
Alert Monitor
All events logged
Command log
Case study – Alert Monitor
capture heavy SQL-s
OT4OSTC
Data Collector
and
Alert Monitor
Collector Data Base
Observed
Instance #1
PL/SQL
block
Observed
Instance #2
OT4O technical
table for
the extreme
SQL statements
ALERT !
e-mail with
the text of
extreme SQL-s
Short demo
setting up an
alert
definition
OT4O Version 2
• Full support for 9.2 client code
– new ISPF driver for SQL*Plus in the TSO foreground
environment
– Data Collector address space
• Data Collector reporting samples
• Alert Monitor interface to Oracle Alert Log file
– OT4O alert for ORA-006xx error messages
– OT4O alert whenever Oracle Alert Log file is filled over
80%
• Alert definition samples
OT4O Version 2
• STATSPACK support
–
–
–
–
–
parameter settings
collect snapshots ad-hoc
select snapshots from the collection and generate reports
browse reports
archive reports
• build archive sets
• assign comments to reports
• notify collegues; handle notification lists
• append notes, document actions
• define keywords to reports
• keyword search engine
Technical prerequisites
• Operating system
– OS/390 v2, any release
– z/OS
• System components
– IBM REXX compiler (product) or
– IBM alternate REXX library (available free of charge)
• ORACLE
– 8.1.7 with OSDI
– 9i
• Simple installation
– complete installation and initial customization is possible within 2
hours
– no need for IPL or Oracle shutdown
Free trial
• 30-days trial is available
• The trial package includes everything:
– full OT4O functionality (all components)
– complete documentation
• Downloadable from our website
– trial agreement
– uid/pw to enter the site
– temporary product key to run OT4O during the trial period
• For more information:
– Visit www.es-con.hu
– e-mail to [email protected]
Questions
&
answers