Choosing your platform based on TCA?

I giggled at "The Total Cost of Administration Champ: Microsoft SQL Server 2005 or Oracle Database 10g" over on ComputerWorld.com.

I've been in this business 17 years working in countless companies. I've implemented everything from cancer protocol databases to telecom billing systems and I can tell you management has never looked at the Annual TCA per database user as a deciding factor.

I can't believe people are actually paid to come up with these numbers. Lets see an apples to apples comparison. For example, put Oracle and MS SQL on the same hardware on Windows XP (yes, I'm going against my "Never Windoz" philosophy, but last I checked, MS SQL wasn't available on Linux). Then run ApplicationX against both platforms and measure number of customer complaints/hour for each system. Now measure how long it takes to solve those problems until customer is satisfied. That's a real number.

Airline Travel

Airline travel has changed a little bit since I last flew.  Granted, I don’t travel on business and fly maybe twice a year on average, so things could change without me taking notice.  Sure, we all know about taking liquids and gels on the plane is a big no-no now, but other changes abound as well.

First thing we noticed was there is a not-so-generous weight limit on baggage.  Our particular carrier had a 50 pound weight limit per checked bag, two bags per passenger.  Otherwise, they hit you with a $80 charge for “overweight” baggage.  We each packed one bag not wanting to lug our whole wardrobe to Hawaii.  No problem, we thought, until we stepped on the bathroom scale with the bags the day before we left.  One bag was OK at 46 pounds, but the bigger one weighed in at 58.  Problem was, the bag itself was 15 pounds (one of those super large 29” bags).  Valerie took out enough stuff to get it under 50 pounds, but that left the bag about 1/3 empty, which seemed like a waste.  Also, that gave us enough room to buy stuff and pack the bag again and go over the limit on the return trip. We made a mad dash to the store and purchased another 26” bag, stuffed all the stuff in and it tipped the scales at 48 pounds.  First hurdle passed.

We get to the gate to check in and there are kiosks instead of agents at the gate.  I’m no stranger to using the electronic check in when I only have carry on luggage.  But when you have bags to check how do they get on the plane?  Well, you wait.  You punch in all your information, put the bag on the scale and wait for an attendant to take you bag and put it on the belt.  Then the attendant goes to the next kiosk while you put your second bag on the scale and you wait again until he comes back around.  Sure, I understand they’re not paying someone to do that work, but now I have to wait longer.  And what about the 70ish woman ahead of me who had no clue as to what she needed to do?  She needed a ticket agent.  To me, that’s the sign of a company that uses automation to cut costs without regard to the customer’s time.

Anybody who knows me, knows I’m a big guy.  I get along in a normal seat, but it’s not the most comfortable experience for me.  Used to be that the exit row seats weren’t assigned until the gate agent saw you were capable to operate the emergency doors in an emergency.  I used to take advantage of that by getting to the airport two hours ahead of time and snagging an exit row seat about 80% of the time.  Got to the airport two hours early as usual, and requested exit row from the kiosk (see above).  “No problem”, the kiosk says, “I’ll just charge your credit card $41 per seat per leg of your trip.”  WTF?  Thanks, but no.  We board the plane and one seat out of 19 has a passenger who paid the extra $41.  Nice.  Luckily, we encountered one of the nicest flight attendants who saw I was “seat challenged” and “made” us change to an unoccupied exit row.  “I’ll get in trouble for this, but you’re going to die back there.” he said.  Nice to see a person who knows how they used to treat customers in a company that doesn’t care about the customer anymore.

Last, but not least, Airline food.  Does anybody really expect a decent meal on an airline?  Me neither.  In fact, I made conscious decision about 10 years ago that I’d rather have nothing than eat the half not-so-hot half ice-cold apple pancakes on a plane.   More and more airlines are going the way of Southwest in not even offering meals on their flights and opting instead for the obligatory snack of 6 pretzels.  Anyway, this airline has gone the same way and don’t provide meals for their economy passengers.  But you can purchase one onboard; for $5.  Personally, I’ve got no problems with this, but is it another way to extract $5 from the customer?  Perhaps.  Maybe when people pay for their food they will expect more quality.

Thanks

Thanks to my guest bloggers the last couple of weeks. I hope they gave you something a little different to read and maybe encouraged them to start blogging on their own. A couple observations over vacation will follow and progress to the normal stuff after that.

The Ultimate job....

What if you were a gamer and an Oracle DBA, what would be the ultimate job for you? You know a job where you couldn't wait to get to every morning!

I think I found the Holy Grail of jobs (at least in my opinion) and it pains me to see that I have to let this slip through my fingers knowing that if I applied I could get it. I keep convincing myself "No, I don't want to move to California". Don't get me wrong, I really enjoy what I'm doing now but... it's Blizzard *droool*.

Gaming has always been a hobby of mine (when I can find the time). I just recently acquired an old P233 and I slapped a hundred megs of ram with Windoz95 on it so I could play all those old games like MecWarriorII. Here’s the kicker I had an extra Nvidia PIC 125meg video card sitting around and I went to their website to see if they had the drivers for Win95 low and behold they did! So you’ve got 100megs of RAM and 125meg video card with plenty CPU power. The games play great the only thing is you can’t get that high resolution like 1024 X 786 only 800 X 600.

Now for something completely different….

For those of you who are upgrading from 9i to 10g Oracle has this nifty little validation checker that runs a script against your server (Solaris) to ensure all the settings, patches, bla, bla are correct. Well as of Aug 25th it’s no longer valid. Last week I was prepping a server for an upgrade ran the script and everything came back all nice, then on Saturday I was doing the actual install, it’s running its own checks and bam! It tells me I’m all messed up and not to even try installing 10g. Ohhh I was soooo mad! Sure would have been nice to know Oracle de-supported it. Only good thing that came from it all was that I was able to get back home and spend the weekend gaming *wink* hee hee.

The Joys of RFID

Welcome all to the “So What?”!! The Hunter family off to the South Pacific? *pffffftttttt* yea right he’s probably syncing up his Blackberry right now or sitting on the couch watching cartoons. Anyway……

I guess I should at least give a little background on myself before I dive into the Oracle silliness. Most of you know me as OracleDoc on the forums and I’m currently working for a Defense contractor who has a contract with the US Army in Germany. I’ve been here for over two years now and I don’t see an end in sight. In all actuality, I rather enjoy what I’m doing because it’s cutting edge and it keeps me on my toes, not to mention it’s in the land of beer and Autobahns!

I’ll give you a little sample of what we do here because I think it’s a great technology and it’s going to be more main stream with the commercial world here very soon (if it hasn’t already).

RFID (Radio Frequency Identification). Go watch this commercial then come back. Basically anything the Army slaps an RF tag on or any other device that has the ability to broadcast its location, my database stores its location. I can then regurgitate that information onto a webpage either textually or as an image on a satellite map. I’m sure you’ve all seen Google earth, so imagine you have a piece of equipment that you want to know where in the world it’s at, you plug in the identification number of the tag and voila`! there it is displayed neatly on a Sat image.

*side note; These things are great for tracking teenagers driving habits

Funny story….couple months ago I was looking for a particular truck that the Army uses solely in Kuwait for transporting food. I plugged in the criteria and said give me all the food carrying trucks that have reported within the last 24 hours and bring it to me on the Sat map. I was expecting to see all these little truck icons scattered throughout Kuwait but to my surprise, I see this one lonely truck icon smack dab in the middle of Beirut. I’m thinking “nawhhh it’s a glitch”…click ”refresh” but nope it was still there. So I plug in the id number of this truck and tell it to give me the history of all its positions. Good Lord…. this truck started out in Kuwait then worked its way up to Iraq, then on over to Syria, then finally stopping in Beirut. At this point my mind is throwing up red flags all over the place because there’s a remarks column next to the tag and it’s saying that the truck was destroyed by an IED (improvised explosive device) several months back. At this point I call in my boss and we start making phone calls.

As it turns out, the truck was stolen and the company that owned the truck instead of saying it was stolen just said it got blown up. You say stolen and the investigation and paper work start, you say blown up and you get reimbursed for the cost of the truck. Needless to say someone got their genitals whacked.

3 days later the little truck icon in Beirut disappeared. I love my job!

Aloha

October 1997 was the last time I took more than a week off for vacation. At that time, I was somewhere south of Summerlands watching Ferry Penguins come ashore at dusk. Anybody (except HJR) know where I was?

Anyway, the moons have aligned and I am taking off to a remote spot in the South Pacific for two weeks and a little R&R. No cell phone, no laptop. I'm not even sure about TV. Since I won't have connectivity for a while, I've invited a few people to be "Guest Bloggers" to fill in while I am away. I know the readers will get a new perspective, and I hope the guest bloggers have a little fun.

Joey Vayo
Actually, this is all Joey's idea. He came up with the idea of "guest blogger" a little while ago and I thought it was a good one. Of course, he was the first one I asked. He's been a regular on DBASupport.com for years and always gives helpful advice.

Steve Prior
Steve's brainchild is http://www.geekster.com. Although he is a developer by trade (boo, hiss), I think he is a tinkerer by heart. A usual conversation with Steve might start out with "I am looking for ODBC drivers for a PROLOG engine that I am building into my smart-home system to automatically flush a toilet..."

Brian Byrd
Another DBASupport.com regular, Brian is a master at making SQL more efficient both from a coding perspective and from the perspective of the database. While Brian and I don't always agree, I certainly respect his knowledge and ability to think about problems using a different approach.

Jeffrey M. Hunter
OK everybody, there is more than one Jeff Hunter in the Oracle world. I usually differentiate us by saying he's the guy with the RAC Paper and I'm the guy with the blog. Someday we're going to run off and create our own consulting company, but until then, I'm sure Jeffrey will check-in with some very exciting information.

David Pittuck
Last, but not least, we have the world renowned DaPi. David likes to be know as the "Old Cranky Philosopher", but is quite enlightened in the world of IT. (He’s also proud of the fact he has a NerdScore of 93). You can check him out at http://www.pittuck.com.

A big thanks to my guest bloggers. I hope you guys have as much fun blogging as I do.

Regularly scheduled programming resumes around September 18th. Take it away guys...

Fun Blogs

I really enjoy peeking into other people's lives for a brief second. I've been reading a couple blogs lately that are very well written and entertaining. If you like reading the likes of Heather B. Armstrong, LeahPeah, or WaiterRant, I think you'll enjoy New York Hack and Everything is Wrong With Me.

9.2.0.8 Patchset

Has anybody heard when the 9.2.0.8 patchset for Solaris and/or Linux will be available? All I see on Metalink is 2006Q3, which we're about half way through...

Compute or Estimate

A question came by me the other day that I had to do a little head-scratching on.

"How can you tell if your table was analyzed with COMPUTE or ESTIMATE?"

Good question. In my enviornment, I know it was COMPUTE because that's how I do my stats. But how would I know otherwise? There's a column called sample_size in dba_tables that indicates how many rows were used for the last sample. Ah, just a simple calculation.

Lets see how it works out. First, create a decent sized table:

SQL> create table big_table as select * from dba_objects;

Table created.

SQL> insert into big_table select * from big_table;

42396 rows created.

SQL> /

84792 rows created.

SQL> /

169584 rows created.

SQL> /

339168 rows created.

SQL> /

678336 rows created.

SQL> commit;

Commit complete.

Of course, we don't have any stats yet, but lets just check it anyway.

SQL> select table_name, last_analyzed, global_stats, sample_size, 
     num_rows, decode(num_rows, 0, 1, (sample_size/num_rows)*100) est_pct
     from dba_tables
     where table_name = 'BIG_TABLE'
     /

TABLE_NAME                     LAST_ANAL GLO SAMPLE_SIZE   NUM_ROWS    EST_PCT  
------------------------------ --------- --- ----------- ---------- ----------  
BIG_TABLE                                NO                                     

Sounds good. Lets analyze our table using dbms_stats and see what we get:

SQL> exec dbms_stats.gather_table_stats('JH','BIG_TABLE');

PL/SQL procedure successfully completed.

SQL> select table_name, last_analyzed, global_stats, 
     sample_size, num_rows, 
     decode(num_rows, 0, 1, (sample_size/num_rows)*100) est_pct
     from dba_tables
     where table_name = 'BIG_TABLE'
    /

TABLE_NAME                     LAST_ANAL GLO SAMPLE_SIZE   NUM_ROWS    EST_PCT  
------------------------------ --------- --- ----------- ---------- ----------  
BIG_TABLE                      14-AUG-06 YES     1356672    1356672        100  

Cool, that's what we expect. Now let's analyze and estimate with 1% of the rows:

SQL> exec dbms_stats.gather_table_stats('JH','BIG_TABLE', estimate_percent=>1);

PL/SQL procedure successfully completed.

SQL> select table_name, last_analyzed, global_stats, sample_size, 
     num_rows, decode(num_rows, 0, 1, (sample_size/num_rows)*100) est_pct
     from dba_tables
     where table_name = 'BIG_TABLE'
     /

TABLE_NAME                     LAST_ANAL GLO SAMPLE_SIZE   NUM_ROWS    EST_PCT  
------------------------------ --------- --- ----------- ---------- ----------  
BIG_TABLE                      14-AUG-06 YES       13371    1337100          1  

That definitely is what I was expecting.

I think I remember that if you estimate more the 25%, Oracle will just COMPUTE. Lets see what happens:

SQL> exec dbms_stats.gather_table_stats('JH','BIG_TABLE', estimate_percent=>33);

PL/SQL procedure successfully completed.

SQL> select table_name, last_analyzed, global_stats, sample_size, 
     num_rows, decode(num_rows, 0, 1, (sample_size/num_rows)*100) est_pct
     from dba_tables
     where table_name = 'BIG_TABLE'
     /

TABLE_NAME                     LAST_ANAL GLO SAMPLE_SIZE   NUM_ROWS    EST_PCT  
------------------------------ --------- --- ----------- ---------- ----------  
BIG_TABLE                      14-AUG-06 YES      447748    1356812 33.0000029  

Here I'm on 9.2.0.5. Previous to 9.2, this would have done 100% of the rows, but in 9.2, it will estimate all the way up to 99%. (Why you would estimate 99% vs. 100%, I have no clue, but it can be done).

Let's see what the block_sampling does:

SQL> exec dbms_stats.gather_table_stats('JH','BIG_TABLE', 
     estimate_percent=>10, block_sample=>true);

PL/SQL procedure successfully completed.

SQL> select table_name, last_analyzed, global_stats, sample_size, 
     num_rows, decode(num_rows, 0, 1, (sample_size/num_rows)*100) est_pct
     from dba_tables
     where table_name = 'BIG_TABLE'
     /

TABLE_NAME                     LAST_ANAL GLO SAMPLE_SIZE   NUM_ROWS    EST_PCT  
------------------------------ --------- --- ----------- ---------- ----------  
BIG_TABLE                      14-AUG-06 YES      132743    1327430         10  

Eh, nothing new there.

Lastly, let's see what dbms_stats.auto_sample_size does.

SQL> exec dbms_stats.gather_table_stats('JH','BIG_TABLE',
     estimate_percent=>dbms_stats.auto_sample_size);

PL/SQL procedure successfully completed.

SQL> select table_name, last_analyzed, global_stats, sample_size, 
     num_rows, decode(num_rows, 0, 1, (sample_size/num_rows)*100) est_pct
     from dba_tables
     where table_name = 'BIG_TABLE'
     /

TABLE_NAME                     LAST_ANAL GLO SAMPLE_SIZE   NUM_ROWS    EST_PCT  
------------------------------ --------- --- ----------- ---------- ----------  
BIG_TABLE                      14-AUG-06 YES     1356672    1356672        100  

That's interesting, 9.2 thinks my best method is to compute. I try this on several 9.2 dbs and different sized tables and it picks 100% every time. So I go to the friendly Metalink and find a couple bugs in 9.2 that indicate auto_sample_size doesn't exactly work in 9.2.

Not sure where I'll use this in the future, but it was an interesting experiment anyway.

Done tweaking

I think I'm done tweaking for a while.  The format is a little less fancy that I envisioned, but to me, it's just as easy to read now as the white & grey.  Plus I like that the text area grows and shrinks to the size of your browser.  When I'm on my desktop with higher resolution I can read more.

Why I got out of the GUI business

I started out life in the IT world as a programmer banging out GUIs for every type of system you can imagine. Every user wanted something different; this one wants green fields, this one wants white. Another wants fonts bigger, another wants them smaller.

On a totally unrelated note, I'm tempted to put the blog back to Grey & White.

Waiting

What do you do when you're waiting for a 17 hour import to finish? Tweak you blog, of course!

Platform migration in a 9i world

One of my 4-way Solaris boxes just wasn't cutting it anymore. We've tuned what we can tune and tweaked what we can tweak. The box was running with an average run queue of 8 lately. Time to upgrade the hardware. We chose a nice fast Linux box from HP and hooked it to an HP EVA disk array.

So how do you move 660GB of data from Solaris to Linux? On Oracle 9i?

Moving to 10g was considered for about 30 seconds, but we decided it was too many changes at one time for one of our most important applications. The next logical choice was export/import. On the first round, it took about 14 hours to export and 4 days to import. OK, that can't be done in a weekend.

The we decided to break the tables up into four somewhat equally sized groups and run export/import in four threads. This actually worked pretty well for the overwhelming majority of tables. About 4 hours for the longest group to export, and 43 hours for the longest thread to import. Still not doable in a weekend, but we're closing in on a reasonable timeframe.

We noticed a lot of time was being taken up creating indexes. So we decided to pre-create the tables without indexes and create indexes in parallel with nologging. This shaved about 6 hours off our total time.

Then we looked and saw two Index Organized Tables were taking a lot of time relative the the number of rows they contained. I then did some testing with import on IOT tables vs. Heap tables and didn't see any significant difference. In fact, the testing I did proved the two IOT tables shouldn't have taken as long as they did. I looked at them a little more closely and found the tables contained a TIMESTAMP field. I did some more testing and compared import with a DATE field vs. import with a TIMESTAMP field and found the TIMESTAMP field was causing the slowdown. Once we identified the TIMESTAMP field was the problem, my boss found note 305211.1 on Metalink which explained our problem. We then timed the import with COMMIT=N for those two tables and found the time went from 26 hours to 17 hours. I thought, "lets try to fill the tables through a database link". The database link yielded the best results of all in about 6 hours.

The last piece of the pie was the FK constraints. We didn't want to re-validate all the constraints since we knew all the data was going to be copied over (after manual verification, that is). So we created the constraint with NOVALIDATE which took relatively no time.

After everything was back in, we estimated statistics on our biggest tables and computed statistics on our smaller tables. It took about three weeks to figure out the method, but the time researching the project paid off when the database was available after 34 hours.

The Cashier and the Cell Phone

The cell phone is everywhere today. You can't walk two blocks without running into 3 people with something stuck to their ear. It's just part of today's world. I'm just as guilty as the next guy of using my cell phone when I probably shouldn't. But the other day, I experienced a person so rude, I just had to tell you about it.

I just finished grocery shopping at my local big-box store. All my items were on the belt for the cashier and he was flicking them by the scanner one by one. The particular market I frequent has about 1 bagger for every 3 cashiers. I'm perfectly able, so I don't mind starting to bag my own groceries while the cashier finishes scanning my order. After all, I'd rather have the dedicated bagger help an old lady than me. I have a mountain of groceries stacked up, but i figure the cashier will help once he's done running my stuff through the scanner.

He scans my last item, tells me $93.14, and whips open his Nextel phone. I continue to bag thinking he'll get right back to me in a second.

"Hey girl" he talks to the phone after the familiar Nextel beep-beep-beep.

"You comin' over?", she asks.

"Who's dere?"

"X and Y are already here, we're waitin' for Z and you."

"Oh. I don't get outa here 'til 8."

I'm done bagging at this point, just a little ticked off. I go to the EFT pad, swipe my ATM card and key in my PIN number. The display reads "Waiting for Cashier". Yeah, no kidding.

Their teen-age banter goes back and forth for another two minutes. The yuppy woman behind me gives the cashier the "What the F*$K are you doing?" look while her significant other blatently flashes his wrist with and obvious look at his Fauxlex.

Two minutes later, we hear "OK, we'll hookup afta I get outa dis place."

He hits the magical combination of buttons to let me pay and I'm off just shaking my head at the poor customer service.

Anti-Midas

Why is it that everytime I touch something with a Microsoft tool, it breaks? For example, I composed my last blog entry using MS Word. Instead of just publishing from Word, I highlighted the whole thing, copied it, and pasted it into blogger's editor. Everything looked fine to me, so I published.

On Monday, I realized I didn't get my feed via RSS. My feed provider said the feed had an invalid format and was complaining about invalid namespaces that were "Mso" something or another. I checked the HTML for my post and sure enough, there were these little tags with proprietary Microsoft stuff in them. Nice.

I edited the HTML and took all the MS garbage out and voila, my feed was back in business.

Funk

I’m in a funk.

A lot of small projects had to take a backseat while I was finishing up my two year project. Now it’s payback time. I have a list of about 20 things that I am working on that aren’t what I’d call real interesting, but they need to get done anyway. Things like cloning Oracle Applications. Yuck. But a necessary evil.

I thought getting some reading under my belt would get me back into the swing of things. I picked up Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions three times in the last two weeks, but just couldn’t get into it. Ventured to a different Barnes and Noble last night to peruse their computer section, but came out with People Magazine.

I get started on a bunch of things, but just can’t seem to finish. In fact, I started on this post about 4 days ago, but just never got around to finishing it. I have four good ideas in my blog “Drafts” area, but just can’t flush out the energy to fully develop them.Sometimes I feel like Sisyphus rolling my stone.

This too shall pass.

Two Years

You may have noticed that the blog hasn't been updated on a regular basis lately. I've been working on a project that has basically consumed me in spurts for the last two years.

Back in the summer of 2004, we decided it was time to undertake an upgrade of some software that I thought would take about six months. During a test upgrade, we discovered that it was a little more complex than we though and that a bunch of technology pieces had to be upgraded before we could upgrade the software. So we put in place a plan to upgrade each technoloy component as a separate phase and implemented them in regular intervals. Once we got one piece implemented, we started upgrading and testing the next technology piece.

Along the way we had to work with various support organizations to get each piece implemented completely correctly. Even though we enlisted those support organizations, piece X version 1.1.8.21 didn't work with piece Y version 8.1.6.1 which lead to another piece that had to be upgraded. We upgraded in about 6 seperate phases, with a couple mini-phases in between. During the same window, we had some staff changes both on the user side and the technology side that made things more difficult. When the project sponsor from the executive side stepped down a month before we were supposed to go live, I got really nervous. We pushed on and were finally able to upgrade the software in a 46 hour marathon session without a hitch.

This has probably been the longest project I have ever worked on as a DBA. After two years of sporadically working on the same project, I'm relieved to have it over with. At the same time, I learned more about the product than I ever dreamed (or wanted to know) and picked up a bunch of technology awareness and project management soft skills that I couldn't have as "just a DBA". Hopefully the business sees it the same way.

Since that time, the vendor has released another update. After two years of work, I'm a version behind now. Back to the drawing board.

Laptop Woes

I've had my Sony Vaio (PCG-GRT250P) for about two years now. I basically use it only for internet browsing, email, and VPNing into work when I am away from the office.

Just about a year ago, it was caught in a vicious reboot cycle. The computer would begin to come up, get to a certain point, and reboot. Any attempt to boot from the harddrive was fruitless. I succombed to handing it over to my crack IT guys and had it back in a couple days with a new drive.

I've been happily spinning along until about a week ago. I'll be working along and all of a sudden the drive starts to spin like crazy and I get a blue screen of death. I have to power the thing off and back on at which point it tells me "Operating System not found". First couple of times I got really worried, but after I learn the pattern, I know to turn it on, reboot, come up in "Safe Mode", shutdown, and startup. At first, it only did it once a day, but now I can run for...




... (OK, I'm back now, it just did it again) ... about 10 minutes before it craps out. Funny thing is, now I have to let it "rest" (or basically cool down) before I start it up again.

I consulted my crack IT guys and sure enough, they said it was the drive again. A new drive is on order and should be here in a couple days. I can...





....(there it goes again) ... live with it until then.

Upgraded "Freeware"

I've used this freeware program called Good Sync on my laptop to sync the "My Documents" and my email folders to my 512M USB key. Sure, it's not perfect, but it's close enough.

I came across Good Sync a few months ago and downloaded version 3.something. I setup a job for each folder and I scheduled it to run every 30 minutes while the USB key was attached.

Today I got a message saying that version 4.6.1 was ready and asked if I wanted to update. I thought "Sure, why not?". I downloaded the software, tried to manually sync up and got an error saying my jobs were too big for the free version of Good Sync and I'd have to pay $19.99 to purchase the "Pro" version. Nice.

A Good Cop?

If you were offended with Typical Greenwich, you'll be even more disgusted with this story.