latch issue
- From: rjamya <rjamya@xxxxxxxxx>
- To: "Oracle Discussion List" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 22 Aug 2006 07:49:15 -0400
Howdy,
we recently had to change from cs=force to cs=exact on our 9204 RAC
instances and now we are seeing a lot of latching issues (in descending
order per statspack)
* row cache objects
* row cache enqueue latch
* lib cache
* shared pool
Tom Kyte would probably shudder with disbelief when I tell you this app is
not exactly bind variable friendly. As we have started further analysis, I
am havign trouble getting adaquet docs about the row cache latches. What can
I do to find more information? How can I track more details about these
latches? This app heavily uses GTTs that have multiple indexes.
Here is a sample output from gv$rowcache ....
SELECT inst_id, parameter ,sum(gets) ,sum(getmisses)
, 100*sum(gets - getmisses) / sum(gets) pct_succ_gets
, sum(modifications) updates
FROM gV$ROWCACHE
WHERE gets > 0 GROUP BY inst_id, parameter order by 5 asc
/
inst_id parameter gets getmisses
pct_succ_gets updates
01 dc_table_scns 112 112
.000 0
01 dc_constraints 1,056 275
73.958 338
02 dc_files 2,706 440
83.740 0
02 dc_sequences 18,151 2,948
83.758 18,151
01 dc_files 14,476 2,332
83.891 0
02 dc_constraints 149 9
93.960 0
02 dc_database_links 684 39
94.298 0
01 dc_sequences 237,806 11,479
95.173 237,806
02 dc_tablespace_quotas 39,092 1,504
96.153 9,767
01 dc_tablespace_quotas 151,895 4,569
96.992 77,325
01 dc_global_oids 479,554 5,734
98.804 0
02 dc_global_oids 101,732 1,202
98.818 0
01 dc_histogram_data_values 307,496 2,600
99.154 24
01 dc_histogram_data 69,647 231
99.668 12
01 dc_database_links 44,820 105
99.766 0
01 dc_objects 231,852,373 476,140
99.795 5,661
02 dc_objects 69,662,007 113,469
99.837 208
01 dc_user_grants 6,711,365 7,897
99.882 0
02 dc_histogram_data_values 210,282 244
99.884 24
02 dc_histogram_data 62,903 52
99.917 12
01 dc_histogram_defs 1,572,883,800 654,042
99.958 112,329
02 dc_user_grants 2,615,682 1,026
99.961 0
02 dc_rollback_segments 739,314 243
99.967 232
01 dc_rollback_segments 2,045,863 663
99.968 663
01 dc_segments 1,691,301,902 182,725
99.989 3,756
02 dc_histogram_defs 1,773,343,580 198,140
99.989 100,302
01 dc_usernames 51,405,579 5,078
99.990 0
02 dc_segments 510,598,824 50,171
99.990 139
02 dc_usernames 15,395,425 708
99.995 2
02 dc_profiles 27,948 1
99.996 0
01 dc_profiles 124,074 2
99.998 0
01 dc_users 1,405,261,232 9,727
99.999 33
01 dc_tablespaces 1,353,230,526 903
100.000 0
02 dc_users 413,832,529 1,237
100.000 48
02 dc_tablespaces 400,370,402 133
100.000 0
Any ideas would be really appreciated as we get ready to log itar with ows
either today or tomorrow.
TIA
Raj
--
----------------------------------------------
Got RAC?
Other related posts:
