Technical Consultant Training ORACLE Week 4
Contents
Course Overview ....................................................................1
Target Group ..........................................................................3
Course Prerequisites ...............................................................4
Course Goals ...........................................................................5
Course Composition .................................................................6
TABCUO - Sections ....................................................................7
TABCNS - Sections .....................................................................8
TABCNO - Sections .....................................................................9
Section: Database Administration - ORACLE ...............................11
Database Overview ......................................................................13
Database Overview ......................................................................15
Review: Oracle Overview .............................................................16
Security: Operating System and Database Users ..........................17
Security: SAPR3 Password ...........................................................18
NET8 Basics ...................................................................................19
Database Administration Tools ......................................................20
Database Performance Monitoring (ST04) .....................................21
Database Space Monitoring (DB02) ................................................22
DBA Operations Monitor (DB24) ......................................................23
DBA Alert Monitor (RZ20) ...............................................................24
SAPDBA (OS Level) ........................................................................25
Oracle - Database Architecture ........................................................26
Oracle - Starting and Stopping the Database ...................................27
Oracle - Writing Data and Log Files ..................................................28
Oracle - Storage Management Concepts ...........................................29
Oracle - R/3 Naming Conventions ....................................................30
Oracle - Oracle Directory Structure in R/3 ............................................31
Oracle - Oracle Directories/Environment Variables ..................................32
Oracle - Database Roles and Users .........................................................33
Oracle - Net8 Listener ..............................................................................34
Oracle - Alert Monitoring Tree ...............................................................35
Unit Summary ............................................................................................36
Unit Actions ...............................................................................................37
Database Overview: Exercises ................................................................39
Database Overview: Solutions ................................................................41
Backup Strategy and Tape Management ...................................................43
Backup Strategy and Tape Management ................................................45
The Importance of Database Backups ....................................................46
Preventing and Handling Errors .............................................................47
Possible Causes of Data Loss (1) ............................................................48
Possible Causes of Data Loss (2) ............................................................49
Possible Causes of Data Loss (3) ............................................................50
Backup Cycle Recommendations ...........................................................51
SAP Database Backup Tools ...................................................................52
Backup Objects ..........................................................................................53
Tape Pools ..................................................................................................54
Tape Initialization .....................................................................................55
Tape Label Contents and Tape Checks ..................................................56
Tape Locking .............................................................................................57
Scenario 1: Automatic Tape Selection ...................................................58
Scenario 2: Manual Tape Selection ........................................................59
Scenario 3: Tape Selection by an External Tool ...................................60
Tape Layout ...............................................................................................61
Unit Summary ............................................................................................62
Unit Actions ...............................................................................................63
Backup Strategy and Tape Management: Exercises ................................65
Backup Strategy and Tape Management: Solutions ..............................67
Scheduling, Performing, and Monitoring Backups .................................69
Scheduling, Performing, and Monitoring Backups...............................71
How SAP Backup Tools Work Together ...............................................72
Backup Profile Parameters ......................................................................73
Profile init.sap Parameter tape_size ............................................74
Hardware Compression ............................................................................75
Scheduling and Performing a Normal Database Backup ..........................76
Phases of a Whole Database Backup ......................................................77
Logical Verification of a Database Backup ...........................................78
Physical Verification of Offline Database Backups...............................79
Monitoring a Database Backup ...............................................................80
Offline Redo Log Files: Status and Option -cds ...................................81
Performing Offline Redo Log File Backups ..........................................82
Monitoring Offline Redo Log File Backups ..........................................83
Log File Cleanup .......................................................................................84
Freespace Problems in Directory saparch ..............................................85
Unit Summary ............................................................................................86
Further Documentation ............................................................................87
Unit Actions ...............................................................................................89
Performing Backups: Exercises ..............................................................91
Performing Backups: Solutions ..............................................................93
Restore and Recovery ...................................................................................97
Restore and Recovery ...............................................................................99
Database Errors ...................................................................................... 100
Scenarios: Introduction ......................................................................... 101
Scenario: Partial Restore and Complete Recovery................................ 102
Scenario: Database Reset ...................................................................... 103
Scenario: Point in Time Recovery ....................................................... 104
How to Handle Problems ...................................................................... 105
Partial Restore and Complete Recovery (1) ....................................... 106
Partial Restore and Complete Recovery (2) ....................................... 107
Partial Restore and Complete Recovery Limitations........................... 108
Database Reset Using a Full Offline Backup ..................................... 109
Database Reset Using a Consistent Online Backup ........................ 110
Full Restore and Recovery (1) ............................................................. 111
Full Restore and Recovery (2) ............................................................. 112
Unit Summary ......................................................................................... 113
Further Documentation ......................................................................... 114
Unit Actions ............................................................................................ 115
Restore and Recovery: Exercises ......................................................... 117
Restore and Recovery: Solutions ......................................................... 119
Backup Strategies Using RMAN ............................................................. 121
Backup Strategies Using RMAN ......................................................... 123
Full Backup (Level 0) with RMAN and SAP Tools (1)........................... 124
Full Backup (Level 0) with RMAN and SAP Tools (2)............................ 125
Savesets .................................................................................................... 126
Preparation Run ...................................................................................... 127
Incremental (Level 1) Backup .............................................................. 128
Level 1 Backup: Important Considerations (1) ....................................... 129
Level 1 Backup: Important Considerations (2) ................................ 130
Recovery Using Incremental Backup with sapdba............................. 131
Unit Summary ......................................................................................... 132
Further Documentation ......................................................................... 133
Unit Actions ............................................................................................ 135
Backup Strategies Using RMAN: Exercises ...................................... 137
Backup Strategies Using RMAN: Solutions ...................................... 139
Advanced Backup Techniques ................................................................. 143
Advanced Backup Techniques ............................................................. 145
Backup Requirements and Costs .......................................................... 146
BRBACKUP and BRARCHIVE: One-Run Strategy................................. 147
Consistent Online Backups ................................................................... 148
Parallel Tape Support ............................................................................ 149
Partial Database Backups ...................................................................... 150
Backing Up Data Tablespaces Only .................................................... 151
Two-Step Disk Backup ......................................................................... 152
Structure-Retaining Database Copy ..................................................... 153
Split Mirror Disk Backups .................................................................... 154
SAP Tools and the Oracle Standby Database .................................... 156
External Backup Tools Using BC-BRI ............................................... 157
Unit Summary ......................................................................................... 158
Further Documentation ......................................................................... 159
Unit Actions ............................................................................................ 161
Advanced Backup Techniques: Exercises .......................................... 163
Advanced Backup Techniques: Solutions ........................................... 165
Storage Management ................................................................................. 167
Storage Management ............................................................................. 169
Space Management: Review ................................................................ 170
Space Management: Fragmentation Types ......................................... 171
Daily Monitoring: sapdba -check ........................................................ 172
Configuring sapdba -check ................................................................... 173
Tablespace Extension ............................................................................ 174
Storage Categories of SAP Database Objects .................................... 175
Using sapdba -next ................................................................................. 176
Daily Monitoring: Tables and Indexes ................................................ 177
Tables and Indexes: Important Reports ............................................... 178
Analyzing Internal Fragmentation ....................................................... 179
Reorganization: Basics .......................................................................... 180
Reorganization: Reasons ....................................................................... 181
Reorganization: Phases and Types ....................................................... 182
Reorganization: Methods ...................................................................... 183
Reorganization: Options ....................................................................... 184
Unit Summary ......................................................................................... 185
Unit Actions ............................................................................................ 187
Storage Management: Exercises .......................................................... 189
Storage Management: Solutions .......................................................... 191
Top 10 Problems ......................................................................................... 193
Top 10 Problems .................................................................................... 195
Troubleshooting Steps ........................................................................... 196
Top 10 Problems .................................................................................... 197
Archiver Stuck Situation ....................................................................... 198
Avoiding an Archiver Stuck Situation ................................................. 199
Incorrect Tape Size (Hardware Comp. Tape Drives) .................................... 200
Missing .....................................................................................................201
Tablespace Overflow ............................................................................. 202
MaxExtents Limit is Reached .............................................................. 203
ORA-1555: Snapshot Too Old ............................................................. 204
Net8 TCP/IP Delay ................................................................................ 205
ORA-1578: Data Block Corruption ..................................................... 206
ORA-600: Internal Database Error ...................................................... 207
Influence of the Cost-Based Optimizer ............................................... 208
Unit Summary ......................................................................................... 209
Section: SQL Cache Analysis - CBO - ORACLE ...................................... 211
Introduction and Technical Background ................................................. 213
Unit: Introduction and Technical Background ................................... 215
Introduction ............................................................................................. 216
Overview ................................................................................................. 217
ORACLE Architecture Review ............................................................ 218
ORACLE Architecture Review ............................................................ 219
Shared SQL Area ................................................................................... 220
How Oracle Processes an SQL Statement .......................................... 221
Summary .................................................................................................. 222
Introduction to the Shared SQL Area ....................................................... 223
Unit: Introduction to the Shared SQL Area ........................................ 225
Definitions ............................................................................................... 226
Expensive SELECT Statements ........................................................... 227
Data Buffer Hit Rate .............................................................................. 228
Examine the Data Buffer Hit Rate ....................................................... 229
Shared SQL Area ................................................................................... 230
Shared SQL Area ................................................................................... 231
Buffer Gets for an SQL Statement ....................................................... 232
Different Statements in the Shared SQL Area ................................... 233
Summary .................................................................................................. 234
Analyzing SQL Statements ....................................................................... 235
Unit: Analyzing SQL Statements ......................................................... 237
SQL Statements ...................................................................................... 238
Expensive SQL Statements .................................................................. 239
Expensive SQL Statements .................................................................. 240
Expensive SQL Statements .................................................................. 241
Expensive SQL Statements .................................................................. 242
Analyzing the Shared SQL Area .......................................................... 243
Buffer Gets .............................................................................................. 244
Buffer Gets per Execution .................................................................... 245
Buffer Gets per Record ......................................................................... 246
Records per Execution ........................................................................... 247
Disk Reads ............................................................................................... 248
Statement Details .................................................................................... 249
Display the Execution Plan for an SQL Statement .............................. 250
The ABAP Dictionary (Transaction SE12) ........................................ 251
Summary .................................................................................................. 252
Update Statistics ......................................................................................... 253
Unit: Update Statistics ........................................................................... 255
Update of Optimizer Statistics ............................................................. 256
Overview of the two-phase strategy .................................................... 257
Support of two-phase strategy by CCMS ........................................... 258
Table Statistics Date .............................................................................. 259
Table Statistics Date .............................................................................. 260
Accuracy of statistics ............................................................................. 261
Accuracy of Table Statistics ................................................................. 262
Accuracy of Table Statistics ................................................................. 263
Control Table DBSTATC ..................................................................... 264
Change the Optimization Mode ........................................................... 265
Change the Optimization Mode ........................................................... 266
Preferential Order of Possible Optimizations ..................................... 267
Identify Coding ........................................................................................... 269
Unit: Identify Coding ............................................................................ 271
Roadmap for Finding SQL Statements in Programs ............................. 272
Where-Used List ..................................................................................... 273
Find the Program Developer ................................................................ 274
Statements not Contained in the Where-used List ........................................... 275
Using the Global Work Process Monitor ............................................ 276
Using the Oracle Session Monitor ...................................................... 277
Differences in ABAP Open SQL and SQL Statements .................................. 278
Statements using Internal Tables ......................................................... 279
Internal Table Handling: FOR ALL ENTRIES ............................................ 280
SQL Statements to Project Views ........................................................ 281
Find the Application Area for a Statement ......................................... 282
Summary .................................................................................................. 283
Workflow and Reporting ........................................................................... 285
Unit: Workflow and Reporting ............................................................ 287
Workflow Overview .............................................................................. 288
Administrator's and Developer's Responsibilities ....................................... 289
Statement Documentation and Logging .............................................. 290
Workflow Overview .............................................................................. 291
OSS Call Template ................................................................................ 292
Workflow Overview .............................................................................. 293
Responsibilities Overview .................................................................... 294
Summary .................................................................................................. 295
Index Utilization ......................................................................................... 297
Unit: Index Utilization .......................................................................... 299
Indexes ..................................................................................................... 300
Oracle Index Structure ........................................................................... 301
Index Unique Scan ................................................................................. 302
Index Range Scan ................................................................................... 303
Order of Fields in the Index ................................................................. 304
Order of Fields in the Index ................................................................. 305
Full Table Scan ....................................................................................... 306
Unselective Index Range Scan ............................................................. 307
Important Execution Plans .................................................................... 308
Summary .................................................................................................. 309
Cost Evaluation ........................................................................................... 311
Unit: Cost Evaluation ............................................................................ 313
Database Cost Based Optimizer ........................................................... 314
Database Optimizer ................................................................................ 315
Number of Blocks read for a Full Table Scan .................................... 316
Costs for a Full Table Scan .................................................................. 317
Costs for an Index Unique Scan ........................................................... 318
Costs for an Index Range Scan ............................................................ 319
Costs for a 'FOR ALL ENTRIES' Statement ..................................... 320
Costs for Operators: Between, Like, < and > ..................................... 321
Costs for two BETWEENS .................................................................. 322
Parameter: dbs/ora/use_hints ................................................................ 323
Estimated Costs for Other Access Paths ............................................. 324
Optimizer Trace ...................................................................................... 325
Optimizer Problems ............................................................................... 326
Appendix: Table Statistics .................................................................... 327
Appendix: Table Statistics .................................................................... 328
Appendix: Index Statistics .................................................................... 329
Appendix: Index Statistics .................................................................... 330
Appendix: Database Parameters that Control Cost Calculation Functions ..................... 331
Appendix: R/3 Parameters that Control SQL Statements ................................................ 332
Creating an Index ....................................................................................... 333
Unit: Creating an Index ......................................................................... 335
Missing Indexes ...................................................................................... 336
Check table statistics ............................................................................. 337
Rules for Creating Indexes ................................................................... 338
Selectivity Analysis ............................................................................... 339
Selectivity Analysis ............................................................................... 340
Selectivity Analysis ............................................................................... 341
Selectivity Analysis ............................................................................... 342
Selectivity Analysis ............................................................................... 343
Selectivity Analysis ............................................................................... 344
Selectivity Analysis ............................................................................... 345
SQLPLUS ................................................................................................ 346
Preferential Order of Possible Optimizations ..................................... 347
Summary .................................................................................................. 348
Similar Statements ...................................................................................... 349
Unit: Similar Statements ....................................................................... 351
Similar Statements ................................................................................. 352
How to Find Expensive Similar Statements ....................................... 353
Example ................................................................................................... 354
Why Similar Statements Occur ............................................................ 355
Possible Optimizations .......................................................................... 356
Summary .................................................................................................. 357
View Processing ......................................................................................... 359
Unit: View Processing ........................................................................... 361
Views ........................................................................................................ 362
View Processing ..................................................................................... 363
View Processing ..................................................................................... 364
View Processing ..................................................................................... 365
Importance of the Table Access Order for a Nested Loop ................................ 366
Importance of the Table Access Order for a Nested Loop ................................ 367
Execution Plan for a View Statement .................................................. 368
Preferential Order of Possible Optimizations ..................................... 369
Summary .................................................................................................. 370
Joins .............................................................................................................. 371
Unit: Joins ................................................................................................ 373
SQL Statements for Joins ..................................................................... 374
Execution plan of a Join ........................................................................ 375
ABAP Statements for Joins .................................................................. 376
Preferential Order of Possible Optimizations ..................................... 377
Summary .................................................................................................. 378
Expensive Statements with a Suitable Access Path............................. 379
Unit: Suitable Access path .................................................................... 381
Expensive Statements Using a Suitable Access Path.......................... 382
Modularization in ABAP ...................................................................... 383
Driven SELECT Encapsulated in a Subroutine (FORM).............................. 384
Navigation in ABAP Coding: Where-Used/Defined .................................. 385
Driven SELECT Encapsulated in Function Module .................................. 386
Why It May Be Difficult to Find the Driver ....................................... 387
Where Resolving Nested SELECTs is not Appropriate.............................. 388
Case Study of a Nested Select .............................................................. 389
Case Study of a Nested Select .............................................................. 390
ABAP Coding from Customer ............................................................. 391
Recommended Coding .......................................................................... 392
Statement Performance After Tuning .................................................. 393
Preferential Order of Possible Optimizations ..................................... 394
Summary .................................................................................................. 395
Appendix ...................................................................................................... 397
Exercises & Solutions - SQL Cache Analysis for Oracle ........................... 399
Expensive Statements List – open problems ...................................... 402
SQL Cache Analysis for Oracle - OSS Call Template ......................... 405
Section: Performance Monitoring ................................................................ 407
Performance Monitoring ........................................................................... 409
Performance Monitoring ....................................................................... 411
Database Related Performance Issues ................................................. 412
Cost-Based Optimizer ........................................................................... 413
Oracle Cost-Based Optimizer: Review ............................................... 414
Cost-Based Optimizer Performance Problems ................................... 415
Refreshing the Object Statistics: Phase 1 ............................................ 416
Refreshing the Object Statistics: Phase 2 ............................................ 417
SAP Two-Phase Strategy ...................................................................... 418
Modifying the Standard Procedure ...................................................... 419
Using R/3 to Monitor Performance Problems .................................... 420
Memory Configuration .......................................................................... 421
Data Buffer Utilization .......................................................................... 422
Identifying the Data Buffer Hit Ratio .................................................. 423
Increasing the Size of the Data Buffer ................................................. 424
Identifying Usage of the Shared Pool .................................................. 425
Identifying the Efficiency of the Shared Pool .................................... 426
Increasing the Size of the Shared Pool ................................................ 427
Application Design ................................................................................ 428
When a Lockwait Situation Occurs ...................................................... 429
Using the Exclusive Lockwait Monitor ............................................... 430
Reducing Exclusive Lockwaits ............................................................ 431
Identifying Expensive SQL Statements (1) ........................................ 432
Identifying Expensive SQL Statements (2) ........................................ 433
Running an Explain Plan ...................................................................... 434
Poorly Qualified SQL Statements ........................................................ 435
Analyzing Poorly Qualified SQL Statements .................................... 436
Physical and Logical Layout ................................................................ 437
I/O Contention ........................................................................................ 438
Identifying I/O Contention in the Database ........................................ 439
Solving the I/O Contention Problem .................................................... 440
Checkpoint not Complete ..................................................................... 441
Rollback Segments ................................................................................. 442
Solving Rollback Segment Problems .................................................. 443
Fragmented Indexes ............................................................................... 444
Identifying a Fragmented Index ........................................................... 445
Unit Summary ......................................................................................... 446
Further Documentation ......................................................................... 447
Download attached file: You must be Loged in to download file