EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials Oracle Tutorial Oracle Performance Tuning
 

Oracle Performance Tuning

Updated March 4, 2023

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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:

Oracle Performance Tuning 1

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:

Oracle Performance Tuning 2

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:

index we altered just is been used for monitoring or not

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:

by using another ALTER statement

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.

Recommended Articles

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW