Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Manoharan .

Asked: December 03, 2002 - 9:03 pm UTC

Last updated: June 10, 2004 - 9:40 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi Tom,

I would like to clear some of my basic doubts.

When i entered into the oracle (Developer/DBA) world.
I read/heard some basic concepts about the development/administration like

1.Don't create indexes for small tables.
2.Give More attention to Physical I/O...
(These are all some examples)

But when i read some expertise guides,i feel there are lot of contradictios to my old beliefs.

Not only me but also for many of dba's facing the same problem.What my doubt is

1.Were we started with bad guidelines?
2.What we are reading now is the real or we may get contracitions to the current expertise gudelines when we read better guidelines

Where we are lacking? how to approach?


Thanks/regards
Jeyaseelan.M


and Tom said...

This is a great question. In the book I am working on now, I have a section entitled "question authority". That is what we all need to do


The problem with basic concepts as you have given, such as "don't create indexes...", "PIO's are what to pay attention to..." is that they are all Rules of Thumb (ROT). I despise ROT.

Don't create indexes for small tables -- why not? Well, suppose this is a frequently updated small table with a foreign key to a parent table and the parent table is frequently deleted from. If you don't index it - you'll have massive - literally massive - concurrency issues related to unindexed foreign keys. What if there is a primary key that needs to be enforced? What if this table is really small -- and we full scan it constantly -- could indexing help us? Yes it can -- we can index full scan or even index fast full scan even less data (skipping the N blocks we get in current mode for a full scan for example).

Pay more attention to PIO's -- that one I think they just got wrong, or at least (as with MOST ROT), they left something out. It should be "after getting your queries to run with the LEAST amount of LIO's possible -- the next thing to attack is PIO's. We can spread IO's out over disks, we can look at our buffer cache. The important thing here though is to reduce LIO's -- so the plans are known and then reduce the PIO's if needed"


It is not that you were given 100% bad guidelines it is that guidelines (ROT) only apply in some typical cases and frequently in the real world -- do not apply to your case.

You will get apparent contradictions constantly! Continously! Do they contradict? Not necessarily -- they might just apply to two different cases.

What you need to do is read the advice...

Make sure the advice giver BACKS IT UP with solid evidence (discount anything that starts with "I think...", "I feel....", "It is my opinion...." and isn't followed by some test use cases showing the point!)

Test the advice in your environment...

Make a sensible decision as to the validity of the advice in your case...


If this all (our jobs) were easily codified into a set of ROT -- the software would just "do it". We would be obsolete.




Rating

  (15 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Great ! Free expert consultancy... ;-)

A reader, December 09, 2002 - 11:05 am UTC


A reader, December 09, 2002 - 11:24 am UTC


My opinion

Cefers.br, December 09, 2002 - 11:58 am UTC

This little thread reminds me of the polemic old question about keep index and tables in separate disks to gain system performance. One of the greatest ROTs.

I think then best way to get "up to date" and get away from old concepts is to read expert´s books, search for brand new information on Internet, etc.



Your book

ken chiu, December 09, 2002 - 12:58 pm UTC

Tom, when will the book be out and what's the content ?

Tom Kyte
December 09, 2002 - 1:16 pm UTC

Spring 2003

here is a blurb from the intro:

Introduction

This is a book that relates my personal experiences and knowledge about building scalable and performant applications that make use of an Oracle database. It has been my longstanding experience that applications dependent on the database for processing - and today that is pretty much every application of note - succeed or fail based on how they use the database.

There are many schools of thought out there today that describe how to build applications that will scale and be performant but the one thing they all have in common is they use a database to store and process data and, if you strip away the GUI at the end of the day - it is really about data and processes accessing and using that data. Therefore, it is my premise that regardless of the tools you use, the approaches you take to implementing your application - sound and proper use of the database from day one will increase your odds of success many times over.

I am not going to promote the use of one development technique over another but rather will stress that database design and implementation and the choices you make as to what features to use in the database versus do yourself outside of the database in the application will affect you.



What is this book about?

This is not a book about "tuning after the fact" or "fixing a broken database" - although all of the concepts and ideas in here are applicable in that environment. You are at that point looking for how to fix things and that is in this book - however, unlike many other performance books out there, I will not show you the dozen scripts you need to run to find little ratios that tell you if your database is "OK". This book is about building it right from the start.


Who should use this book?

The target audience of this book is the development TEAM - the group of people that have 100% of the control over the overall performance of the system. This is contrary to the popular myth that the DBA is solely responsible and has total control over application performance inside of the database. The best way to understand that this is a myth would be to use a car racing analogy. The DBA is the pit stop guy who changes the tires, makes sure the engine is gassed up, that the car functions. If you give the pit stop guy (DBA) a Lincoln Navigator (a truly huge truck) and tell them to race the Indy 500 with it - what'll happen? The DBA can make sure the truck runs as fast as it can but he cannot affect the performance of the truck on a tight corner at 100+ miles per hour. There is really very little to be done after the car has been designed and built (short of throwing out the car and starting over - the car is the application here). That analogy is frightening in its applicability to so many systems. A Lincoln Navigator was built where a high performance Indy car was needed or vice versa. Remember - we need 18 wheelers, minivans, race cars - everything. There is no one size fits all here.
.......


we are shooting for about 600 pages. It is less "reference" then Expert one on one is -- more best practices (eg: why you need a test environment, why you need to benchmark, question authority ;), the tools you'll want and need, when to use MTS (shared server), ....

How BIG is the book gonna be ?

Robert, December 09, 2002 - 3:07 pm UTC

>> Tom, when will the book be out and what's the content ?

How think or (app. how many pages) is the book gonna be comparing to "Expert" ?




Tom Kyte
December 09, 2002 - 3:48 pm UTC

600 pages is the target (but then again, E-1/1 was to be 800 pages ;)

Really, shooting for 600 pages on this one.

Very Nice

Sikandar Hayat Awan, December 09, 2002 - 11:06 pm UTC

The developers and DBA's need the books/experties of experts like you.......

What will be the title of your book?

Tom Kyte
December 10, 2002 - 6:56 am UTC

thats the last thing we decide ;)

we write them and then see what fits...

what's the title for new one?

sean bu, December 09, 2002 - 11:09 pm UTC


A reader, December 10, 2002 - 8:08 am UTC

is that your book or you're on of the authors

I'll wait for the next Book, eventhough I have'nt finished reading E-1/1 :-)

Anthony, December 10, 2002 - 8:12 am UTC


wasnt your next book about 10i?

A reader, December 10, 2002 - 9:53 am UTC

Hi

I read from one of old threads that you are gonna skip 9i R1 and R2 in your next book and focus on some stuffs on 10i... but if the book is going to be published on Spring 2003 I guess it wont have anything on 10i? Or you plan to write another book for 2004?

Tom Kyte
December 10, 2002 - 12:08 pm UTC

2004 is sooo far away ;)

But anyway -- this current book I am working on for 2003 is a book for all versions. Consider it version independent. It applies to Oracle 6.0 as much as 9i.


Dittos!

Robert, December 10, 2002 - 10:09 am UTC

Professor Tom,

Thanks for your great Oracle classroom.
Please don't ever stop teaching!

Thanks,

Robert.

The book should be called ....

Sanjay Raj, December 10, 2002 - 10:35 am UTC

Excellent Tom. I have learnt more from this forum than from the $2500- $3000 Oracle books that I have accumulated. Just a suggestion for the name of the book - "Design it right".
Thanks

A reader, December 10, 2002 - 11:20 am UTC

Or may be the title should be

Expert on Experts :-)

New Book

Umesh, January 07, 2003 - 8:30 am UTC

Tom,
Thanks so much for all the work. Till date I was in training but shifted to s/w development, but with part of your book's knowledge with me. I did not find any thing new till date apart from what ever you have discussed . Thanks.

But for your New Book I request you to ensure that Book be made available in India simulataneously


Where can i buy

jj, June 10, 2004 - 8:33 am UTC

Hi
Tom where can i buy your books, i am looking your all previous released books!
thanx

Tom Kyte
June 10, 2004 - 9:40 am UTC

amazon.com