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

No comments:

Post a Comment