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

Purpose of Query

The queries in this page will help you to find out  the time consumed by stored procedure.

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   dss_snapshots 
                                     WHERE  snapshot_id = <snapshot_id>) 
       AND END_HIST.action_date > (SELECT compute_start_date 
                                   FROM   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   dss_snapshots 
                                     WHERE  snapshot_id = <snapshot_id>) 
       AND END_HIST.action_date > (SELECT compute_start_date 
                                   FROM   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   dss_snapshots 
                                     WHERE  snapshot_id = <snapshot_id>) 
       AND END_HIST.action_date > (SELECT compute_start_date 
                                   FROM   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