Oracle sys.dba_audit_session查询慢

谁践踏了优雅 2023-01-23 09:57 4阅读 0赞

备注:  
Oracle 11.2.0.4

# 一.问题描述 #

![image.png][]

从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

[image.png]: /images/20221004/f839da5f76594d2b895ce56f8b62bda9.png