Old indexes never die…

Recently I was asked to describe the “life” of an index where I work.  Here’s what I had to say:

Primary Key and Foreign Key columns are indexed at table creation.  We always try to make sure all foreign key columns are indexed.

Subsequent indexes are created in response to functional requests for queries where we determine that an index would be the best approach.  These kinds of indexes are often Function-Based usually for space reasons (or to keep the index as small as possible).  Sometimes we use normal indexes, sometimes we use parallel full table scans if the data distribution and physical distribution makes the index impractical.  We’ll also see if we need a histogram on the column at this point.  We try to avoid hinting to use the new index.  Occasionally we compress an index.  Sometimes we determine that we should rebuild the table as an IOT.

Indexes are usually destroyed during space analysis where we do periodic reviews of space to see what we can drop and/or reorganize/move.  For example, our tablespaces have gone from being DMTs, to uniform-extent LMTs, to auto-allocate LMTs.  During each transition we evaluate whether or not we still need the index (and/or some tables).  Usually we drop it if we can prove that the index, even if it was in use, was non-useful.  That is, selects via equally aren’t the best way to get at the data.

Finally we’ve been slowly partitioning our largest tables which are basically historical time based INSERT tables.  Here, all indexes are going local, so it’s another opportunity for index review.

And the answer is…

It depends.

Well, of course it depends on something.  And I know, you’re all saying — that’s a cop out, consultant answer.  A prelude to proposing an expensive “discovery” engagement.  Just tell me the answer, darn it! :-)

To me, the answer is less interesting than in how you tell me what the answer might depend upon.  Here are some of my ideas:

  1. Does the workload remain constant? (or, more importantly, does the arrival rate of work remain the same?)
  2. Where is the workload coming from?
  3. How is the workload “packaged” or “translated” into database work requests?

I can reasonable see answers between 30% and 60%, and can suppose of scenarios where it goes in any direction, even up to 100%.  Here are 2 scenarios:

  1. The workload is from a public web site in which database work requests are “OLTP” like and demand is roughly constant
  2. The workload is from a set of worker programs which are polling the database to see if they have any new work to do

In case #1, I think we’d expect the CPU utilization to decline, as the database can turn around requests more quickly and take some time between requests to “take a break”.

In case #2, the CPU utilization might remain the same, as the work programs can now poll even faster!

The thought exercise for me was how would I phrase my set of questions if confronted with such a demand from my manager, Operations colleague or customer?  I’m not sure I’ve got a set of good questions yet, but I like to think about how I would ask and explain them…


Quiz time.

You have a machine running at 60% cpu (user) (minimal sys or wio) utilization with 12 cpus. You upgrade to a machine where the processers are 6X faster, but only has 4 of them. What’s the new predicited cpu utilization? Why?


Those of you that know me at work know that I don’t like complicated code — I call it “over-engineered”.  My staff now all know who Rube Goldberg is because that’s another thing I call over-engineered code — “Rube Goldberg code”.

Anyway, you know when you’re going down that path when you can’t get something to work — even though your code was copied straight from the Doc?  And then you keep hammering away at it, adding more and more functions, wrappers, etc until it finally works?  Now you have ugly code (but it works!) and still don’t understand why the code doesn’t work like the documentation says.

It’s often a lot of work, but worth it in the end to understand why it doesn’t work like it’s supposed to.

So, revisiting my migration story…

I get a call from the group and they’ve decided to use sqlldr and control files instead external tables.  Ok, I guess.  I think the external tables are cooler, but Ok.  And they try to load 9M rows into a partitioned IOT using a single stream, convential path.  Time to load = 28 minutes.  Too slow for me.  So I look at the control file, and I see something odd:

In the fields spec, I see:

VERSION_LOCK “to_number(trim(both ‘ ‘ from :VERSION_LOCK))”

The fields in the datafile are simple integers separated by tabs.  So why the complicated conversion routine?  They tell me they keep getting “invalid number” during the loads, so this is what they “HAD” to do.  Turns out that VERSION_LOCK is an optional column and some rows in data source don’t contain any data for the column.  And TRAILING NULLCOLS wasn’t taking care of it either.

This looks like overcoding to me.  So I did some digging.  And while I’m at it, I play some more with the external tables.  I declare the external table and lo and behold, I get the “invalid number” errors too.  So finally, I declare all of the external columns as CHAR to see what’s in them.  And then I use the good ole DUMP() function to see.

Guess what?  The column that was “empty” actually had a single blank character in it.  And here’s a simple quiz:

What does to_number(‘    100   ‘) return?  Answer, 100.
What does to_number(’59′) return?  Answer, 59.
What does to_number(’86 ‘) return?  Answer, 86.
What does to_number(NULL) return? Answer, NULL.
What does to_number(‘ ‘) return (single space inside the quotes)?

I think you could be forgiven if you guessed NULL — after all, it appears that to_number trims both leading and trailing spaces, and if it trims all the spaces, it should come up with NULL — but it doesn’t.  It says “invalid number”.

So, I need to convert the single space to NULL, because that’s what it is.  A simple LRTRIM option on the fields spec for the external table takes care of that — and now I can go back to declaring the columns as NUMBER because that’s what they are.

I do one other thing — I convert their partitioned IOT table into a simple heap table with a primary key index (actually I will create the index after the load is complete).  So far I haven’t gotten a reason why their 9M row table needs to be an IOT or partitioned, so I’ll try the heap one.

Time to load 9M records = 90 seconds.  Time to index 9M records = 150 seconds.  Total time = roughly 4 minutes.  Much better.

Do I think the time savings was due to the extra code? No.  It was probably due to the paritioned IOT.  But I still learned a lot.  Moral of the story?  One we all know and don’t apply enough — check your assumptions before starting to write “Rube Goldberg code”.

I’ll see your measly 6,000 tps and raise you…

Remember the other day when I was so impressed by updating roughly 6,000 rows per second.  What a rube I was.  That’s nothing.

So, Saturday night (I know, I need to get out more), I decided to work on the data loading problem I described in the last post.  We have roughly 60,000,000 rows to load and are required to get the job done in 3-4 hours.  Round up a bit and that’s 15,000 seconds.   Do the math and we need to be able to load about 4,000 rows per second.  Should be pretty easy — heck, if I can do 6,000 rows per second on the big AIX/EMC box, I should be able to do 4,000 rows per second on this Linux box.

I decided to try external tables for the loading — haven’t had a chance to play with them yet, so why not?  Here’s the documentation link.

So, I create an empty table for my target.  And then I create a 1,000,000 table to use to generate some test data by inserting as selecting 1,000 rows from dba_objects (twice and use a cartesian product to generate 1,000,000 rows).

(At this point, I should have been prepared for the results — but, it was late, I was tired, and I was just focussed on getting to the final step of loading the table.  But, for those of you who want the foreshadowing: the insert as select of 1,000,000 rows from dba_objects took only 8 seconds).

So then I use sqlplus to dump out the 1,000,000 rows to a flat file — took roughly 1 hour (no tuning of sqlplus fetching, forgot to turn termout off, etc..).

At this point it’s 12:30am and I’m really struggling to type commands correctly.  I finally get the external table created and fire up the insert /*+ append */ as select.

Total time to insert the 1,000,000 rows = 5.85 seconds.

I had to rub my eyes a bit to make sure I didn’t make a typo and only insert 100,000 rows.  But, nope — 1,000,000 rows in under 6 seconds.

I almost don’t believe it myself — at the Hotsos conference, Cary Millsap said: “Isn’t Oracle really fast?  Why is it so hard to make it go slow?  How come so many people are good at that?” :-)

So, that’s roughly 170,000 rows per second — or 40X faster than I need it to be.

Looks like we can load 60,000,000 rows in 6 minutes.

Where can I get more of these Linux boxes running 10gR2?