Tuesday Sep 11, 2012

Webcast on 18-SEP: Three Reasons to Upgrade

You may have noticed that our Upcoming Events listing is a bit sparse due to preparations for OpenWorld. But, we have not gone completely silent; there are still Database Upgrade events available. One that I would like to point out is a webcast that I am delivering next week, "Three Compelling Reasons to Upgrade to Oracle Database 11g." 

This will be a one-hour webcast discussing the three main reasons for upgrading to the latest version of Oracle Database:

  1. Reduce IT costs
  2. Ensure support for your hardware and applications
  3. Take advantage of new features to improve performance, security, and functionality
We will be recording this webcast, but it is always great to have people on live -- particularly if you would like to ask questions. Click on the registration link to get started, and I hope to see you online! 

Friday Aug 03, 2012

Ouch ... again OCM

Another customer, another upgrade - and again from Oracle Database 11.1.0.7 to Oracle Database 11.2.0.3. And again an OCM error during upgrade which is not very obvious to relate to OCM once you see the errors during upgrade.

While monitoring the database during upgrade with tail -f on the alert.log we've spotted this one here:

ORA-604: error occurred at recursive SQL level 3
ORA-904: "XDB"."DBMS_CSX_INT"."GUIDTO32": invalid identifier
XDB SGA reset to NULL.

Looks like some issue with XDB. But actually it is related to OCM and it seems that something gets created in the wrong order. Currently DEV is working on that issue - and the good message: after the upgrade has been completed everything is VALID. But it's one of those strange errors you might wonder about ...

This issue has been filed under:
Bug 13710092: ORA-00904: "XDB"."DBMS_CSX_INT"."GUIDTO32": DURING UPGRADE
and the bug is not public at the moment - but you could simply ignore that error.

- Mike

Monday Jul 16, 2012

How to select statements from AWR?

Simple question - and more a reminder to myself as I'd assume that many people have their examples somewhere already. But in upgrade and migration projects it's sometime useful to simply select SQL statements directly from between two AWR snapshots into a SQL Tuning Set (STS). This will give you the possibility to later put a failing plan directly into a SQL Plan Baseline, part of the free Enterprise Edition's SQL Plan Management feature.

You'll need to create a SQL Tuning Set and simply fill it with staments from an AWR snapshot interval:


exec DBMS_SQLTUNE.CREATE_SQLSET('MD_STS','SYS')
/

DECLARE
  cur sys_refcursor;

BEGIN

open cur for

   select value(p)
from table(dbms_sqltune.select_workload_repository(
      begin_snap => 4711,
      end_snap => 4788,

      basic_filter => 'parsing_schema_name not in
                 (''DBSNMP'',''SYS'',''ORACLE_OCM'')',

      ranking_measure1 => 'elapsed_time',

      result_limit => 250)) p;

    dbms_sqltune.load_sqlset('MD_STS', cur);

  close cur;

END;

/

And fixing a failing plan and overwriting it with the plan from before the upgrade would work that way:

DECLARE
 my_plans PLS_INTEGER;
BEGIN
 my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
   sqlset_name => 'MD_STS',
   basic_filter => 'sql_id="b25h7qc53gowp"',
   fixed => 'YES');
END;
/

- Mike

Thursday Jun 28, 2012

Guaranteed Restore Points as Fallback Method

Thanks to the great audience yesterday in the Upgrade & Migration Workshop in Utrecht. That was really fun and I was amazed by our new facilities (and the  "wellness" lights surrounding the plenum room's walls).

And another reason why I like to do these workshops is that often I learn new things from you :-) So credits here to Rick van  Ek who has highlighted the following topic to me. Yesterday (and in some previous workshops) I did mention during the discussion about Fallback Strategies that you'll have to switch on Flashback Database beforehand to create a guaranteed restore point in case you'll encounter an issue during the database upgrade.

I knew that we've made it possible since Oracle Database 11.2 to switch Flashback Database on without taking the database into MOUNT status (you could switch it off anyway while the database is open before in all releases). But before Oracle Database 11.2 that did require MOUNT status.

SQL> create restore point rp1 guarantee flashback database ;
create restore point rp1 guarantee flashback database
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'RP1'.
ORA-38787: Creating the first guaranteed restore point requires mount mode when
flashback database is off
.

But Rick did mention that I won't need to switch Flashback Database On to create a guaranteed restore point. And he's right - in older releases I would have had to go into MOUNT state to define the restore point which meant to restart the database. But in 11.2 that's no necessary anymore. And the same will apply when you upgrade your pre-11.2 database (e.g. an Oracle Database 10.2.0.4) to Oracle Database 11.2.

As soon as you start your "old" not-yet-upgraded database in your 11.2 environment with STARTUP UPGRADE you can define a guaranteed restore point. If you tail the alert.log you'll see that the database will start the RVWR (Recovery Writer) background process - you'll just have to make sure that you'd define the values for db_recovery_file_dest_size and db_recovery_file_dest.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size                  2228944 bytes
Variable Size             134221104 bytes
Database Buffers          272629760 bytes
Redo Buffers                8466432 bytes
Database mounted.
Database opened.
SQL> create restore point grpt guarantee flashback database;
Restore point created.
SQL> drop restore point grpt;

And don't forget to drop that restore point the sooner or later as it is guaranteed - and will fill up your Fast Recovery Area pretty quickly ;-) Just on the side: in any case archivelog mode is required if you'd like to work with restore points.

- Mike

Wednesday Jun 20, 2012

Let's do the Time Warp again!

Once you start reading about Daylight Saving Time changes in MyOracleSupport you'll find still a lot of notes explaining this and that and back and forth. But sometimes there seems to be a bit too much information - and lacking clear instructions. Once a customer called that the "Time Zone Spaghetti" after reading MOS notes about DST for several hours ending up with the note where he has begun to read before still not clear what to do now ;-)

I'm using usually the scripts from MOS Note:977512.1 as you'll just have to exchange the DST version you are upgrading to and it has everything you need to check and adjust the time zone data in the database - for instance after applying the DST V18 patch to your database's homes. As a reminder to myself when traveling I have stored a copy of the script part of that note here - and please note that this is not an official Oracle version. Always read and check the original MOS Note:977512.1 as it may have gotten changed in between and may contain changes or corrections and as it has a lot of more explainationary information than I could cover here.

And credit to Gunter Vermeir from Oracle Support, who is the owner of that MOS Note and has compiled all that useful stuff together.

Tuesday Jun 19, 2012

New Time Zone Patch DST V18 is available

Sorry for not updating the blog more often at the moment - but more updates will come soon as I play around with Oracle Restart and single instance databases in ASM with Oracle 11.2.

Just on the side there's a new time zone patch to DST V18 available since May 2012. You can download it via PATCH download from MOS with the patch number: 13417321


What do you think? Will Lufthansa operate a faster jet the other night? Will the jet stream be more powerful? Or a better type of fuel? Or is it just the travel portal which hasn't applied the correct time zone patches to catch DST change that night in the US whereas it happens two weeks later in Europe? Guess ...

And please see the readme about how to apply the patch and our slides about why time zone patching may be important even in your environment ;-)

RDBMS bug: Bug 13417321: DST 18 : HALF YEARLY DST PATCHES, MAY 2012
OJVM Bug 14112098 - dst changes for dstv18 (tzdata2012c) - need ojvm fix

Wednesday May 30, 2012

Summer Upgrade Workshops are Open!

The listing of upcoming events is located in the right sidebar of the main blog page, down below the flag counter. If you haven't checked out our schedule lately, you might be surprised at how active we will be with travel this summer. Coming up next week will be upgrade workshops in the USA (St. Louis and Minneapolis) followed by a pair in Canada (Toronto and Montreal) and then two in Europe (Brussels and Utrecht).

June 2012 Workshop locations

Make your plans now to attend an upgrade workshop in your area. As you can see from the long list of planned events, it is very likely that Mike or I will be coming to your area sometime soon!

Tuesday May 29, 2012

Please use the new HTML Support Portal interface

It has taken a while ... ;-) But finally Oracle Support is switching the My Oracle Support Portal (MOS) from Flash to HTML. So please when you log in use the switch for HTML. 

If you'd like to get further information on changes, differences and such please see the following content:

 - Mike

Friday May 11, 2012

NLS_LENGTH_SEMANTICS - Why not in the init.ora?

.Few days ago at the Upgrade and Migration Workshop in Hamburg when I did talk about Unicode Migrations a customer had an interesting question.

"Why does the documentation states NOT to set NLS_LENGTH_SEMANTIC parameter to CHAR in the init.ora/spfile to enable char semantics for newly build objects by default?"

Honestly I was not aware of that hint. So I did some research and tried to get an answer from the developers.

The documentation says clearly not to set that parameter permanently:

  • Oracle Database Reference on NLS_LENGTH_SEMANTICS
    • Caution:
      Oracle strongly recommends that you do NOT set the NLS_LENGTH_SEMANTICS parameter to CHAR in the instance or server parameter file. This may cause many existing installation scripts to unexpectedly create columns with character length semantics, resulting in runtime errors, including buffer overflows.

  • Oracle Database Globalization Support Guide on NLS_LENGTH_SEMANTICS
    • Same warning as above - but also states:
    • NLS_LENGTH_SEMANTICS does not apply to tables created in the SYS schema. The data dictionary always uses byte semantics. Tables owned by SYS always use byte semantics if the length qualifier BYTE or CHAR is not specified in the table creation DDL.

And my colleague from Poland, Sergiusz Wolicki did reply (as always) very quickly (thanks!!!):

"The warning is general as the problem may affect Oracle data dictionary scripts for schemas such as SYSTEM, CTXSYS, ORDSYS, XDB, SYSMAN, Oracle application scripts for schemas such as APEX_030000, APPL, APPLSYS, GL, and other eBS or Peoplesoft or JDE, but also third party prepackaged applications and customer's own legacy stuff.

The recommendation is to avoid the dependency on the parameter altogether
. The character and byte length semantics should be explicitly specified in column definitions and PL/SQL code:


CREATE TABLE emp( ..., first_name VARCHAR2(100 CHAR), last_name VARCHAR2(100 CHAR) )
/
CREATE PROCEDURE read_emp IS
  v_first_name emp.first_name%TYPE;
  v_last_name emp.last_name%TYPE;
  v_full_name VARCHAR2(202 CHAR);
BEGIN
  ...
  SELECT first_name, last_name INTO v_first_name, v_last_name FROM emp WHERE ...;
  v_full_name := v_last_name || ', ' || v_first_name;
END;
/

If you find specifying the data type and the length explicitly as an obvious thing, you should also expect that the length semantics (length units) are specified explicitly.

If you need to migrate existing scripts quickly, put an ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR at the beginning of each script and after each CONNECT command in the script.
"

Thursday May 10, 2012

Upgrade Workshops in Hamburg and Cologne - Recap

Danke an alle Teilnehmer der Upgrade & Migration Workshops in Hamburg und Köln. Und noch einmal eine grosse Entschuldigung wegen der falschen Event-Adresse im Web beim Hamburger Workshop, den dadurch bedingten etwas späteren Beginn und die natürlich die daraus entstandenen Unannehmlichkeiten.

Von meiner Seite ein grosser Dank für die tolle Athmosphäre an beiden Workshop-Tagen, Ihre Fragen und Ihr Feedback. Die Slides finden Sie, wie angekündigt, rechts im Slide Download Center :-)


Tuesday Apr 24, 2012

Some impressions from Seoul and Beijing

There was not much time left to walk around and take some pictures during our fully packed workshop week in Seoul and Beijing. But as I have my camera almost always in my pocket I took some pictures ... :-)


Seoul View


Seoul Food ... yes ... BEEF ... wonderfully thin sliced beef on the barbecue grid


Tumbling Seoul
(that's exactly how I felt in the mornung after our night at Peter, Paul and Mary's (see below)).


Modern Beijing ... very modern


Well ... or even more modern ...


There's always a traffic jam ... simply too many cars ... and some bycicles left as well


And if you don't know where to park your Lambo... no prob ... just park it in the middle of two pedestrian crosswalks on an intersection ... if you drive the right car policemen will simply ignore your intersting habbits ;-)

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

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!

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.

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 ;-)

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
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