将该参数optimizer_capture_sql_plan_baselines 设置为true后,出现大量的等待事件:enq: HW - contention
select * from VW_SESSION_WAIT_LHR;
SELECT a.SID,
a.serial#,
a.WAIT_CLASS,
a.EVENT,
a.BLOCKING_SESSION,
a.SQL_TEXT
FROM VW_SESSION_LHR a
WHERE a.EVENT = 'enq: HW - contention';
Sql语句:
MERGE INTO sqlobj$data
USING dual
ON (:1 IS NULL)
WHEN MATCHED THEN
UPDATE
SET comp_data = :2
WHERE signature = :3
AND category = :4
AND obj_type = :5
AND plan_id = :6
WHEN NOT MATCHED THEN
INSERT
(signature,
category,
obj_type,
plan_id,
comp_data,
spare1,
spare2)
VALUES
(:7,
:8,
:9,
:10,
:11,
NULL,
NULL);
数据库版本:
解决:
总体来说是Oracle的一个bug:
第一步:先把该属性禁用 alter system Set optimizer_capture_sql_plan_baselines=false;
第二步:采用如下的存过把已经捕捉到的执行计划全部删掉,不然系统需要等待很久才能恢复正常,因为当前的很多sql都是正在更新系统表 sqlobj$data (或者使用sys用户直接把相关表清空也可以,同一个道理,主要是减少表的数据量)
---使用拥有dba角色的用户多次执行如下存过,要多次执行哟,直到 DBA_SQL_PLAN_BASELINES 中查询不出来数据为止
DECLARE
v BINARY_INTEGER;
BEGIN
FOR cur IN (SELECT * FROM DBA_SQL_PLAN_BASELINES a) LOOP
BEGIN
v := DBMS_SPM.drop_sql_plan_baseline(sql_handle => cur.SQL_HANDLE);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('error:' || SQLCODE || ',' || SQLERRM);
END;
Description
This defect has many apparent unrelated symptoms :
1) An unexpected growth is observed in sysaux tablespace with
optimizer_capture_sql_plan_baselines set to true.
2) Excessive redo generation
3) Additional recursive calls, increasing parsing and execution time.
The issue is caused by SQL Plan Management that keeps collecting baselines
in every execution instead of just on hard parsing.
Rediscovery Notes:
Query on v$sysaux_occupants shows SQL_MANAGEMENT_BASE as one of the highest.
The following query is observed in SQL traces:
MERGE INTO sqlobj$data
USING dual ON (:1 IS NULL)
WHEN MATCHED THEN
UPDATE SET comp_data = :2
WHERE signature = :3
AND category = :4
AND obj_type = :5
AND plan_id = :6
WHEN NOT MATCHED THEN
INSERT (signature, category, obj_type, plan_id, comp_data,
spare1, spare2)
VALUES (:7, :8, :9, :10, :11, null, null);
Workaround
Set optimizer_capture_sql_plan_baselines=false
HOOKS CBO:SPM parameter:optimizer_capture_sql_plan_baselines LIKELYAFFECTS XAFFECTS_11.1.0.6 XAFFECTS_V11010006 AFFECTS=11.1.0.6 XAFFECTS_11.1.0.7 XAFFECTS_V11010007 AFFECTS=11.1.0.7 XAFFECTS_11.2.0.1 XAFFECTS_V11020001 AFFECTS=11.2.0.1 XAFFECTS_11.2.0.2 XAFFECTS_V11020002 AFFECTS=11.2.0.2 XPRODID_5 PRODUCT_ID=5 PRODID-5 RDBMS XCOMP_RDBMS COMPONENT=RDBMS TAG_CBO TAG_PERF TAG_SPACEUSE TAG_SPM TAG_SYSAUX CBO PERF SPACEUSE SPM SYSAUX FIXED_11.2.0.3 FIXED_12.1.0.1 FIXED_WIN:B107P41 FIXED_WIN:B202P04
Further details on this issue can be found in Note:1304775.1
Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. For questions about this bug please consult Oracle Support. |
References
Bug:9910484 (This link will only work for PUBLISHED bugs)Note:245840.1 Information on the sections in this article
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26736162/viewspace-1248506/,如需转载,请注明出处,否则将追究法律责任。