latch issue

From: rjamya <rjamya_at_gmail.com>
Date: Tue, 22 Aug 2006 07:49:15 -0400
Message-ID: <9177895d0608220449k49473d43m567dd2f7af602b6a@mail.gmail.com>

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?

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Aug 22 2006 - 06:49:15 CDT