SQL Queries - CAST Knowledge Base - Queries on snapshots - How to find out the time consumed by a stored procedure

Purpose of Query

The queries in this page will help you to determine the time consumed by stored procedures.

Applicable CAST Version

ReleaseYes/No
8.3.x✅
8.2.x✅
8.1.x✅
8.0.x✅

Applicable RDBMS

RDBMSYes/No
Oracle Server✅
Microsoft SQL Server✅
CSS2✅

Query for CSS

SELECT Substr(START_HIST.description, 7)                 AS Action, 
       START_HIST.action_date                            Start_date, 
       END_HIST.action_date                              end_date, 
       ( END_HIST.action_date - START_HIST.action_date ) AS consumed_time 
FROM   dss_history START_HIST, 
       dss_history END_HIST 
WHERE  START_HIST.history_id + 1 = END_HIST.history_id 
       AND START_HIST.description LIKE 'Start%' 
       AND END_HIST.description LIKE 'End%' 
       AND START_HIST.action_date > (SELECT compute_start_date 
                                     FROM   <central_schema>.dss_snapshots 
                                     WHERE  snapshot_id = <snapshot_id>) 
       AND END_HIST.action_date > (SELECT compute_start_date 
                                   FROM   <central_schema>.dss_snapshots 
                                     WHERE  snapshot_id = <snapshot_id>) 
ORDER  BY ( END_HIST.action_date - START_HIST.action_date ) DESC

Query result example

“APMAEP_RESULT_EC_TRAN_APP”;“2018-09-18 18:26:36.491”;“2018-09-18 18:51:47.103”;“00:25:10.612”

Query result interpretation

Stored procedure APMAEP_RESULT_EC_TRAN_APP started on “2018-09-18 18:26:36.491”, ended on “2018-09-18 18:51:47.103” and consumed “00:25:10.612”

Query for Oracle

SELECT Substr(START_HIST.description, 7)                 AS Action, 
       START_HIST.action_date                            Start_date, 
       END_HIST.action_date                              end_date, 
       ( END_HIST.action_date - START_HIST.action_date ) AS consumed_time 
FROM   dss_history START_HIST, 
       dss_history END_HIST 
WHERE  START_HIST.history_id + 1 = END_HIST.history_id 
       AND START_HIST.description LIKE 'Start%' 
       AND END_HIST.description LIKE 'End%' 
       AND START_HIST.action_date > (SELECT compute_start_date 
                                     FROM   <central_schema>.dss_snapshots 
                                     WHERE  snapshot_id = <snapshot_id>) 
       AND END_HIST.action_date > (SELECT compute_start_date 
                                   FROM   <central_schema>.dss_snapshots 
                                     WHERE  snapshot_id = <snapshot_id>) 
ORDER  BY ( END_HIST.action_date - START_HIST.action_date ) DESC

Query result example

“APMAEP_RESULT_EC_TRAN_APP”;“2018-09-18 18:26:36.491”;“2018-09-18 18:51:47.103”;“00:25:10.612”

Query result interpretation

Stored procedure APMAEP_RESULT_EC_TRAN_APP started on “2018-09-18 18:26:36.491”, ended on “2018-09-18 18:51:47.103” and consumed “00:25:10.612”

Query for SQL server

SELECT Substr(START_HIST.description, 7)                 AS Action, 
       START_HIST.action_date                            Start_date, 
       END_HIST.action_date                              end_date, 
       ( END_HIST.action_date - START_HIST.action_date ) AS consumed_time 
FROM   dss_history START_HIST, 
       dss_history END_HIST 
WHERE  START_HIST.history_id + 1 = END_HIST.history_id 
       AND START_HIST.description LIKE 'Start%' 
       AND END_HIST.description LIKE 'End%' 
       AND START_HIST.action_date > (SELECT compute_start_date 
                                     FROM   <central_schema>.dss_snapshots 
                                     WHERE  snapshot_id = <snapshot_id>) 
       AND END_HIST.action_date > (SELECT compute_start_date 
                                   FROM   <central_schema>.dss_snapshots 
                                     WHERE  snapshot_id = <snapshot_id>) 
ORDER  BY ( END_HIST.action_date - START_HIST.action_date ) DESC

Query result example

“APMAEP_RESULT_EC_TRAN_APP”;“2018-09-18 18:26:36.491”;“2018-09-18 18:51:47.103”;“00:25:10.612”

Query result interpretation

Stored procedure APMAEP_RESULT_EC_TRAN_APP started on “2018-09-18 18:26:36.491”, ended on “2018-09-18 18:51:47.103” and consumed “00:25:10.612”

Notes/comments

Related Pages