Database Technologies‎ > ‎Oracle‎ > ‎

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

         5) ASMM
               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.