Wednesday, 30 December 2015

Oracle performance tuning : Startup guide for beginners

Oracle database has very rich diagnostic features that empower an educated end user to diagnose issues and tune the system for best performance. In this post I am giving an broad overview of tools and techniques so that people can focus on what to read from the suggested material in previous post. I will also cover some of the techniques in detail in my posts however the objective of this post is broad overview

Lets look at the problem space

Product uses database, can we maximize the performance by tuning DB?
  • Can we increase the transaction throughput of OLTP type application
  • Can we improve the response time for queries on large data set in data warehousing type environment
  • Mixed batch type distributed solution inserts+ selects: can we maximize the throughput
  • etc

 Other problems
  • Trouble shooting database issue, where performance is fine in the replicated Dev environment but issue in customer environment. How do we compare the configuration and workload on two instances and pinpoint the root cause of issue?
  • Multiple products deployed on the same instance, in case of DB issue which one is causing it?
  • What are the SQLs that are taking most DB time?
  • Are we leveraging the available disks to maximize the available I/O? which tablespaces have most reads and writes and how are they distributed across disks?
  • Which DB sub-system is becoming the bottleneck, is it undersized buffers, the I/O etc
  • etc

 Some questions
    Is the application making optimal use of DB ?
  • Bind variables
  • Proper indexes
  • Tuned SQLs
  • Partitions (spatial locality)
  • Views 
  • Are we leveraging parallelism in DB
  • etc
    Is the database properly sized
  • Cursors
  • SGA
  • PGA
  • Temp space
  • Redo log size
  • etc
    Where is the DB time spent
  • SQLs
  • CPU
  • Waits : This is where there is lot of scope for improvement in performance
  •  etc
Hope the spectrum of performance problems that you can solve using Oracle performance tuning knowledge got you excited or at least interested!

Now lets discuss how you can start building these skills.

  • Get equipped with the knowledge of Oracle architecture.
    • Background Processes, memory structures, logical and physical structures
  • Learn SQL tuning techniques
    • Optimizer 
    • Explain plans
    • Parallel processing concepts
    • SQL tuning advisor
  • Understand AWR report
  • Understand Oracle wait events
    • There are too many of them, just focus on the few that you encounter and learn as you go
The reading material I had suggested in previous post covers most of it except how to read AWR reports. I will share my own experience in reading AWR reports in future posts.

AWR report contains very useful information to give focus to the optimization exercise.

  • You can know how busy the DB was in the period of interest.
  • How much concurrent activity is happening?
  • What is the breakdown of time spent by DB?
  • What your sessions were waiting for, and hence what is the bottleneck?
  • What were the top SQLs?
  • Is too much time being spent in parsing?
  • Which queries are causing most I/O activity?
  • How many log switches are happening?
  • What was the configuration of the DB?
  • Are your buffers optimally sized ?
  • etc etc etc etc!
So many "etc" because the report is so rich with information that even after reading many reports there is always a new learning like "Oh I have not used this section/ information in my analysis before".

AWR report helps in SQL tuning by identifying SQLs to tune. It helps is instance tuning by identifying we are waiting for which subsystem

ADDM can analyse the AWR report to provide recommendations for optimization.

Other stuff which you can venture into later is
  • SQL Trace
  • ASH

Saturday, 5 December 2015

Oracle Database : Good reading material from performance perspective

These are some of the resources that I found very helpful in my quest to tune Oracle performance. I will keep updating the list. My focus is on software performance hence on database performance so I wont be focusing on blogs and resources dedicated to Oracle database administration.


 Oracle performance tuning resources

Oracle blogs


  • Expert Oracle Database Architecture: Oracle Database 9i, 10g, and 11g Programming Techniques and Solutions by Thomas Kyte
  • Oracle Core: Essential Internals for DBAs and Developers by Jonathan Lewis
  • Oracle Database 11g Release 2 Performance Tuning Tips & Techniques (Oracle Press) by Richard Niemiec
  • Forecasting Oracle Performance by Craig Shallahamer
  • Troubleshooting Oracle Performance Christian Antognini

My other posts on Oracle