How to find Full table scan-Urgent
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: How to find Full table scan-Urgent

  1. #1
    Join Date
    Jul 2000
    Location
    brewster,Newyork
    Posts
    87

    How to find Full table scan-Urgent

    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
    sat

  2. #2
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    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.

  3. #3
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    Oh, and for worst queries in STATSPACK, I tend to look at the SQL ordered by buffer gets section.

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    start with v$segment_statistics

  5. #5
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    hi,

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

  6. #6
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    Have a read of spdoc.txt in $ORACLE_HOME/rdbms/admin

  7. #7
    Join Date
    Apr 2003
    Location
    Pune,Maharashtra. India.
    Posts
    245
    Got script here itself. Check following link.......

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

    Rgds
    Parag

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width