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

            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 impact.  

Regards

Mudhalvan M.M

From: Tanel Poder [mailto:tanel_at_poderc.com] Sent: Thursday, March 18, 2010 9:34 AM
To: Mudhalvan Moovarkku
Cc: Oracle-L_at_freelists.org
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;  

DBMS_STATS.GET_PARAM('METHOD_OPT')

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

http://tech.e2sn.com            

              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 10.2.0.4 on Redhat Linux AS4 with update 4.          

               CURSOR_SHARING set to SIMILAR and OPTIMIZER_MODE=CHOOSE.          

               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.          

        Regards

        Mudhalvan M.M            

--

http://www.freelists.org/webpage/oracle-l Received on Wed Mar 17 2010 - 19:38:52 CDT