Re: oracle full table scan

From: Joan Hsieh <joan.hsieh_at_tufts.edu>
Date: Thu, 03 Apr 2003 06:03:38 -0800
Message-ID: <F001.00579463.20030403060338@fatcity.com>

Hi Arvind,

I don't judge full table scan is good or not necessary bad. this is the script might answer your question.

-joan

The following scripts provide information on the full table scan activity.
If your application is OLTP only, having long full table scans can be an indicator of having missing or incorrect indexes or untuned SQL.

#

drop table Full_Table_Scans
/
create table Full_Table_Scans as
 select ss.username||'('||se.sid||') ' "User Process",

 sum(decode(name,'table scans (short tables)',value)) "Short Scans",
 sum(decode(name,'table scans (long tables)', value)) "Long Scans",
 sum(decode(name,'table scan rows gotten',value)) "Rows Retreived"

   from v$session ss, v$sesstat se, v$statname sn   where se.statistic# = sn.statistic#

     and (name  like '%table scans (short tables)%'
         OR name  like '%table scans (long tables)%'
         OR name  like '%table scan rows gotten%'     )
     and  se.sid = ss.sid
     and   ss.username is not null

group by ss.username||'('||se.sid||') ';

column  "User Process"     format a20;
column  "Long Scans"       format 999,999,999;
column  "Short Scans"      format 999,999,999;
column  "Rows Retreived"   format 999,999,999;
column  "Average Long Scan Length" format 999,999,999;

ttitle ' Table Access Activity By User '

select "User Process", "Long Scans", "Short Scans", "Rows Retreived"   from Full_Table_Scans
 order by "Long Scans" desc;

Richard Foote wrote:
>
> Hi Arvind,
>
> A little test for you.
>
> You have a table that contains 10,000,000 rows that is packed tightly
> into 1,000,000 data blocks.
>
> You have an index that has a level of 4 and has 10,000 leaf blocks.
>
> The table is well striped across a number of devices and you have 4
> CPUs on the box.
>
> You write a simple select statement that queries the table based on
> the indexed column and *just 10%* of the data needs to be retrieved.
>
> You determine that the CBO has performed a full table scan.
>
> Do you break out into a nervous sweat or do you sigh thank goodness
> and worry about something else instead ?
>
> Cheers
>
> Richard (let me know if you want to know the comparative costs ;)
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Thursday, April 03, 2003 2:58 PM
>
> > Dear All,
> >
> > is there any way to find which tables (table name) are
> suffering from
> > full table scan ,so that i can create indexes on them to enhance the
> > performance.
> >
> >
> > Thanks
> >
> > Arvind
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Arvind Kumar
> > INET: arvindk_at_sqlstarintl.com
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
> >
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Richard Foote
> INET: richard.foote_at_telstra.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Joan Hsieh
  INET: joan.hsieh_at_tufts.edu

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu Apr 03 2003 - 08:03:38 CST