Create / Edit Service Test - Oracle SQL Timing Page

Enter the name or IP address of the host, the Port number and System Identifier (SID).

Specify whether the connection pool should be used. The connection pool is a set of open connections maintained by the agent. It is a resource shared across all functions of the agent.

If you select PL/SQL, specify the index of the bind variable.

Example:

Create a SQL test by entering the following as the Statement test parameter:

SELECT TABLE_NAME
FROM USER_TABLES

The Statement Type should be SQL, and the PL/SQL Out Parameters should be left blank, since this is not a PL/SQL statement.  When the Agent executes the metric, the SQL statement will be run and timed by the agent.

To write a PL/SQL test that returns a cursor, enter the following as the Statement test parameter:

BEGIN
  OPEN :1 FOR SELECT TABLE_NAME FROM USER_TABLES;
END;

Select PL/SQL for the Statement Type.  The Parameter Position should be 1, referring to the first bind variable.  The Parameter Type should be SQL_CURSOR, indicating that the rows of the cursor should be fetched.

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.

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.

Time taken to prepare the SQL statement. This usually includes fetching metadata for the object types in the query.

Time taken to execute the SQL statement.

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.

Time taken to close the connection.

User Action
Close Time might be slow if the network performance is slow. Examine your network configuration.

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.

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 Oracle SQL Timing tests.

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 Oracle SQL Timing tests.