Job Opportunity

Just learned of a great DBA opportunity in the Jersey City area. Contact Evan Lerman from IJC Partners LLC at (212)626-6920. From Evan:

FINANCIAL EXPERIENCE A MUST ORACLE 9I AND 10G LOCATION JERSEY CITY PAYS UP TO 120K BASE

QUALIFICATIONS:

Minimum of three years experience working as a Database Administrator.

Familiarity with Oracle and Microsoft SQL Server with emphasis on
Oracle. Knowledge of relational database concepts and standards, best practices and procedures relating to database administration.

Experience in financial industry, insurance industry or law firm a plus. Must have excellent technical skills and knowledge of Unix and Windows operating systems. Strong interpersonal, analytical and troubleshooting skills with superior verbal/written skills are required.

JOB Description:
The Senior Database Administrator directs and controls the activities related to data planning and development, and the establishment of policies and procedures pertaining to its management, security, maintenance and utilization. Sets and monitors standards; ensures that database objects, program data access, procedures and facilities are used properly. Advises management on database concepts and functional capabilities. Position is also responsible for installation and ongoing maintenance of enterprise server operating systems and system management products, as well as coordination of installation and upgrades to enterprise servers. Position also provides on-going production system support and performs other duties, as assigned.

Duties & Skills
A. Business/Application Knowledge

• Understands the company's general business functions, and has a conceptual understanding of each unit's activities.
• Has general knowledge of assigned application systems.
• Comprehends the relationships between business activities and application systems. Is able to determine impact of database changes to the application systems, and vice versa.

B. Technical/Programming Skills

• Builds and maintains all Corporate database environments.
• Builds and maintains test database environments.
• Is responsible for recommending and planning the installation of new releases of database software.
• Ensures the integrity of all physical database objects and established database procedures.
• Creates storage groups, databases, tables and views, reviews SQL, develops and enforces database standards.
• Ensures work is thoroughly tested and smoothly implemented.
• Is responsible for database performance and capacity monitoring and tuning. Prepares regular capacity analyses for management review.
• Assists in determining storage procedures for on- and off-site storage of historical data.
• Assists in establishing backup, recovery and restart procedures.
• Assesses the need for additional hardware or software to assist in monitoring or performance of database applications.
• Communicates availability requirements for database accessibility.
• Coordinates schedules and procedures for the implementation or discontinuance of relational database applications.
• Provides technical support and basic training in the proper use of production databases to database users.
• Assists in troubleshooting application problems where database management is an integral element.
• Mentors junior staff on database management techniques.
• Installs upgrades and fixes to server operating systems (UNIX, NT, etc)
• Analyzes and recommends upgrades and/or new acquisitions of hardware to support new systems or growth of existing systems.
• Coordinates vendor installation of hardware.
• Installs and utilizes third party system management software to monitor overall server performance and capacity utilization.
• Designs and implements backup schedules for critical databases.

C. Analysis Skills

• Provides ongoing research and development activities to investigate new technologies and tools which might be used by Company personnel to more effectively and efficiently perform their jobs.
• Performs functional evaluations of candidate products.
• Prepares time and cost estimates for assigned projects.
• Understands the Company System Lifecycle Methodology, and project development lifecycle.
• Acts as methodology and process mentor for junior staff, as they prepare project deliverables.
• Contributes to database design reviews.
• Develops and maintains a security scheme for the database environments.
• Assists in disaster recovery planning, testing and execution as needed.
• Possesses strong understanding of the system deployment process and correlation with database administration responsibilities.
• Coordinates and conducts database design reviews.
• Possesses keen troubleshooting and creative problem solving skills.
• Possesses the ability to translate user needs and projections into system hardware and/or software requirements.

D. Basic Skills

• Adheres to Company standards and methodology.
• Adheres to company confidentiality and security requirements.
• Communicates effectively.
• Consistently demonstrates a high level of integrity and professionalism.

The Co-operative

Things are changing in the home offices of "So What?". Some of my guest bloggers expressed an interest in continuing to blog about IT goings on and I thought "Why Not?". We're going to concentrate more on IT stuff on So What and I'll leave the personal stuff over at Wilton Diaries. I present to you, the So What Co-operative.

Oracle Spatial & Wildebeests

Before I make an effort to Blog about Oracle Spatial, Rasters, and Shape files is there anyone who reads this Blog that would benefit from me sharing my experiences with it?

Don’t get me wrong, I’m not saying my time is valuable and I have better things to do with it. I’m just trying to get a feel for what interests you (the reader). I’m sure there are those of you who would much rather discuss the migration habits of the West African Wildebeest during the dry season or how histograms for join predicates only work if you stick your tongue out in the right place. Sorry Dave, you know I have to mess with you :)

So Often I read peoples blogs and it looks like they threw up a paragraph or two of gibberish just to take up virtual space and to make it look like there’s activity. I refuse to succumb to that. You take the time out of your day/evening to come and pay this place a visit the least thing that the contributors can do is write decent content that will make your time spent here either enjoyable or knowledge gained. Nuff Said?

What do you do all day?

The conversation started out "What do you do all day?"

I was talking to a fellow IT worker and was trying to explain my job function. I often get this question from non-computer people and I just respond "computers", but this required a more in-depth answer.

I started off with "Basically, I make sure all those databases that the company uses stay up and functional."

"Ah....But what does that mean?"

So I start explaining my day.

My day typically starts with resolving any non-critical problems that happen overnight. I don't have to worry about the critical problems, because they have already been resolved by the person on duty (which is me every other week). I might create a new schema for a user in Europe. Or I might try and find out why userX tried to login yesterday over 300 times using the wrong password. This type of stuff usually lasts from 15 minutes to an hour. In my group, we each have about the same amount of this type of work in the morning. In addition, we'll respond to these type of quickie tasks throughout the day, recording each one.

Then I spend about 15 minutes catching up on how my people are doing with their projects. Sometimes more, sometimes less, but I generally like to get a feel of how things are going before I start doing my heavy duty work.

Most of my day is spend doing what I call "project work". Project Work are those tasks that can't be done in less than a day. A project might be as short as a day, or may be as long as 18 months. An example of a project might be as complex as upgrading Oracle Applications to 11.5.10 or might be as simple as setting up a connection manager for a particular sub-net. I typically schedule my work so I can work on two projects at the same time (ie. while Oracle Applications is applying patch XYZ, I write code for my monitoring software).

Occasionally, I'll have to respond to a critical situation. I have monitoring software running all the time and when it encounters something that it thinks I should know about, the software sends us a message. If a process is running over X minutes, I get a message indicating that maybe I should investigate more. If the software encounters a condition that could potentially stop business, I get notified right away using a text message. If my backups fail at 02:00, I get notified. If the log_archive_dest gets over 90% full, I get notified. On average, I get about three after-hours messages a week when I'm on duty.

I don't worry about backups, they're automated. I don't worry about my alert.log, it's being monitored. I don't worry about the database being up, it's monitored.

The other person usually wakes up from their coma at that point and says "Oh."

Weathervanes indeed

You may recall Steve's entry about weathervane theft in New England. At these prices, I can understand why.

On top of the world

I’ve never climbed a mountain, but seeing a sunset at 14,000 feet is a breathtaking experience. We took the Mauna Kea (pronounced Mona Kaya) Sunset and Stargazing tour by Hawaii Forest and Trail one evening. They take you from your hotel (sea level, temperature 93) on a 90 minute ride to the summit of Mauna Kea (14,000 feet, 38 degrees) to watch the sunset. The view did not disappoint as an unobstructed sunset was one of the most incredible things I have ever seen.

Mauna Kea is home to some of the world’s most powerful observatories. The stargazing portion of the tour was a 90 minute talk on the constellations and how ancient Hawaiians used these stars to navigate. Then the guide pulled out an 8” telescope and let us see some of the stars up close. The view of Jupiter was striking and we saw details on the moon almost like it was coming from Google Maps.

I’ll let the pictures do the talking, but if you’re ever on the big island, you won’t be disappointed with this tour.

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.