Skip to Main Content
  • Questions
  • Your new book : Expert one-on-one performance by design

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments, but we might still sneak in a few Office Hours sessions! Thanks for being a member of the AskTOM community.

Question and Answer

Tom Kyte

Thanks for the question, Tarry.

Asked: March 03, 2003 - 8:18 am UTC

Last updated: February 23, 2012 - 8:06 pm UTC

Version: 9.2.0

Viewed 50K+ times! This question is

You Asked

Tom ,
I've got a few questions about you new book.
1.How is your new book different than the older one?
After reading the drafts I kind of figure(don't get me wrong here) might be getting a little repetitive there(wrt your older book--although I'm looking forward to reading it).

2. How version dependent or independent is it?

Please throw some light here....


and Tom said...

if you consider bind variables repetitive sure, I mention them a couple of times ;)

Tell you what, visit:


</code> http://asktom.oracle.com/pls/ask/z?p_url=http%3A%2F%2Fwww.expert-oracle.com%2F&p_cat=NEW_BOOK_REVIEW&p_company=10 <code>

and give some feedback. Bear in mind, it is DRAFT, not done the technical review -- I can assure you the finished product will look a tad different -- but you'll get the point.

Consider the Expert One on One outline:

Introduction
Setting Up
Chapter 1: Developing Successful Oracle Applications
Chapter 2: Architecture
Chapter 3: Locking and Concurrency
Chapter 4: Transactions
Chapter 5: Redo and Rollback
Chapter 6: Database Tables
Chapter 7: Indexes
Chapter 8: Import and Export
Chapter 9: Data Loading
Chapter 10: Tuning Strategies and Tools
Chapter 11: Optimizer Plan Stability
Chapter 12: Analytic Functions
Chapter 13: Materialized Views
Chapter 14: Partitioning
Chapter 15: Autonomous Transactions
Chapter 16: Dynamic SQL
Chapter 17: interMedia
Chapter 18: C-Based External Procedures
Chapter 19: Java Stored Procedures
Chapter 20: Using Object Relational Features
Chapter 21: Fine Grained Access Control
Chapter 22: n-Tier Authentication
Chapter 23: Invoker and Definer Rights

and this one:

1 The Right Approach to Building Applications (67pp)
2 Your Performance Toolkit (58 pp)
3 Scalable Oracle Architecture (49 pp)
4 Optimizing Database Processes (32 pp)
5 Effective Administration (24 pp)
6 Getting the Most out of the Cost Based Optimizer (44 pp)
7 Statement Processing
8 Defining an Efficient Schema
9 Writing Efficient SQL
10 Writing Efficient PLSQL
11 So you had an Accident


They are definitely different in size and overall scope. Whereas the first book was pretty much "this is how is works", this one is more "this is what I think you should do". This one has slightly more "opinion" (backed up by facts of course).

I actually see them as being quite complimentary. In fact, electronic copies of large portions of "Expert One on One Oracle" will be made available as a companion to this one -- when talking about defining an efficient schema for example, I needed all of the information in

Chapter 6: Database Tables
Chapter 7: Indexes

from "Expert One on One Oracle". Those chapters (and others) will be available to people who get this book (as I say "read that over there", that wouldn't be fair otherwise).


This book will be repetitive in that I keep saying the same thing over and over -- it is all about how you use the database, period. Same sorts of examples, same sorts of principles. Many of the examples/cases in the book come right from this sight or variations thereof.


I think they are very different books -- that is something that scares me a little here. Anyone expecting a new "Expert One on One Oracle" will not get what they are expecting. I don't teach the architecture, I tell you when to use a specific architecture (eg: use dedicated server when... use shared server when...). I don't teach all about tables, high water marks, pctfree, pctused -- I say use LMTs, do this, do that.


It is very version independent -- sure, some of the structures like IOT's require 8.0 and up -- but others like hash clusters do not. Array processing -- thats been there for years, new in plsql though. Does it contain stuff about the next release of Oracle (after 9iR2) -- nope, but will it all apply ? Yup.

Rating

  (381 ratings)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

Allright Chief!

Tarry, March 03, 2003 - 8:57 am UTC

And yes I will give some feedback there.....

Eagerly waiting for release of your Book

Vijay Sehgal, March 03, 2003 - 9:23 am UTC

Hey Tom,
I read in one of your response to the new coming book that you won't be shooting for too many pages but I think (most of us coming to your site) won't disagree with me when I say please do not confine yourself from what is coming from you, It's really great to have you helping out people and passing on the knowledge... Great work....

Best Regards,
Vijay.

This response was definitely "Most Useful"

Tom Best, March 03, 2003 - 9:27 am UTC

You are apparently at least as interested in sharing your expertise with the Oracle community as you are in making a living at it.

Regarding New Book.

Ranajit Ganguli, March 03, 2003 - 1:02 pm UTC

As a DBA I often come across such issues like how to take advantage of big Storage Area Networks (SAN),how do RAID come into play when our mount points are distributed over various physical volumes, how to best distribute IO on a SAN etc.

The above mentioned issues needs to be answered during design and capacity planning. Are you planning to discuss these issues in the design section of your new book.

Thanks

Ranjit

Tom Kyte
March 03, 2003 - 1:38 pm UTC

Nope, not at all. Getting outside of my area of expertise. In fact, the stuff is so far removed these days, that well, I don't even really think about it at the database level as much anymore. If the hardware guys get it wrong, they just shuffle it about behind the big cabinet while I'm at lunch.

You can preorder it at Amazon

Cristian Perez, March 03, 2003 - 4:16 pm UTC

This morning I saw that you can pre-order it at Amazon. I already have expert one on one and I'm waiting for this one to come out.

Good Work Mr. Tom

A reader, March 03, 2003 - 10:41 pm UTC

I can only say that. Is there any chance you consider adding a chapter on tips for Query Tuning. I know there are few books available in market like one from GUY HARRISON, but if it comes from you , I think that will be something more than real. Thanks for all your support Tom.

clarification

Ram, March 04, 2003 - 7:29 am UTC

Its a pleasure to read the draft of your forthcoming book.
I am now reading the 3rd chapter and one particular thing is not clear to me.
this is about the disadvantages of Shared Server, more specifically about the "artificial deadlock" scenario (point no 6).
according to the draft
<quote>
the session holding the lock attempts to commit or rollback it will hang as there are no more free shared servers.
<unquote>
Q. lets say there are 5 shared servers. 3 are busy with their own work. 4th is trying to update a row which is already locked by the last and only remaining 5th server. now 5 is the blocker and 4 is the blockee. what happens when 5 commits/rollsback ? why does it need more free servers ?
if the session currently using 5 does not have any more work then 5 is free to be used again (by any other session). even if not, it can still use 5, isnt it ?
its not clear to me how this is a problem in the context of a deadlock. if its a question of more sessions then that will come under resource monopoly (point no 7 in the draft).
pls clarify.


not sure whether to post it here or not. (for some strange reason my login always fails at expert-exchange when i try to post it there. )




Tom Kyte
March 04, 2003 - 6:14 pm UTC

The artificial deadlock would happen when

o you have N shared servers.
o you have N+1 sessions
o session 1 holds a lock "Z" and goes "idle"
o sessions 2..N+1 each attempt to get lock "Z" and hence all N shared servers are busy
o session 1 cannot release the lock since there are no shared servers available

your first book

mo, March 04, 2003 - 6:31 pm UTC

Tom:

Just curious, do you have a count of how many copies of your first book were sold. is it a best seller yet?



Tom Kyte
March 04, 2003 - 6:57 pm UTC

JK Rowling doesn't have anything to worry about yet no.

Best seller

A reader, March 04, 2003 - 8:22 pm UTC

Looking for forward to the movie ;)

I think the Tom Kyte Action Figure
will be a popular collectable as well

Books on RAID

Tony, March 04, 2003 - 11:54 pm UTC

Tom, can you recommand some books on RAID?

Tom Kyte
March 05, 2003 - 9:27 am UTC

nope.

Books on RAID

Jim, March 05, 2003 - 1:01 am UTC

Tony

Search Tom's site for RAID

I think you will find nearly all you need to know


Free pdf?

reader, March 05, 2003 - 5:07 am UTC

Hi,Tom ,I read your sample chapter of the book,
in the book ,you said that reader can get free pdf on www.wrox.com (Expert one-on-one (1861004826)),where is it ?

Tom Kyte
March 05, 2003 - 8:00 am UTC

after you buy the book and get the book, you'll be able to use information from the book to get access to selected chapters (ones that I refer to heavily for reference) in pdf format.

Regarding books on RAID

Mark J. Bobak, March 05, 2003 - 9:46 am UTC

It may be worth picking up Jonathan Lewis's Practical Oracle
which covers various RAID strategies, 1+0 vs. 0+1, which
is better and why, how bad is RAID5, and all with the
consideration of how Oracle does I/O.



Thank you!

Fenng, March 06, 2003 - 9:45 am UTC

Free pdf ?sorry ,I'm a reader from China ,and the book is a chinese book,so I can't get the pdf.
:(

Tom Kyte
March 06, 2003 - 9:54 am UTC

But you apparently can read some other languages that are pdf-able?

why isn't chinese pdf-able?

Thank you ,anyway

a reader, March 06, 2003 - 10:02 am UTC

First,I'am sorry for my poor English.

I mean I can only get the book's smaple chapter,
but can not get the **other** free pdf about the book .

In your new book,you said reader can get the free pdf from wrox.com.Maybe it's a mistake.

Tom Kyte
March 06, 2003 - 3:30 pm UTC

What it means is that "selected chapters of expert one on one to which I refer heavily in the new book will be made available to purchasers of the new book in pdf format"



Here

Fenng, March 06, 2003 - 10:06 am UTC

Here:in your new books ,chapter 3,1.1 assumed knowledge,you said :EXPERT ONE-ON-ONE ORACLE(1861004826):chapter 2 architecture(Available as free pdf on www.wrox.com)

I download it(chapter 3's pdf) from www.expert-oracle.com.


Thank you!

Tom Kyte
March 06, 2003 - 3:31 pm UTC

right -- when the book is published -- you would (as an owner of the book) have access to that pdf.

I think what Feeng is asking...

Jeremy Smith, March 06, 2003 - 11:58 am UTC

If you purchase the new book (either in english or another language) are the chapters from Expert-One-on-One that you'll be able to reference online going to be available languages other than english?




Sorry,

Fenng, March 06, 2003 - 8:31 pm UTC

If I purchase the new book (in another language, but not English) ,are the chapters from Expert-One-on-One that can reference online in **english**?

I love the great book ,but hate the damned translation.

Thank you ,Tom.

Tom Kyte
March 07, 2003 - 7:36 am UTC

Ahh -- the chapters online will be available in english, yes, I'm finding out whether they will be in other languages as well.

Please Include PL/SQL best practices

Tony, March 07, 2003 - 7:47 am UTC

Hi,
I went through your new books sample chapters. Please extensively include PL/SQL best practices as well as Exception handling in web/client server apps as they are very important.

Tom Kyte
March 07, 2003 - 8:15 am UTC

I'm not doing programming style like exception handling (that is a matter of programming style).

I'm doing the top 10 things I see for plsql (with the "why" and "proof of why"), namely:

o write as little as you can
o only use packages
o use static sql
o bulk processing
o how to return data to the client
o using %type/rowtype
o properly using authid current user
o how to lookup data correctly
o properly using autonomous transactions
o implicit/explicit cursors

The only coding style thing I have in there is "make the darn procedures fit on a screen, If I have to page up and down to read a subroutine, it is too long"



A reader, March 07, 2003 - 10:24 am UTC

Is Expert one-on-one: Oracle available as online version? If yes, could you please post the link?

Tom Kyte
March 07, 2003 - 10:59 am UTC

sigh...

ok -- here is the scoop.

My next book "expert one on one Oracle performance by design" relies heavily on the material in "expert one on one Oracle". Where I put in references to "expert one on one Oracle" -- we will make that material available online (so you are not "forced" to buy "expert one on one Oracle" to make sense of this new book). That material will be available AFTER the book is published, to those people that purchase "expert one on one Oracle performance by design" as reference material.

will there be an expert one on one oracle, 2nd edition

mary, March 07, 2003 - 4:12 pm UTC

Tom,

Is there going to be a 2nd edition of the 2001 expert one on one book? I guess my main question is does everything in the 2001 book apply to 9i too?

Tom Kyte
March 07, 2003 - 6:19 pm UTC

i would say well over 90% of the material does apply. there are little nuances like the job queue process architecture is a little different, UTL_HTTP is so much better that my reimplementation isn't needed -- but page for page I would say it all applies and is still accurate for 9i.

There may some day be a 2cnd edition but it will include some yet to be released version of the database back to 9i and before ;)

Regarding - Defining an Efficient Schema

A reader, March 07, 2003 - 11:36 pm UTC

Tom,
What are you going to cover in this topic? Does it also include your opinion about designing for performance features for data warehousing applications? if not, it will be nice to include them, as performance issues in data warehousing applications sometimes becomes visible after the growth of data (which can be sometimes after few months/years), and by that time it is too late to change the design.
Thanks

Tom Kyte
March 08, 2003 - 8:58 am UTC

well, I would beg to differ -- it is never too late to change the design. If something ain't broke, don't fix it. Conversely - if something ain't working, what are you going to do? Just keep dumping money into it (money for the dba's, developers and end users that use it) throwing it away or -- gasp -- do you fix it.

If you design in a database centric approach, with lots of centralized controls, you'll actually find that evolving a schema is workable.

The outline for efficient schemas is:

Why the schema matters
Let the database do your work (Ref Integrity)
If you are only using a heap..
clusters
single table hash clusters
IOT
External tables
If you are only using B*Trees
FBI's
Domain Indexes
Using the correct datatype
Compression
Optimize to your most frequently asked questions


ashraf

ashraf, March 09, 2003 - 3:14 am UTC

i belive that any developer or DBA must get this book my mind is change in understanding lock after i read tom expert book

Regarding - Defining an Efficient Schema

A reader, March 09, 2003 - 4:15 am UTC

Tom,
Thanks for your views. Actually, I was referring to your 'expert one-on-one' page 432
>>>>
Design for performance
A database system is not something that can be approached with the 'build it now and tune it later'......
It must be designed this way from day one.
<<<<
in relation to data warehousing applications (DB centric approach) features like MVs (instead of manually creating summerised tables), partitioning key, bitmap indexes, loader vs external tables etc. etc.
Thanks

Tom Kyte
March 09, 2003 - 8:19 am UTC

Ok, let me extend that:

Design for performance
A database system is not something that can be approached with the 'build it now
and tune it later'......It must be designed this way from day one. Or you must be willing to throw out and redesign in order to obtain performance
....


I do talk about partitioning, indexing in general, external tables. Don't get too much into MV's (covered them pretty well in the last book)


Copy of first book.

Rory Concepcion, March 09, 2003 - 8:28 pm UTC

Hi TOm,

First of all I would like to congratulate you on your upcoming book. I have read the drafts of the sample chapters. They were very informative and useful. I'll await the day that the book comes out. When would it be coming out?

Do you mean to say that if we purchase this new book, we would have a sort of access to wrox website and be able to view a copy of your first book? Maybe we'll be given a password or account in the site to access the online copy of the first book? If that's the case, wouldn't it be unfair to those who bought the first book? Coz this time all we need to do is just purchase the 2nd book and be able to download the first one?

Tom Kyte
March 10, 2003 - 7:04 am UTC

Book is out at the very beginning of May.


You would have electronic access to portions of the first book (chapters). You will not get the first book in its entirety. You will only get electronic copies.

Is it unfair? I hardly think so (me, I wanted to have the entire first book included as a CD-Rom with this one, but that didn't fly).

The first book has been out for going on 2 years -- there is something to be said for having the book for the last 2 years. And to have the hard copy. You know -- there isn't much in the first book that you cannot put together yourself from this site, the concepts guide. Is that fair? People bought that which they could get for free :)


Rory Concepcion, March 12, 2003 - 3:21 am UTC

Ok... Ok. it's not unfair. Like you said, the first book could have been gotten for free coz all the information can be found here.... that's a good one..

Congrats again. If the book will be released by May, I hope our bookstores here would purchase it so that I could buy one. This will be another hit. I loved the drafts. I learned more from them.

God bless you!

Publisher going under..

robert, March 14, 2003 - 5:01 pm UTC

you said >> Funny thing about my book My publisher went under...

WROX is going under ?
Isn't "Oracle Press" a publishing unit as well ?
Why didn't you go with it ? Larry wants bigger cut ?

Tom Kyte
March 15, 2003 - 8:58 am UTC

WROX is out of business (no more expert one on one Oracle, what is on the shelf will be the last of them).


I'm looking around now ;)

Who will publish?

Rory Concepcion, March 17, 2003 - 9:34 pm UTC

Hi Tom,

How about ur new book then? If Wrox is out of business then who would publish it? Well, if you'd like you can finish the whole book, post a link here to it's electronic copy... then it's no sweat for all of us. hehehe. Just kidding. I know that would be impossible. But if you'd like to bite my joke then good for all of your fans.

Have a good day.

Tom Kyte
March 18, 2003 - 7:30 am UTC

I'm shopping around for a publisher now. The book will obviously be quite delayed. I was relying on the fact that Expert One On One Oracle existed and would be in electronic form for people who bought this book. So, there are sections that say "for all of the details refer to chapter such and such". As that won't be possible -- Expert One on One Oracle no longer "exists" (no more printed copies will be made) and I cannot as yet transfer the digital rights.... It'll take some re-working.

your survey...

Robert, March 18, 2003 - 2:19 pm UTC

tom, you asked
> What publishing company are you comfortable enough with to >be able to purchase a book from them without worrying >about the quality of the content?

And I said:
Personally Im not sure how to give a reasonable answer...
for I thought "quality of the content" has to do with the author, No ? what does the publisher have anything to do with this important aspect ? I mean who's to stop you as an tech. author to put out hard-to-understand, bad/wrong info (like a WHOLE lot of them out there)
I'd think they have to do with fair pricing (textbook publishers are criminals !), quality of layout, print, paper...

Tom Kyte
March 18, 2003 - 4:51 pm UTC

I've changed the question to

"Tell me the name of the publishing company you most respect technically, the one that you would trust to deliver you high quality material worth the price you paid every time. One you might buy a book by an author you never heard of -- based on the company they keep"

What I'm trying to find out is -- who do you all really like, really respect as a publisher...





One would think...

Jer Smith, March 18, 2003 - 5:09 pm UTC

That one of the assets that any liquidator would be able to squeeze out of WROX would be the rights to those books they've published that have actually made a profit. Maybe we'll get lucky and a decent company will snatch it up.

I feel more sorry for some pretty great books WROX has just published that haven't had a chance to really get "out there" yet. There have been quite a few in the last six months. In all liklihood those authors (and us!) are basically SOL.




Tom Kyte
March 18, 2003 - 5:22 pm UTC

if they are not sold, wrox's contracts appear to revert the rights back to us (man, never thought I'd have to get a lawyer for something like this). I'm actually hoping it doesn't get picked up, so I can choose who gets "2cnd edition" with me.

Same for those other books -- when they revert back to the authors, they would have another chance.

let us know

A reader, March 18, 2003 - 6:04 pm UTC

Can you let us know which publisher got the highest votes and which one you chose ?

Thanks,

Choose a global publisher

Logan Palanisamy, March 18, 2003 - 7:39 pm UTC

Tom,

You are providing a wonderful service to a "worldwide" Oracle audience. When all the other factors are same, you should choose a publisher who has presence in all the continents. (like offices in Boston, San Francisco, New York, Toronto, Montreal, London, Munich, Paris, Madrid, Cape Town, Sydney, Tokyo, Singapore, Mexico City, Auckland, New Delhi, Sao Paulo)

That way it is easy for every one to get a copy of your new book, preferably in a local English edition at a much a cheaper price than the US edition. Remember, how many people were asking you when they are going to get the 'Expert one on one' in their country.

WROX doesn't/didn't seem to have any wordwide offices. May be it is a blessing in disguise. You can choose a better publisher now. Let's hope you get the digital copy rights in your favor without much hassle.

Best wishes to you from all of us.

Tom Kyte
March 19, 2003 - 6:16 am UTC

well, actually WROX was worldwide. It was released in places from china to korea, india to france, russia to spain and all over the place. It just takes time to propagate and in most cases -- it was a stocking issue, if they just take the ISBN into a book store, they would get it right away (the bookstore would gladly get it)

I hear you though...

We're with you...

Rory Concepcion, March 18, 2003 - 8:54 pm UTC

I think the last comment is right. Maybe this really is a blessing in disguise for you or maybe for all of us. I just hope you find the right publisher for you. I'm sorry if I didn't vote.... dont really know much information on any publisher. I could give names of our local publishers coz I used to write children's magazines here. :-)
Anyway, please inform us when you've chosen a publisher. I do hope that publisher has a wide market here in my side of the world. And I hope your book gets published soon. We're all very excited to see it.... and I also hope it comes out cheaper. hehehe. but if not, it's still ok coz it's worth the amount.

Good luck Tom. You'll always have our support. Coz after all, you've always supported us in different ways.

God bless you!

New publisher

Alex, March 18, 2003 - 11:10 pm UTC

Tom,

I hope that you will find a new publisher fast and easy. Best wishes and good luck.

Regards,
Alex

About Expert One on One

Ashiq Shamsudeen, March 19, 2003 - 1:06 am UTC

You mean to say that getting Expert One on One now is impossible ??!! (since WROX is no more ). It'll be good (for the guys who doesn't have ) if u get published with someother publisher or you make a electronic version of it.I being lucky(b'cos i have your book )

Regarding your second book i went some chapters ,its really great .It'll be great if u provide the book with electronic version. Please make sure book should've bigger fonts .Becuase if read Expert one on one for about 1 contieously eyes are getting strained.



Publisher

Steven Cooper, March 19, 2003 - 5:09 am UTC

Tom,

Why not try the Publishers Oracle use... Osborne / McGraw Hill...just don't attach the big price tag like Oracle tend to for their Oracle Press books.

Best wishes
Steven



Tom Kyte
March 19, 2003 - 7:06 am UTC

I'm trying to find out who you all "respect", who is the best...

the authors have little (eg: ZERO) control over the cost of a book -- the publishers pretty much do that.

Please see to it that your book is available World wide

Vijay Sehgal, March 19, 2003 - 7:03 am UTC

Hi Tom,
As most of them have already said it please see to it that the publisher you choose for your book makes it available globally so no one is left out, don't want to be the last few who get the book.

Best Regards,
Vijay.

Your new book : Expert One-on-One Performance by Design

Rich, March 19, 2003 - 9:01 am UTC

I second Alex's comments above, best of luck with finding a new publisher. There were a lot of us who were looking forward to it coming out soon.

So (OReilly), Tom is (OReilly) looking for (OReilly) a new (OReilly) publisher (OReilly)

Mark . Bobak, March 19, 2003 - 9:49 am UTC

Hmm....(OReilly), I wonder (OReilly) who has a great (OReilly)
reputation (OREilly) in the technical market (OReilly.)

If you keep reading this reply, I suspect the name may just
pop into your head, almost like it was subliminal....;-)

Quick question regarding the artifical deadlock in the MTS
case from a while ago: I get it, I can see how it happens,
now, what can or will Oracle do? Can it raise an ORA-0060
or ORA-4020? (Acutally, of those two, I would expect ORA-0060,
but, can/will Oracle do anything, or does the instance
just hang?)

-Mark

Tom Kyte
March 19, 2003 - 11:05 am UTC

hang... sysdba can get in to correct.


</code> http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c07procs.htm#4277 <code>

...When Oracle detects an artificial deadlock, new shared server processes are automatically created as needed until the original user submits a request that releases the locked resources causing the artificial deadlocks. If the maximum number of shared server processes (as specified by the MTS_MAX_SERVERS parameter) have been started, the database administrator must manually resolve the deadlock by disconnecting a user. This releases a shared server process, resolving the artificial deadlock....

My 2.5 cents...

Kashif, March 19, 2003 - 9:54 am UTC

Hi Tom,

So when should we anticipate the book coming out now? I've been fibbing to my wife that I'll be finishing up the Expert One on One really soon so that I can justify my purchase of your new book, but now this setback. Personally, I've never really cared much about who the publisher is, although now that I've briefly glanced my set of technical books, specifically the ones that I like, O'Reilly seems to be the popular choice. Upon further ponderance, I think I like their Oracle related books most among the choices available. Either that or Oracle Press (Osborne). I don't think us readers care much about who the publisher is though, so long as the author is a quality writer, and the subject is one we're interested in. And by that token, I don't think I personally would buy a book simply based on who published it. Besides, I don't think you need a measly publisher to establish your reputation as a first class educator, and most of us (bar the ones who've been chided occassionally perhaps ;)) subscribing to this forum will second that. Though, being a business student by training, I can't help but think that the bigger issue is advertising and marketing of your asktom forum and the new book. I frequently read about how people just come across this website by accident. Maybe they should come here by design, i.e. by some carefully targetted marketing. The defense rests now...

Anyway, good luck with your search, and please let us know when we can expect the new book to come out now.

Kashif

Tom Kyte
March 19, 2003 - 11:12 am UTC

Thanks for the feedback -- yes, I plan on publishing it for sure.

I don't know why -- but i never got around to signing my contract with WROX on this book... I wanted to be able to control the publication date (which I made them slip) cause I was feeling rushed. without a contract -- it was quite easy. So, i have that book totally unencumbered, which makes it much easier for me.

Expert One on One Oracle is the one I'm worried about now -- trying to get that back so I can do it again.

My goal with the "survey" is to find out who everyone respects -- not so much so you all who already come here buy it -- but so that people who don't know me from Adam are tempted to pick it up as well.

List of publishers

Jer Smith, March 19, 2003 - 2:15 pm UTC

If you don't really pay attention to who publishes a book, here's a good list for your comparison:

</code> http://www.bookpool.com/.x/zy9hb31yl4/pl <code>

List of Publishers....

robert, March 19, 2003 - 6:20 pm UTC

"M****soft Press" !!!
Hey ! Jer, you trying to give us a heart attack or something ?

But Springer-Verlag....I have high respect for their textbooks back in college days. High-quality stuff they got...

got lucky... i got the first one ;)

aries, March 20, 2003 - 1:14 am UTC

i have some books from o'reilly and they're good. i voted for it acually.
publish...publish...publissshhh...
;)

Author rules whatever the publisher

Tarry, March 20, 2003 - 3:19 am UTC

I second kashif there..
As an author, teacher, promoter I find Tom as one of the best oracle professionals on he planet. Had he written his own pdf's and sold it, I'd buy it.

I'd go to binding store and get it bound. I've done that for several of my key oracle pdf's. I've done that with bruce eckel's book..

A lot of these companies ,with the self-serving managements are carrying a lot of extra baggage and it's only a matter of time that they'll succumb to theirown undoings.

I only feel bad for all the good and dedicated folks in wrox who will have to get laid off.

Any Publisher But Sams

Jerry, March 20, 2003 - 4:09 pm UTC

I cringe at the thought of "Teach Yourself Expert One-on-One Performance By Design in 21 Days" :)



Oreilly --

Govind, March 20, 2003 - 8:15 pm UTC

Hi Tom,

I have been buying technical books as far back as I can recollect.Honestly I do not make my decision based on the publisher, however the Font and the paper quality would definetly be a choosing factor,they tend to make an unconscious effort to hold the attention while you read. O'Reilly has been my favourite till now.They have a way to present a "Serious Material" attitude and is very cursive at the same time. This is strictly me, and I hope everyone has their own friendly format.
I wouldn't mind reading a page or two from O'Reilly when I refer some thing but definetly cannot read from Osborne or Others. Wrox seems to give an impression of cramminess. Too many words in a page.

Having said all of the above
I intend to complete and comprehend your book back to back before your second book.
So Its all about the author and not the publisher. O'Reilly or NOT O'Reilly ...

Thanks
Govind.



Oracle Press or O'Reilly

Chip, March 20, 2003 - 10:55 pm UTC

Looking at my Baker's dozen bookshelves filled with computer books, I find McGraw Hill/Osborne # 1 and O'Reilly #2 overall (with a wide variety of other publishers). Personally, I am more inclined to buy an Oracle Press or O'Reilly book when shopping since their quality is consistently high and I know I can learn from reading the book.

Possibly a publisher could purchase the digital rights to "Expert One-on-One Oracle" and include an electronic copy on CD with Tom's new book.

If O'Reilly publishes Tom's new book, I wonder what the cover would feature (goofy idea: a soaring Kite).

Very thankful and grateful for Tom's numerous examples and explanations :)

Just an Idea...

Richard, March 21, 2003 - 4:25 am UTC

Tom,

I am sure I speak for many when I say that technical publications would be much easier to use in electronic format.

By publishing in electronic format, you could bypass publishing houses entirely.

I am completely ignorant of the means of preventing electronic publications being copyied etc., but perhaps you could create a downloadable .exe which created the publication. You could incorporate a passsord that expired periodically, with a replacement password sent to the purchaser by email?

Anyway, I look forward to your publication, whatever format it's in!

Tom Kyte
March 21, 2003 - 8:41 am UTC

A .exe? what is that ;) is that something you need to run software on legacy windows machines?

Re: My Last Posting.

Richard, March 21, 2003 - 10:53 am UTC

OK, point taken! Nonetheless, would you consider electronic publication? It'd help with errata update, too.

Regards.

Tom Kyte
March 21, 2003 - 11:01 am UTC

It would be totally up to the publisher.

Book Cover...

robert, March 21, 2003 - 12:49 pm UTC

>>If O'Reilly publishes Tom's new book, I wonder what the cover would feature (goofy idea: a soaring Kite).

I rather like the portrait sketch you used to see on Tom's article in Oracle magazine in the past. That was a really good drawing....kinda like some in MAD magazine....


Re: Book Cover

A reader, March 21, 2003 - 1:11 pm UTC

As long as it's not the same as the other book. Tom's eyes following me around the room is not a comfort to me. :-;

Please don't limit

Vijay Patel, March 21, 2003 - 2:21 pm UTC

Hi Tom, Well I know you will try level best to make others to understand your opinion about database, but here is my point. Please don't limit number of pages on your book. I would like to read more and more facts justified with reasonable number of examples. In reality examples make things very clear.

Thanks for your good work.

Anything close to Wrox quality

Sam, March 24, 2003 - 4:03 am UTC

Are you sure Wrox is going out of business? Amazon is still taking pre-orders of your book.

For your new publisher, I don't have any preference. Personally, I love tech books from Wrox. Unique cover page, proper font styles/sizes (one font style in description part and other style in coding parts), page layout, paper quality make my reading easier. If you have a full control over those book attributes, please remain it the same. Anyway, I like this cover page </code> http://www.wrox.com/news/903.htm <code>

If you can make it by May 14, it'll be great. I have to go back Thailand for good on that day.

Thanks for your dedication to asktom and Oracle Expert One-on-One.

Tom Kyte
March 24, 2003 - 7:48 am UTC

Umm, yes, WROX and all related companies are *gone*, being liquidated.


There is about 0% chance of May for a release date. I have to

a) find a new publisher
b) finish the book

you see -- i stopped, I have no review team in place, no contract -- just a bunch of doc files.

Oracle Press

David Schwartz, March 24, 2003 - 8:15 am UTC

What about Oracle Press?

If not May, then when........

Tarry, March 24, 2003 - 11:38 am UTC

Well Tom,

Responding to what you said.......
--Aren't you being approached by publishers(given the success of the last book)?
--May , ofcourse it won't be, but when then?
--I though you got the book reviewed by jonathan, steve, and others, what does that got to do with the wrox's demise(if you will)



Tom Kyte
March 24, 2003 - 11:53 am UTC

It'll take a while to set up a contact (not too long but May is very close)..

It takes a while to go through their edit and review process...

It takes a while to "fix" the book -- I won't be able to say "using your electronic copy of Expert one on one Oracle" as liberally as I did in this book...

Then it takes a while to lay it out...

Then it takes a while to index it... Proof it.... and so on.

Then it takes a while to print it, ship it, stock it.


It is not as simple as putting a pdf together

my two cents

Jiu, March 24, 2003 - 12:14 pm UTC

How about put the chapters from "Expert one on one Oracle" that you want to reference at the end of the new book as appendix. Then you just replace the reference to the on-line pdf with appendix number. In this way, you don't need to rewrite, incorporate, and index them into the actual content of the new book. Or, if it takes too much paper, put them on a CD as supplement.

Tom Kyte
March 24, 2003 - 12:20 pm UTC

I don't "own" Expert One on One Oracle (yet). WROX does, they are selling themselves (liquidating). Some company may or may not purchase it.

It is all in big "limbo town".


If we could put them on a CD, we could have put them on the web in pdf as planned...

Curlingstone Publishing

Sam, March 24, 2003 - 2:26 pm UTC

I just went to Barnes and Noble today and found some new database books printed by a new publisher called Curlingstone Publishing, </code> http://www.curlingstone.com <code>
They specialize in printing database books. The one that I browsed was "SQL Server 2000 Fast Answers for DBAs and Developers". Compared to Wrox's book side-by-side (regardless of content), this book creates the same feel as Wrox's. You might wanna check at your local bookstore.

Tom Kyte
March 24, 2003 - 3:06 pm UTC

he he, no wonder curlingston reminded you of WROX:

Peer Information Limited
Active Path Limited
Friends of Ed Limited
Wrox Press Limited
Glasshaus
Curlingstone
Tect

Online journals include:
ASPToday
C#Today


all one in the same. hang onto that curlingstone book -- It just became a collectors edition.





X Curlingstone....

robert, March 24, 2003 - 2:34 pm UTC


Yuck...so sql server-ish...

Tom Kyte
March 24, 2003 - 3:09 pm UTC

Oh, are they going out of business too ?

wait a minute!

J., March 24, 2003 - 6:38 pm UTC

Quote/
"Followup:
Umm, yes, WROX and all related companies are *gone*, being liquidated.


There is about 0% chance of May for a release date. I have to

a) find a new publisher
b) finish the book

you see -- i stopped,
b1) I have no review team in place,
b2) no contract --
b3) just a bunch of doc files. "
/Quote

I can help with a)
I can't wait for b)
I can do b1) (just like reading right? I won't charge nuttin either:) )
don't need no stinkin b2 with me brother! I trust you

finally, I've got Word on my windows box, I can read those doc......

HEY!! WAIT A MINUTE!!!




publishers for the common man

dharma, March 24, 2003 - 8:01 pm UTC

Hi Tom,
O'reilly has been in its own class for techno books, well steve adams, steven fstein authors we know of ..and they seem to do good ;)

well the other choice would be apress books eventho a whole lot of their books tend to be MS, a few java they are agood publishing house.

cant wait for your new book..

chaim katz, March 26, 2003 - 11:11 am UTC

I think O'Reilly is the natural choice. Their book Oracle Performance Tuning came out in 1996! I can't imagine that they're still selling any copies. They need a performance tuning book and you've got it. All the best.

Your New Book

Trevor Williams, March 26, 2003 - 6:48 pm UTC

Tom
I hope that you are seriously considering making your new book an e-book. Now that Wrox is gone. I for one am hanging out for a copy.
Come to think of it, "Implications of Adding Creditcard Facilities to a Website" may be a useful topic.
t

Are you covering RAC in your new book !

Shankar Govindan, March 28, 2003 - 2:03 pm UTC

Would like to know if you are covering RAC related stuff in your new book ?

thanks,

shankar

Tom Kyte
March 28, 2003 - 4:18 pm UTC

a teeny tiny bit -- nothing in depth.

http://www.expert-oracle.com/

Alberto Dell'Era, April 08, 2003 - 8:51 am UTC

Tom,

has the site </code> http://www.expert-oracle.com <code>"gone under" too ?

If yes, are you planning any replacement ?

Alberto




Tom Kyte
April 08, 2003 - 12:30 pm UTC

their servers at wrox are just running -- I would assume the first time they crash, it's over.

Not planning a replacement as yet -- that'll be up to my new publisher.

Your Book

A reader, April 09, 2003 - 10:26 am UTC


If i had lots of money, i would have opened an international publishing house just for you. :)



New Book ...

ranajit, April 10, 2003 - 9:33 am UTC

Why not make the performance book available online from oracle's website, like the technet.If someone is trying to get into Oracle I hope they will start with oracle's website.

Also you can work with Amazon if they can make it available online. I always read the reviews there before buying any book even from authors I don't know.

Like someone mentioned above , we can download in pdf , pay you though paypal or something.

Tom Kyte
April 10, 2003 - 9:59 am UTC

I've signed with Oracle Press -- It'll be out this summer later.

so will it be on tech net ?

A reader, April 10, 2003 - 10:02 am UTC

as you going with oracle press will it be on technet ?

or do they have any online forum or online books or something ?

Tom Kyte
April 10, 2003 - 10:22 am UTC

it'll be an old fashioned book on paper with ink.

Oracle Press ??? Yuck !

A reader, April 10, 2003 - 1:34 pm UTC

Sorry, but I -- and several people whose opinions and Oracle skills I admire -- have a thing about Oracle Press.

Most books are of low quality and I would far rather look up Oracle online docs or Tom's site.

I can send you a list of grotesque errors and misconceptions about Oracle they put down on those... hmmmm "books" -- a lot of those myths you talk about.

Except for a couple of reasonably well-written ones. They are exceptions. I hope yours is one of them.

Cheers !



Tom Kyte
April 11, 2003 - 7:49 am UTC

I can do the same for any set of books tho. Any set.

What I discovered from the "survey" I took is that people didn't really care, they looked at the author more than the publisher. I found McGraw Hill (Oracle Press) to be the most "stable" out there -- that was a driving factor for me.

So...

Kashif, April 10, 2003 - 1:47 pm UTC

I guess you can remove/modify the informational message on your homepage which mentions your publisher went under... Can't wait for the new one to come out. Thanks for all the work you put in.

Kashif

P.S. What made you choose Oracle Press in the end?

Tom Kyte
April 11, 2003 - 7:50 am UTC

stability. don't want to be in the same boat 2 years from now..

Congratulations

A reader, April 10, 2003 - 3:04 pm UTC

Hello
I'm really happy you found a publisher.
I don't see why not Oracle Press
After all it is not a dress I'm going to wear
It work
As long as the book containts excellant information
as you always gave us
there is nothing wrong with the book.
Keep up the marvelous work
Bless you

Great News

Sriram Kumar, April 10, 2003 - 11:28 pm UTC

Hi Tom,

It was great to hear the news that your book will be published thro Oracle Press.

Keep up the good work.

Tom is Best




yes this is a great news

Ajeet, April 10, 2003 - 11:51 pm UTC

yes -- Tom this is a great news that your book is coming in summer -- should i say it will come in June this year.

Tom Kyte
April 11, 2003 - 8:47 am UTC

hows about august. I'll be done with my part around the middle of july.

In India I would blindly books published by Oracle Press

umesh, April 10, 2003 - 11:54 pm UTC


Great News indeed!

Yogeeraj, April 11, 2003 - 12:14 am UTC

You couldn't have found a better publisher!

Everyone is eager to get hold of it asap.
Hopefully, it will be available everywhere in this world asap.

best regards


It is really a good news!

A reader, April 11, 2003 - 5:43 am UTC

Tom,
The help I got from your site (I am sure others too) and previous book is really great.
You are an excellent example of real help especially at the time one needs it. I wish you all the best for your new book, and looking forward to it.


At last the wait is going to pay off

Vijay Sehgal, April 11, 2003 - 8:31 am UTC

Hey Tom,
Good to hear that finally the book is going to arrive soon, simply was waiting for that to happen. One request though please see if it can be made available globally so that we are not the left out ones

New publisher: Great news.

Georg B., April 11, 2003 - 8:36 am UTC

Great news !

Regards,
Georg

Excellent News !!!!!!!!!!!!!!!!

Cristian Perez, April 11, 2003 - 2:54 pm UTC


Great news

Marcio, April 11, 2003 - 3:21 pm UTC

Hi Tom,
Great news to us.
But, I hope this time the translation to another language like portuguese be better than last one. Hard to say that in English...
They translation
INSTANCE -> COPY, TABLESPACE -> table's space word by word, and so on. In this time I'll buy English version.
Congratulation.

Cheers,
Marcio.


In other countries

A reader, April 12, 2003 - 2:38 am UTC

When will it come in India?

performance toolkit

Hrishy, April 21, 2003 - 7:37 am UTC

Hi

Tom in expert one to one..i feel the amount of importance given to statspack is very less..i find myself this has got much better examples of finding problems with statspack..Was interested in knowing wheather in your new book..would you show us some case studies wherein you would discover the instance problem with statspack..

regards
Hrishy

Tom Kyte
April 21, 2003 - 8:19 am UTC

yes I do

topic proposal

Alberto Dell'Era, April 22, 2003 - 3:26 pm UTC

Tom,

i would suggest to expand on the subject of cursors,
continuing from what it was left on the botton of
page 440 of your book when you said "this statement
will be parsed once per session if in a procedure,
and once per pl/sql block ...".
I think it would be useful to cover things like
implicit pl/sql caching, session_cached_cursors,
parsing of ref cursors and so on.
Those are all things that i later found on your site, but
of course a brief organized dissertation (note the
academic-like world ;-) ) from you may clarify
things further, and it would be VERY useful for
the general audience IMHO.

I don't know whether your new book may be considered
a continuation of Expert One-On-One, so this request
may be inappropriate ... sorry if it is.

BTW: why don't you open a thread or something like that
for requests ? Like the old site www.expert-oracle.com
at defunct Wrox ... perhaps here on asktom.

Congratulations for having found a new home!
Bye
Alberto

Your new book : Expert one-on-one performance by design

Reader, April 24, 2003 - 11:08 pm UTC

Tom,
It is great news Tom you have finally found a publisher. I back ordered your book in March 2003. Little did I know that Wrox is going out of business. You have been an excellent source of info for me along with Steve Adams. Keep up the good work

Great book

John, May 01, 2003 - 12:56 pm UTC

Hi Tom,

Is it avaliable now? Or could you please let me know when it will be released?

Thanks.

Tom Kyte
May 01, 2003 - 2:35 pm UTC

july 15 is my deadline, say august

buy this book for this chapter alone (so you had an accident)

Hrishy, May 02, 2003 - 12:27 am UTC

Hi Tom

Was browisng through the detail specification of this book..and was really excited by the contents of this chapter..I have not seen the book but would certainly by this book for this chapter..as that chapter seems to be thoughts of an seasoned professional penned down :-)

But for the first time perhaps i have a complaint :-)..the book is supposed to have been titled..export one on one performance shouldnt all experts know about the oracle myths before reading this book..:-D.wish Tom had expanded the chapter So you had an accident and totally skipped oracle myths .

why is august taking so long ?

Alvin, June 03, 2003 - 6:10 am UTC

why ?

can't we just....

select to_date(sysdate+60) from dual;

? after all it did work on my db....

when will the book be released?

Jianhui Zhang, June 04, 2003 - 4:10 pm UTC

Tom,

I have heard it would be released in early May. But i checked the search engine and didnt find anything. Has it been postponed?



Tom Kyte
June 04, 2003 - 4:50 pm UTC

August, the publisher (WROX) went out of business.

feedback after reading ch01 online

Jianhui Zhang, June 04, 2003 - 4:48 pm UTC

Tom,
The ch01 was interesting with lots of real world examples. If possible, you may change the tone when you say do this, not to do that, so some readers may feel not so offended. Why i said that is because you mentioned some rules, like

It's a team effort
Read the concepts guide
...

Themselves are great, just the tone in your book may not sound so friendly, like you are a DBA and DEVELOPER's manager, telling them rule 1, 2, 3... Because i think writing a book is little bit different than answering questions in this site, why not make it more friendly and still let the reader get what you want to say.


As for the technical topics, i 'm an advactor of the concept that it's design for performance instead of tuning for performance. I had some clients who wanted the developers to write effecient SQL in order for better performance, but when i asked how they took the performance into account during the design phase, they simplely couldnt tell me anything!!! They can have top nouch developers but if they design the bad system, it's gonna have performance problems. Most of designers of the real world are business experts instead of the technology specilists i have seen especially in the home grown projects, then they hire consultants to do the coding, not a good approach i think.

Personally, i summarize the performance related issues into two catagories, which are I/O and contensions.

SQL tuning, memory tuning, physical design, etc, all their goals are the same, to minize the IO, both physically and logically.

Scalibility, concurrency are all about contensions, since database is an open shared environment.

So better performance = minized i/o + high concurrency

let's say the I/O speed is given(base on your hardware), then:
the number of transactions(performance of single user) = IO speed / io consumption per transaction under single user mode.

Total throughput (performance of the overall system) = number of transactions * concurrency, so if the concurrency is close to linear function of the number of users, i give this system thumbs up! This system has high scalibility and concurrency.

This is the formular i use to convince my clients to choose the good approach to gain the performance, instead of using hit ratios, parameter settings, etc which are trivial and dont provide you a higher level view of your system.

I havent found any DB related performance problems fall out of these two catagories. As for the CPU usage, since most of DB applications by nature are not scientific caculation intensive, it's really hard for some "bad" developers to write bad programs and algerithm to suck the CPU up before the I/O and contension problems are raised.

So if you can make it clearly why we need to do this (minimize the IO or contension) in your new book, i think it will give us a very clear goal and know why we are doing that.

Regards,

How about some "Read for Fun" Material Too ?

Robert, June 04, 2003 - 6:31 pm UTC

Hey Tom, maybe you should make chapter out of this:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:3512484632553 <code>

or put in as an apendix.
All technical stuff and no fun make us a dull boy, you know...





We can wait till Augest

Ajeet, June 04, 2003 - 8:29 pm UTC

Hi Tom,
No doubt --you taught us that oracle is not only a database but it is a rational and science..we can wait till Augest for your book...we will learn many things from this book again.

Thanks
Ajeet

expert one-on-one Oracle

Valter Rogério, June 11, 2003 - 12:44 am UTC

Hi, i buy your book. It´s a translation for Portuguese from Brazil. I´m in the begining and it have very, very mystakes in the codes. There was a bad translation, mabe for a dentist or a lawyer. Finaly, where i can find an errata ? The Wrox don´t have. The "ciencia moderna" </code> http://www.lcm.com.br/ <code> don´t have too.

Tom Kyte
June 11, 2003 - 6:57 am UTC

sorry, wrox no longer exists, there is no "errata site" anymore, there is no "book" anymore (expert one on one Oracle from wrox, out of print)

errata

Dmitry, June 11, 2003 - 7:56 am UTC

Why don't you move/create errata site somewhere here?

Tom Kyte
June 11, 2003 - 6:08 pm UTC

because I don't have all of the prior errata.... and I'm in the midst of a new book (out in august).

if the expert one on one gets printed again, there will be an errata site

Who picked up the right to Expert-One-on-One Oracle?

Eric Ma, June 11, 2003 - 9:04 am UTC

Does APress have the book right? Will they run reprints? Or did Oracle Press buy it from Wrox? It is such a mistake for Wiley not be buy your book. Maybe it is not in their stratigic plan to publish database books. In my opinion, half of the 30 or so books they bough from Wrox are not that great and they missed a few gems.

Tom Kyte
June 11, 2003 - 6:11 pm UTC

Yes, apress currently does.

Wiley tool MS only titles, nothing else.

We are waiting and seeing what we are going to do with it.

When it'll be released ?????

Ashiq Shamsudeen, June 11, 2003 - 9:19 am UTC

Hi Tom,

When your second book will be released(any dates in near future) ??
Another thing It will be great if you give us supplement copy of Expert One On One(in CD) along with your second book ;)

thanx

Tom Kyte
June 11, 2003 - 6:11 pm UTC

August for the new book.

Not quite possible for Expert one on one, you see, I don't own it.

Expert one-on-one insurance program

Worried, June 11, 2003 - 6:34 pm UTC

Perhaps we need to all take out insurance policies against our expert one-on-one! What if mine is destroyed by fire, flood or other natural (or unnatural for that matter) calamity?!?!?!?! Hey this could be a great business opportunity: out-of-print tech book insurance.

Expert one-on-one insurance program

Kalita, June 12, 2003 - 4:41 am UTC

Excellent idea :-)

Copyright of the new book

Harish, June 12, 2003 - 8:04 am UTC

Tom

Once your new book is published, who will hold the copyright on the material, you or McGraw/Hill?

Have you considered using the GNU or even the LGPL licence for your new book? If I remember correctly, Havoc Pennington of GTK fame used the GNU licence for his GTK programming book. His book is also available on the web, last time I checked!

Harish



Tom Kyte
June 12, 2003 - 9:21 am UTC

McGraw Hill will.

Sorry, bit old fashioned, you see -- they do a ton of stuff for me that I don't have to deal with. It is a business relationship from which we both derive benefit.

Harish, June 12, 2003 - 10:02 am UTC


Errata and code (examples) are still available

tobias, June 12, 2003 - 11:47 am UTC

</code> http://www.wrox.com/dynamic/books/errata.aspx?isbn=1861004826 <code>

for both (code download via button at the top of the page works)

Tom Kyte
June 12, 2003 - 1:03 pm UTC

thanks, don't know how long it'll be there...

Can't access the link to your new book (DRAFT)

Deepak, June 13, 2003 - 9:59 am UTC

Tom Kyte
June 13, 2003 - 10:38 am UTC

company (WROX) went out of business in march.

new company i'm working with (Oracle Press) is setting up a "beta books" site that will be available soon.

Tom's New Book

Paul, June 13, 2003 - 11:45 am UTC

Barnes and Noble are already offering 20% off!:

</code> http://search.barnesandnoble.com/booksearch/isbninquiry.asp?ISBN=0072230657 <code>

This site says the book's available on August 25 (Amazon says August 28). Are the bookshops opening at midnight for this one? ;-)

Aha..So it's there finally!

Tarry, June 29, 2003 - 11:35 am UTC

Tom,

The book turned out more thicker(828) than expected(I like that).

I see the title also changed.

Hopefully soon it'll be in my bookshelf.

Cheers!


30% off on amazon.com

A reader, July 01, 2003 - 1:03 pm UTC

Tom,
Do you know how many copies of your new book have already been reserved?

Tom Kyte
July 01, 2003 - 1:07 pm UTC

nope

Pre-Orders

A reader, July 01, 2003 - 1:14 pm UTC

Hmmm. I would have thought you'd hear a little "ka-ching" every time one was ordered. :-)

Excellent 2nd Chapter!

Brian, July 01, 2003 - 5:44 pm UTC

Just read the preview of the 2nd Chapter, and I thought it was awesome. Of course, I don't understand the transition from chapter 2 to chapter 3.

Tom, your writing ability has improved significantly since your last book.

Brian

Tom Kyte
July 01, 2003 - 7:51 pm UTC

think of the chapters as "books unto themselves"

(and thanks)

mistake on page 17-18 of chapter 1

freek, July 03, 2003 - 7:51 am UTC

Tom,

I think your transaction sequence on page 17-18 of chapter one is wrong.

If you run it in two sessions, you would get the correct answer.
The mistake is made (i think) in the script with the autonomous transaction. There your last statement is updating sum_of_salary for department 1, while it should have been department 2 (where session 1 added the new employee)

greetings

Freek

Tom Kyte
July 03, 2003 - 9:33 am UTC

you are correct, I need to move the commit and cannot use the autonomous transaction.

thanks for reading so close ;) no one else caught that yet! the example is "sound", I was trying to make it easy to see in a single session. need two sessions - each does their work AND THEN they commit.

it makes the example more "interesting"

Is USER a function or pseudo column

Kalita, July 03, 2003 - 12:15 pm UTC

I remember you saying somewhere in asktom that USER is not a actually a pseudo column but a function. But you refer to USER as a pseudo column in Chapter 2 page 25 :-) Tried to find out where you said that but could not.
Regards,
Kalita

Tom Kyte
July 03, 2003 - 8:03 pm UTC

thank you, that'll be corrected

Mistake found on pp.17-18

A reader, July 03, 2003 - 4:49 pm UTC

Would it be a problem to copy and paste the script that had the error so everyone can follow?

Thanks,
Arun


Tom Kyte
July 03, 2003 - 8:11 pm UTC

it is on the website there -- goto the homepage of asktom.oracle.com and click thru to the site to read the samples online. I followed up there with the fix.

need to upload different chapters

hrishy, July 04, 2003 - 6:49 am UTC

Hi Tom

I had a chance to look at some of the chapters of your new book all the three chapters uploaded there talked about how to build the performnace into the design..(which i belive is the books theme)but as a DBA/developer i would like to know about this .but the practical world is all about troubleshooting performance (and we dont get a chance to be involved in design) after the system goes into production..it would be great if you could upload the chapter on case studies with statspack..rather than just design design & design.


regards
Hrishy

P.S:how about a book called best of asktom ;-) where you publish a assorted questions here as casestudies

regards
Hrishy

Tom Kyte
July 04, 2003 - 8:36 am UTC

well, you only have a month to wait to get the whole thing.

And I disagree about your "practical world", that is not my world. And we'll only get the world to be in the right place by working against "your world" in the end.

I don't have a chapter on case studies with statspack. Everyone is a new and exciting mystery. I wail against the "hey make this go faster" throughout.

You did notice the "title" of the book?

Effective Oracle by design

there is a theme to that title.

Your books

Erik, July 10, 2003 - 1:29 pm UTC

Hello Tom,

Today I would order your books, but is it true your publisher doesn't excist anymore ?

Your books, expert one on one , would they be released by another publischer ?

Kind regards

Tom Kyte
July 10, 2003 - 3:06 pm UTC

apress is re-releasing it.

</code> http://www.apress.com/book/bookDisplay.html?bID=220 <code>

What about...

Kashif, July 11, 2003 - 9:30 am UTC

Hi Tom,

What about the "Oracle 9i Java Programming: Solutions for Developers Using PL/SQL and Java" book (on which you're a technical contributor) and the "Professional Oracle 8i Application Programming with Java, PL/SQL and XML"; are they being re-released by apress or someone else?

Kashif

Tom Kyte
July 11, 2003 - 11:02 am UTC

they may well be. I was paid by the page for those -- no royalties -- so I don't really "follow them" ;)

Excellent

Brian, July 11, 2003 - 5:45 pm UTC

It's good that your book is being re-released. I picked up a copy (WROX) from www.bookpool.com when you had the link up.

Anyway, two questions:

1) Have you ever considered writing anything about Oracle
security? I haven't seen much out there that addresses
security around Oracle databases. And I would imagine
that folks would love to steal clob and blob space.

2) Will the code be up for your previous book at Apress?

Thanks

Tom Kyte
July 12, 2003 - 9:12 am UTC

1) A guy I work with -- David Knox -- is doing such a book with Oracle Press right now.

2) should be

no eyes watching!

J., July 11, 2003 - 6:15 pm UTC

(0)(0)
/\
-____-
\__/

(had I waited for the Apress release you wouldn't be staring at me about lack of bind variables all the time)



A reader, July 15, 2003 - 9:57 pm UTC

I have your Export one-on-one book. I am going to order Effective oracle by design soon.

Probably it’s a strange question :)
Are you planning to update your Export one_on_one book to cover 10i topics? Or Are you planning to write any other books?


Tom Kyte
July 15, 2003 - 10:12 pm UTC


I am planning on finishing Effective oracle by design tonight.... (15-jul-2003) the last of the tech edits.

after that -- vacation....

beyond that, well, I haven't thought it out yet ;)



new book

A reader, July 16, 2003 - 9:23 pm UTC

so are you done - now that we are on july 16th?;)

Anyways, I am eagerly awaiting the release. Loved your
three sample chapters - your writing ability (which was
anyways excellent) has shown remarkable improvement
since the first book - it is even better plus the wealth
of knowledge you have makes it a cool combination!

Please keep up the good work - always wonder how you
find time to do all this *and* run this awesome site!!

Regards
Menon:)

A reader, July 17, 2003 - 4:59 pm UTC

Good to know that you have finished Oracle by Design book before your vacation :)

>>beyond that, well, I haven't thought it out yet ;)

Ok I will ask the same question after your vacation :) :)

Have a wonderful vacation!

Release the book in India at the same time

A reader, July 25, 2003 - 8:46 am UTC

I request you to make sure that you release the book at about the same time its being released in US.
Thanks

Tom Kyte
July 25, 2003 - 12:28 pm UTC

when I open "kyte publishing" -- I'll make sure to do that :)

right now, it is all up to McGraw-Hill and their schedules, over which I have zero control.

Expert One on One Oracle is still out of stock

A reader, July 30, 2003 - 6:37 am UTC

Hi Tom,

I was thinking of having this book as my birthday gift and had placed one order on Amazon.co.uk. But recently they replied back saying the book is out of stock. Almost three weeks now. Will you please suggest some other site on which the book is available? I checked on wrox, but couldn't find the title there :(

Regards,
A reader who wants to learn from this book!!!

Tom Kyte
July 30, 2003 - 7:37 am UTC

wrox no longer exists really -- they went out of business.

apress bought the book:

</code> http://www.apress.com/book/bookDisplay.html?bID=220 <code>

and they will be reprinting it soon.

Professional Oracle 8i Application Programming....

robert, August 04, 2003 - 2:44 pm UTC

Tom, what chapters did you write in this book ?
"Professional Oracle 8i Application Programming with Java, PL/SQL and XML ". Your writing in this is after or before
"Expert 1-1" ?
thanks

PS:many of those other authors can use your hair-stylist ;)


Tom Kyte
August 04, 2003 - 4:43 pm UTC

I wrote just one -- chapter 16

easily identifiable by the trademark sqlplus prompt :)

I always get a good smile when I look at the guy to my right on that cover. "nice hat"

this other book...

robert, August 04, 2003 - 4:51 pm UTC

>> I wrote just one -- chapter 16

BTW, can I assume that by virtue of the fact of your contribution
to this book that it's got your blessing...like you reviewed it ?

Thanks



Tom Kyte
August 04, 2003 - 5:38 pm UTC

I'm listed as a technical reviewer, that does not mean i read the entire book -- nor that I necessarily agree with 100% of everything that is in there. As a technical reviewer, we simply raise red flags, ask questions, pointer out errors.

whether they get fixed or not is beyond me.

Also, bear in mind, I was working on my own book and doing my job at Oracle and doing this at the same time, I reviewed a small section of that book only -- the core database related stuff (don't even ask me what chapters, i forget)

out of curiosity

Dave, August 04, 2003 - 7:33 pm UTC

just out of interest, what is your Job at Oracle? Are you purely a manager of people or do you develop as well. Also what kind of people are your clients? Do you develop their applications for them or just advise?

Thanks

Tom Kyte
August 04, 2003 - 7:49 pm UTC

well,

o I have a team of 12 right now. specialize in various technologies. for example Cameron O'Rourke ( </code> http://devtrends.oracle.com/ <code>) does java architecture. Sean Dillon does XML technologies (he did my rss feed for example). Kimberly Better does portal/forms/reports/disco. Another person does high availability/disaster recovery. Another ias. Another capacity planning/mgmt. Another collab suite and so on.

o I still write code, build systems. Not as much as I used to, but still do that alot.

o I do work with development at HQ from time to time but more in a "this is what people want" role then in "here is some code" these days.

o I work in Oracle GEH -- Government, Education and Healthcare. So one day I'm with NASA, the next with a hospital, then a university and then the Pentagon. We are about 10% of Oracle's customer base. I normally provide 411 (info)/ 911 (help!) services for them. Benchmarking, Root cause analysis (i hate that), tuning, architecture, program review sort of stuff.



Good to know about you!!!

Reader, August 05, 2003 - 3:04 am UTC


Tom's Job...

Tarry, August 05, 2003 - 5:04 am UTC

Tom's job as a VP is excellent in terms of what a techie would want. A more managerial but still a techie job where you can/would jump in to code. It's amazing the way he can do this(which is a lot of work when you're solving problems of other people who call for all over the world) and then also fly all over the place to the ailing ;) clients...

I think it's more like when you get sick and tired of seeing too many people , you can always come back and sit behind asktom.com and talk to your machine, who politely listens and does all you want, right Tom?

Keep up the excellent job!

Cheers!

Tarry

A reader, August 05, 2003 - 6:23 am UTC

even though your team members specialize in specific do they share knowledge and how

Tom Kyte
August 05, 2003 - 7:28 am UTC

yes, of course. that is their job.

we are in a technical pre-sales organization. We provide 411/911 help for our division.


A reader, August 05, 2003 - 7:42 am UTC

sorry for not making myself clear
I was thinking about the sharing knowledge within your team

Tom Kyte
August 05, 2003 - 8:16 am UTC

definitely... yes.

Soon means how soon???

Reader, August 05, 2003 - 9:38 am UTC

Hi Tom,

I have pre-ordered Effective Oracle by design book and I see dispatch date to be May-2004 on amazon.co.uk site. I hope the book will be available in a month or so. Let us know your thoughts/views :-)

Tom Kyte
August 05, 2003 - 2:11 pm UTC

I asked the publisher and this is what they say:

The US Edition (USE) is available for sale internationally as soon as it is published. The UK and our other international subsidiaries already have the book on order. It will be available immediately after release. Conditions in India require Tata/McGraw-Hill to license English language translations in order to price to the market. That causes a bit of a delay, but I'm sure they'll get your book out as soon as possible there. Foreign subsidiaries and third party publishers usually wait until the book is actually published before they consider it for translation into other languages. However, I know that we've had some interest on this front already. Any feedback from readers that will help show the demand for specific languages is alway helpful.

Got few more months...

fan, August 05, 2003 - 12:22 pm UTC

>>and I see dispatch date to be May-2004
Hmmm so I got 9 more months to finish up the first book....


Found Errata and Code Samples

Fred, August 05, 2003 - 10:28 pm UTC

This site has the errata and code samples for Expert: One-On-One. Tom, maybe you can download these and make them available to your website?

</code> http://support.apress.com/code_listing.asp?s=0&view=all <code>

Tom Kyte
August 06, 2003 - 7:35 am UTC

they belong there -- with the book! apress "owns" that book now

How do you manage all this....

Ajeet, August 05, 2003 - 11:44 pm UTC

Tom -- What is your secret..of handling :
1.Answering 10-20 questiones (sometime you write all the code as an answer ,you have done more than once in reply of my question)...
and then
2.work as a VP of Oracle..
and
3.Write such great books
and last but not least--always eager to help unknown oracle developers like us..
We too work somewhere in the world ,of course in oracle realted technology but most of the time --we find 12 hours insuffiecinet to complete our project or assigned work and come to you for a solution,helping others is a distant things....
Just curious--how you do all this with such a ease...

Thanks Tom ---looking forward to read your new book and hoping that i will learn from it,

Ajeet

Anthony Reddy, August 06, 2003 - 3:13 am UTC

hai Mr. Tom,
thanks for your great books expert One on One Oracle
and the coming one.


Book Signing

David, August 06, 2003 - 9:26 am UTC

Tom -

When the new book comes out, where will the book signing be? ;)

Tom Kyte
August 06, 2003 - 9:51 am UTC

OracleWorld of course! The bookstore there.

any particular day you will be at OracleWorld?

A reader, August 06, 2003 - 10:51 am UTC

"OracleWorld of course! The bookstore there. "

Do you know what days of OracleWorld - I would love to
just meet you up there!

Menon:)

New Release

David, August 10, 2003 - 11:31 pm UTC

Tom -

If Wrox no longer has your book, where can we download the source code examples?

Thanks,
Dave

Tom Kyte
August 11, 2003 - 7:31 am UTC

Kalita, August 11, 2003 - 8:36 am UTC

Hi Tom,
Why don't you put a link to new Expert One-on-One Oracle in your home page or include it in the messages? I think it will be helpful to people who did not buy the original copy (not me :-)).

Regards,
Kalita


Tom Kyte
August 11, 2003 - 9:56 am UTC

it is on amazon now -- they got my name wrong so the click on "all of my books" doesn't show it yet. they are working on that.

Can't access: asktom.oracle.com/~tkyte/OracleWorld.html

fan, August 11, 2003 - 11:44 am UTC

Tom the link you provided below can only be accessed by
registered attendees, is that what you meant it to be ?
Thanks

>> the new SQL Model Clause (if you thought analytics were >> cool -- wait till you see this!) Click here to read
>> about it: asktom.oracle.com/~tkyte/OracleWorld.html.

Tom Kyte
August 11, 2003 - 1:49 pm UTC

goto

</code> http://www.oracle.com/oracleworld/sanfrancisco/conference/ <code>

-> conference -> session catalog



and search for "kyte" in the "speakers last name", that should get you in. I don't seem to be able to set up a straight bookmark.

Oracle 10G

David, August 11, 2003 - 2:45 pm UTC

Tom, going through the conference schedule, I found the next Oracle version is named Oracle 10G. I was checking out the new features, and some of them seem interesting -- like "your" new SQL model.

Are you going to launch Oracle 10G at Oracle World ? When will it be finally available ?

Every new Oracle version brings some (r)evolutionary stuff.

Can you name some of the features you find interesting in it ?

Tom Kyte
August 11, 2003 - 3:16 pm UTC

Sept 8th is the launch date for 10, yes. Larry (not me :) will be announcing it and its features.

cannot say much more then that for until, until the launch anyway...

Just one show by Tom at OracleWorld ?

fan, August 11, 2003 - 6:45 pm UTC

>> 40166 Oracle Database 10G SQL Model Clause
>> Product Area: Database
>> Length: 1 Hour
>> Level: Introductory

man ! you only got an one-hr show at this 3-day event ??!!
..not fair...to me the value of the event just took a big dip...
Are you gonna get a kiosk or something with a big AskTom logo ?


Tom Kyte
August 11, 2003 - 7:04 pm UTC

I'll be in the bookstore once or twice for at least an hour as well. will let you know the dates/times when I know them :)

You'll find me at the HTML/DB booths as well lots. (aka project marvel -- new 10 feature)

Tee Shirts

Tom Best, August 11, 2003 - 10:49 pm UTC

I would pay money for a tee shirt that says "I AskTom"

:-)

tee shirt that says "I AskTom" - that's cool idea

Alex, August 11, 2003 - 10:52 pm UTC

I think that the idea for the t-shirt "I AskTom" is good. Tom any plans in that direction ?

I would go for a T shirt

Jim, August 11, 2003 - 11:48 pm UTC

I would pay good money. Got it in a 2xl?

A reader, August 12, 2003 - 9:26 am UTC

As to the T-shirt, you can count me in.

Who is this other person who is going to co-present your lecture ? I see no description regarding him whatsoever.

Is he your "ghost writer" by the way ?

;)

Tom Kyte
August 12, 2003 - 9:40 am UTC



Andy Witkowski is the original presenter, I'm co-presenting with him. He is a very Sr architect in the SQL languages group and one of the inventors of the MODEL clause we'll be talking about.

presentation

Dave, August 12, 2003 - 11:31 am UTC

Will you be posting the slides you use after the event, us Brits who won't be going need to see :-)

Tom Kyte
August 12, 2003 - 11:54 am UTC

All OracleWorld presentations are archived and made available on otn

t-shirt do it yourself

fan, August 12, 2003 - 1:27 pm UTC

> I think that the idea for the t-shirt "I AskTom" is good. > Tom any plans in that direction ?
> I would pay good money. Got it in a 2xl?
> As to the T-shirt, you can count me in.

yeah, right...i am trying to picture Tom in a booth behind a pile of t-shirts...
But there is this shirt-print kit if you can't wait.

T-shirt

T-shirt salesman, August 12, 2003 - 1:43 pm UTC

Wow... great idea ! I'd love to wear Tom's shirt !

Anyway... if he won't care, I'll be making them myself.

Who wants to buy ? ;)

I will buy

A reader, August 12, 2003 - 3:38 pm UTC


About Model Clause of SQL.

Asim Naveed, August 14, 2003 - 5:08 am UTC

Its very very good to know that SQL has a new MODEL clause.
I was eagrly waiting for it.

I have read it at that site.

Please answer the following.

1- Will MODEL clasue provides the functionality like the
MDX(Multidimensional Expressions/Queries).by Microsoft.

2- Can SQL return variable number of COLUMNS now.
i.e no. of columns of the output depend on the rows
queried. We do this by DECODE right now, but its still
not variabe.

Thanks

Asim Naveed






Tom Kyte
August 14, 2003 - 8:04 am UTC

1) no, it is a sql extension -- but that does not mean 10 won't have what you ask for...

2) sql will never be able to do that -- consider the IMPLICATION on the client for a moment.

you can use collections for this.
you can use cursor variables for this.

else you are using DECODE/case for a FIXED NUMBER of columns.

even with collections and cursor variables you have a number of FIXED columns.

project marvel -- new 10 feature

robert, August 14, 2003 - 10:07 am UTC

>>You'll find me at the HTML/DB booths as well lots. (aka >>project marvel -- new 10 feature)

Tom, you mean Marvel is "integrated" (not sure if this right word) in 10i ?
thanks

Tom Kyte
August 14, 2003 - 10:12 am UTC

it is a feature of the database, install 10 and you'll have it.

A reader, August 14, 2003 - 6:39 pm UTC

Tom,

You should oraganize a conference in which you will be the only speaker, I'm sure your fans will love it..



Neither of your books available in the UK :((

SP, August 15, 2003 - 6:53 am UTC

Hi Tom,

It is mid August now and neither 'Expert One on One Oracle' nor 'Effective Oracle by Design' are available. Please let us know by when will either of the book will be available.

Regards,
SP

Oracle Press says that your new book is available now

A reader, August 15, 2003 - 10:51 am UTC

If you go to the Oracle Press web site, they state:
"Effective Oracle by Design
ISBN: 0072230657, $59.99 US, Softcover, 45 illus. , 688 pages., Available Now"

However, amazon.com still says it's not released yet. Personally, I'll wait for the amazon discounted version. :-)

Tom Kyte
August 15, 2003 - 11:20 am UTC

I would too -- it is orderable right now (august 15th) but won't be shipping until about the 28th.

Honey, I Blew up the Book

Mark A. Williams, August 15, 2003 - 11:41 am UTC

From the Amazon website:

"Dimensions (in inches): 18.00 x 38.00"

That book is a foot and a half wide and over 3 feet high! :)

I am gonna need a new bookshelf.

Tom Kyte
August 15, 2003 - 12:03 pm UTC

i sent that to the publisher :)

LOL

that was funny!!

A reader, August 15, 2003 - 12:29 pm UTC

Anyways - for the cost conscious bookpool is giving
slightly better discount..check out
</code> http://www.bookpool.com/.x/pcyyyykj56/sm/0072230657 <code>


Tom Kyte
August 15, 2003 - 12:54 pm UTC

well, that does *nothing* for my amazon.com ranking!

:)

ranking?!!

A reader, August 15, 2003 - 1:02 pm UTC

hmm...did not think of that! You should perhaps not
publish these kind of suggestions then, eh?!;)



you need to spend $40 at bookpool for free shipping

A reader, August 15, 2003 - 1:07 pm UTC


Don't forget their shipping costs!

A reader, August 15, 2003 - 2:28 pm UTC

Don't forget their shipping costs!

New version of Oracle 10g

Asim Naveed, August 15, 2003 - 3:40 pm UTC

Hi,

MDX is a standard for multidimensional databases as
SQL is for relational databases. Many multidimensional
databases support MDX.

My question is that why doesnt ORACLE support MDX. Why
are they not following the standard as they followed in
SQL. Even if Oracle provides a better solution than
MDX, it will be difficult to write database independent client side applications.





Tom Kyte
August 15, 2003 - 7:40 pm UTC

point me to the site that talks of this ISO, ANSI, or any open standard owned by standards bodies called MDX?


i'm very aware of it -- but how did you turn it into a standard?

anyway, cannot say much more until around 9am pst on september 8th

What is G stands for in 10G?

Tony, August 18, 2003 - 2:01 am UTC

What is "G" stands for in 10G? Is there any URL to check out 10G new features?

Tom Kyte
August 18, 2003 - 7:19 am UTC

g is for grid.

there will be right after the product is announced at OracleWorld on sept 8, 2003

Link for Expert One on One Oracle

A reader, August 18, 2003 - 9:56 am UTC

Hi Tom,

Will you please put a link for 'Expert One on One Oracle' on asktom home page ... the way there is link for 'Effective Oracle By Design'

RE: Link for Expert One on One Oracle

Mark A. Williams, August 18, 2003 - 10:19 am UTC

> Link for Expert One on One Oracle

You can use the 'quick link to all books' and E:1-1 is right there - only 1 click away...

- Mark

Here's a "straight" bookmark to Tom's Oracle Openworld session

A reader, August 19, 2003 - 7:37 pm UTC

related to MDX question earlier in this discussion

Asim Naveed, August 20, 2003 - 8:54 am UTC

Hi,

I cannot find any site of MDX standard, but I have
read something on internet that lead me to believe that
MDX is standard. May be its a defacto standard.

Please go to the following link </code> http://www.microstrategy.com/Software/Products/MDX_Adapter/FAQs.asp#7

and read the question no. 7
here is a cut/paste of Q.7

7. Can you give a couple of rough analogies explaining how ODBO and MDX relate to each other and to multidimensional and relational data sources? 

Answer.

ODBO is to multi-dimensional data sources as ODBC is to relational databases. 

MDX is to multi-dimensional data sources as SQL is to relational databases. 

Also I have read on the following site about OLE DB for 
OLAP.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnoledb/html/msdn_oledbfaq.asp <code>

On the above link , specially see the following question.

Q. Why should I use OLE DB for OLAP?

A. OLE DB for OLAP allows you to access OLAP data through a set of industry-standard interfaces. ISVs and corporate application developers can depend on a single interface for consuming OLAP data, regardless of the vendor or source. Providers of OLAP data can implement a single set of interfaces that allow all OLAP clients to access their data. Consumers can use the COM query interface and data source properties to dynamically adapt to provider capabilities. We expect OLE DB for OLAP to spawn a new market for interoperable OLAP tools.

Thanks


Tom Kyte
August 21, 2003 - 8:03 am UTC

MDX is not a "standard" in the way that SQL, C, Java, SMTP, FTP and other open standards are.

MDX is a proprietary API owned, governed, made up by Microsoft. It is as "open" as ODBC.

You are reading the MS marketing pages. When was the last time they made an open standard?

One more thing

Asim Naveed, August 20, 2003 - 9:11 am UTC


Regarding "variable no of columns in SQL" you said that:-

"
2) sql will never be able to do that -- consider the IMPLICATION on the client
for a moment."

I am unable to understand this please can you ellaborate
this.

Thanks


Tom Kyte
August 21, 2003 - 8:05 am UTC

what would the client do if the first row contains 100 columns
row 2 has 2
row 3 has 50
row 4 has 10


what the heck would that mean?
how could you array fetch?
imagine the CODE you would have?
think about it -- just doesn't make SQL sense.

I don't suggest you to do an ebook version of your book.

juancarlosreyesp@yahoo.com, August 21, 2003 - 9:34 am UTC

Hi Tom,
I don't suggest you to do an ebook, why?
because this will become available through edonkey, an other file sharing software.

For example in the bestdown oracle collection available at edonkey, I found your book "Oracle 8i Application Programming"
1. Personally I wouldn't suggest it to put ALL as ebook.
2. You can put as ebook, only the examples
2. You could put online the book if it has a good security or license control.

This is only my opinion


variable no. of columns

Asim Naveed, August 22, 2003 - 10:52 am UTC

Yes I understand that this kind of "variable no. of columns" does
not make sense.

But what I mean by "variable no. of columns" is not this.

I am not saying that each row have different no. of columns.


What I mean is that for a specific set of data the SQL statement
will return FIX no. of columns. For another data set
the same sql will return different FIX no. of columns.


For e.g
Create table T (region varchar2(10), product(20), sales number(10));

Now I insert 4 regions, and 3 products, each region have
every product, so there will be 12 rows in the table.

Now I want to see sales of each product in each region.I want
regions on rows and products on columns. I will simply
write SQL statement that will contain 3 DECODEs (i.e the no. of products)

Now if I add 8 more products in the table, I will
have to change my SQL statement and add 8 more DECODES.


I want SQL to return 11 columns because I have 11 products now. That is the
no. of output columns change on the state of data not on each row.
If a region (i.e a row) have only 1 product then we will
show NULL or 0 under every other product.


Will 10g have something in this regard.

Will SQL ever be able to do this kind of "variable no. of columns"




Thanks

Asim Naveed.


Tom Kyte
August 22, 2003 - 7:26 pm UTC

six one way

1/2 dozen the other.

there are no plans I am aware of to make the SAME query return DIFFERENT numbers/types of columns from execution to execution.

Arrgh...they're all out of stock...

Robert, August 23, 2003 - 11:06 am UTC

I was just curious if there's anyone out there selling used "Expert O-O-O Oracle". Search on eBay using the term "expert one on one"...this is in the small list that came up :


Mediterranean Cattle and Sheep in Cross-Breeding
» Paperback, 1977 - Not in stock. Add to Wish List

Tom Kyte
August 23, 2003 - 12:10 pm UTC

it is almost shipping from amazon now. it has been reprinted.

difference between reprinted book and original

Ishaque Hussain, August 23, 2003 - 2:14 pm UTC

Hi Tom,
I was wondering if there were any differences between the reprinted Expert one on one Oracle and the original Expert one on one Oracle book? Does it include additional 9i features?

Tom Kyte
August 23, 2003 - 6:26 pm UTC

nope, same exact book.

they just bought it and are printing it. that'll be the end of it.

MDX again sorry.

Asim Naveed, August 24, 2003 - 4:08 am UTC

Ok , i got it.

But i am unable to understand this

six one one
1/2 dozen the other.

Actually, my problem is that I am responsible here
for developing a front end application for OLAP using
Oracle. In this application there are spreadsheet like
reports that are generated dynamically. So I installed
SQL SERVER's analysis services. Analysis services will
bring all data from ORACLE to its own multidimensional
database and store it in cube. And on that cube I can
write MDX. MDX is so easy to write for the kind of
reports i want.

I want a complete ORACLE solution, I know that oracle 9i
also have OLAP built in, but the additional cost is too
much for our client, so we were left with only one option
if we want to do it complete ORACLE and not microsoft, and
that option was to use SQL to retrieve the datawarehouse
and generate dynamic reports. But because I was used to with
MDX for these reports (which were very easy) I find it
very difficult to write equivalent SQL statements,
specially when making metrices (calculated members).

Does this mean that I can say to my boss that in Oracle 9i
release 2 (without OLAP) this is not possible, or can you
give me some alternative solutions. I already know the
MVs and analytical functions.

Thanks

Tom Kyte
August 24, 2003 - 7:42 am UTC

sorry -- should have been

6 one way.
1/2 dozen the other.


Have you looked at Oracles OLAP extensions -- you may very very will find them more powerful, infinitely faster and maybe even "easy".

I find 99.999% of what people try to do -- and believe they need "cubes" for are done 100% using analytic functions. don't know how many times I've taken a "dump from oracle into sqlserver so we can 'cube' it" and turned it into a simple "group by rollup" or "group by cube" or used lag/lead, first/last, etc straight from sqlplus to get the answer in an extremely small fraction of the time.




reviews on amazon for "Expert on-on-one"

Alberto Dell'Era, August 25, 2003 - 3:36 pm UTC

Why don't you call Amazon and ask them to copy the online reviews of "Expert on-on-one" from the Wrox to the Apress edition ?

Should boost the sells a bit since they averaged 5 stars - they win, you win ;-)

Tom Kyte
August 26, 2003 - 7:46 am UTC

i'll ask and see...

i of course have them permanently archived on my machines :)

Expert one- on -one

vivek, August 26, 2003 - 8:58 am UTC

Tom,

Why can't i buy your book from amazon.co.uk.
I have received mail from amazon
----------------------------------
From: Amazon.co.uk [mailto:auto-response@amazon.co.uk]
Sent: 22 August 2003 17:16
To: Andrew Taylor
Subject: Expert One-On-One: Oracle will not become available


Greetings from Amazon.co.uk

We've recently learned from our supplier that the item you requested to be notified about, Expert One-On-One: Oracle, will not be available in the foreseeable future.

-------------------------------------------------------
I am waiting for this book for long time and on your home page it says it will be available 'september 2003', but this mail from azazon has shattered the hope.

Book seems to be availbale from wrox, but thats a quite old release. Any reasons why new release is not available from Amazon when you say its available.

Thanks

Vivek






Tom Kyte
August 26, 2003 - 10:32 am UTC

i'll send this to the publisher, nothing I can do.

just fyi bookpool has the book and is shipping now!

A reader, August 28, 2003 - 9:16 pm UTC

mine is on the way!

amazon.fr

Fred, September 02, 2003 - 10:33 am UTC

Hi Tom,

Do you know when your book will be avalaible on amazon.fr ?

Thx by advance,

Fred

amazon.fr

Fred, September 02, 2003 - 10:34 am UTC

(i mean your NEW book)

Tom Kyte
September 02, 2003 - 11:29 am UTC

that i have no control over (nor info about). i don't do the distribution :)

Collector's item

sPh, September 02, 2003 - 1:31 pm UTC

>> i was wondering if there were any differences
>> between the reprinted Expert one on one Oracle
>> and the original Expert one on one Oracle book?
>> Does it include additional 9i features?

> Followup:
>
> nope, same exact book.

However, just as people try to collect every edition of _Harry Potter_ ever printed, I am sure that many will be rushing to round out their collection with the new printing. Another techno-sport: collect every edition of every Tom Kyte book ever published!

;-) Only half joking though - I may order the reprint just for that reason. I must be sick!

sPh

Which one should be read first?

Sam, September 06, 2003 - 12:22 pm UTC

In order to effectively learn advanced Oracle from your books, which one should I start first? (expert one-on-one, or oracle by design)



Tom Kyte
September 06, 2003 - 1:21 pm UTC

well, they are very very different books.

expert one on one is three books in one....


architecture/implementation/explanation -- how the server works, how locking works, how transactions work, etc. knowledge you need regardless of who you are.


developer topics -- external procedures, java stored procedures, analytic functions, etc.


dba topics -- partitioning, materialized views, etc.

but - even with the dba/developer stuff -- they apply to the other equally as well. A developer might want to use an external procedure, but the dba has to set them up. A dba might want to have partitioning implemented -- but the developers will have to participate in that design.....



Effective Oracle By Design isn't a reference/tutorial like that. It is more of "how to do it right" or "things you need to think about before doing something"...


It depends on whether you haven't the concepts guide yet!! if you haven't, THAT is where you need to start.

where is the pdf

Ringo Li, September 08, 2003 - 4:07 am UTC

Followup:
after you buy the book and get the book, you'll be able to use information from
the book to get access to selected chapters (ones that I refer to heavily for
reference) in pdf format.

Tom,

I got your book on hand but couldn't find the related info as mentioned above..... Am I missing something ?


Tom Kyte
September 08, 2003 - 11:16 am UTC

ok, long story short

expert one on one -- written with WROX press.
effective oracle by design -- started with WROX press (was actually like a month away from being done)

WROX press goes out of business. All prior plans go out the window with that.

Oracle Press is who I decided to work with on effective Oracle by design.

Apress bought Expert one on one.

So, I removed the references to expert one on one from effective oracle -- backfilled the necessary material instead of just pointing to it.

There cannot be any pdf's since there are two totally differently publishers

Reply to Vivek

Suvarna, September 08, 2003 - 6:50 am UTC

Hi,

Both 'Expert One on One Oracle' and 'Effective Oracle by Design' are available on Amazon.co.uk. I have recently received my copy of 'Expert One on One Oracle' reprinted by Apress!!!

6 months to this thread...

Tarry Singh, September 09, 2003 - 2:48 pm UTC

Tom,

Six months ago I started this thread and today after struggling with a lock escalation issue on a sql server I was pleasently surprised to find the "effective oracle.." on my computer desk(at home).

I will naturally come back with my questions but for now it's reading time ;-)

Keep up the great work.

Cheers,

Tarry

your new book - source code download?

A reader, September 13, 2003 - 3:16 pm UTC

Hi tom
Could not find source code download on the net for your
new book. I hope there is one:)



Tom Kyte
September 13, 2003 - 7:56 pm UTC

yeah, i guess that would be useful.

i ripped all of the code out of the book and put it here:

</code> http://asktom.oracle.com/~tkyte/EffectiveOracleByDesign/code.zip <code>


now, i haven't actually re-run the code. I'm sure there is an issue or two. My email address is in the readme, just drop me a line and I'll fix it up. But, it should save on some amount of typing for you....



thanx Tom!

A reader, September 14, 2003 - 11:38 am UTC

I would definitely let you know if there are any issues!

Please keep up the fantastic work!:)

And did I say - I love your new book too!! It is full of
amazing information - I especially liked the chapters
on optimizer, schema design and effective sql (I confess I have not understood each and every point - i will be doing multiple scans) The PL/sql chapter also looks very good -
haven't finished it yet.

Your book ....

reader, September 17, 2003 - 7:21 pm UTC

Tom, I have bought your book 'Effective Oracle by Design". I was wondering is there a site where I can download the scripts that you use in the book (that would save me typing time). For Expert one on one, I could download the scripts from wrox web site. Is there a similar web site for your new book. Thanks.

Read a couple responses up....

john, September 18, 2003 - 3:21 pm UTC

Tom gives the link there for the .zip file of scripts from the new book...

Yes, I have downloaded the scripts. THANKS.

reader, September 18, 2003 - 8:04 pm UTC


Terrabyte Databases in Oracle

Shouvik, September 19, 2003 - 8:25 am UTC

Tom,

In your book there are a couple of mentions about database sizes going to Terrabytes. I was just curious about the domains in which Oracle database sizes can go to Terrabyte.

Is it advisable to have single terrabyte warehouse repository of data or split it into several subdomains of smaller sizes (if possible of course) from performance and manageability standpoint.

Just expecting another chapter on Oracle VLDBs, in the next book. :-)
Thank you,
Shouvik


Tom Kyte
September 20, 2003 - 5:28 pm UTC

all domains.

we have many "oltp" systems in terabytes
we have many "dw" systems in terabytes
we have many "mixed workload" systems in terabytes

terabyte sized databases are not uncommon anymore -- they used to be, but they are now becoming fairly common place. everything from apps (HR/finapps/ERP/CRM) to custom apps -- they are all getting really big.



Where's the picture?

A reader, September 22, 2003 - 4:50 am UTC

Just a quick gripe, over here in the UK, our version of the new book doesn't have your picture on the front?

It's not fair, why do we always lose out over here? ;-)

Tom Kyte
September 22, 2003 - 7:52 am UTC

IMHO, that is a bonus!

For readers in India

A reader, September 23, 2003 - 5:29 am UTC

I enquired with McGraw Hill about the book and this is what they say -

The title Effective Oracle by Design by THOMAS KYTE will be reprinted in
India by us in the month of October, 2003 and will be priced at Rs.495.00
(Price subject to change without notice). Our titles are available at almost
all the retail outlets across the country. You may contact your nearest
dealer or you can purchase it from TMH Direct, our Direct Marketing
department.


Oracle Versions

Asim Naveed, September 23, 2003 - 7:00 am UTC

1-
What is the latest version of Oracle for which
the information in the book "Effective Oracle By design"
is valid.

2-
What is the latest version of Oracle for which
the information in the book "Expert One on One Oracle"
is valid.

If possible please specify the exact Product and its
exact version
for e.g Oracle 9i Database Release 2 9.2.0.2
Oracle 9i Application server rel 2 9.2.0.2


Thanks


Tom Kyte
September 23, 2003 - 7:21 am UTC

1) all
2) all

the books are mostly filled with "advice" and the advice applies to 7.0 as much as 10g. It is true there might be some features described that did not apply to 7.0 (eg: materialized views were added in 8.1.5 for example) but the material is 'valid' for all releases.

When Expert One on One Oracle was released -- the production release of Oracle was 8.1.7. Hence that book has no new 9iR1 and up "features" -- but the architecture, locking, concurrency control, transactions, etc etc etc -- all of that stuff is 100% valid for everything before and after 817.


When Effect Oracle Be Design was released -- the production release of Oracle was 9iR2. Hence that book has no new 10g and up "features" but everything in it -- is relevant for all releases of Oracle before and after 9iR2. Sure, I'll discussion features only available in 9iR2 (eg: gathering system stats for example)...



Thanks but what about different behaviours

Asim Naveed, September 23, 2003 - 1:45 pm UTC

Thanks for your detailed answer.

I am not only concerned about features, but behavior
changes as well. for example there might be some
rules to follow to make a specific query run
fast in earlier versions, but these rule are not
true in later versions and possibly may have reverse
effect.

Or do you specify the version no. also with your
advice in the books.

Actually i mean that can I read something from these
books and apply it equally to every version.
Actualy for some reasons we have several versions of
Oracle in production. What is the minimum version valid
for these books ? 7.0??



Tom Kyte
September 23, 2003 - 2:38 pm UTC

i'd like to think they are universal version wise for the simple reason that i repeat (and myself do) over and over again:

here is a list of things to consider
here is how to see what works best for you
here is how to test that
here is how to collect metrics for that

it is never "do it like this", it is "follow these steps", "understand how this works", "apply your knowledge in this fashion"

any release, really. there will be goodies in there you cannot use on really old stuff, but most of the content is version independent.

New book on sql!

A reader, September 24, 2003 - 11:18 am UTC

I would LOVE it if you wrote a book on sql itself
explaining all the concepts - neat tricks that you
use etc. I am not aware of any good sql books.
(Your chapter on sql and pl/sql are excellent in your
new book).
Do you have any such plans? May be you could write one
book on sql/plsql (assuming basic knowledge from the
reader on both topics.)

What do you think, Tom?

Tom Kyte
September 24, 2003 - 12:14 pm UTC

not currently on the drawing board -- was thinking about doing the supplied packages in detail. HOW to use them, WHEN to use them, rather then just listing the apis in them.

if you meant plsql supplied packages

A reader, September 24, 2003 - 12:19 pm UTC

there is already one by O'reily.
(though I am sure yours would be a class above.)

I feel that an sql book by you would be just wonderful
- don;t know what others feel about it.

Thanx for an awesome site, amazing sharing of first class technical knowledge and even more amazing generosity!

why not just expanding "expert one-on-one" ?

Alberto Dell'Era, September 24, 2003 - 12:45 pm UTC

If you are going to discuss the supplied pl/sql packages in details, why not just expanding the section on expert-on-one about them - and while you're there, expanding the whole book and bringing it to the 2nd edition ... i.e. aren't you planning any 2nd edition of "expert" ?

Tom Kyte
September 25, 2003 - 4:56 am UTC

it would be in the same vein as the appendix of expert one on one.

I do not "own" that book.

when WROX went under, it was sold to a company Apress.

I am working with Oracle Press -- hence, there will probably never be a 2cnd edition to Expert One on One. I'd have to rewrite it.

Sql + Plsql + expert One-on-One 2nd edition

Pieraldo Antonello, September 24, 2003 - 1:04 pm UTC

Yes, 3/4 more chapters written by Tom about sql & plsql

New Book

A reader, September 25, 2003 - 12:48 am UTC

Hi Tom,
Please tell us about the new book you're working on or are planning to work on.
Thanks

Tom Kyte
September 25, 2003 - 5:15 am UTC

just look up a couple of reviews -- short blurb on what I'm thinking about there.

dbms_aq

Alberto Dell'Era, September 25, 2003 - 7:58 am UTC

Are you planning to cover it - and so, AQ ?

Your coverage of DBMS_PIPE and DBMS_ALERT was so useful, that I sorely missed a chapter about DBMS_AQ in "Expert" ...

Tom Kyte
September 25, 2003 - 8:42 am UTC

i would in such a book, should I decide to actually commit to doing it :)

i would be hitting all of the dbms_ and utl_ packages.

Pictures

Dave, September 26, 2003 - 4:54 am UTC

I just got my copy in England from Amazon and Tom's picture is on the front AND the back - double the pleasure

Indian Edition

A reader, September 29, 2003 - 6:27 am UTC

Hi Tom,
Do you know when would an Indian Edition of your new book be available?
Thanks

Tom Kyte
September 29, 2003 - 8:08 am UTC

i hear that the end of october is the target date.

Sea Biscuit

A reader, September 29, 2003 - 9:31 am UTC

I notice on the back of the book, it says "Oracle Guru", and thats definitely true. I rate this book as high as any of those done by the other Oracle guru, Don Burleson.

A reader, September 29, 2003 - 9:39 am UTC

Please don't compare Tom to Don... Don Burleson's books stink !!! Many of the concepts discussed here are overlooked by him. He insists on cache hit ratios, rebuilding indexes, etc. Plain humbug !

RE: Sea Biscuit

Mark A. Williams, September 29, 2003 - 10:02 am UTC

LOL!

(It is an inside joke)

- Mark

A reader, September 29, 2003 - 10:47 am UTC

I totally agree, TOM is the best!!

Writing is an art

Tarry, September 29, 2003 - 1:40 pm UTC

The book is nice to read(I'm already done reading 3 chapters..must admit I'm a slow reader).Only they've pasted Tom's face all over the place. I mean ok he's got the cute looks but I guess the oracle press guys got carried away with it.;-)

Funny thing about Don's website was that I see him recommending lot's of authors but not a single book of Tom. ;-)

I bought Steven's book(pl/sql programming) as well but there's no author(besides someone like jonathan, harrison, henderson..to name a few)like Tom with excellent conversational style of writing. Writing a book is an art , not everyone can write. A lot of people can do wonderful things with a computer but are hopeless when it comes to conversing/documenting/explaining/sharing.






I agree with Tarry...

Franco, October 02, 2003 - 2:48 am UTC

I'm reading Tom´s books like I can read a spy story: the killer is hidden, and I constantly look for him; but at the end.... I always discover that the killer is a lack of knowledge in my mind, some missed Ora doc, some too complicated idea. Tom's books rating: 20 * !!!!

I need that

Praveen, October 07, 2003 - 9:06 am UTC

Tom,

I badly need your second book too. And I'm in Bangalore...:)


Thanks

Praveen

Your new book : Expert One-on-One Performance by Design

Rich, October 09, 2003 - 4:26 pm UTC

Just a note of appreciation for your site and your new book.

Thanks

New Book

A reader, October 17, 2003 - 6:05 pm UTC

Excellent! Chapter 7: Effective schema design is brilliant. Simple and helpful.

Thanks Tom!



Please verify

muhammad, October 18, 2003 - 10:02 am UTC

I got expert one-on-one book.it is published by apress. it has 1997 pages with 23 chapter. its isbn no is 1590592433.
is it the new edison or the reprint of the previous book.

Tom Kyte
October 19, 2003 - 6:12 pm UTC

WROX went out of business in march 2003.

apress bought expert one on one Oracle

they reprinted it in Aug 2003.

It is word for word for word the same book as WROX sold.

My new book is title "Effective Oracle By Design" published by Oracle Press in Aug 2003.

sorry for the unavoidable confusion -- all of which is so far outside my realm of control.

Indian edition pleaseeeeeeeee

Sameer, October 20, 2003 - 8:35 am UTC

Hi Tom,

My patience is almost dying out !! But I'm a DBA by profession, cannot show my emotions so easily :-)

Why Indian edition is getting sooooo delayed ?
(I don't really expect you to answer this..)

Just a thought... do you have any data of your last book which show's which part of the world bought your last book the most? It could be helpful to prove that India should be one of the first countries where Mr Kyte's book should be published first..!!
You got huge fan following here sir.

Tom Kyte
October 20, 2003 - 9:16 am UTC

it is not "delayed", it is that the indian reprints are done after the first print. I hear that it should be out this months.

It is not about "quantity shipped" really -- but rather quantity*price that drives it. The reprints in india and some other countries can cost sustantially less.

quantity*price

A reader, October 20, 2003 - 10:01 am UTC

Three copied can be bought in India for the price of one :-)
Buy two for yourself and present the third to a person who says Explicit cursors are faster than Implicit :-)

can I get ISBN no. of your books

Anurag, October 23, 2003 - 5:31 am UTC

Tom ,

Can I get an ISBN nos. and name of publishers of both of your books, As my bookseller is unable to find and has asked for same.

Thanks


Tom Kyte
October 23, 2003 - 12:47 pm UTC

see my home page -- click thru to amazon.com. they are there.

Now in India

A reader, October 28, 2003 - 10:19 pm UTC

Hi everyone,

Just got a mail from Tata McGraw Hill,
Effective Oracle by Design has finally launched in india. Hope to rush to my bookstore after office to get my copy.

Cheers

Books24x7

Kimathi, October 30, 2003 - 3:37 pm UTC

Any chance you might publish on Books24x7.
It serves as great reference material to have online versions.

Other authors with the same series i.e 'Expert One-on-One' seem to have done so.
e.g
Expert One-on-One J2EE Design and Development by Rod Johnson


Tom Kyte
October 30, 2003 - 10:00 pm UTC

never heard of it, i don't work on the distribution, just the "development of" material.

Isn't that the book that says "don't do stuff in the database". hmmm, not sure it should be 24x7

Just got your book...

Piotr Jarmuz, October 31, 2003 - 6:13 am UTC

Can't wait the evening to start the lecture :)

To Srinivas M:
The book is maybe expensive but think of it like an investment in yourself. Believe me, the knowledge you will acquire is worth thousendfold the price and eventually your salary will be improved too just like it happened to myself after Tom's first book. Even better it helped me get rid of "database-is-slow-trauma" after a year of work with some other commercial database :)

This means a lot to me and alone is definitely worth the price.


A reader, November 06, 2003 - 8:26 am UTC

Congratulations for your book, I'll see if this time get time to read the whole book.

PD
The next time rest a little before taking the photo for your books. ;)

Book on 10g

Arun, November 06, 2003 - 3:36 pm UTC

1. My friend brought your new book and he says it is summary/collection of info on your site (asktom.oracle.com). Mo need to buy if you have access to site. Your comments ?

2. Are you planning to publish book on 10g in near future ?


Tom Kyte
November 06, 2003 - 5:51 pm UTC



will, if you read the 9,000+ published things here, sure, everything is already here. some of the examples in the book come from here.

but, there is a big difference between a quick answer here and an in depth "lets look at this in a book" answer.

is there overlap -- sure, obviously (same with expert one on one - this site was the genisis for that book -- everything in that book is there as well).

books are just a different medium from a website. I'm able to be a little more coherent, organized in a book then here.


Yes, planning on a 10g book sure -- but "near future" is relative. I like to USE the produce before writing about it :)

Web Seminar

A reader, November 07, 2003 - 10:19 am UTC

Tried to access your Web Seminar from Nov. 5th, it said that it has not yet started.

By the way, what is OGEH?

Tom Kyte
November 07, 2003 - 10:46 am UTC

i'll ask them why....

OGEH is Oracle Government, Education and Healthcare, the vertical I work in.

Book is now available in India

A reader, November 08, 2003 - 3:21 am UTC

Not yet released in India

A reader, November 10, 2003 - 8:06 am UTC


Outer table...

Scott Watson, November 19, 2003 - 2:22 pm UTC

Tom,

I was going through your new book and either I need some clarification on how the hash outer join (p470) works or there may be some errata on this page.

Which table would be the outer table in this example? Normally I would have said EMP since it comes second in the explain plan, however, is this still the case when dealing with an outer join? The reason I ask is because on the previous page you wrote "The outer table (whose rows are being preserved) is used to build the hash table". However, in the section on how this is processed you have the DEPT table as being scanned and hashed and not the EMP table.

Did I miss something or is this an error.

Thanks,
Scott.

PS. Good job on the book.

Tom Kyte
November 21, 2003 - 3:19 pm UTC

in the example in the book -- DEPT is the table with the rows being preserved.

Normally, the EP is read bottom up, inside out -- but with hash joins, the order of the tables can be reversed at will.

in the example, the dept table was scanned and hashed.

Effective Oracle by Design

Justin, November 25, 2003 - 1:04 pm UTC

I can't ever seem to instantiate a new thread, so I'll
/*+ append */ my comment. (We need more Toms.. when will Alan and Megan be ready??)

I'm reading your new book, and I must say, I haven’t ever laughed this much reading a technical book!

It's practically hilarious in parts Tom.

Thanks .. I'm thoroughly enjoying it.



will you be putting a URL to your talk in sweden on asktom?

A reader, January 09, 2004 - 11:47 am UTC

Thanx and have a happy new year!

Tom Kyte
January 09, 2004 - 2:12 pm UTC

the url is already there on the home page. i'll be taking it down after its over of course, but it is there right "now"

what I meant was

A reader, January 09, 2004 - 2:20 pm UTC

would you be putting the url for contents of your
presentation that you will do in Sweden. Current url
points to a pdf detailing the event itself..


Tom Kyte
January 09, 2004 - 2:37 pm UTC

it'll be the "day3" stuff from the master class in denmark that i'm doing next week. I'll be posting those when I get back, yes.

A reader

alex, January 11, 2004 - 9:31 am UTC

Tom

Can I request you to tell me what tool can be used to performance test for Oracle Forms 6i & Reports 6i.
Is it discoverer, win runner or any good tool.

Thanks

Tom Kyte
January 11, 2004 - 9:33 am UTC

I know load runner is a workable solution for sure. (by mercury interactive)

Code Download

Iain, January 15, 2004 - 11:33 am UTC

Tom

Do you know if it's possible to download the code from your Expert One-on_One book from any other source now that Wrox have gone out of business.

A link on this site maybe

Tom Kyte
January 15, 2004 - 12:22 pm UTC

www.apress.com

they bought the book

your next new book ?

Alberto Dell'Era, January 21, 2004 - 11:45 am UTC

Still planning a book on dbms_* and utl_* ?


Tom Kyte
January 21, 2004 - 3:10 pm UTC

not sure.

Your new book : Expert one-on-one performance by design

Subodh Deshpnade, January 28, 2004 - 5:01 am UTC

Hi Tom,

just now i checked, the web site amezon, it stil shows that it covers upto 8.x. I ALSO ASKED THE SHOPKEEPER, HE SAID ME THIS IS THE ONLY LATEST VERSION.

HOW CAN I GET YR NEW BOOK...9.X RELEASE..

PLEASE REPLY...

Tom Kyte
January 28, 2004 - 8:47 am UTC

there is "effective oracle by design", that is the new book (as of august 2003).

the other book is "expert one on one Oracle"


the book "expert one on one performance by design" was never published -- it is called "effective oracle by design"

system stats

Ferenc Palyi, January 28, 2004 - 12:42 pm UTC

Just one little note about your book.

On Page 319, System Statistics Wrap-up you say:
"The important consideration here, however, is that you must gather these system statistics when the system is running a representative load."

In my oppinion, representative load is not enough, I will share my experiences with you.

The problem is that, Oracle collects the stats using the current execution plans. In my case, the database was full table scan happy. So during the collection there were a lot's off executions doing full scans, there were no index blocks in the buffer cache. The collected results indicated that full scans are quiet cheap. I loaded the stats, and everything remained the same, but I wasn't convinced, so I picked some queries and started to hint them, to use indexes. I experienced, that the queirs are running much quicker.

I created artificial stats and loaded them, then I started to collect system stats again. This time the stats said single block access is not so expensive, and the overall performance of the system greately increased.

Creating artificial stats is the same like playing with OPTIMIZER_INDEX_COST_ADJ. It's the same game, you discussed in the following topic: </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6601251003901, <code>

Well, system stats doesn't make optimizer_index_cost_adj obsolote, you still have to "push" the db to the right direction. If you have no clue what's going on, you will just generate "wrong or misleading set of statistics" even using representative workload.

A reader, January 28, 2004 - 2:32 pm UTC

Tom,

Can you please give some feedback regarding connor mcdonald's book "Mastering Oracle PL/SQL Practical Solutions"

Thanks.



Tom Kyte
January 28, 2004 - 2:57 pm UTC

I could, but Connor is a friend and my mother always said "don't say anything unless you have something nice to say"

ONLY kidding. Only kidding.

If you are doing PLSQL development and would like something beyond syntax diagrams and would like to hear about how to make it perform (bulking up, avoiding parses, making it scale), this book might well be for you.

It is not an entry level book.

It is not a "how to write pretty plsql that indents really cool" book.


Maybe if Connor sees this he can post the outline and a little bit of what its about.




A reader, January 28, 2004 - 3:20 pm UTC

Thanks Tom, that was a good one :-)

So Mr. Connor can we hear from you.



Book info

Connor, January 28, 2004 - 7:06 pm UTC

One thing I will say is that the book I've been involved with doesn't have pictures of the author on the front, side, back, top, bottom...man talk about ego....hee hee, just kidding Tom :-)

Seriously though, this from the back cover:

"Dear Reader,

If you need more from a PL/SQL book than just the correct keywords and some pretty syntax diagrams, then this is the book for you. If you’ve been searching in vain for real-world examples and genuine strategies for maximizing the benefits of PL/SQL within your organization, you’ll find them in Mastering Oracle PL/SQL: Practical Solutions, the inaugural title of the pioneering OakTable Press series. Mastering Oracle PL/SQL isn’t a tutorial on how to code PL/SQL. It’s designed to show you how to code PL/SQL *well*. It will show you how to write code that will run quickly and won’t break in high load, multiuser environments. It covers the vast array of functionality that PL/SQL provides, including effective handling of relational and abstract data, security, triggers, dynamic web content presentation from within the database, creation of a DBA toolkit, and effective debugging techniques.

This book sets out to redress the unjust criticism leveled at PL/SQL in years gone by. It gives you the tools and techniques to ensure that, whatever your needs are, the PL/SQL you build will run as efficiently as possible. I am confident the practical solutions provided in this book will help you to realize the true power and functionality PL/SQL can offer your projects."

The chapters are (if memory serves)
1: Efficient PL/SQL (performance, scalability, concurrency)
2. Why Packages
3. Cursors (explict vs implicit, etc)
4. Smart data handling (bulking, record based dml etc)
5. Odds and ends (hints & tips etc)
6. Triggers
7. Security
8. DBA tools in PL/SQL
9. Debugging and instrumentation techniques
10. The web packages (OWA, HTP et al)

I should stress, this is not a book for *learning* PL/SQL.

Cheers
Connor

Pictures

Mark A. Williams, January 28, 2004 - 7:30 pm UTC

Maybe the next book one will have Seabiscuit on it instead :)

Tom Kyte
January 29, 2004 - 7:51 am UTC

i did finally see that movie, it was a good movie

A reader, January 28, 2004 - 9:07 pm UTC

Thanks Connor.. Am placing an order. :-)

Re: Your new book : Expert one-on-one performance by design"

diprey, January 29, 2004 - 12:07 am UTC

Just finished reading this book: took me almost 5 months. It took me some time to understand the details.

My opinion is it's a fine book, a subtle one, regardless of whether you are an Oracle developer or not. It's only second to ORA 1-on-1, by the same author. I, myself, work with various databases, Oracle being one of them, and I do this stuff for a living.

There are quite a few RDBMS books on the market, but this one stands out. For all you SQL Server aficionados out there: buy this book, take your time to understand it. It is inspirational, it bears thought, it makes your creative juices flow.

Thank you Tom for your tireless work and for your insight. You are the best.

Link for 'Efficient Schema Design' presentation

A reader, January 29, 2004 - 8:31 am UTC

Hi Tom,

Will you please add link for 'Efficient Schema Design' presentation again on asktom homepage?


Cheers!!!

Tom Kyte
January 29, 2004 - 8:38 am UTC

</code> http://asktom.oracle.com/~tkyte/ <code>

I moved it to there.

I want to know which is your book on 9.x and 9ids

Subodh Deshpande, January 31, 2004 - 1:43 am UTC

Thanx TOM,

Whcih one is about 9i,

Expert expert one on one Oracle

Or effective oracle by design

and lastly are you going to write anything on 9ids..pl do, i fall in love just by reading sample lessons from 'one on one'

Tom Kyte
January 31, 2004 - 10:02 am UTC

Effective Oracle by Design is upto 9ir2

Expert One on One Oracle goes upto 8iR3 (but is pretty much 99% applicable in whole to 9i, 10g and beyond)

IDS is covering forms, reports, disco, jdev -- things I'm just not "expert in". I do not use those tools personally. I'd be the wrong person to write about them.

your latest book

A reader, February 03, 2004 - 12:05 pm UTC

</code> http://www.apress.com/book/bookDisplay.html?bID=314 <code>

when is this goin to be available on amazon?

Tom Kyte
February 03, 2004 - 1:51 pm UTC

after its been written :)

your latest book / Oracle Insights

Alberto Dell'Era, February 03, 2004 - 5:41 pm UTC

Already planned an outline of your contribution ?

"My fight - Save the Binds" seems mandatory ;-)

ego boost for you

Dave, February 17, 2004 - 6:13 pm UTC

Tom, take a look at this thread - bit of an ego boost for you :)

</code> http://www.dbasupport.com/forums/showthread.php?s=2be256cc5dbc6618b8b1305e5c091022&threadid=41402 <code>

Expert one-on-one performance by design", version 9.2.0

Leonard Anukam, February 23, 2004 - 1:57 pm UTC

Hey Tom, it could be a good idea to write a book from the questions and answer from this site into a book. You could call

Problems and Solutions. How about that or A CD-Rom

your book expert one on one..

A reader, March 15, 2004 - 12:03 pm UTC

hi tom good afternoon,

I was reading your book page 610 (old ed. from wrox).
you have commit inside the for loop .

can you tell me what is the reason/ benifit/ no effect ?
of that ? I thought I shoul always write commit out side the loop ?

Thanks,




Tom Kyte
March 15, 2004 - 12:56 pm UTC

on that page -- i am

insert /*+ append */ into SALES
select ..... from SALES;
commit;


in a loop just to generate data. If I did not commit (due to the direct path write in this case, the append hint) the second "insert as select" would fail since you cannot read/write to a table in the same transaction after doing a direct path operation on it.

very interesting....

A reader, March 15, 2004 - 1:13 pm UTC

Thanks for your replay. I will test further. This is good.

so I can not issue

insert /*+ Append */into t(id,name)
select id_seq.nextval,to_char(sysdate) from all_objects
where rownum < 2;

insert /*+ Append */into t(id,name)
select id_seq.nextval,to_char(sysdate) from all_objects
where rownum < 2;

ERROR at line 2:
ORA-12838: cannot read/modify an object after modifying it in parallel

cool I tested it my self too. great. I didn't know that.

Tom Kyte
March 15, 2004 - 2:42 pm UTC

(and it is a good thing you cannot! it would waste huge amounts of space as only a single row would get onto a block!!!)

Effective Oracle by Design.....

denni50, April 06, 2004 - 3:52 pm UTC

Hi Tom....
Hope you (and family) are enjoying a much deserved splendid
vacation.

Finally got your new book...one of the online bookstores
was having a 45% discount on techy books and yours was one
of them....couldn't pass on that deal!(bought some others
too)

Started reading through chapter 1 and the more I read
the more frustrated I found myself becoming.

Showed my boss your two examples of using analytics vs
generic sql(page 13)..and then the performance analysis
between the two. Needless to say..she was astounded.

I told her that I cannot continue to work in an environment
that is stagnant and does not afford me the tools to advance
the goals of the organization and performance of the database.
I am seriously considering asking our network administrator to build me a high end PC with a SCSI hard drive(found a good deal on a 73gig) and tons of CPU. Then install OS, 9iR2 and import the database.

I will be "FREE" from the confines and limitations of the
software vendor and can start using 9i features especially
"analytics".

At the moment(with 817 SE)I have no:
OLAP
MV
Partitioning
Analytics....ad-nauseum.

Anyhow...just thought I'd let you know what's up!

any comments,recommendations would be most welcomed and
appreciated.

happy spring!
;~)


question about example in your book

reader, April 14, 2004 - 3:35 pm UTC

On pages 106 through 108 of your book Effective Oracle
by Design there are several examples comparing the performance difference using RBO vs CBO, full table scans vs indexes.

The comparison between rbo and cbo is apparent.
The statistics on page 107 showing the select query
with CBO and full table scans of I1 is cost=10 with
92 consistent gets.

Then you created the index:
create index i1_idx on i1(v) and re-ran the statistics
showing Table Access (BY Index Rowid)of i1_idx with a cost=2,card=1 and 2 consistent gets.

My interpretation would be the index did perform better
than the full table scan in this case.

you then go on to state:
"This just helps prove that indexes are not always best and full scans are not always to be avoided....."

am I missing something?....(I am performing my own tests)
thanks

Tom Kyte
April 14, 2004 - 3:53 pm UTC

bottom of page 107 is table access (full) of 'map'

the exact thing they were trying to avoid in the plan in the first place.


reader

reader, April 14, 2004 - 4:19 pm UTC

well the issue of the Full Table scan on Map seems
inconsequential as the two cbo examples show MAP
with a cost=9, in the RBO you can't really tell
and the improvement on performance seems to have
nothing to do with the Map table but more to do with
tables I1 and I2 having indexes on them using cbo.

I guess I'm missing the point with the examples.

Tom Kyte
April 14, 2004 - 4:26 pm UTC

that full scans are not something you want to get rid of -- and that the rbo is index happy and will avoid full scans at all costs.

Is this publisher out of business too?

Venkat, April 30, 2004 - 5:42 pm UTC

Tom,
TCOUG presentations were great. Thanks.

</code> http://www.oraclepressbooks.com <code>is not available and so is www.osborne.com.

I was wondering if there is a list of errors (if any) attached to this book. But can't seem to find the web page of the publishers.

Regards,
Venkat

Tom Kyte
May 01, 2004 - 9:02 am UTC

oraclepressbooks.com is certainly open for business. they haven't gone anywhere (major reason I'm working with them -- i think they have a bit of staying power)

</code> http://shop.osborne.com/cgi-bin/oraclepress/0072230657.html <code>

has a link to errata.

in your book

A reader, May 04, 2004 - 12:28 pm UTC

hi tom,

In your book tom,why have u not included the how to explain
oracle stuff to java people ?

i am an oracle database developer and the pl/sql code develop is getting reviewed by a JAVA developer. It very very creazy to make them understand why I did a perticular step if it is not in book or javaism or little advanced. But they are my bosses. I cann't tell them no I have to explain them and I get crazy counter questions which sometimes impossible to answer. sometimes it is general practice, programming style, my preference or complex michenism.

What is a practical way to make them understand without getting me ....

Tom Kyte
May 04, 2004 - 2:04 pm UTC

(your keyboard is in danger of complete failure! already vowels are missing)


I thought I did that.

At the end of the day, java is just a language -- just a language. It is not a religion, a way of life. It is just a language. People did the same thing with C++, smalltalk, whatever.

funny thing is, SQL and Cobol still exist and run much of the world :)

Effective Oracle: Legacy Storage Clauses

Tak Tang, May 10, 2004 - 6:57 am UTC

Hi Tom,

At the bottom of page 225 (chapter 4) of your new book, you have a storage clause with initial 1m next 5m minextents 3, which would allocate at least 11MB. The text then goes on to say that in an LMT with 10M extent sizes, you would get 20MB initially. Does that means it has ignored the minextents, or should that have read 30MB (ie 3 extents at 10MB each)?

Great books BTW. I've a tip for people who think they're expensive - get twice the value by reading them twice! No really!

Tak


Tom Kyte
May 10, 2004 - 8:33 am UTC

ops$tkyte@ORA9IR2> create tablespace ten_meg
  2  datafile size 100m extent management local uniform size 10m;
 
Tablespace created.
 
 
  1* create table ten_meg_test ( x int ) storage ( initial 1m next 5m minextents 3 ) tablespace ten_meg
ops$tkyte@ORA9IR2> /
 
Table created.
 

ops$tkyte@ORA9IR2> select sum(bytes)/1024/1024, count(*)  from user_extents where segment_name = 'TEN_MEG_TEST';
 
SUM(BYTES)/1024/1024   COUNT(*)
-------------------- ----------
                  20          2
 
 

Assumption #1

Tak Tang, May 11, 2004 - 8:29 am UTC

Doh! Its so obvious I could have tried it out for myself. I assumed that it would create 3 extents and that the number 20MB was wrong. The book says

<QUOTE PAGE=225 BOOK="Effective Oracle by Design">
Oracle would allocate at least 1MB + 5MB + 5MB = 11MB of space in the LMT - 1MB for the initial extent, and then two more 5MB extents to satisfy the MINEXTENTS clause.
</QUOTE>

I tried this with a SYSTEM_MANAGED LMT :-

12:48:34 TANGT@DEVC> create tablespace system_managed
12:51:01   2  datafile size 100m extent management local
12:51:01   3  /

Tablespace created.

12:51:10 TANGT@DEVC> create table eleven_megs ( x int )
12:51:14   2  storage ( initial 1m next 5m minextents 3 )
12:51:14   3  tablespace system_managed
12:51:14   4  /

Table created.

12:51:15 TANGT@DEVC> select sum(bytes)/1024/1024, count(*)
12:51:18   2  from user_extents 
12:51:18   3  where segment_name = 'ELEVEN_MEGS'
12:51:18   4  /

SUM(BYTES)/1024/1024   COUNT(*)
-------------------- ----------
                  11         11



This shows that it allocated 11 extents (probably 1MB each), so the book wasn't referring to SYSTEM_MANAGED LMTs.

I was about to ask how you got it to allocate exactly 3 extents, but then it occured to me that I *like* what it does, and I don't *want* it to create 3 extents.

Thanks for taking the time to answer my original question, dumb though it was.

Tak


A reader, May 24, 2004 - 11:24 am UTC

Tom, any info or news on your third book?

Thanks.

Database as black box

Sikandar Hayat, June 20, 2004 - 2:45 pm UTC

Hi,
I was reading your 2nd book and it is totally about what I was facing. We are out sourcing an ERP system developed in J2EE while on backend it is Oracle9i. After getting an initial overview of the developed application I asked few questions from them. The questions and the answers are as below,

Q-1. Why you have not used Stored Procedures?
A. We don't want to keep database independent.

Q-2. Its mean you will not use Oracle features?
A. We will try to use only those features which very important to use.

Q-3. What are those important features?
A. No answer (means only DML)

Q-4. Its mean you are using database as a text file as you are not using its features then why are we using Oracle database?
A. We want to keep independent as our policy so later we can plug the application to MS SQL. The use of Oracle will benefit only like tablspace management, extent management etc. Means auto features of Oracle....

Q-5. This technique is not acceptable for us as we have to pay for Oracle without using its featues?
A. We are following Oracle Applications technique. Means Oracle Applications are using same procedure. That Integrity Constraints are at front end.

I affraid that in this way we will face data integrity and other issues. May be a complete disaster........

The only comments I need justification is the technique used in Oracle Applications?



Tom Kyte
June 20, 2004 - 4:18 pm UTC

q5 -- it blantly "misinterpreted". when practical, constraints are in the datbase. But Oracle Apps make use of stored procedures, Analytic functions, AQ, Materialized views, sequences, unique indexes -- pretty much every single Oracle feature, pretty much every single feature.



Here is the data

Sikandar Hayat, June 21, 2004 - 2:01 am UTC


Hi,
Yes you are right Oracle Applications are using Oracle features because its front end is in Forms6i and is only for Oracle at backend. I want to know only about constraints at backend in Oracle Applications.

Here is the data I have taken from Oracle Financials 11.5.7 dba_objects view,

OWNER C COUNT(*)
------------------------------ - ---------
AP C 931
APPS C 403
AR C 2463
GL C 1506
HR C 4295
APPS O 3342
AR O 3
APPS P 61
AR P 19
HR P 622
APPS R 4
HR R 807
AP U 1
APPS U 6
HR U 228
APPS V 147

Here you can see the usage of PK and FK. To give an example of a table gl_code_combinations in which we are using first 7 segments,

Name Null? Type
------------------------------- -------- ----
CODE_COMBINATION_ID NOT NULL NUMBER(15)
LAST_UPDATE_DATE NOT NULL DATE
LAST_UPDATED_BY NOT NULL NUMBER
CHART_OF_ACCOUNTS_ID NOT NULL NUMBER(15)
DETAIL_POSTING_ALLOWED_FLAG NOT NULL VARCHAR2(1)
DETAIL_BUDGETING_ALLOWED_FLAG NOT NULL VARCHAR2(1)
ACCOUNT_TYPE NOT NULL VARCHAR2(1)
ENABLED_FLAG NOT NULL VARCHAR2(1)
SUMMARY_FLAG NOT NULL VARCHAR2(1)
SEGMENT1 VARCHAR2(25)
SEGMENT2 VARCHAR2(25)
SEGMENT3 VARCHAR2(25)
SEGMENT4 VARCHAR2(25)
SEGMENT5 VARCHAR2(25)
SEGMENT6 VARCHAR2(25)
SEGMENT7 VARCHAR2(25)

Note: I have pasted only few fields of the table.

The unique index is on CODE_COMBINATION_ID which is just a auto sequence. But there is nothing on SEGMENTS where duplication is possible. If someone will change the segments values and after change if segments are duplicate (composit) the it will be allowed as we have not used front for this modification.

I would really appreciate your comments.


Tom Kyte
June 21, 2004 - 8:18 am UTC

but the rules governing the "uniqueness" there are setup during setup -- not during table creation time.

Now we are looking at the difference between "generic implementation, so customers can customize" vs "do not use anything in the database"

Apps -- to the degree possible -- lets the data do its job. If they don't have to write code for something, they do not. If, due to their software requirements, they cannot -- they do it themselves.

It sounds like you are paying someone to write you an ERP solution.
It seems like this ERP solution should be developed for you, given your rules.

It sounds like this other company is trying to use you to pay for their development costs for an application they will sell and resell to many others -- using you as the development "bank" if you will. Maybe, if they are just coding this, you change the contract such that if they are using you to fund development -- you get a royalty for each and every unit shipped afterwards.

Your Books

Senthil, June 28, 2004 - 1:08 pm UTC

Hi Tom,
I'm about to buy your books (Expert One-on-One Oracle and Effective Oracle by Design).Just to make sure, Are they latest release?.Is your current books covers any 10g features?.Do I need to wait sometime to get new release ?.

Thanks,
Senthil


Tom Kyte
June 28, 2004 - 1:13 pm UTC

Expert One on One Oracle was written in 2001 and covers up to 817.
Effective Oracle by Design was written in 2003 and overs up to 9iR2.



Any future release soon ?.

Senthil, June 28, 2004 - 2:07 pm UTC

Thanks Tom. I'm new to PLSQL and hoping that your books will help me to learn and become the Oracle expert on SQL/PLSQL.Would you like to recommend any other books addition to your books?.

By the way, any plan to release the new edition of your books soon particularly Expert One on One Oracle?.

Thanks, Senthil


Tom Kyte
June 28, 2004 - 2:56 pm UTC

</code> http://www.apress.com/book/bookDisplay.html?bID=276 <code>



there are certain legal issues that need to be worked out before I can write again ;(

A reader, June 28, 2004 - 2:34 pm UTC

Tom,

Any plans/dates on seminars in NYC?

Thanks.

Tom Kyte
June 28, 2004 - 3:08 pm UTC

not in the near future... I did the NYC OUG not too long ago, sept 2003..

A reader, June 28, 2004 - 3:50 pm UTC

Tom,

Any seminars for NYC ppl?


Thanks.

PLSQL Links

Senthil, June 29, 2004 - 4:26 pm UTC

Tom,
I just placed an order to buy your books.Really excited to read them.Meanwhile,can you provide some otn or other links to start learn with plsql stuff,pls.

Thanks,Senthil.

Tom Kyte
June 29, 2004 - 6:45 pm UTC

documention link off of otn.oracle.com's home page (lower right hand side) would be a good start place. the plsql programmers guide is there, the application developers guide as well.

spider

A reader, June 29, 2004 - 7:42 pm UTC

Hi Tom, I didn't know where to ask this.
Tom can I do a spider using teleport from asktom.oracle.com (1 page per time, not 10 process at the same time)
or is not allowed.
Thanks

Tom Kyte
June 29, 2004 - 8:21 pm UTC

not allowed.


A reader, June 30, 2004 - 11:37 am UTC

THANKS

Your old book still relevant

Gabriel,, July 08, 2004 - 9:17 am UTC

Hello Tom,

I recently bought your new book and it is amazing! I've been borrowing for years now your previous book 'Expert one-on-one' from a co-worker but I got tired now and I want to buy it. Is it still relvant for 9i and 10g given that it was written at the time of 8i?
Do you plan a second edition, updated for the new releases?
Do you plan another book all together?
Can I be you agent ?(just kidding)

;-)


Thank you,



Tom Kyte
July 08, 2004 - 9:40 am UTC

yes, I still pull up expert one on one myself from time to time...

I would term it "still very relevant", some of the architecture stuff is dated (job queues are done very differently now at the server level) but the analytics, mv's, partitioning, etc etc -- is all relevant.


some legal issues to clear up before I can do a 2cnd edition with the publisher of my choice.... but I'm looking into it.

Your old book still relevant

Gabriel,, July 08, 2004 - 9:17 am UTC

Hello Tom,

I recently bought your new book and it is amazing! I've been borrowing for years now your previous book 'Expert one-on-one' from a co-worker but I got tired now and I want to buy it. Is it still relvant for 9i and 10g given that it was written at the time of 8i?
Do you plan a second edition, updated for the new releases?
Do you plan another book all together?
Can I be you agent ?(just kidding)

;-)


Thank you,



t:o: Gabriel

Menon, July 08, 2004 - 11:02 am UTC

Tom's book (expert one on one) is going
to be always relevant since it contains Oracle
fundamentals.. It is the best, most comprehensive,
and useful book on Oracle I have come across - period.
Very strongly recommended!



dbms_trace in your two books.

Sean, July 13, 2004 - 5:49 pm UTC

Hi Tom,

Both of your book didn’t mention dbms_trace package though you mentioned it on this site here and there. Is this function covered by other tools such as statspack or dbms_profiler?

If this tool is still useful, would you provide some examples. It is not easy to understand just by reading Oracle manual.

Thanks so much for your help.

Sean


Tom Kyte
July 13, 2004 - 8:09 pm UTC

well, i have examples on this site that use them? so the examples are already there? if you find an example "lacking", followup on that page with details about what is missing/not clear.

on page 287 of your book

A reader, July 17, 2004 - 4:31 pm UTC

you give an example where you use a ref cursor
in a loop and show how we can save time when we
use session-cached cursors for this code. But I thought
since ref cursors are "pointers" they can not be cached?

What am I missing here?
Thank you!

Tom Kyte
July 18, 2004 - 11:39 am UTC

plsql isn't caching them -- but a cursor is a cursor is a cursor, so session cached cursors work just fine for them.


thanx!

A reader, July 18, 2004 - 1:05 pm UTC

I gathered that from subsequent examples in your book...
However still have some doubts...

It seems PL/SQL cache is then separate from session cache?

1. session cache can cache all cursors
(The question still remains - why can a session
cache cache a ref cursor if a PL/sql cache can not?)

2. pl/sql cache can cache only static cursor and I
understand why...
3. Where exactly do these two caches exist in Oracle
memory?

Thanx!

Tom Kyte
July 18, 2004 - 2:46 pm UTC

the plsql cache is definitely different -- and much more effective -- than the session cache, definitely.


1) the session is a "soft" cache -- the plsql cache really truly 100% caches the cursor -- to the point where subsequent soft parses don't need to take place at all. it actually keeps the cursor open and ready to go. the session cursor cache does not go so far.

the session cursor cache is a softer soft parse -- it is still a parse however, and incurrs latching and looking up.

3) your uga.

OK...

A reader, July 18, 2004 - 1:13 pm UTC

thought some more about it...Following may be the answer
to my questions..

session cache caches the "pointers" that ref cursors are
- when you actually access them you get the "pointer"
and verify that it points to a valid entry in
shared pool...then you can proceed...At this level
you dont care about executing a particular cursor - you
just care about caching them and executing them to
"soften" your soft parse...


PL/SQL language made a conscious decision of not
caching ref cursors since the idea of caching here
was to do no parsing at all (not to do a softer
soft parse - which can be done anyways by session cache)?

Is the above argument sound?


Tom Kyte
July 18, 2004 - 2:47 pm UTC

the problem with ref cursors is each time you open it -- it could well be (and generally is) a different cursor all together.

plsql cursor cache effect

A reader, July 18, 2004 - 2:56 pm UTC

some more thoughts..

PL/SQL cache, if it is separate should work as follows
(lemme know if I am correct or not):

1. First the check is made to see if the same cursor
(assuming it is static) is availble in pl/sql cache -
if so we directly execute the statement in the cursor
2. If the cursor does not exist in pl/sql cache, we check
against session cache - if it exists there then
we again check whether the entry points to a valid
entry in session or not - if it is we execute..
3. If entry does not exist in session cache as well,
we go through hard/soft parse as requied...

Is the above logic correct?

Also another question...
Suppose from a client (Say a Pro*C, or a JDBC program)
we are invoking code to insert data into
a table. I know you are a fan of putting sql in
PL/SQL and not in Java (and I agree with this)
but just for understanding:

Case 1: We put the insert statements (more than one)
in java and use Prepared statement to do the insert
( assume that we dont use batching feature.)
Case 2: We put the statements in a pl/sql procedure and
use CallableStatement to do the insert..
Thus each insert statement causes a round trip
in both cases...

Given this scenario, does pl/sql cache improve the
efficiency of the statements (in which case, case 2
would be faster)?

Thanx!


Tom Kyte
July 18, 2004 - 3:45 pm UTC

conceptually -- yes, that is correct.


for the second thing -- both should parse the insert once and only once. so from that perspective, they will be no different.

what would make a large effect would be bulk processing of course, don't do row by row, process many at a time.

thanx!

A reader, July 18, 2004 - 4:10 pm UTC

"for the second thing -- both should parse the insert once and only once. so
from that perspective, they will be no different."

I understand that - but will the pl/sql cache
keep the cursor open in the second case - thus
not requiring one to open the cursor again even
though a "close" has been executed on the client side?
This (if it happens) will reduce the number of soft parses
in the second case as well.. ( I am writing a program
to verify this as we speak..)




Tom Kyte
July 18, 2004 - 5:14 pm UTC

if you use java and

a) prepare a statement
b) bind
c) execute it
d) goto B over and over
e) close it

it will be the same as using plsql to do that statement, they'll both have parsed it ONCE.

ok!

A reader, July 18, 2004 - 5:09 pm UTC

So I verified that PL/SQL cursor caching has no impact
in the case above... Not exactly sure why?

So now when does pl/sql cursor caching give benefit
to a client application (say Pro c/JDBC)?
I guess one reason would be - when you use only PL/SQL
from your client (e.g.. using CallableStatement) you
consolidate code to have less # of "Statements" in your
Java/C layer...That way there is a benefit....



Tom Kyte
July 18, 2004 - 5:25 pm UTC




The benefit is in the fact that you need not think about it at all, it just automagically happens.


create procedure p( p1, p2, p3, .... p8, pk )
as
begin
insert into t (c1,c2,c3,c4,c5) values ( p1, p2, p3, p4, p5 );
update t2 set c6 = p6, c7 = p7, c8 = p8 where pk = p_pk;
delete from t3 where pk = p_pk;
end;


Now, show us the code in java that would do the same three statements with bind variables and ensure that the statements were parsed ONCE per session.

It will be more than three lines of code, of that I am 100% sure.

The benefit is in the transparency, in the ease of use, in the ease of coding.

oops!

A reader, July 18, 2004 - 5:19 pm UTC

Sorry - I was closing outside the loop...
Once I had the logic where I did the following (pseudo):

for i in 1 to 1000 (use prepared statement
bind
execute
end;

for i in 1 to 1000 (use callable stmt )
bind
execute
end;

Then pl/sql caching does come into picture..
So the inserts in the preared statement are soft parsed
1000 times where as the insert in the callable statement
are parsed only once...
However the effect is negated since the anonymous
block used in the callable statement needs to be
parsed 1000 times!:)

So more or less they are equivalent...
I dont understand why anonymous blocks can also not
be "cached" in the pl/sql cursor cache - I know anonymous
blocks are not "cursors" but conceptually why can not
they also be "cached" with some hashing done based
on the text of the anonymous blocks (with bind variables)?




Tom Kyte
July 18, 2004 - 5:39 pm UTC

closing outside of the loop is the only place to close.  the blocks should only be parsed ONCE (and then statement caching does kick in!  as long as you do not lose that handle)....

consider:


ops$tkyte@ORA9IR2> create or replace package demo_pkg
  2  as
  3
  4      procedure p( x       in number,
  5                   tag     in varchar2,
  6                   closeit in boolean default false );
  7
  8  end;
  9  /
 
Package created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> drop table t;
 
Table dropped.
 
ops$tkyte@ORA9IR2> create table t ( x int );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace package body demo_pkg
  2  as
  3
  4  g_cursor integer;
  5
  6  procedure p( x       in number,
  7               tag     in varchar2,
  8               closeit in boolean default false )
  9  is
 10  begin
 11      if ( g_cursor is null )
 12      then
 13          g_cursor := dbms_sql.open_cursor;
 14          dbms_sql.parse( g_cursor,
 15                         'begin insert into t ' || tag || ' values ( :x ); end;',
 16                         dbms_sql.native );
 17      end if;
 18
 19      dbms_sql.bind_variable( g_cursor, 'x', x );
 20      dbms_output.put_line( 'rows inserted = ' || dbms_sql.execute( g_cursor ) );
 21
 22      if ( closeit )
 23      then
 24          dbms_sql.close_cursor( g_cursor );
 25          g_cursor := null;
 26      end if;
 27  end;
 28
 29  end;
 30  /
 
Package body created.
 
ops$tkyte@ORA9IR2> @trace
ops$tkyte@ORA9IR2> alter session set events '10046 trace name context forever, level 12';
 
Session altered.
 
ops$tkyte@ORA9IR2> exec demo_pkg.p( 1, 'keep' );
rows inserted = 1
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec demo_pkg.p( 2, 'keep' );
rows inserted = 1
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec demo_pkg.p( 3, 'keep', true );
rows inserted = 1
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec demo_pkg.p( 1, 'close', true );
rows inserted = 1
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec demo_pkg.p( 2, 'close', true );
rows inserted = 1
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec demo_pkg.p( 3, 'close', true );
rows inserted = 1
 
PL/SQL procedure successfully completed.
 


the first execute of p opened the cursor, the next two reused that cursor.  the following three did the old "open, bind, execute, close" trick.  The tkprof shows that:

INSERT into t keep
values
 ( :b1 )
                                                                                                      
                                                                                                      
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          0          1          9           3
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          1          9           3
                                                                                                      
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 165     (recursive depth: 2)
********************************************************************************
INSERT into t close
values
 ( :b1 )
                                                                                                      
                                                                                                      
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          0          3          3           3
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.00       0.00          0          3          3           3



We we smartly keep the block "open", it caches cursors for that block.   Lose the handle -- lose the cache.  (you cannot take a simple "compare strings" approach here -- too many things could make the same exact block (string) parsed in the same exact session -- be totally 100% different from eachother (think current schema for one)....


The advantage of the plsql cursor cache is that the only thing the programmer needs to "cache" themselves is the original anonymous block they use to call this stored procedure -- not the 50 statements it might have inside of it. 

thanx!

A reader, July 18, 2004 - 5:32 pm UTC

sorry we keep running on each other...
"Now, show us the code in java that would do the same three statements with bind
variables and ensure that the statements were parsed ONCE per session.

It will be more than three lines of code, of that I am 100% sure.

The benefit is in the transparency, in the ease of use, in the ease of coding."

Regarding transparency, JDBC does provide a mechanism to do the above transparently (reasonably that is you need
to do simple set up at connection/statement level)

(similar to the pl/sql it does a "soft close" when
you close a cursor...) This is called statement caching...

I do agree that PL/SQL being much more "SQL friendly" is
much easier to code and use:) And yeah, the number of
lines would be many more also!:)


Tom Kyte
July 18, 2004 - 5:41 pm UTC

yeah, i keep forgetting that new jdbc 3.0 i think it is (correct if wrong) ability that should be turned on out of the box (like autocommit SHOULD be turned OFF out of the box) to do session statement caching.

thanx...

A reader, July 18, 2004 - 6:22 pm UTC

"closing outside of the loop is the only place to close. "

I am aware of that - I was just trying to simulate
the case where we can identify the pl/sql automagically
helping a not-so-well behaved application...


pl/sql cursor cache doc

A reader, July 18, 2004 - 6:41 pm UTC

says that
session_cached_cursor:

This parameter also constrains the size of the PL/SQL cursor cache which PL/SQL uses to avoid having to reparse as statements are re-executed by a user.


I thought pl/sql cache size was constrained
by the open_cursors parameter?

thanx!

Tom Kyte
July 18, 2004 - 7:05 pm UTC

that is new (i believe not so correct) behaviour since 9205.

I'm not a fan of this change as the change was to correct a problem that could not have been caused by plsql cursor caching in the first place!

does that mean...

Kamal Kishore, July 18, 2004 - 11:01 pm UTC

Hi Tom,
Does that mean that when upgrading to 9205, we must bump up the value of session cache cursor to get the benefits of PL/SQL cursor caching or does the current value would be still reasonable?
Thanks,


thanx!

A reader, July 19, 2004 - 1:18 pm UTC

"yeah, i keep forgetting that new jdbc 3.0 i think it is (correct if wrong)
ability that should be turned on out of the box (like autocommit SHOULD be
turned OFF out of the box) to do session statement caching."

yup - it is jdbc 3.0 that introduced statement caching..
...
regarding turning it on out of box - i see your point
but it may be not as clear cut case as autocommit...
in some cases it can lead to lot of memory
consumption on the client side..

Anyways I get your point...
Repeating my question above for you to look at if you
get some time...

"I know anonymous
blocks are not "cursors" but conceptually why can not
they also be "cached" with some hashing done based
on the text of the anonymous blocks (with bind variables)?"

Thanx!




Tom Kyte
July 19, 2004 - 2:00 pm UTC

i answered that last part already? gave a large demo of it actually? see above.

ok...

A reader, July 19, 2004 - 2:13 pm UTC

"We we smartly keep the block "open", it caches cursors for that block. Lose
the handle -- lose the cache. (you cannot take a simple "compare strings"
approach here -- too many things could make the same exact block (string) parsed
in the same exact session -- be totally 100% different from eachother (think
current schema for one)...."

well, the same holds true for procedures, sql statements
etc etc...ultimately should not oracle be able to
compile an anonymous block and "cache" its compiled version? If so it becomes the same as any other compiled
object - and hence eligible to be cached in pl/sql cache thus avoiding soft parses altogether (in subsequent
runs of a block.)



Tom Kyte
July 19, 2004 - 2:43 pm UTC

the same does not hold true for procedures? if you recompile it, sure -- the cache goes poof -- but a procedure is a procedure is a procedure, no ambiguity there.


begin p; end;

ambigous.

scott.p -- not ambigous. scott.p is scott.p. The p in that anonymous block above might be scott.p, might be george.p, might be *whatever*.



hmmm..

A reader, July 19, 2004 - 2:55 pm UTC

"begin p; end;

ambigous.

scott.p -- not ambigous. scott.p is scott.p. The p in that anonymous block
above might be scott.p, might be george.p, might be *whatever*.
"

well..
begin p; end;

is ambiguous only till such time that you have not established which user and what priviliges this
anon block is executing with (just like
any other compiled object.) Once you have that -
should not all the objects in the anonymous block
resolve to the same entity?

In my mind anon block is exactly like procedure
but just does not have a name...
sorry - i dont mean to argue unnecessarily but
am perhaps missing some point here...

thanx!

Tom Kyte
July 19, 2004 - 4:32 pm UTC

yes, but once you close that handle -- they do not -- it is ambigous, the next time you parse it -- begin p; end; might mean something entirely different.

it is just that the caching happens for compiled stored plsql units only (not triggers, just packages, procedures, functions). since you do not submit large plsql blocks from clients (most of them should be nothing more than begin p; end;!! you don't put large chunks in to the client) -- the caching isn't even needed really. put the sql in plsql, compile it into the database and you are done.

ok..

A reader, July 19, 2004 - 6:18 pm UTC

"yes, but once you close that handle -- they do not -- it is ambigous, the next
time you parse it -- begin p; end; might mean something entirely different."

can you give an example where this happens?..once session
authentication is over and parser has resolved
the names - after that I am not sure how the above can occur..


Tom Kyte
July 19, 2004 - 7:12 pm UTC

alter session set current_schema=foobar;

but it really isn't relevant, it isn't done, it isn't worth doing. nothing short of a compiled procedure/package/function has this feature. Once you parse it (the anonymous block) and "close it", it's cache goes away, there is nothing to "hang onto". The problem is 100% solved by parse once, execute many.

well..

A reader, July 19, 2004 - 8:13 pm UTC

"alter session set current_schema=foobar;"

If you did that then in all other cases (compiled objects)as well you would need to parse, correct?
I was looking for an example where you have a case which
exists only in anonymous blocks...

perhaps you are right about it not being required..
but i just failed to see what the reason is for
treating anon blocks as "second class citizens"
in terms of caching as compared to other compiled objects...


Tom Kyte
July 20, 2004 - 8:40 am UTC

the compiled, stored, named object SCOTT.P, as opposed to the compiled but not stored and not named "anonymous" block is not ambigous regardless.

anonymous blocks are not stored, named objects - hence they are second class citizens.


Solution to "inability to get correct answer" in chapter 1

A reader, July 21, 2004 - 11:02 am UTC

Hi Tom,

What would be an appropriate way of addressing the problem you raised in chapter 1 - "inability to get correct answer" . In other words, what strategy should be used when developing in Oracle to avoid the wrong answer problem in that situation? Use select for update on the emp table before doing the update on the dept table? That doesn't seem to make sense. Do we want to force a deadlock / rollback as SQL Server does? Please help me out as I'm a little bit confused here...

Tom Kyte
July 21, 2004 - 11:12 am UTC

you could use a materialized view with "refresh on commit" as one approach.

you could serialize at the deptno level -- a stored procedure for example would:

a) select rowid from dept where deptno = p_deptno FOR UPDATE;
b) do the insert/update/delete of the detail table
c) update the dept row locked in a)

neither of which would deadlock, both of which would cause serialization at the DEPTNO level -- one at commit time only and the other at transaction time

but -- actually -- i would personally not even store that data (I should have said that, it is a poor practice - but a common one none the less).

A reader, July 21, 2004 - 11:26 am UTC

Hi Tom why the letter in your book is biiiiiiiiiiiiiiiiiig, this makes them seems a bible, an is almost imposible to use as a reference you can take to other place.

I don't know if I'm a extraterrestrial, but I print the documentation 2 pages per pages, I hear of someone who did 4 pages per page, print code to debug in courier 6 points.

If you think to write another book, try smaller font please



Solution to "inability to get correct answer" in chapter 1

A reader, July 21, 2004 - 11:43 am UTC

Thanks for the quick response Tom! I was hoping for an answer maybe tomorrow, or at best in a few hours -- not in less than ten minutes!!!! I'm giving an informal talk to some developers tomorrow and wanted to use this example, but I didn't want to pose the problem without having a "best practice" solution. This really helps.

You are truly the best.

your next book

A reader, July 23, 2004 - 4:23 pm UTC

Hi Tom,
What will be your next book about?
why don't you do a expert book, in practical matters, how to read trace, dump files, etc.
hidden hints that helps, etc.

All for experts (not beginners) I think that book will be very required. And you are THE ONE to do that

A reader, July 23, 2004 - 6:29 pm UTC

Hi Tom, where I donload the profile code for you book expert one on one now there is no wrox.

Tom Did you thought in create for dowload (even paying) a spider from your own site in a zip file to read locally, because using internet is slow.




Tom Kyte
July 23, 2004 - 8:15 pm UTC

apress.com has the download for expert one on one Oracle.

No, no thought to make this dynamic content downloadable -- it changes every day, I'm not supporting an offline application and I myself use it every single day over a 144kbs connection (burst to 144kbs -- more like 80kbs standard). I'm using it that way right now. It's more than fast enough loading pages even at that slow rate....

Discussion aboult your book in Russia

Vera, July 30, 2004 - 3:56 pm UTC

Greetings, Tom!

As I discovered Russian Oracle forum at: </code> http://www.sql.ru/forum/actualtopics.aspx?bid=3 <code>I found out that your books are extremely popular in Russian Oracle community and people frequently refer to them and discuss them. Right now there is an ongoing discussion about your Chapter 2 from Expert One on One Oracle, particularly a sub-chapter "DBWn - Database Block Writer".
You stated:
"The fact that DBWn does its slow job in the background while LGWR does its faster job while the user waits, gives us overall better performance. This is true even though Oracle may technically be doing more I/O then it needs to (writes to the log and to the datafile) - the writes to the online redo log could be skipped if, during a commit, Oracle physically wrote the modified blocks out to disk instead"
Your last phrase (about "skipping" redo log) inspired some questions and objections. Russian gurus gave me a permission to ask if you could explain this issue a little bit. Basically, they think that you were either incorrect about skipping redo log, or you meant something else.

Thank you


Tom Kyte
July 30, 2004 - 6:11 pm UTC

I was speaking hypothetically and perhaps something got lost in the translation.



I was saying (with extra thoughts in parens)

.... the writes to the online redo log (in theory) could be skipped if, during a commit, Oracle physically wrote the modified blocks out to disk instead. (but since it doesn't work that way, that is just a hypothetical)



the comment was -- IF Oracle wrote the blocks to disk upon commit, redo logs would not be necessary. BUT Oracle uses redo logs -- increasing overall IO -- in order to increase performance.


Russian Forum

Vera, July 30, 2004 - 11:03 pm UTC

Thank you, Tom.
I guess we Russians take everything too literaly due to the tragic nature of our souls :)

abc, July 31, 2004 - 1:22 am UTC

What book should i buy
1. one to one
or
2. effective oracle by desingn?

???

Tom Kyte
July 31, 2004 - 12:05 pm UTC

both of course!



Dave, July 31, 2004 - 11:25 am UTC

Both, they are mutually exclusive - but compliment each other.

If you are fairly new to Oracle thought, one on one would be the first one to get

Tom Kyte
July 31, 2004 - 12:19 pm UTC

well, if you are new to Oracle -- Beginning Oracle Programing is a good intro as well.

q on your new book page 287 -289

A reader, July 31, 2004 - 7:44 pm UTC

Here you show softer soft parses. In particular
you show in pages 288-289 the impact of pl/sql cursor
cache. My question is why did yu choose dynamic
sql for the first case. Perhaps, a more "fair"
comparison would have been to use refcursor but
use static sql
open l_cursor
for select x
from t
where x = i;

This should reduce the difference between the two
cases - esp. in the latching..

Thank you!

Tom Kyte
August 01, 2004 - 10:20 am UTC

ref cursor sql is always dynamic sql (actually under the covers, all sql is dynamic sql but thats another discussion).

but given that session_cached_cursors is mostly designed for java and vb programs that are ill-behaved in their programming with respect to cursors AND the only kind of sql they do is sql stored in a string....

ok - i have some doubts

A reader, August 01, 2004 - 4:48 pm UTC

"ref cursor sql is always dynamic sql(actually under the covers, all sql is
dynamic sql but thats another discussion)."

Can you elaborate? In my mind static sql is that
which does not change at run time (or from the
compiler point of view can not change at
run time.) e.g.
begin
open l_cursor for
'select x from t where x = :x' using i;
end;
is dynamic but
begin
open l_cursor for
select x from t where x = i;
end;
/
is static since in this case the compiler can
do lots of checks at compile time...(there are
other differences as you have indicated many times
)

Now in case of ref cursors, I think I know why
you say that they are "dynamic" - since they can
be associated with a different select statement
e.g.
(pseudo code)
if user_input = 1 then
open l_cursor for
select x from t where x = i;
else
open l_cursor for
select y from t1 where y1 = i;
end if

But still there is a difference between the
case where you use quotes and the case where you
don't/. Following experiment shows it I think...

schema created as:
scott@ORA10G> drop table t1;
scott@ORA10G> create table t1 as
scott@ORA10G> select rownum as x
scott@ORA10G> from all_objects
scott@ORA10G> where rownum <= 10000;

then compare opening and closing of cursors
in two cases - same select - one "static" another
"dynamic" - both using ref cursors:

scott@ORA10G> alter session set session_cached_cursors=500;

Session altered.

scott@ORA10G> exec runstats_pkg.rs_start

PL/SQL procedure successfully completed.

scott@ORA10G>
scott@ORA10G> declare
2 l_cursor sys_refcursor;
3 begin
4 for i in 1..10000
5 loop
6 open l_cursor for
7 select x
8 from t1
9 where x = i;
10 close l_cursor;
11 end loop;
12 end;
13 /

PL/SQL procedure successfully completed.

scott@ORA10G> exec runstats_pkg.rs_middle

PL/SQL procedure successfully completed.

scott@ORA10G> declare
2 l_cursor sys_refcursor;
3 begin
4 for i in 1..10000
5 loop
6 open l_cursor for
7 'select x
8 from t1
9 where x = :x' using i;
10 close l_cursor;
11 end loop;
12 end;
13 /

PL/SQL procedure successfully completed.

scott@ORA10G>
scott@ORA10G> exec runstats_pkg.rs_stop(80)
Run1 ran in 392 hsecs
Run2 ran in 461 hsecs
run 1 ran in 85.03% of the time

Name Run1 Run2 Diff
LATCH.shared pool 10,041 20,042 10,001
LATCH.library cache 20,062 40,058 19,996
LATCH.library cache pin 20,048 40,044 19,996

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
50,634 100,728 50,094 50.27%

PL/SQL procedure successfully completed.

As you can see the static version runs faster and consumes
much less resources....

"but given that session_cached_cursors is mostly designed for java and vb
programs that are ill-behaved in their programming with respect to cursors AND
the only kind of sql they do is sql stored in a string.... "

I guess this could be justification for using
"dynamic" sql in your tests...


So may be conceptually they are same but practically
there seem to be differences...

Thanx!






Tom Kyte
August 01, 2004 - 5:27 pm UTC

'static' and 'dynamic' are 'precompile' things -- things the 'language' itself can take advantage of.

using static sql in PL/SQL and Pro*C is great -- because the compiler can verify datatypes (and in the case of plsql -- use things like "for x in ( select* from t)" to set up records and datatypes for us and protect us from change.

But, at the end of the day, PLSQL is just sending the SQL to a database API -- regardless of whether it was sql generated by "for x in ( select * from t)", "cursor c is select * from t" or "open c for 'select * from t'".

The compiler at runtime can take advantage of static sql -- setting up dependencies, setting up variables -- maintaining the lengths/datatypes of these variables (because of the dependencies) and so on -- but the SQL is processed in pretty much the same way at the database level.


And don't get me wrong, if the query didn't need to be dynamic, it should *not* be dynamic. In my case -- i was trying to show what a java, vb, etc programmer would expect -- so yes, i used 100% truly "dynamic sql" that would have to go through the same sorts of processing.


thanx!

A reader, August 01, 2004 - 6:01 pm UTC

that explains the logic of why you chose a "dynamic" sql
for your example...

Have a nice day!

Thanks !!! bit more needed pls..

RD, August 12, 2004 - 6:40 pm UTC

Hi Tom,
I am going through both the books at the moment and they are real good, great infact.
I have 2 more weeks in joining a company as a production DBA. I don't have a huge experiance but am quite confident.
The first thing they want me to do is perform a full review of their databases. They are using Oracle 8.1.7.4 on Windows2000 (yuck!!!) and peoplesoft is the application (of which I think I will not have too much control of). Where and how should I start. I have an idea but I want a "REAL" professional to advise me.
I looked for the appropriate answer to it in your books but would really appreciate if you could point me to some kind of methodology or checklist or whatever is the best in your openion to go about it.
Thanks yet again...
Regards,
RD.


Tom Kyte
August 13, 2004 - 9:38 am UTC

If you look at the back inside cover of "Effective Oracle by Design" -- i have a reading roadmap.

Since you are using peoplesoft, you'll want to get active in their groups as well -- perhaps someone will come along and offer up "the best Oracle/Peoplesoft" groups to participate in here.

You might want to peruse groups.google.com -> comp.databases.oracle.* (there are people soft groups as well).

abc, August 17, 2004 - 1:20 am UTC

SQL> SELECT department_name, SUM(salary) AS dept_total
  2  FROM employees, departments
  3  WHERE employees.department_id =
  4  departments.department_id
  5  GROUP BY department_name HAVING
  6  SUM(salary) > (
  7  SELECT SUM(salary) * 1/8
  8  FROM employees, departments
  9  WHERE employees.department_id =
 10  departments.department_id)
 11  ORDER BY sum(salary) DESC;

DEPARTMENT_NAME                DEPT_TOTAL
------------------------------ ----------
Sales                              304500
Shipping                           156400


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=2 Bytes=38)
   1    0   SORT (ORDER BY) (Cost=10 Card=2 Bytes=38)
   2    1     FILTER
   3    2       SORT (GROUP BY) (Cost=10 Card=2 Bytes=38)
   4    3         HASH JOIN (Cost=3 Card=106 Bytes=2014)
   5    4           TABLE ACCESS (FULL) OF 'DEPARTMENTS' (Cost=1 Card=
          29 Bytes=406)

   6    4           TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=1 Card=10
          7 Bytes=535)

   7    2       SORT (AGGREGATE)
   8    7         HASH JOIN (Cost=3 Card=106 Bytes=848)
   9    8           TABLE ACCESS (FULL) OF 'DEPARTMENTS' (Cost=1 Card=
          29 Bytes=87)

  10    8           TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=1 Card=10
          7 Bytes=535)





Statistics
----------------------------------------------------------
          0  recursive calls
         30  db block gets
         18  consistent gets
          0  physical reads
          0  redo size
        512  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL> set timing on


SQL> WITH
  2  summary AS (
  3  SELECT department_name, SUM(salary) AS dept_total
  4  FROM employees, departments
  5  WHERE employees.department_id =
  6  departments.department_id
  7  GROUP BY department_name )
  8  SELECT department_name, dept_total
  9  FROM summary
 10  WHERE dept_total > (
 11  SELECT SUM(dept_total) * 1/8
 12  FROM summary )
 13  ORDER BY dept_total DESC;

DEPARTMENT_NAME                DEPT_TOTAL
------------------------------ ----------
Sales                              304500
Shipping                           156400

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=29 Bytes=870)
   1    2     RECURSIVE EXECUTION OF 'SYS_LE_2_0'
   2    0   TEMP TABLE TRANSFORMATION
   3    2     SORT (ORDER BY) (Cost=5 Card=29 Bytes=870)
   4    3       FILTER
   5    4         VIEW (Cost=1 Card=29 Bytes=870)
   6    5           TABLE ACCESS (FULL) OF 'SYS_TEMP_1_0_FD9D666A' (Co
          st=1 Card=29 Bytes=551)

   7    4         SORT (AGGREGATE)
   8    7           VIEW (Cost=1 Card=29 Bytes=377)
   9    8             TABLE ACCESS (FULL) OF 'SYS_TEMP_1_0_FD9D666A' (
          Cost=1 Card=29 Bytes=551)





Statistics
----------------------------------------------------------
        106  recursive calls
         36  db block gets
         27  consistent gets
          1  physical reads
       1212  redo size
        512  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          2  rows processed

which plan is better as with summary clasue is taking too redo as compare to select
 

Tom Kyte
August 17, 2004 - 7:56 am UTC

tables are way too small to say anything about anything at all. any and all queries can generate redo (block cleanouts) and redo generation is not something that is "slow slow slow".

oracle

saeed, August 19, 2004 - 4:54 am UTC

oracle

Peoplsoft and Oracle

Nick, August 31, 2004 - 2:06 am UTC

To the reviewer who is starting at an Oracle/Peoplesoft site.

Your right about not having much control over PS. It's a pain especially when you get the question "Why is it so slow today?". No detail, no expected run times, nada.

When you join, get their administrator to set you up on Peoplesoft Customer Connection. That's their own forumn. Also use PeopleBooks extensively. At first they are a bit akward, but they do contain a surprising level of information.

Finally, FWIW, a bit of advice. Reasearch is key in this game. Have a good list of web sites and preferably a decent personal library (some sites don't allow web access even in this day and age!!). Apparently this Tom Kyte chap wrote a few notes a while ago, may be worth a look ;).

Good luck



Question on your book "Effective Oracle by Design"

Lisa, August 31, 2004 - 1:13 pm UTC

Tom,

On page 141, you give an example of runstats in which you use various implementations of dbms_sql. My question relates to the bind_execute procedure. But first a little background...

I will be implementing a database auditing utility and am currently prototyping 3 different options:

1) Single audit table, generic structure (example in another thread on your site). Unfortunately, this approach will most likely prove to be too difficult to report against (I guess they didn't like the pivot example I sent them), and the single big table will be truly very, very big since I have to keep this stuff online for 75 months. I also was worried about contention issues with a single table...

2) Table-specific audit table, but still generic structure (column_name, old_value, new_value). This gets the DBAs that will have to support ad hoc reports to calm down a bit. (Except, with any of the "skinny" table approaches they are concerned since our system already produces excessive redo.)

3) Table-specific, strongly-typed (e.g. name_new, name_old)

I plan to try to keep all approaches configurable by having code to write my code based off a config table. Obviously, the code generation gets harder with each approach as the audit table becomes more strongly-typed.

Anyway...

Options 2 and 3 require dynamic SQL to keep the insert_audit logic centralized in a single package.

My question is, how should I handle dbms_sql parsing in these procedures that will be called from a trigger? I have a connection pooling environment, so sessions will be grabbed from the pool and used to perform DML that will eventually fire my trigger and call this procedure.

Is it acceptable to use the bind_execute approach, and basically parse once per session (across session life, not connection pool handle, so the g_first_time value will transcend jdbc "sessions")? Also, the bind_execute procedure in your book never calls close. Isn't there a problem with never closing the cursor?

Lastly (and sorry to get off on a slight tangent), Conner's "Mastering Oracle PL/SQL" book gives an example of using Oracle Streams to implement auditing. Although I looked into Streams and it's cool technology, it struck me as a bit of overkill (i.e. what do I get in return for making my code more complex)? Thoughts?


Tom Kyte
August 31, 2004 - 1:45 pm UTC

option 4)

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:4529781014729 <code>



If I was to go down the "do it yourself path", I would in any case write a code generator that generated static plsql implementations -- not dynamic sql at runtime.

create a package to support a table.
create a trigger that calls package.

all of the generic code would be in the creation of the package and trigger -- they would be 100% optimized, static, specific code.


as for using streams, you get:

a) the ability to build an audit server, one that has the audit trail for many databases consolidated into one

b) the ability to remove from the DBA on the originating site the ability to wipe out the audit trail (as the audit trail is elsewhere in the system, out of their control)

c) the ability to filter what you want to audit

d) the ability to have auditing not materially affect the runtime performance of your existing system (mines redo after the fact instead of runs code during the transaction)

for starters...

Option 4 not an option

Lisa, August 31, 2004 - 2:50 pm UTC

Tom,

I can't use Workspace Manager because we have a legacy system that expects the tables to look exactly as they are, and it can't be tricked by a view (long story). Sorry, I should have mentioned this.

I see your point on the code generation, if I'm going to go to the trouble to generate the audit triggers and modify the audit tables when my configuration changes, I guess I may as well go ahead and generate the insert_audit code on the fly too.

Of course, dynamic sql isn't necessary if I use one big audit table. Do you see a problem with the single table approach, even if it has 500 million rows or more? Seems a bit awkward...I'd have to really fight to defend such a design. I did defend it some on the Oracle-L regarding contention issues (e.g. partition by table_name), but I can't argue that doing complex queries with pivots on a table that big wouldn't be difficult.

So are you advocating using Streams?

Regards,

Lisa


Tom Kyte
August 31, 2004 - 3:06 pm UTC

big tables are what databases are all about. we have tables with billions of rows.


no you cannot argue that doing complex queries with pivots would be easy, it wouldn't.

but you can argue -- so what is the use pattern of this table, in order to design it -- to design this subsystem, we need to know the questions that will most likely be asked, how frequently they are asked and so on. then maybe you can argue that "so what if it is hard, you do it what -- never? and even if you do it, here is a pretty view for you."




one on one

devarshi, September 01, 2004 - 9:02 am UTC

I got myself a copy of one on one .Great book .Why is the book named one on one?

Tom Kyte
September 01, 2004 - 9:30 am UTC

"Expert One on One" is the "type" of book -- expert level, a one on one style -- me, talking to you.

The title is actually just "Oracle" technically.

WROX had a way of branding their books with

Expert one on one <title here>
Professional <title here>
Beginning <title here>

so, I did

Expert One on One Oracle

Contributed 4 or so chapters to:

Beginning Oracle Programming

And did a chapter in

Professional Oracle 8i Application Programming with Java, PL/SQL and XML

the expert 1/1, beginning and professional was a "category"

Constrained by Redo

Lisa, September 01, 2004 - 9:33 am UTC

Tom,

Unfortunately, I have no idea what the usage pattern will be for the audit data. We have been told that we will have to support ad hoc reporting; presumably once or twice a year when financial auditors come knocking.

My main problem is that our Production DBAs are concerned about redo. Our legacy system already generates excessive redo since it performs a lot of unnecessary deletes and inserts (instead of updating a few columns).

Any single table approach amplifies the redo, since the redo incurred by inserting a skinny row vs a fat row is about the same. Therefore, inserted 10 rows to track a change as opposed to 1 row with 10 columns produces roughly 10 times the redo. This constraint makes my life difficult because as a developer, I want a flexible and configurable solution. I do not want a new release of System Audits every time a new audit requirement is added. I also want to be able to configure auditing differently in development, test, and production environments.

My desired solution would be to use Oracle Streams to log audits into a central table (as in Conner's book). That way the code is generic and does not have to be regenerated. When a new row is added to audit_configuration, then I dynamically "turn on" auditing by adding capture and apply rules for the table. I would also need to add supplemental logging information to capture the unique data.

However, this solution not only adds to redo by using a skinny table, I also need to add supplemental logging information as well.

One thing I can't tell from the documentation is when you say "alter table table2audit add supplemental log group new_group (unique columns) always" Are you really adding a new log group to store the uk_column values, or are you simply adding the unique columns to the existing redo logs with every transaction?

I'm afraid that adding even more redo to an already redo-overloaded system may cause more performance problems than using Streams mitigates.

Tom Kyte
September 01, 2004 - 10:00 am UTC

that doesn't create a new logfile group, just adds suplemental data to the redo stream.

Also, if you forall process, you'll find the redo difference to be no where near your estimates:

ops$tkyte@ORA9IR2> create table t1 ( id int, uname varchar2(30), dt date, data varchar2(80) );
 
Table created.
 
ops$tkyte@ORA9IR2> create table t2 ( id int, uname varchar2(30), dt date,
  2  data1 varchar2(80), data2 varchar2(80), data3 varchar2(80), data4 varchar2(80),
  3  data5 varchar2(80), data6 varchar2(80), data7 varchar2(80), data8 varchar2(80),
  4  data9 varchar2(80), data10 varchar2(80));
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create sequence seq cache 100000;
 
Sequence created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace procedure p1
  2  as
  3          type array is table of varchar2(80) index by binary_integer;
  4          l_data array;
  5  begin
  6  for i in 1 .. 1000
  7  loop
  8          for i in 1 .. 10
  9          loop
 10                  l_data(i) := rpad( '*', dbms_random.value(1,80), '*' );
 11          end loop;
 12          forall i in 1 .. l_data.count
 13                  insert into t1 values ( seq.nextval, user, sysdate, l_data(i) );
 14  end loop;
 15  end;
 16  /
 
Procedure created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace procedure p2
  2  as
  3  begin
  4  for i in 1 .. 1000
  5  loop
  6          insert into t2 values ( seq.nextval, user, sysdate,
  7          rpad('*', dbms_random.value( 1, 80 ), '*' ) ,
  8          rpad('*', dbms_random.value( 1, 80 ), '*' ) ,
  9          rpad('*', dbms_random.value( 1, 80 ), '*' ) ,
 10          rpad('*', dbms_random.value( 1, 80 ), '*' ) ,
 11          rpad('*', dbms_random.value( 1, 80 ), '*' ) ,
 12          rpad('*', dbms_random.value( 1, 80 ), '*' ) ,
 13          rpad('*', dbms_random.value( 1, 80 ), '*' ) ,
 14          rpad('*', dbms_random.value( 1, 80 ), '*' ) ,
 15          rpad('*', dbms_random.value( 1, 80 ), '*' ) ,
 16          rpad('*', dbms_random.value( 1, 80 ), '*' ) );
 17  end loop;
 18  end;
 19  /
 
Procedure created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec runStats_pkg.rs_start;
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec p1
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec runStats_pkg.rs_middle;
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec p2
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec runStats_pkg.rs_stop(10000);
Run1 ran in 25 hsecs
Run2 ran in 30 hsecs
run 1 ran in 83.33% of the time
 
Name                                  Run1        Run2        Diff
LATCH.library cache                 30,633      19,823     -10,810
LATCH.library cache pin             20,367       6,282     -14,085
LATCH.sequence cache                30,002       3,000     -27,002
STAT...session pga memory                0     196,608     196,608
STAT...redo size                 1,018,396     693,792    -324,604




 

one on one

devarshi, September 02, 2004 - 5:06 am UTC

Thanks a lot for the clarification.



system managed lmt

jas, October 14, 2004 - 3:10 am UTC

what is the exact command to build system managed lmt by not using OMF.
thanks

Tom Kyte
October 14, 2004 - 10:02 am UTC

you'll find it and all commands fully documented in the sql reference manual?

really -- you will.

(but it is the same command as the OMF one, just use a FILE NAME instead of leaving it out)

efective oracle by design

jas, October 14, 2004 - 3:32 am UTC

hi tom
in your book efective oracle by design page no 227-228
you have mentioned that in 8 kb block size nearly 26 rows will be allocated. how ?

you have made 2 columns in that table date( takes 7 bytes),
y varchar2(255);
it takes 262 bytes
so 262*26=6812 bytes + block header + table directory + row directory + 10 % pctfree.
so 6812 + nearly ( 80 to 100) + 4 + (26*4) + 820 = 7840.
where are ~ 350 bytes gone.

thanks

Tom Kyte
October 14, 2004 - 10:03 am UTC

leading byte lengths, null flags -- you are not seeing the entire row there.

the varchar2 has a leading byte for the lenght. each of the columns has a NULL flag as well.

leadingd byte length

jas, October 15, 2004 - 12:39 am UTC

so that adds only 52 of more bytes.'
i mean to say is there anything i am missing in a block structure.


Tom Kyte
October 15, 2004 - 11:13 am UTC

what is the 10% 10% of -- the entire block, the freespace on the block after the block header....

and 3*26, not 2*26

it is about 26 rows per block. you can set it up, you can measure it.

10 %

jas, October 18, 2004 - 6:39 am UTC

6812 + nearly ( 80 to 100) + 4 + (26*4) + 820 = 7840.

yes i have used 10 % of entire block read as 820.

what is third column for ? you have mentioned only two columns date and varchar2(255) in your book.
if it is for rowid i think rowid's are stored in row directory. please correct me if i am wrong.

thanks

Tom Kyte
October 18, 2004 - 8:52 am UTC

null flags -- 2
byte length -- 1

2+1 = 3

pardon

JAS, October 18, 2004 - 9:00 am UTC

sorry i didn,t got you.



Tom Kyte
October 18, 2004 - 9:11 am UTC

above i said:




leading byte lengths, null flags -- you are not seeing the entire row there.

the varchar2 has a leading byte for the lenght. each of the columns has a NULL
flag as well.




the varchar has a leading byte length field. both have null flags. 3 bytes, not 2 as you said.

thanks

A reader, October 18, 2004 - 9:17 am UTC

now i got you.

thanks

anything you want to change in your book#1

A reader, December 06, 2004 - 12:27 pm UTC

Given an opportunity
do you want to change anything on the Expert ONE-ON-ONE book ? what is that ?

Thanks, I got it

A reader, December 06, 2004 - 1:09 pm UTC


Expert one on one

Mark, December 07, 2004 - 4:12 am UTC

Ive just bought your expert one on one, (on the net) and am disappointed to see it only covers up until ver 8.17. (although that is the database we are using).
Is most of it still relevent to oracle 9 and 10?
Should i wait for a new release?


Tom Kyte
December 07, 2004 - 10:20 am UTC

it is totally relevant, just doesn't cover anything that didn't exist in 8.1.7



effective oracle by design

kit, December 07, 2004 - 7:08 am UTC

Tom,
with reagds to the book. Can you please tell me where I can download the source

Thanks

Tom Kyte
December 07, 2004 - 10:28 am UTC

www.oraclepress.com

effective oracle by design

kit, December 08, 2004 - 6:23 am UTC

Thanks

Startup code in packages

N.Balasubramanian, January 03, 2005 - 7:37 am UTC

Hi Tom
In page 592 of your second book, you have said "anyone accessing the lookup table G_LOOKUP_TBL will actually find it populated with data"
In our Organization, unix shared memory is used (populated with cobol and C programs) for caching this information and all the frontend applications use this unix shared memory for validations (without accessing the database).
1)Do we have any advantages in using pl/sql table for caching lookup data
2)Does it require more memory
3)It has got nothing to do with per session memory. Has it?
4)This is a different question. How do we simulate a java type static variable in pl/sql (That is a truly global variable for all the sessions)

Tom Kyte
January 03, 2005 - 9:08 am UTC

well, databases were born to join, lookup, write to. it is what they do best.


the plsql table is a SESSION variable, it is local to a SESSION. each SESSION would have its own copy in its own session memory.

a java static variable is LOCAL to a java program -- it is not that all java programs running share the same value -- each session in the database is like a separate "program" running in an operating system

Caching the lookup table is the answer ?

N.Balasubramanian, January 03, 2005 - 10:45 pm UTC

Hi Tom,
If plsql table is used as a session variable, then are we left with only two solutions for caching the lookup data.
1) Caching the table in the buffer cache
2) Using the keep buffer pool

Regarding, the global variable, how do we achieve a global variable, which will be continuously incremented by all sessions without holes (Using a table will serialize and using a sequence may cause holes).

Please advise.

Thanks and regards


Tom Kyte
January 03, 2005 - 11:03 pm UTC

er?

not sure where you are going with this one.

if you USE it, guess what, it'll be cached? (the keep/recycle pools are for getting that last 0.0001% out)


the elusive search for the gap free non-blocking sequence number, when you invent it without serialization, let us know :)

anything you do (using whatever technique/algorithm/whatever you want) will either

a) serialize
b) cause gaps

there are these little things called transactions.....

Giridhar, January 08, 2005 - 9:58 pm UTC

Hi Tom,
I am reading your book "Effective oracle by design". In 5th chapter (Statement processing),while explaining "Modification DML from start to finish", you mentioned as follows:

"For example, insert into t values (5) does not have a query component, but insert into t select emp from emp does have a query component".
What happens when the table "T",where we are inserting has primary key constraint? As it is required to check the primary key constraint violation before inserting data, wont we have any query component involved in this case?

Would you pls explain this case.
Thanks in advance.
Giridhar

Tom Kyte
January 09, 2005 - 11:27 am UTC

it is not a query -- it is a constraint check. You are not "reading the table looking for data", you are inserting a set of values into a table and having constraints fire (which as a side effect may well be reading the table)

think of the concepts here:

insert into t select * from t2;

obviously has a query component, you are running a query and inserting what you find.


insert into t values ( 1 );

does not have a query component, you are inserting a known value provided by you into the table.

Thanks

Giridhar, January 09, 2005 - 12:09 pm UTC

Thanks a lot Tom for your quick response.
Giridhar

PAGE 587,588 IN EXPERT ONE-on-ONE

A reader, January 25, 2005 - 4:40 pm UTC

Hello,TOM,
On page 587 you write:
create or replace view
emp_view
...select with analytical func
But on page 588 you don't use the view but the same ref cursor like in page 586.
Is there any reason for that?
Or may be is it a mistake?
Kan

Tom Kyte
January 25, 2005 - 7:01 pm UTC

well, look at that -- you are correct, the example on top of page 588 should be

begin
open :x for select * from emp_view;
end;
/

period.

Any plans to come to Germany ?

A reader, February 02, 2005 - 6:43 am UTC

Hi Tom,
when do you plan to come to Germany ? I think you have a great audiance and fan group here too ...

What can we do to see you here ?
Thank you.

Tom Kyte
February 02, 2005 - 7:55 am UTC

I just connected through Frankfurt this morning, does that count ;)



How About a New Book?

Richard, February 08, 2005 - 5:32 am UTC

Hi Tom,

What about picking all the very best bits of AskTom and creating an electronic (online?) book from them? AskTom is, of course, fantastic, but with proper diagrams/graphics, a good index, and all of the other things that make for a good book, you'd have a sure-fire winner on your hands. Hey, if you published it online, there'd be no more "Wrox has bought the farm" scenarios!

How to get your book

hash, June 14, 2005 - 11:32 am UTC

Sir,
On this site somewhere I said I can't find your books in Pakistan and you said that they are available here and are pretty cheap. But you did'nt tell me how to find them. Could you please tell me how to get them here in Pakistan

Tom Kyte
June 14, 2005 - 1:54 pm UTC

take the ISBN into a book store and see what they say -- it is printed by Apress Now.

You can buy electronic versions of it from apress as well.

A reader, August 24, 2005 - 1:10 pm UTC

Tom,

Could you please provide the link for sample chapter download of your new book Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions -
I tried searching your website but wasn't able to find one.

Thanks.

Asktom and Tom's books

Raj, August 25, 2005 - 6:53 pm UTC

Sample of your site and books popularity :

I found this in the Northrop Grumman's career site when searching for Oracle job.

"
Experience in the following areas is an asset:

* Familiarity with www.asktom.com and Tom Kyte's books
* Oracle Designer experience "


Tom Kyte
August 26, 2005 - 8:18 am UTC

doh, they got the url wrong :)

that's neat, thanks.

interviewing

Jim, August 26, 2005 - 12:51 pm UTC

Recently I had to interview DBA canidates. One of the questions was if they had heard of asktom? It wasn't a deal maker or breaker, but it gave me a feel for what they read.

Tom Kyte
August 26, 2005 - 1:58 pm UTC

instead of that, ask them what they do read (books and sites...)

A reader, February 21, 2006 - 8:26 am UTC

Hi Tom, reading your book about generic solutions, trying to make code that work in any database, I ask my self.
Aren't Oracle did the same when tries to use java on every platform, only to make it generic.
If in oracle 9i oralce would had done the enterprise manager on a specific windows like delphi or VBasic, this would had been very useful, but instead of that you did an enterprise hard to use, personally I used only in exceptional situations and I know people who never used it.
enterprise manager html is reallly better, now.

But I think is as a mistake, to try to get generic solutions when you can take advantage of development tools specific of a platform.

Tom Kyte
February 22, 2006 - 7:51 am UTC

Java is a programming language. and much like C you can achieve a great deal of portability, but never 100%.

And how well did java as a client programming language work out (well, one look to 10g and see as how - well - the client isn't java...)

And - given that a user interface is a user interface is a user interface (eg: they would have given the same user interface regardless of programming language)...


But you seem to be helping me make that point.

A reader, February 22, 2006 - 11:19 am UTC

Thanks Tom

Your point

juancarlosreyesp, March 08, 2006 - 10:22 am UTC

Hi Tom, I understood you need a time to answer the question, so I ask you again.

I think you (oracle) are doing wrong, because instead of having a specific interface for windows, taking advantage of all benefits you can get of programming in a windows specific language.
You had tried to do a generic soluiton.
Evn when now (10g) the situation is not like in 9i enterprise manager, when you tried to avoid using it, because it was not too fsat.

I think you couuld do something in windows (not java) so users say "wow what a good software. instead of doing something good generic program.

That attitude make less competitive agains sql server who has specific.

Going to a specific eaxmple, raptor, could be it better if you do on windows and take advantage to some windows features or not?
I think the difference between databases, and language programings are not as big. But still I think is important.

Even html enterprise (plus other componentes), I don't know, html(plus other componentes) is good, but if you would do enterprise on a windows program, I think this could be faster (even when you don't use any additional advantage of windows),
if you say this is some slow but will be faster on the future meanwhile faster machines are done, doesn't makes too sense, because in 5 years you will have anohter oralce database release.

What do you say about it? I want to understand your point
:)

Tom Kyte
March 09, 2006 - 12:23 pm UTC

"sorry".

Htmldb (apex) seems to be doing pretty darn good.

sql developer (raptor) as well.

I don't use windows, so I'm not really able to tell you. I use mostly linux and firefox (yes, firefox is my operating system - it happens to run on other OS's itself, but firefox is my platform for most things)

I don't agree, I don't want windows software, I don't want windows specific stuff.

A reader, March 09, 2006 - 2:36 pm UTC

Thanks Tom,

This is not about what I thing must be.
I think this is the opposite, I agree with you and with the strategy of using a generic language.
But in the practice, when this is too slow, or simple slow. The tool is started to not be used.

About raptor, I agree with you is incredibly good.

htmldb is too, but still is some slow, I'm not going to die, but if this would b e faster I would be happier.

About enterprise manager, a windows interpface could make me really happy if this would be fsater. I perceive 10g web interface some heavy, specially when you have 6 test databsae on a server + 6 console service....

And I repeat you, this is not what I think, this is something I discovered after some one commented he hated java enterprise manager, then I saw I never used, except counted situations, and the reason is because it was too slow.

The point is if you use the native development tool of every platform you can get a bit faster and some improvements, and that bit faster can be enough to make you feel really more satisfied with the product, and that few features you could additionaly could use, could make me say "uau". I'm not trying oracle change his strategy neither saying this justify to Oracle to have to give maintenance to 5 releases of enterprise, raptor, htmld.
I'm only trying to understand your point
:).

Tom Kyte
March 09, 2006 - 3:49 pm UTC

htmldb would only be slow if you write slow queries ;)


You hated an implementation, the language really didn't do it, you didn't like the program. Proof: you like raptor, raptor uses the same "technology" as the java EM.

disagree with you, that is all.

A reader, March 09, 2006 - 5:22 pm UTC

Thanks Tom

OT question

juancarlosreyesp, March 13, 2006 - 11:56 am UTC

Hi Tom one ot question, so you had published several books, I think one of the main reason to publish a book, at least on oracle, is for marketing not exactly for get money.

Which is the cheapest way to publish, for example
how much is to get the isbn number?
how much to publish only as an ebook(not printing).
Do you think one can publish a book with 1,000 U$.

Any hint?
Thank you

Tom Kyte
March 13, 2006 - 9:29 pm UTC

typically, you get paid to publish your work, I did not pay anything.



still not avaliable in india

Ajeet, March 14, 2006 - 6:36 am UTC

Tom,

We have been trying to get this book in India but we could not -no one says..anything about it.we contacted local publisher of Apress but they also did not say much..

not sure if we can know a date from you..

Expert Oracle Database Architecture: 9i and 10g Programming Techniques and
Solutions

thanks
Ajeet


Tom Kyte
March 14, 2006 - 11:01 am UTC

"sorry" is just about all I can say. I (as I've said before) have nothing but nothing to do with distribution. I wrote it, that is all.

A reader, March 14, 2006 - 7:58 am UTC

Thanks Tom.

Ajeet, why don't you try to buy in www.amazon.com?

how to start

A reader, March 18, 2006 - 4:06 am UTC

I am new to Oracle, I have heard alot about Toms's
books, can you please tell me from where to start as
of today March 2006. I mean can you tell me which of
your books (and its eiditons) to buy first, so that I
dont buy then obselete edition mistakenly.

Thanks


Tom Kyte
March 18, 2006 - 4:37 pm UTC

Expert Oracle Database Architecture (Apress 2005) - which comes with the entire text of Expert one on one Oracle on CD

Effective Oracle by Design (Oraclepress 2003)

non-overlapping, complimentary, current.

.

A reader, March 19, 2006 - 1:17 pm UTC

Thanks,

Can I directly start reading the Expert Oracle database
architecture without reading the expert one on one text
on CD.

Please keep in mind that I am totaly new to oracle
and I dont care about oracle versions prior to 9i Release
2.

Thanks

Tom Kyte
March 19, 2006 - 2:17 pm UTC

yes. expert one on one is the 8i version of the material you'll find in expert Oracle database Architecture.

One Question: How do I invoke a URL from a form developed in forms6i

Anoop, March 27, 2006 - 5:16 am UTC

I want to invoke a URL (like google.com) from my Oracle Form developed using developer2000 forms 6i.
How can I do this?

Tom Kyte
March 27, 2006 - 9:59 am UTC

host( .... );

there is a web package too - don't know the name (but do know it is documented).

maybe? I don't know, I haven't touched forms since March 1995. otn.oracle.com has a good discussion forum on forms

Dilip Patel, April 15, 2006 - 5:55 pm UTC

Tom,
Just finished reading your new book "Expert Oracle Database Architecture" and have to say Thank you. I was a bit hesitant to buy this book since I already have your first edition and being a regular at asktom, was wondering how much more possibly you might have in new book. All I can tell myself is, Money well spent.

Regards,

Dilip.


new to sql

mal, July 10, 2006 - 1:05 pm UTC

Tom,
I am new to sql so can you please help me to undertsnad What advantages do relational database management programs have over file processing systems

Tom Kyte
July 10, 2006 - 1:41 pm UTC

suggest you read:

</code> http://docs.oracle.com/docs/cd/B19306_01/server.102/b14220/toc.htm <code>


security, transactions, recoverability, performance, ease of programming - things like that pop immediately to mind...

discussion link on asktom

A reader, August 17, 2006 - 12:20 am UTC

As always, your books becoming my favorite ones.
Could you give us the link on AskTom that contain the discussion
on your latest book (Expert Oracle Database Architecture) just
like this one for Effective Oracle.

Could you depict the content of your incoming book ?
(Expert Orale Programming)

Thanks Tom.


Tom's books

Anne, August 17, 2006 - 4:50 pm UTC

Hi Tom,

I'd like to buy one of your books, but am not sure which one or maybe all, but I'd prefer them not to overlap and be current:
1. Expert Oracle Database Architecture 9i and 10g Programming Techniques and solutions
or
2. Effective Oracle by Design
or
3. wait for Expert Oracle Database Development 9i and 10g Programming Techniques and Solutions.

Basically, I'd like to read about binding, understanding tkprof and other tools, effective tuning etc .

Would you have suggestions ? Thanks.





Tom Kyte
August 17, 2006 - 5:33 pm UTC

that would be effective Oracle by design - the next book will not be out for a bit.

Tom's books

Anne, August 18, 2006 - 8:44 am UTC

Thanks Tom!

New Books - Suggestion / Request.

Vikas Sangar, September 22, 2006 - 12:55 am UTC

Deare Mr. Kyte

I have a small suggestion (rather a request) for your upcoming books. Can you please include / add few pages in your newly upcoming books on Oracle(espeially Expert one-on-one performance by design), relating to the one of the Oracle's dreaded "Internal Errors" like, for Example Ora - 00600 / 00603 etc? I think it would be a great gesture to implement as some of us (atleast myself) do not exactly have that much amount of knowledge and confidence to deal with such errors. Also, the level of help and documentation avaialabe on theis topic is very much confined and neither do most of us have access to Oracle Support. So throwing some light on this issue (Oracle Internal Errors) in your upcoming books would come out as a blessing to all the members of Oracle fraternity.

Regards.
Vikas.



Tom Kyte
September 22, 2006 - 2:55 am UTC

ora-600
ora-7445
ora-3113

I have a simple answer for that - they all indicate "please utilize support"

However, in chapter 3 of Expert Oracle Database Architecture, I did write a full section entitled:

...
Trace Files Generated in Response to Internal Errors

I’d like to close up this section with a discussion about those other kinds of trace files—the ones we did not expect that were, the ones generated as a result of an ORA-00600 or some other internal error. Is there anything we can do with them?
.......

The reader opinion...

A reader, September 22, 2006 - 8:01 pm UTC

Tom, dont you like to maintain the correction of your book by your self in AskTom. May you could adding a tab in this site titled "My Books" that contain errata, addenda just like Jonathan do with his book.

So they are maintainable, up to date, and easy to get.


Tom Kyte
September 24, 2006 - 1:27 pm UTC

but, well, the publishers do that for me - on their websites.

I've always wondered why Jonathan did it himself, since they (apress, wrox, oraclepress at least) do it for me.

In fact, they roll the changes into the book itself so that subsequent printings don't have the errata.

chapter or book on Analytics

Vinayak, September 25, 2006 - 7:50 am UTC

Hi Tom,
Once you said that Analytics are large enough to write a complete book on them. If i remember it correclty you mentioned writing a book on it as well.
Can you throw some light on it, when that book will be on table.

Regards
Vinayak

Tom Kyte
September 25, 2006 - 2:57 pm UTC

I mentioned that one could think about writing a book on them :)

I haven't made any firm plans to do so

A good Book covering SQL*PLUS and T-SQL

Vicky Bhai, September 27, 2006 - 3:02 am UTC

Hi Tom,

Can you please suugest any good Book that covers both Oracle SQL*PLUS and MSSQLS T-SQL in gerater details, exetensilvely.

Any good SQL Book book that not only makes us well conversant with the syntax anomalies but also makes one more conversant in understanding both the Veriations of SQL and enables the Developer/ DBA to migarate / shift / differentiate / Map / Compare, the two different Databases easily.

Regards..


Tom Kyte
September 27, 2006 - 5:05 am UTC

I cannot imagine a good book that would cover Oracle and SQLServer at the same time.

sqlplus by the way is not comparable to t-sql. sqlplus is a command line interactive tool used to run ad-hoc queries against oracle. t-sql is sqlservers stored procedure language.

You likely meant "plsql and t-sql".

And you will get a book on plsql and then a book on t-sql, you will not likely find a book of any quality (likely not find any book at all really) that covers both topics at the same time.

That would be like a book that does Java and Visual Basic at the same time ;)

reader opinion followup

Rudi, September 27, 2006 - 10:24 am UTC

I think they provide incomplete errata. When I surf this site, I found many correction made by you, but not available in Apress website (Only 9 corrections by now). That is why updateable errata is really IMPORTANT to your lovely reader :).

Anyway, thanks for writing extraordinary good books, and I am waiting for the next..

Confused of which book to buy

ocp_kir, October 27, 2006 - 4:32 pm UTC

Hi Tom,
I am just 15 days old viewing this wonderful site. I am deeply impressed by your knowledge in Oracle.

I am a DBA working with Oracle9i database. And I am interested in 10g also. I see a lot of books written by you. I am very much eager to buy some of ur books which cover architecture, backup and recovery, perf tunning especially. Can you please guide me which all books I have to buy so that I get most out of your books without repetition.

Also please guide me where from can I buy ur books. Are they available in India.

Tom Kyte
October 27, 2006 - 8:11 pm UTC

"ur" - I never worked with anyone named that.....


Effective Oracle by Design and Expert Oracle Database Architecture are my current books - and there are links to discussions on them from the asktom homepage so you can see what they are all about.

Confused on which books to buy

ocp_kir, October 28, 2006 - 11:30 am UTC

Tom,
what about ur Expert one-on-one performance by design book, whcih is the one where I am more interested of.

Thanks


Tom Kyte
October 28, 2006 - 1:14 pm UTC

"ur" - who is that? I'm not familiar with that author.

I did not write an "expert one on one performance by design"

there is "Effective Oracle by Design" - Oracle press, 2003. Sort of "my approach", my practices.

there is "Expert Oracle Database Architecture", Apress, 2005. How Oracle works, my version of the concepts guide.

Confused on which books to buy

ocp_kir, October 28, 2006 - 11:37 am UTC

Hi Tom,
I am not asking for a single book which covers all of the above topics. I just wanted to know what different books I need to buy so that I get most of ur books without repetition.

Thanks in advance

Effective Oracle By Design Question

A reader, November 04, 2006 - 7:16 am UTC

I am reading your book effective oracle by design.
The question occur from chapter 8, Effective SQL page 453.

On middle of the first paragraph you said that

"[32] would actually be more ezpensive than doing the scan with a setting of 16 on this system"

Here I miss the point, based on your observation :

Time Waited Total Waited Cost

16 1,670 1.54 2,603
32 846 2.52 1,650

Why 32 do less efficient than 16?
Please elaborate the way you decide to choose breakoff point.

Thanks so much,
YRT.

Tom Kyte
November 04, 2006 - 12:29 pm UTC

do you see the total time waited?

it was less efficient on that machine, with it's disk to do the IO at that size.

I didn't decide the break off point, it makes itself apparent.

toc

A reader, November 23, 2006 - 2:13 am UTC

where we can found the table of content of Effective Oracle By Design? please refer an url.


Tom Kyte
November 24, 2006 - 6:17 pm UTC

well, I did not know the URL, so let me tell you how I found it.

I know the book was oraclepress (that was obvious from amazon)

so I went to www.oraclepress.com

and I searched for the book

and there it was, clicking on book revealed contents.

</code> http://books.mcgraw-hill.com/getbook.php?isbn=0072230657&template=oraclepress <code>

Index FS and Index Fast FS

A reader, November 30, 2006 - 12:26 am UTC

Why index full scan and index fast full scan do not apply to the select order by, or select
only on indexed column respectively.

create table t
as
select to_char( to_date('01-jan-1995','dd-mon-yyyy')+rownum, 'yyyymmdd' ) str_date,
to_date('01-jan-1995','dd-mon-yyyy')+rownum date_date
from all_objects;

create index t_str_date_idx on t(str_date);

create index t_date_date_idx on t(date_date);

exec dbms_stats.gather_table_stats( user, 'T',
method_opt=> 'for all indexed columns size 254', cascade=> true );


--1) why CBO do not full scanning t_str_idx?

select * from t order by 1;


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=275 Card=42515 Bytes=722755)
1 0 SORT (ORDER BY) (Cost=275 Card=42515 Bytes=722755)
2 1 TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=34 Card=42515 Bytes=722755)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
134 consistent gets
0 physical reads
0 redo size
1003803 bytes sent via SQL*Net to client
31678 bytes received via SQL*Net from client
2836 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
42515 rows processed

--2) why CBO do not fast full scanning the index?
select count(*) from t;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=34 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=34 Card=42515)


I am using Oracle 10g R2 with all default optimizer setting.
Please verify, if I am wrong on understanding chapter about Effective SQL.

Tom Kyte
November 30, 2006 - 9:53 am UTC

because all of the columns are NULLABLE in this example.

make them NOT NULL and you'll see a change.

</code> http://asktom.oracle.com/Misc/mull-about-null.html <code>

Your New Book

Yogesh Purabiya, March 20, 2007 - 11:52 am UTC

Is your book "Expert Oracle Programming" (Apress) now available in India ? Does it cover 9i / 10g ?

Digital version available?

Gabriel Canton, August 05, 2008 - 3:40 pm UTC

Excuse me if this is a dumb question. Is there a digital version of Effective Oracle ... that companies and the like can buy? I've got the printed one and want all the developers in my company to read it, but buying 50 or 60 paperbacks does not seem practical ...
Thanks!
Tom Kyte
August 05, 2008 - 6:40 pm UTC

I do not believe Oracle Press does "ebooks", all of the Apress books (Expert Oracle Database Architecture for example) are ebook-able.

Chen Shapira

Chen Shapira, September 26, 2008 - 5:54 pm UTC

You wrote:
"This is in fact the 2nd edition volume I of Expert one on one.

We broke it into two volumes due to size and to get something out there."

When is the second volume planned?
Tom Kyte
September 27, 2008 - 11:19 am UTC

book

A reader, October 27, 2009 - 11:03 pm UTC

Tom:

I lost the expert one on one copy published in 2001.

has this book been revised or shall i get the same copy.
Tom Kyte
October 28, 2009 - 7:56 am UTC

the architecture (database) portions where updated with Expert Oracle Database Architecture.

suggest a good book on oracle 11g performance tuning

A reader, November 11, 2009 - 10:28 pm UTC

I know you will say refer oracle 11g documentation.
which I will read but to stimualte knowledge hunger and your personal prefernce suggest one.

Regards
Tom Kyte
November 15, 2009 - 1:21 pm UTC

start with the server concepts guide (newly written in 11gr2).

You know how many "performance" books I've read?

I cannot answer, because it has been so so so so SO long.

I personally find that by *understanding*, I don't need anyone to tell me what numbers to look at.


Meaning, I tend to avoid books that offer up tips, tricks, etc - many performance books fit in that general genre.

So, I don't really have any recommendations that would likely appeal to you.

suggest a good book on oracle 11g performance tuning

A reader, November 11, 2009 - 10:30 pm UTC

I know you will say refer oracle 11g documentation.
which I will read but to stimualte knowledge hunger and your personal prefernce suggest one.

Regards

NEW Editions on 11g.

lalu121212., December 10, 2009 - 12:54 am UTC

Hi Tom,

Is there any New Editions of your books released on 11g?

Thanks.
lalu.......
Tom Kyte
December 10, 2009 - 2:35 pm UTC

not yet, working on the 2nd edition of Expert Oracle Database Architecture right now actually - look for it in spring/early summer.

well, spring/early summer in my hemisphere anyway... say june 2010

A reader, December 10, 2009 - 3:08 pm UTC

Is your new edition have same chapters and you are include 11g related changes on top of that?

Also is there is any new chapters you included?
Tom Kyte
December 10, 2009 - 3:32 pm UTC

I'm adding one chapter on encryption (do it yourself encryption, column encryption, tablespace encryption)

and updating all of the content to reflect 11gr2 and before.

Pointers, December 24, 2009 - 12:28 am UTC

Hi Tom,

Thanks for your great site. your site is a dictionary reference for the people who work for sql and plsql.

I am a plsql developer, I started learning DBA as i read the columns written by you and realized that
there should not be like separate DBA and developer.

I have been reading books. These are baisc questions that eats my mind. Please clarify in your words so that i understand better.

1. what is redo. what exactly redo log files (buffer or files) contains.. Does it contain the data that is being changed or the query
that changed the data (like if emp table updated, does redo contain the data being changed or the query that changed the data)

2. In what are all the cases redo is generated (i mean for all DDL or DML what exactly)

3. What is the difference between undo segments and rollback segments.
where does these sits in the architecture. Does these available in SGA.

3. In your books while explaining 'autotrace', it is refered 'sort(memory)'...where does in general a sort happen. Is it in SGA or PGA.
Where does Physical sort happens..is it right if i say, physical sort (TEMP) happens in disk.

4. Does SGA contain the tablespaces or all tablespace sits in Disk. Ofcourse,tablespace is logical and datfile is physical.
we say in general that, oracle keeps the data in the undotable space while updating for consistency...does it mean it saves data in SGA for a while.

Please clarify in your words.

Regards,
Pointers


Tom Kyte
December 31, 2009 - 2:39 pm UTC

Pointers -

actually, if you have access to my book Expert Oracle Database Architecture - I hit all of these topics in depth and more.


1) redo is a binary bunch of data, it is the bits and bytes changes that happen to blocks, it is NOT sql statements to recreate the changes, it is what we call 'change vectors' that say things like "on block 55 for file 42, change the bytes starting at offset 12 to '0x253234231541654264'".

2) Pretty much anything that changes a block will have redo associated with it, all DML, all DDL will generate some amount of redo (even in no-logging mode, some amount of redo will be generated)

3) undo and rollback are synonymous. UNDO segments are used by automatic undo management and ROLLBACK segments are used if you do manual undo management - but they are basically one in the same. There are subtle differences but for our uses, they are the same.

3 again) sorts will take place usually in the PGA, however in shared server mode, they might take place in the SGA, since the UGA is in the SGA with shared server.

But practically speaking, for most cases, think PGA.

4) the SGA has a buffer cache, all blocks for all segments (undo, table, index, etc) are subject to being in the cache in the SGA. At any point in time, some of the data for the database - probably not ALL of the data, a small subset only - will be in the cache.

pointers, January 01, 2010 - 12:04 am UTC

Thanks Tom for your response....
Your answers were awesome..

Can you please clarify my part of 3rd question,
"Where does Physical sort happens..is it right if i say, physical sort (TEMP) happens in disk"..I mean where does sort happens if SGA or PGA memory is not enough to sort the data, does this kind of sort happens in disk or any other mechanism takes place.

In continuation to your response to my 4th question..
Can we also think of "Table space" or "segments" or "extents" (logical memory conecpt) in disk (physical memory) also (or)
Is the disk a flat memory which cant be implemented any logical memory concept like "Table space" or "segments".

In last, i hold your book i am sure i will read that coming days..

Happy New year
Thanks Tom..

Tom Kyte
January 04, 2010 - 10:16 am UTC

if a sort is done entirely in memory, it is done in the PGA in dedicated server mode and in the PGA *and/or* SGA in shared server mode.

if a sort spills to disk, it spills to temp, a sort(disk) would use temp on disk to hold that which cannot fit in memory.



A tablespace is a logical container that holds segments, segments are logical containers that hold extents together, extents are logical containers that group together logically contiguous blocks.

A tablespace is physically made up of files (which could be asm files, cooked files in a file system, raw partitions, etc).

Those logical things (tablespaces, segments, extents, blocks) physically reside in a file.

Shall i purchase or wait

Abdul, February 03, 2010 - 2:22 am UTC

Hello,
Right now i want to purchase books written by you or recommended by you. I just want to make sure from your side if the following books are outdated with respect to 10g and 11g?

# Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions by Thomas Kyte

# Effective Oracle by Design (Osborne ORACLE Press Series) by Thomas Kyte

# Cost-Based Oracle Fundamentals (Expert's Voice in Oracle) (v. 1) by Jonathan Lewis

I am not interested in 8i or 9i now.10g is my priority. So shall i go ahead and order the above books or wait a little while you release new edition?
Tom Kyte
February 03, 2010 - 10:08 am UTC

... # Expert Oracle Database Architecture: 9i and 10g Programming Techniques and
Solutions by Thomas Kyte
...

currently (as of right now) being updated to 11g Release 2 - due out this summer.

Effective Oracle by Design - still relevant, written when 9iR2 was production. Not currently being updated.


The other one is by Jonathan Lewis, not me.

RE: Shall i purchase or wait

Parthiban Nagarajan, February 03, 2010 - 11:37 pm UTC

To: Abdul from Saudi Arabia

You may want to refer this ...

http://jonathanlewis.wordpress.com/2009/01/14/books/#comment-34864

HTH

SUMMER

Parthiban Nagarajan, May 01, 2010 - 8:01 pm UTC

Hi Tom

... currently (as of right now) being updated to 11g Release 2 - due out this summer ...

In India, its Summer, right now ... I am very curious about your book release date ...

Thanks in Advance
Tom Kyte
May 06, 2010 - 11:17 am UTC

about july, 2010 in the US

question on the second edition

James Su, May 11, 2010 - 7:24 pm UTC

Hi Tom,
I just purchased the Alpha version of your new book (second edition) and have some questions. Sorry I couldn't find the thread discussing about it so I have to post them here.

Here's the code from Chapter 1, page 8:

ops$tkyte%ORA11GR2> create or replace
2 function get_first_unlocked_row
3 return t%rowtype
4 as
5 resource_busy exception;
6 pragma exception_init( resource_busy, -54 );
7 l_rec t%rowtype;
8 begin
9 for x in ( select rowid rid
10 from t
11 where decode(processed_flag,'N','N') = 'N')
12 loop
13 begin
14 select * into l_rec
15 from t
16 where rowid = x.rid
17 for update nowait;
18 return l_rec;
19 exception
20 when resource_busy then null;
21 end;
22 end loop;
23 return null;
24 end;
25 /
Function created.

My question:
Is there a teeny tiny possibility that when you lock the row, the processed_flag is updated by another session and committed?

How about:
select * into l_rec
from t
where rowid = x.rid AND processed_flag = 'N'
for update nowait;

Then you capture and ignore NO_DATA_FOUND.

And because 10g will pre-fetch 100 rows but only one is needed, it would be more efficient if we use an explicit cursor here.


page 10, 11G version:
ops$tkyte%ORA11GR2> declare
2 l_rec t%rowtype;
3 cursor c
4 is
5 select *
6 from t
7 where decode(processed_flag,'N','N') = 'N'
8 FOR UPDATE
9 SKIP LOCKED;
10 begin
11 open c;
12 fetch c into l_rec;
13 if ( c%found )
14 then
15 dbms_output.put_line( 'I got row ' || l_rec.id || ', ' || l_rec.payload );
16 end if;
17 close c;
18 end;
19 /
I got row 2, payload 2
PL/SQL procedure successfully completed.

When you open the cursor, all unprocessed rows are locked even you need only one. At this time, if another session runs the same piece of code, it will not get any result. In fact, there are still unprocessed rows available.

about "Alpha" book

A reader, May 21, 2010 - 6:53 am UTC

Hi Tom,
I have just now purchased the alpha version of your new book -

1.Will be there any difference in the 'content' of each chapter between the 'alpha' version and the 'published' book ?

2.Currently there are 15 chapters - Are you going to add any more chapters ? How many chapters are going to be in finally in the published book ?


Tom Kyte
May 24, 2010 - 12:34 pm UTC

1) yes, tech edits will take place - they are now. It will not be hugely different but corrections are being made.

2) will be 15 - maybe, maybe 16.

concurrency questions

James Su, May 24, 2010 - 5:19 pm UTC

Dear Tom,
Could you take a look at my above questions about concurrency?
Thanks.
Tom Kyte
May 24, 2010 - 7:59 pm UTC

My question:
Is there a teeny tiny possibility that when you lock the row, the processed_flag is updated by
another session and committed?


good point, thank you, yes, I will update that.


13 begin
14 select * into l_rec
15 from t
16 where rowid = x.rid <b>and processed_flag = 'N'</b> 
17 for update nowait;
18 return l_rec;
19 exception
20 when resource_busy then null;
   <b>when no_data_found then null;</b>
21 end;




as for the second bit - I do demonstrate skip locked after that part, it would not make sense to do so in that particular example as we are pre-11g and 11g is the first release that allows for skip locked in any fashion (for programmers - AQ has been using it for a while, but until 11g it was not a feature you could use)

SKIP LOCKED

James Su, May 24, 2010 - 8:30 pm UTC

Hi Tom,
About the second question, let's take a look at this test case (it's not easy to produce though):
There are two rows with processed_flag='N';
session 1 runs the code and holds locks on both two rows;
session 2 runs the same code, but returns nothing because it skips all locked rows;
session 1 updates one of the row and release the lock.

Isn't there a concurrency issue here? Session 2 is supposed to get the other row.

Tom Kyte
May 25, 2010 - 7:22 am UTC

first, we cannot do skip locked, that would defeat the purpose of the write up at that point (skip locked comes next)

second, session 2 already returned "nothing" - it would do that regardless of the approach because when it went to look - there were NO records matching the criteria to be processed.

Session 2 would get "nothing" since at the point in time session 2 went to look for anything- there was nothing to be found - no matter how you do it.

Session 2 would have to ask "again" for something to do, in any case.

Suggestion for new book - contents wish list

A reader, May 25, 2010 - 7:08 am UTC

Hi Tom,

If you plan to write any other book in near future , Here is what I sorely miss in any avaliable books on oracle:

1) How to read and understand AWR's - how to make sense out of it. I have asked several questions on this in this forum as well, but there are too many things ,I feel that you know so many thigns which you can share in this book.

2)How to read the tkprof ,explain plan -tricks of the trade . though many notes explain these 2 things but if you could take some real life problems which you might have resolved after reading the explain plans,tkprof that would really useful for us.

as you always say - that one should understand how database work , application to understand these reports and this is totally ture as well - but if there is more than this and you can explain by examples that would really open our eyes...

just a thought..as everyday I fall into such issues..so may be it is just too narrow..that is why i am calling it a wish list.

reading your 3 books have helped us all to reach to a certain level in oralce development/performance- we feel saturated , so need something new..something more to feel alive.

Thanks
Tom Kyte
May 25, 2010 - 7:44 am UTC

for #2 - effective Oracle by design - I did that one, even though it says "9i" on the cover, most all of it applies today (most all of the content is still applicable today, I would not change very much in that book)


Your new book

A reader, June 22, 2010 - 1:28 pm UTC

Hi Tom,

Your new book (Expert Oracle Database Architecture: Oracle Database Programming 9i, 10g, and 11g Techniques and Solutions, Second Edition) will be available in July. Can you please share with us what's new in this edition? How much has changed compared with the first edition? Thanks.
Tom Kyte
June 22, 2010 - 1:57 pm UTC

http://asktom.oracle.com/Misc/what-i-learned-about-deadlines.html

Much of it is an incremental update to cover new topics here and there, chapter 16 is all new.

was it a groundbreaking rewrite? No.

It added the stuff that was added between 10g R1 and 11g R2, fixed errors (caused by the database changing the way things worked) and added a new section on encryption.

Question regarding new book

Nicolas, July 06, 2010 - 9:16 am UTC

Hi Tom,

Quick question about the 2nd edition of your Expert Oracle Database Architecture book: will it ship with a CD that contains searchable materials like the 1st edition did?

Looking forward to the book!

Thanks,
Nicolas

Link in your home page

Parthiban Nagarajan, August 08, 2010 - 11:42 am UTC

Hi Tom
In the home page of this AskTom site, there is no direct link to your 2nd edition of the book "Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions".
Would you update it, please ... ?!!
Tom Kyte
August 09, 2010 - 1:10 pm UTC

updated...

write a book

A reader, August 09, 2010 - 10:27 pm UTC

Tom:

Can I ask you how many hours roughly you work per week?

Between VP job at oracle, writing books and answering questions at your site.
Tom Kyte
August 10, 2010 - 7:47 am UTC

I actually gave up that VP thing - I'm a team of "myself" now. No more expense reports to sign, no more employee evaluations to give - I work at home 50% of the time and travel 50% of the time. No time to deal with other people right now :)

You know, some people work and aspire to have the largest team possible, as many reports as they can - I went the other way...

I cannot pinpoint a number of hours, but it is not less than 40 (and I doubt it is more than 60 - unless you include all of the travel time - in the next week - my travel time alone will be 60 hours in a plane...)

A reader, August 10, 2010 - 9:10 am UTC

sir,

Assume i am writing a book on Oracle and i want to you to review my book technically -

What I have to do ? What is the procedure ?
Tom Kyte
August 18, 2010 - 11:01 pm UTC


There is no procedure. I get approached to do that - and from time to time I do (not often, it takes a lot of time to do this). The publisher would approach me. It would probably only happen if I happen to know you personally (that is, it would be done as a favor for a friend more than anything).

book

A reader, August 10, 2010 - 9:59 am UTC

Tom:

Congratulations! I am sure you are much happier & healthier now than when you had a team. No more headaches.

I had a similar situation. I was frustrated with the *Quality* of work and the *Maintenance* nightmares the team was causing so I decided to do most of the work myself.. I am much happier now and more importantly the client is very pleased (after so many complaints during the team era).

Tom Kyte
August 18, 2010 - 11:02 pm UTC

Not everyone wants to be a manager.

Being a manager is not necessarily a step forward :)

Geek of the Week!

Duke Ganote, January 25, 2011 - 2:55 pm UTC

Tom Kyte
February 01, 2011 - 12:12 pm UTC

;) that was a while ago...

Article

A reader, February 01, 2011 - 1:35 pm UTC


New Book

Bix, October 19, 2011 - 11:10 am UTC

Hi
this is Bix from Ireland (dublin)- eager to meet you in the seminar on Nov2nd .

Sir, i have a request for you :

Why dont you 'RE-WRITE' your book 'Effective Oracle by design' keeping 11gR2 in mind ?

I beleive if you wirte this book, this would be useful for at least 12G,13G in future ?

thanks
Bix

outer join

A reader, October 21, 2011 - 8:20 am UTC

In your book ‘Effective Oracle by design’ , in the chapter –‘Effecient SQL’
In the section – ‘Scalar Subqueries’
1.elimitaing Outer join ..

I don’t have the book in my hand at the moment , the example you shown in the below lines :

select a.username, COUNT(*)
from all_users a , all_objects b
where a.username=b.OWNER(+)
group by a.USERNAME


in this case – you will get ONE ROW definitely though there are no objects created by a user:
Should it not be be something like :

select a.username, COUNT(b.owner)
from all_users a , all_objects b
where a.username=b.OWNER(+)
group by a.USERNAME


am I wrong ?

Tom Kyte
October 21, 2011 - 8:33 pm UTC

that was an errata that should have been corrected in a later printing of the book - yes.

here is a more recent take on scalar subqueries:

http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51asktom-453438.html

Jonathan's new book

asktom fan, February 23, 2012 - 11:32 am UTC

Hi Tom,

Have you had a chance to read Jonathan's new book (Oracle Core: Essential Internals for DBAs and Developers)? If you have read it, what is your opinion about this book?

Thanks for the great work you are doing for the Oracle community!
Tom Kyte
February 23, 2012 - 8:06 pm UTC

Not yet, it is installed on my tablet waiting to be read ;)

anyone else who answers more questions than you

Ajeet, February 24, 2012 - 6:31 am UTC

Hi Tom,

Do you know anyone else on the earth who has answered more queries from unknown oracle developers,dbas , architects...than you , as of today. You are really amazing, I have been visiting this site since 1998 and there has been so many occasions when your answers simply save our job. it is not an easy task,if someone really understand your contribution, he will simply amazed .

Best Regards

typo in your book

asktom fan, October 18, 2013 - 9:12 pm UTC

Hi Tom,

I think i spotted a typo in the 2nd edition of your Expert Oracle Database Architecture.

On page 597 (Local Indexes and Unique Constraints), you write:

<quote>
In the next example, we will create a range partitioned table that is partitioned by a column named
LOAD_TYPE but has a primary key on the ID column.
</quote>


LOAD_TYPE should be LOAD_DATE. The following is your DDL to create the table on the same page:


<quote>
ops$tkyte@ORA11GR2> CREATE TABLE partitioned
2 ( load_date date,
3 id int,
4 constraint partitioned_pk primary key(id)
5 )
6 PARTITION BY RANGE (load_date)
7 (
8 PARTITION part_1 VALUES LESS THAN
9 ( to_date('01/01/2000','dd/mm/yyyy') ) ,
10 PARTITION part_2 VALUES LESS THAN
11 ( to_date('01/01/2001','dd/mm/yyyy') )
12 )
13 /
Table created.
</quote>



As always, thanks for your service to Oracle community!

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.