DBSNMP user query on sys.cdb_audit_session is taking long time to execute. (Doc ID 2789399.1)

Last updated on NOVEMBER 16, 2022

Applies to:

Symptoms

On : NA version, Database Performance Issues

ACTUAL BEHAVIOR
---------------
We are observing the query taking long time to execute vault database OCI

Hi Team,

We are observing one of the select query which is executing from user DBSNMP is taking long time to execute.

SQL Text
------------------------------
SELECT TO_CHAR(current_timestamp AT TIME ZONE :"SYS_B_0", :"SYS_B_1") AS curr_timestamp,
            SUM(failed_count) AS failed_count ,
            TO_CHAR(MIN(first_occur_time), :"SYS_B_2") AS first_occur_time,
            TO_CHAR(MAX(last_occur_time), :"SYS_B_3") AS last_occur_time, name
FROM
( SELECT COUNT(username) AS failed_count, MIN(timestamp) AS first_occur_time, MAX(timestamp) AS last_occur_time, c.name
           FROM sys.cdb_audit_session a, v$containers c
           WHERE a.returncode != :"SYS_B_4"
               AND a.ACTION_NAME = :"SYS_B_5"
               AND a.timestamp >= current_timestamp - TO_DSINTERVAL(:"SYS_B_6")
               AND a.con_id = c.con_id GROUP BY c.name
  ) GROUP BY name

Global Information
------------------------------
 Status : DONE (ALL ROWS)
 Instance ID : 2
 Session : DBSNMP (1466:22771)
 SQL ID : 0a1chmudckb6g
 SQL Execution ID : 33557066
 Execution Started : 06/30/2021 03:04:47
 First Refresh Time : 06/30/2021 03:04:47
 Last Refresh Time : 06/30/2021 03:09:41
 Duration : 294s
 Module/Action : Oracle Enterprise Manager.Metric Engine/-
 Service : SYS$USERS
 Program : perl@oraexa01 (TNS V1-V3)
 Fetch Calls : 1

Binds
========================================================================================================================
| Name | Position | Type | Value |
========================================================================================================================
| :SYS_B_0 | 1 | VARCHAR2(32) | GMT |
| :SYS_B_4 | 5 | NUMBER | 0 |
| :SYS_B_5 | 6 | VARCHAR2(32)

Cause

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