Tag Archive: TopSQL

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.