Products
- Database Tools
  
  Must-have tools for database development, data analysis, data management, and server administration
  
  SQL Server Tools
  
  MySQL Tools
  
  Oracle Tools
  
  PostgreSQL Tools
  
  Data Connectivity
  
  Wide choice of world-class data connectivity solutions for various data connection technologies and frameworks
  
  ADO.NET Data Providers
  
  ODBC Drivers
  
  SSIS Components
  
  Excel Add-ins
  
  Delphi Data Components
  
  dbExpress Drivers
  
  Productivity Tools
  
  Tools that help developers to write code, conduct code reviews, compare sources, track the working time, and much more
  
  Coding Assistance Tools
  
  ORM Solutions
  
  Time Tracking App
  
  Data Services
  
  All-in-one cloud data platform for no coding data integration, data access, cloud to cloud backup, and management
  
  Data Integration Services
  
  Cloud to Cloud Backup
  
  Online SQL Tools
  
  Web API Server
  All Products Free Products Devart for Enterprise
Store
Support
- Tech support
  
  Questions or problems? Our dedicated support teams can answer product related questions.
  
  Request Support
  
  Documentation Center
  
  Forums
  
  Using Website
  
  Sales support
  
  Answers to all common questions that you might have and a bit more.
  
  Ordering FAQ
Blog
Company
- About us
  
  Learn more about the company, people, vision, and history
  
  About us
  
  Customers
  
  Partners
  
  Resellers
  
  Contacts
  
  Resources
  
  Get updated with the latest worldwide events.
  
  Events
  
  Vacancies
  
  Community
  
  Join our community of technology experts. Learn something new or share your experience with a wide audience.
  
  CodingSight

How to Tune PL/SQL Performance with Oracle SQL Profiler

Oracle SQL Profiler helps optimize SQL query performance in Oracle databases using EXPLAIN execution plan and session statistics, as well as controlling resource allocation used to execute the queries efficiently.

On this page, you can find out how to tune query performance with dbForge Studio for Oracle on a particular example of an Oracle SQL query.

Slow-running queries can be time- and server resource-consuming. This may cause poor database performance and longer query execution, as well as impact a client application. Thus, to resolve performance issues efficiently, it is critical to apply query optimization and performance tuning techniques.

In dbForge Studio for Oracle, you can utilize Query Profiler, the ultimate query performance tuning and optimization tool that allows you to:

Examine causes for slow-running queries and bad performance
Monitor query performance using the Oracle explain plan
Search for database bottlenecks and analyze its impact on database performance
Manage loads increased on database
View query profiling results data to prevent possible query performance issues
Compare query performance to identify what operations take longer time and more resources
Decrease database workload with the help of indexes

Activating a Query Profiling Tool

To begin with, we create a query that returns records of the EMPLOYEEDEPARTMENTHISTORY table for employees from certain regions defined by the NATIONALIDNUMBER column of the EMPLOYEE table.

On the Standard toolbar, we click New SQL. In a SQL document that opens, we type a SELECT statement with the WHERE condition.

To start tuning our query, we need to enable Query Profiling Mode on the SQL toolbar prior to executing the query.

How to enable a query profiling mode in dbForge Studio for Oracle

Executing the Query in Query Profiling Mode

After the Query Profiling mode has been enabled, we can execute the query. The query profiling results are displayed as a grid in the Profiler mode where you can view and analyze what should be done and in what step to optimize the query performance.

The Oracle SQL Profiler tree displays information in two tabs:

Plan that shows the EXPLAIN results returned by the SELECT executed query.
Session statistics that show statistics of the server resource used for each query execution.

Displaying query profiling results on the Oracle SQL Profiler tree

Analyzing Session Statistics

Assessing session statistics is critical for efficient query performance tuning in Oracle. Besides, it can help you identify server and database performance issues that may decrease overall performance.

The Oracle SQL Profiler tool collects and processes statistics on each step for the current session of query execution. You can use this information to explore how much resources have been consumed by the query for the current connection.

How to analyze session statistics in an Oracle SQL Profiler tool

Adding an Index to the Query

Using indexes in SQL queries is an effective way to improve query performance, accelerate data retrieval operations, and reduce query response time. With the help of indexes, you can make it easier and faster to access and sort rows in the query.

Since we deal with a query containing a subquery, the results are far from perfect. To make things better, we change the query by creating the index for the table in a SQL document.

How to create an index to optimize query performance in dbForge Studio for Oracle

Analyzing Oracle Explain Plan

After the index was created, we profile our query again by simply clicking Get New Results on the Query Profiler document view. The Plan tab shows that now the query is executed with the created key.

The EXPLAIN plan allows you to view what was checked on each step of the query execution, including optimization approaches the optimizer used, the number of rows processed, indexes to speed up the query, the amount of time (in units) it is expected to take to run the query, etc.

How to analyze Oracle Explain results plan

Comparing Query Profiling Results

To check if the changes have reduced query execution time, we can compare the profiling results of two queries.

To see the differences, we switch to the Session Statistics tab and, holding the Ctrl key, select the nodes of the required query profiling results in the Query Profiler tree. The differences will be highlighted on the grid with the date and time of query execution.

Comparing query profiling results of two queries using Oracle SQL query analyzer

Adding Views to Optimize Performance

To enhance query performance even better, we can create a view with a CREATE VIEW statement instead of the subquery (1).

After that, we will rewrite the query with the INNER JOIN statement (2).

As you can see, using views in SQL statements can significantly boost the execution of the statement, improve query response time, and help you investigate performance issues.

Comparing Query Performance Profiling Results

Now, it's time to get new results again and compare the performance of all three profiling results: without index, with an index, and with a view.

The grid displays that the execution time and session statistics have been improved drastically: the query has become much faster and consumed fewer server resources.

Conclusion

Oracle Profiler is a powerful query optimization tool to fine-tune SQL queries and improve Oracle query performance. The tool allows you to easily view and analyze how good or poor the performance of your query is, quickly detect bottlenecks in query execution and troubleshoot database performance issues, as well as improve SQL query performance, and, therefore, optimize server resources.

In addition to query optimization and performance tuning capabilities, dbForge Studio for Oracle offers a lot of useful and powerful database development and management tools.