Support Article
System Operations page does not load due to long running query
SA-64584
Summary
The System Operations landing page is inaccessible due to an SQL which took a long time to run.
Database operation took more than the threshold of 500 ms: 2,500,978 ms for the below SQL.
Long running SQL:
SELECT "PC0"."PC0PYITEMSTATUS" AS "pyItemStatus", COUNT("PC0"."PC0PYITEMID") AS "pySummaryCount(1)" , COUNT(DISTINCT "PC0"."PC0PXOBJCLASS") AS "pySummaryCount(2)"
FROM ( SELECT "PC0"."PYITEMSTATUS" AS "PC0PYITEMSTATUS" , "PC0"."PZINSKEY" AS "PC0PZINSKEY" , "PC0"."PYITEMID" AS "PC0PYITEMID" , "PC0"."PXOBJCLASS" AS "PC0PXOBJCLASS"
FROM your_schema.pr_sys_queues "PC0" WHERE ( "PC0"."PYITEMSTATUS" IS NOT NULL ) AND "PC0"."PXOBJCLASS" IN (? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) UNION ALL
SELECT "UN1"."PYITEMSTATUS" AS "PC0PYITEMSTATUS" , "UN1"."PZINSKEY" AS "PC0PZINSKEY" , "UN1"."PYITEMID" AS "PC0PYITEMID" , "UN1"."PXOBJCLASS" AS "PC0PXOBJCLASS"
FROM your_schema.pr_sys_queue_col_pop "UN1" WHERE ( "UN1"."PYITEMSTATUS" IS NOT NULL ) AND "UN1"."PXOBJCLASS" = ? UNION ALL SELECT "UN2"."PYITEMSTATUS" AS "PC0PYITEMSTATUS" , "UN2"."PZINSKEY" AS "PC0PZINSKEY" , "UN2"."PYITEMID" AS "PC0PYITEMID" , "UN2"."PXOBJCLASS" AS "PC0PXOBJCLASS"
FROM your_schema.pr_cep_events_queue "UN2" WHERE ( "UN2"."PYITEMSTATUS" IS NOT NULL ) AND "UN2"."PXOBJCLASS" = ? UNION ALL SELECT "UN3"."PYITEMSTATUS" AS "PC0PYITEMSTATUS" , "UN3"."PZINSKEY" AS "PC0PZINSKEY" , "UN3"."PYITEMID" AS "PC0PYITEMID" , "UN3"."PXOBJCLASS" AS "PC0PXOBJCLASS"
FROM your_schema.pr_sys_queue_execreq_conn "UN3" WHERE ( "UN3"."PYITEMSTATUS" IS NOT NULL ) AND "UN3"."PXOBJCLASS" = ? UNION ALL SELECT "UN4"."PYITEMSTATUS" AS "PC0PYITEMSTATUS" , "UN4"."PZINSKEY" AS "PC0PZINSKEY" , "UN4"."PYITEMID" AS "PC0PYITEMID" , "UN4"."PXOBJCLASS" AS "PC0PXOBJCLASS"
FROM your_schema.pr_sys_queue_execreq_svc "UN4" WHERE ( "UN4"."PYITEMSTATUS" IS NOT NULL ) AND "UN4"."PXOBJCLASS" IN (? , ? ) UNION ALL SELECT "UN5"."PYITEMSTATUS" AS "PC0PYITEMSTATUS" , "UN5"."PZINSKEY" AS "PC0PZINSKEY" , "UN5"."PYITEMID" AS "PC0PYITEMID" , "UN5"."PXOBJCLASS" AS "PC0PXOBJCLASS"
FROM your_schema.pr_sys_queue_ftsindexer "UN5" WHERE ( "UN5"."PYITEMSTATUS" IS NOT NULL ) AND "UN5"."PXOBJCLASS" = ? UNION ALL SELECT "UN6"."PYITEMSTATUS" AS "PC0PYITEMSTATUS" , "UN6"."PZINSKEY" AS "PC0PZINSKEY" , "UN6"."PYITEMID" AS "PC0PYITEMID" , "UN6"."PXOBJCLASS" AS "PC0PXOBJCLASS"
FROM your_schema.CRM_SYS_QUEUES "UN6" WHERE ( "UN6"."PYITEMSTATUS" IS NOT NULL ) AND "UN6"."PXOBJCLASS" IN (? , ? ) UNION ALL SELECT "UN7"."PYITEMSTATUS" AS "PC0PYITEMSTATUS" , "UN7"."PZINSKEY" AS "PC0PZINSKEY" , "UN7"."PYITEMID" AS "PC0PYITEMID" , "UN7"."PXOBJCLASS" AS "PC0PXOBJCLASS"
FROM your_schema.pr_sys_queue_sla "UN7" WHERE ( "UN7"."PYITEMSTATUS" IS NOT NULL ) AND "UN7"."PXOBJCLASS" = ? UNION ALL SELECT "UN8"."PYITEMSTATUS" AS "PC0PYITEMSTATUS" , "UN8"."PZINSKEY" AS "PC0PZINSKEY" , "UN8"."PYITEMID" AS "PC0PYITEMID" , "UN8"."PXOBJCLASS" AS "PC0PXOBJCLASS"
FROM your_schema.pr_sys_queue_startflows "UN8" WHERE ( "UN8"."PYITEMSTATUS" IS NOT NULL ) AND "UN8"."PXOBJCLASS" = ? UNION ALL SELECT "UN9"."PYITEMSTATUS" AS "PC0PYITEMSTATUS" , "UN9"."PZINSKEY" AS "PC0PZINSKEY" , "UN9"."PYITEMID" AS "PC0PYITEMID" , "UN9"."PXOBJCLASS" AS "PC0PXOBJCLASS"
FROM your_schema.pr_sys_queue_autotest "UN9" WHERE ( "UN9"."PYITEMSTATUS" IS NOT NULL ) AND "UN9"."PXOBJCLASS" = ? UNION ALL SELECT "UN10"."PYITEMSTATUS" AS "PC0PYITEMSTATUS" , "UN10"."PZINSKEY" AS "PC0PZINSKEY" , "UN10"."PYITEMID" AS "PC0PYITEMID" , "UN10"."PXOBJCLASS" AS "PC0PXOBJCLASS" FROM your_schema.pr_sys_workindexer_queue "UN10" WHERE ( "UN10"."PYITEMSTATUS" IS NOT NULL ) AND "UN10"."PXOBJCLASS" = ? ) "PC0" GROUP BY "PC0"."PC0PYITEMSTATUS" ORDER BY 1
Error Messages
Not Applicable
Steps to Reproduce
Not Applicable
Root Cause
A defect or configuration issue in the operating environment: Out-of-the-box report definition.
pzQueueAggregatedStatus on System-Queue- triggers the query. It reports on all descendant queue classes and has an aggregate field. The report's filter does not screen data if a majority of the records are Scheduled and pxObjClass. The SQL execution may cause a full table scan as the database may choose not to use the index.
Resolution
Make the following change to the operating environment:
- Truncate the following tables,
PR_SYS_QUEUES
PR_SYS_WORKINDEXER_QUEUE
PR_SYS_QUEUE_SL
- Restart the JVM.
Published October 14, 2018 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.