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):

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:

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:

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:

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.