29 May 2007

Changing Oracle Database Character Sets

Our big application database has a character set of USASCII7, something left over from before my time here (approaching 6 years now). We've recently seen "errors" in some of the applications caused by characters being loaded into the database that are outside of the USASCII7 domain, namely "ú" and "ñ". A second database used for ETL uses WE8ISO8859P1 for its character set, and that's when we see the errors.

I've begun researching the process of migrating our main application database to WE8ISO8859P1 as well, which seems to be the Oracle default now as well for new database created with DBCA. I've run csscan to give me an idea of how smoothly that conversion would go. I didn't expect any errors, since USASCII7 is a complete subset of the Western European sets, however csscan did report errors for all of the non-USASCII7 characters, which I asked about on the oracle-l list.

Unfortunately no one has answered my last question as to what would happen if I went ahead with CSALTER, and what, exactly, does csscan mean by "lossy conversion". Guess I'll have to try-it-and-see. I'll follow up with my results and parameters.

21 May 2007

An end to my Oracle listener hanging

Over the weekend I have become obsessed with two things: the Allman Brothers Band and Jeremy Schneider's diagnosis of the listener/ONS bug found in Oracle 10.2.0.2. The former due to "Jessica" being in the playlist for Guitar Hero II, the latter because not only did it provide me a rhyme and reason (and workaround) for my Oracle listener hanging, but it is one of the most well-written and presented diagnoses that I've seen, even busting out tcpdump to sniff the wire.

I somehow missed Jeremy's post when he originally wrote it a month ago, and stumbled across it today via Doug Burns. Doug mentions that he first noticed a problem when he saw multiple listener processes. A-HEM:

$ ps -aef | grep lsnr
oracle 32573 1 0 May13 ? 00:16:15 /u00/app/oracle/product/10.2.0/db_1/bin/tnslsnr LISTENER -inherit
oracle 32574 32573 0 May13 ? 00:00:16 /u00/app/oracle/product/10.2.0/db_1/bin/tnslsnr LISTENER -inherit
oracle 32575 32574 0 May13 ? 00:00:10 /u00/app/oracle/product/10.2.0/db_1/bin/tnslsnr LISTENER -inherit
oracle 32576 32574 0 May13 ? 00:00:00 /u00/app/oracle/product/10.2.0/db_1/bin/tnslsnr LISTENER -inherit


At the risk of gushing like a fanboi, I'll just direct you to Jeremy's post while I remain here and aspire to pull it together like he does when faced with such things. Plus I can watch this:

Tuning the PGA: The Follow-Up

I know that all 3 of you have been shivering with anticiPATION at how my PGA tuning experiment went. Today is your lucky day.

I did indeed raise the value of pga_aggregate_target to 512MB. The cache hit ratio rose to near 80%, but the estimated over-allocations was quickly into the tens of thousands again. Looking at V$PGA_TARGET_ADVICE, giving the PGA 1024MB (or 1GB) seemed the next logical choice. That change was made last weekend, and after one week, I'm getting nothing but love:

SQL> SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
ESTD_OVERALLOC_COUNT
FROM V$PGA_TARGET_ADVICE;

TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
---------- -------------- --------------------
128 60 13700
256 64 12475
512 82 5614
768 91 143
1024 95 0
1229 96 0
1434 96 0
1638 96 0
1843 96 0
2048 96 0
3072 96 0
4096 96 0
6144 96 0
8192 96 0
I've put my current level in blue above. I'm satisfied with these numbers at 95% and 0.

15 May 2007

ASM, we hardly knew ye

Not only did I recently wave goodbye to ASMM, I've also parted ways with ASM and have gone back to traditional filesystem-based storage.

The reason is basically that it simply was not playing nice with our Hitachi disk. The SA worked with Hitachi to try and get things working but in the end it was still causing ASM to lock up and crash the instances. We've had such problems from the beginning. So, needing to provide our developers with something stable to work against, the decision was made to go back to normal file storage. ASM would have been a very nice leap for us, and I was really looking forward to seeing our production database running over it.

So that makes ASM and ASMM down. I think at this point Howard Rogers would put a stake in ASSM's heart as well!

14 May 2007

ASMM, we hardly knew ye.

Yesterday I finally busted a cap in ASMM's proverbial ass, setting sga_target back to 0 and manually configuring the various SGA member pool sizes. After my first problems with ASMM, I began reading more and more about "immaturities" with ASMM which I hadn't come across before, particularly with rapid-fire SGA fluctuations thrashing the hell out of things. Perhaps that's what I get when I only read Oracle-published white papers on new and OMGAWESOME Oracle technologies.

So, not wanting a replay of the 2:30 AM ORA-00600 page, I decided to just go back to good old static SGA allocation.

I also doubled my pga_aggregate_target to 1G, based on the advisor table advice. Hopefully overallocations will be a thing of the past! After a whopping 4 hours of normal usage, my cache hit percentage is up from 85% to 97%. Dare to dream!

10 May 2007

Wherefore art thou, Development DBA?

Highlander Doug Burns wrote a great piece last month entitled "What use is a Development DBA?" that really hits home for me. I have a lot of minor and not-so-minor conflicts with the developers on my team (read about "the compound keys" for some perspective).

As Doug says, a lot of the problems could be avoided if developers leveraged their DBA earlier in the design process, rather than sending him a SQL script to run on release night and having the DBA sigh at being relegated to an overpowered operator.

I'm making more of an effort to track down poorly performing production queries and filing friendly bugs for developers with suggested fixes (normally an additional index). But we could do a lot more by having proactive sessions and getting the DBA involved in testing to find these queries before they get into production. Often times I'll get a reaction that the developer had no idea that such-and-such was possible, and I remind them that I'm "right over there" and to feel free to seek me out for any and all database and application design questions.

Of course I'll still be grinding my teeth when I think of those huge compound primary keys polluting my database.

Oracle SQL Developer 1.1.3 is out

Kris Rice announced the release of Oracle SQL Developer 1.1.3, with "about 200 bug fixes in this build."

Full list of bug fixes is here.

I'm kind of a bigger deal around here now

Eddie Awad recently posted about some new Technorati "Authority" rankings for blogs included in his Oracle blog news aggregator, OraNA. Well I gave Eddie the sad puppy-dog-eyes and he graciously has added this blog to the collective.

Now I just need some suckers to add me to their favorites ... Add to Technorati Favorites

08 May 2007

Hiding "Permission denied" errors with `find`

Jon Emmons over at Life After Coffee, a blog of linux tips and tricks, has posted some tips for the "find" command. Nothing new for myself, although his tip of redirecting STDERR to /dev/null to avoid having my terminal polluted with "Permission denied" errors is something that brightened my day, so I thought I'd share it with YOU. <3

07 May 2007

Almost Famous

Following up on my earlier problems with Barracuda boxes, one of the sys admins today noted that the upstream "intent" list also includes foxnews.com and verizon.com. The former made me chuckle.

Now my understanding is that (more or less) when Average Joe User flags a message as spam (which can often happen accidentally), that message is sent back upstream, and any unfortunate URLs in that message get broadcast from the mothership to all the Barracuda installs around the globe. As far as I can tell, Barracuda offers no recourse for small victims like myself. Their support forums are dead and useless, and I haven't received a response from their support email.

The harsh thing is that the Intent List gets BLOCKED at the filter. It doesn't get filtered into the recipient's Spam or Junk Mail folder. They simply do not receive it and will have NO idea that mail intended for them is being blocked. Nor did I as the sender get any kind of message that my mail was being blocked, but perhaps GMail hides this. Very frustrating.

04 May 2007

pidgin (former gaim) 2.0 released

After a long development cycle (although some of it legal-based), everyone's favorite IM client, pidgin is out for the downloading. The official site, pidgin.im, is currently down due to a major slashdotting, but the sf.net download is still up. Time for me to update some stuff.

I love the look/feel in the Windows version compared to gaim 2.0 beta 6. Congrats the pidgin devs on a job well done.

02 May 2007

Back to Basics: Joins

Eddie Awad has an excellent series on the various join syntaxes, demonstrating both in the "old" Oracle syntax as well as the standard ANSI SQL92 syntax. Now you can get your head around inner vs outer joins and all the rest!

I'm going to make an effort to switch to the ANSI syntax for all the reasons that he lists.

Multi-Table Inserts: A Serendipitous Tale

I was doing a bit of studying last night when the material made a passing reference to Oracle's INSERT ALL and INSERT FIRST, and then had a review question on it. So this morning I set out to do a bit of learning, and was pleased to find that not only had Pythian's Babette Turner-Underwood recently written on the subject, but that Andrew followed up on her post with another creative use.