[20141202]11g审计sys.dba_audit_session.txt
--今天在例行检查，使用Oracle Enterprise Manager的时候(我自己很少使用这个东西)，在检查点击某处时候，我感觉很慢。
--我看了后台的执行语句：
/* Formatted on 2014/12/2 14:57:59 (QP5 v5.252.13127.32867) */
SELECT TO_CHAR (CURRENT_TIMESTAMP AT TIME ZONE 'GMT',
'YYYY-MM-DD HH24:MI:SS TZD')
AS curr_timestamp,
COUNT (username) AS failed_count,
TO_CHAR (MIN (timestamp), 'yyyy-mm-dd hh24:mi:ss') AS first_occur_time,
TO_CHAR (MAX (timestamp), 'yyyy-mm-dd hh24:mi:ss') AS last_occur_time
FROM sys.dba_audit_session
WHERE returncode != 0
AND timestamp >= CURRENT_TIMESTAMP - TO_DSINTERVAL ('0 0:30:00')
/* Formatted on 2014/12/2 11:42:51 (QP5 v5.252.13127.32867) */
SELECT TO_CHAR (TO_TIMESTAMP ('2014-12-01', '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') >= '2014-12-01'
AND TO_CHAR (CAST (timestamp AS DATE), 'YYYY-MM-DD') TO_CHAR ( (TO_DATE ('2014-12-01', 'YYYY-MM-DD') + 1),
'YYYY-MM-DD');
SQL> @dpc 0hzna0h1hvah2 ''
PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID 0hzna0h1hvah2, child number 0
-------------------------------------
SELECT TO_CHAR(TO_TIMESTAMP('2014-12-01' , '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') >= '2014-12-01' AND
TO_CHAR(CAST(timestamp AS DATE) , 'YYYY-MM-DD') TO_CHAR((TO_DATE('2014-12-01', 'YYYY-MM-DD') + 1), 'YYYY-MM-DD')
Plan hash value: 3562285719
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)| OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 802 (100)| | | |
| 1 | SORT AGGREGATE | | 1 | | | | |
|* 2 | HASH JOIN OUTER | | 27 | 802 (1)| 2112K| 2112K| 215K (0)|
|* 3 | HASH JOIN OUTER | | 27 | 800 (1)| 2123K| 2123K| 196K (0)|
|* 4 | HASH JOIN OUTER | | 27 | 799 (1)| 2184K| 2184K| 192K (0)|
|* 5 | HASH JOIN OUTER | | 27 | 798 (1)| 2241K| 2241K| 192K (0)|
|* 6 | TABLE ACCESS STORAGE FULL| AUD$ | 27 | 797 (1)| 1025K| 1025K| |
|* 7 | INDEX RANGE SCAN | I_AUDIT_ACTIONS | 3 | 1 (0)| 1025K| 1025K| |
| 8 | INDEX FULL SCAN | I_SYSTEM_PRIVILEGE_MAP | 209 | 1 (0)| 1025K| 1025K| |
| 9 | INDEX FULL SCAN | I_SYSTEM_PRIVILEGE_MAP | 209 | 1 (0)| 1025K| 1025K| |
| 10 | INDEX STORAGE FAST FULL SCAN| I_STMT_AUDIT_OPTION_MAP | 271 | 2 (0)| 1025K| 1025K| |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("AUD"."LOGOFF$DEAD"="AOM"."OPTION#")
3 - access("SPX"."PRIVILEGE"=(-"AUD"."PRIV$USED"))
4 - access("SPM"."PRIVILEGE"=(-"AUD"."LOGOFF$DEAD"))
5 - access("AUD"."ACTION#"="ACT"."ACTION")
6 - storage(("AUD"."ACTION#">=100 AND "AUD"."ACTION#"0 AND
TO_CHAR(CAST(CAST(FROM_TZ(INTERNAL_FUNCTION("NTIMESTAMP#"),'00:00') AT LOCAL AS date) AS
DATE),'YYYY-MM-DD')>='2014-12-01' AND TO_CHAR(CAST(CAST(FROM_TZ(INTERNAL_FUNCTION("NTIMESTAMP#"),'00:00')
AT LOCAL AS date) AS DATE),'YYYY-MM-DD') filter(("AUD"."ACTION#">=100 AND "AUD"."ACTION#"0 AND
TO_CHAR(CAST(CAST(FROM_TZ(INTERNAL_FUNCTION("NTIMESTAMP#"),'00:00') AT LOCAL AS date) AS
DATE),'YYYY-MM-DD')>='2014-12-01' AND TO_CHAR(CAST(CAST(FROM_TZ(INTERNAL_FUNCTION("NTIMESTAMP#"),'00:00')
AT LOCAL AS date) AS DATE),'YYYY-MM-DD') 7 - access("ACT"."ACTION">=100 AND "ACT"."ACTION"
--注：aud$表我已经裁剪了，执行计划如果在之前cost更大，可以发现要全表扫描sys.AUD$表。
--才想起来这个系统11G，上线1个月后我才关闭登录审计,我们系统登录很频繁，aud$已经800M，已经改为审计不成功的登录。
NOAUDIT CREATE SESSION WHENEVER SUCCESSFUL;
--NOAUDIT CREATE SESSION WHENEVER NOT SUCCESSFUL;
--检查sys.aud$发现，没有建立索引，难道oracle不考虑在11G打开审计，许多应用频繁登录数据库，审计表sys.aud$增加很快的情况，
--而且看上面的查询条件，如果优化它要函数索引，
create index i_aud$_timestamp on sys.aud$( TO_CHAR (CAST (timestamp# AS DATE), 'YYYY-MM-DD'));
--好像没用，我在测试环境建立。什么回事？那位知道....