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.

Creating a materialized view

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.

Final Statistics Comparison

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.

dbForge Studio for Oracle

Multifunctional Oracle GUI

Download

Availability in the editions of dbForge Studio for Oracle

Feature

Enterprise
Professional
Standard
Express
Query Profiler
Yes
Yes
Yes
None