Prototype - Week 3, part deaux

Isn't it? Oh. I need to extend for another week to do additional memory sizing tests?

IBM is going to love hearing that.

I am so exhausted. I have been working day and night trying to get as many of my planned tests done as possible. I've been working from home. I have faster access without going through the corporate firewall.

My VP emailed me yesterday to ask how the testing was going. I had to admit that most of the sizing tests would be complete but not the memory sizing. He said that wouldn't be good enough. I'm not complaining here; that's a fair enough response. Do you have any idea how much 32GB of ram for a 570 or 595 is from IBM? I do now. It's not pretty. And I'm currently running with 512GB. Ouch.

Well, let me start over. In my last blog, I mentioned that I ran into an Oracle bug with my plan A so I was switching to plan B. After re-reading that I realize I didn't explain it very well. Let me do that now.

First some definitions.

INSERT: The basic command to get data into a table:

INSERT INTO tab (col1, col2, col3) VALUES (1, 'A', 'XYZ');

INSERT APPEND (also Insert Select): The way to move data from one table to another as fast as possible:

For my project, I'll be getting a few million rows per day added to the base transaction data. I generate XML and was going to use merge to UPSERT the XML columns in my tables.

A combination of either the amount of data or the way I have it partitioned or something combined with the SQLX functions and the MERGE command is causing me to get an ORA-00600 generic Oracle error. Oracle support has not been able to track down the cause and wants me to do the debugging for them. I haven't had time yet.

That means I needed to come up with Plan B. Instead of UPSERTing, I truncate the tables (delete all of the data) and use INSERT APPEND. The SELECT clause generates the XML from the base transaction data.

I did some quick testing and did not get the ORA00600 so I rewrote my code to use INSERT APPEND instead. I got going with that pretty heavy by Tuesday.

I am gathering statistics for my runs at 62 CPUs, 32 CPUs, 16 CPUs and 8 CPUs. I'm tracking time required to generate the result sets along with CPU and IO loads. I'm almost finished with those tests. I'm completing the 16 CPUs while I'm writing this entry.

To give me time to do memory sizing, I talked to IBM and asked them to extend us for another week. Turns out that they have a client who needs the SHARK san that we're running on. We're getting great support from the people at IBM though and they've come up with a way to support this. So, I will be doing more tests next week.

The database will be down on Friday, and possibly the weekend, to give IBM and my system admins the time needed to set us up for next week. I need to get some paperwork done so this might be an opportunity. Or maybe I will debug Oracle's ORA-00600 for them. Sometimes Oracle's support really annoys me. Not as much as their stupid licensing practices but almost.

Once we get the optimal number of CPUs, we'll drop the memory until we can't run. Then we'll try adding in CPUs to see if the results get better. Then we'll bump up the memory a little at a time until we get the optimal CPU/Memory mix. That will give us our baseline and a scale factor for both CPUs and Memory. I need to provide data for pricing different performance levels.

In my initial testing so far, we are more IO bound than anything else. I can put some temporary pressure on the CPUs but it consistently comes down to how fast we can read and write data.

And that's where it stands right now. I'm going to wrap up the 16 CPU tests tonight and shut down the database so that the system admins can do their thing tomorrow. I'm really hoping to get the system back for the weekend. I want to avoid a week 5 at all costs. heh

If anyone knows exactly what causes, and/or how to fix, "enq: TM - Contention", "enq: TC - Contention" or "enq: PS - Contention" wait events, please post a comment and let me know. I added tablespaces and it seems to be a workaround for the PS and TC but I still get the TM and haven't been able to figure it out. I am using 32k block sizes but I have no foreign keys on the tables with the issues. I just can't seem to figure out exactly what the issue is.

My blog represents my thoughts and opinions and not my employers thoughts and opinions. If you want to know what my employers think, it is best to ask them directly. On the other hand, I pretty much say what I am thinking right here.

Disclaimer: Blog contents express the viewpoints of their independent authors and
are not reviewed for correctness or accuracy by
Toolbox for IT. Any opinions, comments, solutions or other commentary
expressed by blog authors are not endorsed or recommended by
Toolbox for IT
or any vendor. If you feel a blog entry is inappropriate,
click here to notify
Toolbox for IT.

Lewis Cunningham is an Oracle ACE Director, Oracle 11g Certified Professional (OCP), Oracle 11g Certified Data Warehouse Implementation Specialist, Database Architect and self-professed database geek. Lewis has almost 20 years of database experience. Follow along as he builds a working Oracle encyclopedia, sharing his knowledge and experiences, and describing ways to integrate that technology in various projects and business areas.
less

Receive the latest blog posts:

Share Your Perspective

Share your professional knowledge and experience with peers. Start a blog on Toolbox for IT today!