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 12:22:46 +0900
Hi
Thanks a lot and found I can not delete all histogram as you have
mentioned.
Now I know only few SQL have the multiple PLAN_HASH_VALUE. I think I
have remove only related columns histogram. Could you please confirm and
the method if you already done before.
select hash_value,plan_hash_value,count(*) from v$sql where sql_text
like '%:"SYS_B_0"%' group by hash_value,plan_hash_value;
HASH_VALUE PLAN_HASH_VALUE COUNT(*)
3722612061 3351193255 113
1983846182 3351193255 112
2831213151 2975737795 21
Regards
Mudhalvan M.M
________________________________
From: Tanel Poder [mailto:tanel@xxxxxxxxxx]
Sent: Thursday, March 18, 2010 9:44 AM
To: Mudhalvan Moovarkku
Cc: Oracle-L@xxxxxxxxxxxxx
Subject: Re: Library Cache and Share wait on Latch - Version Count Huge
After setting method_opt to REPEAT you will also need to manually drop
all the histograms, as they have been already created for you.
However I don't know your application/data, maybe you do need some
histograms somewhere, so you need to review the histograms you have
yourself and decide whether it's ok to get rid of them. If it's purely
an OLTP system with bind variables used everywhere, its probably ok to
get rid of all histograms as histograms and bind variables are a deadly
combination anyway - for stable performance.
--
Tanel Poder
http://tech.e2sn.com
On Thu, Mar 18, 2010 at 8:38 AM, Mudhalvan Moovarkku
<moovarkku.mudhalvan@xxxxxxxxxxxxxxxx> wrote:
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.
Other related posts: