The Oracle RDBMS provides multiple views detailing the current activities within the database, which can be used to perform analysis on performance. This article covers the user of the views: V$SQL, V$SESSION_LONGOPS, and V$SESSION to determine the longest currently occurring SQL statements within our database and who is executing them. This procedure will work on any OS.
1. Logon to you Oracle database server.
2. Logon to SQLPLUS with sysdba privileges.
mylinux:> sqlplus ‘/ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Sat Dec 26 08:08:39 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
3. Execute the following SQL statement.
select b.username, b.SID, b.SERIAL#, c.totalwork||’ ‘||c.units,
c.time_remaining, a.sql_text from v$sql a,
and c.time_remaining>0 and b.username<>‘SYS’
4. The returned values are as follows:
USERNAME – User executing this statement.
SID – System ID of session executing.
SERIAL# – SERIAL# of session executing.
TOTALWORK+UNITS – Total Work and units of measure of work.
TIME_REMAINING – Estimate in seconds of time remaining.
SQL_TEXT – SQL being executed.
5. This information can be used to determine current performance and possible performance problems.
Larry J. Catt, OCP 9i, 10g