Hi,

I will explain Introduction to Performance Management and Tuning in this Oracle Database Performance Tuning Tutorial series.

 

Performance Management and Tuning in Oracle

 

Before start to reading Oracle Performance Tuning tutorial, you need to complete Oracle database tutorials for beginners. You can read them at the following link.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

 

It is expected to solve performance issues by DBA ( Database Administrator ) when performance complaints in the Oracle database begin to increase.

The purpose of Performance tuning is as follows.

• Minimizing response time
• Increasing throughput
• Increasing load capabilities
• Reducing recovery time

 

Before start to solve the performance issue, you need to determine what the problems are and how to solve them.

The solution of performance problems are done in the 3 categories.

• Monitoring and Diagnostics
  – Monitoring using available tools
  – Identifying the problem
  – Using AWR-based tools

• Instance Tuning
  – Tuning memory components
  – Tuning space usage and I/O

•  SQL Tuning
  – Identifying and tuning SQL statements by influencing the
  optimizer
  – Managing change
  – SQL Performance Management
  – Real Application Testing

 

Oracle Monitoring and diagnostics

To start Performance tuning, you should firstly do Monitoring and diagnostics step to define if problem exists or not and what is the problem. Monitoring and diagnostics step includes the monitoring, diagnostic, and reporting tools that Database administrator is used to identify proactively performance problems. DBA cannot start performance tuning without Monitoring and diagnostics.

 

Instance tuning

This step includes memory,  I/O and space management. This division covers using the diagnostic tools to determine the specific problem area and
implement a solution.

 

SQL tuning

Once first and second steps are completed, SQL Tuning should be started. SQL Tuning should be done high-load SQL statements, badly writen SQL statements.

 

 

Performance tuning task is done like the following triangle steps. Start Performance tuning from the top and go on from the TOP to Down.

 

Performance tuning steps should be as follows.

 

1. Identify the problem (OS, database or SQL Statement).

• Check Operating system resource via TOP, VMStat, OSWatcher or etc.
• Check database via AWR Report, Enterprise Manager, ASH, ADDM or etc..
• Check SQL statements via AWR Report, Enterprise Manager or System views to define TOP SQL

2. Check Operating System

• If problem is related with OS resources like High CPU, RAM Utilization or Swap, then firstly check OS then other.
• If CPU or RAM utilization is always very high like %80+ Utilization, then CPU and RAM should be increased.
• If Disk utilization is always high, and if you use SATA disk then you can try to use SAS instead of it. If you use SAS disk, you may use SSD disk instead of SAS.

3. Check Database

• If problem is related with Database like Lock, Concurrency or other database parameters, then firstly check these and tune them.
• If there is a lock in the database, you should kill it firstly in a controlled way.
• If database memory parameters like SGA or PGA is insufficient, then you need to increase them.
• If database statistics are staled, then you need to collect statistics.

 

4. Check SQL Statement

• If you have completed OS and Database problems, and you have still problems, then start SQL Tuning task.
• If your SQL or table need an index, you need to analyze table and create proper index on table.
• If your SQL plan is bad and cost is very big, then you may think SQL Plan change.
• If Index and Plan management of SQL is very good, then you may need to rewrite SQL code.

 

In the Next tutorial series, I will explain each step in detail and as a Article.

 

Next post link about Performance Tuning Tutorial is as follows.

Performance Tuning Features and Tools in Oracle | Oracle Database Performance Tuning Tutorial -2

 

 

 

 

Do you want to learn Top 30 SQL Tuning Tips and Tricks, then read the following articles.

SQL Tuning Tips and Tricks Tutorial in Oracle -3