A Resource Manager CPU Time Limit for PS/Queries Executed On-line in the PIA

 In previous posts, I have proposed:

• A Sample Oracle Database Resource Manager Plan for PeopleSoft. It can be downloaded from Github: PSFT_PLAN.
• Setting a maximum CPU time for PS/Queries run on the process scheduler (see Management of Long Running PS/Queries Cancelled by Resource Manager CPU Limit).

This blog looks at whether a limit could also be set for PS/Queries run via the PeopleSoft Internet Architecture (PIA).

The main objection to having the database terminate a PS/Query running in the PIA is that the resulting Oracle error message will be displayed in the browser without any further explanation.  Therefore, I think it is better to allow the PIA to handle termination of the query.  However, I also think that it would be prudent to prevent queries continuing to run in background after the client session has been terminated.

Timeout Settings for PS/Query

The inactivity timeout in the PIA, is delivered at 20 minutes (1200 seconds), with a warning 2 minutes earlier.  Then the user's session in the PIA is terminated.

There are timeouts on every Tuxedo service.  In the PeopleSoft Application Server configuration, the service timeout is specified for each type of application server/queue.  

…
[PSQRYSRV]
;=========================================================================
; Settings for PSQRYSRV
;=========================================================================

;-------------------------------------------------------------------------
; UBBGEN settings
Min Instances=3
Max Instances=3
Service Timeout=1200
…

All the services advertised on the queue are given that timeout.  

…
ICQuery         SRVGRP=APPSRV
                LOAD=50 PRIO=50
{QUERYSRV}
                SVCTIMEOUT={$PSQRYSRV\Service Timeout}
{QUERYSRV}
{!QUERYSRV}
                SVCTIMEOUT={$PSAPPSRV\Service Timeout}
{!QUERYSRV}
                BUFTYPE="ALL"
…

PS/Queries are run by the ICQuery service that is advertised on PSQRYSRV server if configured, and whose service timeout is also delivered set to 1200s.  Otherwise, it is advertised on PSAPPSRV whose timeout is 300s.  

Recommendations

• PSQRYSRV should always be configured if PS/Queries are to be run online in an application server domain.  Partly, so that it has the longer timeout, and partly so that long running PS/Queries do not block short on-line requests.
• Please avoid the temptation to increase either of these timeouts.  If a query needs to run for more than 20 minutes, then it should be run on the process scheduler.

When the service timeout is reached, the Tuxedo server process will terminate.  This may not terminate the query on the database until the current fetch operation completes.  If a query involves a large group or sort operation, it can be a long time before the first fetch returns.

Oracle Terminated Connection Timeout (sometimes known as Dead Connect Detection) should be configured by setting SQLNET.EXPIRE_TIME in the SQLNET.ORA file.  Then database shadow process periodically sends a probe to the otherwise idle client, and if the client doesn't respond the session terminates.  However, this process is not perfect.

Therefore, it would be prudent to set a timeout in the consumer group for online PS/Query sessions.  In PSFT_PLAN, that is the PSQUERY_ONLINE_GROUP consumer group.  We don't want the timeout to terminate the session before either ICQuery service times out, nor do we want the query to run on afterwards.  Therefore, the consumer group timeout should be set to the same value as the PSQRYSRV timeout, so also 1200 seconds.

  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    'PSFT_PLAN', 'PSQUERY_ONLINE_GROUP'
    ,mgmt_p6 => 90
    ,switch_group => 'CANCEL_SQL'
    ,switch_time => 1200
    ,switch_for_call => TRUE
  );

If you change the default inactivity timeout, these three settings should all be set to the same value.

If you are not running on Exadata, then it is safe to set this timeout without any further configuration.

Disabling SQL Quarantine on Exadata for PS/Query While Using it Elsewhere

However, on Exadata on 19c, the database will automatically create SQL quarantine directives for statements that exceed the CPU time limit specified in the consumer group and are terminated with error ORA-00040: active time limit exceeded - call aborted. It may take a few minutes for the database to create the quarantine directive - see 2634990.1: 19c New Feature SQL Quarantine Not Work.  Then, the next time the same query generates the same execution plan, it will immediately be terminated with error ORA-56955: quarantined plan used.  Again, we don't want such SQL errors produced in the PIA.

In Oracle 21c, two new parameters have been introduced to control SQL Quarantine.

• OPTIMIZER_CAPTURE_SQL_QUARANTINE enables or disables the automatic creation of SQL Quarantine configurations.  The default value is FALSE.  
• OPTIMIZER_USE_SQL_QUARANTINE determines whether the optimizer considers SQL Quarantine configurations when choosing an execution plan for a SQL statement.  The default value is TRUE.

The parameters can be backported to Oracle 19.3 or later by applying patch 30104721 (see Oracle Doc ID 2635030.1: 19c New Feature SQL Quarantine - How To Stop Automatic SQL Quarantine, and Go-Faster Oracle Blog: New Parameters In 21c To Control Automatic SQL Quarantine Can Be Backported To 19c).  Both these parameters can be set at system and session level.

If you want to take advantage of SQL Quarantine, you have to enable it.  However, I suggest leaving the new parameters at their default values at system level, even though this means a change of behaviour in 19c when the patch is applied.

• OPTIMIZER_CAPTURE_SQL_QUARANTINE = FALSE
• OPTIMIZER_USE_SQL_QUARANTINE = TRUE

Then set both parameters to TRUE at session level in the session for 

• PSQUERY processes run on the process scheduler.
• SQL*Plus and SQL Developer processes.

Ensure they are not set for PIA application server processes

• PSAPPSRV
• PSQRYSRV

CPU Timeouts in the Sample PSFT_PLAN Resource Plan

In my sample resource plan for PeopleSoft, three consumer groups now have timeouts.  SQL Quarantine works in conjunction with consumer groups that have CPU timeouts.  I want SQL Quarantine disabled in PSQUERY_ONLINE_GROUP, but enabled in PSQUERY_BATCH_GROUP and LOW_LIMITED_GROUP.

  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    'PSFT_PLAN', 'PSQUERY_ONLINE_GROUP'
    ,mgmt_p6 => 90
    ,switch_group => 'CANCEL_SQL'
    ,switch_time => 1200 /*same as ICQuery service timeout*/
    ,switch_elapsed_time => 1200
    ,switch_estimate => FALSE /*do not timeout on basis of estimated time*/
    ,switch_for_call => TRUE
  );
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    'PSFT_PLAN', 'PSQUERY_BATCH_GROUP'
    ,mgmt_p6 => 1
    ,switch_group => 'CANCEL_SQL'
    ,switch_time => 14400
    ,switch_estimate => TRUE 
    ,switch_for_call => TRUE
  );
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    'PSFT_PLAN', 'LOW_LIMITED_GROUP'
    ,mgmt_p8 => 1
    ,switch_group => 'CANCEL_SQL'
    ,switch_time => 7200
    ,switch_elapsed_time => 7200
    ,switch_estimate => TRUE 
    ,switch_for_call => TRUE
  );

The consumer groups are mapped to sessions by program name, module and action.

DBMS_RESOURCE_MANAGER.set_consumer_group_mapping (attribute => DBMS_RESOURCE_MANAGER.CLIENT_PROGRAM    , value => 'PSQRYSRV%'                 , consumer_group => 'PSQUERY_ONLINE_GROUP');
DBMS_RESOURCE_MANAGER.set_consumer_group_mapping (attribute => DBMS_RESOURCE_MANAGER.MODULE_NAME_ACTION, value => 'QUERY_MANAGER.QUERY_VIEWER', consumer_group => 'PSQUERY_ONLINE_GROUP');

DBMS_RESOURCE_MANAGER.set_consumer_group_mapping (attribute => DBMS_RESOURCE_MANAGER.MODULE_NAME       , value => 'PSQUERY'                   , consumer_group => 'PSQUERY_BATCH_GROUP');
DBMS_RESOURCE_MANAGER.set_consumer_group_mapping (attribute => DBMS_RESOURCE_MANAGER.MODULE_NAME       , value => 'PSAE.PSQUERY.%'            , consumer_group => 'PSQUERY_BATCH_GROUP');

DBMS_RESOURCE_MANAGER.set_consumer_group_mapping (attribute => DBMS_RESOURCE_MANAGER.CLIENT_PROGRAM    , value => 'SQL Developer%'            , consumer_group => 'LOW_LIMITED_GROUP');
DBMS_RESOURCE_MANAGER.set_consumer_group_mapping (attribute => DBMS_RESOURCE_MANAGER.CLIENT_PROGRAM    , value => 'sqlplus%'                  , consumer_group => 'LOW_LIMITED_GROUP');
DBMS_RESOURCE_MANAGER.set_consumer_group_mapping (attribute => DBMS_RESOURCE_MANAGER.CLIENT_PROGRAM    , value => 'Toad%'                     , consumer_group => 'LOW_LIMITED_GROUP');

Enabling SQL Quarantine for Scheduled PSQUERY Processes

Initialisation parameters can be set for processes run on the process scheduler using a trigger on the PSPRCSRQST table.  This technique was described in a previous blog post: Setting Oracle Session Parameters for Specific Process Scheduler Processes.  

When a PeopleSoft process is initiated on the process scheduler, the first thing it does is to update its RUNSTATUS on PSPRCSRQST to '7' indicating that it is processing.  The SET_PRCS_SESS_PARM_TRIG trigger fires on that transition.  It dynamically generates ALTER SESSION commands for the metadata that matches the current process.  

• Script to create trigger set_prcs_sess_parm_trg.sql 
• Example script to create metadata: set_prcs_sess_parm.sql.  It includes the following statement that will create metadata for PSQUERY to set the new SQL Quarantine parameters if they are available and if running on Exadata.

----------------------------------------------------------------------------------------------------
--Settings for SQL Quarantine in PSQuery on Exadata -- note SQL checks that parameter is valid
----------------------------------------------------------------------------------------------------
DELETE FROM sysadm.PS_PRCS_SESS_PARM where prcsname = 'PSQUERY' 
AND param_name IN('optimizer_capture_sql_quarantine','optimizer_use_sql_quarantine')
/
----------------------------------------------------------------------------------------------------
INSERT INTO sysadm.PS_PRCS_SESS_PARM (prcstype, prcsname, oprid, runcntlid, keyword, param_name, parmvalue)
with n as ( --returns a row on Exadata only
SELECT  COUNT(DISTINCT cell_name) num_exadata_cells
FROM    v$cell
HAVING  COUNT(DISTINCT cell_name)>0
), x (param_name, keyword, parmvalue) as ( --returns rows if parameters available
select  name, 'SET', 'TRUE' 
from    v$parameter, n
where   name IN('optimizer_capture_sql_quarantine','optimizer_use_sql_quarantine')
), y (prcstype, prcsname, oprid, runcntlid) as (
select  prcstype, prcsname, ' ', ' ' 
from    ps_prcsdefn
where   prcsname = 'PSQUERY'
)
select  y.prcstype, y.prcsname, y.oprid, y.runcntlid, x.keyword, x.param_name, x.parmvalue
from    x,y
/

Enabling/Disabling SQL Quarantine for Other Processes at Logon

This is done with a AFTER LOGON trigger and another metadata data table that is similar to the scheduled process trigger.  The mappings in the metadata in this script must match the mappings for the consumer groups where automatic SQL quarantine capture is required.

• Script to create trigger and metadata: set_sess_parm_trg.sql

TL;DR

• Set the timeout for the consumer group for PS/Query to be the same value as the ICQuery Tuxedo service timeout (usually also the same value as the PIA inactivity timeout).
• SQL Quarantine is only available on Exadata.  On other platforms the query just runs to the CPU timeout, by which time the ICQuery service has already timed out.
• If on Exadata, then 
• Apply patch 30104721 on 19c to backport new 21c parameters.
• Leave the quarantine parameters at their default values at system-level
• Set both parameters to TRUE at session level in the session for 
• PSQUERY processes run on the process scheduler.
• SQL*Plus and SQL Developer processes.