Transcript Document

TUNING
Office of the Accountant General (A&E)
Andhra Pradesh
Hyderabad
VLC - AG(AE) AP
Tuning
• When is tuning necessary?
– Only if you feel that application is not running
fast enough
• What is to be tuned?
– Oracle database
– Application
– Operating system
– Network
VLC - AG(AE) AP
Tuning Goals
• To optimize the performance of
database
• To make database available to users
without making them wait for
resources
• To perform maintenance operations
without interrupting users
VLC - AG(AE) AP
Tuning Parameters
• Response time
• Database availability
• Database hit percentages
• Memory utilization
VLC - AG(AE) AP
Tuning Steps
• Tune the design
• Tune the application
• Tune memory
• Tune IO
• Tune contention
• Tune operating system
VLC - AG(AE) AP
Tuning Considerations
• Different for
– OLTP databases
– DSS databases
– Hybrid databases
• Our database
– Hybrid database
– Data entry and Report generation done
simultaneously
VLC - AG(AE) AP
Hybrid Databases
• Number of rollback segments for data
entry
• Some large rollback segments for
report generation
• Balanced db_block_buffers and
log_buffers
• Medium size for db_block_size
VLC - AG(AE) AP
Tuning Hybrid Databases
• Tuning application is important in
hybrid databases
• Indexing plays an important role
• Schedule batch processing if it is
heavy on resources
• Use less number of bind variables in
reports
• Optimize queries using hints
VLC - AG(AE) AP
Views, Utilities and Tools Used
For Tuning
• Dynamic troubleshooting/performance
and dictionary views
– V$XXXX dynamic troubleshooting and
performance views
– DBA_XXX dictionary views
• Utlbstat.sql and utlestat.sql
• Alert logs and traces
•
nitialization parameters
VLC - AG(AE) AP
Tuning Statistics To Be Gathered
•
Library cache statistics
•
System statistics
•
Wait event statistics
•
Latch statistics
•
Rollback contention statistics
•
Buffer busy wait statistics
•
Dictionary cache statistics
•
IO statistics per data file/table space
•
Period of measurement
VLC - AG(AE) AP
Alert Log Files
• The lert log file consists of a
chronological log of messages and
errors
• Check the alert log file to:
– Detect internal errors and block corruption
errors
– Monitor database operations
– View the non-default initialization parameters
• Remove or trim the alert log file
regularly after checking
VLC - AG(AE) AP
Background Processes Trace Files
• The Oracle server dumps information
about errors detected by any
background process in trace files
• Oracle support uses these trace files
to diagnose and trouble shoot
problems
VLC - AG(AE) AP
User Trace Files
•
Server process tracing is enabled or disabled
at the session or instance level by:
– The ALTER_SESSION command
– The SET_SQL_TRACE_IN_SESSION procedure
– The initialization parameter SQL_TRACE
•
A user trace file contain statistics for traced
SQL statements for that session
•
A user trace file is used for SQL tuning
•
The Oracle database creates user trace files
on per server process basis
VLC - AG(AE) AP
Tuning Memory
• The amount of memory occupied by
Oracle is called SGA
• SGA contains
– DB Block Buffers
– Redo Log Buffers
– Shared Pool
o Library Cache
o Data Dictionary Cache
o UGA
– Large Pool
VLC - AG(AE) AP
Tuning Memory
• In hybrid databases like VLC, memory
plays an important role
• Ideally DB_BLOCK_BUFFERS and
LOG_BUFFERS to be in 80-20 ratio
• SHARED_POOL_SIZE also plays an
important parameter in tuning
memory
VLC - AG(AE) AP
Tuning Library Cache
• Library Cache tuned
– If the pins to misses ratio is more than 1%
– If the get hit ratio of name space is less than
90%
• V$Views
– V$LIBRARYCACHE
– V$SQLAREA
– V$SQLTEXT
– V$DB_OBJECT_CACHE
VLC - AG(AE) AP
Tuning Library Cache
• To find hit ratio of library cache
– Select namespace, gethitratio from
v$librarycache
• To find reloads of SQL statements
– Select sql_text, users_executing, executions,
loads from v$sqlarea
• To find library cache reloads
– Select sum(pins) Executions, sum(reloads)
Misses, sum(reloads)/sum(pins) Hitratio from
v$librarycache
VLC - AG(AE) AP
Tuning Library Cache
• Keep often used objects
• Avoid parsing
• Avoid large anonymous PL/SQL Blocks
– Select sql_text from v$sqlarea where
command type=47;
• Reserve un-fragmentable memory in
shared pool
– SHARED_POOL_RESERVED_SIZE
– SHARED_POOL_MIN_ALLOC
VLC - AG(AE) AP
Tuning Data Dictionary Cache
• Keep the ratio of the getmisses to gets
less than 15%
– Select parameter, gets, getmisses from
v$rowcache
• Increase shared pool size if the above
ratio is more than 15%
VLC - AG(AE) AP
Tuning DB Block Buffer Cache
• DB hit ratio should always be more
than 90%
• To find DB Cache hit ratio
– Select 1-(p.value)/(d.value+c.value) from
v$sysstat p, v$sysstat d, v$sysstat c where
p.name = ‘physical reads and d.name = ‘db
block gets’ and c.name = ‘consistent gets’;
VLC - AG(AE) AP
Tuning Buffer Cache
• If DB hit ratio gets below 90%
– Increase buffer cache size
– Use multiple buffer pools
– Cache tables
– Bypass the buffer cache for sorting and
parallel reads
VLC - AG(AE) AP
Tuning Buffer Pool
• To use multiple pool buffers set
– DB_BLOCK_BUFFERS
– DB_BLOCK_LRU_LATCHES
– BUFFER_POOL_KEEP
– BUFFER_POOL_RECYCLE
• To enable table caching
– Alter table with cache clause
– Use cache hint in query
VLC - AG(AE) AP
Tuning Redo Log Buffer
• Following parameters effect log buffer
– LOG_BUFFER
– LOG_CHECK_POINT_INTERVAL
– LOG_CHECK_POINT_TIMEOUT
• Check for
– Redo buffer space event
– Redo buffer allocation retries
– Redo log space requests
– Logfile switch (check point incomplete)
– Logfile switch (archive needed)
VLC - AG(AE) AP
Tuning Check Point
• Each checkpoint cause IO operations
• Frequent checkpoints reduces runtime
performance
• Parameters
– FAST_START_IO_TARGET
– LOG_CHECK_POINT_INTERVAL
– LOG_CHECK_POINT_TIMEOUT
– DB_BLOCK_MAX_DIRTY_TARGET
– LOG_CHECK_POINT_TO_ALERT
VLC - AG(AE) AP
Tuning Large Pool
• Used for oracle maintenance
operations
• UGA in MTS mode
• Parallel query
• IO-server process
• Tuning parameters
– LARGE_POOL_SIZE
– DBWR_IO_SLAVES
VLC - AG(AE) AP
Latches
• Contention areas that the DBA can
tune:
– Redo allocation latch
– Redo copy latch
– LRU latch
• Latch types
– Willing to wait
o Gets, misses, sleeps
– Immediate
o Immediate gets and immediate waits
VLC - AG(AE) AP
LRU Latches
• LRU latches regulate the least recently
used (LRU) lists used by the buffer
cache
• By default, the Oracle server set the
number of LRU latches to one-half the
number of CPUs, with a minimum of
one
• Each latch controls a minimum of 50
buffers
VLC - AG(AE) AP
LRU Latch Tuning Goals
• Ensure there are sufficient number of
LRU latches for the data buffer cache
so that contention between server
processes is minimized
• Balance the number of latches with
the number of CPUs
• Set one DBWn process for each latch
VLC - AG(AE) AP
Resolving LRU Latch Contention
• If the hit percentage for the LRU latch
is less than 99%
– Increase the number of LRU latches by setting
the parameter DB_BLOCK_LRU_LATCHES
– The maximum number of latches is the lower
of:
o Number of CPUs*2*3
o Number of buffers/50
VLC - AG(AE) AP
Free Lists
• A free list for an object maintains a list
of blocks that are available for inserts
• The number of free lists for an object
cannot be set dynamically
• Single CPU systems do not benefit
greatly from multiple free lists
• The tuning goal is to ensure that an
object has sufficient free lists to
minimize contention
VLC - AG(AE) AP
Resolving Free List Contention
• Query the V$SESSION_WAIT view
• Identify the object and get free lists
for the segment from DBA_SEGMENTS
• Re-create the object in question
VLC - AG(AE) AP
Tuning IO-contentions
• Keep data files and log files on
separate disks
• Stripe table data
• Reduce Disk IO
• Evaluate the use of RAW devices
• To know IO of files
– Select d.name, f.phyrds, f.phyrds from
v$datafile d, v$filestat where d.file# = f.file#
VLC - AG(AE) AP
Oracle File Striping
• Operating system striping
– Use operating system striping software or
RAID
– Decide on the right stripe size
• Manual striping
– Use the create table or alter table ALLOCATE
command
– Is worth while with parallel query usage
VLC - AG(AE) AP
Tuning Data Files
• Keep data files and log files on
separate disks to reduce IO contention
• Specify the initialization parameter
DB_FILE_MULTIBLOCK_READ_COUNT
– To determine the number of database blocks
the server reads at once
– To influence the execution plan of the costbased optimizer
VLC - AG(AE) AP
Tuning Log Files
• Size redo log files to minimize the
contention
• Have enough groups to prevent
waiting
• Store redo log files on separate faster
devices
• Dynamic views
– V$LOG
– V$LOGFILE
VLC - AG(AE) AP
Tuning Archive Log Files
• Keep archive log files on separate IO
faster devices
• Archive log operations parameters
– LOG_ARCHIVE_MAX_PROCESSES
– LOG_ARCHIVE_DEST
• Dynamic views
– V$ARCHIVE_DEST
– V$ARCHIVE_LOG
– V$ARCHIVE_PROCESSES
VLC - AG(AE) AP
Tuning Rollback Segments
• Transactions should never wait for
access to rollback segments
• Rollback segments should not extend
during normal running
• Users and utilities should try to use
less rollback
• No transaction should ever run out of
rollback space
• Readers should always see the readconsistent images they need
VLC - AG(AE) AP
Tuning Rollback Segments
• The ratio of the sum of waits to the
sum of gets should be less than 1%
– Select sum(gets) ‘gets’, sum(waits) ‘waits’,
sum(waits)*100/sum(gets) ‘ratio’ from
v$rollstat;
• If the ratio is more than 1% then
create more rollback segments
• The number of waits for any class
should be less than 1% of the total
number of requests
VLC - AG(AE) AP
Sizing Transaction Rollback Data
• Deletes are expensive
• Inserts use minimal rollback space
• Updates use rollback space depending
on the number of columns
• Index maintenance adds rollback
VLC - AG(AE) AP
Possible Problems
• Transactions fails for lack of rollback
space
• “Snapshot too old” error occurs if:
– The inserted transaction list in the block being
queried has been reused, and the SCN in the
block is newer than the SCN at the start of the
query
– The transaction slot in the rollback segment
header has been reused
– The undo data in the rollback segment has
been overlaid after a commit
VLC - AG(AE) AP
Tablespace Usage
•
Reserve the SYSTEM tablespace usage for
data dictionary objects
•
Create locally managed tablespaces to avoid
space management issues
•
Split tables and indexes into separate
tablespaces
•
Create separate rollback tablespaces
•
Store very large database objects in their
own tablespace
•
Create one or more temporary tablespaces
VLC - AG(AE) AP