Re: Global Cache and Enqueue Services statistics

  • From: Tim Gorman <tim@xxxxxxxxx>
  • To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 31 Jul 2012 11:04:40 -0600

FREELIST GROUPS could not be a problem if the tablespaces involved are 
only to SEGMENT_SPACE_MANAGEMENT = MANUAL, which is no longer default.  
Even if someone chose the non-default space-management format, 
contention on FREELISTS and FREELIST GROUPS only affect INSERT 
operations, and the only event in the "/Top Five Timed Events/" report 
you provided which might result from INSERT activity is the "/enq: TX - 
row lock contention/" and that most definitely has nothing to do with 
freelists and everything to do with row-locking on SELECT ... FOR 

Of course, such an event might be occurring as the 6th or 10th most 
prevalent wait (i.e. not in the top 5), but since the top 5 consume 
roughly 85% of DB Time during this period, then it is unlikely to figure 
significantly anyway.

Enable SQL trace on the session, aggregate and analyze those trace files 
with either TKPROF, TRCANLZR, or (better yet) Hotsos/Method-R Profiler.  
Stop guessing, and work from facts. And don't be deterred by unpalatable 

Hope this helps...


On 7/31/2012 10:14 AM, Gaja Krishna Vaidyanatha wrote:
> Hi Amir,
> >From the performance data you have shared, it is evident that RAC's 
> >consumption is approximately 30% of Elapsed Time for your workload. In the 
> >case of your application, that is the overhead that RAC poses in a 3-node 
> >cluster configuration. The row-level locking issue can (and will) exacerbate 
> >the elapsed times of everything that RAC has to do in the form of 
> >"inter-instance communication". I would definitely focus on determining why 
> >14% of DB Time is spent on locking. The application is incurring "lock 
> >waits" that is averaging 1.3 seconds per occurrence. In the realm of a 
> >normal transactional locking application, that is an eternity.
> And to Tim Gorman's point, the data does basically show to you, what life is 
> going to be in a multi-instance-shared-database architecture environment. 
> This could be one of those cases, where an application that performs 
> reasonably well in a single-instance configuration, will fall-apart in a 
> multi-instance configuration WITHOUT additional work to deal with the 
> "intricacies of RAC". I would definitely give Tim's suggestion of setting 
> CLUSTER_DATABASE from one of the instances and re-running your tests, a try.  
> Also, I am not convinced at this time whether "Freelists Groups" set at 1 is 
> the cause of your performance bane. I personally would rather focus on 
> solving the locking issue first, re-running your tests again and then go down 
> the path of Freelist Groups manipulation (if the performance data deems it to 
> be necessary). It may be the case with yours, that you will have to deal with 
> "the perception of HA" in a completely different fashion. Do keep us
>   posted.
> Cheers,
> Gaja
> Gaja Krishna Vaidyanatha,
> CEO & Founder, DBPerfMan LLC
> Phone - +1-650-743-6060
> LinkedIn -
> Co-author: Oracle Insights:Tales of the Oak Table 
> -
> Primary Author: Oracle Performance Tuning 101 -
> Enabling Cloud Deployment & Management for Oracle Databases
> ________________________________
>   From: "Hameed, Amir"<Amir.Hameed@xxxxxxxxx>
> To:gajav@xxxxxxxxx;oracle-l@xxxxxxxxxxxxx  
> Sent: Monday, July 30, 2012 1:47 PM
> Subject: RE: Global Cache and Enqueue Services statistics
> Hi Gaja,
> Below are the top-5 wait events which are the same on all nodes:
> Top 5 Timed Foreground Events
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>                                                             Avg
>                                                            wait   % DB
> Event                                 Waits     Time(s)   (ms)   time Wait 
> Class
> ------------------------------ ------------ ----------- ------ ------ 
> ----------
> db file sequential read          54,365,842      34,374      1   32.3 User I/O
> gc buffer busy acquire              461,651      18,904     41   17.8 Cluster
> enq: TX - row lock contention        11,506      15,269   1327   14.4 
> Applicatio
> DB CPU                                           11,476          10.8
> gc current block busy               255,945      10,747     42   10.1 Cluster


Other related posts: