Oracle Scratchpad

September 5, 2009


Filed under: Indexing,Infrastructure,Performance — Jonathan Lewis @ 5:37 am BST Sep 5,2009

I supppose it’s inevitable that there will be a flurry of 11gR2 posts in the next few days – so here’s mine. A really useful new feature (and its corollary) that caught my eye: Segment Creation on Demand

The initial segment creation for nonpartitioned tables and indexes can be delayed until data is first inserted into an object.

Several prepackaged applications are delivered with large schemas containing many tables and indexes. Depending on the module usage, only a subset of these objects are really being used. With delayed segment creation, empty database objects do not consume any space, reducing the installation footprint and speeding up the installation. Zero-Size Unusable Indexes and Index Partitions

Unusable indexes and index partitions no longer consume space in the database because they become segmentless.

Unusable indexes and index segments are not usable for any data access. Any space allocated by this unusable (dead) object is freed as soon as an object is marked unusable.

While we’re on the topic of 11.2 – here’s an interesting set of results published on the newsgroup by Charles Hooper.

Update 7th Sept: And here’s another interesting post, from the Optimizer Development Group, on a new feature for comparing execution plans. It looks like a nice idea,  but it looks a little clunky at present so I’m not sure it will be of much use to many people.

Update 11th Sept: I see that Greg Rahn has listed his top 10 – the parallel stuff looks as if it might be interesting, and “instance caging” could be very useful.


  1. Hi Jonathan,

    I haven’t worked my way through the new feature list up to the one you’ve highlighted yet. Looks good, but I wonder if it will confuse someone when he/she creates a schema, without populating it, and wonders why no space is used and why he/she can’t find any segments :) Or perhaps the segments will be listed as having 0 blocks – I can’t test this yet.

    BTW, I can’t see what Charles’ post has to do with 11gR2 specifically. The title is “Surprising Performance Changes with Oracle” but the 11gR1 and 11gR2 results are very similar for the same settings.
    I’d guess (from the avg I/O times) that the OS cache was pre-warmed for at least some of the tests, while the database’s cache was cold for them all (high physical I/O). That was a big factor behind why the direct I/O tests were so much slower than the buffered I/O tests.
    I’d also say that the index was large and not appropriate for the SQL. (Full scan/range scan did three times more logical I/Os than the table size for 2.5% of the table’s rows). But then I don’t have the SQL, explain plan, etc etc.
    What did you make of it?


    Comment by hpdba — September 6, 2009 @ 6:20 am BST Sep 6,2009 | Reply

    • Ari,

      “can’t find any segments” – maybe the question “why isn’t Oracle using my index?” will now be overtaken in the popularity race by “why isn’t oracle creating my index?”

      Charles’ posting isn’t really tied very closely to 11gR2 – but a new release gives people a chance to do some proper testing, and there were some interesting obvservations that could be made about his results (which, at first sight, might have been attributable to some change in the Oracle code – until he pointed out his oversight with the filesystemio_options).

      I’ve changed the URL to pick up the whole thread, which includes the data generation code and the query. My comments were similar to yours, but I think there are still a couple of minor variations in performance to examine, even though the biggest variation was almost certainly related to the effects of a file-system cache (possibly coupled with a slightly unexpected sub-optimal use of the buffer cache).

      Comment by Jonathan Lewis — September 6, 2009 @ 10:43 am BST Sep 6,2009 | Reply

  2. […] catching up on blog posts I see that Jonathan Lewis, Christian Antognini and Nuno Souto picked up on the deferred segment creation new feature in […]

    Pingback by Top 10 Oracle 11gR2 New Features | Structured Data — September 9, 2009 @ 9:03 pm BST Sep 9,2009 | Reply

  3. […] Jonathan Lewis: 11gR2 […]

    Pingback by Entradas de Oracle semanas 35-37 « Gruñidos sobre Oracle y SAP — September 13, 2009 @ 11:41 am BST Sep 13,2009 | Reply

  4. Jonathan,
    I was interested when I saw Oracle’s marketing blurb:
    “Oracle Database 11g Release 2 also offers the concept of ‘Instance Caging’ where databases can be confined to use specific cores in the SMP environment, thereby removing the need for virtualization or LPAR software.”
    Looking instance cagine up in the manual reveals it just limits “the number of CPUs that a database instance can use simultaneously”, by foreground processes via resource manager and CPU_COUNT. That doesn’t sound the same as restricting the instance to specific cores.
    I have never used RESOURCE_MANAGER_CPU_ALLOCATION, but perhaps 11gR1 did the same thing with this parameter, which is now deprecated.

    Comment by hpdba — September 13, 2009 @ 6:56 pm BST Sep 13,2009 | Reply

    • Does “Instance Caging” reserve CPUs as well as Memory (e.g. Process memory ) ?


      Comment by Dharmendra — September 14, 2009 @ 5:59 pm BST Sep 14,2009 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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

You are commenting using your 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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: