Resolve

issues faster

Database Tools
Must-have SQL Server tools and tools for MySQL, Oracle, and PostgreSQL database development, management and administration
Learn more

Data Connectivity
The most trusted solutions for your data connectivity needs: ADO.NET, ODBC, SSIS, Excel, dbExpress, Delphi Components
Learn more

Productivity Tools
Write code, create reviews, compare sources, track the working hours with the web app, utilize the power of ORM Solutions
Learn more

Data Services
Data integration, online backup, web-based SQL tools, cloud connectivity solution
Learn more
Previous Next

dbForge Studio for Oracle

How to Tune PL/SQL Performance with Oracle SQL Profiler

Long-running queries is a headache for every developers. They consume our time and server resources. Thus, it's obvious that Oracle query tuning is an issue of primary importance.

Query Profiler included into dbForge Studio for Oracle is the ultimate query performance tuning tool that allows you to quickly locate bottlenecks in your code and boost performance of your Oracle SQL queries. With our Oracle query optimization tool, you can view detailed execution plans, and session statistics for each run of a query.

In this short article, we would like to show how to tune performance of an Oracle SQL query with dbForge Studio for Oracle.

How to Optimize a Query

We have a query that returns records of the EMPLOYEEDEPARTMENTHISTORY table for employees from certain regions (defined by the NATIONALIDNUMBER column of the EMOLOYEE TABLE):

Oracle SQL Profiler - Initial Query

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

Query profiling results are shown in two tabs:

  • The Plan tab shows the EXPLAIN results returned by the executed query. Use the Plan tab to learn how Oracle executes a SELECT statement. It shows when you need to add an index to a table to increase SELECT performance, optimize joining between tables, etc.
  • The Session statistics tab shows statistics of the server resources usage for each query execution.

Let's switch to the Session statistics tab, and see how many resources have been consumed by our query:

Session Statistics

Since we deal with a query containing a subquery, the results are far from perfect. To improve performance of our query, let?s create the following index:

Creating index

Now, let's profile our query again: for this, we need just to click the Get New Results button on the Profliler document view. The Plan tab shows that now our query is executed with the created key:

Query plan

Let's switch to the Session statistics tab and compare performance of new query profiling results and previous ones. For this, we need to select the nodes of the required query profiling results in the tree view by holding the CTRL key.


As you can see, performance has been slightly improved and execution time has reduced as well..

To get more significant performance changes, let's get rid of the subquery and create the following materialized view instead:


Now, we can rewrite our query in the following way:


Let's get new results again, and compare performance of all three profiling results:


As you can see, the execution time and Session statistics have been improved significantly. Our rewritten query has become much faster and consumes much less server resources.

Summary

dbForge Studio for Oracle provides a powerful tool for performance tuning of your Oracle SQL queries. The tool allows you to:

  • easily view how good (or bad) the performance of your query is;
  • quickly detect bottlenecks in it;
  • improve query performance, and, therefore, optimize server resources.