Re: latch issue

Rowcache latches are simply latches held so that Oracle can safely examine the row cache whilst parsing and optimising a statement. Excess activity is just another symptom of excessive hard parsing.

Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

• Original Message ----- From: "rjamya" <rjamya_at_gmail.com> To: "Oracle Discussion List" <oracle-l_at_freelists.org> Sent: Tuesday, August 22, 2006 12:49 PM Subject: latch issue

> 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?
>

No virus found in this incoming message. Checked by AVG Free Edition.
Version: 7.1.405 / Virus Database: 268.11.4/424 - Release Date: 21/08/2006

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