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
