Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: performace question...

Re: performace question...

From: Joel Garry <>
Date: 13 Apr 2004 13:54:21 -0700
Message-ID: <>

Vince Laurent <> wrote in message news:<>...
> Quick question: Are full table scans bad for performance? I am
> guessing yes. I ran this script:
> set serverout on size 1000000
> set verify off
> col object_name form a30
> PROMPT Column flag in x$bh table is set to value 0x80000, when
> PROMPT block was read by a sequential scan.
> spool bufferts.lst
> SELECT o.object_name,o.object_type,o.owner
> FROM dba_objects o,x$bh x
> WHERE x.obj=o.object_id
> AND o.object_type='TABLE'
> AND standard.bitand(x.flag,524288)>0
> AND o.owner<>'SYS';
> spool off
> and the result on one of our DB was 5600! Since Sunday's backup! How
> can I tell the vendor that thier appliction, um, er... works harder
> than it needs to?

You haven't really shown that it does. Explain plan on the code, and maybe even post the code. Some code does indeed need a full table scan. Depends on many things - look for a thread here a while back about full table scans on very small tables. Also about myths having to do with what percentage of rows need to be hit for fts to be beneficial.

Version, platform and optimizer settings may make a difference, too. Not to mention some clue about how the vendor is writing code - OCI? ODBC? PL? Now, if the code is doing a full table scan on a large table to get a single row, that is going to be bad - especially if that fts is in a loop reading another large table. If you can tell the vendor precisely where that is happening in that code, maybe they'll blow you off... and slipstream fix it in the next release in embarassment. Or maybe suggest you add an index.

OEM has explain plan in it, you don't have to be trick these days.


-- is bogus.  
Happy Birthday, Thomas Jefferson!
Received on Tue Apr 13 2004 - 15:54:21 CDT

Original text of this message