27 April 2007

More Log Buffer Pity

The latest Log Buffer is out, and this blog gets a mention for doing little other than posting about an older Feuerstein article. Honestly, I'll gladly accept any non-negative mention of this site, so keep 'em coming!

Writing SQL Developer Reports

Much like hali, I have a preternatural dislike for Toad. There really is no valid reason for it, though, so I won't try to justify it. I just don't dig it. Equally unjustifiable is my preference for Oracle's own SQL Developer, especially now that it gives me a point-and-click interface for killing sessions. Well perhaps I can provide some justification in that Oracle SQL Developer comes packaged with ojdbc so I don't need to install anything else to run it other than the JDK. It's also completely free.

One thing that I had yet to do was add some of my own reports to the mix, and thanks to my previously-mentioned savior, Lewis Cunningham, I've begun that. Lewis has a nice PDF on how to write reports for SQL Developer, from basic table output to child (drill-down) reports to pie/bar/line charts.

25 April 2007

ASMM Pitfalls

Earlier this month, Jonathan Lewis revisited an earlier post of his about the problems of automatic SGA management. It was a great read, and one all too near to (and a wound so fresh in) my heart. I've yet to actually find a bug number to confirm the suggestion, other than Note 396940.1 in Metalink. It lists as a possible problem:

High parse ratios

It is important to identify what could produce high parsing calls:

- Use of dynamic plsql
- Execution of DDL statements during periods of high workload. Every
time a DDL statement is executed, it will cause invalidation of all
the statements referencing the object involved. Next time a sql
statement referencing the object is executed, it will have to be
reparsed and loaded into the shared pool.

Typical operations that cause this situation is the execution of:

- Grant/revoke command
- Alter view
- Alter package | procedure
- Analyze table |index
- DBMS_STATS
- Truncate table
- Alter index
- Alter table move

If an ORA-04031 error is associated with high parse ratios, you will
also see latch contention for the library cache latch as well as
indications of lots of invalidations and reloads in the Library Cache
statists in a Statspack or AWR report.
Seems to fit my situation perfectly, although 2:30 AM isn't a high usage period for me, which might suggest a bug after all.

Feuerstein on Error Tracing

I'm still catching up on some readme bookmarks, and find myself once again face-to-face with PL/SQL sensei Steven Feuerstein. This man has some of the best headshots I've ever seen.

This time Steven is dropping some science regarding the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function, new in Oracle 10g, for better exception handling in PL/SQL.

seilerwerks.blogspot.com is dead! Long live ora.seiler.us!

OK it's not really dead. I've just set up a CNAME front-end.

It seems that Barracuda installations default to blocking emails with seilerwerks.blogspot.com address mentioned anywhere in them, with an "Intent" classification (which I'm told means that the intent of the website is questionable). This means that ever since I put my blog URL in my email signature, a lot of people have been blissfully unaware of my messages. I've confirmed this with some people whose ISPs or companies use Barracuda.

So rather than NOT shamelessly plug my blog, I've just decided to also plug my vanity and create a seiler.us subdomain. Blogger claims it will redirect the old URL to the new URL, so you shouldn't need to really do anything. I'm not sure if RSS feeds will break though.

Apologies for the inconvenience all around.

Don.

BEST HELP REQUEST EVAR!!!!!11

Found this from Tom Kyte. The subject alone tells everyone that this dude is serious and you are lucky to even have the chance to help him:

URGENT URGENT PLZ READ B4 OTHERS VERY URGENT NO TIME WASTERS

The poster uses an perfect storm of IM speak, arrogance, stupidity, and the caps lock key to compose this fantastic post. He responded later in the thread and offered this bit of inside information that I'm sure will tempt any would-be helper:
I KNOW THE ACCESS DATABASE VERY WELL, AND IS FAR BETTER THAN ORACLE. ORCALE IS TOO MANY DOCS
Yeah, too many docs! How can he possibly be expected to read them all, or even just the one that he might need? But seeing as though those jerks in the OTN forums just don't get it, he was forced to tack on this addendum to the original post:
Message was edited by:
user571114

PLZ DO NOT WASTE MY TIME
So everyone hurry on over and help this guy out.

24 April 2007

Upside-Down-Ternet

Neighbors leeching off of your wi-fi? Sure you could just encrypt it or set up MAC filtering, or you could just really screw with them.

23 April 2007

All Quiet on die Seilerwerks

Just wanted to drop a short note regarding my prolonged gap in posting. As I stated earlier, I was working all weekend on April 14-15, as well as the night of the 16th, performing RMAN duplications of our production database to new 64-bit hardware on ASM for our new development instances. This should have been a smooth process (which it was when I tested it a month ago), but problems with our disk (which have popped up even since) really made things painful. I'll try to write-up exactly what the problems were if I can pry the details from my SA.

So I just fought to stay sane through the rest of the week, and then my wife and I decided to take an impromptu vacation on Thursday and Friday to get away from it all and have a nice break together as a family. Top that off with a trip to the zoo yesterday, and it was a fun weekend for the kids.

Oracle PL/SQL Best Practices and Common Mistakes

Eddie Awad dug up some videos from Quest Software of PL/SQL-god and Borat-hater Steve Feuerstein talking about the best and worst of PL/SQL programming.

The videos offer some tasty advice, including tips on writing cleaner, more modular code, and use of BULK COLLECT and FORALL to improve mass data processing performance, and use of table functions. It's even worth sitting through the Quest infomercial towards the end of each video.

15 April 2007

Quick Review of Oracle on CentOS (RHEL) 5

Hampus Linden, the Swedish werewolf in London, has posted a light review of Oracle on CentOS 5, the latest release to match the new RedHat Enterprise Linux 5. CentOS is basically RHEL without the paid support, great for testing/development environments.

SQL for the Childrens

Gary "Igore" Myers, upon hearing the glad tidings of Pete Finnigan's parental status, has produced a handy (if not a bit cheeky) SQL primer for the kids. Maybe I can teach my 21-year-old brother-in-law some SQL now as well.

sprintf() in PL/SQL

René Nyffenegger has hacked up a PL/SQL implementation of sprintf(). I haven't done too much in PL/SQL, but I didn't have much a problem with lpad() and rpad(). But who doesn't love good old sprintf() otherwise?

Christo on Aligning ASM Disks on Linux

As I sit here on a Sunday morning waiting for some disk issues to be resolved in my datacenter, I took the time to catch up on some pages I had marked for later reading. One was a post by Christo Kutrovsky from the Pythian Group, about aligning ASM disks on linux, to avoid misalignments due to odd default offsets in linux that might cause I/O for relatively small amounts of data to be inefficiently split across disks.

Also now I can't help but look at "Christo Kutrovsky" and think of Krusty the Clown, a.k.a. Herschel Krustofski.

12 April 2007

Optimizing through Understanding

Early last month Jonathan Lewis put up a great web presentation called "Optimizing through Understanding." I only just got around to viewing it, and it's packed with good stuff on breaking down queries and rewriting queries to find and hint for the best query plans available.

10 April 2007

Heart of Darkness

I had heard tales of its existence. A dark, soul-less being whose only purpose in this world was to cause pain and misery. It strikes without warning and without prejudice. By the time your eyes come upon it, it is already too late. It has but one name, and few dare speak it. But hear me now when I say that I have seen the beast, and live to tell the tale.

It was this past Monday evening. I was online changing out redo log groups to change from 20M logfiles to 100M logfiles. I finished and all was well. An hour later I checked back and peeked into the alert log, and there it stood. It literally sent a shiver down my spine and made my stomach turn. I will show you what I saw now. Women and children should probably leave the room.

Dear readers, GAZE UPON YOUR DESTRUCTION:

ORA-00600: internal error code, arguments: [17281], [1001], [0x79E0FFE0], [], [], [], [], []

For those of you still reading and have not had your retinas burned out at the mere sight of its name, I shall now tell of my encounter and eventual escape from ... the horror.

Naturally my first thought was that I had somehow SRSLY messed up the redo log group changeover. I went over my steps and log output, and nothing was wrong. The ORA-00600 didn't come up until 20 minutes after I was done. So I did some more log perusing and the 00600 had been coming up earlier that day, and in fact had begun at around 9:30 PM on Sunday evening, coincidentally after some application upgrades.

Metalink note 39361.1 told me that this particular error had to deal with invalid cursors being closed. It also said it should have been fixed in Oracle 10gR1. The stack trace told me that it was JBoss doing it. Looking at timestamps, it was JBoss's IdleRemover that was generating the ORA-00600. BUT WHY??!!?!

The stack trace also had "ORA-01403: no data found" in it, but I couldn't find any mention of this error in the alert log or in the application logs. Then, as if sent by angels on a ray of light, Lewis Cunningham posted an article on the fact that Oracle doesn't report NO DATA FOUND (ORA-01403) exceptions when it should. I quickly confirmed this in both sqlplus and a quick jdbc application.

My theory now was that the JBoss application was generating an ORA-01403, but Oracle wasn't throwing the error back. Instead, the JBoss cursor was left invalid. Then when the IdleRemover awoke to clean up connections, it generated the ORA-00600 when it got to that particular connection. Perhaps not full of technical argument, but it made sense in my head.

One of the developers mocked up a similar test to run through JBoss on his workstation. He ran the code to call a stored function that would raise an ORA-01403, and then we waited for the IdleRemover to kick in. Then it happened ... NOTHING.

I was a bit crestfallen, until a minute later the developer noted that he was still using the ojdbc14.jar from 10.2.0.2, and we had upgraded our jars in production to 10.2.0.3 this past Sunday evening. Sure enough, we were able to duplicate the error 100% when using the 10.2.0.3 ojdbc14 driver.

So JBoss 4.0.5 GA with ojdbc 10.2.0.3 and Oracle RDBMS 10.2.0.2 don't jive. It doesn't help that Oracle isn't throwing ORA-01403, which I think is a bug, as does Lewis. Nor does it help that our application did not appear to notice the ORA-01403 (which it wouldn't be able to) or ORA-00600. So we're reverting back to ojdbc 10.2.0.2 for now. We'll have to find out why JBoss/Hibernate is triggering the ORA-01403, and then see if Oracle knows why the 10.2.0.3 jars are triggering the ORA-00600. Of course it could very well be the case that the ORA-00600 should be triggered, and that the 10.2.0.2 jars are mistaken for not doing so. The list of bug fixes in ojdbc 10.2.0.3 don't mention anything with regard to that, though.

UPDATE: Eddie Awad provides some more insight into Oracle's behavior with regard to NO DATA FOUND.

09 April 2007

Caveats for renaming a server running ASM

I have a small tower running Oracle 10.2.0.3 on CentOS4 under my desk that I use as a personal sandbox for playing with Oracle so I'm not always futzing with developer instances. This was where I first played with ASM, etc. However, I rarely actually use this machine from day-to-day.

Recently, I needed access to a 10.2.0.3 instance to see if a bug still persisted. All of my production and development instances are on 10.2.0.2, so this box was called to duty. However, when I tried to start up cssd (required for ASM), I got errors referring to configuration files missing. (I'll try to find the exact error if I can dig it up).

The trick is that these files were named based on the server hostname. I had recently renamed this box to fall in line with our internal policy of naming servers after Thomas the Tank Engine characters. So I needed to reset the OCR key to use my new hostname, and all was well again.

Well, all was well after I also installed the updated asmlib RPM for the kernel which I thoughtlessly updated as well.

Things You Know: Tom Kyte is Under Siege

A couple of weeks ago I "attended" a webcast from IOUG by Tom Kyte entitled "Things You Know". You can find the presentation materials online (you might need these codecs). It was a great session with Tom focusing on the dangers of what people think they know to be true versus what reality is. "Trust, but verify" rings loud and clear throughout.

One thing that I wrote down in my notes was a reference Tom made to "Steven Seagal and his many great movies." I don't know Tom's personality at all, but he seems like a pretty straight-shooting guy, so I'm not sure if this was sarcasm or not. It seriously has me worried. At least once a day (usually during my 45-minute commute) I wonder if he was serious or not. Am I the only one?

06 April 2007

Log Buffer #39: The April Fool

Hullo and welcome to this week's edition of the Log Buffer! A very special thanks to Dave Edwards at Pythian for this opportunity. Now let's begin ...

The man, the myth, the legend Tom Kyte shared some insight into a new SQL feature under consideration for Oracle: pattern matching over partitioned, ordered rows. He's asking for readers to offer up their opinion, so get to it! Pete did so on his blog as well.

Tom also just posted a lengthy write-up of some of the caveats to be aware of when using "explain plan".

Sachin, broadcasting live from his blog Oracle Online Help, has posted a nice primer on the differences between three join types that anyone who has ever look at a query plan will be familiar with: nested loops, hash joins, and sort-merge joins.

Oracle ACE Chris Foot at dbazine.com generously shared with us his top 10g tuning tools.

Kevin Closson posted a postive preview of things to come in Oracle 11g, as well as Oracle's approach.

Pete Finnigan, via his Oracle Security blog, posted two bits of worrisome information: a 0-day Oracle exploit and an Oracle rootkit are out and about.

Those of you interested in Oracle client-side load balancing might want to head over to yas' Oracle Today.

Robert Vollman at oracleblog snuck in a few Oracle beefs, and also gives a positive mention of this blog! Huzzah!

Coskan Gundogar, from Istanbul (not Constantinople), has some questions for new graduates that think they'd like to be DBAs.

Switching away from Oracle to MySQL, Brian "Krow" Aker posted some hot news about a Postgres storage engine for MySQL. It seemed an open-source enthusiasts utopia, until everyone realized what day it was (although I was ready to buy one of these).

The folks at HackMySQL share with us a script for checking databases or tables for unused indexes: mysqlidxchk.

The aforementioned Tom Kyte even shared a recent MySQL experience with us, and Pythian's Paul Vallee shared a similar experience. Neither bode well for the MySQL fans in the audience.

Paul Tuckfield from YouTube will be giving a talk at an upcoming MySQL conference on how YouTube scales with MySQL.

Jeremy Cole opened our eyes to a more efficient alternative to MySQL's SHOW VARIABLES.

Fans, or even casual acquaintances, of MySQL replication will find joy in Peter Zaitsev's coverage of the MySQL Master-Master Replication Manager. You'll also definitely want to help out MySQL's own Robin Schumacher and give him some feedback on replication monitoring.

Our PostgreSQL fans will have surely seen Robert "xzilla" Treat's tip at PlanetPostgreSQL about monthly log rotation with PostgreSQL's built-in logging.

Both "The SQL Doctor" and Kimberly Tripp posted about Microsoft SQL Server 2005's online book search.

Decipher Information Systems' blog had an interesting post contrasting MS SQL Server and Oracle with regard to computed columns.

Craig Mullins, host of the DB2 Portal Blog, wrote about a couple of tricks and treats in DB2. First up was mimicking ROWNUM in DB2, followed later in the week by a treatise on INTERSECT and EXCEPT in DB2. But he didn't stop there, and returning readers were rewarded with a review of ORDER BY and FETCH FIRST in DB2 subselects. And just when I thought I was out, he pulls me back in with a write-up on DB2's native XML support!

Over at his ITtoolbox blog, Willie Favero asks a question that probably shouldn't be asked.

And I think we'll end this edition of the Log Buffer right there. Probably fitting that I host the April Fool's week issue! I do apologize for the lack of PostgreSQL coverage, not a lot came across my desk this week. It only serves to remind me to make some time to play with PostgreSQL!

I hope you've enjoyed your stay at my meager blog and will return again to read about my many misadventures as I stumble into more Oracle parameters that I've let go untuned for years! For those of you who have made it this far, I'll reward you with some footage of my son's horse impression. Have a great weekend!

05 April 2007

200 is such a hard number to work with, after all

Will in #wilug shared this fun Microsoft bug. From the report:
This problem occurs if the fully qualified domain name ( FQDN) of your computer is exactly 200 bytes long. During logon, one character more than the 200-byte allocation is written and heap corruption occurs.
Of course, the suggested workaround is just as entertaining:
To work around this problem, rename the computer so that it is one character shorter. If you do so, the FQDN is less than 200 characters long and the heap corruption does not occur.
Yes, this is old and was fixed in a service pack years ago.

04 April 2007

Using NOT LNNVL() is NOT GOOD()

In re-working some old reports that I'm still maintaining, I decided to clean up instances of "WHERE foo > bar or foo is null" with some LNNVL() loving. Given the nature of my query and of LNNVL(), I plugged in LNNVL(foo > bar) and prefaced it with a NOT, since LNNVL() will return false when I want thing to evaluate to true. This was met with furious anger:
ORA-03113: end-of-file on communication channel
It kills my database connection! I headed over to Metalink and found Bug 4211527, reported over 2 years ago on 10gR1, with no workaround. I confirmed the bug is still around in 10.2.0.3. Back to the OR statement for me.

03 April 2007

I have not yet begun to fight!

So sayeth John Paul Jones (not this one), so sayeth I.

I've been taking steps to make sure my developers know of the many benefits of using bind variables, and the punishments for not using bind variables. This is paying off in the newer code coming out, but it will take a while to re-write some of the older applications.

Today, while tracing another query, I saw that one of the back office applications that dealt with batch rating was using a query without bind variables. In the timeframe of 1 hour this morning, the same query with a different literal value was called over 21,000 times. The same app was bulk issuing two other queries using literals as well. Let's just say that it was made certain that this needed to be changed.

PGA: Not just for Caddyshack 2 fans

Ahh ... Jackie Mason. The poor man's Rodney Dangerfield. The pride of Sheboygan, Wisconsin (other than meat products).

But, no, today I'm not talking about golf (nor will I ever, if I can help it). I'm talking about Oracle's PGA, the Program Global Area. The PGA is a private chunk of memory dedicated to each server process (i.e. dedicated user processes). It is used by the server processes when doing sorting or hash-joins, among others, and it is definitely something you want to pay attention to and properly tune for your database needs.

After doing this reading, I calcuated that my PGA should probably be at least 256MB for sure, probably closer to 512MB. Note that I hadn't touched or directly set it prior to this. As soon as this output appeared on the screen, I doubled over in self-disgust.

SQL> show parameter pga_aggregate_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 24M


I then went to see what the advisor table had to say. Bear in mind that you want CACHE_HIT_PERCENTAGE as near to 100 as possible, and you definitely want ESTD_OVERALLOC_COUNT to be 0.

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
---------- -------------- --------------------
12 52 30444
18 52 30444
24 75 30444
29 75 30444
34 75 30444
38 75 30444
43 75 30443
48 75 30423
72 75 30377
96 75 30309
144 75 29990
192 76 29335


Yeah. Neither goal is even on the charts. So my plan now is to bump it up pga_aggregate_target to 512MB and see how those numbers settle out in a week. I might even want to go higher in the end. Google seems to imply that 24M is indeed the default pga_aggregate_target value, at least it was when I set this up as a new 9iR2 instance and imp'd the data over.

To be continued ...