Automatic AWR Dmp Importing into AWR Warehouse Times Out Due to Insert Query Slowness (Doc ID 2657367.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

• EM 13c, 12.2 db for AWR Warehouse repository.
• AWR dmp is automatically being moved and imported into AWR Warehouse database in 12.2 db and This keep being failed as main insert query runs too slow sqlid=0c42x72jx1ar1:
  
  INSERT /*+ APPEND */ INTO SYS.WRH$_RSRC_CONSUMER_GROUP ("SNAP_ID", "DBID", "INSTANCE_NUMBER", "SEQUENCE#", "CONSUMER_GROUP_ID", "CONSUMER_GROUP_NAME", "REQUESTS", "CPU_WAIT_TIME", "CPU_WAITS",
  "CONSUMED_CPU_TIME", "YIELDS", "ACTIVE_SESS_LIMIT_HIT", "UNDO_LIMIT_HIT", "SWITCHES_IN_CPU_TIME", "SWITCHES_OUT_CPU_TIME", "SWITCHES_IN_IO_MEGABYTES", "SWITCHES_OUT_IO_MEGABYTES",
  "SWITCHES_IN_IO_REQUESTS", "SWITCHES_OUT_IO_REQUESTS", "SQL_CANCELED", "ACTIVE_SESS_KILLED", "IDLE_SESS_KILLED", "IDLE_BLKR_SESS_KILLED", "QUEUED_TIME", "QUEUE_TIME_OUTS", "IO_SERVICE_TIME",
  "IO_SERVICE_WAITS", "SMALL_READ_MEGABYTES", "SMALL_WRITE_MEGABYTES", "LARGE_READ_MEGABYTES", "LARGE_WRITE_MEGABYTES", "SMALL_READ_REQUESTS", "SMALL_WRITE_REQUESTS", "LARGE_READ_REQUESTS",
  "LARGE_WRITE_REQUESTS", "PQS_QUEUED", "PQ_QUEUED_TIME", "PQ_QUEUE_TIME_OUTS", "PQS_COMPLETED", "PQ_SERVERS_USED", "PQ_ACTIVE_TIME", "CON_DBID")
  SELECT "SNAP_ID", 1890029227 AS DBID, "INSTANCE_NUMBER", "SEQUENCE#", "CONSUMER_GROUP_ID", "CONSUMER_GROUP_NAME", "REQUESTS", "CPU_WAIT_TIME", "CPU_WAITS", "CONSUMED_CPU_TIME", "YIELDS",
  "ACTIVE_SESS_LIMIT_HIT", "UNDO_LIMIT_HIT", "SWITCHES_IN_CPU_TIME", "SWITCHES_OUT_CPU_TIME", "SWITCHES_IN_IO_MEGABYTES", "SWITCHES_OUT_IO_MEGABYTES", "SWITCHES_IN_IO_REQUESTS",
  "SWITCHES_OUT_IO_REQUESTS", "SQL_CANCELED", "ACTIVE_SESS_KILLED", "IDLE_SESS_KILLED", "IDLE_BLKR_SESS_KILLED", "QUEUED_TIME", "QUEUE_TIME_OUTS", "IO_SERVICE_TIME", "IO_SERVICE_WAITS",
  "SMALL_READ_MEGABYTES", "SMALL_WRITE_MEGABYTES", "LARGE_READ_MEGABYTES", "LARGE_WRITE_MEGABYTES", "SMALL_READ_REQUESTS", "SMALL_WRITE_REQUESTS", "LARGE_READ_REQUESTS", "LARGE_WRITE_REQUESTS",
  "PQS_QUEUED", "PQ_QUEUED_TIME", "PQ_QUEUE_TIME_OUTS", "PQS_COMPLETED", "PQ_SERVERS_USED", "PQ_ACTIVE_TIME", DECODE(CON_DBID, DBID,1890029227,CON_DBID)
  AS CON_DBID FROM "AWRLOAD$26398"."WRH$_RSRC_CONSUMER_GROUP" WHERE "DBID" = :c_dbid AND (1890029227, "SNAP_ID")
  NOT IN (SELECT "DBID", "SNAP_ID" FROM SYS."WRH$_RSRC_CONSUMER_GROUP" SUB WHERE SUB.DBID = :d_dbid)
   

Cause

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