SQL-Statements / full-table scans

From: Klaus Brunckhorst <axe_at_mcs-hh.de>
Date: Wed, 04 Dec 2002 13:24:35 +0100
Message-ID: <askre7$he0$1@garnet.hamburg.cityline.net>

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 Received on Wed Dec 04 2002 - 06:24:35 CST