Use SQL Tuning Advisor via SQL Developer for SQL Tuning

Oracle SQL Developer provides SQL Tuning Advisor to tune the SQL commands in your Oracle Database Exadata Express Cloud Service.

To use the SQL Tuning Advisor from Oracle SQL Developer, you must perform the following steps:

1. Install Oracle SQL Developer locally, and create a cloud connection from Oracle SQL Developer to your Exadata Express service. See Connect SQL Developer.

2. Write the SQL query to be analyzed in the SQL Worksheet, and click the SQL Tuning Advisor...(Ctrl+F12) icon in the SQL Worksheet toolbar.

   • The SQL Tuning Advisor task is initiated based on the current statement in the SQL Worksheet.

   • Once the task completes, the SQL Tuning Advisor page opens up with performance tuning suggestions under different categories like Statistics, SQL Profile, Indexes. For each category, you can see an overview as well as detail information.

   • It may also provide a sample restructured SQL which can improve the query performance.

3. On each page, you can click the Run SQL Script (Alt+F11) button, if you wish to implement the recommended changes. Alternatively, you can click the Open SQL Script Worksheet (Alt+F12) button, if you wish to review the recommendations, and make changes before implementing it.
   
   

   
   Description of the illustration slq_tuning.png

   
   

   Note:

   In case, you want to use the SQL Tuning Advisor from a connection other than the default administrator schema i.e; PDB_ADMIN, advisor and administer sql tuning set permissions must have been granted already.