Application Performance Monitoring - Databases & App Servers

  DB2  Oracle  Websphere  WebLogic

DB2

Scott Hayes, from DB2 Magazine, in his article, "Measure, Improve, Repeat" demonstrated a simple tuning methodology for accurate input from the DB2 Design Advisor.  The articles talks about using DB2 event monitors and snapshots to output DB2 database performance statistics for analysis, and gives analysis on what num mean excellent performance, and what to do in case of poor performance.  Here is an excerpt:

"Lather, rinse, repeat. This methodology, which you'll find on the back of most shampoo bottles, is one of the simplest I've ever read (although the programmer in me sees an infinite loop).

I'll show you an equally simple methodology for improving DB2 Universal Database (UDB) v.8.x for Linux, Unix, and Windows performance: measure, improve, repeat. This catchy phrase is actually a process that can yield improved business results. It also leads you to accurate input for the new DB2 Design Advisor tool, which can automatically recommend physical design changes that can improve database performance. (Of course, I'll also provide criteria for exiting the infinite loop.)

Data is at the core of every business function. If data retrieval or entry is slow, applications slow down, productivity is reduced, customers may be dissatisfied, and, ultimately, the company's ability to thrive and grow is compromised. The importance of data means that application performance issues land squarely on the DBA's back. And the only way to move the load to the appropriate network or application team members is to definitively prove, via quantitative measurements, that the database isn't causing the performance problem.

At a 20,000-foot view, the DB2 UDB tuning process includes:

• Taking performance-cost measurements to establish a benchmark and find opportunities for improvement
• Implementing physical design and configuration changes to address identified performance opportunities
• Measuring again to validate and verify that performance improvements were achieved.

In other words, measure, improve, repeat.

Step 1: Measure

The configuration settings and performance data DB2 UDB provides can seem overwhelming. Let's say you support a typical vendor application database with four bufferpools, 70 tablespaces, and 3,000 tables (of which 2,400 are active), executing 10 transactions per second. You're asked to increase the transaction rate to 30 transactions per second-without a hardware upgrade-in just 60 minutes. With one hour to triple performance, you'll have to focus on measuring what matters most. Let's look at how to do that.

Enable the monitor switches. To retrieve performance data from DB2 UDB, you'll need to first enable its monitors by using the UPDATE MONITOR SWITCHES USING BUFFERPOOL ON TABLE ON SORT ON UOW ON LOCK ON STATEMENT ON command at a command prompt. These switches also can be turned on by default at the Database Manager (DBM) configuration.

Retrieve performance data from DB2. Entering any of the commands in Table below at a command prompt will return the corresponding data.

To Retrieve This data                Enter this command
Database Manager Performance         $ db2 "get snapshot for database manager"
Database performance                 $ db2 "get snapshot for database on <DBNAME>" 
Buffer pool performance              $ db2 "get snapshot for bufferpools on <DBNAME>"
Tablespace performance               $ db2 "get snapshot for tablespaces on <DBNAME>"
Table performance                    $ db2 "get snapshot for tables on <DBNAME>"
Dynamic SQL performance              $ db2 "get snapshot for dynamic sql on <DBNAME>"
Database manager configuration       $ db2 "get dbm cfg"
Database configuration               $ db2 "get db cfg for <DBNAME>"

more at DB2 Magazine.

Oracle

The Oracle STATSPACK utility can be used to establish a baseline of database resource usage statistics and then subsequent snapshots can be taken on a periodic basis to ensure the initial database performance has not regressed. The STATSPAK utility stores values from Oracles SGA memory structures to the STATSPACK tables.

STATSPAK requires a separate tablespace to store its objects and data. The tablespace can be created with the following commands.

CREATE TABLESPACE  perfstat
Datafile  < ‘your datafile name’> Size 500m

Make sure the catdbsyn.sql script and the dbmspool.sql scripts has been run as user SYS. If they have not, run them before installing STATSPACK. The above scripts will be found in $ORACLE_HOME/rdbms/admin directory. Also make sure there is a temp tablespace defined. The STATSPACK installation scripts will ask for a temp tablespace as well as a separate tablespace for STATSPACK to use.

If the above requirements have been successfully met then the spcreate.sql script that will install the STATSPACK objects. This scripts will also reside in the $ORACLE_HOME/rdbms/admin directory. As mentioned above, the spcreate.sql script will create a user called perfstat and prompt for a password, default tablespace and default temporary tablespace.

When the spcreate.sql script is complete the STATSPAK installation is ready to use. Snapshots can be taken at the SQL> prompt  by running the following command:

execute statspack.snap

At least two snapshots are needed to generate a report. After executing statspack.snap twice with some time interval in between them a report can be generated.  The STATSPACK report generator is called spreport.sql and resides in the same directory as the spcreate.sql script.

Another monitoring tool provided by Oracle is Enterprise Manager.  The Oracle Enterprise Manager console allows database job scheduling, maintenance, and performance monitoring from within a GUI application. Tasks like database object creation and SQL monitoring can be done. The screenshot on the next page shows a datafiles monitoring screen from Enterprise Manager.

This screen shows information regarding Oracle datafiles such as percent used, what tablespace they belong to and size. The information shown is refreshed periodically.

The next screen shows SGA allocations such as buffer caches and various pool sizes from within Enterprise Manager: This screen also offers some rule of thumb information on sizing the PGA and SGA for the available physical memory.

Enterprise Manager has a specific performance component called Performance Manager where statistics can be collected and charted on resources such as SGA utilization, Parse and Execute statistics, latches, enqueues, etc. The next two screens are shown to give an idea of what can be seen with Performance Manager.  As mentioned above, the first chart shows global parse and execute statistics for this database. The next chart shows a more detailed view of SGA activity than the one above from Enterprise Manager.