Create / Edit Service Test - JDBC SQL Timing Page

Use this page to define a JDBC SQL Timing Test. A JDBC SQL service test will determine the availability and performance of a JDBC (Java Database Connectivity) connection to a database.

Parameter	Description
Connection String	Specify the JDBC connect string here. Example: jdbc:oracle:thin:@host:port:sid
Class String	Specify the class string of the JDBC driver. Example: oracle.jdbc.driver.OracleDriver
Role	This is optional field. Specify the user role here.
Statement	Enter the SQL statement.

Metrics Collected

Metric	Description
Status	Indicates whether the SQL statement could be successfully executed.
Total Time	The total time taken to connect to the database and run the query. It is the sum of Connect Time, Prepare Time, Execute Time, Fetch Time and Close Time. User Action Use the Total Time metric to identify database connectivity problems. Examine the other metrics to isolate and repair the problem. If you have an Enterprise Manager target instance for the database, you should consult the homepage for the database.
Connect Time	Total time taken to connect to the database. User Action Connect Time might be slow if the network is congested, if the database is having trouble authenticating the user, or if the database is having trouble allocating connections. If you have an Enterprise Manager target instance for the database, you should consult the homepage for the database.
Prepare Time	Time taken to prepare the SQL statement. This usually includes fetching metadata for the object types in the query. User Action Prepare Time might be slow if the network performance is slow or if database performance is slow. If you have an Enterprise Manager target instance for the database, you should consult the homepage for the database. .
Execute Time	Time taken to execute the SQL statement. User Action Execute Time will be slow if the database performance is slow. If you have an Enterprise Manager target instance for the database, you should consult the homepage for the database.
Fetch Time	Time taken to retrieve data from the server. User Action Fetch Time might be slow if the network bandwidth is low or if database performance is slow. If you have an Enterprise Manager target instance for the database, you should consult the homepage for the database.
Close Time	Time taken to close the connection. User Action Close Time might be slow if the network performance is slow. Examine your network configuration.
Number of Rows Fetched	The total number of rows fetched during a query. User Action If the Number of Rows Fetched is not what you expected, then examine the contents of your database. Unexpected rows in the result could affect Fetch Time and Total Time.
Total Time per Row	Time taken to perform the entire test divided by the number of rows fetched (Total Time / Number of Rows Fetched). Total Time per Row is a good metric to use for setting thresholds and for comparing the performance of different JDBC SQL Timing tests. User Action Use the Total Time per Row metric to identify database connectivity problems. As with the Total Time metric, you should examine the other metrics to isolate and repair the problem. If you have an Enterprise Manager target instance for the database, you should consult the homepage for the database.
Fetch Time per Row	Time taken to fetch each row (Fetch Time / Number of Rows Fetched). Fetch Time per Row is a good metric to use for setting thresholds and for comparing the performance of different JDBC SQL Timing tests. User Action Use the Fetch Time per Row metric to identify data transfer problems. If the value of this metric is high, then the data-transfer bandwidth between the client and the database is poor. If you have an Enterprise Manager target instance for the database, you should consult the homepage for the database.

Setting up a JDBC Test

To setup a JDBC Test for any database of any type (can be MySQL, Oracle, Microsoft SQL Server etc)

Download the JDBC Type 4 driver for the particular database.
Deploy the driver jar to $EMAGENT_HOME/JDBCDrivers (if it does not exist, create one).
Change $EMAGENT_HOME/sysman/config/emd.properties so that the CLASSPATH variable includes the driver jar.

Example: CLASSPATH= $EMAGENT_HOME/JDBCDrivers/mydriver.jar; <rest of classpath>.

Restart the Agent using emctl stop agent; emctl start agent.

Usage Tips

The JDBC driver must be present in the Agent's class path. The Agent's class path is specified in “CLASSPATH” variable of the sysman/config/emd.properties file.
All third party JDBC drivers must be separately installed.

Related Topics

About Services

Types of Service Tests

Creating a Service

Enterprise Manager Information Roadmap