RE: Library Cache and Share wait on Latch - Version Count Huge

  • From: "Mudhalvan Moovarkku" <moovarkku.mudhalvan@xxxxxxxxxxxxxxxx>
  • To: "Tanel Poder" <tanel@xxxxxxxxxx>
  • Date: Thu, 18 Mar 2010 09:38:52 +0900

Hi Tanel

            Thanks for your suggestion. 


            I am very sorry due the bad application we will not be able
to change SIMILAR to FORCE. 


            So I have to clear the Histograms


            I this METHOD_OPT from AUTO to REPEAT will help me. 


I will check on the dbms_stats.get_param and change it to REPEAT if no



Mudhalvan M.M


From: Tanel Poder [mailto:tanel@xxxxxxxxxx] 
Sent: Thursday, March 18, 2010 9:34 AM
To: Mudhalvan Moovarkku
Cc: Oracle-L@xxxxxxxxxxxxx
Subject: Re: Library Cache and Share wait on Latch - Version Count Huge


Get rid of the histograms or change the CURSOR_SHARING away from SIMILAR
(to FORCE for example if you need it).


There's a bug which number I don't remember where CURSOR_SHARING =
SIMILAR combined with histograms will cause a huge amount of new child
cursors to be generated. And the bad thing is, not even the
V$SQL_SHARED_CURSOR shows what's wrong in 10g, in 11g they've added a
new HASH_MATCH_FAILED condition for that.


I personally don't like cursor_sharing=similar anyway and also I don't
like having histograms being created without my knowledge, so you should
change your default METHOD_OPT from from AUTO to REPEAT, that way Oracle
only refreshes these histograms which already have been created (by you)
and doesn't create new ones based on its "clever" logic:


SQL> select dbms_stats.get_param('METHOD_OPT') from dual;




FOR ALL COLUMNS SIZE AUTO <<--- better to set this to REPEAT



Of course first you need to manually get rid of the histograms that were
automatically created by the AUTO option...



Tanel Poder



              Greetings. When I check the wait events recently for few
days Library Cache and Share Pool wait got the very huge value.


              When i check the V$SQLAREA few sql have very huge versions
more than 3000.


               My Database version is 10g Rel2 on Redhat Linux
AS4 with update 4.




               This is happening only recently and is it related to
Automatic Statistics done on 10g Rel 2.


              Please let me know how to check and avoid it.



        Mudhalvan M.M



Other related posts: