Oracle 數據庫存儲管理與性能優化

甘長春老師

畢業於北京交通大學電氣工程及自動化專業，目前供職於中國鐵路北京局集團公司。
自從參加工作以來，先後參與了多個鐵路應用項目的開發工作，
同時也與多家IT企業合作研發數據庫架構設計及開發項目。
通過這些項目的開發，積累了一定的實踐經驗，並從中獲取了一些數據庫開發運維的心得。
除此之外，自2014年起，甘長春老師一直在天津大學軟件學院從事兼職教學工作，
所授課程為Oracle和PHP，教學經驗的積累也讓他更清晰地明白瞭如何把一個知識點講解更清楚，
力求引領讀者盡快掌握書中所講內容。

張建軍

畢業於天津師範大學計算機科學與技術專業，
現任天津市融創軟通科技股份有限公司副總經理，
教育培訓事業部技術總監，高級工程師；Oracle認證專家，
RedHad（紅帽）認證專家，截至目前有近20年的軟件開發、
項目管理及教育培訓工作經驗。

目錄
第1章Oracle體系結構概述1
1.1 Oracle數據庫進程結構2
1.2 Oracle數據庫邏輯結構3
1.3 Oracle數據庫物理結構5
1.4 Oracle數據庫邏輯結構與物理結構的耦合關係6
1.5本章小結8

第2章SQL執行計劃9
2.1看懂SQL執行計劃前需要掌握的概念9
2.2 SQL*Plus的執行計劃設置12
2.2.1設置Autotrace（自動跟踪）參數12
2.2.2在SQL*Plus中顯示SQL執行計劃13
2.3使用TOAD、SQL Developer工具21
2.3.1 TOAD分析工具21
2.3.2 SQL Developer分析工具22
2.4 SQL解析24
2.4.1 Oracle SQL的硬解析和軟解析27
2.4.2動態分析採樣28
2.5關於RBO與CBO 28
2.6關於執行計劃中的索引訪問方法30
2.6.1索引唯一掃描（INDEX UNIQUE SCAN） 31
2.6.2索引範圍掃描（INDEX RANGE SCAN） 32
2.6.3索引跳躍掃描（INDEX SKIP SCAN） 33
2.6.4索引快速全掃描（INDEX FAST FULL SCAN） 34
2.7通過DBMS_XPLAN包查看以往SQL的執行計劃35
2.8常用Hints（提示） 36
2.8.1與優化器模式相關的Hint 37
2.8.2與表訪問相關的Hint 40
2.8.3與索引訪問相關的Hint 40
2.8.4與表連接順序相關的Hint 47
2.9實踐案例：位圖索引對性能的影響48
2.10實踐案例：分區索引對性能的影響52
2.10.1關於分區索引的說明52
2.10.2分區索引實驗54
2.11如何更好地判斷SQL效率71
2.12本章小結72

第3章Oracle數據庫存儲結構73
3.1邏輯存儲結構73
3.1.1塊（Block） 73
3.1.2區（Extent） 74
3.1.3段（Segment） 75
3.1.4表空間（Tablespace） 75
3.1.5 4種邏輯存儲結構的關係76
3.2 Oracle物理存儲結構76
3.3本章小結77

第4章Oracle表空間78
4.1表空間管理78
4.1.1表空間（TABLESPACE）的類型79
4.1.2表空間（TABLESPACE）的管理80
4.1.3表空間（TABLESPACE）的創建82
4.2表空間的查看88
4.2.1表空間固定信息的查看89
4.2 .2表空間動態信息的查看90
4.3表空間管理準則91
4.4創建表空間應遵循的一般原則92
4.5表空間創建模板及其刪除應用場景分析94
4.5.1表空間創建模板語句95
4.5.2表空間刪除的4種方式及其應用場景分析96
4.6關於表空間創建的數據文件DATAFILE參數98
4.6.1 SIZE子句99
4.6.2 EXTENT分區分配方案99
4.6.3關於REUSE（重複使用）的說明99
4.7關於表空間參數的其他說明101
1．段Segment管理策略101
2．Table/Segment/Extent/Block之間的關係101
3．TABLESPACE和DATAFILE之間的關係101
4.8回收表空間中浪費的空間101
4.8.1查看表空間碎片率102
4.8.2得到表空間的DDL（創建）語句103
4.8.3表空間屬性PCTINCREASE（百分比）參數的修改105
4.8.4回收表空間碎片105
4.8.5 Oracle移動索引到其他表空間112
4.9 TABLE的碎片回收113
4.9.1與回收TABLE碎片有關的兩個存儲過程113
4.9.2表碎片回收處理118
4.10 Oracle 11g undo_retention（撤銷保留時間） 120
4.10.1關於undo的參數121
4.10.2 undo_retention（撤銷保留時間）狀態說明及參數調整122
4.11本章小結123

第5章自動存儲管理（ASM） 125
5.1 ASM概述125
5.1.1 ASM冗餘125
5.1.2 ASM進程126
5.1.3 ASM實例和數據庫實例對應關係127
5.1.4 Cluster（集群） ASM架構127
5.2 ASM實例搭建128
5.2.1環境介紹128
5.2.2創建裸設備以及創建ASM磁盤組129
5.2.3安裝Oracle網絡基礎架構win64_11gR2_grid組件133
5.2.4後續處理134
5.3 ASM實例管理136
5.3.1查看可用分區137
5.3.2加入ASM磁盤138
5.3.3開啟CSS服務139
5.3.4新建ASM DiskGroup給數據庫使用139
5.4磁盤組的管理143
5.4.1磁盤組的創建與刪除144
5.4.2 ASM磁盤的添加和刪除147
5.4.3磁盤組信息的查詢149
5.4.4磁盤組的重新平衡150
5.4.5磁盤組的加載和卸載150
5.4.6目錄管理151
5.4.7別名管理152
5.5如何使用ASM磁盤組153
5.5.1創建數據文件153
5.5.2添加重做日誌文件157
5.5.3創建數據庫159
5.6本章小結159

第6章Oracle的內存結構160
6.1 Oracle內存結構160
6.1.1 SGA（系統全局區） 161
6.1.2 PGA（程序全局區） 163
6.1.3 UGA（用戶全局區） 165
6.2 SGA組件介紹165
6.2.1固定SGA（Fixed SGA） 166
6.2.2塊緩衝區（Database Buffer Cache） 166
6.2.3數據高速緩存的工作原理過程167
6.2.4重做日誌緩衝區（Redo Log Buffer） 168
6.2.5共享池（Shared Pool） 169
6.2.6大池（Large Pool） 170
6.2.7 Java池（ Java Pool） 170
6.2.8流池（Stream Pool） 170
6.3 PGA結構171
6.3.1 Private SQL Area（私有SQL區） 171
6.3.2 Work Area（工作區） 172
6.3.3 Session Memory 173
6.3.4自動PGA管理174
6.4 Oracle 11g系統進程介紹175
6.4.1數據庫寫進程（DBWn） 177
6.4.2日誌文件寫進程（LGWR） 178
6.4.3檢查點進程（CKPT） 179
6.4.4系統監控進程（SMON） 180
6.4.5進程監控進程（PMON） 180
6.4.6恢復進程（RECO） 180
6.4.7作業隊列進程（CJQn） 181
6.4.8歸檔進程（ARCn） 181
6.4.9隊列監控進程（QMNn） 182
6.4 .10調度進程（Dnnn） 182
6.4.11內存管理進程（MMAN） 182
6.4.12恢復寫入進程（RVWR） 182
6.4.13內存管理進程（MMON） 183
6.4.14其他後台進程183
6.5自動共享內存管理（ASMM） 183
6.6關於11g與12c內存管理185
6.6.1 Orodo內存管理形式186
6.6.2 11g下的AMM內存管理187
6.7本章小結188

第7章Oracle的內存分析與調整189
7.1 Oracle內存工作機制189
7.2內存使用情況分析190
7.2 .1剩餘內存190
7.2.2內存擊中率190
7.3 SQL效率及其他指標查看分析193
7.3.1檢查佔用CPU時間比較長的SQL語句193
7.3.2執行效率最差的SQL語句194
7.3.3識別低效率執行的語句195
7.3.4 V$sqlarea視圖提供的執行細節196
7.3.5查看數據庫db_cache_size及各類pool_size值198
7.4 Oracle內存調整——系統全局區SGA 199
7.5 Oracle內存調整——共享池（ Shared Pool） 201
7.5.1共享池（Shared Pool）相關視圖201
7.5.2共享池（Shared Pool） 207
7.5.3庫高速緩存（Library Cache） 208
7.5.4數據緩衝區（Buffer Cache） 215
7.5.5重做日誌緩衝區（Redo Log Buffer） 221
7.5.6大池222
7.5.7 Java池222
7.5.8流池223
7.6本章小結223

第8章Oracle動態性能指標224
8.1主要與Oracle動態性能指標相關的基礎概念224
8.2從v$sysstat視圖獲取負載間檔228
8.2.1 Buffer Cache Hit Ratio（DB緩存命中率） 229
8.2.2 Soft Parse Ratio（軟解析比率） 229
8.2.3 In-Memory Sort Ratio（內存排序率） 230
8.2.4 Parse To Execute Ratio（SQL解析執行比率） 230
8.2.5 Parse CPU To Total CPU Ratio（CPU花費比率） 230
8.2.6 Parse Time CPU To Parse Time Elapsed（鎖競爭比率） 230
8.3其他計算統計以衡量負載方式231
8.3.1 Blocks Changed For Each Read(每次讀引起的塊改變) 231
8.3.2 Rows For Each Sort（每個排序引發的排序行量） 232
8.3.3 Oracle獲取當前數據庫負載情況信息232
8.4本章小結234

第9章Oracle的索引與性能235
9.1 Oracle數據庫索引類型235
9.1.1 B*tree索引235
9.1.2位圖索引（Bitmap Index） 236
9.1.3位圖連接索引（Bitmap Join Index） 237
9.1. 4基於函數的索引（Function-Based Index） 237
9.1.5應用域索引（Application Domain Index） 239
9.1.6 Hash索引242
9.1.7分區索引242
9.2索引典型操作243
9.2.1典型創建操作243
9.2. 2典型刪除操作243
9.2.3典型移動操作244
9.2.4得到創建索引的SQL語句244
9.2.5查看數據庫中的索引及跳過設置246
9.2.6通用索引刪除腳本246
9.3有無索引及不同類型索引對查詢效率高低影響實驗247
9.4關於索引的建議251
9.5普通表轉分區表實驗及分區表相關信息查詢254
9.5.1普通表轉分區表實驗環境搭建254
9.5.2普通表轉分區表255
9.5.3查看Oracle都有哪些分區表258
9.5.4表分區查詢259
9.6本章小結259

第10章Oracle性能實驗260
10.1信息收集、庫加壓處理260
10.1.1信息收集處理260
10.1.2給數據庫加壓處理及瓶頸解決過程270
10.1 .3存儲過程使用綁定272
10.1.4將UPDATE命令加載到共享池並以並行方式執行273
10.1.5通過並發給數據庫加壓273
10.1.6查找SESSION ID及serial# 274
10.1.7殺掉SESSION ID 275
10.1.8通過merge命令加壓275
10.1.9批量數據加壓處理277
10.2信息查看跟踪SQL語句278
10.2.1比率相關278
10.2.2等待、鎖及阻塞相關286
10.2.3獲取SQL語句相關302
10.2.4資源消耗相關310
10.2.5游標相關313
10.3日常需要記錄的監控點以及監控語句315
10.4關於Oracle的I/O 318
10.5實驗結論319
10.5.1調優過程319
10.5.2調優前後SPFILE參數文件對比323
10.5.3實驗總結326
10.6本章小結326

第11章AWR報告327
11.1 AWR報告綜述327
11.2什麼情況下會用到AWR 332
11.3如何生成AWR報告333
11.4分析AWR報告336
11.4.1 AWR報告頭336
11.4.2 Cache Sizes報告337
11.4.3 Load Profile報告337
11.4.4 Instance Efficiency Percentages報告339
11.4.5 Shared Pool Statistics報告341
11.4.6 Top 5 Timed Foreground Events（前5個嚴重等待事件）報告341
11.4.7 SQL ordered by Elapsed Time報告347
11.4.8 SQL ordered by CPU Time報告348
11.5使用腳本自動生成AWR報告349
11.5.1查快照snap_ID 349
11.5.2建立腳本並執行350
11.6本章小結351

第12章Oracle的ADDM報告352
12.1 Oracle性能調優綜述352
12.2 Oracle ADDM報告概述353
12.2.1使用addmrpt.sql來創建ADDM報告353
12.2.2使用DBMS_ADVISOR程序包來創建ADDM報告354
12.3 ADDM報告實驗358
12.3.1負荷環境搭建358
12.3.2第1次採集快照並施加負荷359
12.3.3採集第2次快照359
12.3.4創建一個優化任務並執行360
12.3.5查詢建議結果361
12.3.6 ADDM報告解釋361
12.4本章小結365

第13章Oracle 11g R2 RAC集群部署實驗367
13.1總體規劃367
13.1.1部署環境367
13.1.2網絡配置368
13.1.3 Oracle軟件組件368
13.1.4數據庫配置369
13.1.5存儲組件369
13.2服務器規劃370
13.2.1通過StartWind 6.0虛擬磁盤370
13.2.2劃分Oracle安裝目錄DB(F:)和虛擬內存(G:) 370
13.2.3修改虛擬內存（兩個節點都設置） 371
13.2.4修改winrac1和winrac2的hosts文件372
13.2.5修改註冊表，禁用媒體感知功能372
13.3網絡規劃373
13.3.1修改網卡名（兩個節點都設置） 373
13.3.2修改網卡優先級並配置IP 374
13.3.3測試兩點的連通性375
13.4存儲規劃376
13.4.1規劃磁盤陣列376
13.4.2共享安裝目錄DB(F:)和C盤377
13.5安裝Grid軟件前的設置和檢查378
13.5.1服務器時間同步378
13.5.2檢測節點之間能否相互訪問共享378
13.5.3檢查Grid安裝是否符合條件379
13.6 Grid及數據庫軟件的安裝382
13.6.1安裝win64 grid 11.2.0.4.0集群管理軟件383
13.6.2 Clusterware安裝校驗（檢查CRS資源狀態） 387
13.6 .3安裝DATABASE軟件388
13.6.4創建ASM磁盤組391
13.6.5 DBCA建立數據庫394
13.7 Oracle RAC集群管理常用操作398
13.8本章小結401

第14章Oracle特殊問題的解決案例402
14.1 ORA-00257 archiver error 402
14.2由於恢復區空間不足導致ORA-03113錯誤404
14.3解決Oracle SYSAUX空間佔用嚴重問題405
14.3.1清理SYSAUX下的歷史統計信息406
14.3.2清理SYAUX表空間中無效的ASH（活動會話歷史）信息407
14.3.3檢查SYSAUX表空間可收縮的數據文件408
14.3.4 SYSAUX清理後的檢查409
後記411