So Long

It’s vacation time again — off to Spain to visit the parents and score some free baby sitting for a couple of weeks.
I always take over a compendium of digital photos, taken since the last vacation, and this time I’ve winnowed it down to a shade over 2Gb — and that’s not including those taken when No. 1 son “found” my camera and went on photographic rampages round the house before we were awake. I must have over two hundred photos of our cat cowering in some corner, or running off down a hallway.

Really though I’m my own worst enemy in that regard. I just bought another 1Gb memory stick, so I’ll be coming back with another 2Gb.
My parameter diagram is coming along nicely. I’m grouping the parameters according to the functional catagories listed in the documentation, although there are a few glaring errors and omissions, and colour-coding by their modifiability (session, system, system deferred etc). Keeping control over all the little dependency lines is a kind of fun, I don’t mind saying.

Here’s a thumbnail of it so far, and until I return in a couple of weeks to polish it off I’ll say “Hasta banana” to you all, and “work smarter not harder”.

Documentation Without Authentication

I read at Justin Kestelyn’s blog that the OTN documentation libraries are now accessible without authentication. Good news.

Burleson Forum Hacked?

I got a cryptic email from the forum administrator email at the Burleson Consulting forum, suggesting that it had been hacked. Seems like the forums are gone, though the software is running.

Oracle 10g Release 2 Parameter Dependencies

A wise man once said that one of the clear signs of Compulsive Tuning Disorder (CTD) was an extensively tweaked set of initialization parameters. While it's probably a stretch to say that very few parameters needs to be changed on the creation of a new instance we do seem to be moving in the direction of relying more on default values (IMHO).

Now, the initialisation parameters for 10gR2, indeed for any version, are a rats nest of inter-dependencies. Change X, and Y and Z also change. Usually the dependency is that the default value is determined either in part or in whole by the value of one or more other parameters. To help understand this and because a picture is worth a thousand words, I've knocked up a couple of network diagrams (or whatever you want to call them) to illustrate this, so that you can see these relationships at a glance.

In some cases the relationship is very complex, such as that between large pool size and DBWR IO slaves. In other cases the value of one parameter establishes an upper or llower limit for another, or X is affected only by Y dependent upon values of Z. I've used different connector styles to try and illustrate this.

It's still a work in progress mind you, but I thought I'd throw out an initial version for comments.

Future enhancements …

1. Code the parameters by their dynamic/static type
2. Include links back to the documentation
3. A triple-check of the accuracy
4. Add other types of inter-dependency, such as "X value used only if Y value is set to …"
5. Erm …
6. Anything else that people can think of.

Disclaimer: these are only as accurate as (i) the documentation (ii) my reading of the documentation (iii) my illustrative capabilities. Read the documentation for validation.

Click on the little pictures to get the full-size image, and feel free to make suggestions and whatnot.

Optimizer Features

Just a handy link for the optimizer_features_enabled parameter — I'd forgotten all about this handy page, which correlates optimizer features by Oracle version.

World Cup Loyalties

So,the wife has quite the dilemma — dual Italian-US citizenship, married to an Englishman, brought up in Italy, but also an Hossifer in the US Air Force. Tricky stuff, especially tomorrow when her citizenships collide at K-town, home of Ramstein Air Force Base (the largest NATO airbase in Europe). Local support for the US team ought to be pretty energetic, to say the least.Fortunately her work colleagues have made it easier for her by talking up the chances of the American team to such an extent that she can claim to be merely redressing the balance. Italy had better extractum digitum and do a good job of it anyway. National honour is at stake.

Updating the Link at blogs.oracle.com

Does anyone know how to contact whoever configures the links at http://blogs.oracle.com?

Oracle Patent Watch — An Occasional Series

I find database patents to be a fascinating source of information on what is, or what might in the future be, going on behind the scenes or our favourite RDBMS. They are often as good as a text book and can really expand the boundaries of how I think about the system.

Here are five interesting examples I rounded up.

6,457,000: Method and apparatus for accessing previous rows of data in a table

Everyone who has used analytic functions seems to come away with a sense of expanded mental boundaries — they really are the stuff of Great Leaps Forward. Of such things are patents made, and here it is.

"A method and apparatus is disclosed for allowing access to one or more previously generated rows of data. A server receives a statement that requires delivery of one or more rows of data to a client, and designates a particular row of data as a current row within a generation sequence. As the rows of data are being delivered to the client, the server accesses select previously generated rows of data that has already been delivered to the client without designating a new particular row of data as the current row. Once the previously generated row is accessed, data is retrieved from predetermined columns. The data retrieved may be used to perform various calculations that will insert values into the current row prior to delivery of the current row to the client. The statement transmitted by the client may include various parameters, including a default parameter, that specifies a default value to be used by the server. The previously generated row is then retrieved based on an offset parameter specified by the client. If the location determined is outside a predetermined range of addressable rows in the generation sequence, then the default value specified by the default parameter is returned. The present invention may also be configured to use a buffer and manage dynamic memory allocated to the buffer."

Filed July 1999, issued March 2003. What takes these people so long?

6,205,451 Method and apparatus for incremental refresh of summary tables in a database system

Here's an interesting one.

"A method and apparatus are provided for performing deferred incremental refresh of summary tables that are derived from two or more base tables. Incremental refresh is performed by assigning a hypothetical load sequence to the base tables of the summary table. For each base table that contains new data that affects the summary table, a join is performed between

(1) the new data in the base table,

(2) the pre-update state of base tables that follow the base table in the hypothetical load sequence, and

(3) the post-update state of base tables the precede the base table in the hypothetical load sequence.

The results of the join are then merged with the existing summary table to refresh the summary table. According to one aspect of the invention, efficiency is improved by performing the joins for the various base tables in parallel."
I haven't done much work on the refresh of materialized view based on joins of multiple master tables, but when I do I'll be bearing this description in mind. What do you think is meant by "… a hypothetical load sequence …"? Pretend the one with the lowest object_id was the first? Intriguing.

6,496,830 Implementing descending indexes with a descend function

"A method and apparatus is disclosed for implementing descending indexes in a database management system. A key value to be inserted within an index is decomposed into individual data bytes, such as an integer character value, that make up the key value and dictate the location where the key value will be inserted relative to other key values. A first function is applied to the data sequences in order to generate a reversed key value having reversed sorting properties. The reversed key values are then inserted into the index. The resulting index thus contains key values that are sorted in descending order. Additionally, indexes may be created on multiple key columns, with the sub_keys being independently sorted in either ascending or descending order. The present invention also discloses transformations that may be performed by a server in order to properly interpret statements that require the creation of standard or functional indexes in descending order."

Ah, so is that how they actually work then? I've read, through Tom Kyte's work I think, of descending-order indexes being function-based, and this would explain it I guess. Is the function publically accessible I wonder? If it is, is it of use outside of this?

6,377,943 Initial ordering of tables for database queries

Here is one of the issues at the heart of optimisation — in what order should the tables be accessed?

"Multiple initial orderings of tables are used a multiple starting point in a cost-base, cut-off search for a good ordering of tables in processing a database query that specifies multiple join operations. Multiple heuristics may be used to generate the multiple initial orderings of the tables. The database query is executed with the good ordering of tables."
I'd urge a good reading of the description section of the patent as an example of quality writing on the topic.

6,370,524 System and method for processing queries having an inner query block containing a grouping operator

SQL optimisation again.

"Queries having a outer query block enclosing an inner query block, such as a reference to a view or a subquery, with a grouping operator, such as GROUP or DISTINCT, are transformed by merging the inner query block into the outer query block. The FROM clause of the transformed query includes the tables referenced in the FROM clauses of the outer query block and the inner query block. A new GROUP BY clause is created for the outer query block that contains the rowids of tables referenced in the FROM list of the original query and the expressions in the GROUP BY clause of the inner query block. The outer query block can be split if the outer query block also contains a GROUP BY or HAVING clause."

Here's a phrase I must use more often … "magic set transformation" … as soon as I'm more sure of what it means. Actually I think I do, and it looks like a similar technique to that used in the star transformation. So I'm going to start bandying it about forthwith.

Johnny-come-lately’s

I'm officially declaring 14-Jun-2006 at 13:02 MDT to be the official cut-off between the Oracle bloggers who have moved away from Blogger based on their own initiative and those Johnny-come-lately's who are just following the herd.

That being said, I'm glad that Peter Scott has just squeaked in under-the-wire.

Now, who's left? 

Ah ha … what have we here then?

Another defector?