8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Script: top_sql.sql
( Download Script )
-- ----------------------------------------------------------------------------------- -- 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