Re: SQL-Statements / full-table scans

Hi

I use something like

select sql_text, disk_reads, executions from v$sqlarea where disk_reads in
(select max(disk_reads) from v$sqlarea);

This shows the statement that has the most disk access - divide by executions to give disk hits per execution.

Alistair

"Klaus Brunckhorst" <axe_at_mcs-hh.de> wrote in message news:askre7$he0$1_at_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 - 09:24:03 CST