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?
Other problems
Some questions
Is the application making optimal use of DB ?
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
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
- 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.
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
My other posts on Oracle database
Oracle Database : Good reading material from performance perspective
What other product can learn from Oracle database performance management
