Performance Tuning Methodologies

Performance Tuning Methodologies

1. Ratio based tuning
2. Wait based tuning
3. Stage based tuning

“Tune the cause not the effect”

3.       Stage based tuning

a.       Appn Layer

                                i.  Minimise db requests

                                ii. Increase concurrency

                                iii. Methods

1.       Rewrite code

2.        Indexing, partitionig,  denormalization

3.       Follow  best practices

4.       Tuning using hints,  stored outlines, profiles, sql rewrites

5.       Parallelism, optimizer setting

b.      Db Code Layer

1.               Detection & dealing with oracle internal locks.

c.       Memory Layer

                                          i. Adequate memory at OS layer

                                          ii. Resource allocation

                                          iii. AMM (automatic memory management)

d.      Disk layer

                                         i. Bandwidth

                                         ii. iostat commands           

                                         iii. RAID tech

                                         iv. Ssd, ASM

2.       Instance tuning

       

3.       SQL Tuning

a. Index wisely

b. Reduce parsing

c. Cost based optimizer

d. Optimize table scans - keypool, buffercache, keepcache, recycle pool

e.  Optimize joins

f.   Environment

g. Use array processing

4.       Golden rules of tuning

a. Define objective     

b. Know when to quit

c.  Prevention is better than cure

d. Change one at a time

e. Identify the cause not effect       

f.   Accurate measurement, current performance

5.       Tuning Tools

a.       Explain Plan

                                          i. Reveals execution plan for an sql statement

b.       Oracle Tracing & tkprof

                                          i. Autotrace on

                                          ii. dump trace file

                                          iii. tkprof for formatting

c.       AWR (Automatic Workload Repositories)

                                          i.      In 10g

                                          ii.      Collects processes db.os statistics for problem detection

                                          iii.      Snapshot

                                          iv.      ASH

d.      ADDM (Automatic Database Diagnostic Monitoring)

                                          i.      Analyse awr data on a regular basis

e.      SQL Tuning Advisor       

                                          i.      Generate recommendations based on reports

                                          ii.      Source from any above