Home

Oracle database performance methodology evolution - from 9i to 10g and 11g

Submitted by iniunet on Mon, 10/10/2011 - 10:59

Recently I re-read the performance document of Oracle Database 9i,10g and 11g.  It is interesting to notice that different performance tools available with different version of Oracle database.  The newer version always introduce some new concept that lead to new performance tuning methodology.

This evolution of performance methodology could be best descrbied with the following:

  1. from re-active performance tuning to pro-active performance management
  2. from mannual problem investigation to automatic problem finding and resolution.
  3. Change management

And here is the outline of  tools and concepts available for 9i database  (from the point of view of performance related)

Oracle database 9i performance tools and concepts

 

  • Database instance

configuration,IO,Shared Pool,Buffer Cache,Lock contention,...

  • SQL

Sort operations,Hints,Stored Outline,Object Statistics,System statistics

  • Tools

EM,statspack,V$*,Wait Event,Utlbstat/Utlestat,TKprof

  • OS

paging,memory,IO,CPU

 

Comparing to 9i, Oracle introduce a lot of concepts and tools for 10g database ( parts highlighted in green color is introduced with 10g version)

Oracle database 10g performance tools and concepts

New for 10g

 

  • SQL

Metrics,Threshold,Alert,Baseline,Sql Tuning Set(STS),Sql profile

  •  Tools

Time Model,AWR,ASH,diagnostic pack,SQL tuning advisor,ADDM

 

And 11g introduce relativly fewer concepts and tools (parts highlighted in red color is introduced with 11g version)

 

New for 11g

 

  • SQL

SQL plan Baseline(Sql Plan Management/SPM),SPA

  •  Tools

Database Replay

 

It is obvious that 10g is much different from 9i comparing to the difference between 10g and 11g. The introduction of Metrics and baseline in 10g has turned the attention of Oracle database performance tuning into pro-active and preventive peroformance management.   Metrics/Baseline enable the detection of abnormality using periodic comparison of the same business scenario.  And the ability of sending alerts after abnormality dectection enable DBA to resolve the problem before It become rampant.  SPA(SQL Performance Analyzer) and SQL baseline stabilize the system performance even for making any change to the system.  ADDM and the SQL tuning advisor has make the life of DBA much more easier and they could focus their attention on overall system performance rather than immaterial detail.