1 / 41

TUNING - PowerPoint PPT Presentation

  • Updated On :

TUNING. Office of the Accountant General (A&E) Andhra Pradesh Hyderabad. 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 . Tuning Goals.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'TUNING' - azura

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript


Office of the Accountant General (A&E)

Andhra Pradesh



  • 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

Tuning goals
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

Tuning parameters
Tuning Parameters

  • Response time

  • Database availability

  • Database hit percentages

  • Memory utilization

Tuning steps
Tuning Steps

  • Tune the design

  • Tune the application

  • Tune memory

  • Tune IO

  • Tune contention

  • Tune operating system

Tuning considerations
Tuning Considerations

  • Different for

    • OLTP databases

    • DSS databases

    • Hybrid databases

  • Our database

    • Hybrid database

    • Data entry and Report generation done simultaneously

Hybrid databases
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

Tuning hybrid databases
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

Views utilities and tools used for tuning
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


Tuning statistics to be gathered
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

Alert log files
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


Background processes trace files
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

User trace files
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

Tuning memory
Tuning Memory

  • The amount of memory occupied by Oracle is called SGA

  • SGA contains

    • DB Block Buffers

    • Redo Log Buffers

    • Shared Pool

      • Library Cache

      • Data Dictionary Cache

      • UGA

    • Large Pool

Tuning memory1
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

Tuning library cache
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





Tuning library cache1
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

Tuning library cache2
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



Tuning data dictionary cache
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%

Tuning db block buffer cache
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’;

Tuning buffer cache
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

Tuning buffer pool
Tuning Buffer Pool

  • To use multiple pool buffers set





  • To enable table caching

    • Alter table with cache clause

    • Use cache hint in query

Tuning redo log buffer
Tuning Redo Log Buffer

  • Following parameters effect log buffer




  • Check for

    • Redo buffer space event

    • Redo buffer allocation retries

    • Redo log space requests

    • Logfile switch (check point incomplete)

    • Logfile switch (archive needed)

Tuning check point
Tuning Check Point

  • Each checkpoint cause IO operations

  • Frequent checkpoints reduces runtime performance

  • Parameters






Tuning large pool
Tuning Large Pool

  • Used for oracle maintenance operations

  • UGA in MTS mode

  • Parallel query

  • IO-server process

  • Tuning parameters




  • Contention areas that the DBA can tune:

    • Redo allocation latch

    • Redo copy latch

    • LRU latch

  • Latch types

    • Willing to wait

      • Gets, misses, sleeps

    • Immediate

      • Immediate gets and immediate waits

Lru latches
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

Lru latch tuning goals
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

Resolving lru latch contention
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:

      • Number of CPUs*2*3

      • Number of buffers/50

Free lists
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

Resolving free list contention
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

Tuning io contentions
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#

Oracle file striping
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

Tuning data files
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

Tuning log files
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


Tuning archive log files
Tuning Archive Log Files

  • Keep archive log files on separate IO faster devices

  • Archive log operations parameters



  • Dynamic views




Tuning rollback segments
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

Tuning rollback segments1
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

Sizing transaction rollback data
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

Possible problems
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

Tablespace usage
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