30 November 2006

It's for the childrens.

Give to Child's Play. One of the noblest causes I can think of.

Here's a recent plea from Gabe:

"If you haven't picked anything up yet please give it some thought. It might just be a couple coloring books or even a package of batteries. Trust me when I tell you that anything, no matter what it is will be very much appreciated by these hospitals. Don't think that just becasue you can't get the kids a PS3 you can't help. Some simple things like Playdough actually need to be thrown away after each use. Certain items that the kids like to play with collect germs and can only be used once. They can never have too many containers of the stuff. Or too many batteries for that matter. Or coloring books or markers. Believe me when I tell you that you could spend five dollars and really make a difference."

today in #oracle

[10:09] <rizzo> ok the ts_free script I have shows total bytes at 871,713,865,728
[10:10] <rizzo> yeah one tablespace alone is 223gb
[10:25] -->| gehdan (n=gehdan@xxxxxxxxxxxxx.de) has joined #oracle
[10:43] <teite> rizzo: ok thats quite big ;)
[10:43] <rizzo> yeah it's a pain in the ass
[10:55] <gehdan> what are you guys talking about?
[10:55] <gehdan> sforget i asked, don't really wanna know :)

28 November 2006

How The Oracle Database Processes SQL Statements

Eddie Awad posted (in June) a nice flowchart of what Oracle does when it processes a SQL statement. Of particular import is the note in step 2 (parsing):

"If a similar SQL statement exists in the shared pool, Oracle skips the parsing step. A SQL statement is parsed once no matter how many times the statement is run. As you can see, parsing does many things and consumes time and resources. You should always aim at minimizing parsing when writing SQL."

How do you minimize parsing? Bind variables! Now go re-write all of your applications to use them.

Bind Your Variables or Die

NOTE: I'm re-pasting this from an internal wiki page I wrote up for our new developers who may not be aware of such a thing as bind variables and how they can greatly improve performance and scalability.

Most know that using bind variables help greatly with performance when dealing with an often-used query, such as a customer lookup. Read Mark Rittman's article, which does a great job of explaining all there is to be explained in that regard.

A quick summary that Oracle has to re-parse literal queries everytime, generating new query plans every time and storing that processed query in memory. With bind variables, only one such process, parse and memory storage takes place and that information gets re-used when that bind query is called again with different bound values. This saves on the CPU having to re-parse and re-plan the query, and saves on memory by not having to store yet another query plan.

To get an idea of how important DBAs consider the use of bind variables, read Tom Kyte's thrashing of a user who was unlucky enough to incur his wrath.

Read about Using Bind Variables to Guard against SQL injection. SQL injection exploits are one of the most common attacks on database-driven websites, as attackers would exploit the fact that a SQL query such as user validation is built with literals, and they could turn this query:

WHERE username='dts'
and password='foo'


WHERE username='dts'
and password='foo' or 1=1

Using bind variables prevents such exploits from happening due to how the values are substituted.

The Onion Speaks Sooth

Wal-Mart Sales Disappointing: “You can only give someone a T-shirt of the Tazmanian Devil waving an America flag so many times.”

18 November 2006

Vote Don: early and often

My entry in the Avatar Pumpkin Carving Contest is up. Follow that link, mine is the entry just under the heading "Don -- Aang". Then click on 2006 Voting and vote for me and four other not-as-good-as-Don entries.


"Hi Don,

I'm sorry, but your pumpkin did not win in the DH Avatar Pumpkin Carving Competition. You came in 30th place (out of 33), and tied with Tara and Zach's entries.

Thank you though for participating in my site's competition. And if it's any consolation, I thought your Aang carving was really cute. =) I hope you have a nice week and Thanksgiving, and the season finale of Avatar is on Dec. 1st."

17 November 2006

Hurra Torpedo

"Hurra Torpedo is the world's leading kitchen appliance rock group."


When I strolled into the office this morning, a copy of this book was waiting on my chair. Jonathan Lewis is the God of understanding the Oracle cost-based optimizer (CBO), and "Cost-Based Oracle Fundamentals" is his Bible, Ten Commandments, and the Holy Grail fused together. Plus JL has an awesome beard.

So books I'm concurrently reading are the Oracle 10g DBA Handbook, the Oracle 10g OCA/OCP exam guide, Enterprise Javabeans 3.0, and now this. I don't even miss WoW. On a side note, a co-worker had his account banned for being a dirty botter. HAW.

15 November 2006

Functions Can Trump Indexes

It seems that I'm dense enough to simply read over the first sentence of this post (which I linked to earlier) and not bother to think what it means. Last night I read about it in the 10g DBA Handbook and it stuck, probably because they focused on it with some examples.

Using a function on a B*Tree (normal) indexed column in your WHERE clause means that Oracle won't use that index. For example:

select * from emp
where UPPER(lastname) = 'JONES';

will most likely result in a full table scan, despite an index on lastname. However, you could create a function-based index on UPPER(lastname), like this:

create index emp_upper_lastname on

Of course, the whole point of Jeff's post was to describe an alternative to using a function-based index by using UPPER() check constraints that the CBO can utilize.

The world becomes clearer every day.

14 November 2006

set autotrace traceonly

For those few of you out there more newb than even me, this can make a huge difference. Easy explanation of the power of AUTOTRACE in SQL*Plus, via Tom Kyte. Basically quick and easy way to get query plan and statistical data.

This is usually a sign that you need a better index:
880637 consistent gets
189017 physical reads

Launchy makes Windows (almost) FUN!

See below. No longer will I have to reach for the mouse to open PuTTY sessions!

13 November 2006

GMail Mute feature RAWKS

Read more here.

I'm already using it to "mute" a lot of developer list conversations about things that I'm not interested in but keep popping up in my inbox, like pio's japanese translation.

10 November 2006

Constraints and the CBO

In my recent research into Oracle's CBO, I had read a snippet from Jeff Moss' blog about constraints being particularly extra helpful to the CBO. Tom Kyte had two consecutive blog posts about this topic in the beginning of the year. The first applies to general purpose and OLTP databases, whereas the second is geared specifically towards data warehouses using materialized views.

Tom even breaks out the RELY hint to enable the use of constraints on large warehouse tables so they won't kill bulk load times. I'll see if I can try to implement this on our warehouse side. Unfortunately, now that we have a handle on the previous load/CBO problems, I have a priority java app that I need to get done before I can get too crazy with Oracle.

Compound Primary Keys Must Die

It turns out that a lot of the headaches of the past few weeks at work are all due to an incredibly shitty primary key on a large warehouse table. Before I get into specifics there, I'd like to formally proclaim my allegiance to the use of surrogate keys and my hatred for compound keys. Many of the Oracle "gurus" (including Tom Kyte) agree as well. You can use the Google to find reasons to use them. I'll list smaller indexes on both the table in question AND tables with foreign keys as a big reason off the top.

Now, on this table at work, which I'll call FOO, there is currently a 10-field compound primary key. Yeah, that's right. I take some shame in having this in my database, although the design was inherited, and I'm just now feeling comfortable and confident enough to start suggesting radical changes to our developers. So this primary key begins with columns LOC, CUST_NO, YEAR, and MONTH. Then it includes 5 fields that are rarely, if ever queried (I turned on FGA on this table and verified that). Then the last field is the relatively new FOO_SEQ_NO, which is unique for a LOC, CUST_NO, YEAR and MONTH. Virtually every query against this table uses LOC, CUST_NO, YEAR and MONTH. FYI, this table is also range partitioned on YEAR and MONTH.

The problem is that after we load a new large batch of records from a previously unloaded market into the current partition, the optimizer no longer uses the big PK index, instead choosing to use a second index which doesn't even use CUST_NO. This causes a LOT of I/O and painfully slow query times, until we are able to gather_table_stats() on it, which can take up to 2 hours.

What became painfully obvious after I turned on fine-grained auditing, is that 95% of the queries only use LOC, CUST_NO, YEAR and MONTH, so the massively compound primary key index is almost 50% useless. On a development instance, I dropped the PK constraint and index and built a new non-unique index on just LOC, CUST_NO, YEAR and MONTH. I do plan to add a unique constraint on the former PK fields just to keep things sane in case some part of the application relied on that. The sample query ran fine before and after the dataload, using the new index right away without having to gather stats.

Now, the case of the FOO table was an EXTREME of an ugly compound key/index, and the problem wasn't so much the compoundedness of it as much as the index not being reflective of what user queries needed. But I still loathe compound keys.

07 November 2006

Reverse Key Indexes

So I started reading the Oracle 10g DBA Handbook because I've deluded myself into thinking that I'll just memorize everything in there. The first chapter seemed to cover very basic stuff that even I had a grasp on, but it didn't take long for me to learn something new. 18 pages in, I read of the magic of reverse key indexes. This is a pretty fundamental index type that I had somehow never heard about before this, so I thought I'd share it here.

A reverse key index does just that, stores the indexed values in reverse, so an id value of 123456 would be stored as 654321. One immediate advantage in doing so is reducing contention for blocks by distributing inserts across all leaf keys of the index. For example, inserting orders 1234, 1235, 1236 and 1237 would normally probably all go into the same block (if space is available), and the writers would wait for each other to finish with it. With a reverse key index, they would be spread to 4321, 5321, 6321 and 7321, which are not close together and probably not on the same node.

For a complete analysis, the link to Jonathan Lewis' FAQ above is quality stuff.

01 November 2006

Save Your Wet Cellphone

Saw this via Lifehacker. I left my practically new Moto RAZR in my backpack during Ultimate when a crazy flash thunderstorm suddenly filled the outer pocket of the backpack with water. Apparently the large inner area of the backpack was water proof, allowing the water to pool in the outer pockets.

I'll try this soon, hopefully I get the RAZR love again. I actually did have it mostly working. Some buttons were unresponsive and I missed a couple of calls, so I switched to an old marketing model v710.

Update: Since it has been 3 months since the incident, I had my doubts as to what an alcohol bath would do. So I just turned the phone on and all features seemed to work (including previously unresponsive buttons). I switched service back to that phone and am once again living the high life. You can rest easy now, good readers.