Wednesday, October 05, 2005

Next Book

Ok, book one of three is done. The first “Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions” is focused at developers and DBA alike. I believe the second one “Expert Oracle Database Development: 9i and 10g Programming Techniques and Solutions” will be as well – don’t be fooled by the word development there. It will be full of material relevant to the DBA/Developer and Developer/DBA (I’m a Developer/DBA myself…).

The third book will be the supplied packages (breaking Expert One on One Oracle into three books as it was just getting way too long). But that is later.

Anyway, I want feedback on my proposed topics. If you’ve read Expert One on One, you can see what has been dropped namely:

  • plan stability

  • autonomous transactions (briefly covered in the new Architecture book)

  • dynamic sql

And external procedures will be significantly – much – smaller. Lots of new stuff and all updated. Feedback will not only be gratefully accepted, it will be used when I think it makes sense (and I’ll post a new outline later as it evolves).

Short light and fluffy section to get going – why you have to design if you want to have any of the following three items…
Design to perform
Design to scale
Design to secure

Performance & Scalability
Very much like the existing work in Expert One on One but redone to cover the new tools, new options. Statistics is all new, I ignore that in the first book. All about binding covers everything – performance, scaling, memory utilization, SECURITY, peeking, cursor sharing exact, force and similar.
Performance Strategies
All About Binding

I had FGAC the first time (but a LOT has changed), and a preliminary N-Tier (to be totally redone). Grants, roles and the like – new. Auditing – new. Invokers and Definers rights will be perhaps the least changed of all chapters…
Grants, Roles and the like
Fine Grained Access Control
N-Tier Proxy Authentication
Invokers/Definers Rights

Advanced SQL Features
Lots to add to analytics! Materialized views, much to add. Practical Partitioning – in the Architecture book I described “how” they work, here I’d like to look at practical use cases. So, it’ll be different from the Architecture.
Materialized Views
Practical Partitioning

Spatial will be new… External procedures will be much de-emphasized, as they have been in real life with the advent of java stored procedures over time. Object Relational will be from the perspective of “using them in PLSQL to make PLSQL a better language”, not using them to store data in the database…
Domain Indexing
- Text
- Spatial
External & Java stored procedures
Object Relational Features

NOTE: I've disabled comments on this entry, if you have any - please see Next Book, Part II


Blogger Doug Burns said....

Hi Tom,

I'm certainly keen to see more on materialised views and analytics. I think you could do them both justice. The Object Relational stuff would be nice too, because it's too easy to say 'Yuck' and miss their value in a PL/SQL development environment. Interestingly, someone suggested to me within the past day or two that they struggled to find good information on associative arrays.



Wed Oct 05, 04:58:00 PM EDT  

Blogger Pete_S said....

Spatial would be good - I don't know why more people don't use it - well the locator subset anyway it's more than enough for most

Bet you'll get your book(s) finished before I do mine!

Wed Oct 05, 05:05:00 PM EDT  

Blogger Ajay said....

My boss just handed me a presentation on PL/SQL conditional compilation in 10g. Sounds like this one feature will have a big impact on how PL/SQL is instrumented and traced, and it would be great if you could include this feature in Vol 2.

Do you know yet when the book is going to be published?


Wed Oct 05, 05:17:00 PM EDT  

Blogger Alberto Dell'Era said....

Why you dropped "dynamic sql" ?
The discussion about dbms_sql was very good, the best I've ever seen, and I think that
a) dbms_sql is still useful (in fact I'm going to use it for a niche yet very important problem)
b) it's good for "educational purposes", since its API mimics very well the step performed when processing a statement.

Wed Oct 05, 05:20:00 PM EDT  

Anonymous Mario Cariggi said....

Hi Tom,

it would be great a chapter
devoted to the interactions
between the Oracle software
and some OSes (Unix, Win,...)
And even some details on
the mplementations.

Alberto just say:

b) it's good for "educational purposes", since its API mimics very well the step performed when processing a statement.

I share his opinion.


Mario from Rome, EU.

Wed Oct 05, 05:31:00 PM EDT  

Blogger Jeff Moss said....

how about design for:


advanced SQL:

MODEL clause
Rules engine ?

Can't wait!

Wed Oct 05, 05:50:00 PM EDT  

Blogger Alberto Dell'Era said....

Remember to cover runstats in the "tools" section - maybe it isn't an "officially supplied" tool but it's the one I use more often nowadays.

Wed Oct 05, 05:57:00 PM EDT  

Blogger Tarry said....

"All About Binding"

Heh heh...A whole new chapter :-)

Wed Oct 05, 06:09:00 PM EDT  

Blogger Bill S. said....

Make sure you include a tidy piece about SQL Injection - I can't believe it, but people are STILL not paying attention to it (well, they pay attention to it where I work, but I've talked to some folks and,...well let's just say they didn't even know what it meant).


Wed Oct 05, 07:32:00 PM EDT  

Blogger Thomas Kyte said....

Make sure you include a tidy piece about SQL Injection

That is why the UPPERCASE word SECURITY there :) that is what I'll be talking about.

I to am surprised by the (lack of) show of hands when I ask "how many know what sql injection is" - very few.

Wed Oct 05, 07:50:00 PM EDT  

Blogger Aman Sharma said....

2.Analytic functions
On a different note,as this will cover the programming techniques how about some pages devoted to understand better ways of writing sql statements like interpret the problem and use a better way to solve and instruementation of code in PL/SQL.No need for any syntaxes but guidelines which can be used in and as well as outside of Oracle too.
Sir when your book vol-1 will be available in reprint for India?Do you have any idea?I am still waiting for it to be here:'(
with best regards

Thu Oct 06, 12:05:00 AM EDT  

Anonymous Partha said....

Talking about design, it would probably be a good idea to explain about designing for OLTP and Warehouse.

I keep hearing you talk about DBA/Developer relationship, equally important (or more important probably) is technical / management relationship. Current situation in a lot of the companies in my part of the world (south east asia) is that management is not necessarily a technical person (and even if it is a technical person, not an Oracle person). So, would be good for atleast a page(s) on how to handle this.

In our organization we have a very good system developed by a vendor for OLTP requirement. Design (and datamodel) is very good and performs excellent when worked on for a single set of data. (one business record corresponds to data in 12 different tables). Handles versioning of the business record as well. Management starts asking for business reports from this. This talks about both the points I mentioned. First, to evaluate (and if necessary convince ourselves (team)) that the OLTP data model might not be the ideal one to generate reports, and hence need a separate model. Second to convince management that we need a separate server (mostly) for Warehousing requirements.

Thu Oct 06, 12:05:00 AM EDT  

Blogger venkat viswanathan said....

Hi tom ,
I think you can include a chapter on 10g new features with good examples.
--> Conditional compilation
--> dbms_output enhancement
--> dbms_scheduler
--> enhancements in wait events

It will be realy useful

Thu Oct 06, 02:23:00 AM EDT  

Blogger Chris said....

This comment has been removed by a blog administrator.

Thu Oct 06, 02:41:00 AM EDT  

Blogger Mathew Butler said....

Just some things I have been thinking about recently:

* Designing for archiving

I see alot if systems that try and bolt on data archiving much later in the SDLC. I believe that this should be a design activity.

* Correct exception processing

Handling expected errors and treatment of unexpected exceptions. Logging of error info to table or file. Putting a simple coding framework around exception handling so that user defined exceptions are easily identified and maintained. I'm mostly referring to PL/SQL here, but have been thinking about how a 3GL might inteface to a PL/SQL based framework.

Thu Oct 06, 04:28:00 AM EDT  

Blogger Niall said....

Instrumentation. Ties in with manageability, I guess.

In the design part it would be nice to cover at least a little bit the basics of relational theory and then the built in features of the RDBMS that mean you don't have to (for example) code your own RI constraints.

Thu Oct 06, 05:17:00 AM EDT  

Anonymous Flado said....

I would not mind seeing a tiny section on custom aggregates (STRAGG and the like):
- when would you need them
- How to make one of them
- Caveats (e.g., unordered unless used as an analytic function)

Or even a bigger section on Data Cartridges.

Thu Oct 06, 08:08:00 AM EDT  

Blogger Thomas Kyte said....

Niall said... Instrumentation

man, how did I forget that, smack on the forehead.

Thu Oct 06, 08:32:00 AM EDT  

Anonymous Scot said....

Some of this has already been mentioned, some not:

1. Intermedia / Text / Search

2. Data Guard

3. Instrumentation, a whole chapter on why, how, case examples, with details on exception handling

4. 10g Expression Filters if you use them (although possibly in volume 3 under supplied packages)

5. Maybe some OLAP stuff?

6. Streams (although possibly better in volume 3 under supplied packages)

7. HTMLDB? (that could be outside scope...but I hope towards the end of next year it will be mature enough product for someone to have a good book on it)

Also I'm really looking forward to the spatial.

Thu Oct 06, 08:36:00 AM EDT  

Anonymous Kashif said....

Hey Tom -

I personally would love to see some text on backups and recovery, strategies, scenarios etc.



Thu Oct 06, 09:19:00 AM EDT  

Anonymous Pete Finnigan said....

Hi Tom,

I am glad to see that you will make a conscious focus on security in the next volume of your book, I really look forward to see what you have to say on the subject of security.

If I was writing it I would definatly include something on what SQL Injection is, the different ways it can be exploited/used - the list gets bigger as tme goes on (see the recent Black Hat presentation by Esteban for instance ) - how dangerous it is - remote exploits!.

Also the need to apply the LEAST PRIVILEGE PRINCIPLE not least because SQL Injection is possible. If someone is able to exploit your application with SQL Injection and the user they have exploited only runs non SYS code and he has few privileges and PUBLIC privileges are tied down on key packages then the damage is less.

You obviously should talk about how to protect against it. i.e.

dont use dynamic concatentated SQL or PL/SQL unless you need to, filter input that ends up being concatenated, use binds where possible although i can accept there are sometimes reasons not to, also some hackers have found ways to exploit even when binds are used!

I would also cover auditing as its a big confusing area for most. its not trivial to decide waht audit options to turn on and then when you do there are no standard built in reports to run against the raw data. Also thought should be given to protect the audit trail itself to prevent tampering.

For me Oracle security comes down to:

configuring out the security issues
least privilege principle
defensive coding
and effective auditing

I don't know if this would fit into book 2 or not but I would talk about the growing number of ways to access the OS, either files or commands from within the database. Most are PUBLIC privileged, some not. I would add a section on ring fencing OS access, now to wrap the interface, reveal only the commands necessary via a *proxy* (ok bad choice of word in this context but you know what i mean) and locking down the default OS accesses.

Of course proxy, connection pooling, FGA, VPD, OLS as well..

OK, I think I have talked too much..:-)



Thu Oct 06, 12:25:00 PM EDT  

Anonymous Gabe said....

Alberto said:

Why you dropped "dynamic sql" ?
The discussion about dbms_sql was very good …

Dbms_sql … on the third volume maybe? There has to be some “meat” on that supplied packages book, I should think.


Can you expand a bit on the instrumentation part? Is volume 2 going to cover 10gr2 as well? Someone mentioned the MODEL clause … it doesn’t seem to me this feature really took off. Is this an example of over-engineering? … do you see it used in the field? … and hence deserving coverage in the book.

Thu Oct 06, 01:53:00 PM EDT  

Anonymous Steve said....

I too look forward to the next in the "trilogy". Have the first here on my desk already.

Based on the next title, i'm looking forward to the chapter or section I can hand off to the developer that walks up to me and asks "Why isn't your database fast?". Its always difficult to tell someone they may be naive and need a RDBMS lesson.

Perhaps a route you could take would be to walk a developer through the decision tree a DBA uses when creating new tables (or anything) for a developer. I'm sure the developer would be enlighted to the complexities involved, but also the significant benefits of good decisions. Also, it could be a very good review for a DBA who loses sight of the needs of a developer.

Thu Oct 06, 03:04:00 PM EDT  

Blogger Thomas Kyte said....

i'm looking forward to the chapter or section I can hand off to the developer

that is the goal of the "introduction", which I've recently expanded out to:

Short light and fluffy section to get going - why you have to design if you want to have any of the following items

Design to perform
Design to scale
Design to secure
Design to be available
Design to be portable

Thu Oct 06, 03:13:00 PM EDT  

Anonymous Mark said....

What about a section on how best to invoke Oracle from the operating system, covering topics like:

- externally identified users
- Unix scripts to call sqlplus etc.
- hiding the password
- error handling in scripts,etc

Pretty much all of us that will buy this book use either Unix or Windows. In larger shops, we're using scheduling tools that need to invoke Oracle.

A section that covers how to call Oracle from the outside would be great.

Thu Oct 06, 04:21:00 PM EDT  

Blogger Alberto Dell'Era said....

7. HTMLDB? (that could be outside scope

Yes! (maybe in the third volume? "supplied packages AND TOOLS")

I'm looking forward to understand the internal "mechanics", since programming is much, much easier if you have a "whiter shade of a black box".

Thu Oct 06, 05:08:00 PM EDT  

Anonymous Anonymous said....

Off-topic for this post, but can you point me to the blog entry where you talked about the favorite books you have recently read?


Fri Oct 07, 10:42:00 AM EDT  

Anonymous Anonymous said....

Don't drop information on Dynamic SQL ... misuse of Native Dynamic SQL is a common offense ... people just don't understand how to bind correctly or when to still use DBMS_SQL to avoid excessive parsing...

And then there is the whole SQL Injection issue ... definately need to include a section of Dynamic SQL ... when to use it and how to do it correctly.

Fri Oct 07, 11:51:00 AM EDT  

Anonymous Mariano said....

I guess AQ is in the third volume so I will not insist but please, please you need to talk about model clause. There's little information out there and I guess with it a lot of questions can be answered with SQL.

Fri Oct 07, 12:02:00 PM EDT  


<< Home