Re: SQL-Statements / full-table scans

On Wed, 04 Dec 2002 13:24:35 +0100, Klaus Brunckhorst <axe_at_mcs-hh.de> wrote:

>Hello,
>
>somebody out there who knows how to identify which
>SQL-Statements causes the (most) full-table scans on
>a productive server?
>
>I found a statement on the web that counts the full-table scans grouped
>by tablename (Oracle 8i):
>
>SELECT count(*) as Anzahl, o.object_name, o.owner
>FROM dba_objects o, x$bh x
>WHERE x.obj=o.object_id
> AND o.object_type='TABLE'
> AND standard.bitand(x.flag,524288)>0
> AND o.owner<>'SYS'
>GROUP BY o.object_name, o.object_type, o.owner
>ORDER BY Anzahl DESC;
>
>thanx
>Klaus
>

Please refer to v$session_longops. It also contains the address and the hash_value of the sqlstatement in v$sqlarea.

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Wed Dec 04 2002 - 11:16:53 CST