[20141202]11g审计sys.dba_audit_session

[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#"<=102 AND "RETURNCODE"<>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#"<=102 AND "RETURNCODE"<>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"<=102)             

--注：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'));

--好像没用，我在测试环境建立。什么回事？那位知道....

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-1354110/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-1354110/