Home » RDBMS Server » Performance Tuning » Histograms issue During DB Upgrade. (Oracle - 11.2.0.3 G)
Histograms issue During DB Upgrade. [message #588885] Sat, 29 June 2013 08:02 Go to next message
VIP2013
Messages: 84
Registered: June 2013
Member
Hi,
We are currently migrated our database to Oracle 11g i.e 11.2.0.3 (from 10.2.0.3). Now we are facing application slowness on 11g, we found the issue is with missing histogram or some unnecessary histograms in place. Given below are my details.If anyone having such experience during migration please help.

When we were in 10g, we were having schemalevel gather stats job with 'METHOD_OPT' as 'AUTO' running each weekend and gathering onle STALE stats.And we had faced some performance issue due to optimizer following wrong path of execution due to some missing histograms and some extras too.So we had done skew ness(for each column in table in database) analysis on DB level using 'WIDTH_BUCKET' function manually on each column,and we deleted no of unnecesary histograms and created some on 10g DB. And updated the default schemalevel gather stats job with 'METHOD_OPT' as 'FOR ALL COLUMN SIZE REPEAT'. And we were getting stable enviroment.

Now we just migrated and its done by our techops(Oracle) team, i am confused how the histograms has been changed/modified for columns during migration, causing headache now. So now below are my plans and fears associated with them. Need advice on same.

1. If i will make the histograms exactly same in both the environments, i.e. will delete histograms which are present in new DB(11g) but were not in old DB(10g).and creating those which were there in old DB(10g) but now not in new DB(11g). My question is , will it be safe to do this or it may impact negatively with 11g optimizers features in place?
2. Or should i try easier option once by making 'METHOD_OPT' for the schema level stats job as 'AUTO' on 11g and try once, as because i believe 11g is more stable and optimizer is equipped with more intelligence in case of stats gathering using AUTO? Need experts advice on same.
3. Is there any other way to follow, in this scenario? Please advice.

[Updated on: Sat, 29 June 2013 09:11]

Report message to a moderator

Re: Histograms issue During DB Upgrade. [message #588897 is a reply to message #588885] Sat, 29 June 2013 09:38 Go to previous messageGo to next message
John Watson
Messages: 4403
Registered: January 2010
Location: Global Village
Senior Member
Your option 1 sounds reasonable, and is what I would do. I would also set optimizer_features_enable='10.2.0.5'. That should give stability.

Then,
exec dbms_Stats.set_global_prefs('publish','false')
and gather your new stats (your option 2) and set optimizer_use_pending_statistics=true for a few sessions to test.
Re: Histograms issue During DB Upgrade. [message #589003 is a reply to message #588897] Mon, 01 July 2013 06:53 Go to previous messageGo to next message
VIP2013
Messages: 84
Registered: June 2013
Member
Thanks, will try that.
Also i found i have some of the columns which is having 'HISTOGRAM' column as 'NONE' in dba_tab_col_statistics, but still have entries in 'DBA_TAB_HISTOGRAMS' view. i cant understand the exact cause. Please advice.

[Updated on: Mon, 01 July 2013 06:53]

Report message to a moderator

Re: Histograms issue During DB Upgrade. [message #589044 is a reply to message #589003] Mon, 01 July 2013 11:35 Go to previous message
BlackSwan
Messages: 22526
Registered: January 2009
Senior Member
https://forums.oracle.com/thread/2556090
Previous Topic: performance issue with the query
Next Topic: SQL & PL/SQL Performance Tuning
Goto Forum:
  


Current Time: Wed Jul 30 00:03:24 CDT 2014

Total time taken to generate the page: 0.71228 seconds