In addition to Oracle AWR and ADDM reports for tuning Oracle databases, Oracle SQL Developer also provides the SQL Tuning Advisor to help analyze SQL statements and offer recommendations to improve performance.

In one case, a customer reported very long search times for a particular query. The query was captured from Oracle's V\$SQLAREA view, submitted via SQL Developer, then SQL Tuning Advisor was run for that query.

Note: The Oracle user running SQL Tuning Advisor requires the following System Privileges:

ADVISOR
ADMINISTER SQLTUNING SET
SELECT ANY DICTIONARY

The recommendations from SQL Tuning Advisor looked something the following:

SQL Profile Finding (see explain plans section below)
------------------------------------------------------------------------
A potentially better execution plan was found for this statement.

Recommendation (estimated benefit: 98.49%)
--------------------------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'staName71300',
task_owner => 'ACDDM', replace => TRUE);

Notice the estimated benefit was 98.49%!

The recommendation was to implement the reported SQL profile.

Before implementing the profile, it took several minutes to return the search results.
After implementing the profile, results were returned in less than 6 seconds.

For more information on AWR reports, see Oracle’s documentation, for example:

Using Automatic Workload Repository for Database Tuning: Tips for Expert DBAs

Here is some information on Oracle SQL Tuning Advisor:

SQL Tuning Guide 21​​​​​​​