Oracle Performance Tuning [With *]

Be the first to write a review

Overview

The books in O'Reilly's Oracle series are authoritative — they tell the whole story about complex topics, ranging from performance tuning to the use of packages in PL/SQL to new technologies like Power Objects. And they're independent; they're alternatives for readers who need to know how products and features really work. They're packed with real-world advice and techniques from practitioners in the field, and they come with disks containing code you can use immediately in your own applications. O'Reilly is the ...

See more details below
Paperback (Second Edition)
$54.66
BN.com price
(Save 31%)$79.99 List Price
Other sellers (Paperback)
  • All (31) from $1.99   
  • New (9) from $1.99   
  • Used (22) from $1.99   
Sending request ...

More About This Book

Overview

The books in O'Reilly's Oracle series are authoritative — they tell the whole story about complex topics, ranging from performance tuning to the use of packages in PL/SQL to new technologies like Power Objects. And they're independent; they're alternatives for readers who need to know how products and features really work. They're packed with real-world advice and techniques from practitioners in the field, and they come with disks containing code you can use immediately in your own applications. O'Reilly is the alternative for Oracle people who need to solve problems — and solve them now.Performance tuning is crucial in any modern relational database management system. Too many organizations respond to Oracle performance problems by throwing money at these problems — by buying larger and more expensive computers or by hiring expert consultants. But there's a lot you can do on your own to increase dramatically the performance of your existing system. Whatever version of Oracle you're running — from Version 6 to Oracle8, proper tuning can save your organization a huge amount of money in additional equipment, extra memory, and hardware upgrades.The first edition of Oracle Performance Tuning became a classic for programmers, managers, database administrators, system administrators, and anyone who cares about improving the performance of an Oracle system. This second edition contains 400 pages of updated material updating on Oracle features, incorporating advice about disk striping and mirroring, RAID, client-server, distributed databases, MPPS, SMPs, and other architectures. It also includes chapters on parallel server, parallel query, backup and recovery, the Oracle Performance Pack, and more.

A complete revision of the original title, this second edition adds new material on Oracle 7.3 and many Oracle 8 features. It explores new Oracle capabilities like parallel server, parallel query, and distributed database. It contains more detail on constraints and triggers, many more examples, and information on new tuning tools like the Oracle Performance Pack, Oracle Trace, and Oracle Expert.

Read More Show Less

Editorial Reviews

Booknews
A guide to improving the performance of the Oracle relational database system. Addresses initial equipment and configuration decisions; how to get the best performance out of programs focusing on SQL, PL/SQL, and locking strategies; using memory and disk space efficiently; and long-running job, client-server environment, or system specific tuning. Additionally contains six appendixes containing summary material of relevance. by Book News, Inc., Portland, Or.
Read More Show Less

Product Details

  • ISBN-13: 9781565922372
  • Publisher: O'Reilly Media, Incorporated
  • Publication date: 11/28/1996
  • Series: Nutshell Handbooks Series
  • Edition description: Second Edition
  • Edition number: 2
  • Pages: 966
  • Product dimensions: 7.39 (w) x 9.14 (h) x 2.35 (d)

Meet the Author

As a database administrator on a financial IMS project about eight years ago, Mark Gurry was asked to investigate his company's database direction for the next five years. The number of users was up to 950, the cost of maintaining the mainframes was huge, and he'd heard about relational databases and downsizing. After much investigation, he chose Oracle, and has stuck with the system ever since. He has worked as Manager of Computing and Network Services, senior database administrator, senior Oracle technical support, and other jobs. He now has a small consulting company called New Age Consultants. Mark has worked for many large organizations and is currently working for Telecom Australia, the largest computer site in Australia and one of the largest in the world. He has also spoken on tuning at Oracle user group meetings and has given internal tuning courses at several of his larger client sites. He has been a senior team member on award-winning systems that have been developed using Oracle.

Peter Corrigan runs a small consulting company in Australia called Gauntlet Computers and works as a senior database administrator and project leader developing Oracle applications and tuning systems. His speciality areas include client-server architecture and application downsizing, and he is the co-developer of the Rainbow Financial package, sold internationally. He is a frequent speaker on the topic of tuning and programming at the Oracle Asia Pacific user group conferences and the Victoria Oracle user's group.

Read More Show Less

Table of Contents


Table of Contents

Preface

I. Overview

1. Introduction to Oracle Performance Tuning
Why Tuning?
Who Tunes?
When Do You Tune?
A Look at Recent Versions
How Much Tuning Is Enough?
2. What Causes Performance Problems?
Problems with Design and Development
Problems with System Resources
Memory Problems and Tuning
Disk I/O Problems and Tuning
CPU Problems and Tuning
Network Problems and Tuning

II. Tuning for Managers and Planners

3. Planning and Managing the Tuning Process
Managing the Problem of Response Time
Managing the Problem of Long-Running Jobs
Managing the Workload in Your System
Making the Decision to Buy More Equipment
Management Checkpoints
Performance Hints for Managers
4. Defining System Standards
Selecting a Common Design Methodology
Selecting Your Software
Selecting Your Hardware
Setting Up Screen and Report Templates
Using Modular Programming Techniques
Defining System Libraries
Enforcing Program Version Control
Establishing Documentation Standards
Establishing Database Environment Standards
Security Standards
Performance Standards

III. Tuning for Designers and Analysts

5. Designing for Performance
Common Design Problems
Choosing an Architecture
Tuning Your Data Model
Tuning Indexes
Testing the Data Model for Performance
Denormalizing a Database
Constraints
Triggers
Packages, Procedures, and Functions
Designing a Very Large Database Application
Miscellaneous Design Considerations

IV. Tuning for Programmers

6. Tuning SQL
SQL Standards
The SQL Optimizer
SQL Tuning
Common Sense in SQL
SQL Performance Tips and Hints
Using Indexes to Improve Performance
SQL Tuning Alternatives
Identifying Poor SQL Statements
Adjusting SQL Statements Over Time
7. Tuning PL/SQL
PL/SQL and SQL
What Does PL/SQL Offer?
PL/SQL Limitations
PL/SQL Coding Standards
Tuning PL/SQL
Exploiting the Power of PL/SQL
Oracle PL/SQL Function Extensions
8. Selecting a Locking Strategy
What Is Locking?
Releasing Locks
Avoiding the Dreaded Deadlock
Locking Considerations for Oracle Features
Overriding the Default Locking Strategy
Internal Lock Contention
Lock Detection Scripts

V. Tuning for Database Administrators

9. Tuning a New Database
Steps in Setting Up a Database
Tuning Memory
Tuning Disk I/O
Creating the Database
Creating the Tablespaces
Creating Rollback Segments
Creating Tables
Creating Indexes
Creating Views
Creating Users
INIT.ORA Parameter Summary
Creating Very Large Databases
10. Diagnostic and Tuning Tools
MONITOR: Monitoring System Activity Tables
SQL_TRACE: Writing a Trace File
TKPROF: Interpreting the Trace File
EXPLAIN PLAN: Explaining the Optimizer's Plan
ORADBX: Listing Events
ANALYZE: Validating and Computing Statistics
UTLBSTAT.sql and UTLESTAT.sql: Taking Snapshots
Other Oracle Scripts
Some Scripts of Our Own
Oracle Performance Manager
11. Monitoring and Tuning an Existing Database
Identifying Untuned Application Code
Tuning Memory
Tuning Disk I/O
Avoiding Contention
12. Tuning Parallel Server
Introduction to Parallel Server
Parallel Server Architecture
Parallel Server Locking
Parallel Server Design
Parallel Server Database
Tuning Instances
INIT.ORA Parameters
Ongoing Tuning
13. Tuning Parallel Query
Introduction to Parallel Query
PQO Design Considerations
Constructing Your Database for PQO
INIT.ORA Parameters
Ongoing Tuning of Query Servers
Creating Indexes in Parallel
Using PQO to Speed Data Loads
Performing Parallel Recovery
14. Tuning Database Backup and Recovery
The DBA's Responsibility
Backing Up the Database
Preparing to Recover the Database
Recovering the Database

VI. Tuning for System Administrators

15. Tuning Long-Running Jobs
Correctly Sizing Tables and Indexes
Exploiting Array Processing
Optimizing INIT.ORA Parameters
Disk Tuning
Running Jobs in Parallel
DBA Tuning for Long-Running Jobs
Creating Overnight Extract Tables
Index Operations
Using PL/SQL to Speed Up Updates
Inline Functions
Minimizing the Number of Updates
Tuning EXPORT and IMPORT Utilities
16. Tuning in the Client-Server Environment
What Is Client-Server?
Network Topology
Where Should You Put the Network Hardware?
Client-Server Performance Issues
Tuning Precompilers for Client-Server
Tuning the Network Itself
Tuning SQL*Net
Client-Server: Adapting It in the Real World
17. Capacity Planning
About Capacity Planning
What Do You Need to Test?
Capacity Planning Checklist
18. Tuning for Specific Systems
UNIX-Specific Tuning
VMS-Specific Tuning

VII: Appendixes

A. Summary of New Features
Oracle7.0 Features
Oracle7.1 Features
Oracle7.2 Features
Oracle7.3 Features
B. Hot Tuning Tips
Questions from Planners and Managers
Questions from Analysts and Designers
Questions from Programmers
Questions from Database Administrators
Questions from System Administrators
C. Tuning Oracle Financials
Introduction to Financials Tuning
Installing Oracle Financials
Database-Level Tuning
Upgrading Oracle Financials
Concurrent Request Processing
Archiving and Purging
The GL Optimizer
Developer Utilities
Financials Tips
Resources for Financials Developers
D. Oracle Performance Pack
Oracle Performance Manager
Oracle Lock Manager
Oracle Topsessions
Oracle Tablespace Manager
Oracle Expert and Oracle Trace
E. Tuning Oracle Forms 4.0 and 4.5
GUI Tips
General Tips
F. Tuning Case Studies
Case Study Database 1
Case Study Database 2
G. Dynamic Performance Tables

Index









INDEX:

Symbols
* (dynamic column reference), 170, 175
use in auditing, 170
@ORA_INSTALL:ORA_INSUTL, 479
_DB_BLOCK_MAX_SCAN_CNT, 498
_DB_BLOCK_WRITE_BATCH, 361, 498
monitoring DBWR, 420
_OPTIMIZER_UNDO_CHANGES, 841
_SMALL_TABLE_THRESHOLD, 375
|| (concatenate), 173

A
ACCP/LU6.2, 709
aliases, 138
ALTER INDEX, 563
ALTER TABLE, 215, 535, 555, 563
ALTER TABLESPACE, 539, 596
ALWAYS_ANTI_JOIN, 568
analysis stage
questions about, 786
tuning issues, 12, 88, 99
ANALYZE, 141, 353, 408
monitoring chained rows, 494, 663
statistics, 409
ANSI
SQL standard, 133
standard for duplicate rows, 222
standard for nulls, 169
archive logs, 309, 639, 839
archive mode, 77
arrays, 653
with precompilers, 706
ARRAYSIZE
EXPORT and IMPORT, 695
long-running jobs, 653
Oracle Call Interface (OCI), 695
SQL*Forms, 695, 698
SQL*Loader, 695
SQL*Plus, 695
associations, 91
associative tables, 92
asynch, 709
attributes, 91
defining, 98
scope, 98
auditing
using *, 170

B
backup (EXPORT), 678, 835, 848
BALSETCNT, 761
base tables
views with client-server, 698
BEGIN, 222
BETWEEN, 140
bind variables, 137, 210, 305, 458, 696
BITMAP_MERGE_AREA_SIZE, 358
bitmapped indexing, 564
blocks
chained, 494
PL/SQL, 206, 258
size in PL/SQL, 213
table, 432
bottlenecks, 30, 739
CPU, 38, 740, 849
CPU in UNIX, 753
disk I/O, 35, 36, 740, 749, 849
identifying with VMS, 762
memory, 33, 436, 453, 739
memory in UNIX, 750
memory in VMS, 765
network, 40, 740
network in UNIX, 754
BSTAT and ESTAT
monitoring buffer cache, 473
monitoring contention, 500
BUFFER, 679, 695
buffer cache, 303, 470, 656
busy wait ratio, 419
decreasing, 473
hit ratio, 418, 436, 470
increasing, 472
tuning, 418
UNIX, 747
BUFFPAGES, 747
bus
parallel servers, 524
speeds, 707
topology, 688

C
calculations, avoiding in indexes, 187
capacity planning, 56, 722
checklist, 727
report, 742
CASE tools, 59, 64, 96
casting, 190
catblock.sql, 425
catparr.sql, 545
chained rows, 837
CHAINED_ROWS, 494, 663
chaining, disk, 494, 837
check constraints, 119, 540, 559, 806
check constraints, 119
CHECK_SQL, 707
CHECKPOINT_PROCESS, 359
client, 6, 684
client-server, 4, 6, 56, 78, 86, 683, 818
example of UNIX network, 686
tuning, 818
using PL/SQL, 236, 240, 695
using PS/SQL, 209
CLOSE, 222, 696
code tables, 112
cold backups, 590-594, 599, 643
columns, 89
* (dynamic reference), 170, 175
aliases, 138
composite in indexes, 102
type conversion, 190
commits
with PS/SQL, 209
COMPATIBLE, 564, 566, 575
COMPOSITE_LIMIT, 274
compress, 40, 587, 593
concatenate, 173
concurrency, 269, 860
CONNECT, 215
CONNECT BY PRIOR, 133
CONNECT_TIME, 274
consistency, database, 271, 337
constants
PL/SQL, 209
constraints, 117-121
CHECK, 119, 806
foreign key, 118
primary key, 118
referential integrity, 806
types, 118
unique key, 118
contention
data, 419
DBWR, 497
free list, 292
latches, 422
locks, 289
redo logs, 291, 503, 508
rollback segments, 289, 419, 500
control files, 309, 598, 623, 635, 639
conversion
index column types, 190
cost-based optimizer, 564
COUNT, 175
CPU
bottlenecks in UNIX, 753
priorities, 39, 759
tuning, 38, 725
CPU_PER_CALL, 274
CPU_PER_SESSION, 274
CREATE
DATABASE, 317
INDEX, 215, 351, 574
INDEX sorts, 488
PROFILE, 357
ROLLBACK SEGMENT, 337
SEQUENCE, 215, 374
TABLE, 215, 344, 345, 539, 555
TABLESPACE, 320, 539, 562
USER, 357
VIEWS, 356, 540
CREATE_BITMAP_AREA_SIZE, 359
CURSOR_SPACE_FOR_TIME, 359, 458
cursors, 78
explicit, 222
implicit, 222
in PL/SQL, 222, 259-260

D
data integrity, 579, 584
data loading, 575
data model, 788
denormalization, 106
design, 88
normalization, 93
testing, 103-106
database
consistency, 274, 337
creating, 317
defragmentation, 581
denormalization, 106
design, 88
distributed, 4, 793
distributed with client-server, 692
distributed with deadlocks, 283
environment standards, 74
export, 580-589
files, 309, 317, 603
indexes, 98
names of files, 317
normalization, 93
number of files, 317
partitioning for parallel server, 532-533, 541
performance snapshot, 410
reducing database trips, 165, 174, 206
reducing network trips, 692
size calculations, 430
size of files, 318
summary of tuning steps, 301
tuning, 299, 443, 726
database administration
database backup and recovery, 579
questions about, 808
tuning tips, 808-848
very large database, 123
database backup, 579, 580-600, 835, 848
export, 598
factors, 580
hot backups, 594-598, 599, 644
image (cold) backups, 590, 599, 643
incremental, 585
database backups
image (cold) backups, 590, 599, 643
database recovery, 579
parallel recovery, 576
preparation, 600-601
recovery machine, 630, 634-639
recovery strategy, 628
dates, rounding, 172
DB_BLOCK_BUFFERS, 34, 114, 303, 360, 567, 831
tuning buffer cache, 470
tuning buffer cache hit ratio, 419
DB_BLOCK_CHECKPOINT_BATCH, 360
DB_BLOCK_LRU_EXTENDED_ STATISTICS, 472
DB_BLCOK_LRU_LATCHES, 360
DB_BLOCK_LRU_STATISTICS, 473
DB_BLOCK_SIZE, 362, 563, 567
for PCM locks, 528
for tablespaces, 334
DB_FILE_MULTIBLOCK_READ_COUNT, 362, 563
DB_FILE_SIMULTANEOUS_WRITES, 361
DB_FILES, 362
DB_WRITERS
tuning DBWR, 420, 498
DBA_BLOCKERS view, 425
DBA_DDL_LOCKS view, 425
DBA_DML_LOCKS view, 425
DBA_LOCKS view, 425
DBA_WAITERS view, 425
DBMS_ALERT, 264
DBMS_APPLICATION_INFO, 267
DBMS_DDL, 265
DBMS_JOB, 266, 792
DBMS_OUTPUT, 261
DBMS_PIPE, 264
DBMS_SESSION, 265
DBMS_SHARED_POOL, 261
DBMS_SQL, 262-264
DBMS_TRANSACTION, 262
DBMS_UTILITY, 266
dbmslock.sql, 426
DB-Vision, 850
DBWR (database writer), 39, 420
avoiding contention, 497
DDL_LOCKS, 363
deadlocks, 274
DELETE CASCADE, 282
handling explicitly, 275
debugging
in PL/SQL, 214
DECLARE, 222, 696
DECnet, 709
DECODE, 133, 169
DEFAULT STORAGE, parameters for tablespaces, 333
defragmentation, 581, 592, 597
degree of parallelism, 568-570
DELAY, 708
DELETE CASCADE
with deadlocks, 282
deleting rows, 814
denormalization, 106, 556, 787, 790
dependent tables, 91
design stage
common problems, 83-85
methodology, 64
questions about, 786
tuning issues, 12, 88, 99
very large database application, 121-128
development stage
general tuning issues, 14
PL/SQL tuning issues, 205
questions about, 793
SQL tuning issues, 133
dictionary
performance, 437
dictionary cache, 437
dirty list, 497
DISCRETE_TRANSACTIONS_ENABLED, 363
disk
chaining, 494, 837
clusters, 541
database recovery, 631
fragmentation, 36, 431
layout, 74
load sharing, 36
mirrored, 67
disk I/O
I/Os per disk file, 481
problems, 35
reducing I/Os, 486
spread, 421
tuning, 35, 309, 480, 724, 849
tuning checklist, 309
dispatcher, 512
DISTINCT, 180
sorts, 488
distributed databases, 4, 87, 793
with client-server, 692
with deadlocks, 283
distributed lock manager (DLM), 524
DML_LOCKS, 363, 528, 545
documentation standards, 73
downsizing, 3, 6, 683
driving tables, 151, 204
DROP TABLE, 215
dropping tables, 814
duplicate records, deleting, 175
duplicate rows, PL/SQL, 222
dynamic extension, 36, 350, 490
dynamic performance tables, 899, 923
dynamic SQL, 798
with precompilers, 706

E
Ecosphere, 850
ENQUEVE_RESOURCES, 363
entities, 89
defining, 95
scope, 96
equipment needs, 55, 722, 781
error handling
in PL/SQL, 212
events, 289
exceptions
in PL/SQL, 212
executables, sharing, 479
execution plan, SQL statements, 139
EXISTS
SQL, 178, 181
EXPLAIN PLAN, 22, 139, 397, 559, 569, 572
export (EXP) utility, 589, 598, 677, 816
client-server, 695
compression, 587
database backup, 580-589, 598
incremental, 585
UNIX, 756
EXT_TO_OBJ, 546
EXT_TO_OBJ_VIEW, 546
extension
dynamic, 36, 350, 490
dynamic with temporary segments, 492
extents, contiguous, 36, 335, 429, 490
extract tables, 668
long-running jobs, 668
reason for denormalization, 114

F
fastpath, 710, 711
FETCH, 222, 696
fields
names, 69
FILE_LOCK, 549
FILE_PING, 549
files
control, 309
database, 309, 317, 603
layout, 75
names, 317
number, 317
number of tablespaces, 328
size, 318
financial benefits, tuning, 5
Financials
archiving, 865
concurrent processing, 860
installing, 853
purging, 865
tuning, 857
upgrading, 859
fine-grained locks, 527, 544
First Normal Form (1NF), 92
FOR UPDATE OF, in SELECT, 284
foreign keys, 90
constraints, 118
indexes, 101
Fourth Normal Form (4NF), 93
fragmentation
disk, 36, 431
free list contention, 292
free memory, 33, 471
free space, 605
FREELIST GROUPS, 316, 539, 547
FREELISTS, 316, 547
tables, 349, 539
FROM clause, in SELECT, 150
full table scan, vs. indexes, 184
functions
stored, 121
stored with PL/SQL, 208, 233, 244

G
GC_DB_LOCKS, 364, 542, 548, 549
GC_FILES_TO_LOCKS, 364, 528, 538, 542, 543, 544, 548, 549
GC_RELEASABLE_LOCKS, 528, 544
GC_ROLLBACK_LOCKS, 366, 545
GC_ROLLBACK_SEGMENTS, 366, 544
GC_SAVE_ROLLBACK_LOCKS, 366, 544
GC_SEGMENTS, 365, 544
GC_TABLESPACES, 365, 544
GL optimizer, 868
GOTO, problem with PL/SQL, 217
GRANT, 215
graphical user interface (GUI), 4, 115, 326, 785, 791
GROUP BY
in DECODE, 170
sorts, 488

H
hardware
configuring, 67
recovery from failure, 628-631
selecting, 65-66
hash clusters, 539
hash locks, 528
HASH_AREA_SIZE, 366, 568
HASH_JOIN_ENABLED, 366, 568
HASH_MULTIBLOCK_ID_COUNT, 367
hashed locks, 544
HAVING, 175
hidden parameters, 379, 841
hints, SQL optimization comments, 157
history table, 532
history tables, 92
hot backups, 594-598, 599, 644
human benefits, tuning, 7
hybrid topology, 689

I
IDLE_TIME, 274
image (cold) backups, 590-594, 643
image backups, 599
import (IMP) utility, 583, 677, 814
client-server, 695
IN
SQL, 181
incremental exports, 585
INDEX_ASC, 559, 574
INDEX_ONLY, 574
INDEX_STATS view, 353
indexed retrieval, vs. full-table scan, 184
indexes, 98, 789, 800, 801, 812
additional columns, 187
avoiding calculations, 187
avoiding NOT, 189
avoiding nulls, 189
choosing over parallel query, 557
creating, 351, 812
designing, 98-103
disabling, 186
number, 102, 789
parallel query, 574
reorganization, 833
size, 352, 833
sizes for long-running jobs, 650
specifying tablespaces, 352
statistics, 353
suppressing, 155
tablespaces, 324, 538
testing effectiveness, 424
tuning table access, 420, 803
type conversion, 190
when to define them, 101, 789
with inserts, 669
INIT.ORA file, 635
INIT.ORA parameters, 79, 307, 358-379, 841
parallel query, 564-568
parallel server, 526, 541-545
INITIAL, 313
for rollback segments, 342
for tables, 347
for tablespaces, 335, 493
INITRANS, for tables, 346, 348
internal keys, 111
INTERSECT, sorts, 488
iostat, 749
IRP, 761

J
joins
table sorts, 488
tables, 153, 178, 179

K
keys
foreign, 90
internal, 111
primary, 90, 96, 111
primary concatenated, 113
system-assigned, 111

L
latches, 289
redo log contention, 422, 508
UNIX, 747
latency, 707
least-recently-used (LRU) list, 498
LGWR (log writer), 39, 304, 506
libraries
system, 71
library cache, 305
limits
resource profile, 357
line speeds, 731
local cache, client, 718
lock manager, 879
LOCK TABLE, 286
locking, 269
overriding defaults, 284
parallel server, 280, 525-529
problems, 832
row-level, 271
table-level, 270, 528
tables explicitly, 286
transaction-level, 529
locks, 269, 805
contention, 289
detection scripts, 292-296
exclusive, 272, 286
explicit, 284
fine-grained, 527, 544
Parallel Cache Management (PCM), 280, 526-528, 542, 546
referential integrity, 277-280
releasing, 272, 426
requesting, 426
rows, 271
sequence, 275
spin, 748
System Change Number (SCN), 528
via pseudo-code, 287
waits, 832
log buffer, 304
LOG_ARCHIVE_BUFFER_SIZE, 368
LOG_ARCHIVE_BUFFERS, 367
LOG_ARCHIVE_DEST, 367
LOG_ARCHIVE_START, 368
LOG_BUFFER, 37, 304, 368
redo logs, 505
LOG_CHECKPOINT_BUFFER, 37
LOG_CHECKPOINT_INTERVAL, 369
redo logs, 504
LOG_CHECKPOINT_TIMEOUT, 369
redo logs, 505
LOG_ENTRY_PREBUILD_THRESHOLD, 370
LOG_FILES, 318
LOG_SIMULTANEOUS_COPIES, 370, 509
LOG_SMALL_ENTRY_MAX_SIZE, 370, 508
LOG_SMALL_MAX_SIZE, 509
LOGICAL_READS_PER_CALL, 274
LOGICAL_READS_PER_SESSION, 274
logins, 821
parallel server databases, 541
remote, 712
logs
archive, 309, 839
multiplexed redo, 320
redo, 291, 309, 318, 503, 537, 597, 636, 640, 839
LONG, 100, 215
LONG RAW, 100, 215
long-running jobs, 39, 49, 649
killing, 820
rollback segments, 341
server end, 705

M
mainframe computer, costs, 7
man pages, 749
management
checkpoints, 57
performance summary, 61
questions about, 781
standards, 63
tuning issues, 10, 43
MAX, 140
MAX_DUMP_SIZE, 389
MAXEXTENTS, 314
for rollback segments, 342
MAXLOGFILES, 318
MAXPROCESSCNT, 761
MAXTRANS
for tables, 346, 348
MAXUSERS, 729, 756
memory
bottlenecks, 453
free, 33, 471
layout, 35, 302
pinning packages, 242
pinning procedures, 238
problems, 33, 830
tuning, 302, 453, 724, 810, 830, 849
tuning checklist, 306
methodology, design, 64
MIN, 140
MINEXTENTS, 316
for rollback segments, 342
MINUS
sorts, 488
MIPS, 725
mirrored disks, 67
modular programming, 71
MONITOR, 387, 834
CIRCUIT, 388
DISPATCHER, 388
FILE IO, 387
FILES, 481
IO, 471
LIBRARYCACHE, 388, 457
MONITOR (continued)
LOCK, 426
QUEUE, 388, 513
refreshing the display, 388
SESSION, 388
SHARED SERVER, 388
SQLAREA, 388, 457
SYSTEM IO, 387
monitoring
disk I/O, 480
memory, 453
UNIX commands, 748
monitoring stage, tuning issues, 16, 299, 443
MPW_HILIMIT, 762
MPW_IOLIMIT, 762
MPW_THRESH, 762
MPW_WAITLIMIT, 762
MTS_DISPATCHERS, 371, 513
MTS_MAX_DISPATCHERS, 371
MTS_MAX_SERVERS, 371, 513
MTS_SERVERS, 513
multiplexed redo logs, 320
Multiprotocol Interchange, 710
multithreaded server, 87, 469, 512
mutating triggers, 280

N
names
duplicate in PL/SQL, 216
field, 69
files, 317
standards, 75
NBUF, 747
Netware, 709
network
bus, 688
configuration diagram, 731
delays, 708
hardware, 689
hybrid, 689
ring, 688
star, 687
token ring, 688
topology, 686
traffic, 692
tuning, 40, 707, 726
UNIX, 686
wide-area, 690
NEXT, 314
for rollback segments, 342
for tablespaces, 335, 493
NOCACHE, 374
normalization, 92, 93, 787
NOT, 189
NPAGEDYN, 762
NULL, 189
nulls
avoiding in indexes, 189
flags, 197
rules, 169
numeric variables
in PS/SQL, 210

O
oneidxs.sql, 424
ON-LOCK, 285
OPEN, 222, 696
OPEN_CURSORS, 306, 371
tuning cursors, 457
OPS$ usercodes, 712
OPTIMAL, 316
for rollback segments, 342
optimizer, 138
cost-based, 141
rule-based, 139
OPTIMIZER_MODE, 371
OPTIMIZER_PERCENT_PARALLEL, 372
OR
SQL, 182
Oracle Applications list server, 871
Oracle Applications Users Group (OAUG), 871
Oracle Expert, 884, 885-892
Oracle Forms
tuning, 899
Oracle Lock Manager, 879
Oracle Performance Manager, 876-879
Oracle Performance Pack, 875
Oracle Procedure Builder, 214
Oracle Tablespace Manager, 882
Oracle Topsessions, 880
Oracle Trace, 22, 884, 892-897
Oracle7
features, 773-775
Oracle7.1
features, 18, 775
Oracle7.2
features, 19-20, 776
Oracle7.3
features, 20-23, 777
Oracle8
features, 23-24, 779
ORADBX, 405
ORDER BY
condition ranking, 140
in DECODE, 169
indexes, 558
sorts, 488
overlaying procedures, 235
overnight processing, 54, 649, 786, 800, 816
extract tables, 115, 668
jobs in parallel, 663
rollback segments, 341
with PL/SQL, 243

P
PACING, 709
packages
initialization section, 246
stored with PL/SQL, 244
use with stored procedures and functions, 257-258
packages, stored, 121, 801
packets, 693
size, 708
PAGEDYN, 762
paging, 33, 750, 849
Parallel Cache Management (PCM) locks, 280, 526-528, 542, 546
parallel jobs, 663
parallel query, 129, 564
architecture, 553-556
choosing over an index, 557
comparison with parallel server, 551
data loading, 575
degree of parallelism, 568-570
indexes, 574
suitable applications, 556
tuning, 570-574
with parallel server, 537
parallel query option (PQO), 249, 551
parallel recovery, 576
parallel server, 22, 86
architecture, 523-525
comparison with parallel query, 551
configuration, 523
INIT.ORA parameters, 541-545
locks, 280, 525-529
pinging, 535, 541-542
suitable applications, 523, 531-535
table categories, 530
tuning, 545-550
parallel server database
data files, 537
data separation, 537
tables, 538
tablespaces, 538
tuning, 537-540
parallel server option (PSO), 521
PARALLEL_DEFAULT_MAX_SCANS, 565
PARALLEL_DEFAULT_SCANSIZE, 555, 565
PARALLEL_MAX_SERVERS, 372, 565, 570
PARALLEL_MIN_PERCENT, 372, 566
PARALLEL_MIN_SERVERS, 372, 565, 570
PARALLEL_SERVER_IDLE_TIME, 373, 565, 570
parameters
CREATE DATABASE, 317, 318
DEFAULT STORAGE for tablespaces, 333
documenting for recovery, 615
hidden, 379
INIT.ORA, 79, 358-379, 526, 541-545, 564, 841
INIT.ORA memory, 307
STORAGE for disk, 313
STORAGE for rollback segments, 342
STORAGE for tables, 345
TKPROF SORT, 394
VMS tuning, 761
parent-child relationship
reason for denormalization, 108, 109, 116
PARTITION_VIEW_ENABLED, 373
partition views, 559
partitioned views, 23, 128
partitioning a database for parallel server, 532-533, 541
Patrol, 850
PCM locks, 280, 526-528, 542, 546
pcode, 214
PCTFREE
for tables, 346
with disk chaining, 494
PCTINCREASE, 314
for tablespaces, 335
PCTUSED
for tables, 348
PCTUSED, for tables, 346
performance
checking during development, 104
client-server, 683
CPU, 38
data model, 88
database, 299, 443, 847
disk I/O, 35
long-running jobs, 49
memory, 33, 830
monitoring tools, 385
network, 40, 707
PL/SQL, 205
problems, 27
response time, 44, 782, 808
snapshot, 410
SQL, 133, 793
standards, 77-79
tables, 804, 806
Version 6, 17
Version 7, 17
very large database, 125
ping, 755
pinging, 535, 541-542, 548
PL/SQL
bind variables, 210
block structure, 206, 258
comparison with SQL, 206
debugging facilities, 214
efficiency, 205, 243
error handling, 212
function extensions, 260
inconsistencies, 217
limitations, 213
long-running jobs, 671
recursive subcalls, 211
statements allowed in blocks, 207
triggers, 209
tuning, 205
with client-server, 695
with SQL*Forms, 217, 243
PL/SQL 2.0
features, 775
PL/SQL 2.1
features, 776
PL/SQL 2.2
features, 777
PL/SQL 2.3
features, 779
PL/SQL areas, private, 305
PL/SQL wrapper, 258
PLAN_TABLE table, 397
planning stage
capacity planning, 722
questions about, 781
standards, 63
tuning issues, 10, 43
portability
Oracle, 722
PL/SQL, 208
precompilers, tuning for client-server, 706
primary keys, 90, 96
concatenated, 113
constraints, 118
indexes, 101
internal, 111
natural, 111
prime tables, 91
priorities, 39, 759
private SQL area, 305
PRIVATE_SGA, 274
Pro*C, 571, 706
Pro*COBOL, 706
deadlocks, 275
problems
CPU, 38
database, 29, 443
design, 28
disk I/O, 35, 826
long-running jobs, 49
memory, 33, 830
network, 40
PL/SQL, 205
programs, 29
response time, 44
sorting, 658
SQL, 133
system resources, 30
workload, 53
procedural capabilities, in PL/SQL, 205
procedures
overlaying, 235
stored, 121, 457, 801
stored with client-server, 701
stored with PL/SQL, 208, 233, 244
tuning, 237
products
shared executables, 479
tablespaces, 327
programming stage
tuning issues, 14, 133, 205
programming, modular, 71
protocols, 707, 708
ps, 753

Q
quality assurance (QA) stage, tuning issues, 15
queries
combining, 174
queues, semaphore-based, 748

R
RAID, 67
raw devices, 68, 537, 746
read consistency, 271
README files, 307
records, duplicate, 175
RECOVERY_PARALLELISM, 576
recursive
tables, 92
redo logs, 309, 318, 537, 636, 640, 839
contention, 291
database backup, 597
multiplexed, 320
tuning, 503, 839
reference tables, reason for denormalization, 112
referential integrity, 277-280, 793, 806
relational design, 88
definitions, 89
relationships
defining, 97
scope, 97
remote procedure calls (RPC), 209
replication
tables, 700
RESETLOGS, 637
resource profile limits, 357
resource profiles, 76, 273
RESOURCE_LIMIT, 307, 373
response time, 782, 787, 818
acceptable, 7, 48
degeneration over time, 824
deterioration overtime, 824
maintaining, 836
management, 44, 836
standards, 77
tuning, 808
retrieval path
SQL statements, 139
REVOKE, 215
ring topology, 688
RNPS, 253
rollback segments, 75, 837, 838
contention, 289, 500
creating, 337
locking, 271, 283
long-running jobs, 661
number, 339
optimal size, 338
parallel server, 538
shrinkage, 290
sizes, 342
tablespaces, 326
transactions waiting, 423
tuning checklist, 343
ROLLBACK_SEGMENTS
parallel server, 538
ROW_LOCKING, 272
ROWID
condition ranking, 140
retrievals, 165
rows, 90
counting, 175
deleting, 814
duplicate, 222
in blocks, 432
locks, 271, 284
rule-based optimizer, 564
RUNFORM, 479
running totals, reason for denormalization, 110

S
sar, 749, 751
SCN locks, 528
screens
layout, 69
pop-up, 70
scripts
custom, 427
database recovery, 601-628
lock detection, 292-296
Oracle, 424
Second Normal Form (2NF), 93
security
standards, 76
segment header block, 544
SELECT
FOR UPDATE OF clause, 284
FROM clause, 150
WHERE clause, 153
with client-server, 696
with parallel server, 536
with PL/SQL, 249
semaphore-based queues, 748
SEQUENCE_CACHE_ENTRIES, 374
server, 6, 684
multithreaded, 87, 469, 512
server-server
using PS/SQL, 209
session data, 469
SESSION_CACHED_CURSORS, 374
SESSIONS_PER_USER, 274
SET ROLE, 215
SET TRANSACTION USE ROLLBACK SEGMENT, 340, 661, 838
SGAPAD, 770
shared buffer pool, 214, 238, 241, 242, 304, 305, 616
shared packages
tuning, 242
SHARED_POOL_SIZE, 304, 305, 567, 615, 877
SHMMAX, 760
SHMSEG, 760
SHOW PARAMETERS, 379
sizes, 837
snapshots
BSTAT and ESTAT, 410
table, 700
software, selecting, 64
SORT_AREA_RETAINED_SIZE, 376, 566
SORT_AREA_SIZE, 34, 376, 562, 565, 567, 575, 879
long-running jobs, 658
reducing disk I/O, 486
use with temporary tablespaces, 324
SORT_DIRECT_WRITES, 564, 566, 575
SORT_READ_FAC, 377, 566
SORT_WRITE_BUFFER_SIZE, 377
SORT_SPACEMAP_SIZE, 378
sorting
disk I/O, 486
long-running jobs, 658
parallel query, 568
spin locks, 748
SPIN_COUNT, 291
SQL
adjusting statements over time, 203
comparison with PL/SQL, 206
context area, 305
dynamic, 798
hints, 807
last statement executed, 823
manual tuning, 156
performance, 793
sharing statements, 305, 457
tuning, 133, 427, 796, 797, 833
SQL areas, private, 305
SQL*DBA
MONITOR, 387, 834
SQL*Forms, 797
recording keystrokes, 46
with PL/SQL, 217, 243
SQL*Loader, 563, 575
SQL*Net, 824
comparison of versions, 824
polling the client connection, 273
tuning, 708
with parallel server, 530
SQL*Plus, 796
SQL_TRACE, 378, 388
SRP, 761
standards
database environment, 74
documentation, 73
duplicate rows, 222
nulls, 169
performance, 77-79
security, 76
SQL, 133
system, 63
star topology, 687
stats, 756
STORAGE parameters, 313
rollback segments, 342
stored functions, 121
use with packages, 257-258
use with PL/SQL, 208, 233, 244
stored packages, 121, 238-242, 801
client-server, 240
initialization section, 246
use with PL/SQL, 244
stored procedures, 121, 801
client-server, 236
overlaying, 235
use with packages, 257-258
use with PL/SQL, 208, 233, 244
stored triggers, 230
with deadlocks, 280
striping, 67, 326, 328, 560, 561, 563, 631
SUBSTR, 171
swap space, 730
swapping, 33, 750, 830, 849
sync.sql, 427
SYS.X$KCBCBH
monitoring buffer cache, 473
SYS.X$KCBRBH
monitoring buffer cache, 472
SYSDATE, 185
system administration
questions about, 848
tuning tips, 848-850
System Change Number (SCN) locks, 528
System Global Area (SGA), 302
indexes, 184
parallel server, 522, 526
shared buffer pool, 304, 456
UNIX tuning, 760
system tablespace, 322
system-assigned keys, reason for denormalization, 111

T
tables, 89
aliases, 138
associative, 92
code, 112
data loading, 575
defragmentation, 582
deleting rows, 814
dependent, 91
driving, 151, 204
dropping, 814
dynamic performance, 899, 923
extract, 668
history, 92
joins, 153, 178, 179
locks, 270, 286, 528
lookups, 177
name sequence for efficiency, 150
parallel server, 530
parallel server database, 538-540
performance, 804, 806
prime, 91
purging data, 129, 383, 559
recursive, 92
reference, 112
reorganization, 833
replication, 700
size estimates, 433
sizes, 345, 430, 833
sizes for long-running jobs, 650
sizes for temporary segments, 660
sorts in joins, 488
tablespaces, 323, 344
temporary, 660
TABLESPACE, 344
tablespace
defragmentation, 582
Tablespace Manager, 882
tablespaces
creating, 320
default, 75
free space, 609
indexes, 324, 352, 538
number of data files, 328
parallel server database, 538
products, 327
recommended, 322
rollback segments, 326
striped, 326, 328
structure, 75
system, 322
tables, 323, 344
tablespaces (continued)
temporary, 324, 538, 562
user, 326, 357
TCP/IP, 709
temporary
segments, 492, 538, 660
tables for long-running jobs, 660
tablespaces, 324, 538, 562
Third Normal Form (3NF), 93
thrashing, 38, 830
TIMED_STATISTICS, 378, 389, 394
with BSTAT and ESTAT, 412
TKPROF, 391, 574, 735, 799
listing format, 393
SORT parameters, 394
topology
bus, 688
hybrid, 689
network, 686
star, 687
token ring, 687, 688
trace mode, 795
Transaction Processing Option (TPO), 271
transactions
concurrent access to, 269
locks, 529
triggers, 802, 820
mutating, 280
PL/SQL, 209
restrictions, 120
stored with client-server, 701
stored with deadlocks, 280
tuning, 230
WHEN clause, 78
TRUNC, 172
TRUNCATE, 283
tuned system, example, 33
tuning, 802
amount, 24
analysis questions, 786, 792
analysis stage, 12
case studies, 903
client-server, 683, 818
CPU, 38, 725
data model, 88
database, 299, 443, 726
database administration, 16, 299, 808-848
database administration questions, 808
database procedures, 237
design questions, 786, 792
design stage, 12
development questions, 793
development stage, 14
dictionary, 437
different roles, 8
disk I/O, 35, 309, 480, 724, 849
disk I/O checklist, 309
disk I/O in UNIX, 749
disk I/O in VMS, 762
exercise for capacity planning, 723
financial benefits, 5
Financials, 857
human benefits, 7
I/O spread, 421
indexes, 98
life cycle, 10
long-running jobs, 49
management, 10, 43
management questions, 781
manual of SQL statements, 156
memory, 302, 453, 724, 810, 849
memory checklist, 306
memory in UNIX, 750
memory in VMS, 765
monitoring stage, 16
multi-user, 737
network, 40, 707, 726
network in UNIX, 754
Oracle Forms, 899
parallel query, 570-574
parallel server, 545-550
parallel server database, 537-540
PL/SQL, 205
planning questions, 781
planning stage, 10, 43
precompilers, 706
programming questions, 793
quality assurance (QA) stage, 15
questions about, 781
response time, 44, 808
rollback segment checklist, 343
shared packages, 242
single-user, 734
SQL, 133, 427, 796, 797
SQL*Net, 708
system administration, 17, 848-850
system administration questions, 848
tips, 781-850
tools, 385
triggers, 230, 802
UNIX, 745
very large databases, 381-383
views, 176
VMS, 761
workload, 53
tuning service agreements, 7, 48
TWO_TASK, 709
TYPE definition
in PS/SQL, 209

U
UNION
ALL, 126, 540
in SQL, 182
sorts, 488
UNIQUE, 99, 140
unique keys
constraints, 118
unitary architecture, 85
unitary processing, contrast with client-server, 684, 698
UNIX
monitoring commands, 748
network, 686
parallel server, 537
tuning, 745
untuned system, example, 33
USER, 185
user tablespaces, 326
USER_DUMP_DEST, 378, 389
users
creating, 357
tablespaces, 357
utlbstat.sql, 410
utlchain.sql
monitoring chained rows, 663
utlestat.sql, 410
uuencode, 40

V
V$ tables, 843
V$BH, 546
V$CACHE, 547
V$CACHE_LOCK, 548
V$DISPATCHER
monitoring multithreaded server contention, 512
V$FALSE_PING, 548
V$LATCH
monitoring redo log latch contention, 508
V$LIBRARYCACHE
monitoring free space, 456
V$LOCK_ACTIVITY, 549
V$LOCK_ELEMENT, 548
V$LOCKS_WITH_COLLISIONS, 548
V$PING, 547
V$PQ_SESSTAT, 571
V$PQ_SLAVE, 571
V$PQ_SYSSTAT, 570-572
V$QUEUE
monitoring shared server contention, 513
V$SQLAREA
monitoring the context area, 456, 459
V$SYSSTAT, 550
monitoring chained rows, 662
monitoring memory and disk activity, 487
monitoring performance, 473
monitoring recursive calls, 490
VALIDATE INDEX, 352
variables
bind, 137, 305, 458, 696
PL/SQL local, 209, 216
stored package, 245-??, 246
Version 6
performance, 17
version control, 73
in PL/SQL, 255
views, 820
creating, 356, 540, 820
partitioned, 23
tuning, 176
VMS
identifying bottlenecks, 762
tuning, 761
tuning parameters, 761
vmstat, 750
VMTUNE, 756

W
WHEN, 78
WHERE, 556, 807
efficiency, 153, 175
indexes not used in some cases, 171
sequence, 153
wide-area network, 690
windows
pop-up in SQL*Forms, 70
WNDS, 253
WNPS, 253
workload, 53
World Wide Web, 793
wrapping a PL/SQL block, 258
X
X$ tables, 845
xplainpl.sql, 397

Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star

(0)

4 Star

(0)

3 Star

(0)

2 Star

(0)

1 Star

(0)
    If you find inappropriate content, please report it to Barnes & Noble
    Why is this product inappropriate?
    Comments (optional)