Oracle Performance Tuning

Description

• Copyright 2002
• Dimensions: 7-3/8x9-1/4
• Pages: 432
• Edition: 1st

• Book
• ISBN-10: 0-672-32146-7
• ISBN-13: 978-0-672-32146-7

This book is designed to enhance the skills of the Oracle DBA by giving them knowledge and examples on how to improve their system's performance. Readers will learn how to: understand how to use trace files, the alert log file and event sets to assist with tuning; become familiar with the new features of Oracle9i.; understand and be able to use utilities and dynamic performance views to assist with tuning; understand how to tune applications using index structures and how to perform SQL statement tuning.; tune SQL Statements Efficiently; read Alert Logs and Trace Files; tune the Shared Pool; tune the Buffer Cache; use Multiple Buffer Caches; tune a Multi-Threaded Server; configure and tune the storage subsystem that Oracle is using, and use Oracle in a SAN environment.



Sample Content

Table of Contents

(NOTE: Each chapter concludes with a Summary.)

Introduction.

I. INSTANCE TUNING.

1. Tuning Fundamentals.

Tuning Concepts.

What Is Tuning?

Do You Need to Tune?

When Have You Tuned Enough?

Tuning Goals.

Tuning for Throughput.

Tuning for Response Time.

Tuning for Large Numbers of Users.

Tuning for Fault Tolerance.

Tuning for Load Time.

Tuning Methodology.

Examine the Problem.

Determine the Problem.

Determine the Solution and Set Goals.

Test the Solution.

Analyze the Results.

What Affects Oracle Server Performance?

System Bottlenecks.

System Tuning.

System Limitations.

Sizing and Capacity Planning.

Sizing Versus Capacity Planning.

Sizing Steps.

Capacity Planning Steps.

2. Using the Oracle Configuration Parameters.

Starting the Oracle Instance.

Connect to Oracle.

Using the STARTUP Command to Start the Instance.

Starting the Instance.

Troubleshooting the Instance Startup.

Stopping the Oracle Instance.

The Initialization Parameters.

Instance Tuning Parameters.

Resource Limiting Parameters.

3. Tuning the Oracle Instance.

Initialization Parameters Used in This Chapter.

SGA Parameters.

Program Global Area and User Memory Parameters.

Undo Parameters.

Miscellaneous Parameters.

Tuning the SGA.

Tuning the Operating System.

Tuning the Private SQL and PL/SQL Areas.

Tuning the Shared Pool.

Tuning the Buffer Cache.

Managing Rollback Segments and Undo Information.

Tuning Undo in SMU Mode.

Tuning Undo in RBU Mode.

Summary of Undo Tuning.

Checking for Redo Log Buffer.

Redo Log Buffer Contention.

Tuning Checkpoints.

Optimizing Sorts.

Tuning the Sort Area.

Tuning the Sort Area Retained Size.

Tuning the Temporary Tablespace.

Minimizing Free List Contention.

4. Performance-Enhancing Features.

Parameters That Are Used in This Chapter.

Indexes.

Index Concepts.

Index Types.

B*-Tree Indexes.

How the Oracle Bitmap Index Works.

What to Index.

How to Index.

Parallel Execution in Oracle.

Parallel Query Processing.

Parallel Index Creation.

Parallel Loading.

Parallel Recovery.

Clusters.

Hash Clusters.

When to Hash.

Multiblock Reads.

Partitioning.

Partitioning Concepts.

Range Partitioning.

List Partitioning.

Hash Partitioning.

Composite Partitioning.

Benefits of Partitioning.

Partitioning and Indexes.

Plan Stability.

Multithreaded Server.

Dedicated Server.

Multithreaded Server.

5. Tuning Workloads.

Parameters Used in This Chapter.

Using Resource Consumer Groups.

Overview of Resource Consumer Groups.

Configuring Resource Consumer Groups.

Adding Users to the Plan.

Monitoring the Consumer Groups.

Tuning User Resources.

OLTP Systems.

Response Time.

Relocating Functions to Different Systems.

Distributing Historical Reports.

Distributing Online Reports.

Application Support for Distributed Systems.

6. The Oracle Performance Views.

The V$ Views Versus the G$ Views.

Overview of the Dynamic Performance Views.

Using the Dynamic Performance Views.

Using Queries to Access the Dynamic Performance Views.

Using UTLBSTAT/UTLESTAT and Statspack.

Using Performance-Monitoring Tools.

7. Using UTLBSTAT and UTLESTAT.

UTLBSTAT/UTLESTAT.

Running UTLBSTAT/UTLESTAT.

The UTLBSTAT/UTLESTAT Output File.

Interpreting BSTAT/ESTAT Statistics.

Statspack.

Installing Statpack.

Running Statspack.

Administering Statspack.

Statspack Results.

II. Oracle Hardware Topics.

8. Oracle and System Hardware.

Parameters Used in This Chapter.

Overview of the Oracle Instance.

The Oracle Memory Structure.

Processes.

System Architecture Overview.

CPUs and Caches.

CPU Design.

32-Bit Versus 64-Bit Processors.

System Memory Architecture.

Bus Design.

I/O Bus.

Network.

To Cluster or Not to Cluster.

9. I/O Concepts.

The Disk Drive.

Overview.

Disk Drive Performance.

RAID Disk Subsystems.

Hardware Versus Software RAID.

Striping.

RAID 0.

RAID 1.

RAID 10.

RAID 2.

RAID 3.

RAID 4.

RAID 5.

Parity Overview.

RAID Performance Overview.

RAID Controller Performance Features.

Elevator Sorting.

Controller Caches.

Hardware XOR Engines.

Stripe Size.

Internal Versus External RAID Systems.

Internal RAID Systems.

External RAID Systems.

SAN Systems.

Network Attached Storage (NAS) Systems.

10. Oracle and I/O.

Parameters Used in This Chapter.

Oracle's Dependency on I/O.

Why Read Latency Is Important.

Write Latency.

Fault Tolerance.

Configuring the I/O Subsystem for Oracle.

Performance Versus Fault Tolerance Versus Cost.

Protecting Your Investment.

Tuning I/O.

Understanding Disk Contention.

Identifying Disk Contention Problems.

Solving Disk Contention Problems.

Reducing Unnecessary I/O Overhead.

Migrated and Chained Rows.

Dynamic Extensions.

PCTFREE and PCTUSED Command Options.

A Review of I/O Reduction Techniques.

Block Size.

Using Multiple Block Sizes.

Fragmentation.

III. Application and SQL Tuning.

11. Using EXPLAIN PLAN and SQL Trace.

SQL Trace.

SQL Trace Initialization.

Controlling SQL Trace.

SQL Trace Functionality.

TKPROF Functionality.

Interpreting SQL Trace.

The EXPLAIN PLAN Command.

EXPLAIN PLAN Initialization.

Invoking EXPLAIN PLAN.

Extracting EXPLAIN PLAN Results.

Registering Applications.

12. Index Tuning.

Parameters Used in This Chapter.

Index Types.

Using the B*-Tree Index.

What Should Be Indexed.

Maintaining the Index.

The Index Organized Table (IOT).

The Bitmap Index.

When to Use a Bitmap Index.

Function-Based Indexes.

Using Hints.

Monitoring and Analyzing Indexes.

ALTER INDEX MONITORING USAGE.

13. The Oracle Optimizer.

Understanding the Optimizer.

How the Optimizer Works.

Optimizer Initialization Parameters.

Optimization Methods.

Using the DBMS_STATS Package.

Creating a Statistics Table.

Gathering Table Stats.

Deleting Statistics.

Restoring Statistics.

Other DBMS_STATS Package Functions.

Working with Statistics.

Using the ANALYZE Command.

How to Run the ANALYZE Command.

Data Dictionary Statistics.

Transaction Processing.

SQL Statement Processing.

Cursor Creation.

Statement Parsing.

Query Processing.

Bind Variables.

Statement Execution.

Parallelization.

Fetch Rows to Be Returned.

Analyzing SQL Statements.

Designing New SQL Statements.

Packages, Procedures, and Functions.

Using Hints.

14. Tuning SQL.

Optimal SQL Statements.

How to Identify Poorly Tuned SQL Statements.

Join Types.

Nested Loops Join.

Merge Join.

Hash Join.

Tuning SQL Statements.

Tuning an Existing Application.

Designing a New Application.

15. Using Hints.

Implementing Hints.

Hint Syntax.

Hint Errors.

Using Multiple Hints.

Hints.

Optimization Approaches.

Access Methods.

Join Orders.

Join Operations.

Parallel Query Hints.

Query Transformation.

Miscellaneous Hints.

IV. Advanced Topics.

16. Oracle9i Real Application Clusters.

Overview of RAC.

Computer Systems.

Shared Disk Subsystem.

Server Interconnect.

Locking.

Cluster Configuration.

When to Use RAC.

Tuning RAC.

Configuration and Sizing.

Instance and Lock Tuning.

Application Tuning.

17. Tuning Backup and Recovery.

Parameters Used in This Chapter.

Oracle Operational Review.

Backup Process.

Recovery Process.

Characteristics of the Oracle Backup Process.

Cold (Offline) Backup Using User-Managed Backups.

Hot (Online) Backup Using User-Managed Backups.

Hot (Online) Backup Using the RMAN Utility.

Hot (Online) Backup Using Storage Area Network Features.

Data Access Patterns During User-Managed Backups.

Data Access Patterns During RMAN Backups.

System Load During Backup.

Backup Goals.

System Design Considerations.

Cold Database Backup.

Hot Database Backup.

Tuning Considerations Using User-Managed Backups.

Tuning Considerations Using RMAN Backups.

The RMAN Buffers.

Asynchronous Versus Synchronous I/O.

RMAN Tuning Parameters.

Monitoring RMAN.

System Enhancements to Improve Backup Performance.

CPU Enhancements.

I/O Enhancements.

Network Enhancements.

Split Up the Backup.

Performance Verification.

What to Test in the RDBMS.

What to Test in the OS.

18. Creating a High-Performance Disaster Survival System.

Parameters Used in This Chapter.

Why Plan for a Disaster?

Disaster Survival Concepts.

Remote Mirroring.

Oracle9i Data Guard.

Replication.

Planning for a Disaster.

Planning Steps.

Documentation.

Scenarios.

Recovering from a Disaster.

Tuning Standby Systems.

19. Oracle Networking Performance.

Network Architecture.

Hardware Components.

Network Protocols.

Tuning the Network Components.

Software Tuning.

Oracle Tuning.

Network Design.

Bandwidth Considerations.

Segmenting the Network.

Bridges, Routers, and Hubs.

Index

Preface

Each year, computer systems become faster. At the same time, memory and disk storage are becoming more plentiful and less expensive. Because of this, the means and the budget are now available to store vast amounts of information at relatively inexpensive prices. This has allowed users to increase the amount of data stored as well as increase the amount of processing that is done with that data. Because of this, society cannot simply solve performance problems by throwing more hardware at the problem. It's necessary to analyze, size, and properly configure systems to perform optimally.

For us, performance engineers, one of the problems that we see in the industry today is the introduction of large disk drives. At one time, it would have been necessary to configure 10 disk drives to hold the data that you have generated; you can now store the same data in 1 disk drive. What many people don't know is that this 1 disk drive is 10 times slower than 10 smaller disk drives. In this book, concepts such as this will be explained and explored.

Our philosophy in writing this book is that tuning must be done holistically. You cannot focus on one part of the system and achieve optimal performance. It is necessary to look at the application, the Oracle instance, the operating system, and the hardware and I/O subsystem to achieve optimal performance. In this book, we explore all areas of the system from SQL statements down to the operation of the disk drive.

We hope that you will use this book not only as a guide for learning Oracle performance tuning, but as a reference as well.

How to Use This Book

To keep the book interesting, we have added some personal anecdotes relevant to the subject matter. We hope we have conveyed some of the excitement that comes when you push systems to their limits. Those of us who work in the database performance field constantly push the envelope of technology to achieve new levels of performance previously thought impossible. This kind of experimentation can be satisfying when everything works well, but frustrating when it doesn't.

Our hope is that, having read this book, you will have a basic understanding of how the components of the system work together to form the whole. If you have this foundation, you should be able to tackle a performance problem, know what to look for, and know how to fix it. Not all performance problems are alike, and solutions aren't either. It is important that you have a basic understanding of what to look for and what the possible solutions are.

If we have done our jobs correctly, you should finish this book with the ability to analyze the problem, hypothesize a solution, test that solution, and understand the result. We hope this book gives novices an idea of what performance engineering is all about. Seasoned professionals should receive new insight and ideas. By applying this newly acquired knowledge, you will find that tackling even the most complex performance problems can be broken down into simple, manageable tasks.

0672321467P04222002

Index

Symbols

• + signs

A

• accesses, performance view
• active processes
• aggregation
• alert log
  • initialization parameter problems
  • startup troubleshooting
• ALL_ROWS hints
• ALTER SESSION command
• ANALYZE command
  • capabilities
  • chained rows
  • estimated statistics
  • exact statistics
  • integrity of data
  • modes available
  • obsolete soon
  • running
  • structural integrity
• AND_EQUAL hint
• append mode hints
• ARCH processes
• archive log files
  • ARCHIVELOG mode
  • Archivelog process
  • Data Guard
  • LOG_ARCHIVE_DEST initialization parameter
  • RAID 10 recommended
  • recoveries, role in
• asynchronous I/O
• Automatic Undo Management mode

B

• B*-Tree indexes
  • branch blocks
  • branch nodes
  • composite
  • data storage in
  • defined
  • function-based
  • IOTs
  • leaf blocks
  • leaf nodes
  • nonunique
  • root nodes
  • search method
  • segments
  • unique
  • using
• backdoors, creating
• background processes
• background system-wide wait events
• BACKGROUND_CORE_DUMP parameter
• BACKGROUND_DUMP_DEST parameter
• backup and recovery performance
• backups
  • archive log files
  • ARCHIVELOG mode
  • asynchronous I/O
  • BACKUP_TAPE_IO_SLAVES parameter
  • block size
  • bottlenecks, hardware
  • buffering
  • checkpoints
  • clones
  • cold
  • component performance characteristics, table of
  • compression
  • control files
  • CPUs, enhancing
  • data access, physical
  • Data Guard
  • DBWR_IO_SLAVES parameter
  • disaster preparation. See disaster survival systems
  • export
  • full
  • goals for
  • hardware considerations
  • header information
  • hot
  • I/O subsystem
  • initialization parameters
  • isolated links recommended
  • large pool configuration
  • network bandwidth
  • network device speed
  • network segmentation
  • offline
  • online
  • optimizations available
  • OS performance
  • performance considerations
  • performance verification
  • planning
  • process of
  • quiescing databases
  • RAID
  • recovery from. See recovery
  • remote mirroring
  • replication
  • RMAN
  • SANs for
  • scheduling
  • sequential access
  • snapshots
  • software for, choosing
  • software overhead
  • splitting up
  • staging areas
  • synchronous I/O
  • system enhancements
  • system load during
  • tablespaces
  • tape device speed
  • temporary backup volumes
  • testing
  • workload tuning
• badly-tuned SQL statements
• Balanced Tree Indexes. See B*-Tree indexes
• bandwidth
• binding variables
• bitmap indexes
  • advantages of
  • defined
  • design criteria
  • hints for
  • selectivity
  • structure of
  • using
• block contention
• block size
• bottlenecks
  • defined
  • existing application SQL statements
  • finding
  • frequently occurring areas
• branch blocks
• branch nodes
• bridges
• BSTAT/ESTAT (UTLBSTAT/UTLESTAT)
  • baselines
  • buffer busy wait statistics
  • cache buffers LRU chain latch statistics
  • cache statistics
  • consistent changes
  • consistent gets
  • database statistics
  • Db block gets
  • defined
  • dictionary cache statistics
  • dirty buffers inspected
  • enqueue statistics
  • free buffer wait statistics
  • free buffers inspected
  • hot buffers moved
  • I/O statistics
  • latch statistics
  • library cache statistics
  • logical reads
  • measurement intervals
  • output files
  • overall statistics
  • overhead from running
  • parameters, listing
  • physical reads
  • redo statistics
  • report.txt
  • rollback segments statistics
  • running
  • scripts
  • sort statistics
  • system-wide wait events statistics
  • TIMED_STATISTICS
  • UTLBSTAT.sql
  • UTLESTAT.sql
  • version information
• buffer busy waits statistics
• buffer cache
  • block size
  • DB_BLOCK_BUFFERS parameter
  • DB_CACHE_SIZE parameter
  • Default Buffer Pool
  • defined
  • free buffer wait statistics
  • hit ratio
  • importance of
  • Keep Buffer Pool
  • multiple buffer pools
  • purpose of
  • Recycle Buffer Pool
  • statistics
• buffer pools
• bus design

C

• cache buffers LRU chain latches
• cache fusion
• cache hit ratio statistics
• caches
  • buffer. See buffer cache
  • buffer parameters
  • CACHE hints
  • CPU
  • data dictionary. See data dictionary cache
  • hints for
  • hit ratio
  • importance of
  • library. See library cache
  • miss rate
  • performance view
  • PINS
  • reloads
  • row
  • shared pool
  • SQL statements, caching
  • statistics gathering
• capacity planning
  • Capacity Planner
  • defined
  • steps in
• chaining
• CHAR data type
• checkpoints
• CHOOSE hints
• CISC processors
• CKPT processes
• clean blocks
• cluster buckets
• CLUSTER hint
• cluster indexes
• clustering. See RAC (Real Application Clusters)
• cold backups
• columns
  • indexing
  • views of statistics
• comments
• COMPATIBLE parameter
• composite indexes
• composite partitioning
• configuration parameters. See initialization parameters
• connections
  • creating
  • defined
  • OPEN_LINKS parameter
  • transaction stage
• consistent mode
• consumer groups. See resource consumer groups
• contention
  • data contention, IOPS
  • disk. See disk contention
  • freelist
  • parallel execution
• CONTROL_FILES parameter
• cost-based optimization
• CPUs
  • 32 bit versus 64 bit
  • caches
  • CISC processors
  • designs of
  • MPP (Massively Parallel Processing)
  • multiprocessor systems
  • performance of
  • RISC processors
  • SMP (Symmetric Multiprocessor) systems
• current instance, performance view
• current mode
• cursors
  • creation
  • CURSOR_SHARING_EXACT hints
  • CURSOR_SPACE_FOR_TIME parameter
  • OPEN_CURSORS parameter
  • performance view
  • purpose of
  • SESSION_CACHED_CURSORS parameter
• customer feedback

D

• data blocks
  • buffer busy waits
  • caching
  • free space parameters
• data contention, IOPS, calculating
• data dictionary cache
  • hit ratio
  • performance view
  • purpose of
  • statistics on
• Data Guard
• data integrity, ANAYLYZE command
• data scrubbing
• Database Writer (DBWR) write latency
• DBMS_STATS package
  • creating statistics tables
  • database statistics
  • DELETE_TABLE_STATS function
  • GATHER_TABLE_STATS function
  • IMPORT_TABLE_STATS function
  • index statistics
  • purpose of
  • restoring statistics
  • system statistics
  • table statistics
  • using
• DBWR processes
• DBWR_IO_SLAVES parameter
• DB1K_CACHE_SIZE parameter
• DB_BLOCK_BUFFERS parameter
• DB_BLOCK_CHECKING parameter
• DB_BLOCK_CHECKSUM parameter
• DB_BLOCK_SIZE parameter
• DB_CACHE_ADVICE parameter
• DB_CACHE_SIZE parameter
• DB_FILES parameter
• DB_FILE_MULTIBLOCK_READ_COUNT parameter
• DB_KEEP_CACHE_SIZE parameter
• DB_NAME parameter
• DB_RECYCLE_CACHE_SIZE parameter
• DB_WRITER_PROCESSES parameter
• dedicated server process
• Default Buffer Pool
• definition of tuning
• degree of parallelism
• deletes
• DELETE_TABLE_STATS function
• determination phase
• dictionary cache. See data dictionary cache
• dirty blocks
• dirty buffers
• disaster survival systems
  • advantages of
  • allowable downtime
  • bandwidth considerations
  • concepts in
  • criticality of data
  • Data Guard
  • design factors
  • documentation
  • downtime vs. recoverability
  • hardware documentation
  • I/O capacity
  • importance of
  • initialization parameters for
  • Internet connectivity
  • log shipping via tape
  • offsite backup
  • physical locations
  • physical security
  • planning
  • reasons to use
  • recovery
  • remote mirroring
  • replication
  • software documentation
  • Standby Database
  • tuning
• disk bound systems
• disk contention
  • defined
  • eliminating non-Oracle I/Os
  • hardware striping
  • identifying problems
  • index separation
  • isolating sequential I/Os
  • Oracle striping
  • OS striping
  • problem solving rules
  • RAID
  • random I/Os
  • random vs. sequential I/O
  • statistics
  • striping
• disk drives
  • components of
  • contention
  • cylinders
  • disk bound systems
  • full disk seeks
  • heads
  • hot spots
  • I/O capacity
  • I/O controller latency
  • knee of the curve
  • limitations, designing around
  • operating mechanism
  • parallel processing considerations
  • performance measures
  • platters
  • queuing
  • RAID. See RAID (Redundant Array of Inexpensive Disks)
  • random I/O
  • read latency
  • rotational latency
  • sectors
  • seeks
  • sequential I/O
  • specification sheets
  • statistics, 141,
  • track-to-track seeks
  • write latency
• disk fragmentation
• disk striping
• DISK_ASYNCH_IO
• dispatchers, performance view
• DLM (Distributed Lock Manager)
• Dnnn processes
• documentation for disaster recovery planning
• drives. See disk drives
• DRIVING_SITE hints
• DSS systems
• dynamic extensions
• Dynamic Performance Views. See Oracle Dynamic Performance Views

E

• elapsed time statistic
• elevator sorting
• enqueues
• ENQUEUE_RESOURCES parameter
• Ethernet
• evaluating tuning achievement
• execution plans
  • displaying. See EXPLAIN PLAN command
  • optimization. See Optimizer, Oracle
  • plan stability feature
• execution time
• existing applications
  • bottlenecks
  • broken
  • capacity problems
  • effects of SQL statements
  • EXPLAIN PLAN command
  • familiarization phase
  • optimizable
  • Parallel Query option
  • problem analysis
  • scheduling changes
  • SQL statements
  • SQL Trace analysis
  • steps for tuning
• EXPLAIN PLAN command
  • existing application SQL statements
  • initialization
  • invoking
  • purpose of
  • results, extracting
  • SQL statements covered
  • table definition
  • TKPROF, running from
  • using results from
• export backups

F

• FACT hints
• Fail Safe
• failovers
  • controllers
  • Fail Safe
  • vs. clustering
• false pings, performance view
• fault tolerance. See also RAID
  • best solution
  • budget solution
  • cost vs. performance
  • defined
  • exceptions to guidelines
  • goals for tuning
  • good solution
• file I/O performance view
• file I/O statistics
• file pings performance view
• FIRST_ROWS hint
• fragmentation
• free buffer waits statistics
• freelist contention
• full backups
• full disk seeks
• FULL hints
• function-based indexes

G

• G$ views
• goals, tuning
  • fault tolerance
  • instance tuning
  • number of users
  • operating system tuning
  • Optimizer, specifying for
  • response time
  • setting
  • throughput
• groups. See resource consumer groups

H

• hard drives. See disk drives
• hardware
  • busses. See bus design
  • initialization parameters affecting, list of
  • networks
  • processors. See CPUs
  • RDBMS optimization
  • routers
• hardware striping
• hashes
  • hash clusters
  • HASH_AJ, MERGE_AJ, NL_AJ hints
  • HASH_AREA_SIZE parameter
  • HASH hints
  • hash joins
  • HASH_JOIN_ENABLED parameter
  • Hash Partitioning
  • HASH_SJ, MERGE_SJ, NL_SJ hints
  • hashing, bypassed, problem with
• HBAs (host bus adapters)
• header information
• hints
  • + signs
  • access methods
  • advantages of
  • ALL_ROWS
  • AND_EQUAL
  • APPEND
  • approaches to optimization
  • CACHE
  • CHOOSE
  • CLUSTER
  • comment types
  • compound queries with
  • conditions indicating need for
  • conflicting
  • cost-based optimization
  • CURSOR_SHARING_EXACT
  • DRIVING_SITE
  • errors in
  • FACT
  • FIRST_ROWS
  • FULL
  • full table scans
  • HASH
  • HASH_AJ, MERGE_AJ, NL_AJ
  • HASH_SJ, MERGE_SJ, NL_SJ
  • implementing
  • INDEX
  • INDEX_ASC
  • INDEX_COMBINE
  • INDEX_DESC
  • INDEX_FFS
  • INDEX_JOIN
  • indexes
  • join operations
  • join orders
  • join predicates
  • LEADING
  • MERGE
  • multiple
  • NOAPPEND
  • NOCACHE
  • NOPARALLEL
  • NOPARALLEL_INDEX
  • NOREWRITE
  • NO_EXPAND
  • NO_FACT
  • NO_INDEX
  • NO_MERGE
  • NO_PUSH_PRED
  • NO_UNNEST
  • ORDERED
  • ORDERED_PREDICATES
  • overview of
  • PARALLEL
  • parallel execution
  • parallel queries
  • PARALLEL_INDEX
  • PQ_DISTRIBUTE
  • PUSH_PRED
  • PUSH_SUBQ
  • query transformation
  • response time
  • ROWID
  • RULE
  • rules-based optimization
  • STAR
  • STAR_TRANSFORMATION
  • subqueries
  • syntax
  • throughput optimization
  • UNNEST
  • USE_CONCAT
  • USE_HASH
  • USE_MERGE
  • USE_NL
  • views
• historical reports
• hot backups
• hot buffers
• hot spots
• hubs

I

• I/O (input/output). See also I/O subsystem
  • parallel execution
  • random
  • reducing using indexes
  • reduction techniques
  • sequential
  • statistics using BSTAT/ESTAT
  • statistics using Statpack
• I/O bound systems
• I/O bus
• I/O subsystem
  • best solution
  • budget solution
  • chaining
  • configuring for performance
  • dependence on
  • design considerations
  • drives. See disk drives
  • dynamic extensions
  • good solution
  • initialization parameters
  • migrating
  • overloading of
  • PCTFREE
  • PCTUSED
  • performance vs. fault tolerance and cost
  • read latency
  • write latency
• IFILE parameter
• immediate-gets latches
• index clusters
• INDEX hints
• Index-Organized Tables (IOTs)
• index segments
• indexes
  • advantages of
  • analyzing
  • avoiding
  • B*-Tree. See B*-Tree indexes
  • back-of-the-book
  • benefits of
  • bitmap. See bitmap indexes
  • branch blocks
  • clusters
  • column selection
  • columns, indexing
  • compacting
  • composite
  • creating
  • data reduction with
  • defined
  • deletes, effects of
  • design considerations
  • disk contention, minimizing
  • EXPLAIN PLAN command
  • fragmentation
  • function-based
  • guidelines for creating
  • hints for
  • inserts, effects of
  • IOTs
  • joins
  • leaf blocks
  • maintenance
  • monitoring
  • nodes
  • NOLOGGING option
  • nonunique
  • not used, problem of
  • OPTIMIZER_INDEX_CACHING parameter
  • OPTIMIZER_INDEX_COST_ADJ parameter
  • overhead from
  • overindexing
  • Parallel Index Creation feature
  • parallelism
  • partitioning
  • purpose of
  • RAC
  • rebuilding
  • reducing I/Os with
  • scans, specifying use
  • segments
  • selectivity
  • speeding creation of
  • statements causing performance penalties
  • tables, of
  • testing
  • transparency of
  • types of
  • unique
  • updates, effects of
  • views of statistics
  • when to create
• INDEX_ASC hints
• INDEX_COMBINE hints
• INDEX_DESC hints
• INDEX_FFS hints
• INDEX_JOIN hints
• init.ora. See initialization
• initialization
  • alert log, troubleshooting with
  • asynchronous I/O
  • file for
  • resource limiting parameters
  • parameters. See initialization parameters
• initialization parameters
  • BACKGROUND_CORE_DUMP
  • BACKGROUND_DUMP_DEST
  • backups
  • block size cache
  • buffer cache
  • COMPATIBLE
  • CONTROL_FILES
  • DB1K_CACHE_SIZE
  • DB_BLOCK_CHECKING
  • DB_BLOCK_CHECKSUM
  • DB_BLOCK_SIZE
  • DB_CACHE_SIZE
  • DB_FILE_MULTIBLOCK_READ_COUNT
  • DB_FILES
  • DB_NAME
  • DB_WRITER_PROCESSES
  • DBWR_IO_SLAVES
  • disaster survival systems
  • DISK_ASYNCH_IO
  • I/O subsystem
  • IFILE
  • indexing related
  • INSTANCE_GROUPS
  • LARGE_POOL_SIZE
  • LICENSE_MAX_SESSIONS
  • LICENSE_MAX_USERS
  • listing current
  • LOG_ARCHIVE_DEST
  • MAX_DISPATCHERS
  • MAX_DUMP_FILE_SIZEs
  • MAX_SHARED_SERVERS
  • MTS
  • MTS_DISPATCHERS
  • MTS_MAX_DISPATCHERS
  • MTS_MAX_SERVERS
  • MTS_SERVERS
  • OPEN_LINKS
  • OPEN_LINKS_PER_INSTANCE
  • Oracle Optimizer
  • parallel execution
  • PARALLEL_ADAPTIVE_MULTI_USER
  • PARALLEL_AUTOMATIC_TUNING
  • PARALLEL_BROADCAST_ENABLED
  • PARALLEL_EXECUTION_MESSAGE_SIZE
  • PARALLEL_MAX_SERVERS
  • PARALLEL_MIN_PERCENT
  • PARALLEL_MIN_SERVERS
  • PARALLEL_THREADS_PER_CPU
  • PARTITION_VIEW_ENABLED
  • PGAs
  • PROCESSES
  • RECOVERY_PARALLELISM
  • RESOURCE_LIMIT
  • SESSIONS
  • SGAs
  • standard block size
  • STARTUP commands
  • TIMED_STATISTICS
  • TRANSACTIONS
  • UNDO_MANAGEMENT
  • undos
  • USER_DUMP_DEST
  • writer processes
  • workload tuning
• INSERT activity
• inserts
  • indexes, effects on
  • PCTFREE/PCTUSED, setting
• instances
  • activity statistics
  • checking connection
  • components of
  • connecting
  • memory structure
  • Oracle. See Oracle instances
  • SGA. See SGA (System Global Area)
  • shutting down
  • starting
  • STARTUP command
  • startup event sequence
  • troubleshooting startup
• INSTANCE_GROUPS parameter
• IOPS, calculating
• IOTs (Index Organized Tables)

J

• joins
  • clusters
  • hash
  • hints for
  • indexing
  • merge
  • nested loop
  • orders, hints for
  • read latency

K

• Keep Buffer Pool
• key generation

L

• L1 cache
• L2 cache
• large numbers of user, tuning for
• large pool
  • backups, configuring for
  • defined
  • LARGE_POOL_SIZE parameter
  • MTS requirements
  • using
• latches
  • block size, effect on
  • contention
  • gets
  • hit ratios
  • immediate-gets
  • Keep Buffer Pool
  • LRU
  • misses
  • nowait statistics
  • performance view
  • Recycle Buffer Pool
  • redos
  • sleeps
  • statistics on
  • willing-to-wait
• latency
• LCKn processes
• LEADING hints
• leaf blocks
• leaf nodes
• LGWR (Log Writer) processes
• library cache
  • components of
  • defined
  • hit ratio
  • performance view
  • shared SQL area
  • SQL Trace report on misses
  • statistics
• LICENSE_MAX_SESSIONS parameter
• LICENSE_MAX_USERS parameter
• list buffer busy waits
• List Partitioning
• load time, tuning for
• local tables, advantages of
• lock manager, performance view
• locks
  • cluster system. See PCM (Parallel Cache Management) locks
  • DLM
  • LOCK_SGA parameter
  • performance view
• log buffer. See redo log buffer
• log switches, checkpoint creation by
• Log Writer processes (LGWR)
• LOG_ARCHIVE_DEST parameter
• LOG_BUFFER parameter
• LOG_CHECKPOINT_INTERVAL parameter
• Logical Standby
• long-running operations, performance view
• LRU latches

M

• maintaining indexes
• Manual Undo Management mode
• maximum number of database files, specifying
• MAX_DISPATCHERS
• MAX_DUMP_FILE_SIZE parameters
• MAX_SHARED_SERVERS
• memory
  • effective use of
  • hierarchy of speed
  • importance of
  • insufficient
  • OS requirements
  • performance view
  • persistent areas
  • PGA. See PGA (Program Global Area)
  • private SQL areas
  • runtime areas
  • SGA
  • sorts, required for
  • structure of
  • system memory architecture
  • user processes requirement
  • virtual
• MERGE hints
• merge joins
• methodology, tuning. See tuning methodology
• Microsoft Windows, starting Oracle instances
• migrating
• MPP (Massively Parallel Processing)
• MTS (multithreaded server)
  • advantages of
  • backdoor, creating
  • dispatcher, performance view
  • initialization parameters
  • large pool requirements
  • memory use
  • MTS_DISPATCHERS
  • MTS_MAX_DISPATCHERS
  • MTS_MAX_SERVERS
  • MTS_SERVERS
  • performance view
  • purpose of
  • shared pool use
  • tuning
• multiblock reads
• multiprocessor systems
• multithreaded message queues
• multithreaded server (MTS) configuration. See MTS (multithreaded server)

N

• NAS (network attached storage)
• need for tuning
• nested loop joins
• networks
  • addressing
  • architecture of
  • bandwidth
  • bridges
  • collisions
  • deferred packets
  • design considerations
  • Ethernet
  • frames
  • hardware components
  • hubs
  • limitations
  • load monitoring
  • local tables
  • NICs
  • Oracle tuning
  • packets
  • protocols
  • reducing unnecessary aspects
  • routers
  • segmenting
  • slow
  • SPX/IPX
  • subnets
  • TCP/IP
• new applications
• NICs (Network Interface Cards)
• NOAPPEND hints
• NOCACHE hints
• nodes, cluster
• non-background system-wide wait events
• nonunique indexes
• NOPARALLEL hints
• NOPARALLEL_INDEX hints
• NOREWRITE hints
• NO_EXPAND hints
• NO_FACT hint
• NO_INDEX hints
• NO_MERGE hints
• NO_PUSH_PRED hints
• NO_UNNEST hints
• numbers of users, tuning for

O

• object caches, performance view
• OCI procedure
• offline backups
• OLTP systems. See transaction processing
• online backups
• online reports
• online tablespace backups
• OPEN_CURSORS parameter
• OPEN_LINKS parameter
• OPEN_LINKS_PER_INSTANCE parameter
• operating systems
  • dependence on
  • reason for tuning
  • requirements
  • resource tuning
• OPS (Oracle Parallel Server). See RAC (Real Application Clusters)
• optimization hints. See hints
• Optimizer, Oracle
  • ALL_ROWS option
  • ALTER SESSION command
  • CHOOSE option
  • cluster views
  • column views
  • cost-based approach
  • costs, table of
  • FIRST_ROWS option
  • goal specification
  • hints. See hints
  • index views
  • initialization parameters
  • join order
  • operation of
  • OPTIMIZATION_MODE parameter
  • OPTIMIZER_FEATURES_ENABLE parameter
  • OPTIMIZER_FEATURES_ENABLED parameter
  • OPTIMIZER_INDEX_CACHING parameter
  • OPTIMIZER_INDEX_COST_ADJ parameter
  • OPTIMIZER_MAX_PERMUTATIONS parameter
  • OPTIMIZER_MODE parameter
  • options, table of
  • purpose of
  • RULE option
  • rule-based approach
  • statistics, gathering
  • table views
  • views
• OR expansions, prohibiting
• Oracle bitmap indexes. See bitmap indexes
• Oracle Dynamic Performance Views
  • accesses on objects
  • active processes
  • analysis
  • block contention
  • buffer cache hit ratio
  • buffer pools
  • cache efficiency
  • child cursor execution plans
  • counters
  • current instance
  • cursor usage
  • data dictionary cache ratio
  • data dictionary usage
  • data files
  • defined
  • dispatchers, multithreaded servers
  • false pings
  • file I/O
  • G$ views
  • global transactions
  • instancing
  • latch usage
  • library cache
  • lock manager
  • locks
  • memory usage
  • multithreading (MTS)
  • object cache
  • operations, long-running
  • parallel processes
  • parameters, session
  • pings
  • RAC locks
  • reserved shared pool
  • resource commerce groups
  • resource limits
  • session information
  • session statistics
  • SGA cache
  • shared pool
  • shared pool free space
  • shared SQL area
  • sorts
  • SQL queries to access
  • SQL scripts for
  • statistics names
  • Statspack
  • system parameters
  • system statistics
  • temporary space
  • third-party analysis tools
  • transactions
  • undos
  • UTLBSTAT
  • UTLESTAT
  • V$ views, table of
  • waits
  • X$ tables
• Oracle instances
  • checking connection
  • connecting
  • Microsoft Windows, starting from
  • order of tuning
  • shared memory allocation
  • shutting down
  • starting
  • STARTUP command
  • startup event sequence
  • troubleshooting startup
• Oracle Optimizer. See Optimizer, Oracle
• Oracle Parallel Query. See parallel execution
• Oracle processes
  • background processes
  • server processes
• Oracle Real Application Clusters. See RAC (Real Application Clusters)
• Oracle Recovery Manager. See RMAN (Recovery Manager)
• Oracle striping
• order of tuning
• ORDERED hints
• ORDERED_PREDICATES hints
• OS striping

P

• packages
• paging
• Parallel Cache Management (PCM) locks. See PCM (Parallel Cache Management) locks
• parallel execution
  • contention
  • CPU capacity
  • defined
  • degree of parallelism
  • design guidelines
  • disabling
  • FULL hint
  • hints
  • I/O capacity
  • I/O configuration
  • indexes
  • initialization parameters
  • loading
  • memory limitations
  • monitoring
  • negative consequences of
  • NOPARALLEL hint
  • number of servers, setting
  • operations available for
  • PARALLEL hint
  • Parallel Query option
  • PARALLEL_MAX_SERVERS
  • PARALLEL_MIN_SERVERS
  • performance
  • performance view
  • query coordinators
  • query processing
  • query servers
  • query tuning
  • recovery
  • statistics
  • storage considerations
  • striping, disk
  • table scans
  • temporary tablespace
  • types of operations
• PARALLEL hints
• Parallel Index Creation feature
• parallel loading
• parallel queries. See parallel execution
• Parallel Query. See parallel execution
• parallel recovery
• PARALLEL_ADAPTIVE_MULTI_USER
• PARALLEL_AUTOMATIC_TUNING
• PARALLEL_BROADCAST_ENABLED
• PARALLEL_EXECUTION_MESSAGE_SIZE
• PARALLEL_INDEX hints
• PARALLEL_MAX_SERVERS parameter
• PARALLEL_MIN_PERCENT
• PARALLEL_MIN_SERVERS
• PARALLEL_THREADS_PER_CPU
• parameters
  • initialization. See initialization parameters
  • resource limiting
• parity
• parsing
  • caching of
  • criteria for reuse
  • data dictionary accesses
  • reducing frequency of
  • statistics on
  • steps in
  • stored procedures
• partitions
  • benefits of
  • Composite Partitioning
  • defined
  • Hash Partitioning
  • indexes, options for partitioning
  • keys
  • List Partitioning
  • MAXVALUE range
  • PARTITION_VIEW_ENABLED
  • Range Partitioning
  • schemes available
  • Sub-Partitioning
  • when to use
• PCM (Parallel Cache Management) locks
• PCTFREE
• PCTUSED
• performance snapshot statistics. See Statspack
• Performance Manager
• persistent areas of memory
• PGA (Program Global Area)
  • components of
  • initialization parameters
  • private SQL area
  • session information
  • stack space
  • Statspack statistics
• Physical Standby
• pings
  • OPS
  • performance view
• PINS
• plan stability
• platters
• PMON processes
• PQ_DISTRIBUTE hints
• predicates
• prefetching data
• private SQL areas
• processes
• PROCESSES parameter
• processors. See CPUs
• profiles
• PUSH_PRED hints
• PUSH_SUBQ hints

Q

• queries
  • execution. See EXPLAIN PLAN command; SQL statement execution
  • parallel. See parallel execution
  • processing, steps in
  • subqueries
  • transformation hints
• query coordinators
• query servers
• quiescing databases

R

• RAC (Real Application Clusters)
  • application tuning
  • balancing
  • benefits of
  • cache fusion
  • clusters defined
  • computer system for
  • configuration
  • contention, reducing
  • DB_NAME initialization parameter
  • defined
  • design considerations
  • disadvantages of
  • distributed services
  • DLM
  • Exclusive Lock mode
  • failover vs. true clustering
  • FREELIST GROUPS
  • hardware requirements
  • hash
  • I/O subsystem configuration
  • indexes
  • instance tuning
  • INSTANCE_GROUPS parameter
  • locking system
  • multiplexing
  • nodes
  • Parallel Server option
  • PCM
  • PCTFREE and PCTUSED
  • pings
  • queuing
  • Read Lock mode
  • read tables
  • SANs with
  • scalability
  • segmenting users
  • server interconnects
  • shared disk subsystems
  • single image clusters
  • sizing systems
  • smart routing
  • system diagram
  • Tuxedo TM (transaction monitor)
  • views of statistics
  • when to use
• RAID (Redundant Array of Inexpensive Disks)
  • 0 level
  • 1 level
  • 2 level
  • 3 level
  • 4 level
  • 5 level
  • 10 level
  • backups using
  • cache, controller
  • controller performance
  • data guarding
  • data scrubbing
  • defined
  • disk contention
  • disk mirroring
  • disk striping
  • distributed data guarding
  • drive parity
  • elevator sorting
  • external
  • fault tolerance
  • hardware vs. software
  • HBAs
  • internal vs. external
  • levels
  • parity
  • performance of levels
  • performance vs. fault tolerance and cost
  • purpose of
  • read caches
  • recommended level
  • SANs, as component of
  • stripe size
  • striping
  • virtual drive property
  • write caches
  • XOR operation
• random I/O
• random seeks
• Range Partitioning
• RBU (Rollback Segment Undo Scheme)
  • circular buffering
  • creating rollback segments
  • dynamic growth, avoiding
  • extents
  • number of rollback segments
  • rollback segment operation
  • rules for using
  • sizing
  • statistics
  • vs. SMU
• reads
  • consistency
  • disk, statistics on
  • latency
  • multiblock,
• Real Application Clusters (RAC). See RAC (Real Application Clusters)
• RECO processes
• recovery
  • archive log files
  • ARCHIVELOG mode
  • Data Guard
  • disaster survival systems
  • parallel
  • point-in-time
  • process of
• Recovery Manager (RMAN). See RMAN (Recovery Manager)
• RECOVERY_PARALLELISM parameter
• recursive calls
• recursive SQL statements
• Recycle Buffer Pool
• redo log files
  • ARCHIVELOG mode
  • Archiver process
  • bottlenecks in
  • buffer
  • contention
  • RAID 1 recommended
  • statistics on
  • write latency
• redos
  • block statistics
  • checkpoints caused by
  • information on
  • LGWR process
  • log files. See redo log files
• registering applications
• release compatibility
  • initialization parameter for
  • OPTIMIZER_FEATURES_ENABLE parameter
• reloads
• remote mirroring
• replication
• reporting servers
• reserved share pool
  • performance view
  • using
• resource consumer groups
  • adding users
  • advantages of
  • allocation
  • configuring
  • creating plans
  • directives, setting
  • enabling
  • management software packages
  • monitoring
  • performance view
  • purpose of
  • resource manager plans
• resource limiting parameters
• resource manager, 105-109. See also resource consumer groups
• resources, performance view
• RESOURCE_LIMIT
• RESOURCE_MANAGER_PLAN
• response time, tuning for
  • defined
  • example
  • goals
  • hint for optimizing
• RISC processors
• RMAN (Recovery Manager)
  • access patterns
  • advantages of
  • asynchronous I/O
  • buffering
  • large pool configuration
  • MAXOPENFILES parameter
  • monitoring
  • operational steps
  • parameters
  • RATE parameter
  • synchronous I/O
• rollback entries
• Rollback Segment Undo (RBU) scheme. See RBU (Rollback Segment Undo Scheme)
• rollback segments. See also RBU (Rollback Segment Undo Scheme); SMU (System Managed Undo)
  • automatic transaction assignment
  • circular buffering
  • contention, minimizing
  • creating
  • dynamic growth, avoiding
  • extents
  • hand configuring
  • large updates
  • long queries
  • manual transaction assignment
  • number, setting
  • OLTP transactions
  • operation of
  • purpose of
  • shrinks
  • sizing
  • SMU recommended
  • statistics
  • transaction tables
  • transactions parameter
  • wraps
• root nodes
• rotational latency
• routers
• row cache
• ROWID hints
• rows
  • chaining
  • migrating
  • specific, updating bottleneck
  • SQL Trace, number processed
• RULE hint
• rules-based optimization
• runtime areas

S

• SANs (Storage Area Networks)
  • backups using
  • clustering
  • defined
  • failover controllers
  • OPS
  • RAC
  • remote mirroring
• schema statistics
• seeks
• segments
• SELECT statements
• sequential I/O
• server processes
• session information, storage of
• sessions, performance view
• SESSIONS parameter
• SESSION_CACHED_CURSORS parameter
• SGA (System Global Area)
  • buffer cache. See buffer cache
  • buffer pools
  • components
  • initialization parameters
  • large pools. See large pools
  • locking into memory
  • memory requirement
  • performance view
  • redo log buffer
  • shared pools. See shared pools
  • statistics on
• shadow processes. See server processes
• shared memory
• shared PL/SQL area
• shared pool
  • components of
  • data dictionary
  • defined
  • large pool
  • library cache
  • performance view
  • reserved area
  • SHARED_POOL_RESERVED_SIZE parameter
  • SHARED_POOL_SIZE parameter
• shared processes
• shared SQL area
• shutdown, checkpoint creation by
• SHUTDOWN ABORT command
• SHUTDOWN IMMEDIATE command
• SHUTDOWN NORMAL command
• SHUTDOWN TRANSACTIONAL command
• shutting down Oracle instances
• single image clusters
• sites, choosing with hints
• sizing systems
  • defined
  • for the worst case
  • steps in
• SMON processes
• SMP (Symmetric Multiprocessor) systems
• SMU (System Managed Undo) mode
• sort-merge joins. See merge joins
• sorts
  • elevator
  • memory required
  • performance view
  • runs
  • sort area size
  • SORT_AREA_RETAINED_SIZE parameter 38
  • SORT_AREA_SIZE parameter
  • statistics on
  • temporary tablespaces
• SPX/IPX
• SQL Analyze
• SQL area
• SQL statement execution
  • binding variables
  • cursor creation
  • execution step
  • execution plans. See EXPLAIN PLAN command
  • fetching rows
  • Parallel Query option
  • parsing statements
  • query processing
  • steps in
• SQL statement optimization. See also EXPLAIN PLAN command; SQL Trace
  • analyzing
  • comments. See comments
  • cost
  • designing
  • effects of
  • existing applications
  • EXPLAIN PLAN. See EXPLAIN PLAN command
  • familiarization phase
  • goals for optimizing
  • indexes
  • join optimization. See joins
  • new applications
  • page read statistics
  • parses
  • problem analysis
  • problems, list of
  • SQL Trace analysis. See SQL Trace
  • statistics on
  • steps for tuning
  • tuning steps
  • well-tuned, characteristics of
• SQL Trace
  • counts
  • CPU parameter
  • current parameter
  • disabling
  • disk parameter
  • elapsed times, 240 243
  • enabling
  • EXPLAIN PLAN command
  • guidelines for use
  • index performance
  • indications of problems
  • information gathered
  • initialization parameters required
  • library cache
  • MAX_DUMP_FILE_SIZE parameter
  • optimizer hints
  • output from
  • parameters, table of
  • purpose of
  • query parameter
  • read statistics
  • rows parameter
  • TIMED_STATISTICS parameter
  • TKPROF for formatting
  • USER_DUMP_DEST parameter
• SQL*Plus
• stack space
• standardized naming conventions
• Standby Database
• STAR hints
• star query plans
• starting Oracle instances
• STARTUP command
• STAR_TRANSFORMATION hints
• statistics. See also BSTAT/ESTAT; DBMS_STATS package; Statspack
  • creating table for
  • database statistics
  • DELETE_TABLE_STATS function
  • disk drive accesses
  • GATHER_TABLE_STATS function
  • index statistics
  • parallel execution
  • restoring
  • snapshots. See Statspack
  • system statistics
  • table statistics
• statistics names, performance view
• Statspack
  • buffer pool statistics
  • cache hits ratio
  • defined
  • dictionary cache statistics
  • general information
  • I/O statistics
  • initialization parameters, listing
  • installing
  • instance activity statistics
  • instance recovery statistics
  • latch statistics
  • library cache statistics
  • parse statistics
  • PGA statistics
  • redo block statistics
  • rollback segment statistics
  • running
  • SGA statistics
  • snapshot display
  • SQL statement statistics
  • system load statistics
  • truncating tables
  • wait event statistics
• storage. See also disk drives
  • PCTFREE/PCTUSED, setting
  • STORAGE clause options
• stored procedures
  • benefits of
  • caching in shared pool
  • shared SQL area benefits of
• striping
  • costs of
  • defined
  • hardware
  • Oracle
  • OS
  • parallel execution, benefits with
  • RAID
  • stripe size
• structural integrity
• Sub-Partitioning
• subnets
• subqueries, hints for
• swapping memory
• system memory architecture
• system parameters performance view
• system statistics performance view
• system tuning

T

• table scans
  • ROWID hints
  • statistics indicating
• tables
  • data dictionary cache
  • indexing, guidelines for
  • specific, updating bottleneck
• tablespaces
  • block size, setting
  • checkpoint creation by
  • fragmentation
  • I/O statistics
  • sorting with
  • undo parameters
• TCP/IP networks
• temporary space performance view
• threads, 168. See also processes; MTS (multithreaded server)
• throughput, defined
• throughput, tuning for
  • defined
  • goals
  • hint for
• TKPROF
  • optional parameters
  • output from
  • recursive SQL statements
  • sort options
  • SQL Trace formatted by
• Top Sessions
• Trace Data Viewer
• trace files, maximum size parameter
• track-to-track seeks
• transaction processing
  • block size
  • commitment
  • completion requirements
  • connection stage
  • defined
  • function reallocation
  • performance view
  • processing stage
  • read consistency
  • redo information
  • rollback segment sizing
  • steps in
  • termination stage
  • TRANSACTIONS parameter
  • TRANSACTIONS_PER_ROLLBACK_SEGMENT
  • user profiles for
  • workload management
• transaction tables
• tuning methodology
  • analysis phase
  • analyzing test results
  • determination phase
  • examining the problem
  • existing applications
  • goal setting
  • response time example
  • steps in
  • testing solutions
• tuning, definition of

U

• UGA (User Global Area)
• undo header buffer busy waits
• undos
  • monitoring tablespace
  • parameters
  • performance view
  • rate, determining
  • RBU
  • retention period
  • SMU
  • tablespace creation
  • tablespace size requirement
  • UNDO_MANAGEMENT parameter
  • UNDO_RETENTION
  • UNDO_SUPPRESS_ERRORS
  • UNDO_TABLESPACE
  • write latency
• UNION operator
• unique indexes
• UNIQUE integrity constraints
• UNNEST hints
• updates
  • chaining rows
  • indexes, effects on
  • migrating rows
  • overhead from
  • PCTFREE/PCTUSED, setting
• USE_CONCAT hints
• USE_HASH hints
• USE_MERGE hints
• USE_NL hints
• user processes
• user profiles
• user resources
• UTLBSTAT. See BSTAT/ESTAT
• UTLESTAT. See BSTAT/ESTAT

V

• V$ views. See Oracle Dynamic Performance Views
• VARCHAR2 data type
• views. See also Oracle Dynamic Performance Views
  • data dictionary cache
  • hints for
• virtual memory

W

• waits
  • performance view
  • statistics on
• WHERE clauses, USE_CONCAT hints
• willing-to-wait latches
• Windows NT Fail Safe
• workload tuning
  • Data Guard
  • function reallocation
  • goals of
  • historical reports
  • initialization parameters
  • methods for accomplishing, list of
  • online reports
  • reporting servers
  • resource consumer groups
  • user resource limiting
• write caches
• write latency

X-Z

• X$ tables

Y

There are no entries in this section.

Z

There are no entries in this section.



Updates

Submit Errata

