Oracle Mechanisms with Jonathan Lewis 1 day seminar Jun 23, 2014
This course is, in effect, a walk through the book Oracle Core. The course will cover the core mechanisms of how the central portion of the basic database engine works. There are eight chapters in the book, are reflected in eight topics that we cover. As we examine the mechanisms we will constantly be asking the question – why does knowing how this works allow me to use my time more efficiently, or make the database run faster.
What you’ll learn
- Oracle's core architectural foundations
- How much overhead is reasonable
- How to recognize when you're doing too much work
- How to predict bottlenecks and why they will happen
- How to minimise contention and locking
- Why concurrency can slow things down significantly
Arrive. Contenitnial breakfast served, and pre-workshop disscussion
A brief introduction to the principal files used in an Oracle database, the key processes and what they do with those file, and the way that Oracle uses memory.
Redo and Undo
The mechanisms that Oracle uses to change data, and reverse out change. Redo change vectors and undo records. How the redo and undo mechanisms allow Oracle to increase scalability and reduce blocking. Why we have redo log files but undo tablespaces. The three functions of undo.
Mid-morning break and discussion
Transactions and Consistency
Why writers don’t block readers. Internals of the Undo Segment – undo segment headers and the transaction table. Chaining undo records in undo blocks. Data segment headers and the Interested Transaction List. Commits and the options for cleanout. Two forms of Oracle error ORA-01555. The special handling for LOBs.
Lunch Break, full lunch served.
Latches Mutexes and Locks
Why locks are different from latches and mutexes. Methods of using memory: arrays, pointers, hash tables and linked lists. Shared memory and the threat of lost updates. How latches and mutexes protect memory locations from concurrent overwrites. An example of the interaction of latches hash tables, and linked lists – the library cache. Locks as mechanisms for sharing and queueing. How queueing mechanisms lead to deadlocks. Internal structures used for locks. Some common lock types. A graphic explanation of v$lock. How Oracle also uses locks in the library cache.
The Buffer Cache
Layers of memory structures. Granules and buffer pools, buffers and buffer headers. Working data sets and the database writer(s). Buffer re-use and the LRU/Touch Count algorithm. Finding the right buffer – the cache buffers chains. Latching and pinning to protect shared memory. Reading data blocks into the buffer, and the creation and use of read consistent copies of blocks..
Mid-Afternoon break and disscussion
Writing and Recovery
The main write I/O activity. How DBWR and LGWR work individually, and how they co-operate. The various types of checkpoints and how they anticipate recovery scenarios and minimize the work needed to recover. Ancillary I/O activity – archiving, flashback logging, and change tracking – and how the range of recovery options affects the ongoing I/O load.
Parsing and Optimising
Note particularly that this chapter is not about the arithmetic of the cost-based optimizer, it is about the various structures in the shared memory that are manipulated due to a parse call. How much does Oracle need to know to “understand” an SQL statement, and how is this information handled.
What does the shared pool look like, and how does Oracle deal with loading and accessing all the pieces of information it needs to parse and optimize a statement. We start with memory granules to build the shared pool then consider the finer levels of granularity – the sub-pools and “durations”.
How does the dictionary cache work, how does the library cache work – and what is a cursor. What is the difference between a parent cursor and a child cursor. What is the difference between an “open” cursor, and a “pinned” cursor.
- Item #: jl-004