Oracle Performance Tuning

Introduction to Oracle Performance Tuning

Performance Tuning in Oracle can be defined as the process by which we can optimize (make the best or most effective use of) the performance of the Oracle queries by streamlining the execution of these queries, it also improves the process by which the objects in database are altered, updated or created by improving the response time of the queries, operations of the queries and also improving the communication between different application and Oracle database.

How to Perform Performance Tuning in Oracle?

As we all are aware the Oracle database is a Relational database which means that data is stored in tables and tables are made of rows and columns. The data from a Relational database is retrieved, altered and created with the help of SQL (Structured Query Language). Performance Tuning in Oracle is performed by considering the various elements or systems present in an Oracle RDBMS (relational Database management System) and troubleshoot the source of the performance problem.

In general the performance tuning is done by the DBAs and in general, the performance issues are something when a particular app is facing delays while submitting a form or getting the details shown on the screen of a particular user. In case these issues happen the DBA first looks for the root cause of the issue and it is a tedious task since there are many elements present in the RDBMS and it is quite a time-sensitive to perform the tuning of various components of the Oracle database management system. In general, the Oracle DBA first goes through system-level checks like server connections, instances and tuning them before tuning the individual elements. Once DBAs have done the system level check they can proceed to SQL query tuning.

There are few ways mentioned below in which we can do SQL query tuning:

• We should first isolate the queries which are most frequently used while running an application as performance tuning them will give us better results and one thing we should remember that query tuning is an ongoing process and there will always be scope of improvement.
• We should add indexes to table as it helps us to speed up our execution of queries but one thing to remember that adding too many indexes can actually create performance issues instead of resolving them and we should avoid unnecessary indexing.
• We should try to avoid using * in our SELECT statement unless it is absolutely necessary as adding * might load the system especially if the table has huge columns and load of data.
• We should not use INDEXES with UPDATE and INSERT statement as it will reduce the performance of the query and create issues and similarly also for batch update or insert statements.
• We should not mix data types and should avoid converting numbers to characters and use GTT (Global Temporary Tables) table wherever possible.
• We can also look into the issue of locking. If there are holding locks on database objects then it will make another concurrent session wait. It will cause blocking and may cause deadlock (deadlock happens when two or more users are waiting to access the data which is locked by each other).
• We can also check Activity Monitors. There are statistics available as part of Automatic Workload Repository (AWR) which actually monitors the health of the database and provides reports regarding the performance and health of the database. By checking these reports DBAs can also get an idea of the source of the issue and troubleshooting becomes a lot easier.

Example of Oracle Performance Tuning

We look into index monitoring. In oracle for performance tuning we can set index Monitoring. By enabling index monitoring we can check which Indexes are used in oracle and the index can enabled to monitoring by the owner for a specific amount of time if he wants. In this example we are going to check on that. Let us create an INDEX on a table for monitoring. We will create an index employee_index on table employee.

Let us prepare the query for the same.

Code:

CREATE INDEX employee_index
ON employee (employee_id, employee_name, city, salary);

Let us execute the same in SQL developer.

Output:

As we can see that index has been created now let us enable this index for performance monitoring.

Let us prepare an ALTER statement for the same.

Code:

ALTER index EMPLOYEE_INDEX monitoring usage;

Let us execute the above query in SQL developer and check the result.

Output:

As we can see that the query has been altered successfully and now we will check whether the index we altered just is been used for monitoring or not. In order to check that, we will use SELECT statement to query the table v$object_usage.

Let us prepare an SELECT statement for the same.

Code:

SELECT index_name, used, monitoring, table_ name, START_MONITORING
from v$object_usage;

Let us execute the query in SQL developer and check the result.

Output:

As we can see in the screen shot the table shows that the index is used in monitoring purpose.

We can also stop the monitoring purpose by using another ALTER statement.

Let us prepare an ALTER statement.

Code:

ALTER index employee_index nomonitoring usage;

Let us execute the query in SQL Developer and check the result.

Output:

As we can see in the screenshot the monitoring has stopped.

Advantages of Oracle Performance Tuning

The few advantages of Oracle Performance Tuning are as follows:

• Better Performance Systems.
• The SQL queries take less time to execute.
• The connection between database and application is optimized by proper monitoring of server connections, instances.
• The deadlock or locking situation is avoided better.

Conclusion

In this article we saw about Performance Tuning in Oracle. We started the article by getting to know the definition. We then later saw the ways for performance tuning along with examples and also saw few advantages.

This is a guide to Oracle Performance Tuning. Here we discuss the introduction, how to perform performance tuning in oracle? example and advantages. You may also have a look at the following articles to learn more –

1. Oracle GRANT
2. Oracle Data Types
3. Oracle Synonyms
4. Oracle Date Functions