Performance Tuning in Oracle – Part3

Share via:

Performance Tuning Tools


This is the continuation to the previous article about the Tools used in the performance Tuning.

Please find the previous article here.

Performance Tuning in Oracle – Part2

In this article we will discuss about

  1. Oracle SQL Tuning advisor and baseline.
  2. OS Monitoring.


Oracle SQL Tuning Advisor and Baselines:

Oracle SQL Tuning Advisors can be used for the instance level tuning.

SQL tuning advisor invokes the Automatic Tuning Optimizer to perform the SQL tuning on the statements that it takes as an input. It takes one or more SQL statements to perform this action.

There is an option enable the maintenance window when you install the Oracle DB. One of the jobs of this maintenance window is to run the SQL tuning advisor. This will identify the problematic SQLs which has ran earlier in the 24 hours and try to automatically fix the SQL statements if that can be fixed automatically by the instance. It will fix the issues related to the statistics and optimizer.

To Enable the automatic SQL tuning advisor, follow the below query.

To disable the automatic SQL tuning advisor, follow the below query.

The part of the automatic SQL profiles is, from the AWR snapshots that has been captured, it will identify the SQL statements which require the tuning. Then it will generate the recommendations in the background and test the profile of the SQLs with the fixes, implements them and automatically tunes the reports.

So, it will create the SQL profiles and apply them. When there is any SQL statement is being executed, with that profiles it will take the best optimizer plan and runs it with the better performance and better execution plan.

We can run the SQL Tuning advisor manually using the below statement.

Tuning recommendations include:

  1. Collection of object statistics
  2. Creation of indexes
  3. Rewriting SQL statements
  4. Creation of SQL profiles
  5. Creation of SQL plan baselines

This will only give the recommendations and it is totally our call to actually implement the recommendations or not.

Baselines are used when there is a SQL statement behaving different at different times.



OS Monitoring is also an important task in Performance tuning. The 2 major resources to be monitored in OS are CPU and memory. Other than these 2 resources, I/O and network monitoring are also important in terms of the performance tuning.

Few Commands to monitor CPU and Memory on Linux platform are:

To identify the Top 10 PIDs and commands using CPU .

To Identify the total usage of CPU using SAR:

To identify the total CPU usage excluding SYSTEM using TOP

Memory monitor commands:

By this we conclude the topic of the Performance Tuning Tools and we will discuss more in depth on how to use these tools in my upcoming articles.

Thank you…




Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (8 votes, average: 5.00 out of 5)

Add Comment