ASEMON
JEAN-PAUL MARTIN
May 2015
Agenda
What is ASEMON
Use cases of ASEMON
Features for SAP ASE
Installation of ASEMON
Public
What is ASEMON
Public
ASEMON
What is Asemon
ASEMON is a performance monitoring tool
ASEMON is developed and maintained by SAP Principal Consultant JeanPaul Martin ([email protected])
ASEMON helps to monitor the activity of various SAP database solutions
like: SAP ASE, SAP IQ, SAP SRS and Replication Agent for ORACLE
Two components
ASEMON_LOGGER :
JAVA based program, collects the activity of the monitored server and records it within a
repository
ASEMON_REPORT :
It is the HTML based GUI of ASEMON
Public
Use cases of ASEMON
Server metrics help to:
Monitor in real time the activity of production database server
Focus on performance pain points and investigate performance issues in
real time or in forensic situation
Retrieve useful performance metrics
Compare the activity of servers:
within particular timeframes
before and after an upgrade or new application software rollout
Query metrics help to:
Retrieve actual queries and their arguments
Retrieve explain plan of queries
Public
More about ASEMON
Asemon :
First version of ASEMON for SYBASE ASE was delivered in 2004
ASEMON is a best friend of DBAs and developers. It fills the gap between both
worlds.
ASEMON is used by hundred of people worldwide every day in production, QA
or development environments.
ASEMON is offered as a GNU General Public License version 2.0. Every week
around 50 downloads are observed.
The download page is http://sourceforge.net/projects/asemon/
Public
Type of monitored servers
Server
Version
Counters interfaces
SAP ASE
16.0, 15.7, 15.5, 15.0,
12.5.4
MDA interface
SAP IQ
12.7, 15.4, 16.0
Catalog views, sp_iqsysmon
SAP SRS
12.6, 15.2, 15.6, 15.7
SRS Monitor Counters
SAP Replication Agent for
ORACLE
15.x
RAX counters
Public
Features for SAP ASE
Public
Features for SAP ASE
Available report
pages for SAP ASE :
Public
Features for SAP ASE
Server level information
Public
SAP ASE : Summary
Summary : 1-page for the monitored server activity
User CPU utilization
System CPU utilization
CPU breakout per engine
Cache utilization utilization
Disk device utilization
Network IO utilization
Physical IO, Logical IO and CPU
breakout per application
Number of Users
Number of Active Users
Procedure cache utilization
Stored procedure throughput
Procedure cache breakout per
module
Lock utilization
Blocking lock statistics
Device statistics breakout table
System Wait table
Cache statistics table
Logs contentions
Replication Agents statistics
Public
SAP ASE : Summary
Summary : 1-page for the monitored server activity
Engines breakout
Activity
summary
CPU utilization
Public
SAP ASE : Summary
Summary: 1-page for the monitored server activity
Cache
Activity
Disk
Activity
Network
Activity
Public
SAP ASE : Summary
Summary: 1-page for the monitored server activity
User
connections
Active
User
connections
Procedure
cache
Activity
Public
SAP ASE : Summary
Summary : 1-page for the monitored server activity
Stored
Procedures
throughput
Procedure
cache
module
breakout
Lock
usage
Public
SAP ASE : Summary
Summary : 1-page for the monitored server activity
Procedure
cache
Modules
Statistics
Lock
situation
Analysis
Which table
was
blocked?
Public
SAP ASE : Summary
Summary : 1-page for the monitored server activity
Transaction
Log contention
Replication
Agent Activity
Public
SAP ASE : Other reports
Other server options allow to focus on specific server areas:
Objects statistics
Cached objects
Process activity
Locking situations
Deadlocks situations
Procedures activity (derived from captured statements)
Compiled objects activity (procs, dyn. statments, trig)
Fragmentation
Sp_sysmon
Spinlocks
Long running transactions
Disk devices activity
Statements (long running or in statement cache)
Public
SAP ASE: Process activity
Process activity: Lists processes and their resources consumption
Limit results
as n rows
Cumultative
CPU
consumption
Login time of
the session
Cumultative
Logical IO
consumption
Session ID
Cumultative
Disk IO
consumption
Public
User name
SAP ASE : Blocking locks
Locks: Lists blocked processes characteristics
Lock state:
Blocking or
Blocked
Timestamp of
the blocking
situation
Database of the
locked object
Blocking
duration
in
second
Name of the
locked object
User name
Public
Session ID
SAP ASE : Fragmentation
Fragmentation: Lists fragmented tables and indexes
Number of
Forwarded rows
Space utilization
Database
name
Number of
Deleted rows
Large IO
efficiency
Table name
Index name
Data page
cluster ratio
Index page
cluster ratio
Public
Locking
scheme
SAP ASE : Compiled objects
Compiled objects : stored procs, dyn.statements, triggers,
Click here for
Stored procedure
activity history
On the right :
avgCPUTime
sumCPUTime
avgLogicalReads
sumLogicalReads
AvgEexcTime
SumExecTime
avgPhysicalReads
sumPhysicalReads
Nb. Plans seens
during the interval
Stored procedure
Nb. Of executions
Statement in stmt
cache
Public
SAP ASE : Statement cache
Statement cache : captured statements in statement cache
IO statistics per
statement
Hashkey of statement
Nb. executionsl
Statement SQL
Click on a row and see
statement details, including
plan captured in XML
Drill down
Public
SAP ASE : plans from XML
Check this box to
see raw XML
generated by ASE
SQL Text
Parameters (first
exec and value
when plan
captured)
Statement statistics
Plan with optimiser
estimates and
execution statistics
Public
Public
Unique feature of ASEMON:
ASEMON is able to generate
sp_sysmon output after the event
Sp_sysmon: Generates sp_sysmon output
SAP ASE : sp_sysmon
SAP ASE : spinlocks
Spinlocks : Generates spinlocks report
%
Spinlock
contention
Spinlock
name
Number of
spins
Spins per
waits ratio
Spinlock
grabs
Spinlock
waits
Public
SAP ASE : Long transactions
Syslogshold : Lists long running transactions
Start time of the
transaction
Session ID
Duration of the
transaction
Public
SAP ASE : Devices
Devices : Reports Disk device activity
Device name
Avg response time for writes
Physical Writes
APF Reads
Avg response time for reads
Physical Reads
Drill down
To drill down for a specific device, just click on its row
Public
SAP ASE : Devices
Devices : Drill down specific device activity
Throughput for
Reads, APF Reads,
Writes
operations
Service Time
Average
Device
Contention
%
Here you have information for a specific device
Public
Features for SAP ASE
Object level information
Public
SAP ASE : object level information
Besides server level information aimed to get a general insight of
a database server it is often necessary to drill down on object
level for:
Specific table
Specific statement or stored procedure
Specific user process
ASEMON offers a very handy interface to drill down to object level
and quickly retrieve very relevant and useful information.
Examples in next slides.
Public
SAP ASE : Table activity
First example :
Assume you suspect that your database server performs a lot of
physical I/Os.
But you do not know what tables are involved and what queries
cause these physical I/Os?
Public
SAP ASE : Table activity
Objects Stats : Lists tables, indexes information
Observation
Timeframe
Export result
set as an
Excel
spreadsheet
Focus on
20 first
rows
Logical
I/Os
Physical
I/Os
name
Database
name
Table
name
Index
ID
Index
Object information is aggregated for the given observation timeframe.
For instance, for the table MSEG (first row in blue) 1,340,871 physical I/Os were performed during the observation timeframe.
Tip:
Limit the result set to 20 first rows initially.
To sort the result set on a particular criteria (physical I/O, logical I/O, rows inserted ) click on the radio button
Drill down
To drill down for a specific object, just click on its row, for example MSEG table.
Public
SAP ASE : Table activity
Objects Stats : Lists tables, indexes information
Logical IOs
Throughput
DATA + INDEX
DML operations
Throughput
Physical IOs
Throughput
Here you have the drill down information for table MSEG and its indexes.
Public
SAP ASE : Table activity
Objects Stats : Lists tables, indexes information
Physical IOs
For index #2
Physical IOs
For index #3
Physical IOs
For index #4
Here you have the drill down information for table MSEG and its indexes.
Public
SAP ASE : Table activity
Objects Stats : Lists tables, indexes information
At the bottom of the page, you have the statements related to the MSEG table.
Start time
Of the
statement
Elapsed
Application
Statement
name
line
Physical IOs
time
in second
Client
CPU Time
hostname
Session
ID
Procedure
name or
Statement
name
Logical IOs
Wait Time
(disk IO,
lock)
Plan
available ?
At the bottom of the page, you will find the statements related to the current table
Drill down
To drill down for a specific statement, just click on its row.
Public
SAP ASE : Table activity
Objects Stats : Lists tables, indexes information
Detail information of the statement
Number of affected rows
Elapsed time
[]
Start Time
Logical Reads
[]
Physical Reads
Wait Time
Cpu Time
Table information
Statement text
Here you have execution information of the current statement.
Public
SAP ASE : Table activity
Objects Stats : Lists tables, indexes information
Table information
Statement text
SQL Plan
Here you have execution information of the current statement.
Public
SAP ASE : Statement activity
Second example :
Assume you want to check the statements run on the server. You
want to focus on long running statements.
Public
SAP ASE : Statements activity
Statements: Lists statements information.
Logical Reads
In second
CPU Time
Duration
Name
Wait Time
Plan
available ?
Application
Physical
Reads
To drill down for a specific statement, just click on its row
Drill down
Public
SAP ASE : Statements activity
Statements: Lists statements information.
Detail information of the statement
Number of affected rows
[]
Logical Reads
Elapsed time
[]
Start Time
Physical Reads
Wait Time
Cpu Time
Table information
Statement text
Here you have execution information of the current statement.
Public
SAP ASE : Statements activity
Statements: Lists statements information.
Table information
Statement text
Of the statement
Argument values
Runtime metrics
SQL Plan
SQL Plan
Runtime metrics
Here you have execution information of the current statement.
Public
Installation
Public
Download ASEMON
ASEMON is available at: http://sourceforge.net/projects/asemon/
Current version is ASEMON 2.7.19 (05 may 2015)
Download both components:
ASEMON_LOGGER: asemon_logger_V2.7.19.tgz
ASEMON_REPORT:
asemon_report_V2.7.19.zip
or
Windows only - bundle of Apache/PHP/ASEMON_REPORT/OpenClient:
AsemonReportSRV_V2.7.19.zip
Public
Setting up ASEMON_LOGGER
Create a database for archiving the metrics
Extract the ASEMON_LOGGER distribution
(asemon_logger_V2.7.19.tgz)
Fill $SYBASE/interfaces file of the ASEMON_LOGGER host with
archive dataserver and monitored dataserver
Archive dataserver
Monitored dataserver
Public
Setting up ASEMON_LOGGER
Create a XML configuration file. You can duplicate
sampleConfig.XML template
Fill it the XML configuration file:
<?xml version="1.0" encoding="UTF-8"?>
<Config>
[ ]
<ArchiveSrv>
<name> ASEMON</name>
<user> sapsa </user>
<useKerberos> NO </useKerberos>
<database> asemon </database>
<charset> </charset>
<GranteeList> </GranteeList>
<poolsize> 1 </poolsize>
<packet_size> </packet_size> <!-- not mandatory. If defined, overrides default network packet size of the archive server -->
<useLongSrvNames> NO </useLongSrvNames> <!-- Put YES here if server names have more than 20 chars and archive server version is V15 or
greater -->
</ArchiveSrv>
Aliased DBO is recommended
Fill the name of the archive dataserver and the
name of the login used to archive metrics.
This login must have monrole role.
<MonitoredSrv>
<SRV>
<name> PRODUCTION</name>
<user> sapsa </user>
<useKerberos> NO </useKerberos>
<charset> </charset>
<packet_size> </packet_size> <!-- not mandatory. If defined, overrides default network packet size of the monitored server -->
<srvDescriptor> ASE </srvDescriptor>
<purgearchive daysToKeep="90" deleteSleep="100" />
</SRV>
</MonitoredSrv>
and the name of the collector login.
Fill the name of the monitored dataserver
Public
Running ASEMON_LOGGER
$ ./asemon_logger.sh -c ./PRODUCTIONconfig_file.xml
Run ASEMON_LOGGER to start the
collection and the archiving of the
metrics Mention the .XML file
2015/02/19 09:54:23.638 main - Start Asemon_logger Version V2.7.18
2015/02/19 09:54:23.676 main - Current directory is : /work/ASEMON
2015/02/19 09:54:23.676 main - Java version : 1.7.0
2015/02/19 09:54:23.677 main - Classpath is :
/work/ASEMON/dist/Asemon_logger.jar:/work/ASEMON/lib/jdom.jar:/work/ASEMON/lib/xerces.jar:/work/ASEMON/lib/java-getopt1.0.9.jar:/work/ASEMON/jConnect-7_0/classes/jconn4.jar:/work/ASEMON/jConnect-7_0/classes/jTDS3.jar
2015/02/19 09:54:23.678 main - Config file used : ./PRODUCTIONconfig_file.xml
2015/02/19 09:54:24.659 main - Srv found in interfaces or SQL.INI file. Host=asemon_host Port=4901
ASEMON_LOGGER will archive metrics within
ASEMON.asemon archive database
2015/02/19 09:54:24.657 main - Try to connect to srv : ASEMON
2015/02/19 09:54:24.659 main - Using password from passwords file for 'ASEMON.sapsa'
2015/02/19 09:54:24.929 main - Connected to archive server : ASEMON Database : asemon
2015/02/19 09:54:24.931 main - Try to connect to srv : PRODUCTION
2015/02/19 09:54:24.932 main - Using password from passwords file for 'PRODUCTION.sapsa'
2015/02/19 09:54:25.027 main - connectMonitoredASE - connected to : PRODUCTION Version : 1570
ASEMON_LOGGER monitors PRODUCTION
database server. It will run MDA queries
against this server.
2015/02/19 09:54:24.932 main - Srv found in interfaces or SQL.INI file. Host=production_host Port=4901
2015/02/19 09:54:25.028 main - Time difference (ms) between ASE and asemon_logger (positive when ASE is in advance) : 69422
2015/02/19 09:54:25.207 PRODUCTION_AmStats
- Start thread.
2015/02/19 09:54:25.217 PRODUCTION_AseDbSpce - Start thread.
[]
2015/02/19 09:54:25.447 PRODUCTION_WClassInf - Start thread.
2015/02/19 09:54:40.320 PRODUCTION_MonSQL
- Start thread.
- MonSQL BootID = 6
ASEMON_LOGGER starts logger threads.
2015/02/19 09:54:25.449 PRODUCTION_WEvInf
ASEMON_LOGGER is secure. The first time it boots, it will prompt for password of archive
dataserver login and the monitored server login. Then ASEMON_LOGGER will stored them
encrypted.
Public
Setting up ASEMON_LOGGER
Enable Monitoring on ASE via sp_configure
"Monitoring, following required:
'max SQL text monitored' = 2048 (or more)
'enable monitoring' = 1
'object lockwait timing' = 1
'wait event timing' = 1
Setup the environment variables for
ASEMON_LOGGER
Example for UNIX:
JAVA_HOME=/sybase/shared/JRE-7_0_SR4FP2_64BIT
export JAVA_HOME
PATH=${JAVA_HOME}/bin:${PATH}
export PATH
http
://sourceforge.net/p/asemon/wiki/Asemon%20logger%
20installation
Public
Setting up ASEMON_REPORT
Easiest way is to run ASEMON_REPORT on Windows
Extract the ASEMON_REPORT distribution
AsemonReportSRV_V2.7.19.zip on C:\AsemonReportSRV
This distribution contains an embedded Apache HTTP server
Update C:\AsemonReportSRV\SYBASE\INI\SQL.INI with the name
of the archive server
Archive dataserver
Public
Run ASEMON_REPORT
Update the setup.bat (if needed)
Execute setup.bat
Run ASEMON_REPORT: startApache.bat :
Public
Run ASEMON_REPORT
Open a browser, connect to ASEMON URL
ASEMON URL
Monitored dataserver
Archive dataserver
Archive database
The default ASEMON URL is here: http://127.0.0.1/asemon_report/asemon_report.php
But you can update the httpd.conf file to change the Listen attribute of Apache
Public
