Performance Tuning

Performance Tuning could be divided into two categories.

1) Oracle System Tuning : Oracle System Tuning, involves making sure the

parameters are set up correctly, for example the parameters related to

undo management, undo tablespace size, redo file sizes, memory sizes

for SGA, Large Pool, etc, here we would look at the various parameter

tuning advisors provided by oracle.

2) SQL Tuning : SQL Tuning involves the tuning of SQL Statements, here

we would look at things like indexes, Optimizer,Hints,Stored Outlines etc

Oracle System Tuning

1) Redo Log Sizing

2) Tuning Advisors

3) Optimizer

4) ASMM - Automatic Shared Memory Management

5) Statspack

1) Redo Log Sizing : We have to make sure that the redo logs are not

switching often, check in the alert log file for the frequency of the log

switches. You could increase the size of the redo log, switches within

interval of 30 minutes is considered to be good

FAST_START_MTTR_TARGET : Time in seconds we want the instance to startup

default is 900.

Smaller the Values, more intensive the Log Writer Activity.

2) Tuning Advisors : Oracle creates histogram of the various paramerts based

on their performance history and gives us probable parameter's value and

estimated cost that could be saved when that configuration is done.

(We could also use ASMM, dealt below)

Various Advisors





V$shared_pool_advice, the below mentioned gives us the estimated time saved for various sizes





estd_lc_size, estd_lc_time_saved

from v$shared_pool_advice

order by shared_pool_size_factor


For above to work we should have init parameter STATISTICS_LEVE= TYPICAL


when the workarea_size_policy is not included in init file it is defaulted to

AUTO,implies AUTO PGA Memory Management is enabled this automatically

resizes the various memory areas





parameters, dynamically depending upon memory requirements

When you don't specify the PGA_AGGREGATE_TARGET parameter even though

workarea_size_policy is set to auto, then it defaults to 10MB or 20% or SGA whichever

is higher

3) Optimizer

It is the oracle internal mechanism using which it decides what execution path to

take for SQL Statements

RULE Based Optimizer = It is based on some complex rules and does not depend

on Statistics of tables or indexes

Cost Based Optimizer = It decised which execution path to take based on statistics

of the tables and indexes which are referenced in the query

We have initialization Parameter Optimizer_mode

Optimizer_mode = rule | Choose | first_rows | all_rows

Rule = Obsolete, rule based approach for all the SQL Statements, regardless of the presence of statistics

Choose = Optimizer decides whether to go for RULE based plan i.e when statistics are

not available or to use the cost based method if statistics are available

First_rows = Bring rows as fast as possible mostly used in OLTP databases, used cost based approach best performance

hogs resources

All_rows = Uses Cost Based approach regardless of the presence of statistics, hogs minimal resource and gives output


ASMM - Automatic Shared Memory Management introduced with Oracle 10g

dynamically adjusts the size of





To enable ASMM set SGA_target to higher valie than 0,

ASMM is managed by mman background process

If you set SGA_TARGET to a non-zero values,

Note : Size of SGA_TARGET could increase up to the size specified in SGA_MAX_SIZE.

Memory is distributed among the components depending upon the workload

After setting SGA_TARGET, if you decrease the value of any auto tuned

parameter, the value is not decreased immediately but a lower limit is set

for the component. If you increase the component values the value is

immediately increased

Statistics_level should not be basic, if you want to enable ASMM

When ASMM is enabled and you specify a lower value to any of the auto tuned

Components, then oracle does not automatically decreases it, it does sets a

lower cap on it. And when you increase value of any auto tuned component

it increases it immediately by taking memory from other auto tuned components

4) Statspack : This is nice tool that gather statistical information on regular basis

To install these we have create a tablespace for this or you could create it in

sysaux tablespace.

Execute the following scripts to install the statspace




Of these the last script spcreate.sql is interactive script, here it asks for perfstat users

password, tablespace where to install the scripts, and temporary tablepace to use.

Then we could schedule the snapshot to occur on regular basis using

spauto.sql, default is one hour, for the job to work properly make sure the job_queue_processes

should not be equal to 0

To create report use the spreport.sql

Various Levels in Statspack

Level 0 -- This level is for gathering general performance information

Level 5 -- This is the default level, provides general information + also

information on Executed SQL statements and their resource utilization

Level 6 -- This level provides

1) General information

2) SQL statements and resouce utilization

3) SQL Plans and its usage

Level 10 -- This level Provides

1) General information

2) SQL statements and resouce utilization

3) SQL Plans and its usage

4) Parent and child latches

Also , one could enable statspack for particular session using the command

SQL> Exec Statspack.snap(i_session_id=>20);

SQL Tuning

1) Indexes

2) Explain Plan

3) Hints

4) Stored Outlines

create [unique | bitmap] index <index_name>

on <table_name>(<column_name>,<column_name>) = Columns which are referenced in the where clause

nologging =No log generation

compress =Eliminate duplicate values

reverse =Reverse ordering in order to balance

parallel <Number> = No of process to be involved

compute statistics = Generate statistics

tablespace <tablespace_name> = Tablespace on which to be created

Explain Plan

Execution path taken for your SQL Statements

1) First create Plan table


To get plan for particular sql statements

for example

sql>explain plan for

2>select * from scott.dept

3>where deptno=30;

Explained... This fills the plan table with execution plan

To see the execution plan

SQL>select * from table(dbms_xplan.display);

give us plan table output

You could then delele the plan table rows

SQL>delete from plan_table;


these are the hints that you give to oracle to which optimizer mode to use,

Oracle provides number of hints

select /*+ First_rows */ * from <table_name>

Stored Outlines.

Allow you to transfer the execution plan for a sql query

from development database to production database


To find the Most Read tables,

col TOTAL_READS for a9999999

SELECT t.owner,t.table_name,lr.value+pr.value AS total_reads FROM (SELECT owner,object_name,value FROM v$segment_statistics WHERE statistic_name='logical reads') lr, (SELECT owner,object_name,value FROM v$segment_statistics WHERE statistic_name='physical reads') pr, dba_tables t WHERE lr.owner=pr.owner AND lr.object_name=pr.object_name

AND lr.owner=t.owner AND lr.object_name=t.table_name ORDER BY 3 desc;

To find the Tables with Most write activity

SELECT t.owner,t.table_name,pw.value+pd.value AS Total_Writes FROM

(SELECT owner,object_name,value FROM v$segment_statistics WHERE statistic_name='physical writes') pw,(SELECT owner,object_name,value FROM v$segment_statistics WHERE statistic_name='physical writes direct') pd, dba_tables t WHERE pw.owner=pd.owner AND pw.object_name=pd.object_name AND pw.owner=t.owner AND pw.object_name=t.table_name ORDER BY 3 desc;


Oracle Waits

db file sequential read : - Tune the SQL so that there is less I/O. Analyze all objects

Distribute I/O accross disks.

buffer busy waits : - Increase the db_cache_size (sys.v$bh)

log buffer space : - Increase the log_buffer, or move log file to faster disks.

db file sequential read & db file scattered read : These are important wait events tell us time waited for I/O read request to complete

db file sequential read : Reads data into contiguous memory, normally results from index scan

db file scattered read : Reads data into contiguous memory (p3=1), data from full table scan normally

get scattered over the bugger area.