文件名称：Oracle Core Essential Internals for DBAs and Developers ■ ■
文件大小：5.33MB
文件格式：PDF
更新时间：2017-06-10 08:11:34
oracle core Jonathan Lewis 11g
数据库DBA必备手册 Jonathan Lewis 大神力作 Chapter 1: Getting Started . . . ................................................................................. 1 Oracle in Processes ........................................................................................................... 1 Oracle in Action ................................................................................................................. 4 Summary ........................................................................................................................... 4 ■Chapter 2: Redo and Undo ....................................................................................... 5 Basic Data Change ............................................................................................................. 5 The Approach ........................................................................................................................................... 6 An Example ............................................................................................................................................... 6 Debriefing ................................................................................................................................................. 9 Summary of Observations ...................................................................................................................... 11 ACID ................................................................................................................................. 11 Redo Simplicity ................................................................................................................ 14 Undo Complexity .............................................................................................................. 19 Read Consistency ................................................................................................................................... 20 Rollback .................................................................................................................................................. 21 Summary ......................................................................................................................... 22 ■ CONTENTS v ■Chapter 3: Transactions and Consistency ............................................................. 25 Conflict Resolution ........................................................................................................... 25 Transactions and Undo .................................................................................................... 26 Start and End of Transaction .................................................................................................................. 28 The Transaction Table ............................................................................................................................ 29 Reviewing the Undo Block ...................................................................................................................... 32 Data Block Visits and Undo .............................................................................................. 35 Setting the Scene ................................................................................................................................... 35 The Interested Transaction List .............................................................................................................. 36 Concurrent Action ................................................................................................................................... 39 Commit SCN ..................................................................................................................... 45 Commit Cleanout .................................................................................................................................... 46 Delayed Block Cleanout .......................................................................................................................... 48 Transaction Table Rollback .................................................................................................................... 51 LOBs ................................................................................................................................ 57 Summary ......................................................................................................................... 57 ■Chapter 4: Locks and Latches ............................................................................... 59 First Things, First . . . ....................................................................................................... 59 Arrays ..................................................................................................................................................... 59 Pointers .................................................................................................................................................. 60 Linked Lists ............................................................................................................................................ 60 Hash Tables ............................................................................................................................................ 62 Latches ............................................................................................................................ 66 Logic Behind Latches ............................................................................................................................. 66 Latch Activity Statistics .......................................................................................................................... 69 Latch Scalability ..................................................................................................................................... 75 Locks ............................................................................................................................... 77 Infrastructure .......................................................................................................................................... 77 ■ CONTENTS vi A Graphic Image of v$lock ...................................................................................................................... 78 Deadlock ................................................................................................................................................. 82 Lock Modes ............................................................................................................................................ 85 Latches for Locks ................................................................................................................................... 86 KGL Locks (and Pins) .............................................................................................................................. 88 Locks and Pins ....................................................................................................................................... 89 Summary ......................................................................................................................... 91 ■Chapter 5: Caches and Copies ............................................................................... 93 Memory Management ...................................................................................................... 93 Granules ................................................................................................................................................. 94 Granules and Buffers .............................................................................................................................. 95 Multiple Data Caches ....................................................................................................... 97 Granules and Buffer Pools ...................................................................................................................... 99 Buffer Pools .......................................................................................................................................... 100 Working Data Sets ......................................................................................................... 101 The LRU/TCH Algorithm ................................................................................................. 103 LRU/TCH in action ................................................................................................................................. 104 Relinking a Buffer ................................................................................................................................. 106 REPL_AUX ...................................................................................................................... 107 Finding Data ................................................................................................................... 109 Pinning Buffers ..................................................................................................................................... 112 Logical I/O ............................................................................................................................................. 114 Updates ................................................................................................................................................ 115 Loading a Hash Chain ........................................................................................................................... 115 Read-Consistent Copies ....................................................................................................................... 116 Physical I/O ........................................................................................................................................... 117 Tablescans ........................................................................................................................................... 117 Summary ....................................................................................................................... 119 ■ CONTENTS vii ■Chapter 6: Writing and Recovery . ....................................................................... 121 Targets . ......................................................................................................................... 121 Log Writer . .................................................................................................................... 122 Log Writer Writes . ................................................................................................................................ 125 PL/SQL Optimization . ........................................................................................................................... 126 ACID Anomaly . ..................................................................................................................................... 129 Commit Enhancements. ....................................................................................................................... 130 Mechanics . .......................................................................................................................................... 131 Redo Wastage . ..................................................................................................................................... 134 Private Redo . ....................................................................................................................................... 136 Database Writer . ........................................................................................................... 137 Buffer Headers. .................................................................................................................................... 137 Checkpoint Queues . ............................................................................................................................. 138 Incremental Checkpointing . ................................................................................................................. 140 Database Writer Interactions . ....................................................................................... 142 Database Writer Meets Log Writer. ...................................................................................................... 142 Database Writer and LRU . .................................................................................................................... 143 Checkpoints and Queues . .................................................................................................................... 145 Buffer Header Queues . ........................................................................................................................ 150 Checkpoints and Log Files . .................................................................................................................. 151 Recovery ........................................................................................................................ 152 Media Recovery . .................................................................................................................................. 154 Standby Databases . ............................................................................................................................. 154 Flashback Database . ........................................................................................................................... 155 Side Effects . ........................................................................................................................................ 156 Summary ....................................................................................................................... 156 ■ CONTENTS viii ■Chapter 7: Parsing and Optimizing ...................................................................... 159 Understanding SQL ........................................................................................................ 159 Parsing ................................................................................................................................................. 160 Optimizing ............................................................................................................................................ 160 Interpreting the tkprof Summaries ....................................................................................................... 161 The Dictionary Cache ..................................................................................................... 164 The Structure ........................................................................................................................................ 167 Dictionary Cache Activity ...................................................................................................................... 170 What Is a Parse Call? ..................................................................................................... 173 Cursor Caching ..................................................................................................................................... 174 Holding Cursors .................................................................................................................................... 176 The Library Cache .......................................................................................................... 178 Shared Pool Structures ......................................................................................................................... 181 The Fine Structure of the Shared Pool .................................................................................................. 184 … and Action! ...................................................................................................................................... 190 Parsing and Optimizing .................................................................................................. 192 Executing, Locking, and Pinning .......................................................................................................... 194 Mutexes ................................................................................................................................................ 195 Summary ....................................................................................................................... 196 ■Chapter 8: RAC and Ruin ..................................................................................... 199 The Big Picture .............................................................................................................. 200 Staying Alive ......................................................................................................................................... 202 What’s the Point? ........................................................................................................... 203 High Availability .................................................................................................................................... 203 Scalability ............................................................................................................................................. 204 The Grid ................................................................................................................................................ 205 ■ CONTENTS ix How Can It Work? .......................................................................................................... 207 The GRD ................................................................................................................................................ 207 Masters and Shadows .......................................................................................................................... 210 GCS and GES ......................................................................................................................................... 213 Cache Fusion ........................................................................................................................................ 215 Consequences ...................................................................................................................................... 218 Recovery ........................................................................................................................ 220 Sequences ..................................................................................................................... 222 Caching Sequences .............................................................................................................................. 223 Sequence Internals ............................................................................................................................... 223 Ordered Sequences .............................................................................................................................. 225 Sequences and Indexes ........................................................................................................................ 227 Summary ....................................................................................................................... 230 ■Appendix: Dumping and Debugging .................................................................... 231 oradebug ........................................................................................................................ 231 Suspending Processes ......................................................................................................................... 231 Dumps .................................................................................................................................................. 232 Peeking at Memory ............................................................................................................................... 235 Dumps from SQL ............................................................................................................ 238 oradebug Alternatives .......................................................................................................................... 238 Data File Blocks .................................................................................................................................... 239 Log Files ............................................................................................................................................... 240 Guesswork ..................................................................................................................... 243