Oracle Database 12cR2性能調整與優(yōu)化（第5版）

第1章  Oracle Database 12cR1和12cR2新特性介紹(針對DBA和開發(fā)人員)   1

1.1  Oracle Database 12cR1(12.1.0.1)   4

1.1.1  VARCHAR2和NVARCHAR2的大小限制增加到32K   4

1.1.2  局部索引   4

1.1.3  不可視列   5

1.1.4  相同列上的多個索引   5

1.1.5  獲取前x行   6

1.1.6  可插拔數(shù)據(jù)庫(PDB)   7

1.1.7  Oracle數(shù)據(jù)庫云服務(數(shù)據(jù)庫即服務)   9

1.1.8  PDB級別：MEMORY_LIMIT和MEMORY_MINIMUM(12cR2)   14

1.1.9  在導入時改變壓縮方式   14

1.1.10  自適應查詢優(yōu)化   15

1.1.11  PGA_AGGREGATE_LIMIT   15

1.1.12  UNION / UNION ALL的并發(fā)執(zhí)行   16

1.1.13  調用者權限函數(shù)可以做結果集緩存   16

1.1.14  新的DBMS_UTILITY.EXPAND_SQL_TEXT包   16

1.1.15  列的默認值可以基于序列   16

1.1.16  用于智能閃存的多個SSD設備   16

1.1.17  基于成本的優(yōu)化器統(tǒng)計信息的并發(fā)收集   16

1.1.18  增強的系統(tǒng)統(tǒng)計信息   17

1.1.19  用于可能失控的查詢的資源管理器   17

1.1.20  自動數(shù)據(jù)優(yōu)化(ADO)   17

1.1.21  全局索引維護：DROP和TRUNCATE分區(qū)操作   18

1.1.22  ASM磁盤清理   18

1.1.23  在線操作能力的提升   18

1.1.24  數(shù)據(jù)衛(wèi)士的改進   19

1.1.25  RMAN改進   19

1.2  Oracle Database 12cR1(12.1.0.2)   19

1.2.1  IM內存數(shù)據(jù)庫   19

1.2.2  高級索引壓縮   22

1.2.3  自動大表緩存   22

1.2.4  FDA對容器數(shù)據(jù)庫的支持   22

1.2.5  全數(shù)據(jù)庫緩存   22

1.2.6  JSON支持   22

1.2.7  FIPS 140加密參數(shù)   22

1.2.8  PDB子集克隆   22

1.2.9  快速Home目錄創(chuàng)建：創(chuàng)建“黃金映像”   22

1.3  Oracle Database 12cR2(12.2)   23

1.3.1  應用開發(fā)   23

1.3.2  降低遷移到Oracle的成本和問題的增強功能   23

1.3.3  可用性   24

1.3.4  大數(shù)據(jù)   25

1.3.5  壓縮和存檔   26

1.3.6  Oracle RAC和GI   26

1.3.7  安全   27

1.4  Oracle 12c中新的后臺進程   27

1.5  Exadata——新版本X6！   27

1.6  版本對比   28

1.7  新特性回顧   31

第2章  索引的基本原理(針對DBA和初級開發(fā)人員)   33

2.1  索引的基本概念   34

2.2  不可視索引   36

2.3  相同列上的多個索引   38

2.4  復合索引   41

2.5  索引抑制   42

2.5.1  使用不等于運算符(<>、!=)   43

2.5.2  使用IS NULL或IS NOT NULL   43

2.5.3  使用LIKE   44

2.5.4  使用函數(shù)   45

2.5.5  比較不匹配的數(shù)據(jù)類型   45

2.6  選擇性   46

2.7  聚簇因子   46

2.8  二元高度   47

2.9  使用直方圖   50

2.10  快速全掃描   51

2.11  跳躍式掃描   51

2.12  索引的類型   52

2.12.1  B樹索引   52

2.12.2  位圖索引   53

2.12.3  哈希索引   55

2.12.4  索引組織表   56

2.12.5  反向鍵索引   56

2.12.6  基于函數(shù)的索引   56

2.12.7  分區(qū)索引   57

2.12.8  12cR2中分區(qū)索引的新特性   59

2.12.9  位圖連接索引   60

2.13  快速重建索引   60

2.14  在線重建索引   60

2.15  要訣回顧   61

第3章  可插拔數(shù)據(jù)庫、磁盤實施方法和ASM(針對DBA)   63

3.1  可插拔數(shù)據(jù)庫(Oracle 12c新增)   64

3.1.1  CDB或PDB創(chuàng)建的對象   65

3.1.2  創(chuàng)建PDB的多種方法   66

3.1.3  可插拔數(shù)據(jù)庫的一些強大命令   66

3.1.4  在PDB中使用ALTER SYSTEM和其他一些命令   70

3.1.5  在可插拔數(shù)據(jù)庫中使用In-Memory(IM)   71

3.1.6  可插拔數(shù)據(jù)庫在12cR2中的其他新特性   71

3.1.7  子集備庫(12cR2新增特性)   71

3.2  磁盤陣列   72

3.2.1  使用磁盤陣列改進性能和可用性   72

3.2.2  所需的磁盤數(shù)量   72

3.2.3  可用的RAID級別   73

3.2.4  更新的RAID 5   73

3.2.5  SSD固態(tài)硬盤   74

3.2.6  ASM存儲管理(條帶/鏡像)   74

3.3  傳統(tǒng)文件系統(tǒng)的安裝和維護   75

3.3.1  考慮成本   75

3.3.2  分開存儲數(shù)據(jù)和索引文件   75

3.3.3  避免I/O磁盤爭用   76

3.4  Oracle 12c熱圖和自動數(shù)據(jù)優(yōu)化(ADO)   77

3.5  Oracle 12c I/O性能跟蹤視圖(Outliers)   78

3.6  Oracle大文件表空間   78

3.7  ASM簡介   79

3.7.1  IT部門內各個角色之間的溝通   80

3.7.2  ASM實例   80

3.7.3  ASM初始化參數(shù)   81

3.7.4  12c版本中ASM的安裝   82

3.7.5  srvctl命令增強   88

3.7.6  ASM磁盤清洗(scrubbing)   91

3.7.7  ASM再平衡增強   92

3.7.8  ASM快速鏡像再同步   94

3.7.9  ASM過濾驅動器(Filter Driver)   94

3.7.10  ASM和權限   101

3.7.11  ASM和多路徑   103

3.7.12  大文件和ASM   104

3.8  使用分區(qū)來避免磁盤爭用   104

3.8.1  獲得關于分區(qū)的更多信息   106

3.8.2  其他類型的分區(qū)   106

3.8.3  (本地)分區(qū)索引   109

3.8.4  部分索引   109

3.8.5  全局索引維護：刪除或截斷分區(qū)操作   111

3.8.6  其他分區(qū)選項   111

3.8.7  使用索引分區(qū)   113

3.8.8  導出分區(qū)   113

3.9  消除碎片(按需操作——小心使用！)   114

3.9.1  使用正確的區(qū)塊大小   114

3.9.2  正確設定PCTFREE以避免鏈化(Chaining)現(xiàn)象   115

3.9.3  使用自動段空間管理(ASSM)   115

3.10  增加日志文件尺寸和LOG_CHECKPOINT_INTERVAL以提高速度   116

3.11  確定重做日志文件的大小是否存在問題   116

3.11.1  確定日志文件的大小和檢查點的時間間隔   117

3.11.2  其他有幫助的重做日志命令   117

3.12  在不同的磁盤和控制器上存放多個控制文件   118

3.13  磁盤I/O的其他注意事項和提示   119

3.14  設計階段需要注意的問題   119

3.15  要訣回顧   120

第4章  通過初始化參數(shù)調優(yōu)數(shù)據(jù)庫(針對DBA)   123

4.1  升級到Oracle 12c之后   124

4.2  使用SEC_CASE_SENSITIVE_LOGON   125

4.3  與性能相關的重要的內存初始化參數(shù)   126

4.4  PDB級：MEMORY_LIMIT和MEMORY_MINIMUM   128

4.5  內存數(shù)據(jù)庫(INMEMORY_SIZE)   129

4.6  在不重啟數(shù)據(jù)庫的情況下修改初始化參數(shù)   133

4.7  修改PDB級別的初始化參數(shù)   136

4.8  通過Oracle實用程序洞察初始化參數(shù)   137

4.9  用企業(yè)管理器查看初始化參數(shù)   137

4.10  優(yōu)化DB_CACHE_SIZE來提高性能   138

4.11  使用V$DB_CACHE_ADVICE優(yōu)化DB_CACHE_SIZE   140

4.12  設定DB_BLOCK_SIZE來反映數(shù)據(jù)讀的大小   143

4.13  把SGA_MAX_SIZE設置為主內存大小的25%~50%   144

4.14  優(yōu)化SHARED_POOL_SIZE以獲取最佳性能   144

4.14.1  使用存儲過程來優(yōu)化共享SQL區(qū)域的使用   145

4.14.2  設定足夠大的SHARED_POOL_SIZE以保證充分利用DB_CACHE_SIZE   146

4.14.3  保證數(shù)據(jù)字典緩存能夠緩存足夠對象   146

4.14.4  保證庫緩存的重載率為0，并使命中率在95%以上   147

4.14.5  使用可用內存來判斷SHARED_POOL_SIZE是否設置正確   149

4.14.6  使用X$KSMSP表詳細觀察共享池   150

4.14.7  關于緩存大小需要記住的要點   151

4.14.8  與初始化參數(shù)相關的等待   151

4.15  在Oracle中使用多個緩沖池   152

4.15.1  與DB_CACHE_SIZE相關并為數(shù)據(jù)分配內存的池   152

4.15.2  修改LRU算法   153

4.15.3  與SHARED_POOL_SIZE相關并為語句分配內存的池   153

4.16  調整PGA_AGGREGATE_TARGET以優(yōu)化內存的使用   154

4.17  修改SGA大小以避免換頁(Paging)和交換(Swapping)   155

4.18  了解Oracle優(yōu)化器   155

4.19  創(chuàng)建足夠的調度程序(Dispatcher)   156

4.19.1  要有足夠的打開游標(OPEN_CURSORS)   157

4.19.2  不要讓DDL語句失敗(使用DDL鎖超時機制)   157

4.20  兩個重要的Exadata初始化參數(shù)(僅針對Exadata)   157

4.21  25個需要深思熟慮的重要初始化參數(shù)   157

4.21.1  歷年的初始化參數(shù)   159

4.21.2  查找未公開的初始化參數(shù)   160

4.22  理解典型的服務器   161

4.22.1  典型服務器的建模   161

4.22.2  Oracle Application數(shù)據(jù)庫選型   162

4.23  要訣回顧   166

第5章  Oracle企業(yè)管理器云控制器(針對DBA和開發(fā)人員)   169

5.1  Oracle企業(yè)管理器(EM)基礎和通過Oracle云訪問OEM   170

5.2  從All Targets和其他分組開始   173

5.3  使用OEM的Performance菜單進行監(jiān)控和優(yōu)化   175

5.3.1  Performance選項卡：Top Activity   175

5.3.2  Performance選項卡：SQL | SQL 

Performance Analyzer   176

5.3.3  Performance選項卡：Real-Time ADDM   178

5.3.4  Performance選項卡：SQL | Access Advisor   181

5.3.5  Performance選項卡：管理Optimizer Statistics   182

5.3.6  Performance選項卡：AWR | AWR Administration   182

5.3.7  Performance選項卡：ASH Analytics   184

5.4  使用OEM Administration菜單進行監(jiān)控和優(yōu)化   185

5.4.1  Database Administration選項卡：Storage | Tablespaces   185

5.4.2  Database Administration選項卡：In-Memory Central和Initialization Parameters   187

5.4.3  Database Administration選項卡：全部初始化參數(shù)   188

5.4.4  Database Administration選項卡：Resource Manager (Consumer Groups)   188

5.5  使用OEM Database或Cluster Database菜單進行監(jiān)控和優(yōu)化   189

5.5.1  Database選項卡：Job Activity   189

5.5.2  Cluster Database選項卡：Configuration | Database Topology   190

5.6  監(jiān)控主機   190

5.7  監(jiān)控應用服務器和Web應用   191

5.8  真正應用測試(數(shù)據(jù)庫回放)   193

5.9  小結   194

5.10  要訣回顧   194

第6章  使用EXPLAIN PLAN、TRACE和SQL計劃管理(針對開發(fā)人員和DBA)   195

6.1  Oracle的SQL跟蹤(SQL TRACE)實用工具   196

6.1.1  對簡單查詢使用SQL跟蹤的簡單步驟   196

6.1.2  TRACE輸出部分   200

6.1.3  深入探討TKPROF輸出   201

6.2  使用DBMS_MONITOR   203

6.3  單獨使用EXPLAIN PLAN   207

6.3.1  對簡單查詢使用的另一個EXPLAIN示例   208

6.3.2  EXPLAIN PLAN—— 自頂而下還是從下往上讀取   209

6.3.3  在開發(fā)者產品中利用跟蹤/EXPLAIN發(fā)現(xiàn)有問題的查詢   213

6.3.4  PLAN_TABLE表中的重要列   214

6.3.5  使用DBMS_XPLAN   215

6.3.6  未公開的TRACE初始化參數(shù)   216

6.4  使用STORED OUTLINES(存儲綱要)   217

6.5  使用SQL計劃管理(SPM)和SPM示例   218

6.5.1  SPM術語   218

6.5.2  使用SPM   219

6.5.3  使用固定的SQL計劃基線   223

6.5.4  從STORED OUTLINES移植到SQL計劃管理   223

6.5.5  自適應計劃(Oracle 12c新特性)和SPM   225

6.6  要訣回顧   231

第7章  基本的提示語法(針對開發(fā)人員和DBA)   235

7.1  最常用的提示   236

7.1.1  慎用提示   237

7.1.2  首先修正設計方案   237

7.2  可用提示及歸類   238

7.2.1  執(zhí)行路徑提示   238

7.2.2  訪問方法提示   238

7.2.3  查詢轉換提示   239

7.2.4  連接操作提示   239

7.2.5  并行執(zhí)行提示   239

7.2.6  其他提示   240

7.3  指定提示   240

7.4  指定多個提示   241

7.5  使用別名時，提示別名而非表名   241

7.6  提示   242

7.6.1  Oracle的演示樣板：HR方案   242

7.6.2  FIRST_ROWS提示   242

7.6.3  ALL_ROWS提示   243

7.6.4  FULL提示   243

7.6.5  INDEX提示   244

7.6.6  NO_INDEX提示   245

7.6.7  INDEX_JOIN提示   246

7.6.8  INDEX_COMBINE提示   246

7.6.9  INDEX_ASC提示   246

7.6.10  INDEX_DESC提示   247

7.6.11  INDEX_FFS提示   247

7.6.12  ORDERED提示   248

7.6.13  LEADING提示   249

7.6.14  NO_EXPAND提示   249

7.6.15  DRIVING_SITE提示   250

7.6.16  USE_MERGE提示   250

7.6.17  USE_NL提示   251

7.6.18  USE_HASH提示   252

7.6.19  QB_NAME提示   253

7.6.20  PUSH_SUBQ提示   253

7.6.21  PARALLEL提示   254

7.6.22  NO_PARALLEL提示   255

7.6.23  PARALLEL_INDEX提示   255

7.6.24  APPEND提示   255

7.6.25  NOAPPEND提示   256

7.6.26  CACHE提示   256

7.6.27  NOCACHE提示   257

7.6.28  RESULT_CACHE提示   257

7.6.29  CURSOR_SHARING_EXACT提示   258

7.6.30  INMEMORY、NO_INMEMORY及其他IM提示   258

7.6.31  USE_INVISIBLE_INDEXES提示   259

7.6.32  CONTAINERS提示   260

7.6.33  WITH_PLSQL提示   260

7.7  雜項提示及注意事項   260

7.7.1  未公開的提示   262

7.7.2  如何在視圖中使用提示   263

7.7.3  關于提示和STORED OUTLINES(或SPM)的注意事項   263

7.8  提示為什么不起作用   263

7.9  提示一覽   264

7.10  要訣回顧   265

第8章  查詢優(yōu)化(針對開發(fā)人員和初級DBA)   267

8.1  應該優(yōu)化哪些查詢？查詢V$SQLAREA和V$SQL視圖   268

8.1.1  從V$SQLAREA視圖中選出最糟糕的查詢   268

8.1.2  從V$SQL視圖中選出最糟糕的查詢   270

8.2  使用Oracle 12c視圖定位占用大量資源的會話和查詢   271

8.2.1  從V$SESSMETRIC視圖中選出當前最占用資源的會話   271

8.2.2  查看可用的AWR快照   272

8.2.3  從DBA_HIST_SQLSTAT視圖中發(fā)現(xiàn)最糟糕的查詢   272

8.3  何時應該使用索引   272

8.3.1  從DBA_HIST_SQLTEXT視圖中選擇查詢文本   273

8.3.2  從DBA_HIST_SQL_PLAN視圖中選出查詢的EXPLAIN PLAN   274

8.4  忘記了索引怎么辦   275

8.4.1  建立索引   275

8.4.2  不可視索引(Invisible Index)   276

8.4.3  查看表上的索引   276

8.4.4  在列上正確建立索引了嗎   276

8.5  建立了差的索引怎么辦   277

8.6  刪除索引時保持謹慎   279

8.7  在SELECT和WHERE子句中的列上建立索引   280

8.8  使用索引快速全掃描   281

8.9  使查詢“魔術”般加速   283

8.10  在內存中緩存表   283

8.11  使用結果集緩存(Result Cache)   285

8.12  在多個索引間選擇(使用選擇性最佳的索引)   285

8.13  索引合并   287

8.14  可能被抑制的索引   289

8.15  基于函數(shù)的索引   290

8.16  虛擬列(Virtual Column)   291

8.17  “古怪”的OR   291

8.18  使用EXISTS函數(shù)和嵌套子查詢   292

8.19  表就是視圖   293

8.20  SQL和“大一統(tǒng)”理論   293

8.21  Oracle Database 12c中的優(yōu)化變化   294

8.21.1  Oracle 12c自適應查詢優(yōu)化   294

8.21.2  自適應統(tǒng)計信息   300

8.21.3  Oracle 12c統(tǒng)計信息收集的更新和兩種新的直方圖   304

8.21.4  Oracle 12c SQL計劃管理的更新   305

8.22  Oracle自動SQL優(yōu)化   306

8.22.1  保證執(zhí)行優(yōu)化操作的用戶能訪問API   307

8.22.2  創(chuàng)建優(yōu)化任務   307

8.22.3  確定從顧問日志中可以查看到任務   307

8.22.4  執(zhí)行SQL優(yōu)化任務   307

8.22.5  查看優(yōu)化任務的狀態(tài)   308

8.22.6  顯示SQL優(yōu)化顧問生成的報告   308

8.22.7  檢查報告輸出   308

8.23  使用SQL優(yōu)化顧問自動優(yōu)化SQL語句   310

8.23.1  啟用自動SQL優(yōu)化顧問   310

8.23.2  配置自動SQL優(yōu)化顧問   310

8.23.3  查看自動SQL優(yōu)化的結果   311

8.24  使用SQL性能分析器(SPA)   314

8.25  要訣回顧   319

第9章  表連接和其他高級優(yōu)化(針對高級DBA和開發(fā)人員)   321

9.1  數(shù)據(jù)庫回放(捕獲/回放)   322

9.1.1  設置源數(shù)據(jù)庫，為數(shù)據(jù)庫回放捕獲負載   323

9.1.2  準備捕獲負載   323

9.1.3  捕獲負載   323

9.1.4  準備負載以回放   324

9.1.5  為回放處理負載   325

9.1.6  準備回放負載   325

9.1.7  執(zhí)行負載回放   325

9.2  SQL性能分析器(SPA)   327

9.2.1  創(chuàng)建SQL優(yōu)化集   327

9.2.2  創(chuàng)建分析任務   328

9.2.3  執(zhí)行分析任務   328

9.2.4  查詢性能分析的顧問任務   329

9.2.5  取消正在執(zhí)行的SPA分析任務   329

9.2.6  刪除SPA分析任務   329

9.2.7  確定活動的SQL優(yōu)化集   330

9.2.8  移除SQL優(yōu)化集引用   330

9.2.9  刪除SQL優(yōu)化集   330

9.3  連接方法   331

9.3.1  嵌套循環(huán)連接   331

9.3.2  排序合并連接   332

9.3.3  聚簇連接   333

9.3.4  哈希連接   333

9.3.5  索引合并連接   334

9.4  表連接相關的初始化參數(shù)   336

9.5  雙表連接：等尺寸表(基于成本)   337

9.6  雙表索引連接：等尺寸表(基于成本)   340

9.7  強制使用特定的連接方法   343

9.8  排除多表連接中的連接記錄(候選行)   345

9.9  大小不同的表間的雙表連接   346

9.10  三表連接：不那么有趣   349

9.11  位圖連接索引   350

9.11.1  位圖索引   351

9.11.2  位圖連接索引   352

9.11.3  位圖連接索引的最佳應用   353

9.12  第三方產品性能優(yōu)化   355

9.13  優(yōu)化分布式查詢   359

9.14  一切就緒   360

9.15  其他優(yōu)化技術   361

9.15.1  外部表   361

9.15.2  數(shù)據(jù)快照太舊(Snapshot Too Old)：開發(fā)人員的編程問題   364

9.15.3  設置事件以轉儲每次等待   365

9.15.4  從14小時到30秒—— EXISTS操作符   366

9.16  在塊級別進行優(yōu)化(高級內容)   367

9.16.1  數(shù)據(jù)塊轉儲中的關鍵部分   370

9.16.2  索引塊轉儲簡介   376

9.17  使用簡單的數(shù)學方法進行優(yōu)化   378

9.17.1  傳統(tǒng)的數(shù)學分析   378

9.17.2  七步方法論   378

9.17.3  性能推導公式   379

9.17.4  模式分析   383

9.17.5  數(shù)學方法總結   387

9.18  要訣回顧   387