As some of you probably already noticed, there was a thread on AskTom discussing the scalability tests I did back in 2007. You are welcome to read the entire thread, but in a nutshell, Tom Kyte claimed that my tests did not reflect how one would use the result cache in the real world.

What is “real world?”

Of course, the important question is whether I tested a feature in a way it was never designed to be used, or whether someone is just trying to make an excuse for poor scalability results by defining “real world” in a way that makes my tests inappropriate.

A new feature

What do you do, then, you first see a new feature? You read about it in the documentation, and then you test it in order to compare what you have read with what you have in reality.

What the documentation tells us

Open the Performance Tuning Guide and go to 7.3.1.4 Result Cache Concepts:

When these queries and functions are executed repeatedly, the results are retrieved directly from the cache memory. This results in a faster response time. The cached results stored become invalid when data in the dependent database objects is modified. The use of the result cache is a database-wide decision.

All it says is that you have to have repeatedly-executed functions and queries to get faster response time. It says nothing about what kind of queries or functions. It also suggests that the result cache should be used database-wide or shouldn’t be used at all (which is perfectly sound according to Jonathan Lewis’s Rules for Hinting).

Now skip up to 7.3.2.7 Use of Result Cache:

OLTP applications can benefit significantly from the use of the result cache. The benefits highly depend on the application. Consider the use of the PL/SQL function result cache and the SQL query result cache when evaluating whether your application can benefit from the result cache.

It clearly says that result cache is perfectly appropriate for OLTP applications. They leave a backdoor with the words, “depend on the application” but, yet again, they say nothing about what kind of OLTP applications.

(more…)

Karun Dutt and I managed to get DBD::Oracle 1.21 to install on a 64-bit Linux OS against the Oracle 11 full client. Here’s what we did.

As root, we downloaded DBD::Oracle from CPAN.

# perl -MCPAN -eshell
cpan> get DBD::Oracle
...

We replaced the distribution makefile with: http://svn.perl.org/modules/dbd-oracle/trunk/Makefile.PL (this is the latest Makefile.PL).

# cd /root/.cpan/build/DBD-Oracle-1.21
# export ORACLE_HOME=<actual value of Oracle Home>
# export ORACLE_SID=<actual value of ORACLE_SID>
# export ORACLE_USERID=<a working ORACLE_USERID>
# export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME
# perl Makefile.PL
...
# make
...
# make test
...
# make install
...

It works!

Contemporary software engineering models include many loosely-defined layers. Database developers might help with other layers, but for the most part a database administrator’s domain is the persistence layer.

• Presentation


• Application


• Business Logic


• Persistence (also called Storage)

The Daily WTF has an article on The Mythical Business Layer makes the case for not separating the business layer and the application layer:

A good system (as in, one that’s maintainable by other people) has no choice but to duplicate, triplicate, or even-more-licate business logic. If Account_Number is a seven-digit required field, it should be declared as CHAR(7) NOT NULL in the database and have some client-side code to validate it was entered as seven digits. If the system allows data entry in other places by other means, that means more duplication of the Account_Number logic is required.

It almost goes without saying that business logic changes frequently and in unpredictable ways. The solution to this problem is not a cleverly coded business layer. Unfortunately, it’s much more boring than that. Accommodating change can only be accomplished through careful analysis and thorough testing.

I will call this merged business/application layer the “functional layer.”

The serious scaling requirements posed by most applications these days call for partitioning, clustering, sharding or some other term for “dividing up the data so it does not become the bottleneck”. Enter the “architecture layer”.

“Wait a minute,” I hear you asking. “Isn’t that just the persistence layer?”

Yes and no. To me, there’s a difference between the storage and the architecture of said storage. The database schema for storing a user profile is a persistence layer issue. Figuring out which database instance to go to is an architecture layer issue.

This is an important distinction for me. Many folks are coding the architecture layer directly into the functional layer. A “save_profile()” API function might call an ORM to deal with the persistence, or it will have MySQL (or other database) connection handling and queries. However, the database will grow, and at some point you will find yourself wanting to split the data [more].

This type of information, like the presentation layer, needs to be separate. Why should the application care whether save_profile(’Sheeri’,'hair color’,'blonde’) accesses database1 or database2? More importantly, why should there be major code changes to the functional layer if the architecture changes? Just like no functionality has changed when you change your website color from blue to red, there is no functionality change when you go from splitting data between 2 database servers to splitting among 3, or 10.

For me, the persistence layer is about how the data is stored. Which, explicitly and for the record, I also believe should be separate from the functional layer — if you store hair color and eye color in one table or 2, the functionality of the application has not changed; all that’s needed is a change in how that data is stored and retrieved.

The architecture layer is all about where the data is stored. Early forms of the architecture layer are configuration files, though most would not call that a “layer”. Database administrators should be able to change the architecture of the database system without requiring mucking about in the application’s functional code.

Thoughts?

Thanks to Paul for announcing the founding of Pythian Europe. Paul finished his blog by inviting me to tell you the story about “how we met Pythian”. Here it is.

As I get older, I am starting to see some symbolic links connecting significant moments of my life. I realize now that the link to Pythian started 20 years ago in Prague in the year 1988. Let me share with you the trip. Although in Soviet Union, Mikhail Gorbachev began to moderate the totalitarian regime by introducing Perestrojka, the government of Czechoslovakia was still ruling with an iron hand.

I worked as a VMS administrator on a Czechoslovak clone of the VAX computer and, by the way, I became an expert in backup/restore, an expertise I had to exercise several times per week due to frequent crashes of our 29MB disks (also a East European clone). Besides administering VAX/VMS systems, I had to write hundreds of lines of Assembler, Fortran, and C code, just to handle inserts, updates, and queries for records in a few data files.

One day a colleague of mine brought me a tape, which he had smuggled from Vienna (there was an embargo on US software imports). On the back of the tape was written, “Oracle V4 for VAX/VMS.” Oracle V4 was released in 1984, the year I knew better as the title of the famous George Orwell novel. Out of curiosity I installed it after hours.

The “Readme1st” said to run one script with a strange extension — “.SQL”. Some program called UFI logged in as scott/tiger into a “database” and did SELECT * FROM EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND DNAME = 'SALES', and then increased the salaries of all salesmen by 10% by one simple UPDATE command.

I was so fascinated that I spent the whole night playing with SQL, as I did on many evenings over the next two years. Once, after returning from a political demonstration in Old Town Square, I escaped back into my SQL world. Despite tanks running the crowds down and the imprisonment of the dissident Vaclav Havel, I perceived strongly that regime change was as inevitable as the victory of SQL.

(more…)

Beware this parameter can prevent your database from starting. Indeed it can prevent your instance from starting!

There are two dynamic views v$system_fix_control and v$session_fix_control which were introduced in 10.2 and control whether fixes for bugs in the optimizer can be turned on or off. This can also be controlled using the _fix_control initialization parameter.

If the parameter _fix_control is set incorrectly i.e. with invalid bug ids then, when starting the database, you may get the following error

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Wed May 7 10:55:43 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount
ORA-00940: invalid ALTER command

There are no error messages in the alert log and the instance has refused to start.

And look at that helpful error message. No doubt behind the scenes it doing some sort of ALTER command but still …

If you get this error then check the pfile or spfile and see if there is some spurious _fix_control setting. This is especially valid for the upgrading of Oracle. I came across this error upgrading 10.2.0.2.0 to 10.2.0.3.0.

Also beware if setting this parameter in a running database. You can prevent further logins if you get it wrong or indeed try to unset it. Do not do

ALTER SYSTEM SET "_fix_control" = '';

This will require a bounce to fix. Once, of course, the parameter has been removed from the initialization files.

The moral of the story is be very careful with undocumented parameters when upgrading!

This is the 96^th edition of the weekly review of database blogs, Log Buffer.