Upgrading to 11g

I feel very fortunate that my current client is planning to upgrade a significant production system to Oracle 11g very shortly – you can’t get to know something like this properly until you use it in anger and work through the problems in a time-constrained environment. And I get the impression that there are very few production 11g sites out there. So it will be a good experience and it will look good for me.

Some say that 11g is no more than 10gR3, nevertheless it’s a bold upgrade decision – we’ll be going from 9i Standard Edition to 11g Enterprise Edition and switching from Windows to Linux (with a delayed phase of RAC thrown in).

It’s a bit like going from a Ford Focus to a Ferrari (but I’ll keep my thoughts on the futility of certain aspects of this to the RAC rant).

The upgrade is scheduled for March or Aprial and, so far, things have looked relatively pain free. In terms of general performance, not unexpectedly, Oracle 11g on Linux seems significantly faster than 9i on Windows.

The changes we have had to make so far seem mostly to concern changes in XML behaviour (change that actually mostly happened in 10gR2, but we’ve skipped that generation).
For example:

  • In 9i, if you tried to create an XML element with a space in the tag name using XMLELEMENT, the space would be replaced with “_x0020_”. In 11i, the space is preserved as a space. I’m not convinced by this change in behaviour as an element surely cannot have a space in it’s name. Anyway, in those unwise places where spaces have been used, these tags will have to change in both our client and database code.
  • There has been a change in behaviour regarding dates within XMLELEMENTs. In 9i, a date without an explicit format would pick up the default NLS_DATE_FORMAT of the session. In 11g, the date format will be YYYY-MM-DD. So, to preserve our 9i behaviour, we need to TO_CHAR our dates in XML so that they pick up the session format.
  • When using XMLFOREST in 9i, empty columns would result in no tag being present in the resultant XML. Although deliberate, I think there’s something dodgy about this behaviour anyway. However, it’s changed slightly now we’re in 11g. Columns with not null columns will always produce a tag, even if empty (consider outer joins to tables with not null colums). Empty nullable columns continue to have their tags omitted. I’m not sure how we will address this in our particular situation (empty tags cause us issues if the client entity is not a nullable object, but if the tag is missing apparently the whole enigma is avoided. Somehow!). The solution certainly won’t involve changing not null columns to nullable, but, to limit changes in both client and database, it could involve using XSL to strip out empty tags.

I anticipate further issues to come out of the wash however I expect the majority of these to only reveal themselves post-production upgrade. There are bound to be some statements that degrade significantly but it might not be until the business do their daily thing day-in day-out that the most significant problems for them float to the surface (as opposed to things that are most obvious from automated load testing which I am always doubtful regarding the efficacy thereof).


10 Responses to Upgrading to 11g

  1. Paweł Barut says:

    Thanks for sharing issues with XML DB in 11g. I’m also planing to move from 10gR2 to 11g. Hopefully those issues don’t have impact on us, as we convert dates explicitly to chars and we do not use spaces in tag names. Issue with XMLForest seems to affect us, but I do not expect problems with this change.

    Best Regards,

  2. Thanks for sharing.

    Regarding the XMLFOREST item, this sounds like introducing nullology within an XML context.

    Your a brave people 😉

  3. Paweł Barut says:


    This post inspired me to check how it works in 10gR2. It is the same as in 11g.
    I’ve tested workaround:
    nvl(COLUMN, null) as “Tag”
    and it works great. But when somebody read this, it could see silly 😉

    BR, Paweł

  4. dombrooks says:

    Pawel – I just tested it out and it works perfectly – excellent.

    That solution never occurred to me.

    It’s the least intrusive and involves the least changes of all the solutions that we’d considered so far and it’s highly likely that we will go with that so many, many thanks for being inspired.

  5. dombrooks says:

    Just want to remark again that I don’t like either behvaiours of XMLFOREST – the old or the new.

    I’m sure (!) they’ve changed it for a good reason, but I just don’t think tags should be left out in the first place.

  6. Ravi says:

    I was wondering if any have you faced issues with import performance in 11g. I have upgraded a 10gR2 database to 11gR1 and the import performance with impdp is quite bad. We have a 110GB schema that used to take approx 3.5 hours to import in 10gR2. The same import takes 6 hours.
    Both the 10g and 11g databases share the same box, Linux redhat AS 4. Both have approx same amount of memory allocated to SGA and PGA (using automatic memory management.)

    The differences I see are more during index builds. The data load is approx the same between 10g and 11g.

    I have a case opened at metalink but so far no clues.

    – Ravi

  7. dombrooks says:

    Hi Ravi,

    Thanks for stopping by.

    This time I wasn’t really involved in the DBA side of the upgrade. My primary focus has been the performance of the application on the upgraded database so I can’t offer much insight.

    Maybe it would be interesting to see a trace of the impdb in the two databases to note any differences in behaviour.

    I did observe performance problems with database statistics gathering a compile schema command kicking off in parallel, causing contention in itself and completing much more slowly than an old-fashioned serial stats gathering.

    If you have any more information, I’d be very interested to know.


  8. Abhilash says:

    Migrating a database from Oracle 10.1 to 11.2 and I have the following problem.

    The statement

    SELECT ” || (Xml).getclobVal() AS Xmlclob
    FROM (SELECT XmlElement( “Element1”,(SELECT XmlAgg(tpx.Xml) FROM (SELECT XmlElement(“Element3”,XmlForest(‘content’ as Element4)) AS Xml FROM dual ) tpx) AS “Element2”) AS Xml FROM dual)

    On the original 10.1 database produces XML like this…


    On the new 11.2 system it looks like this…


    Is there some environmental variable I am missing that tells Oracle how to format its XML. There are hundreds of thousands of lines of PL/SQL in the database; it would be a mammoth task to rewrite if it turned out they had changed they way Oracle formats XML between versions.

    Hopefully someone has come accross this before. Thanks

  9. Abhilash says:

    XML Structure was missing in the above post. I am pasting it again. Basically when the query was run in sqlplus it returns different XML Structure.

    On the original 10.1 database produces XML like this…


    On the new 11.2 system it looks like this…


    Thanks in Advance..

    • dombrooks says:

      Abhilash – you might have to try again with that XML – the tags have been stripped out.

      Between versions there have been significant changes in how XML is dealt with.
      There are changes around tags, e.g nullable columns and what happens with the tag (is it there or not) if the value is null, etc.

      The bottom line is that in later versions, Oracle has tightened it’s behaviour in relation to the XML standards.

      If you manage to post successfully then I’m happy to have a look. Feel free to email if not.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: