RE: Global Cache and Enqueue Services statistics

From: Hameed, Amir <Amir.Hameed_at_xerox.com>
Date: Wed, 1 Aug 2012 15:23:21 -0400
Message-ID: <304F58144267C5439E733532ABC9A3A1159EBD05_at_USA0300MS02.na.xerox.net>



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_at_gmail.com] Sent: Wednesday, August 01, 2012 1:23 PM To: Hameed, Amir
Cc: oracle-l_at_freelists.org
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_at_gmail.com> 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/> :                      

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Aug 01 2012 - 14:23:21 CDT

Original text of this message