• How much memory is the request is using?
• Why the request not completing (for instance, was it cancelled, or did it fail?)
• When did the request start, and for how long has it been running?
• What is the request's timestamp (to use to search <TDV_INSTALL>/logs for any log entries that could help to ascertain whether any issues were occurring at the time, that may have affected the performance of the request)?

----------------------
PROCEDURE p_GetRequestDetails(IN sql_string VARCHAR(100), 
                               OUT result cursor 
                                  (
                                     request_id INTEGER,
                                     status VARCHAR(24), 
                                     start_time TIMESTAMP, 
                                     end_time TIMESTAMP, 
                                     description VARCHAR , 
                                     message VARCHAR(50000),
                                     max_memory INTEGER,
                                     max_used_memory INTEGER,
                                     current_memory INTEGER,
                                     total_duration INTEGER,
                                     server_duration INTEGER,
                                     transaction_id INTEGER         
                                  )
                                )
BEGIN
declare sqltext VARCHAR(1000);
set sqltext = 'select request_id, status, start_time, end_time, description, message,
               max_memory, max_used_memory, current_memory, total_duration, 
               server_duration, transaction_id 
               from /services/databases/system/SYS_REQUESTS where description
               like ''%' || sql_string || '%'' and description not like ''%' || 'request_id,%''';
open result for sqltext;
CALL PRINT(sqltext);
END

----------------------
PROCEDURE p_GetEventDetails( IN request_id VARCHAR(500),
    OUT result CURSOR 
    )
BEGIN
declare sqltext VARCHAR(500);
set sqltext = 'select * from /services/databases/system/LOG_EVENTS where 
               description like ''%request id=' || request_id || '%''';
CALL PRINT(sqltext);
OPEN result FOR sqltext; 
END
----------------------

Below are the steps to use the procedures.

1.   If the request is still running, click on Purge Completed Requests. This will clear all completed requests, making it easier to search the systems tables.
         

2.   For purposes of illustration, let us say that the request of interest is:

            SELECT * from  /shared/DATA_SOURCES/oracle_ds/JOE/WAIT_A_WHILE(3000);



We will use WAIT_A_WHILE as a unique string that  identifies the request.

3.  Run p_GetRequestDetails(). It will display a prompt to enter an input string.  Enter WAIT_A_WHILE at the prompt. Ensure that no additional characters are entered (e.g leading or trailing spaces).



The procedure will run the following request:

----------------------
select
request_id, status, start_time, end_time, description,
message, max_memory, max_used_memory,
current_memory, total_duration, server_duration, transaction_id 
from /services/databases/system/SYS_REQUESTS where description like '%WAIT_A_WHILE%' and description not like '%request_id,%'
----------------------

The details obtained from SYS_REQUEST will be displayed. For instance, the screenshot below shows that the request failed because it was cancelled:



4.  Make a note of the request id shown in the details.For instance, let us say that the request id is 2403146.

5.   Run p_EventRequestDetails(). It will display a prompt to enter an input.  Enter the request id 2403146 at the prompt. Ensure that no additional characters are entered (e.g leading or trailing spaces). The procedure will run the following request:

----------------------
select * from /services/databases/system/LOG_EVENTS where description like '%request id=2403146%'
----------------------

The details obtained from LOG_EVENTS for the request will be displayed.


Note: As an alternative to using the procedures, the queries listed in steps (3) and (5) may be run in Studio Scratchpad.