Performance improvement for Oracle on MQ* tables.

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

The following SQL statement is causing a high CPU usage on Oracle:

 SELECT MQ1WP.*,ROWID FROM MQ1WP WHERE MQWP_SchedTime <= sys_extract_utc(systimestamp(0)) ORDER BY MQWP_Priority, MQWP_SchedTime, MQWP_PK FOR UPDATE SKIP LOCKED

 

 

Environment

Release : 12.3

Cause

Technical change.

Resolution

The query was changed in version 21.0.

12.3:

SELECT MQ1WP.*,ROWID FROM MQ1DWP WHERE MQDWP_SchedTime <= sys_extract_utc(systimestamp(0)) ORDER BY MQDWP_Priority, MQDWP_SchedTime, MQDWP_PK FOR UPDATE SKIP LOCKED

21.0:

SELECT MQ1WP.*,ROWID FROM MQ1DWP WHERE MQDWP_SchedTime <= CAST(SYS_EXTRACT_UTC(SYSTIMESTAMP(0)) AS DATE) ORDER BY MQDWP_Priority, MQDWP_SchedTime, MQDWP_PK FOR UPDATE SKIP LOCKED