Skip to main content

This content has been archived and is no longer being updated. Links may not function; however, this content may be relevant to outdated versions of the product.

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: 
  1. Truncate the following tables,

    PR_SYS_QUEUES
    PR_SYS_WORKINDEXER_QUEUE
    PR_SYS_QUEUE_SL

     
  2. Restart the JVM.
Suggest Edit

Published October 14, 2018 - Updated October 8, 2020

Did you find this content helpful? Yes No

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.

We'd prefer it if you saw us at our best.

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice
Contact us