Pythian Group

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

Day 2 finished yesterday. It was quite a busy day, with some excellent sessions.

Battle of the Nodes: RAC Performance Myths — Riyaj Shamsudeen
A great presentation on popular RAC myths, with some great examples. Excellent visuals that made complex processes look simple. I really liked this one.

Getting the Most Out of AWR — Tim Gorman
A first-rate session attended by a lot of the conference. It went into detail on what scripts are available to extract AWR information without needing Grid Control or Database Control. For command-line lovers, it’s great.

The SAN is guilty… until proven otherwise — Gaja Krishna Vaidyanatha
A very important session for all DBAs, showing the end-to-end components involved in database I/O. There are so many more components that can cause problems between the database and the physical spindles. Concepts, case studies, plenty of information.

Understanding Oracle Execution Plans: How SQL is Really Executed – Tanel Poder
One of those eye-opening sessions, starting with how to read SQL Execution Plans, and moving to showing stack traces and mapping function calls, to Execution Plan steps. A must-see for everyone tuning SQL.

And that is it. As exhausting conferences are, I always wish for them to have been longer.

Day One at RMOUG in Denver is now over.

There were quite a few interesting presentations. Unfortunately, the very first I went to was canceled due to car trouble. I also found that several sessions of similar interest to me overlapped, so I had to choose my spots.

Advanced Oracle Troubleshooting
This presentation was particularly good. Tanel goes into detail on how to quickly asses a situation without going through a number of “health checks” and still be nowhere near solving the problem. His approach is to look directly at what a “hanging” session is waiting on, and to systematically determine the cause of the problem, with no time wasted.

Putting your database on a Diet: Oracle’s Data compression
A short overview of table compression. I found that that even though the presenter obviously had some experience with compression, there were hardly any examples nor anything mentioned about how to determine proper re-ordering to improve compression.

All About Oracle’s In-Memory Undo
An unusual topic—something that works so well that no one really talks about it. The presentation, however, was very short, and provided little new information. There was only one demonstrated test case. Although it went into detail about the difference between in-memory and standard undo, the other-than-obvious effects were omitted.

During lunch I took a picture that shows the entire RMOUG crowd:

Tomorrow is Day 2, and I will be posting about it here.

I am back on the road, going to RMOUG Training Days to present The Answer to Free Memory, Swap, Oracle, and Everything.

I am quite excited, as the RMOUG schedule (PDF) looks quite promising, especially these presentations:

• Further RMAN Optimizations in 11g — Stephan Haisley
• Advanced Oracle Troubleshooting: No Magic is Needed — Tanel Poder
• Understanding Oracle Execution Plans: How SQL is Really Executed — Tanel Poder
• The SAN is Guilty until proven otherwise — Gaja Krishna Vaidyanatha

Some of these overlap, so I guess I will have to make a difficult choice.

I hope to see you all in Denver.

Last Friday (September 26), Paul Vallée and I were lucky enough to interview Kevin Closson about the Oracle Exadata Storage Server. A tidied-up stream of the audio is here: closson-interview.m3u.

The audio quality is a little spotty here and there, so you might like to follow the transcription below.

Paul gets the interview started.

Paul Vallée (PV): Christo Kutrovsky and myself, Paul Vallée. We’re on the line with Kevin Closson of Oracle (and prior to that with Hewlett-Packard, and prior to that with Polyserve, and prior to that with Sequent). A giant of our industry, and I’m honoured to be speaking to him. Kevin, hello.

Kevin Closson (KC): Well, they always say that flattery gets you nowhere, but apparently it’ll get you on the phone.

PV: [laughs] Very nice!

KC: No seriously, it’s more than a pleasure to be here. I like what you guys do, so this is good.

PV: Thank you, Kevin. So, we are here to talk about the work that Larry Ellison announced yesterday, specifically the work around the Oracle Database Machine and the Exadata Storage Server. Kevin, can you just quickly introduce yourself and how you came to be involved in the project?

KC: Right. So, I’m a performance architect with Oracle, and the project that I’m stationed on, if you will, is the development team for Oracle Exadata Storage Server. And the way I came to Oracle is, quite a few of the folks who are involved with the very genesis of Exadata are people that I’ve known and worked with closely dating back to the early ’90s. And after a fruitful endeavour as the chief software architect for Oracle solutions at Polyserve, it became an opportunity to latch onto Oracle, because we sold our company to them. So there we are.

PV: How exciting! Congratulations! So I noticed that there’s still a little, I guess a diversion in terms of the branding. Larry definitely introduced it as the Exadata Programmable Storage Server, and I double-checked the video. But in your blog, you’re calling it, for sure, just the Exadata Storage Server. Just how recently was the marketing/messaging developed for this?

KC: You know, I’m not a part of the Go-To-Market (GTM) efforts, but, you know, honestly, the way these things are brought to market . . .  They’re developed under a project name, and the project name remains the same for years. It was over the last few months that Marketing began cooking the name and what-have-you. Now, if you’re referring to something that Larry said in his keynotes, I have to admit I didn’t commit to photographic memory all the slides. And certainly, if he used the term “programmable”, I’m not going to correct Larry Ellison.

PV: [laughs] That would be risky.

Read the rest of this entry . . .