Debes Secrets of the Oracle Database

1. ed
ISBN: 978-1-4302-1953-8
Verlag: Apress
Format: PDF
Kopierschutz: 1 - PDF Watermark

E-Book, Englisch, 450 Seiten

Secrets of the Oracle Database is the definitive guide to undocumented and partially-documented features of the Oracle Database server. Covering useful but little-known features from Oracle Database 9 through Oracle Database 11, this book will improve your efficiency as an Oracle database administrator or developer. Norbert Debes shines the light of day on features that help you master more difficult administrative, tuning, and troubleshooting tasks than you ever thought possible. Finally, in one place, you have at your fingertips knowledge that previously had to be acquired through years of experience and word of mouth through knowing the right people. What Norbert writes is accurate, well-tested, well-illustrated by clear examples, and sure to improve your ability to make an impact on your day-to-day work with Oracle.

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.

Autoren/Hrsg.

Debes, Norbert

Inhaltsverzeichnis

1;Contents at a Glance;4
2;Table of Contents;7
3;Foreword;19
4;About the Author;20
5;About the Foreword Writer;21
6;Acknowledgments;22
7;Introduction;23
7.1;ORACLE Database Server Releases;24
7.2;Intended Audience of This Book;25
7.3;Organization of This Book;25
7.4;Source Code Depot;27
7.5;Conventions and Terms;27
7.5.1;Database vs. Instance;28
7.5.2;Instance Service Name vs. Net Service Name;29
7.5.3;Typographical Conventions;29
7.6;Send Us Your Comments;31
8;PART 1 InitializationParameters;32
8.1;CHAPTER 1 Partially Documented Parameters;33
8.1.1;AUDIT_SYSLOG_LEVEL;33
8.1.1.1;Syslog Facility;34
8.1.1.2;Introduction to Auditing;34
8.1.1.3;Using AUDIT_SYSLOG_LEVEL;36
8.1.1.4;Auditing Non-Privileged Users;36
8.1.1.5;Lessons Learned;38
8.1.2;PGA_AGGREGATE_TARGET;38
8.1.2.1;Introduction to Automatic PGA Memory Management;38
8.1.2.2;Misconceptions About PGA_AGGREGATE_TARGET;40
8.1.2.3;Researching PGA_AGGREGATE_TARGET;41
8.1.2.4;Creating a Large Table with a Pipelined Table Function;41
8.1.2.5;V$SQL_WORKAREA_ACTIVE;42
8.1.2.6;_PGA_MAX_SIZE;46
8.1.2.7;_SMM_MAX_SIZE;47
8.1.2.8;_SMM_PX_MAX_SIZE;48
8.1.2.9;Shared Server;48
8.1.2.10;Parallel Execution;48
8.1.2.11;Lessons Learned;50
8.1.3;EVENT;51
8.1.3.1;Syntax;51
8.1.3.2;Leveraging Events at the Instance-Level;52
8.1.3.3;Case Study;52
8.1.4;OS_AUTHENT_PREFIX;52
8.1.4.1;OPS$ Database Users and Password Authentication;53
8.1.4.2;Case Study;53
8.1.4.3;Lessons Learned;56
8.1.5;Source Code Depot;57
8.2;CHAPTER 2 Hidden Initialization Parameters;58
8.2.1;Trace File Permissions and_TRACE_FILES_PUBLIC;59
8.2.2;ASM Test Environment and_ASM_ALLOW_ONLY_RAW_DISKS;60
8.2.2.1;ASM Hidden Parameters;61
8.2.2.2;Setting Up Oracle Clusterware for ASM;62
8.2.2.3;ASM Instance Setup;63
8.2.2.4;Disk Failure Simulation;66
8.2.3;Source Code Depot;66
9;PART 2 Data Dictionary Base Tables;67
9.1;CHAPTER 3 Introduction to Data Dictionary Base Tables;68
9.1.1;Large Objects and PCTVERSION vs. RETENTION;69
9.2;CHAPTER 4 IND$, V$OBJECT_USAGE, and Index Monitoring;72
9.2.1;Schema Restriction;72
9.2.2;Index Usage Monitoring Case Study;74
9.2.2.1;Function MONITOR_SCHEMA_INDEXES;74
9.2.2.2;Enabling Index Monitoring on Schema HR;75
9.2.2.3;Lessons Learned;79
9.2.2.4;Source Code Depot;80
10;PART 3 Events;81
10.1;CHAPTER 5 Event 10027 and Deadlock Diagnosis;82
10.1.1;Deadlocks;82
10.1.2;Event 10027;83
10.2;CHAPTER 6 Event 10046 and Extended SQL Trace;86
10.3;CHAPTER 7 Event 10053 and the Cost Based Optimizer;88
10.3.1;Trace File Contents;91
10.3.2;Case Study;92
10.3.2.1;Query Blocks and Object Identifiers;93
10.3.2.2;Query Transformations Considered;93
10.3.2.3;Legend;95
10.3.2.4;Results of Bind Variable Peeking;96
10.3.2.5;Optimizer Parameters;96
10.3.2.6;System Statistics;101
10.3.2.7;Object Statistics for Tables and Indexes;102
10.3.2.8;Single Table Access Path and Cost;104
10.3.2.9;Join Orders;106
10.3.2.10;Execution Plan;109
10.3.2.11;Predicate Information;110
10.3.2.12;Hints and Query Block Names;110
10.3.3;Source Code Depot;111
10.4;CHAPTER 8 Event 10079 and Oracle Net Packet Contents;112
10.4.1;Case Study;112
11;PART 4 X$ Fixed Tables;115
11.1;CHAPTER 9 Introduction to X$ Fixed Tables;116
11.1.1;X$ Fixed Tables and C Programming;116
11.1.2;Layered Architecture;117
11.1.3;Granting Access to X$ Tables and V$ Views;119
11.1.4;Drilling Down from V$ Views to X$ Fixed Tables;120
11.1.4.1;Drilling Down from V$PARAMETER to the Underlying X$ Tables;120
11.1.5;Relationships Between X$ Tables and V$ Views;125
11.1.6;Source Code Depot;127
11.2;CHAPTER 10 X$BH and Latch Contention;128
11.2.1;Source Code Depot;134
11.3;CHAPTER 11 X$KSLED and Enhanced Session Wait Data;135
11.3.1;Drilling Down from V$SESSION_WAIT;135
11.3.2;An Improved View;136
11.3.3;Source Code Depot;140
11.4;CHAPTER 12 X$KFFXP and ASM Metadata;141
11.4.1;X$KFFXP;141
11.4.2;Salvaging an SPFILE;142
11.4.3;Mapping Segments to ASM Storage;144
12;PART 5 SQL Statements;148
12.1;CHAPTER 13 ALTER SESSION/SYSTEM SET EVENTS;149
12.1.1;Tracing Your Own Session;149
12.1.2;ALTER SESSION SET EVENTS;150
12.1.3;ALTER SYSTEM SET EVENTS;151
12.1.4;ALTER SESSION/SYSTEM SET EVENTS and Diagnostic Dumps;152
12.1.5;Immediate Dumps;153
12.2;CHAPTER 14 ALTER SESSION SET CURRENT_SCHEMA;154
12.2.1;Privilege User vs. Schema User;154
12.2.1.1;Creating Database Objects in a Foreign Schema;156
12.2.2;Restrictions of ALTER SESSION SET CURRENT_SCHEMA;157
12.2.2.1;Advanced Queuing;157
12.2.2.2;RENAME;158
12.2.2.3;Private Database Links;158
12.2.2.4;Stored Outlines;159
12.3;CHAPTER 15 ALTER USER IDENTIFIED BY VALUES;161
12.3.1;The Password Game;161
12.3.2;Locking Accounts with ALTER USER IDENTIFIED BY VALUES;163
12.3.3;ALTER USER and Unencrypted Passwords;164
12.4;CHAPTER 16 SELECT FOR UPDATE SKIP LOCKED;166
12.4.1;Advanced Queuing;166
12.4.2;Contention and SELECT FOR UPDATE SKIP LOCKED;168
12.4.3;DBMS_LOCK—A Digression;176
12.4.4;Source Code Depot;179
13;PART 6 Supplied PL/SQL Packages;180
13.1;CHAPTER 17 DBMS_BACKUP_RESTORE;181
13.1.1;Recovery Manager;181
13.1.2;Disaster Recovery Case Study with Tivoli Data Protection for Oracle;186
13.1.3;Source Code Depot;188
13.2;CHAPTER 18 DBMS_IJOB;189
13.2.1;Introduction to DBMS_JOB;189
13.2.2;BROKEN Procedure;189
13.2.2.1;Syntax;190
13.2.2.2;Parameters;190
13.2.2.3;Usage Notes;190
13.2.2.4;Examples;190
13.2.3;FULL_EXPORT Procedure;191
13.2.3.1;Syntax;191
13.2.3.2;Parameters;191
13.2.3.3;Examples;191
13.2.4;REMOVE Procedure;192
13.2.4.1;Syntax;192
13.2.4.2;Parameters;192
13.2.4.3;Examples;192
13.2.5;RUN Procedure;193
13.2.5.1;Syntax;193
13.2.5.2;Parameters;193
13.2.5.3;Usage Notes;193
13.2.5.4;Examples;194
13.2.6;Source Code Depot;195
13.3;CHAPTER 19 DBMS_SCHEDULER;196
13.3.1;Running External Jobs with the Database Scheduler;196
13.3.1.1;Exit Code Handling;197
13.3.1.2;Standard Error Output;198
13.3.2;External Jobs on UNIX;200
13.3.2.1;Removal of Environment Variables;201
13.3.2.2;Command Line Processing;203
13.3.2.3;External Jobs and Non-Privileged Users;205
13.3.3;External Jobs on Windows;206
13.3.3.1;Command Line Argument Handling;207
13.3.3.2;Windows Environment Variables;208
13.3.3.3;External Jobs and Non-Privileged Users;208
13.3.3.4;Services Created by the ORADIM Utility;209
13.3.3.5;OracleJobScheduler Service;209
13.3.4;Source Code Depot;210
13.4;CHAPTER 20 DBMS_SYSTEM;211
13.4.1;GET_ENV Procedure;211
13.4.1.1;Syntax;211
13.4.1.2;Parameters;211
13.4.1.3;Usage Notes;212
13.4.1.4;Examples;212
13.4.2;KCFRMS Procedure;212
13.4.2.1;Syntax;212
13.4.2.2;Usage Notes;212
13.4.2.3;Examples;212
13.4.3;KSDDDT Procedure;214
13.4.3.1;Syntax;214
13.4.3.2;Usage Notes;214
13.4.3.3;Examples;214
13.4.4;KSDFLS Procedure;215
13.4.4.1;Syntax;215
13.4.4.2;Usage Notes;215
13.4.4.3;Examples;215
13.4.5;KSDIND Procedure;215
13.4.5.1;Syntax;215
13.4.5.2;Parameters;215
13.4.5.3;Usage Notes;216
13.4.5.4;Examples;216
13.4.6;KSDWRT Procedure;216
13.4.6.1;Syntax;216
13.4.6.2;Parameters;216
13.4.6.3;Usage Notes;216
13.4.6.4;Examples;217
13.4.7;READ_EV Procedure;218
13.4.7.1;Syntax;218
13.4.7.2;Parameters;218
13.4.7.3;Usage Notes;218
13.4.7.4;Examples;218
13.4.8;SET_INT_PARAM_IN_SESSION Procedure;219
13.4.8.1;Syntax;219
13.4.8.2;Parameters;219
13.4.8.3;Usage Notes;219
13.4.8.4;Examples;219
13.4.9;SET_BOOL_PARAM_IN_SESSION Procedure;221
13.4.9.1;Syntax;221
13.4.9.2;Parameters;221
13.4.9.3;Usage Notes;221
13.4.9.4;Examples;221
13.4.10;SET_EV Procedure;221
13.4.10.1;Syntax;222
13.4.10.2;Parameters;222
13.4.11;SET_SQL_TRACE_IN_SESSION Procedure;224
13.4.11.1;Syntax;224
13.4.11.2;Parameters;224
13.4.11.3;Usage Notes;224
13.4.11.4;Examples;224
13.4.12;WAIT_FOR_EVENT Procedure;224
13.4.12.1;Syntax;225
13.4.12.2;Parameters;225
13.4.12.3;Usage Notes;225
13.4.12.4;Examples;225
13.5;CHAPTER 21 DBMS_UTILITY;227
13.5.1;NAME_RESOLVE Procedure;227
13.5.1.1;Syntax;227
13.5.1.2;Parameters;228
13.5.1.3;Usage Notes;230
13.5.1.4;Exceptions;230
13.5.1.5;Examples;230
13.5.2;Name Resolution and Extraction of Object Statistics;232
13.5.3;Source Code Depot;234
14;PART 7 Application Development;235
14.1;CHAPTER 22 Perl DBI and DBD::Oracle;236
14.1.1;Circumnavigating Perl DBI Pitfalls;236
14.1.2;A Brief History of Perl and the DBI;237
14.1.3;Setting Up the Environment for Perl and the DBI;237
14.1.3.1;UNIX Environment;238
14.1.3.2;Windows Environment;242
14.1.4;Transparently Running Perl Programs on UNIX Systems;245
14.1.5;Transparently Running Perl Programs on Windows;246
14.1.6;Connecting to an ORACLE DBMS Instance;248
14.1.6.1;DBI connect Syntax;249
14.1.6.2;Connecting Through the Bequeath Adapter;250
14.1.6.3;Connecting Through the IPC Adapter;250
14.1.6.4;Connecting Through the TCP/IP Adapter;252
14.1.6.5;Easy Connect;253
14.1.6.6;Connecting with SYSDBA or SYSOPER Privileges;253
14.1.6.7;Connecting with Operating System Authentication;254
14.1.6.8;Connect Attributes;256
14.1.7;Comprehensive Perl DBI Example Program;257
14.1.8;Exception Handling;261
14.1.9;Source Code Depot;262
14.2;CHAPTER 23 Application Instrumentation and End-to-End Tracing;263
14.2.1;Introduction to Instrumentation;263
14.2.2;Case Study;265
14.2.2.1;JDBC End-to-End Metrics Sample Code;266
14.2.3;Compiling the Program;268
14.2.4;Instrumentation at Work;268
14.2.4.1;Setting Up Tracing, Statistics Collection, and the Resource Manager;268
14.2.5;Using TRCSESS;273
14.2.5.1;TRCSESS and Shared Server;275
14.2.6;Instrumentation and the Program Call Stack;278
14.2.7;Source Code Depot;279
15;PART 8 Performance;280
15.1;CHAPTER 24 Extended SQL Trace File Format Reference;281
15.1.1;Introduction to Extended SQL Trace Files;281
15.1.2;SQL and PL/SQL Statements;282
15.1.3;Recursive Call Depth;282
15.1.4;Database Calls;283
15.1.4.1;Parsing;284
15.1.4.2;PARSING IN CURSOR Entry Format;285
15.1.4.3;PARSE Entry Format;286
15.1.4.4;PARSE ERROR Entry Format;288
15.1.4.5;EXEC Entry Format;288
15.1.4.6;FETCH Entry Format;288
15.1.4.7;Execution Plan Hash Value;289
15.1.4.8;Plan Hash Value Case Study;289
15.1.4.9;CLOSE Entry Format;293
15.1.5;COMMIT and ROLLBACK;294
15.1.6;UNMAP;295
15.1.7;Execution Plans, Statistics, and the STAT Entry Format;295
15.1.7.1;STAT Entry Format in Oracle9i;296
15.1.7.2;STAT Entry Format in Oracle10g and Oracle11g;296
15.1.8;Wait Events;298
15.1.8.1;WAIT Entry Format;298
15.1.8.2;WAIT in Oracle9i;299
15.1.8.3;WAIT in Oracle10g and Oracle11g;300
15.1.9;Bind Variables;300
15.1.9.1;BINDS Entry Format;301
15.1.9.2;Statement Tuning, Execution Plans, and Bind Variables;305
15.1.10;Miscellaneous Trace File Entries;311
15.1.10.1;Session Identification;312
15.1.10.2;Service Name Identification;312
15.1.10.3;Application Instrumentation;313
15.1.10.4;ERROR Entry Format;316
15.1.10.5;Application Instrumentation and Parallel Execution Processes;318
15.2;CHAPTER 25 Statspack;321
15.2.1;Introduction to Statspack;321
15.2.1.1;Retrieving the Text of Captured SQL Statements;323
15.2.1.2;Accessing STATS$SQLTEXT;327
15.2.1.3;Capturing SQL Statements with Formatting Preserved;333
15.2.2;Undocumented Statspack Report Parameters;334
15.2.3;Statspack Tables;335
15.2.4;Finding Expensive Statements in a Statspack Repository;340
15.2.5;Identifying Used Indexes;341
15.2.6;Execution Plans for Statements Captured with SQL Trace;341
15.2.7;Finding Snapshots with High Resource Utilization;344
15.2.7.1;High CPU Usage;345
15.2.7.2;High DB Time;347
15.2.8;Importing Statspack Data from Another Database;350
15.2.9;Source Code Depot;353
15.3;CHAPTER 26 Integrating Extended SQL Trace and AWR;354
15.3.1;Retrieving Execution Plans;354
15.3.2;Lessons Learned;357
15.3.3;Source Code Depot;358
15.4;CHAPTER 27 ESQLTRCPROF Extended SQL Trace Profiler;359
15.4.1;Categorizing Wait Events;360
15.4.2;Calculating Response Time and Statistics;361
15.4.2.1;Case Study;362
15.4.2.2;Running the Perl Program;362
15.4.2.3;Calculating Statistics;365
15.4.2.4;Calculating Response Time;365
15.4.3;ESQLTRCPROF Reference;366
15.4.3.1;Command Line Options;367
15.4.3.2;ESQLTRCPROF Report Sections;368
15.4.3.3;Lessons Learned;377
15.4.3.4;Source Code Depot;378
15.5;CHAPTER 28 The MERITS Performance Optimization Method;379
15.5.1;Introduction to the MERITS Method;379
15.5.2;Measurement;380
15.5.2.1;Measurement Tools;380
15.5.3;Assessment;385
15.5.3.1;Resource Profiles and Performance Assessment Tools;386
15.5.4;Reproduction;387
15.5.5;Improvement;388
15.5.6;Extrapolation;388
15.5.7;Installation;389
15.5.8;MERITS Method Case Study;389
15.5.8.1;Phase 1—Measurement;390
15.5.8.2;Phase 2—Assessment;390
15.5.8.3;Phase 3—Reproduction;397
15.5.8.4;Phase 4—Improvement;400
15.5.8.5;Phase 5—Extrapolation;405
15.5.8.6;Phase 6—Installation;405
15.5.8.7;Lessons Learned;406
15.5.9;Source Code Depot;406
16;PART 9 Oracle Net;407
16.1;CHAPTER 29 TNS Listener IP Address Binding and IP=FIRST;408
16.1.1;Introduction to IP Address Binding;408
16.1.2;Multihomed Systems;410
16.1.3;IP=FIRST Disabled;412
16.1.3.1;Host Name;412
16.1.3.2;Loopback Adapter;414
16.1.3.3;Boot IP Address;414
16.1.3.4;Service IP Address;415
16.1.4;IP=FIRST Enabled;416
16.1.5;Lessons Learned;417
16.2;CHAPTER 30 TNS Listener TCP/IP Valid Node Checking;419
16.2.1;Introduction to Valid Node Checking;419
16.2.2;Enabling and Modifying Valid Node Checking at Runtime;421
16.3;CHAPTER 31 Local Naming Parameter ENABLE=BROKEN;425
16.3.1;Node Failure and the TCP/IP Protocol;425
16.4;CHAPTER 32 Default Host Name in Oracle Net Configurations;429
16.4.1;Default Host Name;429
16.4.2;Disabling the Default Listener;431
17;PART 10 Real Application Clusters;432
17.1;CHAPTER 33 Session Disconnection, Load Rebalancing, and TAF;433
17.1.1;Introduction to Transparent Application Failover;433
17.1.2;ALTER SYSTEM DISCONNECT SESSION;434
17.1.2.1;SELECT Failover;435
17.1.2.2;Failover at the End of a Transaction;439
17.1.3;Session Disconnection and DBMS_SERVICE;441
17.1.3.1;Setting Up Services with DBMS_SERVICE;441
17.1.3.2;Session Disconnection with DBMS_SERVICE and TAF;443
17.1.4;Lessons Learned;446
17.1.5;Source Code Depot;447
17.2;CHAPTER 34 Removing the RAC Option Without Reinstalling;448
17.2.1;Linking ORACLE Software;448
17.2.2;Case Study;450
17.2.2.1;Simulating Voting Disk Failure;450
17.2.2.2;Removing the RAC Option with the Make Utility;452
17.2.2.3;Conversion of a CRS Installation to Local-Only;454
17.2.2.4;Re-enabling CRS for RAC;457
17.2.3;Lessons Learned;458
18;PART 11 Utilities;459
18.1;CHAPTER 35 OERR;460
18.1.1;Introduction to the OERR Script;460
18.1.2;Retrieving Undocumented Events;462
18.1.3;Source Code Depot;464
18.2;CHAPTER 36 Recovery Manager Pipe Interface;465
18.2.1;Introduction to Recovery Manager;465
18.2.2;Introduction to DBMS_PIPE;466
18.2.3;RMAN_PIPE_IF Package;467
18.2.4;RMAN_PIPE_IF Package Specification;468
18.2.5;Using the Package RMAN_PIPE_IF;468
18.2.6;Validating Backup Pieces;475
18.2.7;Internode Parallel Backup and Restore;476
18.2.8;Source Code Depot;477
18.3;CHAPTER 37 ORADEBUG SQL*Plus Command;478
18.3.1;Introduction to ORADEBUG;478
18.3.2;ORADEBUG Workflow;479
18.3.3;ORADEBUG Command Reference;479
18.3.3.1;Attaching to a Process;480
18.3.3.2;ORADEBUG IPC;482
18.3.3.3;ORADEBUG SHORT_STACK;484
18.3.3.4;Diagnostic Dumps;485
18.3.4;Lessons Learned;490
19;PART 12 Appendixes;491
19.1;APPENDIX A Enabling and Disabling DBMS Options;492
19.2;APPENDIX B Bibliography;493
19.2.1;References;493
19.3;APPENDIX C Glossary;495
20;Index;502