Pythian Group

Solaris 11 has been release a few days ago. I was anxious to upgrade as I was using Solaris Express 2010.11 for some time and I was hitting a couple of bugs. One was a nasty IP layer bug (BAD TRAP: type=e (#pf Page fault) rp=ffffff005c9b1040 addr=20 occurred in module “ip” due to a NULL pointer dereference) causing kernel panics – not a good thing for a storage server.

Since I was using a version of 11 already, an experimental upgrade was not a problem. With the BE (boot environments) feature, one could boot into any version safely. BE is an awesome feature. Need to install a patch? Install into a boot environment – any problems reboot into the old environment. BEs leverage ZFS snapshots to create a clone of your boot disk, install any patches onto it and allow you to switch flawlessly between the two.

The upgrade process
The upgrade was extremely easy. With the pkg manager – everything is fully automated. Simply run the update and wait. It downloads everything as needed, creates a clone, upgrades it by installing and removing any packages required and makes it current. The next restart brings you the new upgraded release.
So I gave it a try – and it worked – flawlessly. I was pleasantly surprised and happy. Of course, it did give me a scare after the first reboot. Took nearly 15 minutes (compared to 2) as it had to initialize something about the packages. It even converted by /etc/hostname* network config files to the new ipadm method – which I love. Read the rest of this entry . . .

One of the exclusive Exadata features is the Smart Flash Cache (Oracle White Paper PDF). On a full rack, there is 5 TB of flash cache, which can store a significant amount of data. Quite often it’s several times more than the working set for a given reporting system.
Read the rest of this entry . . .

Blogging from the Speaker Ready room which is now suited on the balcony overlooking the exhibition. I must say that I like the new location. Not only it is closer to the action, but allows you to oversee who is hanging out over the exhibitors.

There’s been a number of interesting sessions, but it’s little things here and there that I learn that provide some real value.

Here’s a few examples

Tomorrow at 8:45 AM GMT time (which is 3:45 AM on my internal EST time clock) I will be doing the updated version of my Memory presentation – “The Answer to Free Memory, Swap, Oracle and everything”. I should perhaps rename it to “Memory Tales from the Middle of the Night” for next year…

This is just a quick post to note that I’ve corrected my blog on Storage Indexes here, after a follow up blog from Kerry Osborne indicating an error on my part.

Storage Indexes DO work with bind variables, but the implicit convertion works differently.

Thanks Kerry!

This post will be removed in 2 weeks.

There is a little know fact that Oracle table compression doesn’t work with tables that have more than 255. It’s has been reported here and compression limitations in general here.

As a follow up from my previous post on Exadata Design, where I question the use of dimensions for certain attributes in data warehouses, I figured I should test whether HCC works with tables that have more than 255 columns.

And it does. Here’s my test case.

Setup:

create table ctest (
col_1  varchar2(30) default 'JUSTSOMEDATA',
col_2  varchar2(30) default 'JUSTSOMEDATA',
...
col_255  varchar2(30) default 'JUSTSOMEDATA'
);
insert into ctest (col_1) select 'OTHERDATA' from dba_objects;
insert /*+APPEND*/ into ctest select * from ctest;
commit;
insert /*+APPEND*/ into ctest select * from ctest;
commit;

Build various scenarios (NOTE: second create adds the 256th column):

/*REGULAR*/
create table ctest_cmp compress as select * from ctest;
create table ctest_cmp1 compress as select t.*, 'NEW' col_256 from ctest t;

/*HCC QUERY*/
create table ctest_hcc compress for query low as select * from ctest;
create table ctest_hcc1 compress for query low  as select t.*, 'NEW' col_256 from ctest t;

/*HCC ARCHIVE*/
create table ctest_hccA compress for archive low as select * from ctest;
create table ctest_hccA1 compress for archive low  as select t.*, 'NEW' col_256 from ctest t;

And the results:

SQL> select round((bytes)/1024/1024) as mb ,  segment_name from user_segments t where segment_name like 'CTEST%' order by segment_name;

        MB SEGMENT_NAME
---------- ----------------------------
       824 CTEST
         3 CTEST_CMP
       824 CTEST_CMP1
         1 CTEST_HCC
         1 CTEST_HCC1
         1 CTEST_HCCA
         1 CTEST_HCCA1

Oracle Exadata V2 is a very well balanced database machine combined with smart and innovative software. One of these innovative features is the Storage Index (see also Marc’s post here) – a game changing feature in my opinion.

What is the Storage Index?

The Storage Index is more similar to partitions than to an index. It should be called an anti-index. It allows Oracle to skip reading granules of 1Mb when they are known not to have the data requested. More info here Similar to bloom filters (See Christian Antognini ‘s paper) they reduce the amount of work required, similar to partition elimination, but far more granular, and fully automatic on ALL columns (certain datatypes excluded).

Here’s an extreme example of a Storage Index at play. CKTEST is a 80 Gb table, with no partitioning nor any kind of indexes. The ID column is the equivalent of a primary key, and ever-incrementing value.

select * from cktest where id = 283942;

cell physical IO bytes eligible for predicate offload	81 920 000 000
cell physical IO bytes saved by storage index	81 917 984 768
physical read bytes	81 920 212 992
physical read flash cache hits	0
physical reads	10 000 026
physical reads cache	26
physical reads cache prefetch	0
physical reads direct	10 000 000

Read the rest of this entry . . .

It’s one of those hints that you’ve heard about many times, but all the information is scattered, and finding what you need is really hard.

Many (including myself) have expressed the deepest desire to see it documented, but that never happened. I’ve just troubleshot a problem and used this hint a lot during my “what if” scenario testing, and found this hint to be quite useful. Thus, I decided to document it here on the blog.

I will come back and update this blog as I discover new parameters, and feel free to suggest what you’ve found in the comments. If you can, include an example to illustrate usage.
Read the rest of this entry . . .

Here are the slides from my presentation at RMOUG 2010.

I am not sure how much sense all this will make without my comments. We may do it in a webinar if there is sufficient interest. Regardless I will probably be doing it again at some point in the future.

Ever wondered why recreating certain indexes takes forever, even when you do so in parallel? Ever wondered why certain PQ queries just don’t run that fast?

Here’s a serious performance bug that’s been in Oracle for a while, and finally there are hints of it been fixed, but only partially.

The bug happens when performing sorting operations in parallel, and the source data is already well sorted. The “ranger” doesn’t do a good job of properly assigning row ranges to sorter processes, and ~90% of the rows end up being sent to the same parallel process, regardless of the level of parallelism. So even if you have 256 CPUs, only about 10% performance improvement is achieve, instead of a factor of your parallelism when running the query in parallel.

For example, if the non parallel sort/index creation took 45 minutes, running with parallel 32 will take 41 minutes, instead of the possible 1.4 minutes (assuming you have sufficient horsepower).

Read the rest of this entry . . .

Oracle 11g has a new performance enhancement when adding columns. In the pre-11g releases, adding a new not null column with a default value would have caused a massive update on the entire table, locking it for the operation and generating tons of undo and redo. I’ve seen this happening in production.

Oracle 11g has improved this behaviour by storing the default value in the metadata and making the column addition instantaneous. An example of this feature at work can be seen in 11g Enhancement for ALTER TABLE .. ADD COLUMN Functionality and some bugs regarding sysdate, as pointed out in the comments.

Although this is a welcomed enhancement, there are some unexpected aspects beyond the basic operations.

First, we know default values for new columns are stored in the metadata, but what happens when you change the default?

Read the rest of this entry . . .