Oracle Performance Diagnosis art (milestone book on Oracle Database optimization)

Oracle Performance Diagnosis art (milestone book on Oracle Database optimization)

[Original book name] troubleshooting Oracle Performance
[Original Publishing House] apress
[Author] (Swiss) Christian antognini [same as the author's work] [Translator's introduction]
[Translated by] Tong Jiawang; Hu Yiwen; Feng dahui [same translator's work]
[Book name] Turing programming Series
[Release news agency] People's post and telecommunications Publishing House [book no.] 9787115215147
[Mounting time]
[Publication date] May October 2009 [Opening] 16

Edit recommendations

A milestone in Oracle Database optimization.
Covers Oracle versions ..
Insights from practice...

[Content Overview]
The hot day for front-end business applications is the time to optimize the performance of backend databases. At this time, the treasure of database optimization allows you to move to the workplace and stand out from the Internet ..

This book is a milestone in the future by Christian antognini, an Oracle database Optimization Expert. The best practices and many suggestions in this book are all from the rich accumulation of the author in practice. It is not only simple and practical, but also thought-provoking. It is a "treasure house" that is suitable for readers at all levels to study and explore...

Unlike other similar books, this book not only covers a variety of common Oracle versions on the market, but also highlights the unique performance optimization features of each version. The book opens with a new perspective. It thoroughly explains the configuration of the query optimizer and the optimization of table access, connection, and physical table layout around finding out the truth and searching for effective strategies, as well as accelerating SQL Execution plans and other important theme, readers are hailed as "the most thorough, but most popular performance optimization book ". Worth it !...

[Directory]
 
Part 1 Basis
Chapter 2 Performance Question 2
1.1 do I need to plan the performance? 2
1.1.1 Requirement Analysis 2
1.1.2 Analysis and Design 4
1.1.3 coding and unit test 4
1.1.4 integration and Acceptance Test 5
1.2 What is performance problem 5
1.2.1 System Monitoring 6
1.2.2 Response Time Monitoring 6
1.2.3 force-tuning disorder 6
1.3 How to Solve performance problems 7
1.3.1 business and system 7
1.3.2 classify problems 8
1.3.3 Problem Solving 8
1.4 Conclusion 9
Chapter 1 key concepts 10
2.1 selectivity and base 10
2.2 lifecycle 11 of A cursor
2.3 parsing process 13
2.3.1 shared cursor 15
2.3.2 Bind Variable 17
2.4 read/write data block 24
2.5 Conclusion 25
Part 2 find out the problem
Chapter 2 identifying performance problems 28
3.1 divide and conquer 28
3.2 analysis roadmap 29
3.3 Performance Measurement and Analysis 32
3.4 performance measurement 32
3.4.1 application code 33
3.4.2 database call 35
3.5 profiling application code 38
3.5.1 concise performance analysis 38
3.5.2 detailed performance analysis 43
3.6 tracking database calls 46
3.6.1 SQL tracking 46
3.6.2 tracking file structure 58
3.6.3 use trcsess 60
3.6.4 profiling tool 61
3.6.5 use tkprof 62
3.6.6 use TVD $ xtat 71
3.7 Analysis of PL/SQL code 79
3.7.1 install profiling tool 80
3.7.2 install output table 80
3.7.3 collect and analyze data 81
3.7.4 Report Analysis Data 82
3.7.5 GUI method 83
3.8 summary 84
Part 3 Query Optimizer
Chapter 1 system and object statistics 86
4.1 toolkit dbms_stats introduction 86
4.2 system Statistics 87
4.2.1 data dictionary 88
4.2.2 non-workload statistics 89
4.2.3 workload statistics 90
4.2.4 impact on the query optimizer 92
4.3 object statistics 94
4.3.1 available statistics of objects 95
4.3.2 collect object statistics 108
4.3.3 locked object statistics 123
4.3.4 comparison object statistics 125
4.3.5 Delete object statistics 127
4.3.6 policy 129
4.4 general service 130
4.4.1 history of Statistics 130
4.4.2 create and delete a backup Table 131
4.4.3 Export, Import, retrieve, and set operations 132
4.4.4 log 132
4.5 conclusion 133
Chapter 2 configure the query optimizer 5th
5.1 configure or not configure 134
5.2 configure roadmap 135
5.3 set the correct parameter 136
5.3.1 query optimizer parameter 137
5.3.2 PGA management 150
5.4 conclusion 153
Chapter 4 Execution Plan 6th
6.1 get the execution plan 154
6.1.1 SQL statement explain Plan 154
6.1.2 dynamic performance 157
6.1.3 AWR and statspack 161
6.1.4 tracking 163
6.1.5 dbms_xplan package 165
6.2 explain the execution plan 175
6.2.1 Parent and Child Relationship 176
6.2.2 operation type 177
6.2.3 stand-alone operation 177
6.2.4 non-associated operations 179
6.2.5 associated operations 180
6.2.6 divide and conquer 186
6.2.7 special example 188
6.3 identify inefficient execution plans 190
6.3.1 evaluation of errors 190
6.3.2 unrecognized constraint 192
6.4 conclusion 193
Chapter 2 SQL optimization technology 7th
7.1 change access structure 195
7.1.1 Operating Mechanism 195
7.1.2 when to use 196
7.1.3 defects and errors 196
7.2 modify SQL statement 196
7.2.1 operation mechanism 196
7.2.2 when to use 198
7.2.3 defects and errors 198
7.3 tip 198
7.3.1 Operating Mechanism 198
7.3.2 when to use 204
7.3.3 defects and errors 204
7.4 change execution environment 205
7.4.1 mechanism of work 206
7.4.2 when to use 208
7.4.3 defects and errors 208
7.5 SQL summary 208
7.5.1 mechanism 209
7.5.2 when to use 219
7.5.3 defects and errors 219
7.6 storage outline 220
7.6.1 mechanism 221
7.6.2 when to use 227
7.6.3 defects and errors 227
7.7 SQL plan baseline 229
7.7.1 mechanism of work 229
7.7.2 when to use 240
7.7.3 defects and errors 240
7.8 conclusion 241
Part 4 Optimizer
Chapter 2 analysis 8th
8.1 identify resolution problems 244
8.1.1 quick resolution 245
8.1.2 long resolution 248
8.2 resolve resolution problems 250
8.2.1 quick parsing 251
8.2.2 resolution duration 256
8.3 resolve problems 256
8.3.1 share cursor 257
8.3.2 server-side statement cache 258
8.4 use the Application Programming Interface 260
8.4.1 PL/sqls 261
8.4.2 OCI 264
8.4.3 JDBC 265
8.4.4 ODP. Net 266
8.5 conclusion 268
Chapter 5 optimize data access 9th
9.1 locate the unsatisfactory access path 269
9.1.1 recognition 269
9.1.2 trap 271
9.1.3 cause 273
9.1.4 solution 274
9.2 SQL statements with weak selectivity 277
9.2.1 full table scan 277
9.2.2 full partition scan 279
9.2.3 partition range: 279
9.2.4 hash and list partition 289
9.2.5 combined partition 289
9.2.6 design considerations 291
9.2.7 full index scan 293
9.3 highly selective SQL statement 295
9.3.1 access by rowid 296
9.3.2 index access 297
9.3.3 single table hash clustering access 324
9.4 conclusion 325
Chapter 2 Table connection optimization 10th
10.1 definition 326
10.1.1 connection tree 326
10.1.2 type of connection 329
10.1.3 restrictions and connection conditions 332
10.2 nested loop connection 333
10.2.1 concept 333
10.2.2 two-table join 334
10.2.3 four-table join 335
10.2.4 block prefetch 336
10.2.5 other optional execution plan 337
10.3 merge connections 338
10.3.1 concept 338
10.3.2 two-table join 339
10.3.3 four-table join 341
10.3.4 workspace 342
10.4 hash connection 346
10.4.1 concept 346
10.4.2 two-table join 347
10.4.3 four-table join 348
10.4.4 workspace 350
10.4.5 index connection 350
10.5 external connections 350
10.6 select connection method 351
10.6.1 first_rows optimization goal: 352
10.6.2 all_rows optimization objective 352
Connection Method 352 supported by 10.6.3
10.6.4 Parallel Connection 352
10.7 partition smart connection 353
10.7.1 fully partitioned smart connection 353
10.7.2 smart partition connection 355
10.8 conversion 357
10.8.1 connection removal 357
10.8.2 external connection to internal connection 358
10.8.3 subquery non-nesting 359
10.8.4 star conversion 360
10.9 conclusion 365
Chapter 2 advanced optimization technology 11th
11.1 materialized view 366
11.1.1 Operating Mechanism 366
11.1.2 when to use 384
11.1.3 defects and errors 385
11.2 result cache 385
11.2.1 Operating Mechanism 386
11.2.2 when to use 391
11.2.3 defects and errors 391
11.3 parallel processing 392
11.3.1 Operating Mechanism 392
11.3.2 when to use 408
11.3.3 defects and errors 408
11.4 insert directly to path 412
11.4.1 Operating Mechanism 412
11.4.2 when to use 414
11.4.3 defects and errors 414
11.5 row prefetch 415
11.5.1 Operating Mechanism 415
11.5.2 when to use 419
11.5.3 defects and errors 419
11.6 array interface 419
11.6.1 Operating Mechanism 419
11.6.2 when to use 422
11.6.3 defects and errors 422
11.7 Conclusion 422
Chapter 1 Optimization of Physical Design 12th
12.1 optimal Field Sequence 424
12.2 optimal data type 426
12.2.1 trap in Data Type Selection 426
12.2.2 best practices for selecting data types 429
12.3 row migration and row link 430
12.3.1 migration and link 431
12.3.2 question 433
12.3.3 identify problem 433
12.3.4 solution 433
12.4 data block contention 434
12.4.1 question 434
12.4.2 locate problem 435
12.4.3 solution 437
12.5 Data Compression 440
Part 5 Appendix
Appendix A downloadable file 444