This blog proposes additional logging for scheduled PS/Queries so that long-running queries can be reconstructed and analysed.
Previous blog posts have discussed limiting PS/Query runtime with the resource manager (see Management of Long Running PS/Queries Cancelled by Resource Manager CPU Limit). From 19c, on Engineered Systems only, the 'Oracle Database automatically quarantines the plans for SQL statements terminated by … the Resource Manager for exceeding resource limits'. SQL Quarantine is enabled by default in Oracle 19c on Exadata (unless patch 30104721 is applied that backports the new 23c parameters, see Oracle Doc ID 2635030.1: 19c New Feature SQL Quarantine - How To Stop Automatic SQL Quarantine).
What is the Problem?
SQL Quarantine prevents a query from executing. Therefore, AWR will not capture the execution plan. AWR will also purge execution plans where an execution has not been captured within the AWR retention period. The original long-running query execution that was quarantined, if captured by AWR, will be aged out because it will not execute again.
If we want to investigate PS/Queries that produced execution plans that exceeded the runtime limit and were then quarantined, we need to reproduce the execution plan, either with the EXPLAIN PLAN FOR command or by executing the query in a session where the limited resource manager consumer group does not apply.
However, PS/Queries with bind variables present a challenge. A PS/Query run with different bind variables can produce different execution plans. One execution plan might be quarantined and so never complete, while another may complete within an acceptable time.
In AWR, a plan is only captured once for each statement. Therefore, it is possible to find one set of bind variables for each plan, although there may be many sets of bind variables that all produce the same execution plan. However, we cannot obtain Oracle bind variables for quarantined execution plans that did not execute. To regenerate their execution plans, we need another way to obtain their bind variables.
This problem occurs more generally where the Diagnostics Pack is not available, then it is not possible to reconstruct long-running queries without additional logging or tracing.
Solution
- PS_QUERY_RUN_CNTRL: Scheduled Query Run Control. This record identifies the query executed. Rows in this table will be copied to PS_QRYRUN_CTL_HST.
- PS_QUERY_RUN_PARM: Scheduled Query Run Parameters. This record holds the bind variables and the values passed to the query. The table contains a row for each bind variable for each execution. Rows in this table will be copied to PS_QRYRUN_PARM_HST
Two database triggers manage the history tables:
- A database trigger that fires when the run status of the request is updated to '7' (processing). It copies rows for the current run control into two corresponding history tables. Thus, we will have a log of every bind variable for every scheduled query.
- A second database trigger will fire when a PSQUERY request record is deleted. It deletes the corresponding rows from these history tables.
When a PS/Query produces a quarantined execution plan, the PSQUERY process terminates with error ORA-56955: quarantined plan used (see Quarantined SQL Plans for PS/Queries). Now we can obtain the bind variables that resulted in attempts to execute a quarantined query execution plan.
Implementation
The following script (ps_query_run_cntrl_hist_trigger.sql) creates the tables and triggers.
- Application Designer record definitions should be created for the two history tables by importing the project QRYRUN_HST (download QRYRUN_HST.zip from GitHub).
REM ps_query_run_cntrl_hist_trigger.sql
REM 21.4.2025 - trigger and history tables to capture
set echo on serveroutput on timi on
clear screen
spool ps_query_run_cntrl_hist_trigger
rollback;
CREATE TABLE SYSADM.PS_QRYRUN_CTL_HST
(PRCSINSTANCE INTEGER DEFAULT 0 NOT NULL,
OPRID VARCHAR2(30) DEFAULT ' ' NOT NULL,
RUN_CNTL_ID VARCHAR2(30) DEFAULT ' ' NOT NULL,
DESCR VARCHAR2(30) DEFAULT ' ' NOT NULL,
QRYTYPE SMALLINT DEFAULT 1 NOT NULL,
PRIVATE_QUERY_FLAG VARCHAR2(1) DEFAULT 'N' NOT NULL,
QRYNAME VARCHAR2(30) DEFAULT ' ' NOT NULL,
URL VARCHAR2(254) DEFAULT ' ' NOT NULL,
ASIAN_FONT_SETTING VARCHAR2(3) DEFAULT ' ' NOT NULL,
PTFP_FEED_ID VARCHAR2(30) DEFAULT ' ' NOT NULL) TABLESPACE PTTBL
/
CREATE UNIQUE iNDEX SYSADM.PS_QRYRUN_CTL_HST
ON SYSADM.PS_QRYRUN_CTL_HST (PRCSINSTANCE) TABLESPACE PSINDEX PARALLEL NOLOGGING
/
ALTER INDEX SYSADM.PS_QRYRUN_CTL_HST NOPARALLEL LOGGING
/
CREATE TABLE SYSADM.PS_QRYRUN_PARM_HST
(PRCSINSTANCE INTEGER DEFAULT 0 NOT NULL,
OPRID VARCHAR2(30) DEFAULT ' ' NOT NULL,
RUN_CNTL_ID VARCHAR2(30) DEFAULT ' ' NOT NULL,
BNDNUM SMALLINT DEFAULT 0 NOT NULL,
FIELDNAME VARCHAR2(18) DEFAULT ' ' NOT NULL,
BNDNAME VARCHAR2(30) DEFAULT ' ' NOT NULL,
BNDVALUE CLOB) TABLESPACE PSIMAGE2
/
CREATE UNIQUE iNDEX SYSADM.PS_QRYRUN_PARM_HST
ON SYSADM.PS_QRYRUN_PARM_HST (PRCSINSTANCE, BNDNUM) TABLESPACE PSINDEX PARALLEL NOLOGGING
/
ALTER INDEX SYSADM.PS_QRYRUN_PARM_HST NOPARALLEL LOGGING
/
- PSQUERY is not a restartable Application Engine program. Therefore, there is no risk of duplicate inserts into the history tables.
- The exception handlers in the triggers deliberately suppress any error, in case that causes the process scheduler to crash.
CREATE OR REPLACE TRIGGER sysadm.query_run_cntrl_hist_ins
BEFORE UPDATE OF runstatus ON sysadm.psprcsrqst
FOR EACH ROW
WHEN (new.runstatus ='7' AND old.runstatus != '7' AND new.prcsname = 'PSQUERY' AND new.prcstype = 'Application Engine')
BEGIN
INSERT INTO PS_QRYRUN_CTL_HST
(PRCSINSTANCE, OPRID, RUN_CNTL_ID, DESCR ,QRYTYPE, PRIVATE_QUERY_FLAG, QRYNAME, URL, ASIAN_FONT_SETTING, PTFP_FEED_ID)
SELECT :new.prcsinstance, OPRID, RUN_CNTL_ID, DESCR ,QRYTYPE, PRIVATE_QUERY_FLAG, QRYNAME, URL, ASIAN_FONT_SETTING, PTFP_FEED_ID
FROM ps_query_run_cntrl WHERE oprid = :new.oprid AND run_cntl_id = :new.runcntlid;
INSERT INTO PS_QRYRUN_PARM_HST
(PRCSINSTANCE, OPRID, RUN_CNTL_ID, BNDNUM, FIELDNAME, BNDNAME, BNDVALUE)
SELECT :new.prcsinstance prcsinstance, OPRID, RUN_CNTL_ID, BNDNUM, FIELDNAME, BNDNAME, BNDVALUE
FROM ps_query_run_parm WHERE oprid = :new.oprid AND run_cntl_id = :new.runcntlid;
EXCEPTION WHEN OTHERS THEN NULL; --exception deliberately coded to suppress all exceptions
END;
/
CREATE OR REPLACE TRIGGER sysadm.query_run_cntrl_hist_del
BEFORE DELETE ON sysadm.psprcsrqst
FOR EACH ROW
WHEN (old.prcsname = 'PSQUERY' AND old.prcstype = 'Application Engine')
BEGIN
DELETE FROM PS_QRYRUN_CTL_HST WHERE prcsinstance = :old.prcsinstance;
DELETE FROM PS_QRYRUN_PARM_HST WHERE prcsinstance = :old.prcsinstance;
EXCEPTION WHEN OTHERS THEN NULL; --exception deliberately coded to suppress all exceptions
END;
/
show errors
spool offExample
When a query is scheduled to run on the process scheduler, the bind variables are specified through this generic dialogue.
Once the PSQUERY process has started (it immediately commits its update to RUNSTATUS), these values are written to the new history tables.
select * from ps_qryrun_ctl_hst; PRCSINSTANCE OPRID RUN_CNTL_ID DESCR QRYTYPE P QRYNAME URL ASI PTFP_FEED_ID ------------ -------- ------------ ------------------------------ ---------- - ------------------------------ -------------------------------------------------- --- ------------------------------ 12345678 ABCDEF 042225 Journal Line Detail - Account 1 N XXX_JRNL_LINE_DTL_ACCT https://xxxxxxx.yyyyy.com/psp/XXXXXXX/EMPLOYEE/ERP select * from ps_qryrun_ctl_hst; PRCSINSTANCE OPRID RUN_CNTL_ID BNDNUM FIELDNAME BNDNAME BNDVALUE ------------ -------- ------------ ------ ------------------ -------------------- ------------------------------ 12345678 ABCDEF 042225 1 bind1 BUSINESS_UNIT 354XX 12345678 ABCDEF 042225 2 bind2 BUSINESS_UNIT 354XX 12345678 ABCDEF 042225 3 FISCAL_YEAR FISCAL_YEAR 2025 12345678 ABCDEF 042225 4 ACCOUNTING_PD_FROM ACCOUNTING_PD_FROM 2 12345678 ABCDEF 042225 5 ACCOUNTING_PD_TO ACCOUNTING_PD_TO 2 12345678 ABCDEF 042225 6 bind6 ACCOUNT 23882XXXXX 12345678 ABCDEF 042225 7 bind7 ACCOUNT 23882XXXXX 12345678 ABCDEF 042225 8 bind8 ALTACCOUNT 23882XXXXX 12345678 ABCDEF 042225 9 bind9 ALTACCOUNT 23882XXXXX
Conclusion
If the query is quarantined, PSQUERY will terminate with error ORA-56955: quarantined plan used. The SQL statement can be extracted from the message log, and the execution plan can be generated with the EXPLAIN PLAN FOR command, using the bind variable values captured in the history tables.
Note: The signature of the SQL Quarantine directive is the exact matching signature of the SQL text (it can be generated from the SQL text with dbms_sqltune.sqltext_to_signature). There can be multiple PLAN_HASH_VALUEs for the same signature (because there can be multiple execution plans for the same SQL). Verify that the FULL_PLAN_HASH_VALUE of the execution plan generated with the captured bind variables corresponds to the PLAN_HASH_VALUE of a SQL Quarantine directive.
@go-faster.co.uk on BlueSky
Posts
