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
V$pga_target_advice
V$db_cache_advice
V$java_pool_advice
V$shared_pool_advice, the below mentioned gives us the estimated time saved for various sizes
select
shared_pool_size_for_estimate,
shared_pool_size_factor,
estd_lc_memory_object_hits,
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
PGA_AGGREGATE_TARGET
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
sort_area_size,
hash_are_size,
bitmap_merge_area_size
create_bitmap_area_size,
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
5) ASMM
ASMM - Automatic Shared Memory Management introduced with Oracle 10g
dynamically adjusts the size of
Shared_pool
Large_pool
Java_pool
db_buffer_cache.
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
ORACLE_HOME\rdbms\admin\catdbsyn.sql
ORACLE_HOME\rdbms\admin\dbmspool.sql
ORACLE_HOME\rdbms\admin\spcreate.sql
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
SQL>@?/rdbms/admin/utlxplan.sql
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;
Hints
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
=====Miscellaneous=========
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.