Re: Global Cache and Enqueue Services statistics
Date: Wed, 1 Aug 2012 12:23:10 -0500
Message-ID: <CAA2DszxRbnbsnNB1srb0QA85joTHkivaYS=remU+H3eb=HvxaQ_at_mail.gmail.com>
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 % TotalTime (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,62213.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/>:
<http://tinyurl.com/book-expert-plsql-practices>
> > 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://tinyurl.com/book-expert-plsql-practices> >> >> >
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Aug 01 2012 - 12:23:10 CDT