How to Run SQL Tuning Advisor in Oracle 19C Manually | 6 Steps Procedure

To run SQL Tuning Advisor manually in Oracle Database 19c, you can use the SQL*Plus command-line tool or Oracle SQL Developer. 

You need to identify slow SQL, create a tuning task in Oracle 19c using DBMS_SQLTUNE, execute it, and follow recommendations for better performance. Manual tuning is essential for complex queries and allows control over indexing, resource usage, and adaptation to application changes.

Manual SQL tuning is necessary in certain situations to optimize the performance of SQL queries and, consequently, the overall performance of a database.

Procedure of Running SQL Tuning Advisor in Oracle 19C Manually

Here are the steps on how to manually run the SQL Tuning Advisor in Oracle 19c:

1. Identify the SQL Statement to Tune

Use tools like SQL Developer, Enterprise Manager, or SQL*Plus to find the SQL statement causing performance issues. Note its SQL_ID or hash value.

2. Create a Tuning Task:

Use the DBMS_SQLTUNE package to create a tuning task:

SQL

EXEC DBMS_SQLTUNE.CREATE_TUNING_TASK(

  sql_id       => ‘your_sql_id_here’,  — Replace with the actual SQL_ID

  task_name    => ‘my_tuning_task’,

  scope        => DBMS_SQLTUNE.scope_comprehensive,

  time_limit   => 60,  — Set a time limit in minutes

  description  => ‘Tuning task for my query’

);

3. Execute the Tuning Task:

Run the tuning analysis:

SQL

EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => ‘my_tuning_task’);

4. Review the Recommendations:

View the tuning advisor’s recommendations:

SQL

SELECT *

FROM   TABLE(DBMS_SQLTUNE.REPORT_TUNING_TASK(‘my_tuning_task’));

5. Implement Recommendations (Optional):

Carefully review and implement suggested changes, such as: 

• Creating indexes
• Rewriting SQL statements
• Gathering statistics
• Adjusting database parameters

6. Drop the Tuning Task (Optional):

Clean up the completed task:

SQL

EXEC DBMS_SQLTUNE.DROP_TUNING_TASK(task_name => ‘my_tuning_task’);

Why Is It Necessary to Tune It Manually

While the Oracle Database Management System (DBMS) includes automatic features for performance tuning, manual tuning is required for several reasons:

1. Complex Queries: Automatic tuning tools might not always be able to fully understand and optimize highly complex or non-standard SQL queries. Manual tuning allows database administrators (DBAs) to apply their expertise and insight to optimize such queries effectively.
2. Indexing Strategies: While automatic tools can suggest indexes, DBAs may need to make decisions based on a broader understanding of the database schema, query patterns, and overall performance goals.
3. Query Hints and Optimizer Features: Manual tuning allows DBAs to use query hints or leverage specific optimizer features that may not be automatically applied. This level of control is essential for addressing unique situations or edge cases.
4. Application Changes: Changes in the application code or schema may necessitate adjustments to SQL queries that automatic tuning tools may not be aware of. Manual tuning allows for quick adaptation to changes in the application environment.
5. Performance Testing: DBAs may perform detailed performance testing with various tuning configurations to ensure that changes do not have unintended consequences. This level of testing is often beyond the capabilities of automatic tools.

Frequently Asked Questions

How do I view the recommendations generated by SQL Tuning Advisor in Oracle 19c?

To view the recommendations, query the USER_ADVISOR_RECOMMENDATIONS view. This view provides details about the recommended changes to improve the SQL statement’s performance.

Are there any considerations before running SQL Tuning Advisor manually?

Before running SQL Tuning Advisor, ensure that you have the necessary privileges, and review the SQL statement to be tuned. It’s also essential to have a backup and understand the potential impact of the recommendations.

Can I use Oracle SQL Developer to run SQL Tuning Advisor manually?

Yes, Oracle SQL Developer provides a graphical interface for running SQL Tuning Advisor. You can use the SQL Tuning Advisor wizard to create tasks, monitor progress, and implement recommendations.

Conclusion

Before implementing recommendations, thoroughly test changes in a non-production environment to validate performance improvements and avoid unintended consequences. Additionally, create a rollback plan to swiftly revert changes in case issues arise during the production deployment of SQL Tuning Advisor recommendations.