《深入浅出Oracle》修订稿第五、六章目录

第4章  内存管理..........................................2

4.1 PGA管理..............................................2

4.2.1 什么是PGA..........................................2

4.2.2 UGA与CGA...........................................4

4.2.3 PGA管理技术的变迁..................................6

4.2.4 参数的设置与内存分配...............................7

4.2.5 自动PGA管理实现原理...............................10

4.2.6 PGA的调整建议.....................................15

4.2.7 PGA的转储.........................................19

4.2 SGA管理.............................................21

4.1.1 SGA的组成.........................................22

1.  固定区域 - Fixed Area...............................22

1. Buffer Cache.........................................25

2.Shared Pool...........................................28

3. Redo Log Buffer......................................28

4. 其他内存组件.........................................28

4.1.2 SGA与共享内存.....................................31

4.1.3 SGA管理的变迁.....................................36

1.  Oracle8i中静态SGA的管理.............................36

2.  Oracle9i动态SGA管理.................................36

3.  Oracle10g自动共享内存管理...........................43

4.  Oracle11g自动内存管理...............................47

4.3 Oracle的内存分配和使用..............................52

4.3.1 诊断案例一-SGA与Swap..............................52

1.  登陆数据库，检查系统进程............................53

2.  检查警报日志文件....................................53

3.  尝试连接数据库......................................54

4.  检查系统日志........................................54

5.  检查系统内存及交换区使用............................54

6.  检查数据库的SGA设置.................................55

7.  调整内容............................................55

7.  问题总结:...........................................56

4.3.2 诊断案例二-SGA设置过高导致的系统故障..............56

1.  登陆数据库，检查警告日志文件........................56

2.  检查共享内存设置....................................57

3.  检查SGA设置.........................................58

4.  交换区问题..........................................58

5.  解决方案............................................59

6.  系统调整后状态......................................59

7.  一点总结............................................60

8.  后续研究............................................60

4.3.3 诊断案例三-如何诊断和解决CPU高度消耗(100%)问题....61

1. 通过Top命令查看......................................61

2. 找到存在问题的进程信息...............................62

3. 捕获存在问题的SQL语句................................62

4. 连接数据库,找到问题sql及进程.........................62

5. 进一步的跟踪.........................................63



第5章  Buffer Cache与Shared Pool原理..............................1

5.1  Buffer Cache原理.............................................2

5.1.1  LRU与Dirty List............................................2

5.1.2  Cache Buffers Lru Chain闩锁竞争与解决......................8

5.1.3  Cache Buffer Chain闩锁竞争与解决..........................10

5.2  Shared Pool的基本原理.......................................24

5.2.1  Oracle11g新特性-Result Cache..............................25

5.2.1  Shared pool的设置说明.....................................30

5.2.1  Oracle9i子缓冲池的增强....................................36

5.2.1  Oracle10g共享池管理的增强.................................39

5.2.2  了解X$KSMSP视图...........................................41

5.2.2  Shared Pool的转储与分析...................................45

5.2.3 诊断和解决Ora-04031错误....................................60

5.2.3.1 什么是Ora-04031错误......................................60

5.2.3.2 关注相关Bug信息..........................................61

5.2.3.3 绑定变量和cursor_sharing.................................63

5.2.3.4使用Flush Shared Pool缓解共享池问题.......................66

5.2.3.5 SHARED_POOL_RESERVED_SIZE参数的设置及作用................67

5.2.3.6 其他.....................................................68

5.2.3.7 模拟Ora-04031错误........................................69

5.2.4 Library Cache pin及Library Cache Lock分析..................72

5.2.4.1 LIBRARY CACHE PIN等待事件................................73

5.2.4.2. LIBRARY CACHE LOCK等待事件..............................79

5.2.4.2. Oracle10g的增强.........................................80

5.2.5诊断案例一-version_count过高造成的Latch竞争解决.............81

5.2.6 V$SQL与V$SQLAREA视图.......................................89

5.2.4.1 V$SQL视图与V$SQLAREA视图.................................89

5.2.4.1 v$sql视图和v$sqlarea的构建...............................92

5.2.6 Oracle10g中version_count过高的诊断.........................94

5.2.6诊断案例二-临时表引发的竞争.................................99

5.2.7总结.......................................................102