Database client operation duration metric

[alanwest]

Timing database operations is important. I propose a new metric: db.client.operation.duration. This metric would apply to all types of database systems.

Currently, the only client-level database metrics defined are related to connections, i.e., db.client.connections.*.

The database span conventions already include a set of generic call-level attributes. Some of these attributes would be equally applicable to the db.client.operation.duration metric.

Minimally, the db.client.operation.duration metric should include the following attributes:

• The connection-level attributes
• db.name
• db.operation

[chameleon82]

I propose next attributes for this metric to be included:

Attribute	Type	Description	Examples
error.type	string	class of error operation ended with	timeout; ERROR 1146; ORA-01722
db.operation.phase	string	Operation phase. exec for statement (stored-proc/function/sql) execution, fetch (if applicable) to fetch result sets	exec; fetch

[jack-berg]

@chameleon82 can you give some examples of what values you'd expect to see for db.operation.phase for common operations against common databases? For example, consider the typical application workloads and typical sql SELECT, UPDATE, INSERT, DELETE operations against common relational databases like mysql / postgres, etc. If I understand correctly, there would be separate measurements for the exec phase and the fetch phase.

[chameleon82]

@jack-berg , let me give an example with jdbc driver:

• SQL

     String sql = "SELECT * FROM users";
     try (PreparedStatement statement = connection.prepareStatement(sql)) {
                try (ResultSet resultSet = statement.executeQuery()) {
                    while (resultSet.next()) {    ...   }
                }
          }

• Stored Proc with prepared statement (MS SQL)

  CREATE PROCEDURE get_users AS 
  BEGIN  
    SELECT id, name FROM users;
  END;  

    String sql = "exec get_users";
    try (PreparedStatement statement = connection.prepareStatement(sql)) {
               try (ResultSet resultSet = statement.executeQuery()) {
                   while (resultSet.next()) {    ...   }
               }
         }

• Stored proc with callable statement (no fetch phase)

   try (CallableStatement statement = connection.prepareCall(storedProcedure)) {
                boolean hasResults = statement.execute();
                ...
   }

It split execution in 2 phases:

• prepareStatement or prepareCall where SQL or StoredProcedure compiled on DB side. Exception can happened due incorrect syntax (table modifications) or SP unavailablility. RDBMS (like Oracle, MS SQL, MySQL and etc) usually build execution plan and keep it in cache and metric result can reflect cache hit.
• fetch results, both statement.executeQuery() and until resultSet.next() != null. That part is heavy and may take seconds/minutes to process execution plan and depends on RDMBS settings (for example, Oracle can have execultion plan settings FIRST_ROWS with value 1000 and small resultSets can be processed way faster than the ones with 1000+ rows). Result can failed due various reasons like dividing on zero (db side) and result set mapping as string assigned to number and value is not numeric (client side).

Corner cases:
MS SQL allow to return multiple resutSets from StoredProc means instead of executeQuery() possible to have getResultSet() multiple times

Alternatives:
phase can be prepare and process instead of exec and fetch.