Re: How to Monitor if "table scans" occur

mikea730_at_my-deja.com a écrit :
>
> Is there any kind of performance view that collects statistics on table
> scans. I'm familiar with using "EXPLAIN PLAN" when the SQL statement
> is known. I need to monitor from the server side to determine if an
> excessive number of table scans are occuring. I need to then be able
> to isolate the SQL statement.

1. Have a look at the statistic "table scan rows gotten" (v$sysstat)
2. Spool out the contents of v$sqlarea and run explain against the most expensive statements (may need to have a look at v$sqltext (join on hash_value)
3. Try using statspack if you're on 8.0.5 or higher : http://www.oracle.com/oramag/oracle/00-Mar/o20tun.html

-- 
Christian GILBERT
cgilbert_at_mail.dotcom.fr
IBM Certified Specialist - AIX System Administration