30 November 2006
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."
[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 (firstname.lastname@example.org) 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
"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.
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:
SELECT * FROM users
SELECT * FROM users
and password='foo' or 1=1
Using bind variables prevents such exploits from happening due to how the values are substituted.
18 November 2006
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
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
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
This is usually a sign that you need a better index:
880637 consistent gets
189017 physical reads
13 November 2006
10 November 2006
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.
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
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
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.