Oracle sys.dba_audit_session查询慢

备注:
Oracle 11.2.0.4

一.问题描述

从awr报告可以看到这个EM相关的sql消耗了大量的IO。
也可以通过消耗IO的sql来查找:

--找出消耗物理IO资源最大的的SQL语句
select disk_reads, substr(sql_text,1,4000) from v$sqlarea  order by disk_reads desc;

具体的慢SQL如下:

SELECT TO_CHAR(current_timestamp 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(timestamp, 'YYYY-MM-DD HH24:MI:SS') >= TO_CHAR(current_timestamp - TO_DSINTERVAL('0 0:30:00'), 'YYYY-MM-DD HH24:MI:SS')

二.问题分析

我们来查找 sys.dba_audit_session这个视图

SQL> set linesize 1000
SQL> set pagesize 1000
SQL> set long 10000
SQL> select view_name,text from user_views where view_name='DBA_AUDIT_SESSION';

VIEW_NAME                      TEXT
------------------------------ --------------------------------------------------------------------------------
DBA_AUDIT_SESSION              select os_username,  username, userhost, terminal, timestamp, action_name,
                                      logoff_time, logoff_lread, logoff_pread, logoff_lwrite, logoff_dlock,
                                      sessionid, returncode, client_id, session_cpu, extended_timestamp,
                                      proxy_sessionid, global_uid, instance_number, os_process
                               from dba_audit_trail
                               where action between 100 and 102
SQL> select view_name,text from user_views where view_name='DBA_AUDIT_TRAIL';

VIEW_NAME                      TEXT
------------------------------ --------------------------------------------------------------------------------
DBA_AUDIT_TRAIL                select spare1           /* OS_USERNAME */,
                                      userid           /* USERNAME */,
                                      userhost         /* USERHOST */,
                                      terminal         /* TERMINAL */,
                                      cast (           /* TIMESTAMP */
                                          (from_tz(ntimestamp#,'00:00') at local) as date),
                                      obj$creator      /* OWNER */,
                                      obj$name         /* OBJECT_NAME */,
                                      aud.action#      /* ACTION */,
                                      act.name         /* ACTION_NAME */,
                                      new$owner        /* NEW_OWNER */,
                                      new$name         /* NEW_NAME */,
                                      decode(aud.action#,
                                             108 /* grant  sys_priv */, null,
                                             109 /* revoke sys_priv */, null,
                                             114 /* grant  role */, null,
                                             115 /* revoke role */, null,
                                             auth$privileges)
                                                       /* OBJ_PRIVILEGE */,
                                      decode(aud.action#,
                                             108 /* grant  sys_priv */, spm.name,
                                             109 /* revoke sys_priv */, spm.name,
                                             null)
                                                       /* SYS_PRIVILEGE */,
                                      decode(aud.action#,
                                             108 /* grant  sys_priv */, substr(auth$privileges,1,1),
                                             109 /* revoke sys_priv */, substr(auth$privileges,1,1),
                                             114 /* grant  role */, substr(auth$privileges,1,1),
                                             115 /* revoke role */, substr(auth$privileges,1,1),
                                             null)
                                                       /* ADMIN_OPTION */,
                                      auth$grantee     /* GRANTEE */,
                                      decode(aud.action#,
                                             104 /* audit   */, aom.name,
                                             105 /* noaudit */, aom.name,
                                             null)
                                                       /* AUDIT_OPTION  */,
                                      ses$actions      /* SES_ACTIONS   */,
                                      cast((from_tz(cast(logoff$time as timestamp),'00:00') at local) as date)
                                                       /* LOGOFF_TIME   */,
                                      logoff$lread     /* LOGOFF_LREAD  */,
                                      logoff$pread     /* LOGOFF_PREAD  */,
                                      logoff$lwrite    /* LOGOFF_LWRITE */,
                                      decode(aud.action#,
                                             104 /* audit   */, null,
                                             105 /* noaudit */, null,
                                             108 /* grant  sys_priv */, null,
                                             109 /* revoke sys_priv */, null,
                                             114 /* grant  role */, null,
                                             115 /* revoke role */, null,
                                             aud.logoff$dead)
                                                        /* LOGOFF_DLOCK */,
                                      comment$text      /* COMMENT_TEXT */,
                                      sessionid         /* SESSIONID */,
                                      entryid           /* ENTRYID */,
                                      statement         /* STATEMENTID */,
                                      returncode        /* RETURNCODE */,
                                      spx.name          /* PRIVILEGE */,
                                      clientid          /* CLIENT_ID */,
                                      auditid           /* ECONTEXT_ID */,
                                      sessioncpu        /* SESSION_CPU */,
                                      from_tz(ntimestamp#,'00:00') at local,
                                                                  /* EXTENDED_TIMESTAMP */
                                      proxy$sid                      /* PROXY_SESSIONID */,
                                      user$guid                           /* GLOBAL_UID */,
                                      instance#                      /* INSTANCE_NUMBER */,
                                      process#                            /* OS_PROCESS */,
                                      xid                              /* TRANSACTIONID */,
                                      scn                                        /* SCN */,
                                      to_nchar(substr(sqlbind,1,2000))      /* SQL_BIND */,
                                      to_nchar(substr(sqltext,1,2000))      /* SQL_TEXT */,
                                      obj$edition                   /* OBJ_EDITION_NAME */,
                                      dbid                                      /* DBID */
                               from sys.aud$ aud, system_privilege_map spm, system_privilege_map spx,
                                    STMT_AUDIT_OPTION_MAP aom, audit_actions act
                               where   aud.action#     = act.action    (+)
                                 and - aud.logoff$dead = spm.privilege (+)
                                 and   aud.logoff$dead = aom.option#   (+)
                                 and - aud.priv$used   = spx.privilege (+)


SQL> 

从如下可以看到，aud$是大表，其它表的数据量都比较小

SQL> 
SQL> select count(*) from system_privilege_map;

  COUNT(*)
----------
       209

SQL> select count(*) from system_privilege_map;

  COUNT(*)
----------
       209

SQL> select count(*) from STMT_AUDIT_OPTION_MAP;

  COUNT(*)
----------
       271

SQL> select count(*) from audit_actions;

  COUNT(*)
----------
       181
SQL> select BYTES/1024/1204/1024 from user_segments where SEGMENT_NAME='AUD$';

BYTES/1024/1204/1024
--------------------
          31.0888704

将aud$审计表备份后truncate，这样查询就会快很多

create table aud$_20210714 tablespace TS_AUDIT  as select * from aud$;
--备份aud$

--然后清空原表
truncate table aud$;

参考:

1. https://blog.csdn.net/cimeng0072/article/details/100286827