[20150707]OEM的问题.txt
--今天看awr报表时发现如下命令：
--已经格式化：
SELECT TO_CHAR
(
TO_TIMESTAMP ('2015-07-06', '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') >= '2015-07-06'
AND TO_CHAR (CAST (timestamp AS DATE), 'YYYY-MM-DD') TO_CHAR
(
(TO_DATE ('2015-07-06', 'YYYY-MM-DD') + 1)
,'YYYY-MM-DD'
);
--没有想到这个是module='Oracle Enterprise Manager.Metric Engine',PARSING_SCHEMA_NAME='DBSNMP'.FIRST_LOAD_TIME＝'2015-07-07/02:17:04'
--那个时候没有什么业务。
--执行计划如下:
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 14y9t18qjnbn8, child number 0
-------------------------------------
SELECT TO_CHAR ( TO_TIMESTAMP ('2015-07-06',
'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') >=
'2015-07-06' AND TO_CHAR (CAST (timestamp AS DATE),
'YYYY-MM-DD') (TO_DATE ('2015-07-06', 'YYYY-MM-DD') + 1)
,'YYYY-MM-DD' )
Plan hash value: 3441003015
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2758 (100)| | | | |
| 1 | SORT AGGREGATE | | 1 | 52 | | | | | |
| 2 | NESTED LOOPS OUTER | | 2 | 104 | 2758 (1)| 00:00:34 | | | |
|* 3 | HASH JOIN OUTER | | 2 | 96 | 2757 (1)| 00:00:34 | 3104K| 3104K| 464K (0)|
|* 4 | HASH JOIN OUTER | | 2 | 86 | 2756 (1)| 00:00:34 | 3104K| 3104K| 410K (0)|
|* 5 | HASH JOIN OUTER | | 2 | 76 | 2755 (1)| 00:00:34 | 2981K| 2981K| 410K (0)|
|* 6 | TABLE ACCESS STORAGE FULL| AUD$ | 2 | 68 | 2754 (1)| 00:00:34 | 1025K| 1025K| |
|* 7 | INDEX RANGE SCAN | I_AUDIT_ACTIONS | 3 | 12 | 1 (0)| 00:00:01 | 1025K| 1025K| |
| 8 | INDEX FULL SCAN | I_SYSTEM_PRIVILEGE_MAP | 209 | 1045 | 1 (0)| 00:00:01 | 1025K| 1025K| |
| 9 | INDEX FULL SCAN | I_SYSTEM_PRIVILEGE_MAP | 209 | 1045 | 1 (0)| 00:00:01 | 1025K| 1025K| |
|* 10 | INDEX RANGE SCAN | I_STMT_AUDIT_OPTION_MAP | 1 | 4 | 1 (0)| 00:00:01 | 1025K| 1025K| |
--------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5C160134
6 - SEL$5C160134 / AUD@SEL$3
7 - SEL$5C160134 / ACT@SEL$3
8 - SEL$5C160134 / SPM@SEL$3
9 - SEL$5C160134 / SPX@SEL$3
10 - SEL$5C160134 / AOM@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
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')>='2015-07-06'
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')>='2015-07-06'
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" 10 - access("AUD"."LOGOFF$DEAD"="AOM"."OPTION#")
--看看id=6,全表扫描sys.aud$.而且这个条件是无法通过建立索引来解决的。我们表aud$前一阵子用户到期，导致短期内大量登陆报错，记录许多垃圾。
--我删除了一些记录，move一下。减少了不少逻辑读。上面的执行计划是已经整理后的结果。
--google发现如下链接：2011年就存在，真不知道oracle什么回事？看来程序员的通病都是一样的。
--不过这个表sys.aud$本身也没有什么索引。看来要保持这个表不要太大，定期要整理才行。