Full Table Scans
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Full Table Scans

  1. #1
    Join Date
    Jan 2000
    Location
    Manama, Bahrain
    Posts
    50

    Full Table Scans

    Hi!

    Can anyone advise how I can find out if full table scans are occuring in the database? What stats should be collected? What event should I be looking for?

    Thanks.

  2. #2
    Join Date
    Dec 2001
    Location
    Slovenia, Kranj
    Posts
    82

    select *
    from v$sysstat
    where name = 'table scans (long tables)';

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    PHP Code:

    SQL
    set time on
    14
    :38:16 SQLL
      1  select owner
    ,
      
    2         object_name,
      
    3          object_type
      4  from   dba_objects OB
    ,
      
    5         select /*+ NO_MERGE */
      
    6                  distinct obj
      7             from x$bh
      8            where bitand
    (flag,524288) > 0
      9         
    BH
     10  where Ob
    .data_object_id BH.obj and
     
    11        ob.owner != 'SYS'
     
    12*
    14:38:21 SQL> /

    OWNER        OBJECT_NAME                       OBJECT_TYPE
    ------------ --------------------------------- ------------
    SYSTEM       AQ$_QUEUES                        TABLE

    SQL
    set time on

    Now I counted rows from a table
    This table has no indexSo Oracle does FTS.

    14:38:40 SQLselect count(*) from test_table;

      
    COUNT(*)
    ----------
         
    23376

    14
    :38:50 SQL>

    14:38:23 SQL> @FTS

    OWNER        OBJECT_NAME                       OBJECT_TYPE
    ------------ --------------------------------- ------------
    SYSTEM       AQ$_QUEUES                        TABLE
    TAMIL        TEST_TABLE                        TABLE

    14
    :38:59 SQL
    Tamil
    Last edited by tamilselvan; 02-07-2005 at 04:38 PM.

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    This will be better than the previous one.

    PHP Code:

    select owner
    ,
           
    object_name,
            
    object_type
    from   dba_objects OB
    ,
           ( 
    select objRN
              from  
    (select /*+ NO_MERGE */
                            
    obj ,
                            
    row_number() over (partition by obj order by objRN
                       from x$bh
                      where standard
    .bitand(flag,524288) > 0
                    
    )
             
    where RN 1
           
    BH
    where Ob
    .data_object_id BH.obj and
          
    ob.owner != 'SYS' 
    Tamil

  5. #5
    Join Date
    Jan 2000
    Location
    Manama, Bahrain
    Posts
    50
    Thanks Tamil.

    Thats helpful. But how can I find out how often FTS are occuring? I basically don't want one-off FTS, but need to know if the same table is involved over and over again.

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    What is your oracle release?

    Tamil

  7. #7
    Join Date
    Jan 2000
    Location
    Manama, Bahrain
    Posts
    50
    DB is 9.2.0.5 64-bit on HP-UX 11.11

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    There's a system view called v$segment_statistics that will probably tell you all you need to know.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    In 9i/10g, you can query from v$sql_plan that tells you FTS objects.

    Code:
     select object_name , options
     from   v$sql_plan
     where  upper(options) like '%FULL%'
    Tamil

  10. #10
    Join Date
    Mar 2002
    Posts
    534
    A nice document about the "bad" full table scans:

    http://www.ioug.org/tech/IOUGinDefense.pdf

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