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
impact.
Regards
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;
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
Other related posts:
