Oracle Top SQL Statements Report
Displays SQL statements consuming the most resources, ordered by reads per execution. Includes buffer gets, disk reads, executions, sorts, and SQL address for further inspection. Useful for performance tuning and identifying expensive SQL statements.
oraclesqlperformance-tuningv1.0.0
0 stars0 downloads16 views1 comments
By OracleDba • Created
Code
(26 lines)1234567891011121314151617181920212223242526
-- -----------------------------------------------------------------------------------
-- File Name : https://oracle-base.com/dba/monitoring/top_sql.sql
-- Author : Tim Hall
-- Description : Displays a list of SQL statements that are using the most resources.
-- Comments : The address column can be use as a parameter with SQL_Text.sql to display the full statement.
-- Requirements : Access to the V$ views.
-- Call Syntax : @top_sql (number)
-- Last Modified: 15/07/2000
-- -----------------------------------------------------------------------------------
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
SELECT *
FROM (SELECT Substr(a.sql_text,1,50) sql_text,
Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution,
a.buffer_gets,
a.disk_reads,
a.executions,
a.sorts,
a.address
FROM v$sqlarea a
ORDER BY 2 DESC)
WHERE rownum <= &&1;
SET PAGESIZE 14