| row cache lock/dc_users?? [message #500057] |
Thu, 17 March 2011 09:19  |
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  |
 |
Michel Cadot
Messages: 68645
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 #500065 is a reply to message #500061] |
Thu, 17 March 2011 09:49 |
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 #500068 is a reply to message #500065] |
Thu, 17 March 2011 10:01 |
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 #500071 is a reply to message #500070] |
Thu, 17 March 2011 10:15 |
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 #500086 is a reply to message #500075] |
Thu, 17 March 2011 13:09 |
chris32680
Messages: 92
Registered: January 2006
Location: Charlotte
|
Member |
|
|
Michel Cadot wrote on Thu, 17 March 2011 12:14Quote: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 #500113 is a reply to message #500108] |
Thu, 17 March 2011 15:14 |
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 |
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
