Mohamed Houri’s Oracle Notes

November 14, 2013

Expert Oracle Database Architecture: buy your Oracle job insurance

Filed under: Book review — hourim @ 3:42 pm

Oracle Database ArchitectureIn the process of reviewing books I have bought almost a decade ago (first edition), today is the turn of Tom Kyte Expert Oracle Database Architecture.

The review of this book, and a couple of others that will follow I hope in a near future, will not be a classical review in which I will explain what I learnt in Chapter 1 and what I’ve most appreciated in Chapter 4 and so on. However, what I prefer to emphasize is what this book procured me in my daily Oracle consultancy work. It is like if I was shooting in the dark before reading this book and the light came up after I have started investigating the content of this book.

I remember a performance meeting crisis where I have been invited as one of the Oracle suffering application developers. The application was inadequately range partitioned by a partition key that was never invoked in the client business queries in which there were practically no partition elimination. All partitioned tables have been given a composite primary key (id, partition key) policed via a unique locally partitioned index. Few days before the crisis meeting, I was religiously reading Chapter 13 about Partitioning in which, among other interesting things, Tom Kyte explained the relationship between Local Indexes and Unique Constraints which should absolutely include the partition key in their definition to be allowed to exist. Oracle enforces uniqueness only within an index partition—never across partitions.

To trouble shoot this performance issue, the newly recruited DB -Architect suggested with authority to (a) transform all global (or b-tree) indexes into locally partitioned ones and (b) to get rid of the partition key from the primary local index.

I am against advises that say ”transform all”. In Oracle there are always  ”It depends” situations that make the ”change all” advice very often if not always a bad one. But the Tom Kyte words about local Indexes and unique constraints were still ringing in my ears so that I couldn’t resist the temptation to stop by and say to the architect ”You need to review your partitioning skills before suggesting such an impossible unique index change”. I would have never said that if I haven’t been in touch with this book.

This book gave me the necessary self-confidence I was lacking to develop performant scalable and available Oracle applications. It gave me a good picture of how Oracle works. I learnt via the content of this book how to model and test a situation before jumping to a hurried conclusion.

I need no effort to persuade you to have this book with you. Just go to ask tom web site and see the enormous work the author has done and you will realize how intelligent you could be by buying this book. 

November 7, 2013

Cost-Based Oracle Fundamentals: a magic book

Filed under: Book review — hourim @ 3:40 pm

Cost Based OptimizerIt was back in late 2005 or early 2006 when I was prompted by an Oracle magazine issue to buy the then best Oracle seller book of the year: Oracle Cost Based Fundamentals. When Amazon shipped me this book, I was really disappointed when I started browsing its content. The first question that came to my mind was: is this book speaking about an Oracle technology? You can imagine how much this question revealed my degree of Oracle ignorance by that time. To my defense I was working for a customer as a PL/SQL developer under Oracle 8i. My tasks were to faithfully transform business requirements into a technical requirements and then into a set of stored procedures. Oracle 8i was under Rule Based model while the book I bought explains fundamentals of Oracle Cost Based Optimizer.  I was in such a situation that inevitably the content of this new book was not matching my interests. So I put it on hold.

Several years after, I started a new job where trouble shooting performance issues was a crucial part. The application was upgraded from 8i (Rule Based) to 10gR2 (Cost Based) and was suffering terrible performance problems. It was time for me to wake up both the book and my head from their hibernation. Degustation of the book content begins.

More than 4 years after I have started trouble shooting performance problems and particularly bad query execution time, I am still using and savoring the content of this unrivalled and unequalled book.

There are 14 chapters in this book; I am not going to tell you what chapter is must to read or what has most retained my attention. This is not a book to only read and re-read. This is a book to learn by heart. This is a book you should always have with you when trouble shooting queries bad execution time. This is a Swiss knife allowing a CBO dissection.

Simply put, this book is so that if, when speaking with an experienced Oracle tuning DBA-Developer, I came to realize that he still has not read this book then I immediately measure the gap he has to fill before he will start doing correctly his job (unless he has read the Performance Guide itself :-)).

October 16, 2013

You want a bridge from single instance to RAC? this book is for you

Filed under: Book review — hourim @ 1:21 pm

rac expert

When Syed Jaafar Oracle ACE Director and one of the world RAC experts asked me to review the new book Oracle Expert Oracle RAC 12c he has coauthored with 3 other Oracle ACE Directors, my first thought was: how a non experienced  RAC developer could seriously review this book?

In my last decade of Oracle single Instance DBA-Developer carrier, I have got the feeling that Jonathan Lewis books (plus Tom Kyte and Christian Antognini ones) have let no place for other books ; at least in what those books deal about. In addition, Jonathan has a style of writing and narrating  technical features that makes you feeling as if you are reading an agreeable story. I am still waiting to read as attractive and as elegant books as those written by Jonathan Lewis.

Although I might not have any RAC experience, I finally decided to start reviewing this book more like a typical reader who knew the single instance fundamentals and wanted to learn basics of RAC. I know that I am very severe in judging the writing style of books (even in English which isn’t my mother tongue) and this is on what I was going to strengthen my attention.

The first thing I did is to go through the Table of content and isolate Chapters that might be close to my background. The Chapters I have selected for review was then 6, 10, 11 and 12. Unintentionally, those Chapters belong to Riyaj Shamsudeen. Then I started reading Chapter 8 Backup and Recovery in RAC written by Syed. I couldn’t resist the temptation to write the current review before finishing reading the complete book.

Below you can find my review of what I have already read. The review I sent to the authors contains several pages where I asked several clarifications and questions that revealed to be crucial and very important by the author themselves:

Chapter 6:   Application Design Issues: I am very pleased to say that I have been agreeably surprised by Riyaj style of writing. He goes to the essential using concise words touching the heart of his desired goal (what he wants to explain).  Although the design issues he presented can be found in several other trusted documents and blog articles, it is nevertheless worth having them listed all over in a dedicated chapter and in an elegant writing style; and among all of that, Riyaj has presented these issues always with a comparison between the effect these issues have on single instance and the magnified effect they can have in RAC. We have had a discussion about Localized Inserts and globally hash partitioning them to reduce the contention on the index. About excessive Commits, Sequence Cache, Index Design, Excessive Parallel Scans and Full Table Scans which you have already guessed are the main application design you should absolutely be aware of before embarking into a RAC project. Definitely this Chapter is a must to have Chapter for RAC (and even single Instance) designer.

Chapter 10: RAC Database Optimization: A very interesting Chapter where several important RAC specific feature have been clearly identified and explained such as Cache Fusion, Global Resource Directory (GRD), Several specific RAC wait events, etc…

When you read this (red is mine): In a single instance, a database block can reside only in the local buffer cache. Therefore, if the block is not in the local buffer cache, it is read from the disk. In RAC, the block can be resident in any buffer cache. So, a special type of processing takes place that deviates sharply from single-instance processing” and you know the fundamental work Oracle has to do to guaranty the ACID in single instance then you may realize how the task could be complicated in RAC. Riyaj  ”simplifies” this for you. He has clearly explained this concept and showed how a consistent read is fabricated and how a Current Block read is requested and got in RAC.

If you read this Chapter and Chapter 2 (Chapter 2 Redo and Undo) of the last Jonathan Lewis book  then you will be sure that you’ve made your Redo and Undo Internal “Giro” or “tour de France”.

Chapter 11: Locks and Deadlocks: In the process of reviewing this book I was very impatient to start reading this chapter. Because I am a big fan of Locks and Deadlocks and I have read everything about these two subjects. Although I can read and interpret easily a single instance deadlock graph I am unable to correctly interpret a deadlock graph from a RAC machine. Riyaj, as far as I am aware, is the first author who has published something about deadlock in RAC. I told him that I would have highly appreciate if he went into one or two real life RAC deadlock graphs and explained them in details in this chapter. Because practical cases are what readers want to see and  what they most appreciate.

Chapter 12: Parallel Query in RAC: Having no real experience in this particular subject, I read it as someone who wanted to learn how parallel Query is handled in a RAC machine. I will certainly keep this Chapter very close to me and will couple it to the work (blog articles and webinars) done by Randolph Geist and recent articles written by Jonathan Lewis to  definitely master the parallel query in both single instance and RAC. As always, my experience let me know that sometimes I read a chapter (for example Chapter 2 Redo and Undo of Jonathan Lewis) that I found very difficult to understand. But when I worked hard to understand it (and it took me several months), I magically discovered how much this Chapter is well written and strictly speaking is wonderful. I have a feeling that it will be the same story with Riyaj Chapter 12.

Chapter 8: Backup and Recovery in RAC: this chapter explains step by step how a backup and recovery of instance are done in RAC. It includes very interesting pictures which confirm the mantra “a picture is worth a thousand words”. The concepts are well presented and explained with details. I have no real experience in this field to make objective judgments. However, if I am to start a new RAC job where Backup and Recovery are in my tasks then this chapter will be close to me and I will be basing my comprehension effort on it. Nice to have 

Conclusion: The book contains 14 Chapters and more than 431 pages. I have reviewed 5 Chapters. If you come from single instance as I am, you will find this book an excellent bridge. If you are experienced RAC developers and you want to learn how to avoid main application design in RAC (you shouldn’t doing them as you are already an experienced RAC person) then Chapter 6 is for you and should absolutely be read and understood. If you want to learn internal of Redo, Undo, Consistent and Current Read, the Chapter 10 is for you. You will also learn and understand in this Chapter the main RAC wait events. If you want to learn how Locks and Deadlocks are handled (do you know that the internal process, that kills the first session which started waiting in case of deadlock, takes place every 10 seconds and not every 3 seconds?) Chapter 11 is for you; and if, as I have the intention to do, you want to definitely understand and master Parallel Query in general and in RAC particularly then print out Chapter 12 and start exploring it. I am sure that after a hard work (it depends on your knowledge) you will certainly finish by savoring it

Create a free website or blog at

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog


Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)