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.