[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'));

--好像沒用，我在測試環境建立。什麼回事？那位知道....

[20141202]11g審計sys.dba_audit_session

相關文章