Oracle Openworld Directory Index

Efficiency + tuning = good performance

Efficiency minimizes resource requirements
Tuning optimizes the use of those resources
The 7 Deadly Sins
They are efficiency problems
They magnify resource requirements
If the resources are available …
… performance suffers a little
If not …
… performance suffers greatly
The #1 Sin
Using literal SQL
Why Bind?
Literal SQL
always unique
1 hard parse per execution
SQL with bind variables
can be shared between sessions
can be reused within a session
1 soft parse per session
The Parse Steps Required
The Performance Difference
cursor_sharing = FORCE
Causes automatic binding
can be shared between sessions
but cannot be reused within a session
1 soft parse per execution
Better than literal SQL
1 hard parse per execution
Worse than SQL with bind variables
1 soft parse per session
Concurrent Parsing
Parsing makes heavy use of these latches
the library cache latches
the row cache objects latch
the shared pool latch(es) (hard parses only)
The library cache and shared pool latches are also used for SQL statement execution
Intense parsing causes latch contention
The Wages of Sin #1
Literal SQL causes excessive parsing
even with cursor_sharing = FORCE
Parsing is CPU intensive
Performance suffers even if there is ample CPU
Intense parsing stresses key latches
Scalability is severely limited
The 2nd Sin
Using client-side code
Why Stored Code?
Client-side code and anonymous blocks
resolve names in the current schema
run with the current user’s permissions
Definer’s rights stored code
executes in a recursive session
resolves names in the definer’s schema
runs with the definer’s permissions
Cache Litter
The library cache
keeps lists of all the users sharing each cursor
keeps lists of all synonyms translated during name resolution
The library cache and dictionary cache
both keep lists of all non-existent objects sought during name resolution
Stored code has 1 user, needs 0 synonyms
Comparison
The Wages of Sin #2
Network bandwidth is wasted on
extra round trips and higher traffic volume
CPU time is wasted on extra name resolution and permission checking
Extra latching is needed for soft parses

These factors make users wait and limit scalability
The 3rd Sin
Disconnecting and reconnecting
stateless middleware applications
shell scripts
pre-compiler programs with AUTO_CONNECT
instead of retaining database connections
Why Retain Connections?
The cost of establishing a new operating system process (or thread)

Plenty of recursive SQL during connection
each requires a soft parse

The cost of reopening datafiles for the initial read from each file
The Wages of Sin #3
CPU time is wasted on process startup and recursive SQL
Extra Latching is needed for recursive SQL
Users must wait for file opens and IPC

Reduces performance and limits scalability
The 4th Sin
Committing often
Why not Commit?
Redo generation
extra redo at start and end of transactions
maybe extra redo for ITL changes
Syncing LGWR
users must wait
makes LGWR overactive
Commit cleanout
may cleanout blocks repeatedly
The Wages of Sin #4
Extra log file I/O volume for extra redo generation and redo wastage due to syncs
Many tiny log writes, instead of few larger
Stresses redo allocation latch for redo generation and to free space in log buffer
Users must wait for sync writes and IPC
LGWR over-activity and redo allocation latching limits scalability
The 5th Sin
Over-indexing transactional databases with
redundant indexes
unused indexes
indexes for batch jobs
extra columns in concatenated indexes
instead of minimal indexing
Minimal OLTP Indexing
Indexes required are a function of
the physical data model
not the SQL

Batch processing is often extraneous
Move to separate database
Build and drop indexes for batch runs
The Wages of Sin #5
Extra index I/O is needed to find, read and then write index blocks affected by DML
Redo generation for index block changes
CPU time is wasted on index block I/O and redo generation
Users must wait for the index block reads
Extra redo generation limits scalability
The 6th Sin
High-concurrency monolithic PK indexes
Every query must visit the index root block
Consistent gets require a shared buffer lock
Buffer locks require the cache buffers chains latch

Can only sustain a few hundred consistent gets per second on a single block.
Alternatives
Single table hash cluster
PK index not used for queries
rows fetched directly from table blocks
based on hash value of PK columns

Range partition the PK index
Partitions are a separate index segments
Activity is spread over multiple root blocks
The Wages of Sin #6
Scalability is limited by contention for the cache buffers chains latch protecting the index root block

Users wait for the latch and sometimes for buffer locks as well
The 7th Sin
Stripe and mirror everything (SAME)
over a few large disks
Disks are too large to “waste”
Broad striping uses all disks equally
maximizing random I/O performance
The SAME Problems
Sequential I/O (esp. LGWR)
logically sequential I/O is degraded (~100%)
Adding disk capacity
introduces an unstriped hot spot
or requires a major disk reorganization
Slow backups
parallel backups thrash the disk heads
and prevent tape streaming
Alternative
Log files on dedicated mirrored disk pairs
even if they are largely empty
Use multiple small stripe sets
3 to 5 disks wide
Make all datafiles a uniform size
swap datafile positions to balance I/O
The Wages of Sin #7
Users wait for slow LGWR writes

Cannot add disk capacity in sensible quanta

Cannot use parallel backup streams to reduce backup window
Inefficiency Again
The 7 sins introduce gross inefficiency
degrades performance
limits scalability
Automatic tuning
can optimize the use of system resources
but cannot compensate for inefficiency
Self-tuning
Self tuning does not automatically eliminate all performance problems

It does free you focus on
application efficiency, instead of
fine tuning system resource allocations




Fill Out a Survey and Get a Chance to Win a Compaq iPAQ!

We want to know what you think!

Fill out the survey that was handed out at the beginning of the session for a chance to win a Compaq iPAQ. Remember to include your name and email in the available section and we will enter your name into two daily drawings to win an iPAQ