Re: SQL-Statements / full-table scans

As a variation on that theme:

    select ... from v$sql
    where buffer_gets < 2 * disk_reads     and disk_reads > {some arbitrary limit}

This TENDS to catch SQL with tablescans - but a high proportion of disk_reads does not guarantee tablescans (nor does a small
proportion guarantee that you are not
doing tablescans - the table might be
in-memory all the time).

The original query has an error, by the way: the join to dba_objects should be on
data_object_id, not object_id. For a large buffer, or large data dictionary, it might just be cheaper to switch on sql_trace
from time to time.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____England______January 21/23


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





Alistair Thomson wrote in message ...


>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

>>

>>

>

>