Hello everyone,
Note: Apologize for the bad formatting, tried
but it seems I forgot how to use it
BANNER
----------------------------------------------------------
Oracle Database 11g Release 11.2.0.2.0 - 64bit Production
I've seen high "*latch: row cache objects*" in SP/ASH report for ~14 hours back, when the users were unable to connect to the database. There were,
WARNING: inbound connection timed out (ORA-3136)
Time: 30-APR-2012 02:24:36
Tracing not turned on.
Tns error struct:
errors all over the alert log for the duration of 6 minutes of the problem.
I've put few records in bold due to which I concluded that the problem was with "dc_users" thing.
Can anybody tell me how/where I should proceed forward ?
SP report:
Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.84 Optimal W/A Exec %: 100.00
Library Hit %: 97.43 Soft Parse %: 87.86
Execute to Parse %: 22.54 Latch Hit %: 99.95
Parse CPU to Parse Elapsd %: 0.30 % Non-Parse CPU: 87.83
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 45.09 46.98
% SQL with executions>1: 11.49 13.15
% Memory for SQL w/exec>1: 72.96 21.33
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
latch: row cache objects 6,655 634,260 95306 97.0
log file sync 289,923 6,469 22 1.0
CPU time 5,039 .8
db file sequential read 310,084 2,840 9 .4
log file parallel write 451,706 1,144 3 .2
ASH Report
Analysis Begin Time: 30-Apr-12 02:24:00
Analysis End Time: 30-Apr-12 02:30:00
Elapsed Time: 6.0 (mins)
Begin Data Source: DBA_HIST_ACTIVE_SESS_HISTORY
in AWR snapshot 12185
End Data Source: DBA_HIST_ACTIVE_SESS_HISTORY
in AWR snapshot 12185
Sample Count: 1,385
Average Active Sessions: 38.47
Avg. Active Session per CPU: 1.60
Report Target: None specified
Top User Events DB/Inst: NIKU/niku (Apr 30 02:24 to 02:30)
Avg Active
Event Event Class % Event Sessions
----------------------------------- --------------- ---------- ----------
latch: row cache objects Concurrency 75.45 29.03
CPU + Wait for CPU CPU 9.75 3.75
log file sync Commit 3.83 1.47
db file sequential read User I/O 3.61 1.39
-------------------------------------------------------------
Top Event P1/P2/P3 Values DB/Inst: NIKU/niku (Apr 30 02:24 to 02:30)
Event % Event P1 Value, P2 Value, P3 Value % Activity
------------------------------ ------- ----------------------------- ----------
Parameter 1 Parameter 2 Parameter 3
-------------------------- -------------------------- --------------------------
latch: row cache objects 75.60 "42287858200","279","0" 75.60
address number tries
1* select addr, latch#, child#, name, misses, gets from v$latch_children where name like '%row%cache%objec%' order by gets , misses
niku> /
ADDR LATCH# CHILD# NAME MISSES GETS
---------------- ---------- ---------- -------------------------------------------------- ---------- ----------
0000000A16FF21C8 279 26 row cache objects 0 0
0000000A16FF14C8 279 2 row cache objects 0 0
00000009D88D7ED8 279 3 row cache objects 0 0
0000000A16FF1B48 279 14 row cache objects 0 0
00000009D88D8558 279 15 row cache objects 0 0
0000000A16FF1CE8 279 17 row cache objects 0 0
0000000A26265A28 279 19 row cache objects 0 0
0000000A16FF1E88 279 20 row cache objects 0 0
00000009D88D8898 279 21 row cache objects 0 0
0000000A26265BC8 279 22 row cache objects 0 0
0000000A16FF2028 279 23 row cache objects 0 0
00000009D88D8A38 279 24 row cache objects 0 0
0000000A26265D68 279 25 row cache objects 0 0
00000009D88D8BD8 279 27 row cache objects 0 0
0000000A26265F08 279 28 row cache objects 0 0
00000009D88D8D78 279 30 row cache objects 0 0
0000000A262660A8 279 31 row cache objects 0 0
0000000A16FF2508 279 32 row cache objects 0 0
0000000A16FF26A8 279 35 row cache objects 0 0
00000009D88D90B8 279 36 row cache objects 0 0
0000000A262663E8 279 37 row cache objects 0 0
0000000A262668C8 279 46 row cache objects 0 0
0000000A26266A68 279 49 row cache objects 0 0
0000000A16FF2368 279 29 row cache objects 0 11
0000000A16FF2848 279 38 row cache objects 0 116
0000000A16FF29E8 279 41 row cache objects 0 200
00000009D88D93F8 279 42 row cache objects 0 318
00000009D88D9258 279 39 row cache objects 0 1010
0000000A16FF2EC8 279 50 row cache objects 0 1406
00000009D88D9598 279 45 row cache objects 0 1472
0000000A26266588 279 40 row cache objects 0 1705
0000000A26266728 279 43 row cache objects 0 7383
0000000A16FF2B88 279 44 row cache objects 0 32346
00000009D88D98D8 279 51 row cache objects 19 63948
0000000A26265888 279 16 row cache objects 0 88045
0000000A26266248 279 34 row cache objects 0 141176
00000009D88D9738 279 48 row cache objects 0 326672
0000000A16FF19A8 279 11 row cache objects 867 1770385
00000009D88D8078 279 6 row cache objects 9 1979542
0000000A16FF2D28 279 47 row cache objects 2 3435018
00000009D88D86F8 279 18 row cache objects 2557 14956121
0000000A26265068 279 1 row cache objects 224 24335868
0000000A262653A8 279 7 row cache objects 29760 133991553
00000009D88D8F18 279 33 row cache objects 60612 677263122
00000009D88D83B8 279 12 row cache objects 23981 739014460
0000000A26265208 279 4 row cache objects 19973399 852043775
0000000A26265548 279 10 row cache objects 280137 856097342
00000009D88D8218 279 9 row cache objects 715879777 1219000976
0000000A262656E8 279 13 row cache objects 3856073 2397402780
0000000A16FF1668 279 5 row cache objects 12763217 2920278217
*0000000A16FF1808 279 8 row cache objects 67329804 4145389092*
51 rows selected.
niku> list
1 select addr, latch#, child#, name, misses, gets from v$latch_children where name like '%row%cache%objec%' order by gets , misses
niku> select distinct s.kqrstcln latch#,r.cache#,r.parameter name,r.type,r.subordinate#
from v$rowcache r,x$kqrst s
where r.cache#=s.kqrstcid
order by 1,4,5; 2 3 4
LATCH# CACHE# NAME TYPE SUBORDINATE#
---------- ---------- -------------------------------------------------- ----------- ------------
1 3 dc_rollback_segments PARENT
2 1 dc_free_extents PARENT
3 4 dc_used_extents PARENT
4 2 dc_segments PARENT
5 0 dc_tablespaces PARENT
6 5 dc_tablespace_quotas PARENT
7 6 dc_files PARENT
*8 10 dc_users PARENT*
*8 7 dc_users SUBORDINATE 0*
*8 7 dc_users SUBORDINATE 1*
*8 7 dc_users SUBORDINATE 2*
9 8 dc_objects PARENT
9 8 dc_object_grants SUBORDINATE 0
10 17 dc_global_oids PARENT
11 12 dc_constraints PARENT
12 13 dc_sequences PARENT
13 16 dc_histogram_defs PARENT
13 16 dc_histogram_data SUBORDINATE 0
13 16 dc_histogram_data SUBORDINATE 1
14 54 dc_sql_prs_errors PARENT
15 32 kqlsubheap_object PARENT
16 19 dc_table_scns PARENT
16 19 dc_partition_scns SUBORDINATE 0
17 18 dc_outlines PARENT
18 14 dc_profiles PARENT
19 47 realm cache PARENT
19 47 realm auth SUBORDINATE 0
20 48 Command rule cache PARENT
21 49 Realm Object cache PARENT
21 49 Realm Subordinate Cache SUBORDINATE 0
22 46 Rule Set Cache PARENT
23 34 extensible security user and rol PARENT
24 35 extensible security principal pa PARENT
25 37 extensible security UID to princ PARENT
26 36 extensible security principal na PARENT
27 33 extensible security principal ne PARENT
28 38 XS security class privilege PARENT
29 39 extensible security midtier cach PARENT
30 43 AV row cache 1 PARENT
31 44 AV row cache 2 PARENT
32 45 AV row cache 3 PARENT
33 15 global database name PARENT
34 20 rule_info PARENT
35 21 rule_or_piece PARENT
35 21 rule_fast_operators SUBORDINATE 0
36 23 dc_qmc_ldap_cache_entries PARENT
37 52 qmc_app_cache_entries PARENT
38 53 qmc_app_cache_entries PARENT
39 27 qmtmrcin_cache_entries PARENT
40 28 qmtmrctn_cache_entries PARENT
41 29 qmtmrcip_cache_entries PARENT
42 30 qmtmrctp_cache_entries PARENT
43 31 qmtmrciq_cache_entries PARENT
44 26 qmtmrctq_cache_entries PARENT
45 9 qmrc_cache_entries PARENT
46 50 qmemod_cache_entries PARENT
47 24 outstanding_alerts PARENT
48 22 dc_awr_control PARENT
49 25 SMO rowcache PARENT
50 40 sch_lj_objs PARENT
51 41 sch_lj_oids PARENT
61 rows selected.
niku> select parameter, gets from v$rowcache order by gets desc;
PARAMETER GETS
-------------------------------- ----------
dc_users 2802019571
dc_tablespaces 2405092307
dc_objects 1815427326