Re: Finding full-table scans?

Mikito Harakiri wrote:
>
> select * from v$sql_plan
>
> where operation = 'TABLE ACCESS'
>
> and options = 'FULL'
>
> and object_name in (
>
> select table_name from all_tables
>
> where blocks > 1000
>
> )
>
> grouping is left as an exersize to the reader.
>
> "Sarah Billings" <rukind68_at_yahoo.com> wrote in message
> news:b7b5fcac.0304040733.58fccdb6_at_posting.google.com...
> > I am trying to figure out the v$sql_plan view so that I can display
> > counts of all tables that are over 300 blocks and are getting
> > full-table scans.
> >
> > Has anyone had experience with this?
> >
> > Thanks in advance. S.

You could also temporarily bump up statistics_level to all and take a look through v$sql_plan_statistics to get some more granular plan info.

hth
connor

-- 
=========================
Connor McDonald
http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue"