Secrets of the Oracle Database

Prelims
......Page 1
Contents at a Glance......Page 6
Contents......Page 9
Foreword......Page 21
About the Author......Page 23
About the Foreword Writer......Page 25
Acknowledgments......Page 27
Introduction......Page 29
ORACLE Database Server Releases......Page 30
Organization of This Book......Page 31
Conventions and Terms......Page 33
Database vs. Instance......Page 34
Typographical Conventions......Page 35
Send Us Your Comments......Page 37
AUDIT_SYSLOG_LEVEL......Page 41
Introduction to Auditing......Page 42
Auditing Non-Privileged Users......Page 44
Introduction to Automatic PGA Memory Management......Page 46
Misconceptions About PGA_AGGREGATE_TARGET......Page 48
Creating a Large Table with a Pipelined Table Function......Page 49
V$SQL_WORKAREA_ACTIVE......Page 50
_PGA_MAX_SIZE......Page 54
_SMM_MAX_SIZE......Page 55
Parallel Execution......Page 56
Lessons Learned......Page 58
Syntax......Page 59
OS_AUTHENT_PREFIX......Page 60
Case Study......Page 61
Lessons Learned......Page 64
Source Code Depot......Page 65
Hidden Initialization Parameters......Page 67
Trace File Permissions and _TRACE_FILES_PUBLIC......Page 68
ASM Test Environment and _ASM_ALLOW_ONLY_RAW_DISKS......Page 69
ASM Hidden Parameters......Page 70
Setting Up Oracle Clusterware for ASM......Page 71
ASM Instance Setup......Page 72
Source Code Depot......Page 75
Introduction to Data Dictionary Base Tables......Page 79
Large Objects and PCTVERSION vs. RETENTION......Page 80
Schema Restriction......Page 83
Examples......Page 85
Enabling Index Monitoring on Schema HR......Page 86
Index Rebuild......Page 87
Indexes Used by DML......Page 88
Lessons Learned......Page 90
Source Code Depot......Page 91
Deadlocks......Page 95
Event 10027......Page 96
Event 10046 and Extended SQL Trace......Page 99
Event 10053 and the Cost Based Optimizer......Page 101
Trace File Contents......Page 104
Case Study......Page 105
Query Transformations Considered......Page 106
Legend......Page 108
Optimizer Parameters......Page 109
System Statistics......Page 114
Object Statistics for Tables and Indexes......Page 115
Single Table Access Path and Cost......Page 117
Dynamic Sampling......Page 118
Join Orders......Page 119
Execution Plan......Page 122
Hints and Query Block Names......Page 123
Source Code Depot......Page 124
Case Study......Page 125
X$ Fixed Tables and C Programming......Page 131
Layered Architecture......Page 132
Granting Access to X$ Tables and V$ Views......Page 134
Drilling Down from V$PARAMETER to the Underlying X$ Tables......Page 135
Relationships Between X$ Tables and V$ Views......Page 140
Source Code Depot......Page 142
X$BH and Latch Contention......Page 143
Source Code Depot......Page 149
Drilling Down from V$SESSION_WAIT......Page 151
An Improved View......Page 152
Source Code Depot......Page 156
X$KFFXP......Page 157
Salvaging an SPFILE......Page 158
Mapping Segments to ASM Storage......Page 160
Tracing Your Own Session......Page 167
ALTER SESSION SET EVENTS......Page 168
ALTER SYSTEM SET EVENTS......Page 169
ALTER SESSION/SYSTEM SET EVENTS and Diagnostic Dumps......Page 170
Immediate Dumps......Page 171
Privilege User vs. Schema User......Page 173
Creating Database Objects in a Foreign Schema......Page 175
Advanced Queuing......Page 176
Private Database Links......Page 177
Stored Outlines......Page 178
The Password Game......Page 181
Locking Accounts with ALTER USER IDENTIFIED BY VALUES......Page 183
ALTER USER and Unencrypted Passwords......Page 184
Advanced Queuing......Page 187
Contention and SELECT FOR UPDATE SKIP LOCKED......Page 189
DBMS_LOCK—A Digression......Page 197
Source Code Depot......Page 200
Recovery Manager......Page 203
Disaster Recovery Case Study with Tivoli Data Protection for Oracle......Page 208
Source Code Depot......Page 210
BROKEN Procedure......Page 211
Examples......Page 212
Examples......Page 213
Examples......Page 214
Usage Notes......Page 215
Examples......Page 216
Source Code Depot......Page 217
Running External Jobs with the Database Scheduler......Page 219
Exit Code Handling......Page 220
Standard Error Output......Page 221
External Jobs on UNIX......Page 223
Removal of Environment Variables......Page 224
Command Line Processing......Page 226
External Jobs and Non-Privileged Users......Page 228
External Jobs on Windows......Page 229
Command Line Argument Handling......Page 230
External Jobs and Non-Privileged Users......Page 231
OracleJobScheduler Service......Page 232
Source Code Depot......Page 233
Parameters......Page 235
Examples......Page 236
Examples......Page 238
Parameters......Page 239
Usage Notes......Page 240
Examples......Page 241
Examples......Page 242
Examples......Page 243
SET_EV Procedure......Page 245
Examples......Page 246
WAIT_FOR_EVENT Procedure......Page 248
Examples......Page 249
Syntax......Page 251
Parameters......Page 252
Examples......Page 254
Name Resolution and Extraction of Object Statistics......Page 256
Source Code Depot......Page 258
Circumnavigating Perl DBI Pitfalls......Page 261
Setting Up the Environment for Perl and the DBI......Page 262
PERL5LIB......Page 263
Shared Library Search Path......Page 264
Windows Environment......Page 267
Transparently Running Perl Programs on UNIX Systems......Page 270
Transparently Running Perl Programs on Windows......Page 271
Connecting to an ORACLE DBMS Instance......Page 273
DBI connect Syntax......Page 274
Connecting Through the IPC Adapter......Page 275
Connecting Through the TCP/IP Adapter......Page 277
Connecting with SYSDBA or SYSOPER Privileges......Page 278
Connecting with Operating System Authentication......Page 279
RaiseError......Page 281
Comprehensive Perl DBI Example Program......Page 282
Exception Handling......Page 286
Source Code Depot......Page 287
Introduction to Instrumentation......Page 289
Case Study......Page 291
JDBC End-to-End Metrics Sample Code......Page 292
Setting Up Tracing, Statistics Collection, and the Resource Manager......Page 294
Using TRCSESS......Page 299
TRCSESS and Shared Server......Page 301
Instrumentation and the Program Call Stack......Page 304
Source Code Depot......Page 305
Introduction to Extended SQL Trace Files......Page 309
Recursive Call Depth......Page 310
Database Calls......Page 311
Parsing......Page 312
PARSING IN CURSOR Entry Format......Page 313
PARSE Entry Format......Page 314
FETCH Entry Format......Page 316
Plan Hash Value Case Study......Page 317
CLOSE Entry Format......Page 321
COMMIT and ROLLBACK......Page 322
Execution Plans, Statistics, and the STAT Entry Format......Page 323
and Oracle11......Page 324
WAIT Entry Format......Page 326
WAIT in Oracle9......Page 327
Bind Variables......Page 328
Bind in Oracle9......Page 329
and Oracle11......Page 331
Statement Tuning, Execution Plans, and Bind Variables......Page 333
Miscellaneous Trace File Entries......Page 339
Service Name Identification......Page 340
and Oracle11......Page 341
Client Identifier......Page 343
ERROR Entry Format......Page 344
Application Instrumentation and Parallel Execution Processes......Page 346
Introduction to Statspack......Page 349
Retrieving the Text of Captured SQL Statements......Page 351
Accessing STATS$SQLTEXT......Page 355
Capturing SQL Statements with Formatting Preserved......Page 361
Undocumented Statspack Report Parameters......Page 362
Statspack Tables......Page 363
Finding Expensive Statements in a Statspack Repository......Page 368
Execution Plans for Statements Captured with SQL Trace......Page 369
Finding Snapshots with High Resource Utilization......Page 372
High CPU Usage......Page 373
High DB Time......Page 375
Importing Statspack Data from Another Database......Page 378
Source Code Depot......Page 381
Retrieving Execution Plans......Page 383
Lessons Learned......Page 386
Source Code Depot......Page 387
ESQLTRCPROF Extended SQL Trace Profiler......Page 389
Categorizing Wait Events......Page 390
Calculating Response Time and Statistics......Page 391
Running the Perl Program......Page 392
Calculating Response Time......Page 395
ESQLTRCPROF Reference......Page 396
Command Line Options......Page 397
Session Level Resource Profile......Page 398
Session Level Statistics......Page 399
Statement Level Resource Profiles......Page 402
Lessons Learned......Page 407
Source Code Depot......Page 408
Introduction to the MERITS Method......Page 409
Measurement Tools......Page 410
Extended SQL Trace, AWR, and ASH......Page 411
Extended SQL Trace and Session Level Statspack Snapshot......Page 412
Assessment......Page 415
TKPROF vs. ESQLTRCPROF......Page 416
Reproduction......Page 417
Extrapolation......Page 418
MERITS Method Case Study......Page 419
Phase 2—Assessment......Page 420
Phase 3—Reproduction......Page 427
Phase 4—Improvement......Page 430
Phase 6—Installation......Page 435
Source Code Depot......Page 436
Introduction to IP Address Binding......Page 439
Multihomed Systems......Page 441
Host Name......Page 443
Boot IP Address......Page 445
Service IP Address......Page 446
IP=FIRST Enabled......Page 447
Lessons Learned......Page 448
Introduction to Valid Node Checking......Page 451
Enabling and Modifying Valid Node Checking at Runtime......Page 453
Node Failure and the TCP/IP Protocol......Page 457
Default Host Name......Page 461
Disabling the Default Listener......Page 463
Introduction to Transparent Application Failover......Page 467
ALTER SYSTEM DISCONNECT SESSION......Page 468
SELECT Failover......Page 469
Failover at the End of a Transaction......Page 473
Setting Up Services with DBMS_SERVICE......Page 475
Session Disconnection with DBMS_SERVICE and TAF......Page 477
Lessons Learned......Page 480
Source Code Depot......Page 481
Linking ORACLE Software......Page 483
Simulating Voting Disk Failure......Page 485
Removing the RAC Option with the Make Utility......Page 487
Conversion of a CRS Installation to Local-Only......Page 489
Re-enabling CRS for RAC......Page 492
Lessons Learned......Page 493
Introduction to the OERR Script......Page 497
Retrieving Undocumented Events......Page 499
Source Code Depot......Page 501
Introduction to Recovery Manager......Page 503
Introduction to DBMS_PIPE......Page 504
RMAN_PIPE_IF Package......Page 505
Using the Package RMAN_PIPE_IF......Page 506
Validating Backup Pieces......Page 513
Internode Parallel Backup and Restore......Page 514
Source Code Depot......Page 515
Introduction to ORADEBUG......Page 517
ORADEBUG Command Reference......Page 518
Attaching to a Process......Page 519
SETOSPID......Page 520
ORADEBUG IPC......Page 521
ORADEBUG SHORT_STACK......Page 523
CONTROLF......Page 524
ERRORSTACK......Page 525
HANGANALYZE......Page 527
PROCSTAT and Process-Level Operating System Statistics......Page 528
Lessons Learned......Page 529
Enabling and Disabling DBMS Options......Page 533
References......Page 535
Glossary......Page 537
Index......Page 545