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.
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.