Purpose of Query
The queries in this page will help you to determine the time consumed by stored procedures.
Applicable CAST Version
Release | Yes/No |
---|---|
8.3.x | |
8.2.x | |
8.1.x | |
8.0.x |
Applicable RDBMS
RDBMS | Yes/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