Tuesday Apr 03, 2012

New Slides - and a discussion about Dictionary Statistics

First of all we have just upoaded a new version of the Upgrade and Migration Workshop slides with some added information. So please feel free to download them from here.The slides have one new interesting information which lead to a discussion I've had in the past days with a very large customer regarding their upgrades - and internally on the mailing list targeting an EBS database upgrade from Oracle 10.2 to Oracle 11.2.

Why are we creating dictionary statistics during upgrade?

I'd believe this forced dictionary statistics creation got introduced with the desupport of the Rule Based Optimizer in Oracle 10g. The goal: as RBO is not supported anymore we have to make sure that the data dictionary has fresh and non-stale statistics. Actually that would have led in Oracle 9i to strange behaviour in some databases - so in Oracle 9i this was strongly disrecommended.

The upgrade scripts got hardcoded to create these stats. But during tests we had the following findings:

It's important to create dictionary statistics the night before the upgrade. Not two weeks before, not 60 minutes before your downtime begins. But very close to the upgrade. From Oracle 10g onwards you'd just say:

$ execute DBMS_STATS.GATHER_DICTIONARY_STATS;

This is important to make sure you have fresh dictionary statistics during upgrade for performance reasons. Tests have shown that running an upgrade without valid dictionary statistics might slow down the whole upgrade by factors of 2x-3x.

And it would be also a great idea post upgrade to create again fresh dictionary statistics when you've did suppress the stats creation during the upgrade process. Suppress? Yes, you could set this underscore parameter in the init.ora:

_optim_dict_stats_at_db_cr_upg=FALSE

to suppress the forced dictionary statistics collection during an upgrade. We believe strongly that (a) people using the default statistics creation process which will create dictionary statistics by default and (b) create fresh stats before upgrade on the dictionary. Therefore we find it save once you have followed our advice to use the underscore during upgrade. And we've taken out that forced statistics collection during upgrade in the next release of the database.

Please note: If you are using the DBUA for the upgrade it will remove underscore parameters for the upgrade run to improve performance - which is generally a good idea. So you'll have to start the DBUA with that call:

$ dbua -initParam "_optim_dict_stats_at_cb_cr_upg"=FALSE

-Mike

OEL6 and RHEL6 certification

Well, it took some time until Oracle Database 11.2.0.3 got finally certified on Oracle Unbreakable Linux 6 (OEL6) and RHEL6 (see the official announcement here). One really interesting change is that effective immediately, Oracle will provide its Red Hat compatible Linux binaries, updates and errata for free http://public-yum.oracle.com

Finally MOS now has been updated as well (please go to the CERTIFICATION tab):

Wednesday Mar 28, 2012

Upgrade Workshop in Sydney - Recap

Late, but hopefully not too late, a big THANK YOU to everybody who did attend the Upgrade and Migration Workshop in Sydney at the Cliftons past week. You were a really good crowd, thanks for all your questions, the great conversations in the breaks, thanks to the local marketing team for the excellent organization - and we'll looking forward to see you next time again with all your databases then live on Oracle Database 11.2 

To download the slides please find them in the Slides Download Center to your right - or use the direct link to download the workshop slide deck.

And I really don't understand how you can go to daily work (or to a workshop) with such beaches nearby ... I would immediatelly change my job profile ;-) Honestly, Sydney is really a great place. Australia and New Zealand generally are wonderful places and we've met so many great people in Perth, Brisbane, Melbourne, Wellington, Sydney and during our travel in between. Just if there wouldn't be over 20 hours pure flight time in between Germany and Down Under ;-)

Hope to see you all again next time for 12c ;-)

-Mike

Monday Mar 19, 2012

Upgrade Workshop in Wellington - Recap

Wow! Wellington is really a wonderful city - except for the weather situation ;-) But it was the first time that Roy and me did arrive to a workshop with a ferry boat. We flew in on Friday to Christchurch (btw, this was the longest customs and border control I've ever went through - and I traveled to Israel by bus via Allenby Bridge from the West Bank some years ago - it took us two hours to go through immigration and customs in the night from Friday to Saturday) and drove up the Southern Island. Very nice :-) Great landscapes, great wines and great people!


I'f you'd like to download the slides please download them from the Slides Download section to your right.

And next time you'll have to ask more questions :-) Don't be this shy - Roy and me (usually) don't bite :-)

-Mike



Upgrade Workshop in Melbourne - Recap

Thanks to everybody who did attend at our Upgrade and Migration Workshop in Melbourne last Friday. First of all it was a Friday so we really appreciated your patience of staying until the very end :-) And then, yes we know, it was a full room. And we'd really like to thank you :-) It was a great day for Roy and me. And you were such a great crowd with many questions and excellent discussions during the breaks. Please have all successful upgrade and migrations. And feel free to get in touch directly with Roy and me if you have additional questions or if you'd like to become a reference.

Please feel free to download the slides from the Slides Download section to your right - or simply use that link here.

Ah, and sorry that neither Mark Webber nor Sebastian Vettel did win ... next time ;-)

-Mike

Thursday Mar 15, 2012

Upgrade Workshops in Perth and Brisbane - Recap

Thanks to everybody who did attend on Tuesday in Perth and today in Brisbane for the Database Upgrade and Migration Workshop in the Oracle offices. We hope you've enjoyed the day as we did enjoy it ;-) Feel free to contact us regarding your experiences, upgrade successes, pitfalls you have seen and miss in the slides etc. 

Slides - please don't forget to download them via this link here.

And for those who have asked for the component dependency presentation containing also a script (find it at the very end of the presentation) to drop an unused or accidentially created RAC option in a non-RAC database, you'll find that here.

Thanks and CU next time again ;-)

-Mike


Tuesday Mar 13, 2012

Oracle Open World 2012 - Call for Papers 14-MAR-2012

Wow - this year it's an early call. But yes, it's time now to submit your papers for OOW 2012. Oracle Open World 2012 will happen again in San Francisco and will run from September 30 - October 5, 2012 at the Moscone Center.

Customers and Partners are encouraged to submit their proposals until April 9, 2012 at 11:59pm PDT. You'll find more information on the OOW's 2012 Call For Papers page. We are looking forward to check out your session on database upgrades, migrations and projects. And feel free to contact us anytime :-)

-Mike

Wednesday Mar 07, 2012

Upgrade & Migration Workshops Down Under

Time flies ... and the Database Upgrade and Migration Workshops in Australia and New Zealand come closer and closer. It has been exactly two years ago since Roy and me had run the Upgrade Workshop in Perth and Melbourne. In 2010 visiting Australia was part of our Asian workshop route. But this time we'll fly the looooooooooooong way (ouch ... I'm definitely not looking forward to my eco seat in cattle class for the 18 flight hours down to Perth - and, even worse, to the 22 hours with 2 stops on the way back from Sydney) down just for Australia and New Zealand.

The local Marketing group and our colleagues from Presales and Support had done a really good job - and due to the high registration rates the events in Melbourne, Wellington and Sydney had been moved now to external vernues :-) Thanks to the local folks for their hard work!!!

All registration links are now open again. Grab your seat if you haven't registered yet or tell your colleagues. It's a technical workshop. Just 2 out of 478 slides have marketing content :-)

  • 13-MAR-2012: Perth ... Registration
    Oracle Corporation - Level 2, 66 Kings Park Rd - West Perth, WA 6005 - Theatre/Presentation Room

  • 15-MAR-2012: Brisbane ... Registration
    Oracle Corporation - Level 14/300 Ann Street - Brisbane, Australia - Brisbane room

  • 16-MAR-2012: Melbourne ... Registration
    Cliftons Melbourne - Level 1, 440 Collins Street - Melbourne, VIC 3000

  • 19-MAR-2012: Wellington ... Registration
    Rydges Wellington - 75 Featherston Street - Wellington, New Zealand

  • 21-MAR-2012: Sydney ... Registration
    Cliftons Sydney - 190-200 George Street - Sydney, NSW 2000

We are really looking forward to meet you next week or the week after - CU there :-)

P.S. The picture on top if from Cottesloe Beach near Perth - unfortunately next week we won't have enough time to spend an hour or so there ...

Thursday Feb 23, 2012

Upgrade Workshop in Bangkok - Slides

Thank you very much for the warm welcome today at the Upgrade and Migration Workshop in Bangkok. Roy and me are very happy to be finally here.

You can download the most recent version of the slides via this link:
Upgrade and Migration to Oracle 11.2 Workshop Slides

Have successful upgrades - and let us know your experiences with the upgrades and migrations!
And hope to see you next time again!

Monday Feb 20, 2012

Upgrade Workshop in Jakarta - Slides

Thanks to everybody today in the Upgrade and Migration Workshop in Jakarta. And thanks for all your questions during the breaks.

In order to download the most recent version of the slides (just refreshed them now during the lunch break) please find them here:
Upgrade and Migration to Oracle 11.2 Workshop Slides


Have successful upgrades - and don't even think about upgrading to Oracle 10g right now - Oracle 11.2.0.3 is the release you should upgrade to in the near future :-)

Friday Feb 17, 2012

Oracle 11g in Action: Real World Customer Experiences

31 minutes about customer who'd upgrade to Oracle Database 11g:


(and neither Prash nor me did choose the lady as teaser ;-) ...)

Wednesday Feb 15, 2012

Why is every patchset now a full release?

This question got posted by Naveen in the previous entry today - and I found it worth it to create a new blog entry as this question gets raised in nearly every 2nd workshop as well - so others might be interested as well.

Mike,

A question has been bothering me for a while and I thought I'll throw it out there. I didn't raise a support ticket, I knew what the response is going to be.

So here is the question. I have an 11g ORACLE HOME that was built as 11.2.0.1. Now if I want to upgrade to 11.2.0.2 or 11.2.0.3, I guess there is no easy way to upgrade the existing ORACLE HOME. Why didn't Oracle give the option to just apply a patch (opatch) and upgrade to 11.2.0.2 (or .3). For a customer to create a new ORACLE HOME is just a lot of work and breaks a lot of things. We have to copy over so many configs from the old home to the new home.

I'm sure there is a good reason, I'm just trying to understand what they are.

Thanks
Naveen

Naveen, thanks for your question. And the answer has many aspects.

Install into your existing ORACLE Home

First of all you can still install the new patch set (which is now a full release since Oracle 11.2.0.2) into your existing $ORACLE_HOME. But you'll have to detach your current home from the OUI's inventory first. Please see slide 41 of the Upgrade and Migration workshop deck. Please backup the contents of $ORACLE_HOME/dbs and $ORACLE_HOME/network/admin first as you'll have to copy them back later.

$ ./runInstaller -detachHome ORACLE_HOME=/u01/orahomes/11.2.0

Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB. Actual 10047 MB Passed

The inventory pointer is located at /etc/oraInst.loc

The inventory is located at /u01/orabase

'DetachHome' was successful.

Once you've done that you'll install into your existing (old) directory.  And finally you'll copy back your dbs and network/admin files.

Why customers might use this procedure?

There are basically two reason why a customer chooses this strategy:
(a) Home naming conventions not allowing an Oracle 11.2.0.2 home and another Oracle 11.2.0.3 home
(b) Space issues
Both are valid reasons and therefore you can stay with the old strategy.

Why did we change from patchsets to full release patchsets?

Simple reason: Customers did ask us for a looooong time why we are delivering 3GB large patchsets instead of full releases. So we'd follow this wish. And second it will decrease the overall downtime when you'd install into your new Oracle Home. If you apply patchset software to your existing Oracle Home every Oracle process serviced by that home must be shut down as well - obviously including the database(s). And furthermore if something fails you'll have to restore your OUI's inventory plus all home contents - or reinstall your previous Oracle software.

Besides that we recommend to patch this home first with important one-offs (see links in MOS Note 161818.1) and the latest PSU or CPU. If you'd do that to your already emptied old home it will simply increase again your overall downtime.

And this is the reason why we recommend:
Always install into a new Oracle home beginning with Oracle Database 11.2 - don't erase your old home to reuse it unless there's no significant need for that procedure.

Tuesday Feb 14, 2012

Upcoming Workshops in Jakarta, Singapore and Bangkok

Next week will be again a travel week - and it means going from deep frosty winter into "warmer" areas of the world. But we are really looking forward to meet you in Jakarta, Singapore and Bangkok. And in case you just read this now there are still seats open as far as I know :-)

Friday Feb 10, 2012

Note:1389592.1 Public - Speed Up xTTS

As we have already mentioned in our recent workshops the MOS Note:1389592.1 is public now:

  • MOS Note:1389592.1
    Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backups

This technique currently works for migrations to Linux64 and is supported by Oracle Support for Exadata migrations only. It utiliuzes RMAN incremental backups to reduce the large amount of downtime it takes to copy and convert the datafiles cross Endianness.

Two requirements must be met to use this new functionality:

  • Oracle 11.2.0.2 plus Exadata BP12 (patch 12982245)
  • One-off patch 13340675 contains the RMAN extension and is currently available on top of BP12 on Oracle 11.2.0.2

Let me know if you have further questions - and this restriction on Oracle 11.2.0.2 with BP12 applies to the intermediate instance you'll use to roll forward your converted incremental backups only. Source might be Oracle 10.2 whereas target might be also an Oracle 11.2.0.3 installation.

Saturday Feb 04, 2012

Nuremberg Sausage???

I'll try almost all food wherever I am in the world (except for Scorpios-on-a-Stick, Sheep Penis and such stuff). But I didn't try this in our hotel in Rio:

No offense - but Nuremberg (or in German: Nürnberg) is my hometown - and Nürnberger Bratwürste look a little bit different. If you'd ever visit Germany and you'll have to chance to see Nürnberg you might order "Drei im Weggla" (three [sausages] in a bread bun) and every Franconian will understand you ;-)


But still Roy and me had some fun at the breakfast buffett ;-)

Obrigado :-)

Thanks a lot to everybody who did visit our workshops in Sao Paulo and Rio de Janeiro in the past days. And actually our "Thank You" or Obrigado! can't express how Roy and me felt in the past days. I believe I've never felt so welcome - you all, customers, partners and Oracle folks were simply great. Thanks a lot for that. We've had a great time - and we hope you had some fun as well and enjoyed the hands-on lab as well.

Let us know if anything with your upgrades does not run as desired - or if all worked out well. And also if you'd like to try the "Turbo xTTS" technique. The note got published last night and I'll write something about it in the upcoming weeks.

So we hope to see you again - maybe in 2014 during the World Cup ;-) I'll push Murilo to setup another series of workshops by then ;-)

And thanks a lot for all your comments and feedbacks - we really appreciate that!!

Obrigado!!!

PS: Sorry for the typo in obrigado - and thanks to all who'd let me know ;-)

Wednesday Feb 01, 2012

Upgrade Workshops in Sao Paulo and Rio

Thanks to everybody who did attend today at our Upgrade and Migration Workshop in Sao Paulo and Rio de Janeiro. We hope that you'd enjoy the day and the hands-on exercise :-)

And please feel free to download the most recent version of the slides with many hidden slides included we couldn't show at the workshop today.

Obrigado!

Thursday Jan 26, 2012

Fundamental Oracle flaw revealed??? Update ...!

Writing a blog has a nice effect on the side: it brings me in touch with people I've never met - but we have similar targets or interests. Today I've received a comment from Gökhan Atil about the Fundamental Oracle Flaw known also as the SCN issue.

And for sure I did visit Gökhan's blog and I did watch his very interesting 5 minutes demonstration how to bring down a database with the SCN issue. Which is very good to know as I thought the database won't come down because of this issue. But watch Gökhan's video by yourself and read his blog entry. And please note: this database was unpatched!


In conclusion we both come to the same recommendation:
PATCH YOUR DATABASES!!! NOW!!! ;-)

Teşekkür ederim, Gökhan!

Thursday Jan 19, 2012

Fundamental Oracle flaw revealed??? Really ...?

This Infoworld article from Jan 17, 2012  Fundamental Oracle flaw revealed did alert Oracle database customers.Infoworld has raised this issue to Oracle before going public with it. Patches are included in the Jan 2012 CPU and PSU. So again, it's strongly recommended to apply the Jan 2012 PSU (or CPU if you are just asking for security fixes) to your environments.

What is the background of this issue?
Everything in an Oracle database is dependent on the SCN (System Change Number). This number is crucial to ensure read consistency. It will always be just incremented and is defined as a large 48-bit integer (281 trillion SCNs). But the SCN can jump as well - especially in cases of distributed transactions. Besides that hard limit there's also a soft limit for the SCN (see the MOS Note for more information).
Distributed Transaction

Hot backup bug
Now there's a backup bug which will increment the SCN to a much higher value once ALTER DATABASE BEGIN BACKUP gets used. We call this putting tablespaces into hot backup mode. Actually I'd assume that most people out there (at least those doing backups on a regular basis) use RMAN - and RMAN does not need to put anything into hot backup mode when creating online backups as the real downside of the hot backup mode is an increased value of log information.
Strong recommendation: Use RMAN! And you may apply patch 12371955: "High SCN growth rate from ALTER DATABASE BEGIN BACKUP in 11g" to your environment.

Combination of backup up and distributed transactions
The people who've detected this issue paint now a large Oracle database infrastructure to the wall - with many databases running distributed transactions - and a misbehaving BEGIN BACKUP routine in combination. This would elevate the SCN over and over again - on all interconnected databases - over time as the SCN will be synched over and over again - and will do huge jumps because of the backup bug.

What's the real risk?
I'm not a security expert - but I've seen many customer environments in the real world. I'd say (and skilled DBAs gotten interviewed by Infoworld and others stated similar opinions) it may be just a small risk in larger environments where many databases are connected together - and CPUs or PSUs got not applied on a regular basis. The PSU/CPU fix will prevent the SCN to be incremented in extensive jumps by several ways.
I'd completly disagree with Infoworld's prediction that databases will crash or abandon - transactions won't be executed anymore and an error will be raised. Yes, this is bad enough - true - but the database(s) will remain open.

What should you do?
Apply the January 2012 PSU or CPU and hot backup fix covered by patch 12371955. But keep in mind

  • Take the PSUs over CPUs as PSUs will contain also important non-dictionary changing fixes whereas CPUs contain security fixes only
  • You can't put a CPU on top of a previous applied PSU
  • Both CPUs and PSU are cummulative 
  • And well, you'll need Extended Support to get acces to PSUs or CPUs for Oracle Database 10.1 and 10.2 - and yes, please don't cry: We've asked you to upgrade a looooooong time ago ;-)

Monday Jan 09, 2012

Happy 2012 - and my music top tens ;-)

Dear All,

thanks for all your feedbacks, for visiting our workshops, for working together with us on upgrades and migrations, in reference projects, in escalated situations, thanks for all your respect and your support. We had a wonderful 2011 together with you and we all wish you a very Happy New Year 2012!!!

Thanks a lot!!!
Roy and Mike



P.S. As I never leave the house without my iPod or my iPhone and a pair of headphones here is my personal list of Top Ten Songs from 2011 - not only according to my listening stats iTunes is displaying ;-)

  1. Steve Wilson - Deform to form a Star
    Steve Wilson
    If you ever ... ever ... really ever in your life have the chance to watch Porcupine Tree (or Steve Wilson solo) live then grab this chance. I've seen so many concerts in the past but Porcupine Tree a while ago in Munich was one of the most impressive concerts I've ever been to. Their music is simply from somewhere outer space ...

  2. Anathema - Summernight Horizon
    Anathema
    Anathema got founded in 1990 in Liverpool as a Doom Metal band - but changed in the later 90s with their album Judgement to clear vocals and athmospheric sounds. I did listen to Judgement many many times (ask my good ol' Oracle support buddies - they will tell you ;-) ) in the past. But this year at OOW in San Francisco I passed by Rasputin Music near the cable car stop in Powell St. - and apparently had two Anathema albums in my hands. Summernight Horizon is just an example of the excellent We're Here Because We're Here album - mixed by (guess?) Steve Wilson from Porcupine Tree ;-) And yes, the album got released in 2010 but I did listen to it since October almost every second day.

  3. Bon Iver - Perth
    Bon Iver
    My album of 2011 - Bon Iver's self titled second album. Bon Iver is mainly Justin Vernon, a hermit from Wisconsin - and this CD is something you'll have to listen more than once. This music is full of open spaces - and I can't decode the meaning of all lyrics. But this isn't really important ...

  4. Bon Iver - Calgary
    --- see above ---

  5. Sólstafir- Fjara
    Solstafir

    This band with the strange name (Sólstafir means Crepuscular Rays) is from Iceland. That's a really small island in the very very north-north-west of Europe with a small number of people, dramatic landscapes ... hey, and volcanoes ;-) Thanks Iceland (by the way!!). Anyway, this band got formed in 1995 and I have never heard anything about them until I've read a recommendation at my favourite news page spiegel.de. They've played at Wacken Festival in 2010 - this is the largest Heavy Metal festival in the world in a small small town in northern Germany. And their 2011 album Svartir Sandar (meaning 'black sands') touched me from the first track to the end. Another great tune is Djákninn

  6. Mogwai - Death Rays
    Mogwai

    I know Mogwai from Glasgow, Scotland, quite a long time. And their 2011 album Hardcore will never die, but you will (great title!) is simply outstanding. I did listen it for instance when walking through Stockholm in the night before my 2011 workshop. It's like a soundtrack for something which you see through your eyes with music tunes by Mogwai.

  7. It's all amazon's fault ;-) You know the recommendations "People who have bought this did buy as well ...". When I was listening to Bon Iver's self titled CD on amazon.com their algorithms did recommend "Fink" to me. I've never heard of Fink before. And I simply did ignore it. But just a few days later a friend sent me an email asking 'Do you know Fink?' telling me about the great songs, lyrics and the wonderful voice of this guy. The same night I did order Sort of Revolution - and a few weeks later the 2011 album Perfect Darkness ...

  8. Girls - Vomit
    Girls
    Sorry for this strange title (not my idea) but this is cool song, a cool video - and it was a recommendation of one of my favourite music pages in the web, They Shoot Music, Don't They from Vienna, Austria ;-)

  9. Feist - Caught a long Wind
    Feist
    From Feist's 2011 album Metals recorded on a huge ranch at Big Sur in California. Great album, great song ...

  10. Coldplay - Paradise
    Coldplay
    I own all Coldplay CDs. But their last one with the strange title Mylo Xyloto has been discussed very controversial in the web and the media. And I fully agree - it's somewhat a mediocre Coldplay album. And I've sold my copy right before christmas on ebay - but kept a copy on my iPod. I like to listen to Paradise and Charlie Brown but I've deleted other tracks such as the duet with Rhianna. I'd bet Chris Martin and his colleagues sell again millions - but if you'd compare this album to Parachutes or Rush of Blood ... ouch ... ;-)

    Happy 2012!!!

Thursday Dec 22, 2011

Some impressions ...

Thanks again to our colleagues of Oracle Japan for the (as always) excellent organization of our journey to Japan in December 2011. And thanks to all the customers and colleagues we've met and which did visit the workshops :-) We'll hope to meet you next time again!

And here are some impressions:
(you might click on the pictures to get a larger view)


Osaka by Night

Silent Cooking in Tokyo

Mackerel :-)


Yes ... Otoro and Maguro ... delicious!!!

Okonomiyaki - also VERY delicious!!!

Fuji-san in the morning

Ginko Trees in Tokyo

Kyoto - Kinkaku-Ji (Temple of the Golden Pavilion)

Beautiful gardens at Kinkaku-Ji in Kyoto
(the picture doesn't reflect that it was freezing cold)

German (yes!!!) Christmas Market in Osaka :-)
(and the two big cans contain Sauerkraut - what else ...)

ありがとうございます。
おおきに

Merry Christmas!!!

Thursday Dec 08, 2011

New version of the slides "Upgrade & Migrate to Oracle Database 11.2" available for download now

Fuji-san

A new and revised version of the slides for the Upgrade & Migrate to Oracle Database 11.2 is available now for download including some corrections and additions. The Japanese version of the slides for today's workshop in Osaka will follow soon. 

おおきに!

OTN Day Osaka

Monday Dec 05, 2011

Exclude DBMS_SCHEDULER Jobs from expdp?

You have never thought about excluding DBMS_SCHEDULER jobs from a Data Pump export? Me neither but I've recently got a copy of an email for such a customer case from Roy who owns Data Pump as well. And this is the code example from Dean Gagne:

exclude.par:
exclude=procobj:"IN (SELECT NAME FROM sys.OBJ$ WHERE TYPE# IN (47,48,66,67,68,69,71,72,74))"

  • This will work only on export
  • It's an all or nothing approach

Quite interesting, isn't it? 

Tuesday Nov 29, 2011

Wrong statistics in AUX_STATS$ might puzzle the optimizer

We do recommend the creation of System Statistics for quite a long time. Since Oracle 9i the optimizer works with a CPU and IO cost based model. And in order to give the optimizer some knowledge about the IO subsystem's performance and throughput - once System Statistics are collected - they'll get stored in AUX_STATS$. For this purpose in the old Oracle 9i days some default values had been defined - and you'll still find those defaults in Oracle Database 11g Release 2 in AUX_STATS$. But these old values don't reflect the performance of modern IO systems. So it might be a good best practice post upgrade to create fresh System Statistics if you haven't done this before. 

You can collect System Statistics with:

exec DBMS_STATS.GATHER_SYSTEM_STATS('start');

and end it later by executing:

exec DBMS_STATS.GATHER_SYSTEM_STATS('stop');


You could also run DBMS_STATS.GATHER_SYSTEM_STATS('interval', interval=>N) instead where N is the number of minutes when statistics gathering is stopped automatically.

Please make sure you'll do this on a real workload period. It won't make sense to gather these values while the database is in an idle state. You should do this ideally for several hours. It doesn't affect performance in a negative way as the values are anyway collected in V$SYSSTAT and V$SESSTAT. And in case you'd like to delete the stats and revert to the old default values you'd simply execute:
exec DBMS_STATS.DELETE_SYSTEM_STATS;

The tricky thing in Oracle Database 11.2 - and that's why I'm actually writing this blog post today - is bug9842771. This leads to wrong values in AUX_STATS$ for SREADTIM and MREADTIM by factor 1000 guiding the optimizer sometimes into the totally wrong directon. The workaround is to overwrite these values manually and divide them by 10000. Use the DBMS_STATS.SET_SYSTEM_STATS procedure. See this MOS Note:9842771.8 for the above bug for some further information. This issue is fixed in Oracle Database 11.2.0.3 and above.

To get some background information about the statistics collected in please read this section in the Oracle Database 11.2 Performance Tuning Guide. And gathering System Statistics might have some implication if you have mixed workloads - and interacts with DB_FILE_MULTIBLOCK_READ_COUNT. For more information please read section 13.4.1.2.


Correction: I had to correct the factor (marked in RED above) from 1000 to 10000. Thanks to Andre Duvekot from the Netherlands for highlighting this to me!!!


Friday Nov 18, 2011

11.2.0.3 Pre-Upgrade Script Now Available!

It took a little while because of upgrades to My Oracle Support, but the pre-upgrade script for Oracle Database 11.2.0.3 is now available for download. If you want the latest pre-upgrade script for any supported version of the database, simply go to MOS Note 884522.1 and download the script that corresponds to your destination release. This is a lot easier than downloading the entire kit in order to obtain the script!
About

Mike Dietrich - Oracle Mike Dietrich
Senior Principal Technologist - Database Upgrade Development Group - Oracle Corporation

Based near Munich/Germany and spending plenty of time in airplanes to run either upgrade workshops or work onsite with reference customers. Acting as interlink between customers and the Upgrade Development.

Contact me either via XING or LinkedIn

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
3
4
5
6
9
10
12
13
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today
Slides Download Center
OOW Slides Download
Visitors since 17-OCT-2011
White Paper and Docs
Oracle Blogs
Workshops
Viewlets and Videos
This week on my Rega/iPod/CD
Workshop Map
Upgrade Reference Papers