Hard parsing and fixed table statistics

From: Hameed, Amir <Amir.Hameed_at_xerox.com>
Date: Thu, 5 Sep 2013 01:24:09 +0000
Message-ID: <AF02C941134B1A4AB5F61A726D08DCED0DE0CA33_at_USA7109MB012.na.xerox.net>

Hi Folks,
Last week, we ran into a situation where one of the four RAC instances froze for about 10 minutes and no one could login during that time period. Here is a brief description of the RAC environment: Oracle ERP 11.5.10.2
Oracle Grid and RDBMS 11.2.0.3.6
Three nodes are dedicated for Concurrent Processing Fourth node is dedicated for the Application connections (forms, http, etc.) The above mentioned issue occurred on the 4th node, the application connections node. AWR reports showed the following events: Top 5 Timed Foreground Events
Event

Waits

Time(s)

Avg wait (ms)

% DB time

Wait Class

latch: row cache objects

756,294

273,519

362

32.37

Concurrency

library cache: mutex X

56,684

250,761

4424

29.67

Concurrency

row cache lock

129,038

117,973

914

13.96

Concurrency

cursor: pin S wait on X

1,488

98,947

66497

11.71

Concurrency

enq: TX - row lock contention

232

17,660

76119

2.09

Application

To investigate the issue further, we also opened an SR with Oracle. One of the recommendations that came from Oracle is that because of hard parses, statistics on fixed table should be collected and that should take care of the DB hanging issue. I am trying to find out if there is any dependency of hard parses on fixed table statistics?

One of the things that I noticed was that there are a lot of child cursors created for exact same SQL in the instance that had the issue: select sql_id, count(*) from gv$sql where inst_id=4 group by sql_id having count(*) > 10 order by count(*) ;

czsrhk0tmrs8r        544
3g2ugdp1af2h8        628
a1xgxtssv5rrp       1296

One of the analysts who had worked on the SR previously had suggested that we were hitting a bug that can occur in RAC environments and can cause the creation of multiple child cursors.

Can someone please shed some light on if there is a correlation between hard parses on fixed table statistics?

Thank you,
Amir

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Sep 05 2013 - 03:24:09 CEST