备注:
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$;
参考:
标签：audit,aud,dba,sys,logoff,SQL,null,name From： https://www.cnblogs.com/jijm123/p/18373175