Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: latch issue

Re: latch issue

From: Polarski, Bernard <Bernard.Polarski_at_atosorigin.com>
Date: Wed, 23 Aug 2006 10:19:31 +0200
Message-ID: <25D4919915CCF742A88EE3366D6D913D0D463E95@mailserver1>

 

The session_cached_cursors was set to 0, probably due to this DB 9i a result of an ugrade from 8i.

-----Original Message-----
From: Jonathan Lewis [mailto:jonathan_at_jlcomp.demon.co.uk] Sent: Wednesday, 23 August, 2006 9:45 AM To: Oracle Discussion List
Subject: *** SPAM scored: Low ***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

> 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





--
http://www.freelists.org/webpage/oracle-l
Received on Wed Aug 23 2006 - 03:19:31 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US