Next Book, Part II
I put back in some dynamic SQL, in a newly added section on PL/SQL. It makes sense there. Beefed up the security section with more stuff. Added a Design considerations section.
One thing I will not be covering is the use of specific application development tools such as HTML DB. There are a couple of books coming out on HTML DB itself in the near future that I’m aware of. I prefer to remain “tool independent”. I believe the material I’m covering impacts every Oracle development shop – regardless of the tools used. And I’d like to keep it that way.
One other thing I will not be covering is the area of backup and recovery. I cannot do it justice in a chapter, and there are in fact pretty good books out there already (Freeman, Oracle Press – RMAN book).
So, if you have further feedback, feed away – I’m going to stop comments on the other entry and point them here.
Short light and fluffy section to get going – why you have to design if you want to have any of the following three items…
1) Design to perform
Case studies, analogies to why performance is something you design into the “system”, not something you graft on later. You need to understand a bit about the use patterns of your data and design accordingly. Performance does not “just happen by accident”.
2) Design to scale
Similar to prior section, but goes one step further. Ok, so you have the fastest single user piece of code ever. But if two people run it at the same time – it takes forever. Single user doesn’t cut it – you need to design your application with scalability in mind – and constantly benchmark mark throughout development in order to ascertain you can meet the required load.
3) Design to secure
Security doesn’t happen by accident. Security is really hard to graft in after the fact. Case studies and analogies (I have a favorite analogy using a “car” and what would happen if security wasn’t designed in)
4) Design to be available
Availability – when was the last time someone said “it is OK if you are down, no rush, take your time…”. So, how do you design to be available…
5) Design to be portable
Same sort of concepts here as the prior sections….
II) 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.
1) Performance Strategies
Overview of ‘how to tune’ which to me really starts with ‘how to design’, much easier to design than to ‘tune’.
Coverage of the classics – tkprof, statspack, runstats. Coverage of the 10g new dbms_sqltune package and the like. Might even mention the GUI (
Statistics are an area of “gray fog” for many people. This will be an introduction to what they are – what the defaults for gathering them imply – and suggestions for what to consider when gathering them/determining how/when to gather them
The ins and outs and why of instrumentation. Why it is crucial, vital, necessary – and most of all, helpful
What to test, how to test, how 1, 2, 3 above fit into testing.
6) All About Binding (includes sql injection)
All about binding covers everything – performance, scaling, memory utilization, SECURITY, peeking, cursor sharing exact, force and similar.
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…
1) Grants, Roles and the like (least privilege as a theme)
how to use grants, roles and the like properly – meaning most implementations get it “wrong”, they over grant like mad “just in case”
2) Automating database logins
A good idea – how to automate database tasks “safely”
3) Fine Grained Access Control
All about VPD, maybe compare to OLS?
The options you have for auditing, why auditing is relevant, why auditing is not overhead, why auditing should be designed into the application beforehand. Ties in with last section way below however – on data purging…
5) N-Tier Proxy Authentication
description of what it is, how it fits in, why it is useful. Examples of how to “use it”
6) Invokers/Definers Rights
What is the difference – roles and plsql – when do you use which kind. Why plsql stored procedures rock and roll from a security perspective in further helping to carry the least privilege theme further on down..
IV) 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.
All about analytics, take existing chapter and update with more examples learned over the last 4 years and add new features.
2) Materialized Views
What they are, how they are, what they do – cover MV creation, maintenance, query rewrite, dimensions.
3) Practical Partitioning
Different from the architecture chapter where I described the physics behind partitioning, this will show “use cases” – when to use partitioning, how to decide when to use partitioning.
V) Advanced PLSQL Features
1) Using Datatypes (collections, arrays, etc)
Cover the ins and outs of using the various more “complex” datatypes in PLSQL, when to use what.
2) Dynamic SQL
When and Why to use dynamic sql (which is in reality a lot less often than you think). How to use dynamic sql – revisit sql injection talked about in all about binds
3) Optimizer Settings
Not the CBO – the PLSQL optimizing compiler, what the levels are/do and what changes between each.
4) Native Compilation
How and why and when to ncomp your code.
5) Exception Handling
How and why and when to actually have an exception, why “when others” not followed by RAISE; is probably a bug.
6) Conditional Compilation
How and why and when to use conditional compilation in 10gr2 and above.
7) Bulk Processing
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…
1) Domain Indexing
2) External & Java stored procedures
De-emphasize external procedures in C, but still describe them and demonstrate them. Concentrate more on java stored procedures – the how, when and why to use them.
3) User defined functions/aggregates
Define them – demonstrate them, caveat them – show also how you can create your own *analytic* functions which is pretty cool.
4) Object Relational Features
Much like the existing chapter, updated to cover the 4 intervening releases.
VII) Design considerations
Collection of thoughts on things to consider when working towards build a system in support of OLTP, Data Warehousing, or in order to support data purging.
1) For OLTP
2) For Data Warehousing
3) For Archiving