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 cost-
based 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 read-
consistent 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