die Seilerwerks

Feeling inspired by the MEEP project, I think I’ll renew my efforts to read my stack of Oracle texts, starting with Lewis’ CBO book. Not sure if I can digest a chapter per day every day, but at least giving the book one complete readthrough will be an achievement. I’ll probably need 1-2 more readthroughs to get things to sink through my thick (perhaps numb) skull.

After that I’ve got some PDFs from tahiti that have been in my README directory for a while now.

Good luck, Jan.

Jonathan Lewis shared an example of joining two tables where the joining field on table is comprised of two fields on the other table. The original poster thought he’d need to re-design his table, but Jonathan proved otherwise with the grace and efficiency of a true Englishman.

Definitely good to know!

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.

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.

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.

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.