Home » RDBMS Server » Performance Tuning » row cache lock/dc_users?? (oracle 11g solaris 10)
row cache lock/dc_users?? [message #500057] Thu, 17 March 2011 09:19 Go to next message
chris32680
Messages: 92
Registered: January 2006
Location: Charlotte
Member
Hi all,
I'm trying, despite my severe lack of knowledge on the subject, to determine why one of our client's databases is experiencing random slowness. It's quite possible it's application related, but i'm just trying to determine the actual cause now.

Initially i started reviewing the performance data from Enterprise Manager which led me to the point where i now believe the 'row cache locks' are the source of the slowness that their users are seeing.

After googling and trying to find out what to do about this newfound (possibly incorrect knowledge), i came across a set of sql statements to determine what's causing the row cache locks....but that's kind of where i'm stuck.

Could anyone give me any suggestion on how to proceed from here?

Thanks
10:13:57 >select * from v$version
10:16:50   2  ;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Solaris: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production


10:13:55 >;
  1* select p1text,p1,p2text,p2,p3text,p3 from v$session where event = 'row cache lock'
10:13:57 >/

P1TEXT                  P1 P2TEXT                  P2 P3TEXT                  P3
--------------- ---------- --------------- ---------- --------------- ----------
cache id                 7 mode                     0 request                  3
cache id                 7 mode                     0 request                  3
cache id                 7 mode                     0 request                  3

10:17:18 >;
  1* select PARAMETER ,COUNT ,GETS ,GETMISSES ,MODIFICATIONS from v$rowcache where cache#=7
10:17:21 >/

PARAMETER                             COUNT       GETS  GETMISSES MODIFICATIONS
-------------------------------- ---------- ---------- ---------- -------------
dc_users                                  0          0          0             0
dc_users                                 64    4672795        209             0
dc_users                                  0          0          0             0


09:59:43 >;
  1*  SELECT parameter, sum(gets), sum(getmisses), 
100*sum(gets - getmisses) / sum(gets) pct_succ_gets, 
sum(modifications) updates FROM V$ROWCACHE WHERE gets > 0 
GROUP BY parameter
10:17:33 >/

PARAMETER                         SUM(GETS) SUM(GETMISSES) PCT_SUCC_GETS      UPDATES
-------------------------------- ---------- -------------- ------------- ------------
dc_constraints                         4857           1746          64.1        4,857
qmtmrctp_cache_entries                    2              2            .0            0
outstanding_alerts                    47585            844          98.2          959
dc_awr_control                        37948             41          99.9          938
sch_lj_objs                             701            145          79.3            0
sch_lj_oids                           17803            277          98.4            0
dc_objects                        209487880         170497          99.9       49,837
dc_table_scns                          1016           1016            .0            0
qmtmrciq_cache_entries                  114            114            .0            0
qmtmrctq_cache_entries                 1113           1113            .0            0
dc_used_extents                        2917           1859          36.3        2,897
dc_users                          284643403           8755         100.0        9,434
qmrc_cache_entries                      105            105            .0            0
dc_histogram_defs                  34986609         626671          98.2      221,618
dc_profiles                          242867              3         100.0            1
dc_object_grants                   45597627          14167         100.0            0
dc_histogram_data                  52464963         141933          99.7      244,007
dc_segments                        38812592          57322          99.9       27,044
qmtmrctn_cache_entries                   68             68            .0            0
dc_files                              34257            285          99.2            0
dc_sequences                         130515            731          99.4      130,515

PARAMETER                         SUM(GETS) SUM(GETMISSES) PCT_SUCC_GETS      UPDATES
-------------------------------- ---------- -------------- ------------- ------------
dc_global_oids                     19594442           4805         100.0           94
global database name                1311000             11         100.0            0
qmc_app_cache_entries                    28             28            .0            0
dc_rollback_segments                 376552             21         100.0           83
dc_free_extents                       24854           3029          87.8        7,352
dc_tablespaces                    260267398            236         100.0            0
dc_tablespace_quotas                      4              2          50.0            0
qmtmrcin_cache_entries                   38             38            .0            0

29 rows selected.

[Updated on: Thu, 17 March 2011 09:33] by Moderator

Report message to a moderator

Re: row cache lock/dc_users?? [message #500061 is a reply to message #500057] Thu, 17 March 2011 09:37 Go to previous messageGo to next message
Michel Cadot
Messages: 59748
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
It's quite possible it's application related, but i'm just trying to determine the actual cause now.

If it is the application then you cannot see it in the database.

Quote:
Initially i started reviewing the performance data from Enterprise Manager

You did it the wrong, you should FIRST investigate the application.

Quote:
where i now believe the 'row cache locks' are the source of the slowness that their users are seeing.

How did you come to this conclusion? Explain.

Quote:
select p1text,p1,p2text,p2,p3text,p3 from v$session where event = 'row cache lock'

Event is valid ONLY if the session is waiting, you didn't restrict on this condition.

Quote:
select PARAMETER ,COUNT ,GETS ,GETMISSES ,MODIFICATIONS from v$rowcache where cache#=7

Why 7?

What do you conclude from your queries? Why?

Regards
Michel
Re: row cache lock/dc_users?? [message #500064 is a reply to message #500061] Thu, 17 March 2011 09:48 Go to previous messageGo to next message
BlackSwan
Messages: 23029
Registered: January 2009
Senior Member
>determine why one of our client's databases is experiencing random slowness.
Compare/contrast STATSPACK/AWR reports between when performance is OK to when performance is not OK.

>It's quite possible it's application related,
Possible? Yes, approaching 100% certainty!
Re: row cache lock/dc_users?? [message #500065 is a reply to message #500061] Thu, 17 March 2011 09:49 Go to previous messageGo to next message
chris32680
Messages: 92
Registered: January 2006
Location: Charlotte
Member
Thanks for the reply, Michel.

I'll try to explain this a bit.

First of all, our system and our client's systems were both recently upgraded from 9 to 11. We run the same applications as well. We upgraded our system first, and while we came across several little snags early that we worked through, our system is now very stable. Our client was upgraded the last week of February and experienced no issues for nearly 2 weeks. Early this week, they began complaining that some apps would randomly take much longer to process than usual. And it wasn't reproducible. The app would run perfectly 9 times out of 10, but that one time, the app would take sometimes 5 times longer.

I opened Enterprise Manager and looked at the performance graphs, and noticed quite a few spikes in the Concurrency data. I can't really tell the google and thought path that got me from that data to 'row cache lock', so I very well could've just jumped to an invalid conclusion.

The source that got me to run those queries is found here:
http://it.toolbox.com/blogs/surachart/row-cache-lock-35256

Thanks again for your time.
Re: row cache lock/dc_users?? [message #500067 is a reply to message #500065] Thu, 17 March 2011 10:00 Go to previous messageGo to next message
Michel Cadot
Messages: 59748
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I can't really tell the google and thought path that got me from that data to 'row cache lock'

So you can also assume this is not the point and start to search what is the real concurrency issue.
This is the first point to determine, which kind of concurrency issue have you?
You have the AWR data, we have not.

And do not trust all scripts you find on the web, first try to understand them and be sure they are accurate.

Regards
Michel
Re: row cache lock/dc_users?? [message #500068 is a reply to message #500065] Thu, 17 March 2011 10:01 Go to previous messageGo to next message
chris32680
Messages: 92
Registered: January 2006
Location: Charlotte
Member
Here is an AWR report from a 2 hour period yesterday when the problem was being reported the most.

The one part that caught my eye was this:


Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                           Avg
                                                          wait   % DB
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
row cache lock                        1,696       9,775   5763   54.1 Concurrenc
DB CPU                                            4,688          25.9
SQL*Net message from dblink           2,942         390    133    2.2 Network
db file sequential read           1,471,059         289      0    1.6 User I/O
enq: TX - row lock contention            52         149   2867     .8 Applicatio
Host CPU (CPUs:    4 Cores:    4 Sockets:    4)
~~~~~~~~         Load Average
               Begin       End     %User   %System      %WIO     %Idle
           --------- --------- --------- --------- --------- ---------
                0.86      1.23      17.5       6.7       0.0      75.8

Re: row cache lock/dc_users?? [message #500069 is a reply to message #500067] Thu, 17 March 2011 10:04 Go to previous messageGo to next message
chris32680
Messages: 92
Registered: January 2006
Location: Charlotte
Member
Michel Cadot wrote on Thu, 17 March 2011 11:00

And do not trust all scripts you find on the web, first try to understand them and be sure they are accurate.


Absolutely...i was just trying to provide whatever information i could. I trust you guys way more than random scripts on the web! Surprised
Re: row cache lock/dc_users?? [message #500070 is a reply to message #500068] Thu, 17 March 2011 10:06 Go to previous messageGo to next message
BlackSwan
Messages: 23029
Registered: January 2009
Senior Member
"In order for DDL to execute, it must acquire a row cache lock to lock the Data Dictionary information."
WHY? is application doing DDL so frequently?
I contend application only does DDL during application version upgrades during maintenance window.
Re: row cache lock/dc_users?? [message #500071 is a reply to message #500070] Thu, 17 March 2011 10:15 Go to previous messageGo to next message
chris32680
Messages: 92
Registered: January 2006
Location: Charlotte
Member
BlackSwan wrote on Thu, 17 March 2011 11:06
"In order for DDL to execute, it must acquire a row cache lock to lock the Data Dictionary information."
WHY? is application doing DDL so frequently?
I contend application only does DDL during application version upgrades during maintenance window.


One of the issues we have is our client likes to develop their own apps to supplement their business, and we end up supporting them.

Is there a way for me to find out exactly what these DDL's that are being called so frequently are?

Re: row cache lock/dc_users?? [message #500072 is a reply to message #500071] Thu, 17 March 2011 10:19 Go to previous messageGo to next message
BlackSwan
Messages: 23029
Registered: January 2009
Senior Member
>Is there a way for me to find out exactly what these DDL's that are being called so frequently are?
I'd start by querying V$SQL to see what I could see for like "CREATE" statements, etc.
Re: row cache lock/dc_users?? [message #500073 is a reply to message #500072] Thu, 17 March 2011 10:23 Go to previous messageGo to next message
Roachcoach
Messages: 1224
Registered: May 2010
Location: UK
Senior Member
Look for Truncate.....my money is on that.
Re: row cache lock/dc_users?? [message #500075 is a reply to message #500071] Thu, 17 March 2011 11:14 Go to previous messageGo to next message
Michel Cadot
Messages: 59748
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is there a way for me to find out exactly what these DDL's that are being called so frequently are?

Create a DDL trigger and log all of them.

Regards
Michel
Re: row cache lock/dc_users?? [message #500086 is a reply to message #500075] Thu, 17 March 2011 13:09 Go to previous messageGo to next message
chris32680
Messages: 92
Registered: January 2006
Location: Charlotte
Member
Michel Cadot wrote on Thu, 17 March 2011 12:14
Quote:
Is there a way for me to find out exactly what these DDL's that are being called so frequently are?

Create a DDL trigger and log all of them.

Regards
Michel


is there something in the AWR report that i can point to that definitively points the finger at excessive ddl? I'll need some proof for them to let me install a new trigger.

Thanks again guys.
Re: row cache lock/dc_users?? [message #500091 is a reply to message #500086] Thu, 17 March 2011 13:27 Go to previous messageGo to next message
Michel Cadot
Messages: 59748
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
is there something in the AWR report that i can point to that definitively points the finger at excessive ddl

No.

Quote:
I'll need some proof for them to let me install a new trigger.

Either use Log Miner or argue that you cannot achieve the task without the trigger.

Regards
Michel
Re: row cache lock/dc_users?? [message #500093 is a reply to message #500091] Thu, 17 March 2011 13:32 Go to previous messageGo to next message
chris32680
Messages: 92
Registered: January 2006
Location: Charlotte
Member
Fair enough. Just for my knowledge, what part of the AWR at least suggests that it's DDL's causing the issue?
Re: row cache lock/dc_users?? [message #500097 is a reply to message #500093] Thu, 17 March 2011 13:53 Go to previous messageGo to next message
Michel Cadot
Messages: 59748
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No one but the "row cache lock" waits is an indication of DDL.

Regards
Michel
Re: row cache lock/dc_users?? [message #500098 is a reply to message #500097] Thu, 17 March 2011 13:58 Go to previous messageGo to next message
chris32680
Messages: 92
Registered: January 2006
Location: Charlotte
Member
Michel Cadot wrote on Thu, 17 March 2011 14:53
No one but the "row cache lock" waits is an indication of DDL.

Regards
Michel


perfect. thanks for the education.
Re: row cache lock/dc_users?? [message #500105 is a reply to message #500098] Thu, 17 March 2011 14:58 Go to previous messageGo to next message
BlackSwan
Messages: 23029
Registered: January 2009
Senior Member
http://www.toadworld.com/Experts/GuyHarrisonsImprovingOraclePerformance/ResolvingOracleContention/Feb2008DealingwithLockContention/ta bid/304/Default.aspx

"The "row cache" is an area in Oracle's shared pool that keeps data dictionary information in memory. The "row cache" lock prevents two sessions from updating the same information in the row cache simultaneously.
Most of the time, row cache updates are infrequent since data dictionary information is fairly static. However, if row cache information is being updated frequently then contention for the row cache lock can occur.

One scenario that can cause row cache contention is rapid sequence number generation from a sequence with an insufficient CACHE value. By default, sequences only cache 20 numbers. Every time the cache is exhausted, Oracle needs to update the row cache to get the next set of numbers. If sequence numbers are being allocated very rapidly then row cache lock waits might become serious.
"
Re: row cache lock/dc_users?? [message #500108 is a reply to message #500105] Thu, 17 March 2011 15:06 Go to previous messageGo to next message
Michel Cadot
Messages: 59748
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To check this point, it should be interesting to get the result of:
select sum(dlm_requests), sum(dlm_conflicts) from v$rowcache where parameter='dc_sequences';

or, better, the same on DBA_HIST_ROWCACHE_SUMMARY at the time of the problem.
Regards
Michel
Re: row cache lock/dc_users?? [message #500113 is a reply to message #500108] Thu, 17 March 2011 15:14 Go to previous messageGo to next message
chris32680
Messages: 92
Registered: January 2006
Location: Charlotte
Member
great info. Thanks guys.

Michel,
Here's the result of that query as of right now. I'll see if i can catch it when the issue is being reported with the other:


16:11:55 >select sum(dlm_requests), sum(dlm_conflicts) from v$rowcache where parameter='dc_sequences';

SUM(DLM_REQUESTS) SUM(DLM_CONFLICTS)
----------------- ------------------
                0                  0

16:11:57 >
Re: row cache lock/dc_users?? [message #501309 is a reply to message #500113] Mon, 28 March 2011 08:52 Go to previous messageGo to next message
chris32680
Messages: 92
Registered: January 2006
Location: Charlotte
Member
Just for everyone's knowledge. I ended up entering a SR with Oracle to track this down.

Oracle has confirmed that due to an existing bug in 11.2.0.1, described in doc 1162566.1.

It looks like the issue occurs when there is a logon atempt that fails (not necessarily for this process), which required a row cache lock in order to modify the user data.

We found a lot of "custom" client apps that we had nothing to do with, had invalid logins, so we're pretty confident at this point that that was the actual cause of the issue. We're having our client go through and resolve these invalid logins.

Thanks everyone for the help.
Re: row cache lock/dc_users?? [message #501311 is a reply to message #501309] Mon, 28 March 2011 09:03 Go to previous message
Michel Cadot
Messages: 59748
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback.

Regards
Michel
Previous Topic: clarification of query EXPLAIN plan
Next Topic: How to assure partition table is better than non-partition table (2 MERGED)
Goto Forum:
  


Current Time: Sun Nov 23 02:59:23 CST 2014

Total time taken to generate the page: 0.08466 seconds