Search and Find
Service
Secrets of the Oracle Database
of: Norbert Debes
Apress, 2010
ISBN: 9781430219538 , 450 Pages
Format: PDF, Read online
Copy protection: DRM
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
All prices incl. VAT