Performance Tuning Methodologies
- Ratio based tuning
- Wait based tuning
- 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