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

Performance Testing Using JMeter in Non-GUI Mode

Performance Testing Using JMeter in Non-GUI Mode

Apache JMeter, the popular open source performance testing tool, can be run in non-gui mode using command prompt.One of the way to resolve java.lang.OutOfMemoryError is by running Jmeter in non-gui-mode. Follow the steps below to run jmeter in non gui mode,

1. Open Jmeter (in gui mode) and prepare test plan for load testing with no of users/threads. Save it as jmx file (eg: test500.jmx).

Note: Avoid adding listeners in the test plan since reports can be generated from jtl file generated in non-gui run.

2. Open command prompt (Run>>cmd) and change directory to bin folder of apache-jmeter. Type the  following command to run jmeter with the saved testplan (test500.jmx).

Jmeter –n –t “path to jmx file “ –l “path to jtl file”

Eg :  D:\JMeter\apache-jmeter-2.11\bin>jmeter -n -t D:\JMeter\TestNonGUI.jmx -l D:\JMeter\Testresults\test500.jtl

Load teting will start and test results will be stored in test500.jtl file.

3.  To analyse the results. Open JMeter and add the listener test element by which you want to analyse it. Eg: To view summary report add summary report listener element as child set jtl file (test500.jtl).