Print Friendly, PDF & Email

SYMPTOMS
The Enterprise Manager Queries are the first CPU/TIME consuming queries in the order by Elapsed time section of a statspack report.

For instance

          -------------------------------------------------------------
^LSQL ordered by Elapsed time for DB: MERIDB2  Instance: meridb2  Snaps: 27081 -27
-> Total DB Time (s):           6,849
-> Captured SQL accounts for  144.8% of Total DB Time
-> SQL reported below exceeded  1.0% of Total DB Time

  Elapsed                Elap per            CPU                        Old
  Time (s)   Executions  Exec (s)  %Total   Time (s)  Physical Reads Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
   6620.82        1,673       3.96   96.7    4681.11     576,152,561 1528629476
Module: Oracle Enterprise Manager.Metric Engine
select rowid, inst#, bucket#, inst_lob#, max_seq#,  flush_scn, f
lush_time, min_scn, min_time, max_scn, max_time, sid#, serial#,
status, length(log_piece) from "CLI_SWP$11051857$1$1" where flus
h_scn >= :1 and inst# = :2  order by flush_scn, inst_lob# asc

   3518.05            0              51.4    3136.52     479,994,409 2126530821
Module: Oracle Enterprise Manager.Metric Engine
SELECT TO_CHAR(TO_TIMESTAMP('2021-03-31' , '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

   3121.42            1    3121.42   45.6    1559.31      96,359,140 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

 
The related SQL ID for found queries can be found by their Old Hash Value value

SQL> SELECT OLD_HASH_VALUE, SQL_ID FROM V$SQL WHERE OLD_HASH_VALUE IN (1528629476,2126530821,1497681416);

OLD_HASH_VALUE SQL_ID
-------------- -------------
    1528629476 7jjdq77gjkjxx
    1497681416 c85zupw0dgrm4

 
All of these OEM Queries query against audit tables.

 
RELATED
(1) THE FAILED LOGINS AND THE FAILED LOGINS (HISTORICAL) METRICS ARE EXTREMELY SLOW RESULTING IN HIGH IO AND CPU CONSUMPTION
(2) HOW TO TURN OFF ORACLE 12C UNIFIED AUDITING AND CLEAN UP ALL UNIFIED AUDITING RECORDS

 
CAUSE
There are too many audit records

 -- Query the view

SQL> set timing on
SQL> select count(*) from unified_audit_trail;
^Cselect count(*) from unified_audit_trail
                     *
ERROR at line 1:
ORA-01013: user requested cancel of current operation

Elapsed: 05:28:40.57

-- or query one of view's table directly

SQL> SET TIMING ON
SQL> SELECT COUNT(*) FROM AUDSYS."CLI_SWP$11051857$1$1";

  COUNT(*)
----------
   5095793

Elapsed: 00:00:50.49

 
SOLUTION
Clean up audit records. The following command will purge all available audit records. For other available purging options see the links below.

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.

See more details in the (1) THE FAILED LOGINS AND THE FAILED LOGINS (HISTORICAL) METRICS ARE EXTREMELY SLOW RESULTING IN HIGH IO AND CPU CONSUMPTION and the (2) HOW TO TURN OFF ORACLE 12C UNIFIED AUDITING AND CLEAN UP ALL UNIFIED AUDITING RECORDS

 
REFERENCES

THE FAILED LOGINS AND THE FAILED LOGINS (HISTORICAL) METRICS ARE EXTREMELY SLOW RESULTING IN HIGH IO AND CPU CONSUMPTION
HOW TO TURN OFF ORACLE 12C UNIFIED AUDITING AND CLEAN UP ALL UNIFIED AUDITING RECORDS
GATHER_FIXED_OBJECTS_STATS Runs Extremely Long (Doc ID 2581098.1)

 
 

Version  : 15:35 01-Apr-2021
Database : 12.1.0.1
OEM      : 13.3.0.0.0