RE: Performance graphs

From: Iggy Fernandez <iggy_fernandez_at_hotmail.com>
Date: Fri, 9 Nov 2012 12:15:58 -0800
Message-ID: <BLU165-W43109B72B442449EEFE56CEB680_at_phx.gbl>



The best solution I've seen was based on a tool called Orca which is a portal for publishing graphs based on time series data. This is perfect because all AWR data is time series data. Orca solves the problem of publishing graphs to an enterprise wide audience. Here are some slides from a presentation by Ashish Rege some years ago at OpenWorld. Please excuse the lack of formatting; I simply grabbed the text from the slides. Please contact me directly if you would like a copy of the presentation. -- Iggy FernandezEmail: iggy_fernandez_at_hotmail.comCellphone: (925) 478 3161Blog: So Many Manuals So Little Time Author of Beginning Oracle Database 11g AdministrationPresident of the Northern California Oracle Users GroupEditor of the NoCOUG Journal What
DBA’s, SYS admin’s need

•
Database
Administrators, Sys Administrators are called on everyday to make decisions on capacity planning.

–
Which
database has trended high for CPU, Memory, I/O etc. over the last year, month, week, day versus other databases on the same server?

–
Do
these trends have some unique cyclical patterns based on year, month, week, day for different applications?

–
Do
we have the capacity to add one or more database to existing servers?

–
Where
do we re-allocate databases at the next opportunity and where do we see capacity?

–
Do
we need to buy new servers and factor this into next year’s budget?

Executive
Summary

Database
administrators and sys administrators need

consolidated data
points to provide holistic

"Capacity Trend
Analysis"     

from an intraday to multiyear via a web interface for the databases and their host servers from CPU, Memory, I/O, wait bottlenecks, throughput, and efficiencies perspective.

•
Stack
up trends for different database per server on the same graph to enable side-by-side comparison and a better awareness of percentage usage by database/application and its alignment with the business cycle.

•
Help
trend uptick or downward spiral of business measures and find correlation of those with database and server statistics to model what if analysis.  

Now
to the details

•
OEM
grid captures at a 15 minutes interval, write PERL scripts to transfer this to a trending utility that graphs those on the web with reporting

•
At
the grain of intraday to multiyear for performance counters like consumption of CPU, memory, I/O, throughput, efficiency, wait stats etc. for the different databases.

•
Capture
metadata into flat files

(1) Details per database

(2) Comparative numbers across different databases on the same or different servers

(3) Supplement the above with details from stats pack

Grid
repository raw data

•
Oracle
Enterprise Manager stores away host and database stats in the SYSMAN schema in tables like

•
sysman.MGMT_TARGETS,
sysman.MGMT_METRICS, sysman.MGMT_METRICS_1HOUR

•
 The
tgt.target_type ='host' OR tgt.target_type ='oracle_database' determines whether the data is HOST based or DATABASE related.

•
The
rollup process is the aggregation mechanism for Enterprise Manager Grid Control. Once an hour, it processes all the new raw data loaded into the Management Repository table MGMT_METRICS_RAW, calculates averages and stores them in the tables MGMT_METRICS_1HOUR and MGMT_METRICS_1DAY.

Putting
it all together

Reporting
Classifications ..

•
Thus
the reporting that comes out of the OEM is at two levels

–
Database
statistics

–
Host
statistics, this also includes side-by-side Oracle comparison for databases on that host

•
The
above statistics have associated flat files extracted out of OEM to feed this data to Orca/RRDTool to generate the graphs/trend.

•
Have
found these graphs very useful in

•
Re-alignment
decisions

•
Budget
discussions around shared infrastructure resources for different cost centers  

Orca/RRD
Tool

Orca/RRD Tool is a tool useful for
plotting arbitrary data from text files onto a directory on a Web server. It has the following features:

•
files
into the same or different plots. Creates an HTML tree of HTML and image (PNG or GIF) files.

•
Creates
an index of URL links listing all available targets.

•
Creates
an index of URL links listing all different plot types.

•
No
separate CGI set up required.

•
Can
be run under cron or it can sleep itself waiting for file updates based on when the file was last updated.

•
Configuration
file based.

•
Reads
arbitrarily formatted text or binary data files.

•
Watches
data files for updates and sleeps between reads.

•
Finds
new files at specified times.

•
Remembers
the last modification times for files so they do not have to be reread continuously.

•
Allows
arbitrary grouping of data from different sources

•
Allows
arbitrary math performed on data read from one file

Orca architecture

•
Out
of the box Orca statistics for the host

–
On
clients, orcallator.se, a component of the SE Toolkit, collects data every 5 minutes and dumps the data in orca's home directory. Orcallator.se has a startup script in /etc/init.d. This data is dumped in /home/orca/data/<hostname>

–
A
crontab entry for the orca user polls each client every 5 minutes, grabs the current data, and prunes old data. This is done via an SCP from orca's crontab on the server, and is driven by a list of hosts on the orca server at /apps/orca/xfer/hostlist.

–
 scp
-r -p -v orca_at_$host:/home/orca/data/* /apps/orca/orcallator

–
Orca's
crontab on the orca server calls /apps/orca/xfer/pull.sh which processes all files in /apps/orca/xfer/hostlist  

Out of box OS performance Statistics

•
The
Orca server app which simply checks a directory tree (/apps/orca/orcallator) every five minutes and graphs any new data which has appeared there

•
Then,
as orca on the orca server does an ssh to the new client ssh <client>" and when prompted, adds to known hosts, unless this is done for each client added, no data transfer can succeed.

•
Then,
add the client to the file /apps/orca/xfer/hostlist. This file contains a list of all clients and is used by the script 'pull.sh', driven by orca's crontab, included here. Orca's crontab also prunes some files on each client. Clients are completely passive, orcallator.se dumps performance data in orca's home directory on each client, the server collects it, processes it.

•
Orca
produces HTML files, along with "index.html". A standard Apache server is required to publish the pages.

•
The
url for viewing orca is: http://<orac server IP>/orca/  

OEM Custom statistics

•
Extract
performance data out of OEM repository into flat files, via a custom script running at an hourly frequency. These flat files are dropped into the same directory which Orca checks and processes data from to plot graphs (/apps/orca/orcallator). Thus data is
generated right off the Orca server connecting remotely to the OEM repository.

•
Thus
any data can be plotted including business measures. It is that simple

•
Using
the DBI::Oracle library to query the OEM GRID Repository; joining tables like sysman.MGMT_TARGETS, sysman.MGMT_METRICS, sysman. MGMT_METRICS _1HOUR extracting data out of into files.

•
A
timestamp is written to a snapfile that tracks time intervals already queried and reported on; the next run is based on querying the sysman repository table to find snapshots with rollup_timestamp greater than the last run e.g. 2008-11-12_19:00:00

> Date: Tue, 6 Nov 2012 13:01:05 -0600
> Subject: Performance graphs
> From: lambu999_at_gmail.com
> To: oracle-l_at_freelists.org
>
> All
> Our management has decided to put together statistics from various
> resources including OS, network, san etc into a central location and plot
> things in the form of a graph. The purpose is to track the trends in growth
> and utilization patterns in the resource usage.
>
> We can use awr to track database related information. Is there anyway to
> see data in the form a graph; upper management is interested in graphical
> outputs.
>
> --
> Thanks,
> Ram.
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
                                               

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Nov 09 2012 - 21:15:58 CET

Original text of this message