longops vs sql

From: Ben <balvey_at_comcast.net>
Date: 7 Sep 2006 07:22:53 -0700
Message-ID: <1157638972.274900.23040@e3g2000cwe.googlegroups.com>

Oracle Server 9.2.0.5 Ent Ed AIX5L

The following SQL is supposed to give me disk reads that take longer than 10 seconds.

SELECT sql_text nl, 'Executions= ' ||

       executions nl,
	   'Expected Response Time in Seconds= ',
	   disk_reads / decode(executions, 0, 1, executions) / 300 "Response"

FROM v$sql
WHERE disk_reads / decode(executions, 0, 1, executions) / 300 > 10 AND executions > 0
ORDER BY hash_value, child_number

And the following gives me long running SQL.

SELECT b.username||'/'||b.osuser, sql_text, a.sofar, a.totalwork, a.units
FROM v$sql, v$session_longops a, v$session b

WHERE a.sql_address = address
AND	  a.sql_hash_value = hash_value
AND	  a.sid = b.sid
AND	  a.serial# = b.serial#

ORDER BY address, hash_value, child_number

My question is what is the difference in SQL that takes longer than 10 secs for disk reads and the sql from the longops view? Some of the disk read sql doesn't show in the longops view, but I thought that SQL statements that ran longer than 6 secs were supposed to be in longops.

Does the v$sql view display all SQL in the library cache? Then the longops view only shows currently executing SQL that is running longer than 6 secs? I would appreciate some explanation on this. I've searched the group already and couldn't find a good comparison.

Thanks,
Ben Received on Thu Sep 07 2006 - 09:22:53 CDT