New to the "Top 5" list

A new entry to my all-time Top 5 list of bad advice I got from Oracle Support:

Oracle Support: You should distribute your log groups to multiple disks to avoid contention.
Me: But doesn't Oracle write to only one group at a time?
Oracle Support: No, we're multi-threaded.
Me: So you're telling me that Oracle writes to more than one redo log group at a time?
Oracle Support: Yes.
Me: Please close this TAR, I'll open another one.

Virus'

I've been battling the "Vundo" virus on a machine I was asked to work on for a family member. Somewhere between the 13th and 14th time scanning with Trend Micro's Internet Security, my mind began to wander.

Exactly what type of person writes a virus intent on inflicting aggravation on countless people? Are virus programmers the same people that cut you off in traffic? Are they the type of people that beat puppies?

I bet there are plenty of virus writers who think it's funny when a bus blows up.

I finally came to the conclusion that these people are lower than dog shit. I hope they can carry on a conversation with Saddam Hussein when they meet him.

Pick Two

There's a saying in the computer business, "Good, Fast, Cheap. Pick Two." This saying represents the trade offs we have to make every day on the front lines of IT.

Recently, we have started playing around with MySQL Enterprise Monitor (MEM). MEM is MySQL's offering for an event driven monitoring application similar to Oracle's Enterprise Manager. There are four pieces to MEM; the monitoring agent that gathers that db statistics, the query analyzer that allows you to "trace" queries, the connection proxy that allows you to redirect connections, and the data repository.

Our first stab at configuring MEM was putting the monitoring agent, query analyzer, and query proxy on the host being monitored with the repository db on a different machine. This actually worked pretty well until we had some big queries run through the query proxy. CPU utilization spiked because both the query proxy and the database had to handle the same set of data.

So MySQL Support suggested we move the proxy, query analyzer, and monitoring agent to a separate host. This "middle-tier" would handle the extra CPU load we were generating and keep our db host performance consistent. This solution worked very well, except we weren't getting host related statistics like CPU utilization on the DB host.

No problem, right? We moved the monitoring agent to the db host and kept the proxy and query analyzer on the middle-tier. Now we had our host related statistics, except we couldn't get the query analyzer to work because it has to have a monitoring agent present in order to work properly.

The only solution MySQL Support can offer us at this point is to have two agents monitoring the same database; one on the middle-tier host and one on the db host. OK, we'll do what needs to be done, but I have two fundamental problems with that configuration. First, my database background tells me that duplication will come back and bite you in the ass someday. My other very practical issue is now we are storing twice as much data in the repository for 10% more information.

So while the concept of MEM is great, the actual implementation is a compromise.

Note: Some of the above configurations are not currently supported by MySQL. Use at your own risk.

SGA Latch Contention

We had been getting a pretty persistent "WARNING: inbound connection timed out (ORA-3136)" error thrown to the alert.log on one of our 10.2.0.4 dbs. This error wasn't received once or twice, but about 40 times within a 2 minute window.

I did the research on it and found some sources that suggested my SQLNET.INBOUND_CONNECT_TIMEOUT needed to be adjusted upwards from the default value of 60 to 120 or something higher. I was pretty sure that wasn't the case as it was already set at 120. For the sake of progress, I bumped the timeout to 300 (5 minutes) and monitored closely.

About two days later, we received the same warning message in the alert.log file, except this time it happened about 90 times in a two minute period. Aha, a change. So that told me that something was going wrong during the authentication phase since increasing the connection timeout made the problem worse.

During the last occurrence, I also noticed that the mman process was consuming 100% of the CPU. At the time I wasn't sure if it was a symptom or a byproduct of the problem. However, as soon as the problem went away, the ora_mman process went back down to nearly 0%.

I setup a script to automatically do three systemstate dumps a minute apart when the mman process went to 100%. Oracle Support was able to tell me that during this time period, a bunch of my SQL had been invalidated as was waiting on a latch to be loaded back into the shared_pool. They also indicated that this massive amount of reparsing could happen because somebody did a DDL on a popular object or somebody flushed the shared pool.

I was pretty sure nobody flushed the shared pool, but that got me to thinking what would happen if Oracle shrank my shared pool due to the automatic memory management? I checked v$sga_resize_ops and found that around the times of my warning message, the shared_pool was being re-sized down in size. I brought this up to the support analyst and he suggested I set the shared_pool to the maximum size Oracle had re-sized it to.

That was three days ago, and we haven't had a warning since.

The theory is that while the shared_pool was being re-sized in the SGA, Oracle grabbed a latch. The memory resize operation took a while and while Oracle held that latch, nobody could login. Interesting theory, we'll see if it holds.

PC Support for Oracle

So I'm working with Oracle Support on an Oracle Applications R12 issue. They want to see some data from one of our tables and give me a query to run. I run the query in SQL Developer and export the results as a .csv file and upload it to the TAR.

Three days later they get back to me and say they can't open the files. Hmm, that's funny, let me try the same thing.

I load it into Open Office in about 2 seconds, no problem.

Then I load it into a database using SQL*Loader with no issues.

Same thing with MySQL and LOAD DATA, no issues.

I mail the file to myself and again it works no problem.

I send the file to a collegue and Open Office works for them as well.

The support analyst asks for a plain .xls worksheet so I try to load it into MS Excel. Bingo, I get an error:

This error is usually encountered when an attempt to open a file with more than 65,536 rows or 256 columns is made. Excel is limited to 65,536 rows of data and 256 columns per worksheet. You can have many worksheets with this number of rows and columns, but they are usually capable of fitting into one workbook (file). The number of worksheets you can have per workbook is limited only by the amount of available memory your system has. By default, Excel can manage 3 worksheets, more if there is available memory to support the quantity of data.

Truncation of rows or columns in excess of the limit is automatic and is not configurable. This issue can usually be remedied by opening the source file with a text editor, such as Microsoft Office Word, and then saving the file off into multiple files with row or column counts within the limits of an Excel worksheet. These files can then be opened or imported into Excel worksheets.

If you are using a data format that does not support use of a text editor, it may be easier to import the data into Microsoft Office Access and then use the export feature of Access to import the data to an Excel format. Other methods of importing large source material into multiple worksheets are available, but may be more complex than using either a text editor or Access.

Aha! MS Excel can't handle anything with over 256 columns of data and my data is 325 columns! (Not my design, it comes from Oracle Applications).

I post my findings to the TAR and suggest they load the data into a piece of software that can handle 325 columns, like maybe Oracle. (I didn't have the heart to suggest MySQL, but I guess they wouldn't take that as a slight anymore ...)

Oracle vs. SQL Server

information_schema.tables.table_rows

Here's something to be aware of. The table_rows column in the information_schema.tables table does not accurately reflect the number of rows in the actual table.

This weekend I was moving a db from one host to another. As a good DBA, I took the row counts from each table I was moving so I could check it on the other side. When the copy was done, I queried information_schema.tables again and dumped the results to a file. Running the two files through diff, I found that almost every table had a different number of rows.

I knew that couldn't be possible because I used a method where nobody had access to these tables when I was doing the copy.

On serverA, the information_schema reported that table1 had 2337 rows. On serverB, the information_schema reported that table1 had 2341 rows after the move. Doing a "SELECT count(*) FROM table1" yeilded 2343 rows on both serverA and serverB.

Some Helpful Drupal Resources

The best resource for understanding how Drupal works is actually the handbooks at Drupal.org.

Learn By The Drop has some very interesting videos about setting up Drupal and some other basic info to get your site up and working quickly. I browsed their screencast about setting up FCKEditor and was very impressed.

There are also some very interesting screencasts at DrupalTherapy.com. In fact, I used the Date + Calendar screencast to setup my own event content type.

Lastly, a blog aggregator called PlanetDrupal is also fun to keep up with.

What are some of your favorite Drupal resources?

SQL Developer Connecting to MySQL

We introduced Oracle SQL Developer to our developers about a year ago. They seem to like it for querying Oracle, but the MySQL connectivity was quirky in previous releases.

Now that Oracle SQL Developer 1.5.4 has been released, I figured I'd give it a try again. I downloaded the software and it started right away with no problem (although I wasn't paying attention and I forgot to migrate over my settings).

Once the software starts, go to Tools > Preferences > Database > Third Party JDBC Drivers and choose the appropriate JAR file.

Then I added a connection with the correct parameters and Voila! I was querying MySQL data from an Oracle tool!

My Oracle Support

Did everybody get the notice that the HTML version of MetaLink is going away?

If you've got a hot duo2 box and your IT Department allows you to install Flash, you've probably already been experimenting with the new version. I tried using it when it first came out but it was so slow on my 2.2Ghz Laptop with only 512Mb of RAM that I had to go back to "MetaLink Classic".

Well, now I have no choice now that my days are numbered. I've got to figure out how to tell my IT group that all my people need new computers because Oracle upgraded their website. Maybe the government can bail us out?

Point & Click DBAs

Maybe I'm old school.

Maybe I'm just behind the times.

Today's crop of DBA candidates sure know how to point and click their way through Oracle. When I ask them how to tune a SQL statement they'll give me the exact instructions on how to click on some Grid Control adviser or how to get an "explain plan" through TOAD. I guess the new query plan is called "explain plan" now because it's easier to understand.

Recently I asked one candidate, "What if grid control goes away, how do you tune a query?"

"Well, first you start the grid control agent, and..."

"No, no, I mean, if there was no such thing as grid control, how would you tune a query?"

Crickets....

"Um, SQL*Plus?"

Yes, good old SQL*Plus.

I used to believe that GUIs are icing on the cake and as long as you know what was going on under the covers, it was perfectly acceptable to use a GUI.

Maybe I'm just old school.

Vista VPN Vacillation

I tried setting up the NCO Secure Entry Client on Vista x64, but just couldn't get it to work. Before I found that VPN Software, I contacted Dell and they sent me out a Vista x32 installation CD that I could use to re-install my OS if it came to that. I messed around with x64 and NCO Secure Entry Client until the install disk came and then I punted. The Vista install took about an hour or so and the VPN setup took another five minutes to setup and test. It came up fine the very first time.

Maybe Cisco will get with the program by the time I'm ready to get a new machine. I wonder if that extra 2G of RAM will fit in my W2K box...

When the Latest and Greatest isn't the Greatest

I thought my Windows 2000 computer was starting to show it's age. 512M of RAM and a 200G hard drive just doesn't go as far as it used to. I setup a Vista laptop a few months ago and it's working pretty good, so figured I'd take the plunge and get 6GB of RAM and get Vista x64 to take full advantage of it. Everything was going fine (with the normal Vista quirks, of course) until I tried to install the Cisco VPN client. Seems as though there is no support for Vista64 and my particular VPN software.

Dandy.

I found NCO Secure Entry Client and will try to set it up, but we'll see...

Adobe Automation

I have a friend who needs some Adobe Acrobat automation. The project description is:

We need to find a programmer who is willing to do some high-end Adobe form work for us.

We need to embed a "send to" button in some of our company forms so we can email the form to clients who can input the data and submit the form back to us by clicking on a submit button on the form.

If anybody has this skill set and is interested, please email me at marist89 (at) excite %dot% com and I will forward to the appropriate party.

Did I get my money worth?

At the end of November I submitted a Purchase Order for my 2009 Annual Oracle Support. I passed the six figure mark several years ago and am well on my way to topping the next hundred next year. Here is my report from Metalink of the number of incidences and close times for the year:

2. Close Rates (closed calls only)

Time	Incidents	%	Cumulative Total
4 h	1	1.41	1.41
12 h	1	1.41	2.82
24 h	0	0	2.82
48 h	3	4.23	7.05
72 h	1	1.41	8.46
1 Week	2	2.82	11.28
2 Weeks	3	4.23	15.51
4 Weeks	40	56.34	71.85
>4 Weeks	20	28.17	100.02
Total Incidents	71

That doesn't count the 5 TARs I still have open; one from May. 84% of my TARs are closed in four weeks or more. Let me say that again; FOUR WEEKS OR MORE. Each TAR cost me approximately $2500.

So, Oracle Community, did I get adequate return on my investment?

(Oh, and BTW, I love how my cumulative total adds up to 100.02%)

The Bug, part II

AH HA! you guys took the bait!

Granted, this is a bug in the particular JDBC Driver I am using. The 10.2.0.2 driver straight out of the box has bug 4711863 that can possibly insert bad data when you use Java 1.5. In fact, I proved it with a scenario similar to:

drop table tablea
/
drop table tableb
/
create table tablea (
record_id number(38) primary key,
price number)
/
create table tableb (
record_id number(38),
price number)
/
-- fill it
begin
for i in 1..100 loop
 insert into tablea values (i, 0+(i*.0000001));
 commit;
end loop;
end;
/

quit

Then, my java program sucks data from tableA and inserts it into tableB. Inside my java program, I convert a Double to BigDecimal using code like:

String insertString = "insert into tableB values (?, ?)";
PreparedStatement stmt2 = this.dbConnection.prepareCall(insertString);
stmt2.setInt(1,rs.getInt("RECORD_ID"));
stmt2.setBigDecimal(2, new BigDecimal(rs.getDouble("PRICE")));

I query the data from tableB and boy am I surprised:

SQL> select * from tableb where price <> price*1;

      RECORD_ID                PRICE
-------------------- --------------------
              2          .00000019RS
              3          .00000029RS
              4          .00000039RS
              5          .00000049RS
              6          .00000059RS
              7          .00000069RS
              8          .00000079RS
              9          .00000089RS
             10          .00000099RS

So maybe it's a sqlplus setting or something. I try:

SQL> set numwidth 20
SQL> /

      RECORD_ID                PRICE
-------------------- --------------------
              2          .00000019RS
              3          .00000029RS
              4          .00000039RS
              5          .00000049RS
              6          .00000059RS
              7          .00000069RS
              8          .00000079RS
              9          .00000089RS
             10          .00000099RS

Same deal.

I go back and verify my source data from tableA, and sure enough it's correct:

SQL> select * from tablea where record_id = 2;

 RECORD_ID           PRICE
--------------- ---------------
         2        .0000002

SQL> l
  1* select record_id, price, dump(price) dmp from tableb where price <> price * 1
SQL> /

RECORD_ID                PRICE DMP
--------- -------------------- ----------------------------------------
        2          .00000019RS Typ=2 Len=21: 189,20,246,100,100,100,100
                               ,100,100,100,10,50,63,24,66,18,73,52,74,
                               72,23

        3          .00000029RS Typ=2 Len=21: 189,30,246,100,100,100,100
                               ,100,100,99,65,25,44,36,48,77,59,78,61,5
                               7,85

        4          .00000039RS Typ=2 Len=21: 189,40,246,100,100,100,100
                               ,100,100,99,19,100,25,48,31,36,46,4,48,4
                               3,46

        5          .00000049RS Typ=2 Len=21: 189,50,246,100,100,100,100
                               ,100,100,98,74,75,6,60,13,95,32,30,35,29
                               ,7

        6          .00000059RS Typ=2 Len=21: 189,60,246,100,100,100,100
                               ,100,100,98,29,49,87,71,96,54,18,56,22,1
                               4,69

        7          .00000069RS Typ=2 Len=21: 189,70,246,100,100,100,100
                               ,100,100,97,84,24,68,83,79,13,4,82,8,100
                               ,30

        8          .00000079RS Typ=2 Len=21: 189,80,246,100,100,100,100
                               ,100,100,97,38,99,49,95,61,71,91,7,95,85
                               ,92

        9          .00000089RS Typ=2 Len=21: 189,90,246,100,100,100,100
                               ,100,100,96,93,74,31,7,44,30,77,33,82,71
                               ,53

       10          .00000099RS Typ=2 Len=21: 189,100,246,100,100,100,10
                               0,100,100,96,48,49,12,19,26,89,63,59,69,
                               57,14

OK, so JDBC isn't 100% correct when you cast between BigDecimal and Double. Big whoop. And in fact, the fix for the aforementioned bug is fixed if you apply the appropriate patch.

My problem is in the fact that Oracle (the database software) let a bad piece of data in. Oracle says the client should type the data when binding. I agree. But I also think that the server shouldn't let non-numeric data into numeric columns, regardless. I contend that this is a bug on the server side as well as a bug on the client side. This is only one specific case of a Java program inserting bad data. I have some tables that have non-numeric characters that were never touched by Java.

Where is the bug?

So, if I have a table that has a column defined as NUMBER(10) and try to insert the value of 'ABCDEFG' in it, I get an "ORA-01858: a non-numeric character was found where a numeric was expected" error. Fair enough, I expect that the database does type checking and won't allow an invalid number in the field.

In addition, if I try to insert an 11 digit number, I get an "ORA-01438: value larger than specified precision allowed for this column" error.

So far so good.

If an invalid number, such as "-0" or "17~:49" got into the database via JDBC, would it be a client problem, a server problem, or both?

11g

I was asked the other day when I was going to start writing about 11g.

Quite frankly, I literally just got everything off 9i last weekend. The final thorn in my side of Oracle Applications was started at the close of business on Friday and lingered on to the wee hours of Saturday morning. It took about 18 months and my standard install of 10g has 23 patches applied, but finally I have a consistent environment on 10g.

And lets face it, 10g is a pretty darn good product once it's patched. The combination of 10g and the Linux 2.6 kernel is really a rock solid platform with a laundry list of features that you'll probably never need.

I'm keeping abreast of what's new in 11g and I haven't really seen anything compelling that piques my curiosity enough to even install it. Maybe my opinion will change when the calendar flips over and we have some of that mythical "free" time, but for now, I'll be sticking to 10g topics.

Phone Interview tips for DBAs

You only get one chance to make a first impression. I would think the following tips would be common sense, yet I am surprised every day.

1. When on a phone interview, don't put the interviewer on hold.
2. I don't mind calling you on your cell, but if the call drops, you're done.
3. I know you might be nervous, but don't cut me off in mid-sentence.
4. When you take my call, don't be driving in a car.
5. I don't expect you to pick up the phone on the first ring, but please pick it up. If I get your Voicemail, I'll try you again in five minutes. After that, you're on your own.

Database Administrator

Job Description
Interactive Brokers Group, industry leader in direct access brokerage, is seeking a motivated Database Administrator to provide day-to-day production support for our highly automated mission critical systems. This position requires interacting with very technical users, big-picture analysis, and acute problem solving skills.


Required Skills

• 3+ years experience as Oracle DBA in a production environment.
• Linux and/or Solaris
• Day-to-day Database Administration.
• Backup and Recovery using Recovery Manager.
• Oracle 10g (at least 2 years)
• Ability to quickly diagnose and resolve performance using standard tools. (explain plan, OWI, Profiler, Statspack)
• Some experience with partitioning
• Self-starter with initiative and desire to learn
• Ability to work quickly in a fast-paced environment.
• Rotating on-duty coverage is expected.
• Ability to communicate clearly, constructively and concisely to peers and end-users.
• Ability to work independently with minimal supervision and drive issues to closure.

Desired Skills

• Experience with Oracle Applications R12
• Automation of DBA tasks (Perl, CGI, Ksh)
• Some experience in 11g
• DR Strategies including Standby Database
• Financial Industry Background
• Oracle Connection Manager

Job Code: JH-DA-CT

Interactive Brokers Group LLC

Apply Now