RE: Global Cache and Enqueue Services statistics

  • From: "Hameed, Amir" <Amir.Hameed@xxxxxxxxx>
  • To: Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>
  • Date: Wed, 1 Aug 2012 15:23:21 -0400

Hi Riyaj,
Thank you for the analysis. Please see my comments below.

1.       All of the three indices belong to the table with high insert
activity. This is one of the most critical jobs in production and it
spawns ~ 30 processes that insert into this table concurrently. I have
just looked at the code and it does not seem to use any sequence when
inserting into the table. Unfortunately, we do not own partitioning and
therefore, it is not an option at this time.

2.       The sleep time and cache size is set to 30 and 30 respectively.
I will find out if the sleep time can be changed but I am not very
hopeful because these values have been set after carefully reviewing the
requirements.

 

I am looking into binding managers to specific instances so that all
processes from the same manager stick to the same RAC node.

 

Also, Like Gopal pointed out, the latency of above 40ms at the gc layer
seems too high and I am not sure what might be causing it.

 

Thanks,

Amir

From: Riyaj Shamsudeen [mailto:riyaj.shamsudeen@xxxxxxxxx] 
Sent: Wednesday, August 01, 2012 1:23 PM
To: Hameed, Amir
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Global Cache and Enqueue Services statistics

 

Hello Amir
   Thanks for the data. 
  ( I have hidden the table and object names to protect your privacy.
But, if you refer to node 1 AWR reports, you would instantly identify
the object  names )
  Intensity of the problem is listed in the order.
 
1. There are high amount buffer busy/gc buffer busy waits on  indexes on
one table. That table has high insert concurrency too .

    Investigate the table below and review the indexes on them. If the
application is populating the rows through sequence or if the indexes
are almost unique, then you might want to consider hash partitioning
those indexes or dropping those indexes.

    From the names of the table and indexes, I am guessing that all
three indexes in the top of gc buffer busy list is on the table listed
below statement with sql_id 4hzg9pmxdvuyz.

  Elapsed      CPU                  Elap per  % Total
  Time (s)   Time (s)  Executions   Exec (s)  DB Time    SQL Id
---------- ---------- ------------ ---------- ------- -------------

    14,072        141      330,725        0.0    13.2 4hzg9pmxdvuyz
Module: REP45XX_CP
INSERT INTO TXRMI0_XXX_XXXX_XXX_TMP VALUES (:B13 , :B12 , :B11 , :B1 ,
:B10
, :B9 , :B8 , :B7 , :B6 , :B5 , :B4 , NULL, NULL, 'Current
supplyXXXXXXXXXX' .

node 2:
   19,982        217      531,595        0.0    18.6 4hzg9pmxdvuyz
Module: REP084522_CP
INSERT INTO TXRMI0_XXXXXXXXXXXXX_TMP VALUES (:B13 , :B12 , :B11 , :B1 ,
:B10
, :B9 , :B8 , :B7 , :B6 , :B5 , :B4 , NULL, NULL, 'Current supply XXXXX

node 1:
    22,699        318      632,046        0.0    18.0 4hzg9pmxdvuyz
Module: REP84522_CP
INSERT INTO TXRMI0_XXXXXXXXXX+TMP VALUES (:B13 , :B12 , :B11 , :B1 ,
:B10
, :B9 , :B8 , :B7 , :B6 , :B5 , :B4 , NULL, NULL, 'Current supply meets
demand.
Order is not required in OQIT.', SYSDATE, :B3 , :B2 , 'SUCCESS', NULL)


Segments by Buffer Busy Waits       
-> % of Capture shows % of Buffer Busy Waits for each top segment
compared
-> with total Buffer Busy Waits for all segments captured by the
Snapshot

                                                                  Buffer
           Tablespace                      Subobject  Obj.          Busy
% of
Owner         Name    Object Name            Name     Type         Waits
Capture
---------- ---------- -------------------- ---------- ----- ------------
-------
REP        REPLACED_ID RIYAJ_INDEX1            INDEX       12,398
18.02
REP        REPLACED_ID RIYAJ_INDEX2            INDEX       12,092
17.58
APPLSYS    XXXXAOL_TB  FND_CONCURRENT_REQUE    TABLE       10,911
15.86
REP        REPLACED_ID RIYAJ_INDEX3            INDEX        7,083
10.30
APPLSYS    XXXXAOL_ID  WF_ITEM_ATTRIBUTE_VA    INDEX        5,443
7.91
          -------------------------------------------------------------

Segments by Global Cache Buffer Busy
-> % of Capture shows % of GC Buffer Busy for each top segment compared
-> with GC Buffer Busy for all segments captured by the Snapshot

                                                                      GC
           Tablespace                      Subobject  Obj.        Buffer
% of
Owner         Name    Object Name            Name     Type          Busy
Capture
---------- ---------- -------------------- ---------- ----- ------------
-------
REP        REPLACED_ID RIYAJ_INDEX1            INDEX       52,543
16.67
REP        REPLACED_ID RIYAJ_INDEX2            INDEX       48,681
15.44
REP        REPLACED_ID RIYAJ_INDEX3            INDEX       43,649
13.85
REP        REPLACED_TB SOME_OTHER_T            TABLE       18,214
5.78
INV        MFGPINV_ID  MTL_SUPPLY_N10          INDEX        9,614
3.05

2. fnd_concurrent_requests table is suffering from high gc buffer busy
waits too. Check if sleep_time and cache is properly set for managers
with higher number of workers (Ebiz). Since, the concurrent  manager
threads constantly peak in to fnd_Concurrent_requests table to look for
work and if your managers are running from all nodes, then gc buffer
busy waits occurs on   fnd_Concurrent_requests table. Also, verify that
these sql_id are efficient. at3hqt06quby2 and 8s9vxqq4rp290. Both
accessing fnd_Concurrent_requests table. I would increase cache to 30
and sleep_time to 60 seconds, but understand that can delay the requests
by one minute which may not be optimal for high frequency concurrent
requests such as inventory workers or RVCTP etc.

3. Row locking contention can be related to (2). FND managers select
rows to process and they can run in to locking contention if cache and
sleep time is not set properly.
 
Segments by Row Lock Waits         DB/Inst: M24VT3/m24vt301  Snaps:
3626-3656
-> % of Capture shows % of row lock waits for each top segment compared
-> with total row lock waits for all segments captured by the Snapshot

                                                                     Row
           Tablespace                      Subobject  Obj.          Lock
% of
Owner         Name    Object Name            Name     Type         Waits
Capture
---------- ---------- -------------------- ---------- ----- ------------
-------
APPLSYS    XXXX_TB FND_CONCURRENT_REQUE            TABLE       22,250
55.23
APPLSYS    XXXX_TB FND_USER                        TABLE        5,622
13.96

4. There were 29 gc remaster events too, but I think, that is not
causing problems to you. You might want to review which segment is
undergoing those issues. LMD and LMON trace files will be useful to
review that..

HTH
( Sorry, removing some part of thread here as I am got overposting error
earlier)
Cheers

Riyaj Shamsudeen
Principal DBA,
Ora!nternals -  http://www.orainternals.com - Specialists in
Performance, RAC and EBS
Blog: http://orainternals.wordpress.com
OakTable member http://www.oaktable.com and Oracle ACE Director

Co-author of the books: Expert Oracle Practices
<http://tinyurl.com/book-expert-oracle-practices/> , Pro Oracle SQL,
Expert PL/SQL Practices <http://tinyurl.com/book-expert-plsql-practices>


Join me for next RAC training in Fall 2012
<http://www.orainternals.com/services/training/advanced-rac-training/> :


 

         

        On Wed, Aug 1, 2012 at 11:21 AM, Riyaj Shamsudeen
<riyaj.shamsudeen@xxxxxxxxx> wrote:

        Amir 
          Can you post the top SQL by cluster wait time and also
segments suffering from high buffer busy waits/gc buffer busy waits?
Most probably, gc buffer busy waits are causing downstream initrans
based locking contention.If the statement is insert statement and if the
segment is index segment, can you tell us how is column values are
generated? 
           Better yet, I wouldn't mind if you can send whole AWR report
so that we can give you little bit more insights.
        Cheers
        
        Riyaj Shamsudeen
        Principal DBA,
        Ora!nternals -  http://www.orainternals.com - Specialists in
Performance, RAC and EBS
        Blog: http://orainternals.wordpress.com
        OakTable member http://www.oaktable.com and Oracle ACE Director

        Co-author of the books: Expert Oracle Practices
<http://tinyurl.com/book-expert-oracle-practices/> , Pro Oracle SQL,
Expert PL/SQL Practices <http://tinyurl.com/book-expert-plsql-practices>


        Join me for next RAC training in Fall 2012
<http://www.orainternals.com/services/training/advanced-rac-training/> :


         

         

 


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


Other related posts: