遭遇Bug 5918642

老外发来邮件：

-------------------------------------------------------------------------------

Hi Robinson,

I need help – on adw1u – user adwgu_intrc, pass –xxxxx

select * from INTRC_RPT_BENCH_VW where SID = '00106158' and RPT_NAME in ('3ININ_MB')

this sql is extremely slow – but has good explain plan

same sql on adwgq_intrc on adw1d – runs very fast

please help

if it returns no data – then please execute script to fill SID parameters:

insert into intrc_user_selct_prc select initv_id,'BOOKT','ALL' from intrc_initv_dim;

insert into intrc_user_selct_prc select initv_id,'CUST_SKID','-99999' from intrc_initv_dim;

insert into intrc_user_selct_prc select initv_id,'FPC_SKID','-99999' from intrc_initv_dim;

insert into intrc_user_selct_prc select initv_id,'GRP_NAME','ALL' from intrc_initv_dim;

insert into intrc_user_selct_prc select initv_id,'GTIN_SKID','-99999' from intrc_initv_dim;

insert into intrc_user_selct_prc select initv_id,'INITV2_SKID',to_char(initv_skid) from intrc_initv_dim;

insert into intrc_user_selct_prc select initv_id,'INITV_ID',initv_id from intrc_initv_dim;

insert into intrc_user_selct_prc select initv_id,'INITV_ORDR_ID','001' from intrc_initv_dim;

insert into intrc_user_selct_prc select initv_id,'INITV_SKID',to_char(initv_skid) from intrc_initv_dim;

insert into intrc_user_selct_prc select initv_id,'TRADE_CNL_ID','ALL' from intrc_initv_dim;

insert into intrc_user_selct_prc select initv_id,'VARNT_NAME','Consumer Benefit 1' from intrc_initv_dim;

commit;

--------------------------------------------------------------------

INTRC_RPT_BENCH_VW 是一个视图，这里我就不贴执行计划了，老外都说了执行计划看起来很好

经过一番调查后，给老外回了个邮件。

Hi Tomasz,

I’m running the SQL in a session, and monitoring it in another session: I’m using account BW9518 to run it in adw1u1.

SQL> select sid,event ,p1,p2,p3 from v$session where username='BW9518';

SID EVENT P1 P2 P3

------------- ------------------------------ ---------- ---------- ----------

4697 SQL*Net message to client 1413697536 1 0

4763 SQL*Net message from client 1413697536 1 0

4839 latch free 1.3835E+19 127 0

P2 means latch number Below is p2 info from ASH:

SQL> select p2,count(*) from v$active_session_history where session_id=4839 group by p2 ;

P2 COUNT(*)

---------- ----------

0 56

1 19

2 29

122 519

127 3267

128 112

214 27

1208 1

1212 1

1232 1

1254 1

1260 1

1980 22

6865 1

7083 1

7097 1

7101 1

475473 1

2.0663E+13 3

1.3835E+19 1

1.3835E+19 1

1.3835E+19 1

1.3835E+19 1

1.3835E+19 1

1.3835E+19 1

1.3835E+19 1

1.3835E+19 1

1.3835E+19 1

1.3835E+19 1

29 rows selected.

So I query what’s the name for that number:

SQL> select name,misses,sleeps,addr from v$latch where latch#=127;

NAME MISSES SLEEPS ADDR

------------------------------ ---------- ---------- ----------------

simulator lru latch 140213897 1866579 C000000101E182A8

I check simulator lru latch from metalink, I guess it’s a bug to affect our SQL:

Bug 5918642Heavy latch contention with DB_CACHE_ADVICE on

This note gives a brief overview of bug 5918642.
The content was last updated on: 01-APR-2008
Click here for details of each of the sections below.

Affects:

Product (Component)	Oracle Server (Rdbms)
Range of versions believed to be affected	Versions < 11.2
Versions confirmed as being affected	10.2.0.3
Platforms affected	Generic (all / most platforms affected)

Fixed:

This issue is fixed in		11.2 (Future Release) 10.2.0.4 (Server Patch Set) 11.1.0.7 (Server Patch Set)
Symptoms:		Related To:
Latch Contention Waits for "latch free"		Performance Monitoring DB_CACHE_ADVICE

Description

High simulator lru latch contention can occur when db_cache_advice is

set to ON if there is a large buffer cache.

Workaround:

 Set db_cache_advice to OFF

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. Always consult with Oracle Support for advice.

References

Bug:5918642 (This link will only work for PUBLISHED bugs)
Note:245840.1 Information on the sections in this article

The document said that the bug was confirmed as being affect on 10.2.0.3

SQL> select * from v$version;

BANNER

----------------------------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi

SQL> select count(*) from v$db_cache_advice;

COUNT(*)

----------

21

The db cache advice is set to on .