Oracle Veda

Monthly Archive: May 2023

Use ChatGPT to Tune SQL

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.

 

May 14, 2023 Oracle DBA, , , 0 Read more >

Use ChatGPT to Find Top SQL

You can now use ChatGPT to rapidly create scripts. You still need to know what to do. The process of finding how to do it is now much simpler. You don’t need to use Google to get a list of urls and browse them. And you certainly don’t need to remember commands and syntax. Here’s an example.

 

Prompt: Write an Oracle SQL script to identify most expensive queries in the last hour.
ChatGPT: Here’s an Oracle SQL script that can identify the most expensive queries in the last hour:

SELECT sql_text, executions, elapsed_time, cpu_time
FROM (
SELECT sql_id, sql_text, executions,
elapsed_time_delta/1000000 AS elapsed_time,
cpu_time_delta/1000000 AS cpu_time,
disk_reads_delta, buffer_gets_delta,
ROW_NUMBER() OVER (ORDER BY elapsed_time_delta DESC) AS row_num
FROM v$sql
WHERE last_active_time > SYSDATE – (1/24) — filter for queries executed in the last hour
ORDER BY elapsed_time_delta DESC
)
WHERE row_num <= 10; — adjust this number to show more or fewer queries

This script uses the v$sql view to retrieve information about SQL statements that have been executed recently. The elapsed_time_delta and cpu_time_delta columns provide information about the execution time and CPU time for each query. The script sorts the queries by the elapsed_time_delta column in descending order, and then uses the ROW_NUMBER function to assign a row number to each query. Finally, the script selects the top 10 queries based on the row number. You can adjust the row_num condition to show more or fewer queries.

 

 

May 14, 2023 Oracle DBA, , , 0 Read more >