Re: Library Cache and Share wait on Latch - Version Count Huge
- From: Tanel Poder <tanel@xxxxxxxxxx>
- To: moovarkku.mudhalvan@xxxxxxxxxxxxxxxx
- Date: Thu, 18 Mar 2010 08:34:12 +0800
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:
