Tuesday, September 26, 2006

Interview with Tom Kyte

This is an interview which I made with Tom Kyte in Zurich in September 2006

L.H.: Mr. Kyte, you are Vice President of the world's largest database software company and you also are the man behind the http://asktom.oracle.com/ website.

Since more than 6 year you have provided kind of support to the Oracle community for their every day problems in your forum.
Judging from your experience, what are the most common mistakes made about database applications?

T.K.: This is a timely question, I just finished my "Worst Practices" material for some upcoming conferences (Oracle Open World included).

The worst practices I see out in the field are:

1) Blindly believing everything just because it is printed. I cannot even guess what percentage of technical material about Oracle published on the Internet is wrong. During the construction of my material for this Worst Practices talk - it took me about 10 minutes to find 10 really "good" (meaning really bad) examples of technically incorrect information. I used http://www.google.com/ to search for "Oracle Tuning Tips" and found some really bad material quickly.

2) Not using bind variables! Developers for whatever reason make this common mistake time and time and time again. Not using bind variables in most applications will cause 4 things to happen:
a) they will run slower in single user mode, a large percentage of the program run time will be spent parsing SQL, not executing it.
b) they will use memory entirely inefficiently, the shared pool will be a point of contention.
c) their applications will not scale as users are added - hard parsing is a point of much serialization in the database.
d) their application will be subject to SQL injection issues (put SQL Injection into the search box on http://www.google.com/ and see how many articles you get back on that).

So, by not using bind variables - the developers help to ensure they will have a slow running program that creates memory issues in the shared pool, that cannot scale and will be a huge security risk.

3) Implementing very generic data base designs. Generic code can be useful, in some rare cases - however, if you want something maintainable, understandable and performant - you will actually design your system, from day one.

4) Not having a credible test environment. For example - not testing to scale (if you are going to support 100 concurrent transactions, you need to be able to test that you can do that - and then test it!). Or - testing against an empty database; that will not give you any indication that your developed application will actually perform in real life.

L.H.: After 14 year of work with Oracle Database, what is the feature to your mind today which makes Oracle Database unique among other database providers?


My favorite feature - Oracle's concurrency model.

L.H.: The average database of today is by far larger than what has been considered a Very Large Database only a few years ago.
What are the most challenging task for a database administrator today?

T.K.: Keeping up with the technology! Many of the things we used to spend a lot of time doing manually are much more automated than they were a decade ago. And there are hundreds of new features to learn about. In the last decade we've gone from having a 1 Terabyte database be considered "huge" to being considered just "average" in size. Many things we did not use or need to use a decade ago - such as partitioning (did not exist) - are things we need to have mastery over today. Therefore, the working set of knowledge a DBA needs today is very much different from the working set of knowledge they had to have 10 years ago. We need to "forget" about some of the things that were automated (such as undo management - I'm glad I don't have to figure out exactly how many rollback segments of what size I need anymore) and keep up with all of the new tools we have to manage data such as partitioning, data guard, streams and so on.

L.H.: The trend of the Oracle Database Server goes clearly into the direction of self management and self maintenance.
Will there be a need for database administrators for the database of the future and what will be their main tasks?

T.K.: Absolutely - see the points above. Every time we automate something - 3 or 4 other "new things" are added. Sure - we automated PGA memory management in 9i, but we added Streams and Change Data Capture. The list of things DBAs do is not necessarily shrinking, but it is ever changing.

L.H.: Will the database server of the future be able to also kind of auto-tune the application?

T.K.: This is why I say "tuning is not dead", but how we tune has to change. In the past - we would tune by tuning SQL. Nowadays the database is pretty good at tuning SQL - with SQL profiles, an ever smarter optimizer, the index advisor, the materialized view advisor, the SQL advisor and so on - tuning SQL is something we spend less and less time on.

What we have to focus on is how we do things - our algorithms. Maybe far far out in the future, the database can detect what an application is doing and "fix it", but for the time being - only we can.

For example, the datab ase will not turn logic such as:

for x in (select * from t1)
insert into t2 values X;
end loop;
insert /*+ APPEND */
into t2 select * from t1;

That is our job and will be for the immediate future.

L.H.: Oracle already has its own logical volume manager (Automatic Storage Management) its own cluster file system and since March 2006 also its own media management server (Oracle Secure Backup), which can be used to not only backup the database but also the entire Oracle software stack including application servers, the collaboration suite as well as OS-files.
Will Oracle be kind of an operating system some day?

T.K.: We tried that once before actually, a thing called "Raw Iron" http://news.com.com/2100-1001-219075.html

It was not widely adopted - maybe it was before its time. I can certainly see the usefulness of such a thing - I personally already consider Oracle to be my operating system in many cases. I'm not saying Oracle is working on it (I have no knowledge to that effect), just that I could see it happening sometime in the future. A database appliance, much like you have network attached storage.

L.H.: Thank you very much Mr. Kyte.


At Tue Oct 03, 11:20:00 AM, Blogger Laurent Schneider said...

> Blindly believing everything just because it is printed
so true.

Ex: Oracle University 9i Tuning Course Chapter 3 Slice 6 Tablespace Usage :

- Separate tables and indexes in different tablespaces

have a look at
CDOS post

At Wed Oct 04, 07:46:00 AM, Blogger lutz_hartmann said...

Hi Laurent,
I am surprised!

Oracle 9i Tuning Course, Chapter 3, Slice 6 talks about I/O Topology Support.
And I cannot find anything like tables and indexes on different tablespaces there.

Looking at Chapter 3, Slice 6 of the same course I find:
"- Split tables and indexes into separate tablespaces.
- Create rollback segments in their own tablespaces."

This slide talks about "The important principle is to separate data of different types and uses for housekeeping and backup purposes" in the notes.

At this point I do not want to jump on to the old discussion from more than fife years ago whioch you quoted.
I just want to say a few words about contention on disk headers, which we discuss in our Performance Tuning Classes of course:
-i t makes sense, to separate tables and indexes which are accessed concurrently on different disks. No?
- you do this for instance if you find out that you have a "hot disk" and that the reason for this is that you have concurrent write and read access to objects which are located on this same very disk.
May it be tables or indexes or what ever.

What I do not understand fully is, here is your problem with the courseware for the Otracle 9i Performance Tuning course exacly?
By the way, the Oracle 10gR2 Performance Tuning Course covers a lot of new features and it is really worth taking it. I have deliverd it just last week in Baden. It has good chances to become one of my favorite courses. It goes very much into detail espacially for I/O issues.


At Wed Oct 04, 04:53:00 PM, Blogger lutz_hartmann said...

I ment "Looking at Chapter 3, Slice 7 of the same course I find:"

At Fri Oct 06, 10:57:00 PM, Blogger Laurent Schneider said...

hi Lutz,
it is all about taking what is written as automatically true.

one day, a dba came to me and said : "it is incredible, they put the indexes and tables in the same tablespaces, I will move all those segments immediately". I answered that there is no problem by having tables and indexes in the same tablespace, because index and table are never accessed concurently (first read the index, than the table). But then he show me the courseware (in german), and I had to say : "well, this is a common belief, but you know, tom kyte does not think the same :-)".

Well, it is not necessarly wrong to separate different types of segments to different tablespace, but tom kyte explained us last year that moving all segments just because "it is well known that it improves performance" is a kind of common mistake...


At Tue Oct 10, 10:47:00 PM, Anonymous Marco Gralike said...

Wasn't it Tom who said never to trust a authority ;-) Nice eh, such dualism.

The one that pups up into my mind is the use multiple controlfiles (as a disciple, in this case of Steve Adams) I create only one (to much unnessesary I/O) and I can imagine that this item is still mentioned in the Administrators Guide with the advise to have multiple controlfiles.

The only case I, uptil now could think off ("again never trust the authority"), is having multiple controlfiles only in the case (by this I mean a simple standard database layout, no RAC or other "new" architectures involved) where they are used as a repository for RMAN.

The database assistant still creates more than one and OEM start squicking when I apply this strategy.

All in all, I think the best way to go is to know your basics and use your common sense. And in time, re-evaluate those "rules of thumb" every so many times...

with regards


At Wed Oct 11, 08:09:00 AM, Blogger lutz_hartmann said...

Hi MArco,
what does Steve Adams recommend you to do if you lose the disk with the one and only singel controlfile?

At Wed Oct 11, 09:57:00 AM, Anonymous Marco Gralike said...

Despite a good backup plan and making use of for instance RAID1 (you have somewhere you mirrored controlfile on disk) - use concurrently "alter database backup controlfile to trace" (available syntax since Oracle 7.2? ) or a variant on this principle (for instance controlfile backup in a RMAN backupset).

Every access on a controlfile will cause a minimum of 4 I/O (I am doing this now by hart, but if I am not mistaken 1 R/W action on the header segments 1 R/W action for the actual change). Correct me if I am wrong, controlfile are updated sequentially, so I am losing speed (possible latency problem introduced by sequenced actions) and 4 I/O extra for every extra added controlfile.

At Wed Oct 11, 10:02:00 AM, Anonymous Marco Gralike said...

Just to emplify, for instance with 3 controlfiles based on RAID1, the system has to maintain 6 files (3 of them are mirror copies maintained on hardware level)...on a stressed (database) system these kinds of items will make a difference.

At Wed Oct 11, 11:19:00 AM, Blogger lutz_hartmann said...

Hey Marco!
I do not want to jump on to old disputations of "experts" from other forums here.
Just to recall: in your first comment you have stated, that following your guru, you only use one controlfile - without specifying any raid configuration and so forth.
This has left the impression that you do not have any mirroring at all for the controlfile.
Now you come up with raid 1.
As one expert in his very good seminar said recently: "Half of the truth can be worse that a wrong statement" (not quoted literally!).
What is your point anyway?
Do you want to point out that the online documentation is wrong at some point?
Or would you like to see something aded to it?
What is it?

At Thu Oct 12, 06:23:00 PM, Anonymous Marco Gralike said...

following a Guru ;-)

As I said before, one shoulod use common sense and beware of the "why" (also if you follow "printed stuff regarding Oracle courses" and/or courses from guru's).

You pointed out a "what if you lose the disk with the one and only singel controlfile?". Then I would answer (quoted from a peer ;-):

"As one expert in his very good seminar said recently: "Half of the truth can be worse that a wrong statement" (not quoted literally!)."

Yeah, could be that it is on 1 disk. But what have we lost (only the controlfile? --> Still no big deal) IF you had a working backup plan (you didn't point that one out) in place IF archiving or db flashback is enabled (you didn't point that one out), etc.

The RAID1 example is only to show the impact of the use of multiple controlfiles. I am not trying to confuse the discussion.

In the Database Administration Manual it says (http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14231/control.htm#sthref771):

Multiplex Control Files on Different Disks

Every Oracle Database should have at least two control files, each stored on a different physical disk. If a control file is damaged due to a disk failure, the associated instance must be shut down. Once the disk drive is repaired, the damaged control file can be restored using the intact copy of the control file from the other disk and the instance can be restarted. In this case, no media recovery is required.

Even in this case you will have to be lucky. It wouldn't be the first to see that at that moment your OS or hardware where you trust is set upon, will leave you with a corrupted (or maybe not written in time because of delayed I/O principles) second, third, ... controlfiles.

Regarding the doc's. TELL people (document) that there is trade off. DON't imply that "multiple controlfiles is the golden rule". I think it is still in there because this was the way to do in in the time we used Oracle 7 and hardware / harddisks were expensive and not common goods.

In short...

"> Blindly believing everything just because it is printed
so true"

At Sat Oct 14, 12:21:00 AM, Blogger lutz_hartmann said...

Hi Marco,
not everybody has a raid array,
and not everybody can efford an OPEN DATABASE RESETLOGS. This can take time because you need a new backup afterwards.
And all this because you did not multiplex the controlfile!
You have to make up your mind which death you want o die at some point.
Either you have more I/Os or your recovery might take longer!
I think that we can agree at this point at least.

At Mon Oct 16, 06:26:00 PM, Anonymous Marco Gralike said...

How many customers do you know, that build their production environments NOT on raid environments or ASM controled environments, etc, in other words build their production environment on single disks...?

How many customers do you know, do not have a well tested, and/or at least, backup plan. I know this is a discussion point again, but customers who have do not have a working backup plan, essentially deserve a single controlfile solution.

How many customers do you know, who think that performance is NOT esstential.

I think the statements above are base rules for standard operations and therefore (I would love to agree with you) I choose for the complex solution, which is a little bit more demanding on DBA's, where I prefer I/O's before recovery.

Recovery principles in production environments, should not be dependable on 1 single controlfile.

Documentation, which is based in my view, on very poor customers (how much does a harddisk cost nowadays?), or is based on the exception (of exceptions), should be OR explained OR be revised.

with respect & regards,


At Wed Oct 18, 10:43:00 PM, Blogger lutz_hartmann said...

Hi Marco,
what we are talking about is avoiding single points of failure.
From this point of view we must protect the controlfile and the redo logs
from getting lost or corrupted.
Oracle recommends to protect the controlfile and the redo log files by multiplexing them.
Which means: have multiple controlfiles specified in the CONTROL_FILES parameter and have multiple members for your redo log groups.

Leave it to ORACLE to detect corruptions in the controlfile!
Multiplexing protects from the introduction of corruptions by the OS or hardware.

What you are talking about is is called mirroring.
This protects from disk failure. If a disk fails,
mirroring will allow for transparent repair of the problem at a level below Oracle.
You leave it to the OS to take care of your data in the controlfile.
This does not protect you from bit flips, lost writes, OS scribbling on the disk, etc.

You should multiplex through two different controllers,
to different volumes/disks,
even to different storage arrays, depending on the degree of paranoia you have.


At Thu Oct 19, 02:41:00 AM, Anonymous Marco Gralike said...

I am doing my best to stay on topic...

...but you are introducing now even more unnessary I/O's by mentioning multiplexing redolog files. As you probably expected I am also against this principle, based on partical reasons from the Oracle 7 days (hardware was not cheap, and the database wasn't as sophisticated as it is nowadays).

As said, the documentation ("the printed ...") is not up to date.

By mentioning "This does not protect you from bit flips, lost writes, OS scribbling on the disk, etc.", does this mean that it is possible that Oracle, the database, cripples it's own controlfile(s)? In other words reasoning from the viewpoint of the database.

If this is not what you are saying, then it therefore possible that, for instance, the OS in case of lost writes could cripple all you controlfiles. In other words reasoning from the viewpoint of the OS / Hardware.

Ad 1. Should be dealt with, by Oracle

Ad 2. Should be dealt with, by the OS / Hardware

If I understood you correctly then you also would multiplex controlfiles and redologfiles on SAME architectures and RAID5 configurations...

At Thu Oct 19, 08:46:00 AM, Blogger lutz_hartmann said...

Hi Marco,
it depends on teh level of parqanioa you have, I said!

I said, leave it to ORACLE.
The rest is up to you.

At Thu Oct 19, 02:59:00 PM, Anonymous Marco Gralike said...


Thanks for taking the time to discuss about these kind of issues




Post a Comment

<< Home