How to find Full table scan-Urgent

[portal]

Guys,
How can I get information from my db , as what are all the tables are used for FULL TABLE SCAN by the optimizer for a given period.Is there any view we can qry out please suggest me here and also how can I get the info from statspack about top 10 worst qrys by performing.
Thanks

[hacketta1]

In 9i the v$sql_plan and v$sql_plan_statistic views hold this information. Not got Oracle to hand, but you should work it out easily enough. Remember though, that a full table scan does not always = bad. In fact some of the queries most responsible for LIO on your system may actually be ones that are (inappropriately) using indexes. The best strategy is to ask your users what is too slow and then look at those individual queries regardless of their access path. It is quite possible to eliminate all the full table scans and still have a performance problem.

[hacketta1]

Oh, and for worst queries in STATSPACK, I tend to look at the SQL ordered by buffer gets section.

[pando]

start with v$segment_statistics

[yxez]

hi,

I havent tried statspack yet, is it invoked or launch via OEM?

[hacketta1]

Have a read of spdoc.txt in $ORACLE_HOME/rdbms/admin

[paragp1981]

Got script here itself. Check following link.......

http://www.dbasupport.com/oracle/scr...iled/137.shtml

Rgds
Parag