Secrets of the Oracle Database (eBook)

Norbert Debes has more than 13 years experience as an Oracle database administrator. He holds a master's degree in computer science from the University of Erlangen, Germany, and is an Oracle8, Oracle8i, and Oracle9i certified professional Oracle database administrator. For over 6 years, he held different positions and technical roles at Oracle Germany. He was a team leader in Oracle Support Services, and a technical account manager in Strategic Alliances. In his last role at Oracle, Norbert was responsible for promoting Real Application Clusters on a technical level. During his tenure, he contributed to the Oracle9i SQL Reference, the Real Application Clusters manual set, and to various Real Application Clusters training materials. Right from the beginning of his quest in the Oracle database management system, Norbert always wanted to know exactly how things worked. He would not be satisfied with superficial explanations, but demanded evidence. His passion to dig deeper served him well in acquiring extensive knowledge of the Oracle database, and occasionally makes him a restless researcher who often ends up working from dusk until dawn when captured by ""the flow."" In his spare time, Norbert likes to hike, snowboard, play basketball, and read nonfiction on topics such as the emotional brain. Furthermore, he is a passionate analog and digital photographer. Having been intrigued by the vibrancy of stereoscopic (i.e., three-dimensional) capture for 20 years, he rejoices in his recent acquisition of a stereo camera.

Contents at a Glance 4
Table of Contents 7
Foreword 19
About the Author 20
About the Foreword Writer 21
Acknowledgments 22
Introduction 23
ORACLE Database Server Releases 24
Intended Audience of This Book 25
Organization of This Book 25
Source Code Depot 27
Conventions and Terms 27
Database vs. Instance 28
Instance Service Name vs. Net Service Name 29
Typographical Conventions 29
Send Us Your Comments 31
PART 1 InitializationParameters 32
CHAPTER 1 Partially Documented Parameters 33
AUDIT_SYSLOG_LEVEL 33
Syslog Facility 34
Introduction to Auditing 34
Using AUDIT_SYSLOG_LEVEL 36
Auditing Non-Privileged Users 36
Lessons Learned 38
PGA_AGGREGATE_TARGET 38
Introduction to Automatic PGA Memory Management 38
Misconceptions About PGA_AGGREGATE_TARGET 40
Researching PGA_AGGREGATE_TARGET 41
Creating a Large Table with a Pipelined Table Function 41
V$SQL_WORKAREA_ACTIVE 42
_PGA_MAX_SIZE 46
_SMM_MAX_SIZE 47
_SMM_PX_MAX_SIZE 48
Shared Server 48
Parallel Execution 48
Lessons Learned 50
EVENT 51
Syntax 51
Leveraging Events at the Instance-Level 52
Case Study 52
OS_AUTHENT_PREFIX 52
OPS$ Database Users and Password Authentication 53
Case Study 53
Lessons Learned 56
Source Code Depot 57
CHAPTER 2 Hidden Initialization Parameters 58
Trace File Permissions and_TRACE_FILES_PUBLIC 59
ASM Test Environment and_ASM_ALLOW_ONLY_RAW_DISKS 60
ASM Hidden Parameters 61
Setting Up Oracle Clusterware for ASM 62
ASM Instance Setup 63
Disk Failure Simulation 66
Source Code Depot 66
PART 2 Data Dictionary Base Tables 67
CHAPTER 3 Introduction to Data Dictionary Base Tables 68
Large Objects and PCTVERSION vs. RETENTION 69
CHAPTER 4 IND$, V$OBJECT_USAGE, and Index Monitoring 72
Schema Restriction 72
Index Usage Monitoring Case Study 74
Function MONITOR_SCHEMA_INDEXES 74
Enabling Index Monitoring on Schema HR 75
Lessons Learned 79
Source Code Depot 80
PART 3 Events 81
CHAPTER 5 Event 10027 and Deadlock Diagnosis 82
Deadlocks 82
Event 10027 83
CHAPTER 6 Event 10046 and Extended SQL Trace 86
CHAPTER 7 Event 10053 and the Cost Based Optimizer 88
Trace File Contents 91
Case Study 92
Query Blocks and Object Identifiers 93
Query Transformations Considered 93
Legend 95
Results of Bind Variable Peeking 96
Optimizer Parameters 96
System Statistics 101
Object Statistics for Tables and Indexes 102
Single Table Access Path and Cost 104
Join Orders 106
Execution Plan 109
Predicate Information 110
Hints and Query Block Names 110
Source Code Depot 111
CHAPTER 8 Event 10079 and Oracle Net Packet Contents 112
Case Study 112
PART 4 X$ Fixed Tables 115
CHAPTER 9 Introduction to X$ Fixed Tables 116
X$ Fixed Tables and C Programming 116
Layered Architecture 117
Granting Access to X$ Tables and V$ Views 119
Drilling Down from V$ Views to X$ Fixed Tables 120
Drilling Down from V$PARAMETER to the Underlying X$ Tables 120
Relationships Between X$ Tables and V$ Views 125
Source Code Depot 127
CHAPTER 10 X$BH and Latch Contention 128
Source Code Depot 134
CHAPTER 11 X$KSLED and Enhanced Session Wait Data 135
Drilling Down from V$SESSION_WAIT 135
An Improved View 136
Source Code Depot 140
CHAPTER 12 X$KFFXP and ASM Metadata 141
X$KFFXP 141
Salvaging an SPFILE 142
Mapping Segments to ASM Storage 144
PART 5 SQL Statements 148
CHAPTER 13 ALTER SESSION/SYSTEM SET EVENTS 149
Tracing Your Own Session 149
ALTER SESSION SET EVENTS 150
ALTER SYSTEM SET EVENTS 151
ALTER SESSION/SYSTEM SET EVENTS and Diagnostic Dumps 152
Immediate Dumps 153
CHAPTER 14 ALTER SESSION SET CURRENT_SCHEMA 154
Privilege User vs. Schema User 154
Creating Database Objects in a Foreign Schema 156
Restrictions of ALTER SESSION SET CURRENT_SCHEMA 157
Advanced Queuing 157
RENAME 158
Private Database Links 158
Stored Outlines 159
CHAPTER 15 ALTER USER IDENTIFIED BY VALUES 161
The Password Game 161
Locking Accounts with ALTER USER IDENTIFIED BY VALUES 163
ALTER USER and Unencrypted Passwords 164
CHAPTER 16 SELECT FOR UPDATE SKIP LOCKED 166
Advanced Queuing 166
Contention and SELECT FOR UPDATE SKIP LOCKED 168
DBMS_LOCK—A Digression 176
Source Code Depot 179
PART 6 Supplied PL/SQL Packages 180
CHAPTER 17 DBMS_BACKUP_RESTORE 181
Recovery Manager 181
Disaster Recovery Case Study with Tivoli Data Protection for Oracle 186
Source Code Depot 188
CHAPTER 18 DBMS_IJOB 189
Introduction to DBMS_JOB 189
BROKEN Procedure 189
Syntax 190
Parameters 190
Usage Notes 190
Examples 190
FULL_EXPORT Procedure 191
Syntax 191
Parameters 191
Examples 191
REMOVE Procedure 192
Syntax 192
Parameters 192
Examples 192
RUN Procedure 193
Syntax 193
Parameters 193
Usage Notes 193
Examples 194
Source Code Depot 195
CHAPTER 19 DBMS_SCHEDULER 196
Running External Jobs with the Database Scheduler 196
Exit Code Handling 197
Standard Error Output 198
External Jobs on UNIX 200
Removal of Environment Variables 201
Command Line Processing 203
External Jobs and Non-Privileged Users 205
External Jobs on Windows 206
Command Line Argument Handling 207
Windows Environment Variables 208
External Jobs and Non-Privileged Users 208
Services Created by the ORADIM Utility 209
OracleJobScheduler Service 209
Source Code Depot 210
CHAPTER 20 DBMS_SYSTEM 211
GET_ENV Procedure 211
Syntax 211
Parameters 211
Usage Notes 212
Examples 212
KCFRMS Procedure 212
Syntax 212
Usage Notes 212
Examples 212
KSDDDT Procedure 214
Syntax 214
Usage Notes 214
Examples 214
KSDFLS Procedure 215
Syntax 215
Usage Notes 215
Examples 215
KSDIND Procedure 215
Syntax 215
Parameters 215
Usage Notes 216
Examples 216
KSDWRT Procedure 216
Syntax 216
Parameters 216
Usage Notes 216
Examples 217
READ_EV Procedure 218
Syntax 218
Parameters 218
Usage Notes 218
Examples 218
SET_INT_PARAM_IN_SESSION Procedure 219
Syntax 219
Parameters 219
Usage Notes 219
Examples 219
SET_BOOL_PARAM_IN_SESSION Procedure 221
Syntax 221
Parameters 221
Usage Notes 221
Examples 221
SET_EV Procedure 221
Syntax 222
Parameters 222
SET_SQL_TRACE_IN_SESSION Procedure 224
Syntax 224
Parameters 224
Usage Notes 224
Examples 224
WAIT_FOR_EVENT Procedure 224
Syntax 225
Parameters 225
Usage Notes 225
Examples 225
CHAPTER 21 DBMS_UTILITY 227
NAME_RESOLVE Procedure 227
Syntax 227
Parameters 228
Usage Notes 230
Exceptions 230
Examples 230
Name Resolution and Extraction of Object Statistics 232
Source Code Depot 234
PART 7 Application Development 235
CHAPTER 22 Perl DBI and DBD::Oracle 236
Circumnavigating Perl DBI Pitfalls 236
A Brief History of Perl and the DBI 237
Setting Up the Environment for Perl and the DBI 237
UNIX Environment 238
Windows Environment 242
Transparently Running Perl Programs on UNIX Systems 245
Transparently Running Perl Programs on Windows 246
Connecting to an ORACLE DBMS Instance 248
DBI connect Syntax 249
Connecting Through the Bequeath Adapter 250
Connecting Through the IPC Adapter 250
Connecting Through the TCP/IP Adapter 252
Easy Connect 253
Connecting with SYSDBA or SYSOPER Privileges 253
Connecting with Operating System Authentication 254
Connect Attributes 256
Comprehensive Perl DBI Example Program 257
Exception Handling 261
Source Code Depot 262
CHAPTER 23 Application Instrumentation and End-to-End Tracing 263
Introduction to Instrumentation 263
Case Study 265
JDBC End-to-End Metrics Sample Code 266
Compiling the Program 268
Instrumentation at Work 268
Setting Up Tracing, Statistics Collection, and the Resource Manager 268
Using TRCSESS 273
TRCSESS and Shared Server 275
Instrumentation and the Program Call Stack 278
Source Code Depot 279
PART 8 Performance 280
CHAPTER 24 Extended SQL Trace File Format Reference 281
Introduction to Extended SQL Trace Files 281
SQL and PL/SQL Statements 282
Recursive Call Depth 282
Database Calls 283
Parsing 284
PARSING IN CURSOR Entry Format 285
PARSE Entry Format 286
PARSE ERROR Entry Format 288
EXEC Entry Format 288
FETCH Entry Format 288
Execution Plan Hash Value 289
Plan Hash Value Case Study 289
CLOSE Entry Format 293
COMMIT and ROLLBACK 294
UNMAP 295
Execution Plans, Statistics, and the STAT Entry Format 295
STAT Entry Format in Oracle9i 296
STAT Entry Format in Oracle10g and Oracle11g 296
Wait Events 298
WAIT Entry Format 298
WAIT in Oracle9i 299
WAIT in Oracle10g and Oracle11g 300
Bind Variables 300
BINDS Entry Format 301
Statement Tuning, Execution Plans, and Bind Variables 305
Miscellaneous Trace File Entries 311
Session Identification 312
Service Name Identification 312
Application Instrumentation 313
ERROR Entry Format 316
Application Instrumentation and Parallel Execution Processes 318
CHAPTER 25 Statspack 321
Introduction to Statspack 321
Retrieving the Text of Captured SQL Statements 323
Accessing STATS$SQLTEXT 327
Capturing SQL Statements with Formatting Preserved 333
Undocumented Statspack Report Parameters 334
Statspack Tables 335
Finding Expensive Statements in a Statspack Repository 340
Identifying Used Indexes 341
Execution Plans for Statements Captured with SQL Trace 341
Finding Snapshots with High Resource Utilization 344
High CPU Usage 345
High DB Time 347
Importing Statspack Data from Another Database 350
Source Code Depot 353
CHAPTER 26 Integrating Extended SQL Trace and AWR 354
Retrieving Execution Plans 354
Lessons Learned 357
Source Code Depot 358
CHAPTER 27 ESQLTRCPROF Extended SQL Trace Profiler 359
Categorizing Wait Events 360
Calculating Response Time and Statistics 361
Case Study 362
Running the Perl Program 362
Calculating Statistics 365
Calculating Response Time 365
ESQLTRCPROF Reference 366
Command Line Options 367
ESQLTRCPROF Report Sections 368
Lessons Learned 377
Source Code Depot 378
CHAPTER 28 The MERITS Performance Optimization Method 379
Introduction to the MERITS Method 379
Measurement 380
Measurement Tools 380
Assessment 385
Resource Profiles and Performance Assessment Tools 386
Reproduction 387
Improvement 388
Extrapolation 388
Installation 389
MERITS Method Case Study 389
Phase 1—Measurement 390
Phase 2—Assessment 390
Phase 3—Reproduction 397
Phase 4—Improvement 400
Phase 5—Extrapolation 405
Phase 6—Installation 405
Lessons Learned 406
Source Code Depot 406
PART 9 Oracle Net 407
CHAPTER 29 TNS Listener IP Address Binding and IP=FIRST 408
Introduction to IP Address Binding 408
Multihomed Systems 410
IP=FIRST Disabled 412
Host Name 412
Loopback Adapter 414
Boot IP Address 414
Service IP Address 415
IP=FIRST Enabled 416
Lessons Learned 417
CHAPTER 30 TNS Listener TCP/IP Valid Node Checking 419
Introduction to Valid Node Checking 419
Enabling and Modifying Valid Node Checking at Runtime 421
CHAPTER 31 Local Naming Parameter ENABLE=BROKEN 425
Node Failure and the TCP/IP Protocol 425
CHAPTER 32 Default Host Name in Oracle Net Configurations 429
Default Host Name 429
Disabling the Default Listener 431
PART 10 Real Application Clusters 432
CHAPTER 33 Session Disconnection, Load Rebalancing, and TAF 433
Introduction to Transparent Application Failover 433
ALTER SYSTEM DISCONNECT SESSION 434
SELECT Failover 435
Failover at the End of a Transaction 439
Session Disconnection and DBMS_SERVICE 441
Setting Up Services with DBMS_SERVICE 441
Session Disconnection with DBMS_SERVICE and TAF 443
Lessons Learned 446
Source Code Depot 447
CHAPTER 34 Removing the RAC Option Without Reinstalling 448
Linking ORACLE Software 448
Case Study 450
Simulating Voting Disk Failure 450
Removing the RAC Option with the Make Utility 452
Conversion of a CRS Installation to Local-Only 454
Re-enabling CRS for RAC 457
Lessons Learned 458
PART 11 Utilities 459
CHAPTER 35 OERR 460
Introduction to the OERR Script 460
Retrieving Undocumented Events 462
Source Code Depot 464
CHAPTER 36 Recovery Manager Pipe Interface 465
Introduction to Recovery Manager 465
Introduction to DBMS_PIPE 466
RMAN_PIPE_IF Package 467
RMAN_PIPE_IF Package Specification 468
Using the Package RMAN_PIPE_IF 468
Validating Backup Pieces 475
Internode Parallel Backup and Restore 476
Source Code Depot 477
CHAPTER 37 ORADEBUG SQL*Plus Command 478
Introduction to ORADEBUG 478
ORADEBUG Workflow 479
ORADEBUG Command Reference 479
Attaching to a Process 480
ORADEBUG IPC 482
ORADEBUG SHORT_STACK 484
Diagnostic Dumps 485
Lessons Learned 490
PART 12 Appendixes 491
APPENDIX A Enabling and Disabling DBMS Options 492
APPENDIX B Bibliography 493
References 493
APPENDIX C Glossary 495
Index 502