11.2.0.2的SPM的一个bug

 

 

将该参数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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26736162/viewspace-1248506/