11g

I’ve previously looked at how to generate an Oracle block dump, time to now go into a little more detail. As I mentioned, a block dump is a formatted representation of the actual contents of an Oracle block. Producing strategic block dumps can be an extremely useful method of determining what might be going on in Oracle under the [...]

When using table or index compression certain restrictions apply. Since I find it always hard to gather that information from the manuals and also some of the restrictions are not documented properly or not at all, I'll attempt to summarize the restrictions that I'm aware of here:

1. Compression attribute on subpartitions
Note that you can't define the COMPRESSION on subpartition level - the subpartitions inherit this attribute from the parent partition.

It is however perfectly valid and legal to exchange a subpartition with a compressed table and that way introduce individual compression on subpartition level. It is unclear why this is only possible using this indirect method - it simply looks like a implementation restriction. Since compressed data is most suitable for data warehouse environments and these ought to use EXCHANGE [SUB]PARTITION technology anyway this restriction is probably not that crucial.

2. Number of columns
Basic heap table compression and the new OLTP (in 11.1 called "for all operations") compression are both limited to 255 columns. If you attempt to compress a table with more than 255 columns then no compression will be performed although you don't get an error message. Interestingly the COMPRESSION column in the dictionary still shows "ENABLED" which is certainly misleading.

3. DDL Restrictions - modification of table structure
As soon as a segment contains compressed data or has been marked for compression (does not apply in all cases, see below for more information), the following restrictions regarding the modification of the table structure apply:

Here is another good reason why you probably don't want to use tables with too many columns.

The first good reason is that Oracle stores rows of conventional heap tables with more than 255 columns (where at least one column value after the 255th is non-null) in multiple row pieces even when the entire row may fit into a single block (and up to 11.2 doesn't support basic and OLTP heap table compression on tables with more than 255 columns). This leads to something that is sometimes called "intra-row chaining" which means that Oracle needs to follow the row piece pointer to access columns after the 255th one leading to multiple logical I/Os per row, up to four for a row approaching the hard limit of 1,000 columns.

Of course such a multiple-piece row easily can result in actual row chaining where the row pieces are scattered across different blocks now potentially leading to additional random single block I/O, but I digress...

This simple example allows to see this (and the other, yet to describe) effect in action - it creates by default a table with 1,000 columns with 10,000 rows where each row resides in a separate block (and therefore only intra-row chaining should occur). I used a 8K block size tablespace with Manual Segment Space Management (MSSM) in order to avoid the ASSM overhead in this case:

A troubleshooting session at one of my clients started with the following description of the problem:

In some specific database environments sometimes (but not always) a particular batch process takes significantly longer than expected - which in that case meant hours instead of a few minutes.

It could also be observed that particular SQL statements showed up as most prominent in that case when monitoring the process.

So the first thing was to be able to reproduce the issue at will which was not the case so far.

It was quite quickly possible to narrow the problem down to a single call to a complex PL/SQL stored procedure (that was part of a package and called a lot of other stored procedures / packages) that dealt with handling of LOBs representing XML data.

It turned out that by simply calling this stored procedure multiple times in a loop the issue could be reproduced at will and also in different database environments as initially reported.

The rather unique aspect of that stored procedure was that it got executed in an "exclusive" mode which means that it was run on a quite large IBM pSeries server (AIX 5.3, 11.1.0.7, 240 GB RAM, numerous P6 multi-cores, expensive storage) with no other business processes active at the same time. It was mostly a serial execution and when it was monitored, it spent most of its time in two SQL statements that were very simple and straightforward. It actually were static SQLs embedded in PL/SQL that queried a single, very small table using a unique index access plus a table access by ROWID. So we had two SQL statements that under normal circumstances at most could generate two logical I/Os per execution, since the index/table queried was so small and therefore was completely held in the buffer cache.

I will get back to the stats stuff at some point, but I'm quite busy at the moment working on something that I can't talk too much about, but which is throwing up enough generic issues to talk about. This is one that I meant to blog about ages ago when I first noticed it but when it caused us some problems last week, it was a useful reminder.

In summary you need to be careful when you upgrade to 11g because Resource Manager is enabled by default!

I don't want to blog about the ins and outs of Resource Manager and whether it's a good thing or not, but I do think this is a pretty extreme change to implement without a lot of surrounding publicity. It's a bit like the auto stats gather job that appeared in 10g that caused so many problems for Oracle users. It seems like it might be a good idea, but would you really want to introduce it on to a stable system that you're upgrading to 11g?

But rather than just talk about the change, I wanted to highlight how I first realised it was going on ...

<!-- s9ymdb:292 --><!-- s9ymdb:292 --><!-- s9ymdb:292 -->

Update 9-Apr-10: The documentation was pulled out from public which is expected. Looks like someone “leaked” it hoping that it won’t be discovered without being referenced from the OTN. Guess, what? How many of us who expected it and downloaded it locally just in case? ;-) – documentation is back.

Thanks to Marco Gralike for drawing my attention to it — yes, Oracle Enterprise Manager 11g Grid Control documentation is available online!

There you can find the list of EM 11g new features. After I quickly skimmed through it I could mention few areas:

Jeremiah Wilton will be presenting High Performance Oracle 11g in the Amazon Cloud at Collaborate 2010 – an updated version of his February RMOUG presentation.  For a preview, you can find both the white paper and presentation slides from RMOUG on our white paper page.  Currently scheduled for Monday, April 19, the session abstract reads:

The Amazon Elastic Computing Cloud is rapidly gaining acceptance as an enterprise-class Oracle platform. In this virgin territory, Oracle professionals need a complete understanding of cloud computing concepts and architectures. This session addresses the basics, and goes further, providing guidance on how best to optimize and configure Oracle for performance, stability and manageability in the cloud. Gain a complete understanding of Cloud Computing Learn the details of Amazon’s Elastic Compute Cloud Develop the knowledge needed to deploy and effectively manage high-performance Oracle services on Amazon EC2.

Just a quick announcements…

If you didn’t manage to attend my presentation, Oracle 11g ASM — The Evolution, during RMOUG or other conferences, you have a chance to see it online today. I’m doing it a web-cast at RAC SIG. It’s today, 4-Mar-10 at 12:00pm EST (9:00am PST).

Oracle ASM 11g Release 2 – The Evolution

Oracle Automatic Storage Management has proven to be one of the most widely adopted new features in Oracle Database 10g and it has been dramatically improved in the later 11g releases. This presentation will explain what changes are solved by ASM, how these challenges are solved, what barriers there are to ASM adoptions, and how 11g Release 2 addresses these barriers.

I shall say that the slides alone are not that helpful without my commentary but if you didn’t manage to attend it on one of the previous conferences, we will be releasing it as a webinar soon so stay tuned.

At present I'm quite busy and therefore don't have much time to spent on writing blog notes, but I couldn't resist to publish this small and simple test case.

Often you can read (mostly unqualified) rants in various places and forums about the Cost Based Optimizer how stupid, unpredictable etc. it seems to be.

So I think it's time to demonstrate how clever the optimizer sometimes can be.

Consider the following setup: