OMC: OMC Query Causes High CPU Usage on Database. (Doc ID 2740961.1)

Last updated on MAY 24, 2023

Applies to:

Symptoms

In OMC, one of the queries is consuming significant CPU on 12.1 Oracle Database. The detail about the query is:

sql_id: cfjyw2r8bp8ty
module: omc_sqlperf_ASHomc_oracle_db
action: omc_ASH_SQLStatements
SQL text: with all_sqls as (SELECT s.sql_id, v.command_name, s.sql_fulltext, s.force_matching_signature, s.exact_matching_signature FROM TABLE(GV$(CURSOR( SELECT t.sql_id, t.command_type, sql_fulltext, t.parsing_schema_name, t.force_matching_signature, t.exact_matching_signature FROM v$sql t, XMLTABLE(:"SYS_B_0" PASSING xmltype(:ids) COLUMNS sql_id VARCHAR2(:"SYS_B_1") PATH :"SYS_B_2") y WHERE t.sql_id = y.sql_id))) s, v$sqlcommand v WHERE s.command_type = v.command_type) , all_sql_rownums as (select ROW_NUMBER() OVER (PARTITION BY sql_id ORDER BY sql_id) AS rn, t.* from all_sqls t) select sql_id, command_name, sql_fulltext, force_matching_signature, exact_matching_signature from all_sql_rownums where rn = :"SYS_B_3"

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.