Customer Reviews

Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions

5 star:		(26)
4 star:		(6)
3 star:		(1)
2 star:		(0)
1 star:		(0)

 

 

I have a confession to make. I haven't read an Oracle book cover-to-cover in almost three years. Sure I skim through the latest titles for what I need and of course check out documentation of the latest releases. That's what good docs provide, quick reference when you need to check syntax, or details of a particular parameter, or feature, but have you ever read some documentation, sift through a paragraph, page or two, and say to yourself, that's great, but what about this situation I have right now? Unfortunately documentation doesn't always
speak to your real everyday needs. It is excellent for reference, but doesn't
have a lot of real-world test cases, and practical usage examples. That's where Tom Kyte's new book comes in, and boy is it a killer.

I've read Tom's books before, and always enjoyed them. But his new APress title really stands out as an achievement. Page after page and chapter after chapter he uses straightforward examples pasted right from the SQL*Plus prompt to illustrate, demonstrate, and illuminate concepts that he is explaining. It is this practical hands on, relentless approach that makes this book 700 pages of goodness.

Already an expert at Oracle? You'll become more of one after reading this book. With reviewers like Jonathan Lewis I expected this book to be good from the outset I have to admit. But each chapter delves into a bit more depth around subjects that are central to Oracle programming and administration.

No SCREEN SHOTS!
----------------
One of the things I loved about this book most of all is its complete lack of screenshots! But how does one illustrate a concept then, you might ask? These days with graphical interfaces becoming more and more popular even among technical folks, I run into the question of the command line over an over again. How can you be doing sophisticated database administration of the latest servers running Oracle with the command line? Or another question I often get is, can you really do everything with the command line? The answer to both is a resounding yes, in fact you can do much more with the command line. Luckily for us, Tom is of this school too, and page after page of his book are full of real examples and commands that you can try for yourself, with specific instructions on
setting up the environment, using statistics gathering packages, and so on. In an era of computing where GUIs seem to reign like magazines over the best literature of the day, it is refreshing to see some of the best and most technical minds around Oracle still advocate the best tool, command line as the interface
of choice. In fact it is the command line examples, and happily the complete lack of screenshots that indeed makes this book a jewel of a find.

Audience
-----------
As a DBA you might wonder why I'm talking so highly of a book more focused towards developers. There are a couple of reasons. First this book is about the Oracle architecture, as it pertains to developers. In order for developers to best take advantage of the enterprise investment in Oracle *** they need to thoroughly understand the architecture, how specific features operate, which features are appropriate, and how to optimize their code for best interaction with them. Of course a DBA who is trying to keep a database operating in tip top shape needs to be aware of when developers are not best using Oracle, to identify,
and bring attention to bottlenecks, and problem areas in the application. Second, it is often a DBAs job to tune an existing database, and the very largest benefits come from tuning application SQL. For instance if a developer has chosen to use a bitmap index on an INSERT/UPDATE intensive table, they're in for serious problems. Or if a developer forgot to index a foreign key column. This book directly spearheads those types of questions, and when necessary does mention a thing or two of direct importance to DBAs as well.

Highlights
-----------
Chapter 2 has an excellent example of creating an Oracle database. You simply write one line to your init.ora "db_name=sean" for example, and then from the SQL> prompt issues "startup nomount" and then "create database". Looking at the processes Oracle starts, and the files that are created can do wonders for your understanding of database, instance, and Oracle in general.
Chapter 3 covers files, files, and more files. Spfile replaces a text init.ora allowing parameters to be modified while an instance is running *AND* stored persistently. He covers redolog files, flashback logs, and change tracking file
s, as well as import/export dump files, and lastly datapump files.

Chapter 4 covers memory, and specifically some of the new auto-magic options, how they work, and what to watch out for.

Chapter 5 covers processes.

Chapter 6, 7, and 8 cover lock/latching, multiversioning, and transactions respectively. I mention them all here together because to me these chapters are the real meat of the book. And that's coming from a vegetarian! Seriously these
topics are what I consider to be the most crucial to understanding Oracle, and modern databases in general, and the least understood. They are the darkest corners, but Tom illuminates them for us. You'll learn about optimistic versus pessismistic locking, page level, row level, and block level locking in various modern databases such as SQLServer, Informix, Sybase, DB2 and Oracle. Note Oracle is by far in the lead in this department, never locking more than it needs to, which yields the best concurrency with few situations where users block each other. Readers never block, for instance, because of the way Oracle implements all of this. He mentions latch spinning, which Oracle does to avoid a context switch, that is more expensive, how to detect, and reduce this type of contention. You'll learn about dirty reads, phantom reads, and non-repeatable reads, and about Oracle's Read-committed versus Serializable modes. What's more you'll learn about the implications of these various models on your applications, and what type of assumptions you may have to unlearn if you're coming from developing on another database to Oracle. If I were to make any criticism at all, I might mention that in this area Tom becomes ever so slightly preachy about Oracle's superb implementation of minimal locking, and non-blocking reads. This is in large part due I'm sure to running into so many folks who are used to developing on databases which do indeed dumb you down *BECAUSE* of their implementation, encouraging bad habits with respect to transactions, and auto-commit for instance. One thing is for sure you will learn a heck of a lot from these three chapters, I know I did.

Chapter 9 Redo & Undo describes what each is, how to avoid checkpoint not complete and why you want to, how to *MEASURE* undo so as to reduce it, how to avoid log file waits (are you on RAID5, are your redologs on a buffered filesystem?), and what block cleanouts are.

Chapter 10 covers tables. After reading it I'd say the most important types are normal (HEAP), Index Organized, Temporary, and External Tables. Use ASSM where possible as it will save you in many ways, use DBMS_METADATA to reverse engineer objects you've created to get all the options, don't use TEMP tables to avoid inline views, or complex joins, your performance will probably suffer, and how to handle LONG/LOB data in tables.

Chapter 11 covers indexes, topics ranging from height, compression count, DESC sorted, colocated data, bitmap indexes and why you don't want them in OLTP data
bases, function based indexes and how they're most useful for user defined functions, why indexing foreign keys is important, and choosing the leading edge of an index. Plus when to rebuild or coalesce and why.

Chapter 12 covers datatypes, why never to use CHAR, using the NLS features, the CAST function, the number datatypes and precision versus performance, raw_to_hex, date arithmatic, handling LOB data and why not to use LONG, BFILEs and the new UROWID.

Chapter 13 discusses partitioning. What I like is he starts the chapter with the caveat that partitioning is not the FAST=TRUE option. That says it all. For OLTP databases you will achieve higher availability, and ease of administration of large options, as well as possibly reduced contention on larger objects,
but it is NOT LIKELY that you will receive query performance improvements because of the nature of OLTP. With a datawarehouse, you can use partition elimination on queries that do range or full table scans which can speed up queries dramatically. He discusses range, list, hash, and composite partitioning, local indexing (prefixed & non-prefixed) and global indexing. Why datawarehouses tend to use local, and OLTP databases tend to use global indexes, and even how you
can rebuild your global indexes as you're doing partition maintenance avoiding a costly rebuild of THE ENTIRE INDEX, and associated downtime. He also includes a great auditing example.

Chapter 14 covers parallel execution such as parallel dml, ddl, and so on. Here is where a book like Tom's is invaluable, as he comes straight out with his opinions on a weighty topic. He says these features are most relevant to DBAs doing one-off maintenance and data loading operations. That is because even in
datawarehouses, todays environments often have many many users. The parallel features are designed to allow single session jobs to utilize the entire system resources. He explains that Oracle's real sweet spot in this real is parallel
DDL, such as CREATE INDEX, CREATE TABLE AS SELECT, ALTER INDEX REBUILD, ALTER TABLE MOVE, and so on.

Chapter 15, the final chapter covers loading and unloading data. A significant portion of the chapter covers SQL*Loader for completeness, but he goes on to celebrate the wonders of external tables for loading data into Oracle. In particular there is an option in SQL*Loader to generate the CREATE statement for an
external table that does the SAME load! This is great stuff. External tables provide advantages over SQL*Loader in almost every way, except perhaps loading over a network, concurrent user access, and handling LOB data. External tables can use complex where clauses, merge data, do fast code lookups, insert into multiple tables, and finally provide a simpler learning curve.

Conclusions
------------

Yum. If you love Oracle, you'll want to read this book. If you need to know more about Oracle say, for your job, that's another reason you might read this book. Oracle is fascinating technology, and Tom's passion for understanding every last bit of it makes this book both a necessary read, and a very gratifying
one.

Couldn't decide whether to get this or Tom's earlier book "Expert One-on-One Oracle." Got this and was pleased to learn that the earlier book is included as a searchable PDF on the accompanying CD! How can you beat that?

My consulting experience has been that most implementers of Oracle don't know what they're doing. Read this and you'll know what you're doing; it has quick little experiments that drive home the most important points --how to make the common cases fast-- with complete explanations. I was already Oracle certified and learned some new wrinkles. You'll know why you paid for Oracle in this day of commoditized, open-source DBMS's.

I have read several Oracle 10G books before BUT Thomas Kyte's "Expert Oracle Architrecure" is an excellent resource on understanding some very basic concepts to highly technical details. For example: From chapter 2 "Architecture Overview", Tom gave a clear definition on what is a database and what is an instance. I think most of people made mistakes without knowing these details. I would strongly recommend you all to read this book. The same chapter has very technical explanation about memroy structures and networking architecture.
I recommend this book to all developers and DBAs who dealt with day to day operations maintaning the databases.

I liked the way Tom explained Files from Chapter 3. Is is good to find all configuration files and parameter files at one place and knowing them each individually.
Chapter 4 and Chapter 5 are two excellent resources to understand completely about an "ORACLE INSTANCE".

Overall I am really happy to have this book and will certainly recommend to my co-workers.


Ramesh

If you want to see line by line how Oracle works, then is is the book 2 get.

My only complaint is that much of this book is redundant from his previous books and I felt ripped.

Since this book is the first volume of the second edition of "expert one-on-one: Oracle", which I've been reading and re-reading for years, I will review the book by comparing it with the previous edition, hoping to help people who are considering to "upgrade".

First thing - if in the first edition you enjoyed the great writing style, the everything-backed-by-examples approach, and the handling of real-life scenarios coming from the (oustanding) experience of the Author ... great news for you: everything is still there, this edition matches (or even surpasses) the first as far as quality is concerned.

Second, I've found a lot of new topics/chapters that are brand-new, not to be found in the first edition; for example the coverage of "write consistency", the excellent chapter about "datatypes", the "parallel execution" one - in addition, obviously, to the coverage of new features and objects of 9i/10g (automatic pga management, assm, index/table compression, sorted hash clustered tables, to name just a few).

Third, the vast majority (90% or more) of the topics/chapters already present in the first edition have been improved (expanded and/or rewritten for better readability), with new examples and new scenarios - I particularly loved the new discussion about the log buffer/buffer cache interdependencies, the fresh section about "indexing myths", the use of statspack to show the impact of not using bind variables, the new ways to implement optimistic locking, and many others (there are too many to discuss, it really looks like a brand new book - a real "new edition", not just a "new version").

In short - lots of new material, first-edition stuff much improved - I couldn't ask for more or better.

This is a very good book, and I will start with what it is and what it is not:

1. It is not a manual for an Oracle administrator. For example it does not contain anything about Oracle installation, troubleshooting, backing up or restoring a database.

2. It is not a SQL or PL/SQL reference, neither it is a introduction to databases. You have to know a significant deal about databases already for the book to make effect.

3. It is not an optimization guide. You will not find execution plans and configuration parameters here.

What is it then ?

It is an extensive coverage of Oracle internals - its files, memory structures, processes, undo/redo, tables, indexes and data types. Plus it explains Oracle's approach to transactions, locking and concurrency. On top of it, a couple of chapters offer you a view to partitioning and parallel execution being another very specific tools.

The book introduces you the Oracle ultimate goal of building highly concurrent database applications and then walks you through its internal details to show how to achieve it in the applications you build.

The book strikes the right balance so as not to go deep into unnecessary bit flags and names of undocumented variables, but clearly explain the architecture of the engine. The architecture (also mentioned in the title) is the "whiteboard" kind of architecture - the one you can sketch on a whiteboard with a couple of boxes and a few arrows, which is just right in this case.

[quote]
The target audience for this book is anyone who develops applications with Oracle as the database back end. It is a book for professional Oracle developers who need to know how to get things done in the database. The practical nature of the book means that many sections should also be very interesting to the DBA.
[/quote]

The author is undoubtedly an expert, who has been working for Oracle for 15 years, with Oracle for even longer and has been consulting for God knows how many years. He also sticks to an honest, pragmatical and straightforward approach - there is not a single "enter Oracle, hold your breath" word.

[quote]
The inspiration for the material contained in this book comes from my experiences developing Oracle software, and from working with fellow Oracle developers and helping them build reliable and robust applications based on the Oracle database. The book is basically a reflection of what I do every day and of the issues I see people encountering each and every day.
[/quote]

Like the other good books out there, it just gives you something to think about. Avoiding rules of thumb,

[quote]
This is one of the main reasons why rules of thumb do not work on real-world systems: what works for you might not work for others in similar but different conditions.
[/quote]

it gives you the invaluable knowledge you could apply to your work. It is up to you to apply it afterwards.

Written very clearly, very easy to understand, nice trimmed examples, no screenshots. Highly recommended reading.

This is a great book. For the first time I can say I understand how concurrency works in Oracle. The first chapter and the ones about latches and concurrency are great. Besides, Tom has a very nice writing. He mentions some cases based on his experience as a consultant that are really interesting. Every architect/developer working with Oracle should have this book.

This is an excellent read for those looking for information on construction and deployment of the physical database model, and all of the accompanying PL/SQL code. From the title you might think it had some information on the effecient data modelling for Oracle, you would be wrong. That's not in here, but what's in here is good all on it's own. Versioning, transactions, locking, the Oracle memory model, it's all in here.

The writing is good. If I had a complaint it would be that there could be a lot more illustrations. Particular in the section on locking and latching.

Kyte is a veteran Oracle expert, and this is his latest tome on good practices when using Oracle. He discusses the most recent versions 9i and 10g.

The book is geared to both DBAs and developers using Oracle. It essentially dives straight into detailed prescriptions that should improve your coding. Kyte points out that some developers want to treat their choice of database as a black box. Sticking instead to generic SQL statements that do not assume specifics about that database. He strongly suggests that this is badly misinformed. Different databases have different implementations. Hence, having SQL code cognizant of Oracle lets you take advantage of Oracle's strengths. After all, he points out, you paid plenty for that Oracle license!

Chapter 10 on database tables was especially interesting. While you might think a table is a table, Oracle defines 9 major table types. Each addressing an important need. Of these, the object table seems to be Oracle's response to the rise of competing object oriented databases. A nifty way to combine relational and object properties and make it compatible with Oracle's main effort.

To the extent that the book compares Oracle code with other databases, it seems to be mostly with Microsoft's SQL Server. While IBM's DB2 gets barely a mention. And apparently none at all for the free alternatives of MySQL or Postgresql. But, typically, books on those often do likewise.

There is something stated in the book that I cannot let pass unremarked. It asserts that with Oracle, you need to know how it works, but you don't need "to know everything inside or out". Whereas, it goes on to claim, "When programming in C or Java, you do need to know everything inside and out, and these are huge languages". Look, for C, this is demonstrably false. C is a small language, in comparison with C++, Java or C#. C has no graphics and no object oriented ability. The classic text on C, by Kernighan and Ritchie, is only 274 pages. While Kyte's book itself states that the Oracle documentation is some 10 000 to 20 000 pages. So which is smaller, C or Oracle?

As for Java, by now the Java Standard Edition does come with many classes. Maybe, if you had to write explanatory text for all those, it might approach the 10 000 pages for Oracle. But just as the book says that you do not need to know Oracle comprehensively, so too is this true for Java. How many of you Java programmers really know all those base classes?

The book is on solid ground when it sticks to the matter at hand, Oracle. But comments like the above on C and Java can somewhat undermine its efficacy.

This is the first book I have read from Tom Kyte. I use his web site often and appreciate all the work he does to maintain the "AskTom" site.

The book is excellent. Tom's approach to helping you understand the concepts are displayed well with code examples. You will read a passage and then see an example to really enforce the principle. The examples support the concepts and using both together helped me out a great deal.

The chapters on architecure and indexes I found to be really helpful. The architecure chapter goes in much needed detail as so many books just glance over the different Oracle processes. What the processes are used for and how they interact with each other is well documented. The index chapter helped me understand how indexes operate at a block level and also what indexes are best for a given situation. At the end of the index chapter Tom has a section on Myth's and this section should be required reading for all DBAs and developers.

Something else I got from the book are methods for writing SQL and tuning it. The SQL that the author puts together looks so easy and natural for him to write. He is a master of SQL*Plus and that is a side benefit to reading the book. The techniques I picked up from reading Toms examples will help me just as much or more so than content of the book. This book is an excellent addition to my Oracle collection of books and I highly recommend reading it.