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
Amir,
FREELIST GROUPS could not be a problem if the tablespaces involved are
SEGMENT_SPACE_MANAGEMENT = AUTO; FREELISTS and FREELIST GROUPS pertain
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
UPDATE, UPDATE, DELETE, or MERGE.
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
truths.
Hope this helps...
-Tim
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
> http://www.dbperfman.com
> http://www.dbcloudman.com
>
> Phone - +1-650-743-6060
> LinkedIn -http://www.linkedin.com/in/gajakrishnavaidyanatha
>
> Co-author: Oracle Insights:Tales of the Oak Table
> -http://www.apress.com/9781590593875
> Primary Author: Oracle Performance Tuning 101 -http://www.amzn.com/0072131454
> 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
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
