Using ChatGPT to tune a SQL statement is easy. Here’s an example.
Prompt: Write an Oracle SQL script to tune a SQL statement based on SQL ID.
ChatGPT: Here’s an Oracle SQL script that can be used to tune a SQL statement based on SQL ID:
Here’s an Oracle SQL script that can be used to tune a SQL statement based on SQL ID:
DECLARE
l_sql_text CLOB;
l_sql_id VARCHAR2(13) := ‘<SQL_ID>’; — replace with the SQL ID of the statement you want to tune
BEGIN
SELECT sql_fulltext INTO l_sql_text FROM v$sql WHERE sql_id = l_sql_id;
DBMS_SQLTUNE.create_tuning_task(sql_text => l_sql_text, task_name => l_sql_id);
DBMS_SQLTUNE.execute_tuning_task(task_name => l_sql_id);
DBMS_SQLTUNE.drop_tuning_task(task_name => l_sql_id);
END;
This script creates a SQL tuning task for the SQL statement with the specified SQL ID, executes the task, and then drops the task. The sql_fulltext column in the v$sql view contains the complete text of the SQL statement, which is used as input to the create_tuning_task procedure. The task_name parameter is set to the SQL ID to ensure that the task is associated with the correct statement. The execute_tuning_task procedure executes the task, which can take several minutes to complete depending on the complexity of the statement. The drop_tuning_task procedure removes the task from the database once it is completed.
After executing this script, you can view the tuning results by querying the dba_advisor_log table. This table contains information about all tuning tasks that have been executed on the database. To view the results for the task created by this script, you can use a query like this:
SELECT dbms_sqltune.report_tuning_task(‘<SQL_ID>’) FROM dual;
This will generate an HTML report that provides recommendations for tuning the SQL statement.