Using custom SQL queries

Number of records

Set a threshold for this attribute to get a notification if the query returns a high number of records (rows) that exceed the threshold. By default, this attribute does not generate an alarm or warning event. For more information about setting an alarm or warning event for this attribute, or changing the default attribute range, see Defining a monitoring policy. 

For example, you can use the Number of Records attribute of the Custom Query monitor type to monitor how many sessions are using more than 200 cpu seconds.

While configuring a policy in the TrueSight console, you add a custom query to the environment configuration through the Custom SQL queries panel. In the Custom SQL queries section, if you enable the number of records metric, the result is displayed as an annotation on the number of records metric.

Here is an example on how to sample the name of the users whose expiry date is less than seven days:

Custom SQL Query panel sample

Here is the complete query:

SELECT USERNAME, ACCOUNT_STATUS, EXPIRY_DATE FROM DBA_USERS WHERE (EXPIRY_DATE - SYSDATE) < 7

The screenshots below show the output of the query with an annotation.

Number of records graph

Suggested action

A high number of return rows can indicate an over-utilized resource. A generated event indicates that you might want to update the number of rows returned by the query to avoid alarms or warning events.

Query status

Set a threshold for this attribute to get a notification if the query was successful or unsuccessful. By default, this attribute will not generate an alarm or warning event as the threshold is not set. For more information about setting an alarm or warning event for this attribute, or changing the default attribute range, see Defining a monitoring policy. 

To see more information about this attribute, see the Query status.

Query status graph

Suggested action

If an SQL query fails because of a bad SQL query definition, revise your query and correct the configuration in the TrueSight console.

Response time

Set a threshold for this attribute to monitor the response time, in milliseconds, required by a query to retrieve data. By default, this attribute will not generate an alarm or warning event. For more information about setting an alarm or warning event for this attribute, or changing the default attribute range, see Defining a monitoring policy. To see more information about this attribute, see the Response time.

Response time graph

Suggested action

A high response time value can indicate delays occurring in the Oracle Enterprise Database, check your database performance.

Value

This attribute is used to return a numeric value. If the query result is a string which can be converted to a numeric value, the Value attribute displays the converted value else the attribute will stay offline.

For example: Result = 123.456e+5, the numeric value would be 12345600.

While configuring a policy in the TrueSight console, you add a custom query to the environment configuration through the Custom SQL queries panel. If the query result is an output of multiple rows, the Value attribute will be in the offline state, and the output is displayed as an annotation in the number of records attribute. 

By default, this attribute will not generate an alarm or warning event. For more information about this attribute, see the Value attribute.

Here is an example on how to set a threshold for the Value attribute to identify an increase in the number of inactive sessions in the database. 

SELECT COUNT(*) FROM v$session WHERE STATUS = 'INACTIVE'

For more information about setting an alarm or warning event for this attribute, or changing the default attribute range, see Defining a monitoring policy.

Value graph

Event generated by the KM

You can see the event in the TrueSight console in Monitoring > Events.

Suggested action

A higher value can indicate many inactive sessions in the database which can result in exhausting the max sessions limit.