Print Friendly, PDF & Email

 
SYMPTOMS

The Oracle Enterprise Manager Cloud Control 13c Release 3 and The Oracle Management Agent 13c release 3 are used to monitor the Oracle database 12.1.0.1.0. Both The Failed Logins and Failed Logins (Historical) Metric are enabled and execute on a regular base.

The Mixed Mode Auditing is enabled in the database. The Unified Auditing records are never purged since the database creation or there is a large number of Unified Auditing records in the SYS.UNIFIED_AUDIT_TRAIL view. The AUD$ table is very small and only serveral MB in size.

There are both the direct path write temp and the read by other session wait events with high value of waited time in awr/statspack reports during a period when metrics queries were running.

 
The issue can also be confirmed though The Oracle Enterprise Manager Cloud Control 13c Release 3 console. When clicking the Failed Logins Metric for the monitored database the OEM hangs.

 
RELATED
 
HOW TO TURN OFF ORACLE 12C UNIFIED AUDITING AND CLEAN UP ALL UNIFIED AUDITING RECORDS
THE FAILED LOGINS METRIC QUERY IS VERY SLOW BECAUSE OF AUD$ TABLE SIZE
THE AUD$ TABLE IS VERY LARGE

 
DIAGNOSE
 

NOTE : The issue exists when the following products are used together for monitoring the database.

	1. The Oracle Enterprise Manager Cloud Control 13c Release 3
	2. The Oracle Database 12c Release 12.1.0.1.0
	3. The Oracle Management Agents 13c Release 3

 
Under other conditions, you will not encounter the problem. For instance, with 12c agent the SYS.UNIFIED_AUDIT_TRAIL view is not used by the metics as the 12c part of the failedLogin.pl script is commented by default.

oracle@srv-sovadb|11:48|~:emctl getversion agent
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
       Oracle Enterprise Manager Cloud Control Agent 12c Release 5
cat /agent12c/plugins/oracle.sysman.db.agent.plugin_12.1.0.8.0/scripts/failedLogin.pl

   elsif ($audit_trail_value =~ /DB/)
   {
     $failed_login_sql = "SELECT TO_CHAR(TO_TIMESTAMP('$date_last_run' , 'YYYY-MM-DD') ".
                           "AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') AS curr_timestamp, ".
                           "COUNT(username) AS failed_count ".
                         "FROM sys.dba_audit_session ".
                         "WHERE returncode != 0 ".
                         "AND TO_CHAR(CAST(timestamp AS DATE) , 'YYYY-MM-DD') ".
                           ">= '$date_last_run' ".
                         "AND TO_CHAR(CAST(timestamp AS DATE) , 'YYYY-MM-DD') ".
                           "< TO_CHAR((TO_DATE('$date_last_run', 'YYYY-MM-DD') + 1), 'YYYY-MM-DD')";

      #if($db_is12_1 == 1) {
      #    $failed_login_sql = "SELECT TO_CHAR(TO_TIMESTAMP('$date_last_run' , 'YYYY-MM-DD') ".
      #                     " AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') AS curr_timestamp, ".
      #                     " SUM(failed_count) AS failed_count ".
      #                     " FROM ( SELECT COUNT(username) AS failed_count ".
      #                   "FROM sys.dba_audit_session ".
      #                   "WHERE returncode != 0 ".
      #                   "AND TO_CHAR(CAST(timestamp AS DATE) , 'YYYY-MM-DD') ".
      #                     ">= '$date_last_run' ".
      #                   "AND TO_CHAR(CAST(timestamp AS DATE) , 'YYYY-MM-DD') ".
      #                " < TO_CHAR((TO_DATE('$date_last_run', 'YYYY-MM-DD') + 1), 'YYYY-MM-DD') ".
      #                " UNION ".
      #                " SELECT COUNT(DBUSERNAME) AS failed_count ".
      #                " FROM sys.unified_audit_trail ".
      #                " WHERE ACTION_NAME='LOGON' and return_code <> 0 ".
      #                "AND TO_CHAR(CAST(event_timestamp AS DATE) , 'YYYY-MM-DD') ".
      #                ">= '$date_last_run' ".
      #                "AND TO_CHAR(CAST(event_timestamp AS DATE) , 'YYYY-MM-DD') ".
      #                "< TO_CHAR((TO_DATE('$date_last_run', 'YYYY-MM-DD') + 1), 'YYYY-MM-DD'))";
      #}

   }

 
So,
In the library cache of the Oracle database 12.1.0.1.0, there are queries as follows

-- Currently running metrics queries

SET LINES 300 
SET PAGES 999
COL SID FOR 9999
COL CHN FOR 9999
COL SQL_FULLTEXT FOR A150 WORD_WRAPPED


WITH FAILED_LOGINS_QUERIES AS (
SELECT SQL_ID,
       CHILD_NUMBER,
       SQL_FULLTEXT
FROM
       V$SQL
WHERE
       PARSING_SCHEMA_NAME='DBSNMP' AND
       UPPER(SQL_FULLTEXT) LIKE UPPER('%AS failed_count%FROM%sys.dba_audit_session%returncode%unified_audit_trail%')
)
SELECT S.SID,
       S.SERIAL#,
       F.SQL_ID,
       F.CHILD_NUMBER CHN,
       F.SQL_FULLTEXT
FROM
       V$SESSION S,
       FAILED_LOGINS_QUERIES F
WHERE
       S.SQL_ID=F.SQL_ID AND
       S.SQL_CHILD_NUMBER=F.CHILD_NUMBER;
  SID    SERIAL# SQL_ID          CHN SQL_FULLTEXT
----- ---------- ------------- ----- ----------------------------------------------------------------------------------------------------------------
  215      59337 c85zupw0dgrm4     0 SELECT TO_CHAR(current_timestamp AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD')  AS curr_timestamp, SUM(faile
                                     d_count) AS failed_count,  TO_CHAR(MIN(first_occur_time), 'yyyy-mm-dd hh24
                                     :mi:ss') AS first_occur_time,  TO_CHAR(MAX(last_occur_time), 'yyyy-mm-dd h
                                     h24:mi:ss') AS last_occur_time  FROM ( SELECT COUNT(username) AS failed_co
                                     unt, MIN(timestamp) AS first_occur_time,  MAX(timestamp) AS last_occur_tim
                                     e FROM  sys.dba_audit_session WHERE returncode != 0 AND timestamp >= curre
                                     nt_timestamp - TO_DSINTERVAL('0 0:30:00')  UNION  SELECT COUNT(DBUSERNAME)
                                      AS failed_count,  MIN(event_timestamp) AS first_occur_time,  MAX(event_ti
                                     mestamp) AS last_occur_time  FROM unified_audit_trail  WHERE ACTION_NAME='
                                     LOGON' and return_code <> 0  AND event_timestamp >= current_timestamp - TO
                                     _DSINTERVAL('0 0:30:00'))

  699      48597 1y1k03vvus43c     0 SELECT TO_CHAR(TO_TIMESTAMP('2019-03-04' , 'YYYY-MM-DD')  AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') AS c
                                     urr_timestamp,  SUM(failed_count) AS failed_count  FROM ( SELECT COUNT(use
                                     rname) AS failed_count FROM sys.dba_audit_session WHERE returncode != 0 AN
                                     D TO_CHAR(CAST(timestamp AS DATE) , 'YYYY-MM-DD') >= '2019-03-04' AND TO_C
                                     HAR(CAST(timestamp AS DATE) , 'YYYY-MM-DD')  < TO_CHAR((TO_DATE('2019-03-0
                                     4', 'YYYY-MM-DD') + 1), 'YYYY-MM-DD')  UNION  SELECT COUNT(DBUSERNAME) AS
                                     failed_count  FROM unified_audit_trail  WHERE ACTION_NAME='LOGON' and retu
                                     rn_code <> 0 AND TO_CHAR(CAST(event_timestamp AS DATE) , 'YYYY-MM-DD') >=
                                     '2019-03-04' AND TO_CHAR(CAST(event_timestamp AS DATE) , 'YYYY-MM-DD') < T
                                     O_CHAR((TO_DATE('2019-03-04', 'YYYY-MM-DD') + 1), 'YYYY-MM-DD'))
-- The Metric queries that are not running now but were executed earlier

SET LINES 300
SET PAGES 999
COL SQL_ID FOR A15 
COL CHILD_NUMBER FOR 99999
COL SQL_FULLTEXT FOR A150 WORD_WRAPPED
SET LONG 200000
SELECT SQL_ID,
       CHILD_NUMBER,
       SQL_FULLTEXT
FROM
       V$SQL
WHERE
       PARSING_SCHEMA_NAME='DBSNMP' AND
       UPPER(SQL_FULLTEXT) LIKE UPPER('%AS failed_count%FROM%sys.dba_audit_session%returncode%unified_audit_trail%');

 
The first query with the sql_id c85zupw0dgrm4 is caused by the Failed Logins Metric and the second one with the sql_id 1y1k03vvus43c is caused by the Failed Metric (Historical) Metric. Both queries are extremely slow causing high IO and CPU consumption. It can be confirmed by reviewing execution details in V$SQL view for those queries.
 
NOTE: If you are intresting in finding exact queries for any database metric refer to the HOW TO FIND A QUERY THAT IS EXECUTED BY A DATABASE METRIC IN A TARGET DATABASE note for details how to do that.

 
For instance, the execution details for the Failed Logins Metric query

 Enter value for sql_id: c85zupw0dgrm4
old   3: for a in (select SQL_ID,CHILD_NUMBER from v$sql where SQL_ID='&SQL_ID')
new   3: for a in (select SQL_ID,CHILD_NUMBER from v$sql where SQL_ID='c85zupw0dgrm4')
 *************************************** : ******************
                                  SQL_ID : c85zupw0dgrm4
                                  Child# : 0
                           Cursor Status : VALID
                     Is Cursor Obsolete? : N
                          Parsing Schema : DBSNMP
                        System Timestamp : 09-09-2020 10-49-02
                               First Run : 2020-09-08/14:01:44
                        Last Active Time : 09-09-2020 10-49-01
                   Elapsed Time Total(s) : 133200.17
                       CPU Time Total(s) : 105009.3
                   IO Wait Time Total(s) : 39692.53
          Concurrency Wait Time Total(s) : 283.69
          Application Wait Time Total(s) : 0
              Cluster Wait Time Total(s) : 0
                              Executions : 5
                Elapsed Time per Exec(s) : 26640.03
                    CPU Time per Exec(s) : 21001.86
                IO Wait Time per Exec(s) : 7938.51
       Concurrency Wait Time per Exec(s) : 56.74
       Application Wait Time per Exec(s) : 0
           Cluster Wait Time per Exec(s) : 0
                                         :
                              Disk Reads : 2435895983
                             Buffer Gets : 1010890452
                           Direct Writes : 158299040
                           Row Processed : 0
                                         :
                       Number of latches : 0
                             Sql Profile :
                        Sql Command Type : 3
 *************************************** : ******************

PL/SQL procedure successfully completed.

 
The c85zupw0dgrm4 tried to exectute 5 times (never completed). It took 26640.03 seconds or 7.4 hours per 1 execution (it's still running). The IO operations took 7938.51 seconds per execution that is 29,79% of elapsed time. So, 2.2 hours the query did masive IO operations during execution. At the same time the c85zupw0dgrm4 had really high CPU time - 21001.86 seconds or 5.8 hours per execution.

The execution details for the Failed Logins (Historical) Metric query

Enter value for sql_id: 1y1k03vvus43c
old   3: for a in (select SQL_ID,CHILD_NUMBER from v$sql where SQL_ID='&SQL_ID')
new   3: for a in (select SQL_ID,CHILD_NUMBER from v$sql where SQL_ID='1y1k03vvus43c')
 *************************************** : ******************
                                  SQL_ID : 1y1k03vvus43c
                                  Child# : 0
                           Cursor Status : VALID
                     Is Cursor Obsolete? : N
                          Parsing Schema : DBSNMP
                        System Timestamp : 09-09-2020 10-49-29
                               First Run : 2020-09-08/15:08:52
                        Last Active Time : 09-09-2020 10-49-27
                   Elapsed Time Total(s) : 64291.4
                       CPU Time Total(s) : 51072.73
                   IO Wait Time Total(s) : 18927.5
          Concurrency Wait Time Total(s) : 136.86
          Application Wait Time Total(s) : 0
              Cluster Wait Time Total(s) : 0
                              Executions : 8
                Elapsed Time per Exec(s) : 8036.42
                    CPU Time per Exec(s) : 6384.09
                IO Wait Time per Exec(s) : 2365.94
       Concurrency Wait Time per Exec(s) : 17.11
       Application Wait Time per Exec(s) : 0
           Cluster Wait Time per Exec(s) : 0
                                         :
                              Disk Reads : 1130294937
                             Buffer Gets : 2468194405
                           Direct Writes : 72165160
                           Row Processed : 0
                                         :
                       Number of latches : 0
                             Sql Profile :
                        Sql Command Type : 3
 *************************************** : ******************

PL/SQL procedure successfully completed.

 
The same issue is here. No comment.

 
In total, both queries spent 58620,03 seconds or 16.28 hours for unnecessary IO operations loading the IO subsystem of the database server.

 
Next step, knowing the SID of the sessions executing the queries let's find out sessions wait events during the sessions lifetime

SET LINES 300 
SET PAGES 999
COL WAIT_CLASS FOR A30
COL EVENT FOR A30 

SELECT EVENT,
       TOTAL_WAITS,
       ROUND(TIME_WAITED/100/60) TIME_WAITED_MIN,  -- Minutes
       ROUND(AVERAGE_WAIT/100) AVERAGE_WAIT_SEC,   -- Seconds
       ROUND(MAX_WAIT/100) MAX_WAIT_SEC,           -- Seconds
       WAIT_CLASS 
FROM
       V$SESSION_EVENT
WHERE
       SID='&SID'
ORDER BY TIME_WAITED_MICRO;
 -- The Failed Logins Metric

Enter value for sid: 215
old  10:        SID='&SID'
new  10:        SID='215'

EVENT                          TOTAL_WAITS TIME_WAITED_MIN AVERAGE_WAIT_SEC MAX_WAIT_SEC WAIT_CLASS
------------------------------ ----------- --------------- ---------------- ------------ ------------------------------
library cache: mutex X                   1               0                0            0 Concurrency
SQL*Net message to client                6               0                0            0 Network
Disk file operations I/O                 4               0                0            0 User I/O
log file sync                            1               0                0            0 Commit
latch: row cache objects                 2               0                0            0 Concurrency
SQL*Net message from client              6               0                0            0 Idle
latch: shared pool                       1               0                0            0 Concurrency
cursor: pin S                            2               0                0            0 Concurrency
db file parallel read                 2789               0                0            0 User I/O
events in waitclass Other              204               0                0            0 Other
direct path read temp                50861               0                0            0 User I/O
latch: cache buffers chains        7093359               2                0            0 Concurrency
db file sequential read           78889789               4                0            0 User I/O
db file scattered read           239212974              38                0            0 User I/O
direct path write temp             4160105              73                0            0 User I/O
read by other session            405269728             242                0            0 User I/O

16 rows selected.
SQL> -- The Failed Logins (Historical) Metric

/
Enter value for sid: 699
old  10:        SID='&SID'
new  10:        SID='699'

EVENT                          TOTAL_WAITS TIME_WAITED_MIN AVERAGE_WAIT_SEC MAX_WAIT_SEC WAIT_CLASS
------------------------------ ----------- --------------- ---------------- ------------ ------------------------------
SQL*Net message to client                8               0                0            0 Network
library cache: mutex X                  22               0                0            0 Concurrency
Disk file operations I/O                 4               0                0            0 User I/O
latch: row cache objects                 5               0                0            0 Concurrency
latch: shared pool                       1               0                0            0 Concurrency
SQL*Net message from client              8               0                0            0 Idle
cursor: pin S                            9               0                0            0 Concurrency
events in waitclass Other              164               0                0            0 Other
db file parallel read                 1959               0                0            0 User I/O
direct path read temp                10263               0                0            0 User I/O
latch: cache buffers chains        6782381               2                0            0 Concurrency
db file sequential read           73511660               4                0            0 User I/O
db file scattered read           205576776              31                0            0 User I/O
direct path write temp             2956869              53                0            0 User I/O
read by other session            392551501             232                0            0 User I/O

15 rows selected.

According to the sessions wait events stat, it is obvious that the Enterprise Manager Metrics cause high IO during their executions. The User I/O wait class for both sessions is almost 100% of wait events time.

 
The same IO wait classes with the high elapsed time for the class can also be seen in awr/statspack reports for the period of time when the metrics queries were executing

SQL> @?/rdbms/admin/spreport.sql

                              22106 09 Sep 2020 09:00     5
                              22107 09 Sep 2020 10:00     5
                              22108 09 Sep 2020 11:00     5



Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 22107
Begin Snapshot Id specified: 22107

Enter value for end_snap: 22108


Just exerpt of the report 


Snapshot       Snap Id     Snap Time      Sessions Curs/Sess Comment
~~~~~~~~    ---------- ------------------ -------- --------- ------------------
Begin Snap:      22107 09-Sep-20 10:00:02       68       3.2
  End Snap:      22108 09-Sep-20 11:00:03       77       6.5
   Elapsed:      60.02 (mins) Av Act Sess:       3.9
   DB time:     233.92 (mins)      DB CPU:     185.04 (mins)


Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
CPU time                                                    11,111          74.4
direct path write temp                       1,825,879       1,958      1   13.1
read by other session                       13,529,754         799      0    5.4
db file scattered read                      14,969,922         374      0    2.5
db file sequential read                     11,491,394         325      0    2.2
          -------------------------------------------------------------
^LHost CPU  (CPUs: 8  Cores: 8  Sockets: 1)
~~~~~~~~              Load Average
                      Begin     End      User  System    Idle     WIO     WCPU
                    ------- -------   ------- ------- ------- ------- --------
                       3.17    2.69     35.58    3.07   61.29    6.64

 
The metrics queries are in the Top queries of the report

^LSQL ordered by Reads  DB/Inst: MERIDB2/meridb2  Snaps: 22107-22108
-> End Disk Reads Threshold:      1000  Total Disk Reads:     204,850,624
-> Captured SQL accounts for   96.4% of Total Disk Reads
-> SQL reported below exceeded  1.0% of Total Disk Reads

                                                     CPU      Elapsd
  Physical Rds   Executions  Rds per Exec   %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------

     92,021,808            0                  44.9  4760.68   6270.26 1497681416
Module: Oracle Enterprise Manager.Metric Engine
SELECT TO_CHAR(current_timestamp AT TIME ZONE 'GMT', 'YYYY-MM-DD
 HH24:MI:SS TZD')  AS curr_timestamp, SUM(failed_count) AS faile
d_count,  TO_CHAR(MIN(first_occur_time), 'yyyy-mm-dd hh24:mi:ss'
) AS first_occur_time,  TO_CHAR(MAX(last_occur_time), 'yyyy-mm-d

     55,798,415            0                  27.2  2724.58   3601.85 2593843182
Module: Oracle Enterprise Manager.Metric Engine
SELECT TO_CHAR(TO_TIMESTAMP('2019-03-04' , 'YYYY-MM-DD')  AT TIM
E ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') AS curr_timestamp,  S
UM(failed_count) AS failed_count  FROM ( SELECT COUNT(username)
AS failed_count FROM sys.dba_audit_session WHERE returncode != 0

 
 
SOLUTION
 
The issue is related to the Unified Auditing feature that was added starting from the 12c database. There are serveral workarounds to fix it

(1) Purge the Unified Auditing data. Execute the following statements under the SYS user. Be careful as it will clean all records.

EXEC DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL; 

BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
use_last_arch_timestamp => FALSE);
END;
/

To clean up the Unified Auditing records on regular base a clean up job must be created. Refer to the HOW TO TURN OFF ORACLE 12C UNIFIED AUDITING AND CLEAN UP ALL UNIFIED AUDITING RECORDS note for details.

or

(2) Disable the Failed Logins and the Failed Logins (Historical) Metrics in the Oracle Enterprise Manager 13c for the monitored database. It will stop the queries executions in the target database.

a. Failed Logins:

Target Database -> Monitoring -> Metric and Collection Settings -> Search for "Failed Logins" -> Click on "Every 30 minutes" -> Disable

 
b. Failed Logins (Historical):

Target Database -> Monitoring -> Metric and Collection Settings -> Other Collected Items -> Failed Logins (Historical) -> Click on the Duration "Every 12 hours" -> Disable.

or

(3) Disable completely the Unified Auditing and switch back to standard audit in AUD$
Refer to the HOW TO TURN OFF ORACLE 12C UNIFIED AUDITING AND CLEAN UP ALL UNIFIED AUDITING RECORDS to know how to disable the Unified Auditing.

 
 
RESULT
 
After the number of rows of the Unified Audting were reduced (workaround 1) the queries execute fast. For instance,

(1) Find sql_id of the queries of the related metrics. The bellow query shows list of all already executed metrics queries.

SET LINES 300
SET PAGES 999
COL SQL_ID FOR A15 
COL CHILD_NUMBER FOR 99999
COL SQL_FULLTEXT FOR A150 WORD_WRAPPED
SET LONG 200000
SELECT SQL_ID,
       CHILD_NUMBER,
       SQL_FULLTEXT
FROM
       V$SQL
WHERE
       PARSING_SCHEMA_NAME='DBSNMP' AND
       UPPER(SQL_FULLTEXT) LIKE UPPER('%AS failed_count%FROM%sys.dba_audit_session%returncode%unified_audit_trail%');
SQL_ID          CHILD_NUMBER SQL_FULLTEXT
--------------- ------------ ------------------------------------------------------------------------------------------------------------------------------------------------------

... 

f85np3tt99spq              0 SELECT TO_CHAR(TO_TIMESTAMP('2020-09-12' , 'YYYY-MM-DD')  AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') AS c
                             urr_timestamp,  SUM(failed_count) AS failed_count  FROM ( SELECT COUNT(use
                             rname) AS failed_count FROM sys.dba_audit_session WHERE returncode != 0 AN
                             D TO_CHAR(CAST(timestamp AS DATE) , 'YYYY-MM-DD') >= '2020-09-12' AND TO_C
                             HAR(CAST(timestamp AS DATE) , 'YYYY-MM-DD')  < TO_CHAR((TO_DATE('2020-09-1
                             2', 'YYYY-MM-DD') + 1), 'YYYY-MM-DD')  UNION  SELECT COUNT(DBUSERNAME) AS
                             failed_count  FROM unified_audit_trail  WHERE ACTION_NAME='LOGON' and retu
                             rn_code <> 0 AND TO_CHAR(CAST(event_timestamp AS DATE) , 'YYYY-MM-DD') >=
                             '2020-09-12' AND TO_CHAR(CAST(event_timestamp AS DATE) , 'YYYY-MM-DD') < T
                             O_CHAR((TO_DATE('2020-09-12', 'YYYY-MM-DD') + 1), 'YYYY-MM-DD'))

54 rows selected.

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
------------------
13-SEP-20

The query f85np3tt99spq is the Failed Logins (Historical) Metric query.

(2) Find execution details of the query from V$SQL view

 *************************************** : ******************
                                  SQL_ID : f85np3tt99spq
                                  Child# : 0
                           Cursor Status : VALID
                     Is Cursor Obsolete? : N
                          Parsing Schema : DBSNMP
                        System Timestamp : 13-09-2020 20-54-20
                               First Run : 2020-09-13/00:01:13
                        Last Active Time : 13-09-2020 00-01-12
                   Elapsed Time Total(s) : .16
                       CPU Time Total(s) : .15
                   IO Wait Time Total(s) : .01
          Concurrency Wait Time Total(s) : 0
          Application Wait Time Total(s) : 0
              Cluster Wait Time Total(s) : 0
                              Executions : 1
                Elapsed Time per Exec(s) : .16
                    CPU Time per Exec(s) : .15
                IO Wait Time per Exec(s) : .01
       Concurrency Wait Time per Exec(s) : 0
       Application Wait Time per Exec(s) : 0
           Cluster Wait Time per Exec(s) : 0
                                         :
                              Disk Reads : 2
                             Buffer Gets : 16396
                           Direct Writes : 0
                           Row Processed : 1
                                         :
                       Number of latches : 0
                             Sql Profile :
                        Sql Command Type : 3
 *************************************** : ******************

PL/SQL procedure successfully completed.

 
So, the query now is very fast and does not cause any extra load in the database as it previously was before the Unified Audit data were cleaned up.

 
 
REFERENCES
 
HOW TO TURN OFF ORACLE 12C UNIFIED AUDITING AND CLEAN UP ALL UNIFIED AUDITING RECORDS
HOW TO FIND A QUERY THAT IS EXECUTED BY A DATABASE METRIC IN A TARGET DATABASE
13.2 EM: "Failed Logins" and "Failed Logins (Historical)" Metrics Keep Running Despite Being Disabled (Doc ID 2459666.1)
EM13c : How to disable Failed Logins Metric Completely? (Doc ID 2429080.1)
EM 12c, 11g: High Cpu Utilization from Enterprise Manger Agent perl Process Running failedLogin.pl for audit_failed_logins metric (Doc ID 1265699.1)
Fixed Objects Statistics (GATHER_FIXED_OBJECTS_STATS) Considerations (Doc ID 798257.1)

 
 

Version  : 21:01 13.09.2020
Database : 12.1.0.1
OEM      : 13.3.0.0.0