Setting up Multiple Listeners

Somebody had a problem in one of the forums with connections timing out. I suggested the error might be due to the listener being really busy and the poster could setup multiple listeners and load balance between the two. The setup was complex enough that I wanted enough room to explain the whole thing.

Setting up two listeners on the same box is done via the listener.ora file. The listener.ora file is usually located in your $ORACLE_HOME/network/admin directory. I have setup two listeners on my test box and the listener.ora file looks like this:

LSNR1666 =
(DESCRIPTION_LIST =
(DESCRIPTION =
 (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = jake)(PORT = 1666))
 )
)
)

LSNR2666 =
(DESCRIPTION_LIST =
(DESCRIPTION =
 (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = jake)(PORT = 2666))
 )
)
)

SID_LIST_LSNR1666 =
(SID_LIST =
(SID_DESC =
 (GLOBAL_DBNAME = lx10r2.us)
 (ORACLE_HOME = /u01/app/oracle/product/10.2.0.1)
 (SID_NAME = lx10r2)
)
)

SID_LIST_LSNR2666 =
(SID_LIST =
(SID_DESC =
 (GLOBAL_DBNAME = lx10r2.us)
 (ORACLE_HOME = /u01/app/oracle/product/10.2.0.1)
 (SID_NAME = lx10r2)
)
)

This part of the listener.ora tells me my listener name is LSNR1666 and that it is listening on port 1666 of hostname "jake" and I'll be using the TCP protocol.

LSNR1666 =
(DESCRIPTION_LIST =
(DESCRIPTION =
 (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = jake)(PORT = 1666))
 )
)
)

This part of the listener.ora tells me that LSNR1666 will be listening for the DB named lx10r2.us whose ORACLE_HOME is /u01/app/oracle/product/10.2.0.1 and whose ORACLE_SID is lx10r2. (This could have been specified with a SERVICE_NAME also, but I chose to do it this way because it is simpler).

SID_LIST_LSNR1666 =
(SID_LIST =
(SID_DESC =
 (GLOBAL_DBNAME = lx10r2.us)
 (ORACLE_HOME = /u01/app/oracle/product/10.2.0.1)
 (SID_NAME = lx10r2)
)
)

That's it, now you just start the listeners by name:

[oracle@jake admin]$ lsnrctl start lsnr1666

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 15-OCT-2005 08:52:23

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/10.2.0.1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/10.2.0.1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0.1/network/log/lsnr1666.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jake)(PORT=1666)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=jake)(PORT=1666)))
STATUS of the LISTENER
------------------------
Alias                     lsnr1666
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                15-OCT-2005 08:52:23
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/10.2.0.1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0.1/network/log/lsnr1666.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jake)(PORT=1666)))
Services Summary...
Service "lx10r2.us" has 1 instance(s).
Instance "lx10r2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@jake admin]$ lsnrctl start lsnr2666

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 15-OCT-2005 08:52:30

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/10.2.0.1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/10.2.0.1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0.1/network/log/lsnr2666.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jake)(PORT=2666)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=jake)(PORT=2666)))
STATUS of the LISTENER
------------------------
Alias                     lsnr2666
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                15-OCT-2005 08:52:30
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/10.2.0.1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0.1/network/log/lsnr2666.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jake)(PORT=2666)))
Services Summary...
Service "lx10r2.us" has 1 instance(s).
Instance "lx10r2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

We can see that the listeners are started and waiting for connections:

[oracle@jake admin]$ tail $ORACLE_HOME/network/log/lsnr1666.log
Trace information written to /u01/app/oracle/product/10.2.0.1/network/trace/lsnr1666.trc
Trace level is currently 0

Started with pid=3201
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jake)(PORT=1666)))
Listener completed notification to CRS on start

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
WARNING: Subscription for node down event still pending
15-OCT-2005 08:52:23 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=jake)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=lsnr1666)(VERSION=169869568)) * status * 0
[oracle@jake admin]$ tail $ORACLE_HOME/network/log/lsnr2666.log
Trace information written to /u01/app/oracle/product/10.2.0.1/network/trace/lsnr2666.trc
Trace level is currently 0

Started with pid=3211
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jake)(PORT=2666)))
Listener completed notification to CRS on start

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
WARNING: Subscription for node down event still pending
15-OCT-2005 08:52:30 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=jake)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=lsnr2666)(VERSION=169869568)) * status * 0

Next, we configure the client. The client naming is usually controlled by a file called tnsnames.ora in the $ORACLE_HOME/network/admin directory of the client. My tnsnames.ora file looks like this:

D:\oracle\product\10.2.0\NETWORK\ADMIN>type tnsnames.ora
# tnsnames.ora Network Configuration File: D:\oracle\product\10.2.0\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

LX10R2.US =
(DESCRIPTION =
  (ADDRESS_LIST=
     (LOAD_BALANCE=on)
     (ADDRESS = (PROTOCOL = TCP)(HOST = jake)(PORT = 1666))
     (ADDRESS = (PROTOCOL = TCP)(HOST = jake)(PORT = 2666))
  )
  (CONNECT_DATA = (SID = lx10r2))
)

Here, the database alias is "lx10r2.us". Normally, when you specify multiple address lines for an alias, Oracle will attempt the first and if that fails will attempt the second. This might actually work for the poster, but would still pound port 1666 until it couldn't handle any connections and THEN try port 2666. The key to having a somewhat even distribution over the two listeners is by using the (LOAD_BALANCE=ON) parameter.

Just to test the theory, we create a small batch file on the client machine that connects and disconnects in an infinite loop. The batch file looks like this:

C:\temp>more infinite.bat
:start
sqlplus jeffh/jeffh@lx10r2.us @disconnect
goto :start

C:\temp>more disconnect.sql
exit

Then, we open up multiple command prompt windows and start pounding away. You can see the listener.log file keeps growing and both listeners are accepting approximately the same number of connections:

[oracle@jake log]$ wc -l *.log; sleep 120; wc -l *.log
   206 lsnr1666.log
   199 lsnr2666.log
   405 total
   528 lsnr1666.log
   582 lsnr2666.log
  1110 total

Nice. About three hundred connections per minute. Not bad for an 802.11b connection to a 733Mhz Linux box connected to a 100Mbps router. Will this solve the poster's problem? I don't know. I do know this is how you can setup multiple listeners, though.

Using MySQL more frequently

As I use MySQL more frequently, I really get perturbed when I get back on to Oracle and can't do:

SELECT someFunctionName();

Thanks Howard, Part II

I use statcounter to keep track of my blog's visitors. No, I don't use it for ads or anything like that, but still, I like to know who is reading the blog and how they got here.

A couple of weeks ago, Howard J. Rogers added my blog to the links section of The Dizwell Blog and I started getting a lot of referring links. Sometimes when I post an entry about MySQL, I'll get a spike from Planet MySQL and other related sites. Over the last few days, The Dizwell Blog has taken the top spot and I'm starting to get referrals from other sites I never knew about. In fact, I've found a couple blogs that are interesting that I'll be modifying my own links section in the near future to replace some of the blogs that don't get updated on a regular basis.

So, thanks to Howard and others to helping me get recognized and thanks to the readers. Keep on blogging.

This is the second time HJR has helped me out in the last couple weeks.

Wicked ORA-27054, Part I

We're doing some testing with RMAN and 10.2.0.1 on Linux. Our standard backup strategy is to backup to NFS mounted disk and backup to tape at a later time. With 9.2 I could mount the NFS filesystems without any particular options and RMAN would run just fine.

On 10.2.0.1, we setup the backup and immediately got an error (ORA-27054: NFS file system where the file is created or resides is not mounted with correct options). So we submit a TAR and find out that 10.2 requires NFS filesystems to be mounted with the following options:

rsize=32768,wsize=32768,hard,noac

The backup works now, except it takes 1 hour 9 minutes to backup a 2G database. We indicate this in the TAR and the analyst basically says "new problem, new tar". So we create a new TAR for the performance issues, but I post a followup on the existing TAR:

Me: Is there any flag or something that we can change to turn off the checking that results in the ORA-27054?
OCS: I have seen the following being used on different problems (not related to RMAN), sometimes it works, sometimes it doesn't, I would not be able to explain why since it is not on my skills, also this parameter is not documented so I would not know what other effects could cause.

Set init.ora parameter:
_filesystemio_options=directio

You can also try:
_filesystemio_options=none

Hope this helps.

Well, at least he's being honest about it. Of course, we all know that filesystemio_options is not hidden in 10.2.

Me: This is a production system. We'd rather not "try" something if we don't know if it will solve our problem or not. Since you say you're not qualified, how do we get to someone who can tell us the answer?

OCS: Create a new tar.

OK, I get it. You're just trying to close as many TARs as possible. Glad to see my support dollars "at work".

So we pursue our "performance" problem with the other TAR and reference the first TAR. The analyst suggests we contact the vendor of our NAS to find out what they suggest. We explain this is plain NFS and it doesn't really have a vendor. They suggest getting an strace on the rman process. Now we're getting somewhere.

While they are looking at the TAR, one of my DBAs does some more testing. He backs up my 2G database to local disk in 4 minutes. He then copies the backup pieces to the NFS mounted disk mounted with the options specified by Oracle Support. 1 hour 9 minutes. Ah ha! Poor performance even with plain old cp.

I know what Oracle Support is going to say. I'd probably say it myself. "The fact that plain old cp is slow tells me your NFS is setup wrong." And I wouldn't disagree. The conundrum is that these are the options Oracle told us to use...

to be continued...solution

Lions and zookeepers

A friend from geekster.com pointed out this article about MySQL and Oracle.

What I think about MySQL

After my series on MySQL to an Oracle DBA (here, here, here, here, and here), a lot of people have asked what I think about MySQL.

I'm not really qualified to compare the two. I think I know Oracle much better than I know MySQL, so the comparison would be jaded. Some people claim that MySQL is much easier to setup and maintain than Oracle. I'm not completely convinced of that. Each has their own set of hundreds of parameters that can be tweaked. Having installed MySQL from source a couple times, I'd actually say it's a little harder to setup in certain circumstances. Not hard, just harder.

I'm very intrigued by MySQL's backup methods. At first glance mysqldump looks more like Oracle's export program than a backup tool. An Oracle DBA would scoff this off as single point in time snapshot of the data. Of course, we Oracle DBA's know you can't import and then apply archived redo logs, but imagine a scenario where you could. Appearantly MySQL has the ablity to import and re-apply the bin log (kind of like the redo log). This concept was tossed about in the class and I'm anxious about trying it out.

I'm a little concerned about connection scalability with MySQL. The MySQL server is bound to one port. In the coming months I'll be stress testing high frequency connections to see of they scale.

I think Oracle gives you much more information about the health of the database through views than MySQL does. With Oracle, I can query v$this, x$that, and dba_whatever to tell me certain things about how the database is performing. In addition, I can write tools using these queries to automate my job and alert me to when certain conditions arise. MySQL has a lot of SHOW commands, but it's difficult to get this information in an automated fashion without some pipes, grep, and awk. Maybe I just don't know enough about it yet.

One thing I really like about MySQL is the concept of a "slow log". Here, queries that run longer than a specified threshold are logged to a "slow log" that can be investigated later. My developers will hate me. I wonder if it's to obnoxious to set my threshold at 60 seconds. Hmmm...

We'll definitely be using MySQL. It will start off with backoffice apps and progress to bigger apps. I've got half a mind to port my Oracle monitoring app to MySQL just for kicks. I'll be updating this blog as I find interesting things about MySQL.

Sysadmin at work

For you sysadmin types out there, this is not such a big accomplishment, but for me it is. I salvaged a drive from one of my older systems that I was throwing out. I decided to add it to my Linux box because I wanted to separate my home directories from my OS files.

This particular drive was a 800M IDE drive that I slapped into the box and the machine booted right away. Now, I'm not a hardware guy, so the fact that this worked the first time was pretty amazing to me.

Next, I knew I had to partition the disk and cook a filesystem, but didn't know the Linux commands to do it. I went to the Red Hat docs and managed to partition my disk and cook an ext3 filesystem. On Solaris, I'd just put an entry in /etc/vfstab and mount the filesystem. But there's no vfstab file on Linux. I did some more reading and found I need to put my entry in /etc/fstab. Although the format of the fstab file is different then I'm used to, I put the entries in like I think they should have been and tried to mount. Still can't mount.

Finally, I realize that my new disk has to have a label. I go through the same process except put a label on the disk this time. I use the new syntax in the /etc/fstab file and finally the filesystem mounts. I reboot the box and the filesystem mounts automatically. Basically a two hour process for something my sysadmins could have done in about 5 minutes. The joy of learning.

New Toy

Our old VCR finally died a couple days ago. We've had that thing for about 7 years now and use it pretty extensively to tape shows and watch them later. The quality of the recordings has gotten pretty poor over the last 6 months or so. In fact, we made the decision to junk it only when the top half of the picture didn't record and we had to have to volume all the way up to hear it.

We went through the process of looking at new VCRs, but decided a DVD recorder might make more sense. We looked at Tivo, but didn't want to be saddled with the additional cost. So we asked my sister-in-law; a buyer in the consumer electronics field. She suggested we look at a DVD recorder with a hard drive. (Hard drive, I'm into that!) We could record up to 100 hours on the drive and burn to DVD when we needed to. Our particular DVD recorder has on-screen listings that get updated automatically. I am ashamed to say that my DVD recorder has more disk space than my Linux box.

It was really a snap to hook up to the TV. After 24 hours, the listings downloaded to the unit and we setup all our shows to record. I'll see tonight if American Chopper recorded sucessfully Monday. Now if I can figure out how to NFS mount that bad boy to my Linux box, I'd be all set...

Problem Solved

Thanks to a tip by HJR, my .pdf problems have been solved. I've downloaded Foxit Reader and installed it on all my Windoz machines to take the place of Acrobat Reader 7.0. All my .pdf files now display correctly and print correctly. Thanks Howard.

Book Bonanza

I went a little crazy at Amazon.com last weekend.

I ordered these books Saturday and they all came in today. Where to start?

How many?

If you've ever seen...

There are now 76772 jobs remaining (current phase=A31):
   12 running, 14235 ready to run and 62525 waiting.

... you know you're in for a looooong weekend.

My Last Straw

I've had it with Adobe Acrobat Reader 7.0. Whenever I encounter a .pdf file that has both graphics and text on it, I can't seem to print it. No matter what I try to do, I just can't print the whole thing.

I recently upgraded to Acrobat Reader 7.0 on all my machines. My wife's computer is a W2K box that has the Brother HL-1440 printer attached to it. My laptop runs XP and I can normally print to the Brother with no problems over the network. My main box is a RH9 box that also prints to the same printer via the network.

I received an invoice from MySQL AB for the training I attended last week. I viewed it fine with Acrobat Reader 7.0 and then clicked "Print". Only the logos and lines on the document came out. So I tried a couple of the different "Comments and Forms" options in the Print dialog. Same thing. I figured my printer drivers might be out of date, so I updated them and tried again. Boxes and graphics only. Then I un-installed and re-installed Reader 7.0 with the most current updates. Same thing.

Next, I brought the document up on my laptop which also has Acrobat Reader 7.0 and tried printing to the network printer. Same deal. Then I tried printing the same file from my Linux box with Reader 7.0 with exactly the same result. Just for kicks, I brought up the file in xpdf and printed it, and voila, it prints fine. That ruled out the printer.

I thought maybe it was just the document I was trying to print. So I created a document with lines, graphics and text in Open Office and saved it as a .pdf. I tried it on all three computers; Reader 7.0 couldn't print, xpdf could.

Maybe it is the printer after all. I brought the same two documents to work and tried to print on an HP 8150. Still only those damn boxes and lines. Then I figured I'll downgrade to Reader 6, but I couldn't find it on Adobe's site.

My next step is seeing if there is a xpdf version for Windows.

New York Reflections

I guess you could call me a frequent visitor to New York. I’m in the city at least once a month for a user group meeting, training, or just as a plain old tourist. This week I’ve been one of the “commuter bots” that wake up at ungodly times, rush to the train station with their Starbucks Venti Moca something or another Latte and promptly go back to sleep for 72 minutes. I don’t know how these people do it every day, I couldn’t.

The training facility for this particular course is in lower Manhattan. I’m pretty comfortable with the subway system in New York, so I had a good idea which train to catch and that walking 3 or 4 blocks wouldn’t kill me. While walking around at lunch time one day, I strolled through Battery Park and came upon “The Sphere”. The Sphere was located at the World Trade Center four years ago when evil came to my backyard. This giant, scarred orb remains in this now vibrant community as a reminder that although time marches on, those who came before us will never be forgotten.

Next to “The Sphere” were impromptu tributes to those that died on that fateful day. A boy scout troop from somewhere in the Midwest had left a plaque. Somebody left a flower. There was a picture of somebody that sacrificed their life on that day to save others. The whole experience was overwhelming, more so, in my opinion, than seeing the empty spot where the WTC stood.

Walking the streets of New York on the weekend is different than during business hours. On the weekend, people are casually walking along drinking their $6 cup of coffee chatting about the weeks events and letting their dogs sniff every hydrant along the way. Rush hour turns these same people into machines silently screaming "GET OUT OF MY WAY!!!" with their eyes and expressionless face. On the weekend you can look lost and some Golden Retreiver walker will ask you where you are going and suggest you go up 41st Street instead of 42nd Street to avoid the crowds. On the weekday you have to ask three people which way to go and you'll be lucky if you get a point in the right direction. It's not that they're rude, they just have some place to be and the 5 train was delayed and they had to change to a local train at 14th street and ...

There's a lot of great things about New York, but I'm sure it's tough living there. If I could live and work North of City Hall, I think getting around would be pretty easy. You see, the Streets go East/West and the Avenues go North/South. The street numbers go up as you go North, the avenue numbers go up as you go West. Usually within a block you can tell if you are going in the wrong direction or not. Lower Manhattan is a differnt story. It's like another political party suddenly took control of the Street Naming Division and decided to start giving streets actual names instead of numbers. And forget about any grid of North/South and East/West.

I don't know if I am cut out for living in Manhattan. Sure it would be fun. For a while. I think I'd miss cutting the grass.

MySQL to an Oracle DBA, Part IV

Today is where the rubber meets the road in the Oracle/MySQL comparison; locking and transactions. At the end, I'll give you a little Oracle fun.

As I said yesterday, the different storage engines may or may not support transactions. MyISAM tables do not support transactions while InnoDB tables do. First, I create two tables; one as InnoDB, one as MyISAM:

system@localhost:world> create table city_myisam 
   engine = myisam as select * from city;
Query OK, 4063 rows affected (0.78 sec)
Records: 4063  Duplicates: 0  Warnings: 0

system@localhost:world> create table city_innodb 
    engine = innodb as select * from city;
Query OK, 4063 rows affected (0.53 sec)
Records: 4063  Duplicates: 0  Warnings: 0

Next, I set autocommit off.

system@localhost:world> set autocommit = 0;
Query OK, 0 rows affected (0.06 sec)

Now I'll delete some rows from the tables in a transaction and see what happens:

system@localhost:world> begin;
Query OK, 0 rows affected (0.00 sec)

system@localhost:world> delete from city_myisam 
   where country = 'FRA';
Query OK, 40 rows affected (0.11 sec)

system@localhost:world> delete from city_innodb 
   where country = 'FRA';
Query OK, 40 rows affected (0.09 sec)

system@localhost:world> rollback;
Query OK, 0 rows affected, 1 warning (0.05 sec)

system@localhost:world> show warnings;
+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                       |
+---------+------+---------------------------------------------------------------+
| Warning | 1196 | Some non-transactional changed tables couldn't be rolled back |
+---------+------+---------------------------------------------------------------+
1 row in set (0.00 sec)

system@localhost:world> select count(*) from city_innodb 
   where country = 'FRA';
+----------+
| count(*) |
+----------+
|       40 |
+----------+
1 row in set (0.08 sec)

system@localhost:world> select count(*) from city_myisam 
   where country = 'FRA';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.27 sec)

Aha, MySQL gives me a warning, but does it anyway. Note to self: no money transactions in MyISAM tables.

The next interesting thing was locking. The MyISAM storage engine only uses table level locks. When you update a row in a MyISAM table, you have to get an exclusive table level lock before you can update. These table level locks severly limit MyISAM scalability in a write intensive envrionment. The InnoDB engine, on the other hand, has row level locks, like Oracle.

The biggest Oracle misstatement today: Oracle will escalate to a table level lock when you delete more than 25% of the rows. Nope. In fact, I have a table with 5000 rows where the pk ranges from 1 to 5000:

SQL> l
  1  select count(*),
  2     min(x),
  3     max(x)
  4* from xyz
SQL> /

  COUNT(*)     MIN(X)     MAX(X)
---------- ---------- ----------
      5000          1       5000

In one session, I delete the first 4999 rows:

SQL> delete from xyz where x < 5000;

4999 rows deleted.

No problem. Now, from a different session I try to delete the row where x=4000.

SQL> delete from xyz where x=4000;

And, as expected, we are waiting on a lock from the first session:

SQL> l
  1  select waiting_Session wt, holding_session ho, lock_type lt,
  2     mode_held, mode_requested, lock_id1, lock_id2
  3* from dba_waiters
SQL> /

  WT   HO LT           MODE_HELD  MODE_REQUE LOCK_ID1 LOCK_ID2
---- ---- ------------ ---------- ---------- -------- --------
  24   25 Transaction  Exclusive  Exclusive    262148      163

But, if we try to delete the row that is not locked, it should go through with no issues.

SQL> delete from xyz where x=5000;

1 row deleted.

No problem, with no locks:

SQL> select waiting_Session wt, holding_session ho, lock_type lt,
  2     mode_held, mode_requested, lock_id1, lock_id2
  3  from dba_waiters
  4  /

no rows selected

MySQL to an Oracle DBA, Part III

Today is where we started digging our DBA teeth into MySQL. As with any database, the first thing you must do is install the software. MySQL comes in a couple different flavors depending on the platform and how you intend to use it. For example, on Linux you can install via RPM's, precompiled binaries, or source. On Windows you can install via a Windoz Installer, precompiled binaries, or source. While the quickest way to install is via RPMs or Windows Installer, you can build your mysql software tailored specifically for your hardware and the options you want to use. I chose to install from Windows Installer on Windows XP so I could work with my own laptop.

Once installed, you have to go through the configuration process. A lot of the configuration process is similar to Oracle; data goes here, logfiles (transaction logs) go on a seperate filesystem, the message logs go another place, etc. Similar to Oracle, you setup memory parameters and other features you want installed in the instance.

One interesting feature of MySQL is that you can store your tables using different "Storage Engines". A storage engine is basically the method used to access your data. You can use any number of storage engines in your database. The main storage engines are MyISAM, InnoDB, Memory, Merge, Berkley, and NDBCluster.

Why would you want to choose your storage engine? You can assign the storage engine based on what type of activity the table will have and the features you need.

Storage Engine	Advantages	Disadvantage
MyISAM	Extremely fast for Queries	Can't use transactions with, Concurrent INSERTs don't scale because the entire table is locked,Dirty Reads
InnoDB	Can use transactions, multi-versioning read consistency, DML scales well	Queries slower than MyISAM
Memory	Tables stored directly in memory. Access is really fast. Best for TEMP type tables	Tables stored directly in memory. DB Goes down, your data is toast.
Merge	Can present two identical tables as one table. Kind of like a materialized view, kind of like a partitioned table	Must be MyISAM tables
Berkley	Offers transactions.	Older technology
NDBCluster	Supports transactions and clusters, highly scalable	Not widely used.
Federated	Can store data on a seperate server	your I/O is limited by bandwidth

Last, but not least, we talked about security. We went through the same things you would do to secure an Oracle database; protect the root OS user, protect the mysql "root" (or SYS) user, use strong passwords, protect remote root logins, etc. One interesting concept is that MySQL users are identified not only by a username, but by the host they can login from. For example, jeffh@localhost is a totally different identity than jeffh@webserver. You grant permissions to both user depending on how you want to want them to access your data. If you want the root user to only login on the server itself, you only create root@localhost. This lets you setup access rules such as "When user jeffh is logged into the database on an internal domain, he can INSERT, UPDATE, DELETE, SELECT from my tables. When user jeffh is logged in from the VPN, he can only SELECT from my tables". That's kind of cool.

Two side notes: I leared about google sets and gvim (vi for windows).

MySQL to an Oracle DBA, Part II

Found out some things that you need to be aware of when converting from Oracle to MySQL. Let me create a table and show you some examples.

mysql> create table number_stuff (
    ->    description varchar(20) not null,
    ->    ti tinyint,
    ->    ival integer,
    ->    fl   float,
    ->    dc   decimal(15,5));
Query OK, 0 rows affected (0.13 sec)

This is kind of cool. You can insert multiple values in one shot.

mysql> insert into number_stuff values
    ->    ('123',123, 123, 123, 123),
    ->    ('384',384, 384, 384, 384),
    ->    ('12.5', 12.5, 12.5, 12.5, 12.5),
    ->    ('13.5', 13.5, 13.5, 13.5, 13.5),
    ->    ('14.5', 14.5, 14.5, 14.5, 14.5),
    ->    ('12345.67890',12345.67890, 12345.67890, 12345.67890, 12345.67890);
Query OK, 6 rows affected, 2 warnings (1.78 sec)
Records: 6  Duplicates: 0  Warnings: 2

mysql> show warnings
    -> ;
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'ti' at row 2 |
| Warning | 1264 | Out of range value adjusted for column 'ti' at row 6 |
+---------+------+------------------------------------------------------+
2 rows in set (0.00 sec)

Fair enough. We tried to insert values outside the bounds (-127 to 127) for TINYINT, and it makes sense that they’d fail. So let’s see what we got here:

mysql> select * from number_Stuff;
+-------------+------+-------+---------+-------------+
| description | ti   | ival  | fl      | dc          |
+-------------+------+-------+---------+-------------+
| 123         |  123 |   123 |     123 |   123.00000 |
| 384         |  127 |   384 |     384 |   384.00000 |
| 12.5        |   13 |    13 |    12.5 |    12.50000 |
| 13.5        |   14 |    14 |    13.5 |    13.50000 |
| 14.5        |   15 |    15 |    14.5 |    14.50000 |
| 12345.67890 |  127 | 12346 | 12345.7 | 12345.67890 |
+-------------+------+-------+---------+-------------+
6 rows in set (0.00 sec)

Our first row looks like it went in just fine.

The second row is what makes the DBA in me a little nervous. We tried to insert the integer value 384 into a TINYINT column and MySQL correctly gave us a warning. However, it then modified our data and put a 127 (the maximum value) into the TINYINT field.

Rows 3, 4, and 5 appear to have gone in without a hitch. But wait, it looks like MySQL insert the rounded number into the columns that contained integers. Hold on, you’re being paranoid, Oracle does the same thing:

SQL> create table xyz (x integer);

Table created.

SQL> insert into xyz (x) values (12.5);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from xyz;

         X
----------
        13

But what am I trying to show you with these numbers? Just that FLOAT and NUMBER round differently:

mysql> select description, round(fl), round(dc) from number_stuf
    -> where description in ('12.5','13.5', '14.5')
    -> ;
+-------------+-----------+-----------+
| description | round(fl) | round(dc) |
+-------------+-----------+-----------+
| 12.5        |        12 |        13 |
| 13.5        |        13 |        14 |
| 14.5        |        14 |        15 |
+-------------+-----------+-----------+
3 rows in set (0.00 sec)

Last, but not least, lets look at real numbers.

mysql> select description, fl, dc
    -> from number_stuff
    -> where description = '12345.67890';
+-------------+---------+-------------+
| description | fl      | dc          |
+-------------+---------+-------------+
| 12345.67890 | 12345.7 | 12345.67890 |
+-------------+---------+-------------+
1 row in set (0.00 sec)

Is that fl a display issue or a storage issue? Lets just use the TRUNCATE function to see what MySQL is really storing:

mysql> select description, fl, truncate(fl, 25) trunc_fl, dc
    -> from number_stuff
    -> where description = '12345.67890';
+-------------+---------+---------------------------------+-------------+
| description | fl      | trunc_fl                        | dc          |
+-------------+---------+---------------------------------+-------------+
| 12345.67890 | 12345.7 | 12345.6787109375000000000000000 | 12345.67890 |
+-------------+---------+---------------------------------+-------------+
1 row in set (0.00 sec)

Yup, that’s really what MySQL is storing, a wrong value. But does it really? Lets multiply by 10000 to see:

mysql> select description, fl, fl * 10000 big_fl, dc
    -> from number_stuff
    -> where description = '12345.67890';
+-------------+---------+-----------------+-------------+
| description | fl      | big_fl          | dc          |
+-------------+---------+-----------------+-------------+
| 12345.67890 | 12345.7 | 123456787.10938 | 12345.67890 |
+-------------+---------+-----------------+-------------+
1 row in set (0.00 sec)

Sure enough, that’s the value being stored.

Note to self: Don’t use FLOAT to store money or fractional shares.

As we were going through data types, I couldn’t understand why there were all these different INTEGER subtypes. The reason is that MySQL will take the entire 4 bytes in a 4 byte INTEGER even though it might not need it. For example, you create the table below:

CREATE TABLE xyz (
   id   BIGINT);

BIGINT is a data type that is an 8 byte integer. You intend to fill xyz.id with sequential numbers starting at 1. When you insert the value 1 into xyz.id, you have taken 8 bytes. This is different than Oracle in that the number data types are variable length. Is this good or bad, depends. It’s just something to be aware of.

MySQL to an Oracle DBA, Part I

As you may remember, I am at MySQL training this week. The first day was introductory material which included installing MySQL and query basics. Because we didn’t breeze through the Query portion, it gave me plenty of time to experiment.

SQL> select count(*) from country;

  COUNT(*)
----------
       239


mysql> select count(*) from country;
+----------+
| count(*) |
+----------+
|      239 |
+----------+
1 row in set (0.00 sec)

OK, that’s cool, that’s what I expected. Let’s do a simple aggregate function:

SQL> select continent, sum(population) TotalPop
  2  from country
  3  group by continent
  4  order by continent
  5  ;

CONTINENT                        TOTALPOP
------------------------------ ----------
Africa                          784475000
Antarctica                              0
Asia                           3705025700
Europe                          730074600
North America                   482993000
Oceania                          30401150
South America                   345780000

7 rows selected.

OK, that’s what we expected. Let’s try the same query in MySQL:

mysql> select continent, sum(population) TotalPop
    -> from country
    -> group by continent
    -> order by continent
    -> ;
+---------------+------------+
| continent     | TotalPop   |
+---------------+------------+
| Asia          | 3705025700 |
| Europe        |  730074600 |
| North America |  482993000 |
| Africa        |  784475000 |
| Oceania       |   30401150 |
| Antarctica    |          0 |
| South America |  345780000 |
+---------------+------------+
7 rows in set (0.00 sec)

Hmm, that’s interesting. Maybe it has something to do with default sorting order or something. I’ll have to ask Tobias (my Instructor) about that in the morning. Let’s try sorting by a number field:

SQL> select continent, sum(population) totalpop
  2  from country
  3  group by continent
  4  order by totalpop desc
  5  ;

CONTINENT                        TOTALPOP
------------------------------ ----------
Asia                           3705025700
Africa                          784475000
Europe                          730074600
North America                   482993000
South America                   345780000
Oceania                          30401150
Antarctica                              0

7 rows selected.

mysql> select continent, sum(population) totalpop
    -> from country
    -> group by continent
    -> order by totalpop desc
    -> ;
+---------------+------------+
| continent     | totalpop   |
+---------------+------------+
| Asia          | 3705025700 |
| Africa        |  784475000 |
| Europe        |  730074600 |
| North America |  482993000 |
| South America |  345780000 |
| Oceania       |   30401150 |
| Antarctica    |          0 |
+---------------+------------+
7 rows in set (0.02 sec)

Yeah, that’s we expected. Let’s try another query:

mysql> select continent, population totalpop
    -> from country
    -> group by continent
    -> order by totalpop desc
    -> ;
+---------------+----------+
| continent     | totalpop |
+---------------+----------+
| South America | 37032000 |
| Africa        | 31471000 |
| Asia          | 22720000 |
| Europe        | 15864000 |
| North America |   217000 |
| Oceania       |    68000 |
| Antarctica    |        0 |
+---------------+----------+
7 rows in set (0.00 sec)

Hmm, what’s weird about this query (assuming the data is correct)? Let’s try it in Oracle:

SQL> select continent, population totalpop
  2  from country
  3  group by continent
  4  order by totalpop desc
  5  ;
select continent, population totalpop
                  *
ERROR at line 1:
ORA-00979: not a GROUP BY expression

That’s right, our old friend didn’t even run the query for us.

Another interesting tidbit was with the DISTINCT Operator. In Oracle, the optimizer may determine DISTINCT values by sorting, whereas MySQL can use one of two algorithms. For example:

SQL> select distinct continent from country;

CONTINENT
------------------------------
Africa
Antarctica
Asia
Europe
North America
Oceania
South America

7 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=239 Bytes=7
          648)

   1    0   SORT (UNIQUE) (Cost=6 Card=239 Bytes=7648)
   2    1     TABLE ACCESS (FULL) OF 'COUNTRY' (TABLE) (Cost=5 Card=23
          9 Bytes=7648)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
        665  bytes sent via SQL*Net to client
        507  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          7  rows processed

mysql> select distinct continent from country;
+---------------+
| continent     |
+---------------+
| Asia          |
| Europe        |
| North America |
| Africa        |
| Oceania       |
| South America |
| Antarctica    |
+---------------+
7 rows in set (0.00 sec)

BTW, another participant in the class suggested that Oracle always sends back the results of DISTINCT in sorted order. I didn’t think so, but wasn’t able to disprove it. Maybe I learned something new about Oracle today.

Edit: 09/19/2005 23:33 -
Tom correctly points out in the comments that Oracle doesn't always use a sort to determine duplicates. If I had that looked on metalink at doc 655027.999 or 249919.999 I would have confirmed my suspicians about always using ORDER BY. Bang head on desk, Bang head on desk, ...

We're doing MySQL

My manager called me into his office the other day and explained how we were going to put our first MySQL database up. It's a relatively simple app with 99% read activity and some batch loads in the night. This wasn't a total surprise as I did some investigation into MySQL about a year ago and determined that for the right types of application it would be adequate.

We've got some of the basics up in development already, but I feel like I'm a newbie all over again. When I can't figure something out, I say "That's how it works in Oracle" and need to step outside the box to figure it out.

This week is training in NYC. Five days of the basics will hopefully get me up to speed where I can be relatively proficient. Expect some blog entries next week about the differences between MySQL and Oracle.

Top N

A Frequently Asked Question on many Oracle related forums is “How can I get the first/last N rows in my table?” Sometimes that’s not an easy question to answer. I always ask the poster what they mean by “first”. Some people mean the order the records were inserted, some people mean the top-N rows. I’ve found it’s always easier to get the poster to explain what they are trying to do instead of guessing.

First/Last – in an ordered set
This one is easy. You take advantage of inline views and Oracle’s pseudocolumn ROWNUM. People usually get tripped up when they use ROWNUM because while ROWNUM is relevant in all queries, in this type of query it only makes sense when coupled with an ORDER BY.

For example:

SQL> select * from xyz;

         X          Y Z
---------- ---------- -------------------
       127          1 09/16/2005 14:46:50
       148         22 09/17/2005 14:48:42
       149         23 09/18/2005 14:48:42
       150         24 09/19/2005 14:48:42
       131          5 09/20/2005 14:46:50
       151         25 09/20/2005 14:48:42
       133          7 09/22/2005 14:46:50
       152         26 09/21/2005 14:48:42
       153         27 09/22/2005 14:48:42
       154         28 09/23/2005 14:48:42
       137         11 09/16/2005 14:46:50
       155         29 09/24/2005 14:48:42
       139         13 09/18/2005 14:46:50
       156         30 09/15/2005 14:48:42
       143         17 09/22/2005 14:46:50
       145         19 09/24/2005 14:46:50
       147         21 09/16/2005 14:48:42

17 rows selected.

SQL> select rownum, x, y, z from xyz;

    ROWNUM          X          Y Z
---------- ---------- ---------- -------------------
         1        127          1 09/16/2005 14:46:50
         2        148         22 09/17/2005 14:48:42
         3        149         23 09/18/2005 14:48:42
         4        150         24 09/19/2005 14:48:42
         5        131          5 09/20/2005 14:46:50
         6        151         25 09/20/2005 14:48:42
         7        133          7 09/22/2005 14:46:50
         8        152         26 09/21/2005 14:48:42
         9        153         27 09/22/2005 14:48:42
        10        154         28 09/23/2005 14:48:42
        11        137         11 09/16/2005 14:46:50
        12        155         29 09/24/2005 14:48:42
        13        139         13 09/18/2005 14:46:50
        14        156         30 09/15/2005 14:48:42
        15        143         17 09/22/2005 14:46:50
        16        145         19 09/24/2005 14:46:50
        17        147         21 09/16/2005 14:48:42

Here, ROWNUM does exactly what it’s supposed to do. It gives you a sequential number according to how the rows were pulled out of the table. There is obviously no logical order (to the human eye, anyway), but that’s how the rows come out. If you restrict by ROWNUM, you will certainly get a result, but probably not what you expect:

SQL> select * from xyz
  2  where rownum < 6
  3  /

         X          Y Z
---------- ---------- -------------------
       127          1 09/16/2005 14:46:50
       148         22 09/17/2005 14:48:42
       149         23 09/18/2005 14:48:42
       150         24 09/19/2005 14:48:42
       131          5 09/20/2005 14:46:50

So what to do? Just put an ORDER BY in?

SQL> select * from xyz
  2  where rownum < 6
  3  order by y
  4  /

         X          Y Z
---------- ---------- -------------------
       127          1 09/16/2005 14:46:50
       131          5 09/20/2005 14:46:50
       148         22 09/17/2005 14:48:42
       149         23 09/18/2005 14:48:42
       150         24 09/19/2005 14:48:42

Nope. But we’re getting closer. You have to use your ordered query as a subquery and then apply the filter.

SQL> select * from (
  2     select * from xyz
  3     order by y
  4  )
  5  where rownum < 6
  6  /

         X          Y Z
---------- ---------- -------------------
       127          1 09/16/2005 14:46:50
       131          5 09/20/2005 14:46:50
       133          7 09/22/2005 14:46:50
       137         11 09/16/2005 14:46:50
       139         13 09/18/2005 14:46:50

Bingo. That’s what we wanted. Now suppose we wanted a “window” from 6 to 10:

SQL> select o.x, o.y, o.z from    (
  2      select rownum r, i.x, i.y, i.z from (
  3         select x, y, z from xyz
  4         order by y ) i
  5      where rownum < 11 )o
  6  where o.r > 5
  7  /

         X          Y Z
---------- ---------- -------------------
       143         17 09/22/2005 14:46:50
       145         19 09/24/2005 14:46:50
       147         21 09/16/2005 14:48:42
       148         22 09/17/2005 14:48:42
       149         23 09/18/2005 14:48:42

First/Last – in insertion order
Since there is no inherent way for you to figure out the order the rows were put into a table, this one is a little trickier. The only way you can positively indicate the order the rows were inserted into the table is if you explicitly mark each record. Sometimes this may be a “DATE_INSERTED” field or it may be some other field that indicates order. In my example, I have created a BEFORE INSERT trigger to populate the Primary Key (x) with a value from a sequence:

create trigger xyz_bi
before insert
on xyz
for each row
declare
   pkval number;

begin
   select xyz_id.nextval into pkval from dual;

   :new.x := pkval;
end;

Therefore, I know my rows were inserted in the order according to X. I can then use my PK value to find out the last 10 rows inserted by:

SQL> select * from (
  2     select * from xyz
  3     order by x desc
  4     )
  5  where rownum < 11
  6  /

         X          Y Z
---------- ---------- -------------------
       156         30 09/15/2005 14:48:42
       155         29 09/24/2005 14:48:42
       154         28 09/23/2005 14:48:42
       153         27 09/22/2005 14:48:42
       152         26 09/21/2005 14:48:42
       151         25 09/20/2005 14:48:42
       150         24 09/19/2005 14:48:42
       149         23 09/18/2005 14:48:42
       148         22 09/17/2005 14:48:42
       147         21 09/16/2005 14:48:42

10 rows selected.

Recruiting Jack of all Technology

I got a cold call from a recruiter this morning. I was feeling rather playful, so I persued the conversation.

Recruiter: Hi, this is MaryJo Recruiter from XYZ firm. Do you have a couple of minutes to talk to me?
Me: Um, OK.

R: I am looking for a combination Database Administrator/Database Developer/Business Analyst for a financial company.
Me: Let me stop you right there. What kind of money are we talking?

I always like to ask this question to see what the jobs out there are paying.

R: Well, they didn't specify a range. I asked if I found somebody at 80K would they want to look at them and the client said yes.
Me: Oh, so they're really looking for 65K-75K.
R: Probably.
Me: What kind of skills?
R: They want 5 years of Oracle DBA, 5 years using VB and/or MS Access, and business analyst background.
Me: That's a pretty strange combination.
R: The position is not defined very well.
Me: I see.
R: It's a really good company. Would you be interested?
Me: You'd have to triple the money.

I always like to throw out some factor like double or triple to see what they say.

R: Oh....I'll pay referral fees.
Me: I see. I know several people in the industry and know of a couple looking right now. Tell me more about the position.
R: This position would report to the CIO. They would interface with the users to define the project and then implement the project with VB and Access.
Me: OK, so where does Oracle come into the picture?
R: Well, they're thinking they might have a lot of data and they will need Oracle.
Me: Oh. I think you will have a very hard time filling this position. There's not that many BA's that know how to program in VB/Access and setup an Oracle Database.
R: Do you know of anyone that might fit this position? I pay referral fees.
Me: I have a couple people in mind, but they would be more on the technical side.
R: Great! Can I send you my contact information?

So if anybody wants to do VB/Access/Oracle DBA/Business Analyst for $70K in Connecticut, drop me a line.

Goodbye DBAs

Forrester Research has jumped on the "zero administration" database bandwagon. Looks like I'll be retiring in 2009. Woo hoo!!!

Oracle WTF

Stumbled upon a funny blog by Willam Robertson, Oracle WTF. It's modled after The Daily WTF, but with an Oracle slant. Check it out.

Keep it in your pants

You know that gap between the elevator and the floor you are on? Ever wonder what would happen if you dropped something down there? I have had that very thought on more than one occasion.

I had something to do after work today, and I was already late when I packed up and hit the elevator button. My keys were in my hand and I was fiddling with them when the elevator door opened. As I stepped into the elevator, my keys dropped from my hand; keys on the floor and keyless remote dangling over “the gap”. In what seemed like slow motion, I bent down to pick them and just then the remote won out and they slipped down the gap.

All I could muster was “Oh Shit” as they banged their way down to some unknown place.

I immediately went to the security office hoping they could do something. The security guard paged building maintenance and Darrel says “no problem”. We take the elevator to the bottom floor, send it back up, and he uses his special key to get in the elevator shaft. We look around, but there’s no keys down there. About $3 in change, countless gum and candy wrappers, but no keys. Darrel says “Are you sure you were in this elevator?” We repeat the same procedure floor by floor thinking that maybe they got hung up on somewhere in the shaft. I wasn’t worried about the keys, because the maintenance guy said the elevator people would come in Monday and do a better search. If we can’t find them, I’ll have to call my security officer and let him know I lost my keys. After hours. Then another thought hits me; how do I get home?

As we’re on our pursuit, Darrel says somebody drops something down the shaft about once a month, which surprises me. He suggests we go back down to the basement and look around again. We look in the same shaft and still no dice. He suggests we take a peek in the other elevator shaft with the possibility that the keys bounced around in the shaft. Sure enough, there they were. He smiles at me and says “You were in this one, weren’t you?”

The only problem was my keyless entry remote didn’t survive the drop. As luck has it, I keep my keys to the car inside the car and use the remote to open the door. Quickly, I realize I’m taking the train home tonight.

Moral of the story: Keep you keys in your pants until you get to your car.

The Carolina Way

I love being at the beach. It gives me lots of time to catch up on non-technical reading. This year, I brought along The Carolina Way: Leadership Lessons from a Life in Coaching, by Dean Smith and Gerald Bell. A little disclosure here; I'm a Carolina fan and if Dean Smith said he made kids practice in bare feet, I'd think it was a good idea.

This book is more than your typical sports book. Sure, it's filled with stories about games, seasons, and individual players. Each chapter starts out with Coach Smith explaining a particular aspect of his coaching methodology. A Player's Perspective follows outlining how the particular aspect and Dr. Bell relates the aspect to the modern workplace. This book is all about building teams.

The Carolina Way can be summed up by these words: Play Hard; Play Together; Play Smart. For example, Coach Smith talks about recruiting players that will fit into his system. Dr. Bell then takes those ideas one step further and relates the recruiting to hiring workers. Another example is Dean Smith held regular one-on-one meetings with each of his players to discover their goals in life as well as basketball. Dr. Bell then outlines how you can pattern employee reviews around these same principals.

I personally got a lot out of this book and plan on implementing some of the strategies in my own team.

Blogger for Word

I am posting this blog entry using the new Blogger for Word add-on.  I downloaded it a couple days ago and tried to get it working, but had problems connecting to blogger.com.  I fired it up today, and voila! It works.

I tried some of the other editors out there, but just found it easier to use the blogger.com editor, except in cases of posting source code.  Lets see how this comes out:

FOR i IN 1..100 LOOP
   i:= i+1;
END LOOP;

OK, that should suffice.  Heck, I already know Word, let me try this for a couple days to see how it goes.

Give 'em the finger

Saw an interesting piece of technology this week. You know those little cards you get at the supermarket that give you the sale price on advertised items? Well, the local Piggly Wiggly has gone high tech. When you sign up for that card you can tie it to a debit/credit card and they'll also scan your fingerprint. You pick up your groceries, proceed to the checkout, and scan you fingerprint to checkout.

Traffic and Taxes

I've been in traffic hell the last two days. Friday I left work to go home, pack the car, and take off for 10 days on Kiawah Island near Charleston, South Carolina. Kiawah is our most favorite spot on Earth and we always look forward to winding down on the beach. I headed for home, but it took me 75 minutes to go 15 miles. I passed three fender benders which caused the traffic. Outside of Washington DC, we hit three more accidents that made the long drive even longer.

As I was sitting in traffic, I came up with two ideas. First, I think the driver at fault in a fender bender should have to pay the state a $3000 fine. You could call this a nuisance tax, a traffic tax, or a whatever tax. Either way, it comes directly out of the driver's pocket and would not be covered by insurance. This money, in turn, goes directly into the state's transportation budget and the state reduces the amount everybody else pays on gas by reducing the taxes. The person that caused the accident then gives a little back to the rest of us that were stuck in traffic while they were doing their lipstick going 75mph. If no fault can be assessed, then that's OK too.

Another idea that I came up with was I should be able to take the time I am sitting in traffic off my income taxes. Think about it for a second. The state AND federal government are responsible for having inadequate roadways that can't handle the amount of traffic that is on them. If every state were like New Jersey with its ten and twelve lane highways then nobody would take time off their taxes. If every state was like Connecticut or California and people deducted $10k off their taxes every year, the roads would get fixed. In a hurry.

Then again, maybe I had been breathing too much exhaust.

Anything But Windows Revolution

So much for the Linux revolution. Only 4% of my readers use Linux as their desktop of choice (me, counting as 1%). That only slightly beats out MacOS X. How depressing.

Arrr, Hardware

Got home last Friday night and I couldn't connect to my wireless network. It sometimes happens and I have to reboot the WAP and/or router, so I didn't think anything about it. Once everything came back up, my laptop could see the WAP, but I couldn't aquire a network address through DHCP. There was no activity on the WAP so I thought something was screwy with my laptop. I tried my Wife's laptop, saw the WAP, but wasn't able to get a network address. Since both laptops are running Windoz, I figured I'd reboot everything and see what happens. Nothing happens.

I fired up my Linux box and started looking at the network. All seemed fine. I could ping the other hosts on the network, but couldn't ping the WAP (I think I can do that, but by now I am second guessing myself). So I reset both the router and WAP back to factory settings and changed all the static IP numbers on my network. Still no dice. At this point I figured my WAP is toast and I'll have to replace it. I have my eye on that little 4 port router with WAP attached anyway, so it will be a good chance to upgrade my technology. It's 23:30 and I need to go to a wedding tomorrow, so I give up.

While we were away Saturday the power went out at the house. Now, both the router and WAP are on a UPS, so they shouldn't have been affected. My wife fires up her laptop while I am on the desktop and says "When did you fix the wireless network?". WTF? Now it's working. I wonder if the WAP is running embedded Windoz?

Mr. & Mrs. Johnson

Saturday we went on a road trip to Clifton Park, New York (outside Albany) for the wedding of our long-time friend, Denise and her soon-to-be, Randy. It looked like the weather was not going to cooperate as it rained just about the entire way. About 15 minutes before we pulled into Clifton Park, the clouds started breaking and the sun came out. What a perfect day for a wedding.

Denise and I worked together at the computer center when we were in school. Always bubbly and upbeat, you just knew she was one of those special people in the world. Denise also lived with my wife (then gf) when we were in school. I've always known Denise as a strong, independent, easygoing woman. When we met Randy about a year ago, the two seemed inseparable and make each other very happy.

All had a wonderful time at the wedding. The service was very unostentatious with a small bridal party, best man, and Pastor Chuck presiding over the nuptials. The reception was elegant and afforded lots of time to catch-up with friends. My only regret is that I would have liked to see Ann (another college friend) doing the YMCA, but alas, the DJ didn't serve that one up. Maybe that's just an '80s thing.

Just to show how thoughtful the newlyweds are, when we checked in to the Hotel there was a gift-basket containing snacks and drinks.

Congratulations to Mr. & Mrs. Johnson. May your new life be filled with happiness forever.

A New High

$2.899/gal this morning for gas. Over $33 to fill the Honda.

Installing Standard Edition

I installed 10.2.0.1 Enterprise Edition on RHEL 4 a couple weeks ago and got it working with a couple small tweaks to the OS. Yes, I know 10gR2 is not supported on RHEL 4 yet, but by the time I got to production it will be.

A couple days later I got a new project that will require 10.2.0.1 Standard Edition on RHEL 4. Not thinking about it, I used the installer that I downloaded from OTN to install. I chose the custom install (like I always do), de-selected the "Enterprise Options" and click,click,click, I was done.

Once I started the database things got interesting. My banner said:

sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 26 20:31:32 2005
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning and Data Mining options

OK, that's not cool. I de-install and re-install thinking maybe I screwed something up. Nope, same thing.

"Surely, someone has run into this problem before", I think to myself. I searched metalink for a while and noticed some bugs with the install so I created a TAR.

Me: I installed SE and came out with this banner. I did this, this, and this. I am seeing this, this, and this, which I think is incorrect.
TAR: Did you install from part # XYZ? There have been issues with that set of disks.
Me: No, I downloade from OTN.
TAR: Oh, install from CD.
Me: Isn't it the same software?
TAR: You downloaded from one of the first OTN downloads. There might have been a problem.
Me: But, I don't have the CD. I was told in TAR ### that CD's aren't ready for shipment.
TAR: create another TAR and tell them you need CDs because you're working on TAR ###.
Me: OK.

I create another TAR and get the CDs shipped to me. I copy from the CD to my local box and install from there. Same thing.

Me: OK, I installed from CD and got the same thing.
TAR: Ship us the installActions log and these other logs.
Me: Here they are.

Wait 3 days.

TAR: The log shows you installed EE. Install SE.
Me: No, I chose "Custom", unchecked the EE options, and installed.
TAR: Install SE.
Me: But that's the way I've done it before with no problems.
TAR: Install SE.
Me: But I don't want OEM, Apache, or any of the other stuff installed.
TAR: That's the way it's been done since Oracle7. That other stuff won't hurt, just install it.
Me: That's not true, but OK.

I saw this was going nowhere, so I closed the TAR. A few minutes later I get an email from Oracle Support saying my TAR has been updated.

TAR: Yeah, you're right. But the way you get around it is install everything for SE and then de-install the options you don't want.

Before Insert

One of my developers came to me with this problem. He is trying to manipulate the data coming into a table by using an INSERT TRIGGER. For the life of me, I can't figure out why this doesn't work (I must be missing something simple):

SQL> drop table xyz
 2  /

Table dropped.

SQL> create table xyz
 2     (
 3      x number(10),
 4      y varchar2(20),
 5      z varchar2(4))
 6  /

Table created.

SQL>
SQL> create or replace trigger xyz_bi
 2     before insert on xyz
 3     for each row
 4  begin
 5
 6     :new.z := '777';
 7
 8  end;
 9  /

Trigger created.

SQL>
SQL> insert into xyz values (1, '123456789','123');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from xyz;

        X Y                    Z
---------- -------------------- ----
        1 123456789            777

SQL>

OK, so far so good. The trigger populated the Z value like I expected. However, when I pass in a string that is longer than the Z field, I get:

SQL> insert into xyz values (1, '123456789','123456789');
insert into xyz values (1, '123456789','123456789')
                                      *
ERROR at line 1:
ORA-12899: value too large for column "JEFFH"."XYZ"."Z" (actual: 9, maximum: 4)


SQL>
SQL> commit;

Commit complete.

I can't explain why in a BEFORE INSERT trigger Oracle would care what the length of the string is before the trigger even fires. Any hints?

Before Insert, Part II

Heath Sheehan said...

Row level triggers fire for each row that is affected by the triggering statement. That would imply that all validity checks have to be passed before the triggers fire. Any explicit or implicit data conversions have to result in valid data. Any check constraints have to pass, etc.

If the data isn't valid, the row isn't inserted and there's nothing for which the trigger should fire.


Yeah, but if that's true, why doesn't a NOT NULL constraint elicit the same behaviour?

SQL> drop table xyz
  2  /

Table dropped.

SQL> create table xyz
  2     (
  3      x number(10),
  4      y varchar2(20),
  5      z varchar2(4) not null)
  6  /

Table created.

SQL>
SQL> create or replace trigger xyz_bi
  2     before insert on xyz
  3     for each row
  4  begin
  5
  6     IF :new.z IS NULL THEN
  7        :new.z := '777';
  8     END IF;
  9
 10  end;
 11  /

Trigger created.

SQL>
SQL> insert into xyz values (1, '123456789','123');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from xyz;

         X Y                    Z
---------- -------------------- ----
         1 123456789            123

SQL>
SQL> insert into xyz values (1, '123456789',NULL);

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL> select * from xyz;

         X Y                    Z
---------- -------------------- ----
         1 123456789            123
         1 123456789            777

SQL>

Job Shock

I read with interest IT Workers Confront 'Job Shock' by Thorton A. May in the latest Computerworld. Mr. May brings up a very valid point that users have extraordinary tools in their hands to do the work that IT Professionals used to do. It brought to mind one of my first programming projects way back in 1989. I was to write a program that pulled data out of an Ingres database, sort and group it in illogical ways, and spit it out on a landscape page. The tool of choice was C and it took me six weeks to write. Today, I'd dump some summarized data in a CSV file and let the user mess with it.

Mr. May goes on to wonder "What if paid IT employment was to steadily disappear?". Good question. I can tell you the people at my first job are still employed. No, they're not writing too many C programs anymore, but web interfaces. They give their users the tools to get their own data. Last I knew, there were just as many people as when I left.

Mr. May goes on to point out how to "save" your fat IT career. All his points are well taken, but the bottom line is your career is your business. If you let you business stagnate, it will die. (If this sounds familiar, thanks for reading).

dbms_stats.alter_database_tab_monitoring

While implementing my new statistics gathering procedure in production, I ran into a snag with permissions.

The very first step in my process is to setup my "analyze" user and create a job that runs every night that turns MONITORING on for any new tables. Sounds simple enough, right?

$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.5.0 - Production on Tue Aug 9 21:14:18 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning option
JServer Release 9.2.0.5.0 - Production

SQL> create user analyzer identified by youbetcha
2  temporary tablespace temp
3  default tablespace tools
4  quota unlimited on tools;

User created.

SQL> grant create session to analyzer;

Grant succeeded.

SQL> grant alter session to analyzer;

Grant succeeded.

SQL> grant execute on dbms_stats to analyzer;

Grant succeeded.

OK, that should be enough, right? Let's give it a try.

SQL> connect analyzer/youbetcha
Connected.

SQL> exec dbms_stats.alter_database_tab_monitoring(TRUE);
BEGIN dbms_stats.alter_database_tab_monitoring(TRUE); END;

*
ERROR at line 1:
ORA-20000: Insufficient privileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 10733
ORA-06512: at "SYS.DBMS_STATS", line 10752
ORA-06512: at line 1

Hmm. I looked at the package spec and find out it's run with Invoker Rights and not Definer's rights. Ah, I must need "ANALYZE ANY" privilege.

SQL> grant analyze any to analyzer;

Grant succeeded.

That must be it, let's try it again.

SQL> connect analyzer/youbetcha
Connected.
SQL> exec dbms_stats.alter_database_tab_monitoring(TRUE);
BEGIN dbms_stats.alter_database_tab_monitoring(TRUE); END;

*
ERROR at line 1:
ORA-20000: Insufficient privileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 10733
ORA-06512: at "SYS.DBMS_STATS", line 10752
ORA-06512: at line 1

OK, that ain't it. I did some research on Metalink, AskTom, and poured through the docs, but didn't really get anywhere. Then I thought, how about a Trace? I started a level 12 trace and then re-executed the procedure. Under the covers, dbms_stats.alter_database_tab_monitoring just does a "ALTER TABLE xyz MONITORING". I get it, I need "ALTER ANY TABLE".

SQL> connect / as sysdba
Connected.
SQL> grant alter any table to analyzer;

Grant succeeded.

SQL> connect analyzer/youbetcha
Connected.
SQL> exec dbms_stats.alter_database_tab_monitoring(TRUE);

PL/SQL procedure successfully completed.

Simple as that. Now all I have to do is create a dbms_job and run it every day:

SQL> declare
2     lJobNo INTEGER;
3     lJob   VARCHAR2(2222);
4  begin
5
6     lJob := 'dbms_stats.alter_database_tab_monitoring(monitoring=>TRUE);';
7     dbms_job.submit(
8        job=>lJobNo,
9        what=>lJob,
10        next_date=>SYSDATE,
11        interval=>'trunc(sysdate+1)');
12
13     dbms_output.put_line('job submitted: ' || to_char(lJobNo) || '...');
14
15  end;
16  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select job, last_date, last_sec,
2     next_date, next_sec, broken,
3     failures, what
4  from user_jobs;

     JOB LAST_DATE LAST_SEC NEXT_DATE NEXT_SEC B   FAILURES WHAT
---------- --------- -------- --------- -------- - ---------- -------------------------
      23 09-AUG-05 21:39:31 10-AUG-05 00:00:00 N          0 dbms_stats.alter_database_tab_monitoring(monitoring=>TRUE);                                        

That's done, on to the next step!

Thanks Doug

I'd been lurking in Doug Burns' Blog for a couple of weeks and found most of his posts interesting. Interestingly, enough, that last week I put a link on my blog to his so I could share with others. Then he goes and changes blog hosts. Now I have to update my blog links to include his new site. Thanks Doug.

Oracle Job Scheduling

Managing jobs in Oracle 9i and below was a pretty straight forward process; start the job queue processes and submit a PL/SQL Block using dbms_job. If you wanted any level of detail you had to wrap the dbms_job functionality around a package and some other tables. Job scheduling was basic and it generally worked. Any complicated scheduling or interaction with the OS and you were out of luck and had to revert to cron.

I'm not sure what I expected when I started reading Oracle Job Scheduling, by Dr. Tim Hall, a few days ago. I've got a decent handle on dbms_job and have used it extensively for all sorts of maintenance tasks. How different could job scheduling in 10g be? Believe me, it's different.

Dr. Hall explains how the new dbms_scheduler packages works and the details of each call. In addition he explains in detail how the new INTERVAL type works and gives very through examples. Chapter 4 is by far the most valuable chapter as it explain four different methods to schedule dependant jobs. The examples in this chapter are an extension of Tims experience in the real-world implementing solutions. Later on, the book explains about how to monitor the new scheduler and how to view the job logs.

I've got to admit I breezed over the sections on OEM and OS Scheduling. I don't use OEM and there's nothing I really need to know about cron.

This book was a good read. Don't get me wrong, it's no A Dog Year: Twelve Months, Four Dogs and Me. It's a technical book through and through. I would definitely recommend it if you are planning on using the Oracle job scheduler to implement complex business schedules.

Oh, and by about the third time I saw:

-- ****************************************************
-- Copyright 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties.  Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- ****************************************************

I was ready to barf.

Hiring Round 3

I can't freakin' believe it! You may recall my sagas about finding a new Database Administrator (here and here). Well, it's happened again.

We interviewed about 20 canidates for the open DBA position and narrowed the choice down to two very qualified candidates. Each had their own strengths and weaknesses. It was a difficult choice, but we finally chose one over the other and offered NewDBA2 the position. NewDBA2 thought about it for about an hour before he called us back to accept the position. A start date was scheduled for two weeks from the next Monday. As recently as yesterday, NewDBA2 contacted the recruiter and reiterated his excitement for the new position. In fact, they decided to go to lunch today to celebrate.

Then I got the call. NewDBA2 is sorry to say that he has been offered another position and can't start on Monday. Yes, Monday, three days away.

That's twice in a row. I had two people commit to a job and at the last minute they backed out. Personally, if I don't like something about the job, I just don't take it. Sigh, back to the recruiters.

Around New York

The Wife and took in a matinee showing of The Lion King (which was spectacular) on Saturday. Our plan was to take a train to New York City, see the show, go to dinner, and be back by the evening. We've been to New York a number of times the last five years, so we've gotten pretty good at getting around town. We're used to crowds, pan handlers, and those annoying hawkers trying to get you on a bus tour.

As we exited the theatre in Times Square, there were lots of people around. I mean LOTS of people. Seems some dingbats with cameras were filming a "man on the street" type reality TV show about fashion faux-pas and everybody wanted to be on. We only had to go four blocks to get to our restaurant and 15 minutes to get there. Fashionistas in training were swarming the cameras so we crossed the street - right under the MTV studio. Some guy was giving away CD's and of course the TRL crowd was grabbing them up by the second. We weren't encouraged by the 1/2 block line outside Bubba Gump, but still pushed on. The next block, we traveled down 45th street and got out of Times Square. A block later it was like a ghost town with hardly any people at all. We finally got to the restaurant only 5 minutes late and had a great dinner and still was able to catch the train at a decent hour. Except for the crowds, a great day.

Parking

We were out and about the other day running errands in one of the snooty towns on the Gold Coast when we came upon a Carvel. I'm never one to pass up Carvel, so I flipped my blinker on and started pulling into the parking lot. The Tahoe driving lady ahead of me started taking a spot right up front, but half way in, she stopped and started backing up. Meanwhile, I'm half in the lot and my tail end is sticking out into a four lane road. I mumble "WTF?" as Mrs. Tahoe just about backs into me and takes another spot in the lot. Ah, she saw it was a handicap spot. My wife looks at me with rolled eyes like I did something wrong. As as last ditch effort to save my manhood I mumble "That's how people get killed." (Jesus, that was stupid. People get killed from a fender bender, yeah right.)

So Mrs. Tahoe gets out with Grandma Tahoe and her brood and goes to the counter. I notice one of the little Tahoes wants to get behind the counter and touch everything. Then he's looking up and around and Grandma Tahoe quietly grabs his hand. The other two little Tahoes are ordering their ice cream; one with sprinkles, one with chocolate syrup. Mrs. Tahoe orders a fat-free cup (no sprinkles) for herself, a chocolate cup for Grandma (chocolate sprinkles), and a cup (no sprinkles) for Jr. Tahoe.

When we make it outside the Tahoes are enjoying their ice cream. Jr. Tahoe has barely touched his and is looking around touching this and that. I get it, Jr. Tahoe is probably challanged. I feel even worse about the parking as Mrs. Tahoe can probably justify parking in the handicap spot. We finish our ice cream, Jr. Tahoe looks at me and I smile back. He smiles. Cool, maybe he forgives me at least.

As we're walking out past "the" parking spot, Grandma-but-I-won't-admit-it Mercedes pulls into the handicap spot, jumps out of the S-class and heads for the counter. I shake my head knowing I'll get another look if I say something. Out of the blue, my wife says, "Um, you know that's a handicap spot, don't you?" Grandma-but-I-won't-admit-it Mercedes looks startled but heads back to the car and looks at the handicap signs as plain as day. She keeps circling the car like the signs will magically disappear until the Tahoes get up and head for their troop transport. Grandma Tahoe shoots her a look but Mrs. Tahoe is too busy tending to messy faces to notice. Busted. As we're pulling out, I see Grandma-but-I-won't-admit-it Mercedes slinks back to the car and moves it.

"That's how people get killed", I mutter to a smirk.

SA Superhero

Fortunately for me, the best SA was on-duty. After the server rebooted itself about 6 times, we decided to swap it out for a spare. A couple disk swaps, attach the SAN filesystems and we were back in business in 2 hours.

Waiting on my SA

One of my DB Servers is in a perpetual state of rebooting. Waiting for the SA to figure out what's going on. [sarcasm]I love being on-duty.[/sarcasm]

The price you pay

The price of gas never really bothered me. You see, my everyday car is a Honda Civic and I typically get about 37 miles per gallon. Sometimes more, sometimes less. I used to drive about 130 miles a day back and forth to work and it cost about $50 a week for gas. I recently moved and now a tank of gas lasts me about 10 days. My wife drives half the miles I do and when I fill her 4 Runner's tank it's always been $35 or more.

I filled my tank tonight and the pump clicked off at $30.03. $30.03 to fill a Honda! I've always put more than $20 in, but I'd never broken $30. I know at $2.599/gallon we've got nothing to complain about in the U.S. compared to other parts of the world, but sheesh. This is starting to cut into my mad money.

Preparing for "Oracle Job Scheduling"

I got Tim Hall's new book, Oracle Job Scheduling, a day after I started installing Oracle 10g R2 on my home system. I had just installed it at work on one of my development systems and it was literaly click, click, click, done. I figured it would be about the same at home and I could start learning about the job scheduler that night. Started downloading from OTN and, BAM, filesystem filled up. I guess it wouldn't hurt to remove the old software, right? First I removed 9.2.0.5 and only freed up about a gig. I won't need 10.1 now that I got 10.2, right? So I removed it. Then I started the 10.2.0.1 install and the software seemed to install OK. During the database creation, however, I got an ORA-12547 message. I checked out a couple things on metalink and figure my libaio needs to get updated. Off to rhn.redhat.com to get the right rpm and install it. Then I remake oracle. Finally, oracle starts and I can create the database.

I know I promised Tim I'd review his book on my blog. I open up the book to an icon caricature of Don Burleson giving me a gigantic "thumbs up". I can't deal with this, I'm going to bed.

There's a storm a brewin'

Seems as though DBASupport.com has compromised the presentation of their forums in order to accomodate advertisers and their own interests. Several members (myself included) have expressed their dislike of the new format. Join in on the discussion and tell us what you think.

Futzing with 10g Connection Manager

I'd like to think I'm somewhat proficient using Oracle's Connection Manager product. After all, I first implemented CMAN in 8.1.6 for connection pooling. I then used CMAN to receive database traffic through a firewall. Pretty simple stuff, but I could get it to work. Sure, CMAN had it's shortcomings, but there were ways around most of them. Migrating through the 9i versions was no problem; install the cman in a new $OH, copy the cman.ora file and start it up. No fuss, no muss.

While investigating a related connection problem, Oracle Support suggested testing my problem on a 10g connection manager. "No problem", I said confidently knowing the 9i upgrade was a piece of cake. Lets just say things are a little different in the 10g world. Oracle has addressed some of CMAN's shortcomings in the latest release. Below are some of the things I encountered during my upgrade.

Looking through a new window
The first thing I noticed was cmctl now has a new set of commands. I found myself doubting that I ever ran connection manager in the past. A quick review of the 10g connection manager architecture gave me a foundation for the new environment.

CMAN.OR Migration
10g comes with this neat little tool called cmmigr. I used this tool to migrate my working cman.ora file into a non-working cman.ora file.

Tweaking cman.ora file #1
Here's where the real work came in. I had to tweak my cman.ora file to include my host's IP number. One of the nice things about 9.2 cman was you could use (HOST=) in your configuration and the connection manager would automatically use the hostname it was running on. I used this to my advantage to have a generic cman.ora file that I could run on multiple hosts. Since this is only a test, I'll leave this mystery for later.

Starting CMAN
I have what I think is a correct cman.ora file now. I start cmctl and issue the ADMINISTER command. No problem. I then try to STARTUP the connection manager and get a "TNS-04012: unable to start Oracle Connection Manager". The next thing you learn is there is an alert.log file in $OH/network/log that you can check. I had a couple errors in my alert.log that looked like:

(LOG_RECORD=(TIMESTAMP=18-JUL-2005 11:43:35)(EVENT=Failed to start listener process)(REASON=)(OPN=65)(NS1=12545)(NS2=12560)(NT1=515)(NT2=2))

Hmm, nothing useful there. I go back to the docs and see that cman now needs a listener to pass connections off to cmgw. So I try to start a listener process on my own and realized there was no lsnrctl in $OH/bin. Aha! I only installed cman and not the listener. Install the listener components and retry. SUCCESS!

Statcounter

Just after Tom Kyte put a link on his blog to mine, I decided to put a counter on the page. I looked at a couple other sites I respected and I decided to use statcounter from www.statcounter.com. My statcounter looks just like Tom's (except maybe the scale is off by a factor of 100!)

Firefox 1.0.5

Learned from my statcounter that some of you are using Firefox 1.0.5. I didn't know it was even out yet! Needless to say, I upgraded right away.

The Search Continues...

You may remember that I had been looking for a DBA to add to my team a few weeks ago. I offered the position to New DBA and basically met everything he was asking for. A few days before New DBA is supposed to start, he calls and says "Sorry, not interested anymore." Seems NewDBA has found a better situation for himself. So it's back to the recruiters sending 10 resume's a day, sigh... Maybe I'll have some good answers to my tech questions to post.

ORA-22856

Putting some changes into a 9.2.0.5 database. The table is range partitioned, but only has two simple fields (not object types). Adding columns in QA and DEV worked just fine, although they didn't have the volume that PROD does. I run my script and find:

ALTER  TABLE xyz ADD (z varchar2(20));

ORA-22856: cannot add columns to  object tables

WTF?? Oh yeah, production uses COMPRESS. Chalk it up to bug 2421054, adding columns to a compressed table. Can you say patch anyone?

It's Here!!!

It's here!

Trace it!

I often give people the advice to trace their SQL session to see what is going on. Knowing how to trace is one of the tools that every developer and DBA should have in their toolbox.

By tracing, I mean capturing all the SQL in a user's session, or a SQL trace. As you can tell from the Oracle Performance Tuning Guide and Reference, there are many ways to enable tracing. Below are two methods I use as a DBA in my day-to-day routine.


Turn tracing on

Enabling tracing in your own session is very easy. From SQL*Plus, just alter your session and set sql_trace=true.

SQL> alter session set sql_trace=true;

Session altered.

Dude, you're tracing. Every query you run from now on will go into a trace file until you set sql_trace to false.

Sometimes, a session is already underway and you need to start a trace midway through it's execution. Here, you need to be a DBA and enable the 10046 event in the user's session. This is a little more tricky since you have to know the sid and serial# from v$session in order to enable the tracing. For example,

SQL> select sid, serial#, username
2  from v$session
3  where username = 'JEFFH';

     SID    SERIAL# USERNAME
---------- ---------- ------------------------------
      25          5 JEFFH

SQL> exec sys.dbms_system.set_ev(25, 5, 10046,8,'');

PL/SQL procedure successfully completed.

Statements get written to the trace file when they are first encountered after the trace is started. The statement that is executing may not be in the final trace file.

If you are using dedicated servers, the trace file will typically be in the directory on the server specified by the user_dump_dest parameter in the init.ora file. If you are using shared servers, the trace file will be in the directory specified by background_dump_dest. The trace file will have your server process id in it somewhere. If you are running the trace on your own session, you can create a stub with the ALTER SESSION SET TRACEFILE_IDENTIFIER=xxx; This way, your file will have the "xxx" name in it and be easier to identify.


What do I do with it?

OK, so you got this big file. It's just a simple ASCII file, go ahead and read it.

Nah, just kidding. Oracle gives you this program called TKProf that reads your trace file and spits out somewhat meaningful output. (I think Oracle uses tkprof as an acronym for Tom Kyte, Professor, but that's just speculation on my part.) My favorite option to tkprof is sort=prsela,fchela,exeela. This sorts the statements from longest running to shortest running. I prefer this format because I can concentrate on the top two or three statements for the most impact.

SQL> alter session set sql_trace=true;

Session altered.

SQL> alter session set tracefile_identifier='mytest';

Session altered.

SQL> select count(*) from xyz_master;

COUNT(*)
----------
       4

SQL> select count(*) from xyz_detail;

COUNT(*)
----------
      16

SQL> exit

D:\oracle\product\admin\xp10\udump>dir *mytest*
Volume in drive D has no label.
Volume Serial Number is 70AE-6E52

Directory of D:\oracle\product\admin\xp10\udump

07/04/2005  08:11 PM            38,785 xp10_ora_1544_mytest.trc
             1 File(s)         38,785 bytes
             0 Dir(s)  14,751,383,552 bytes free

D:\oracle\product\admin\xp10\udump>tkprof xp10_ora_1544_mytest.trc foo.out sort=prsela,exeela,fchela

TKPROF: Release 10.1.0.2.0 - Production on Mon Jul 4 20:16:53 2005

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

D:\oracle\product\admin\xp10\udump>dir /od foo.out
Volume in drive D has no label.
Volume Serial Number is 70AE-6E52

Directory of D:\oracle\product\admin\xp10\udump

07/04/2005  08:16 PM            21,368 foo.out
             1 File(s)         21,368 bytes
             0 Dir(s)  14,751,358,976 bytes free

D:\oracle\product\admin\xp10\udump>

Things you will want to know about tracing

• The timed_statistics init.ora parameter must be set to true in order to get any meaningful timing data out of tkprof.


• I prefer to set the max_dump_file_size init.ora parameter to either unlimited or a really large value (2G). If you're going through the extra work of tracing a process you don't want to potentially miss the most important part.

For more details
Oracle-Base.com
Pete Finnigan

WEP #2

Some people just don't get it. I'm here at a laundromat and working on a post for my blog when my wireless icon tells me I'm connected to "linksys-secure". Secure. Yeah right.