Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: How come my SELECT COUNT(1) takes so long...

Re: How come my SELECT COUNT(1) takes so long...

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Tue, 6 Mar 2001 07:44:40 +0100
Message-ID: <ta91mq3vtnhc0f@beta-news.demon.nl>

Hi Van

There are just too many unknowns to give a verdict on that. He may have his whole database on one drive, he may have a very fragmented table, he may have his parameter settings like db_file_multiblock_read_count incorrect, etc, etc, etc.
In my job I have seen that many shitty installs that I wouldn't be surprised at anything.

Regards,

Sybrand Bakker, Oracle DBA

"Van Messner" <vmessner_at_bestweb.net> wrote in message news:fdWo6.411$FQ3.40951_at_monger.newsread.com...
> Hi Sybrand:
>
> Even if he hits all 2000 blocks don't you think 1/2 hour seems awfully
> long?
>
> Van
>
>
> "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> news:87i7at0fafgd0u3lqjrra9oj15446q4is0_at_4ax.com...
> > On Mon, 05 Mar 2001 16:10:44 +0000, Seb
> > <donotreply_at_interbulletin.bogus> wrote:
> >
> > >.. even when my table is empty ??
> > >
> > >I have an 7.3.4 instance on a Sun-Solaris 2.6 server,
> > >in which a SELECT COUNT(1) takes ages (without WHERE
> > >clauses). The anonymous PL/SQL code quoted at the end
> > >of this mail returns me the following:
> > >> Object:MySchema.MyTable
> > >> TOTAL Blocks 35840
> > >> TOTAL Bytes 146800640
> > >> UNUSED Blocks 420
> > >> UNUSED Bytes 1720320
> > >> Last used extent_file_id 30
> > >> Last used extent_block_id 312
> > >> Last used block with last extent (HWM) 2140
> > >
> > >The time it takes doesn't depend on the contents of this
> > >table, because sometimes this table is EMPTY but the COUNT
> > >statement lasts for about half an hour... (this table endures
> > >a strong INSERT/DELETE activity, can go from a few records or
> > >even no rows at all up to hundredths of thousands of rows
> > >within a few hours every day, BUT WHEN I PERFORM THE COUNT I
> > >FIRST CHECK THERE'S NO SUCH ACTIVITY ON IT !)
> > >
> > >How could I remedy this slowliness ? How does Oracle behave
> > >in such a case ??
> > >
> > >Thanks a lot !
> > >Regards,
> > >Seb
> > >
> > >
> > >PL/SQL that computes the high-water mark:
> > >> SET showmode OFF
> > >> SET verify off
> > >> SET serveroutput on
> > >>
> > >> PROMPT Enter the segment owner
> > >> ACCEPT owner char
> > >> PROMPT Enter the segment name
> > >> ACCEPT name CHAR
> > >>
> > >>
> > >> DECLARE
> > >> TOTAL_BLOCKS NUMBER;
> > >> TOTAL_BYTES NUMBER;
> > >> UNUSED_BLOCKS NUMBER;
> > >> UNUSED_BYTES NUMBER;
> > >> LAST_USED_EXTENT_FILE_ID NUMBER;
> > >> LAST_USED_EXTENT_BLOCK_ID NUMBER;
> > >> LAST_USED_BLOCK NUMBER;
> > >> STYPE VARCHAR2(20);
> > >> BEGIN
> > >> SELECT segment_type INTO stype FROM dba_segments WHERE
> > >> owner = UPPER('&owner') AND
> > >> segment_name = UPPER('&name');
> > >> DBMS_SPACE.UNUSED_SPACE (UPPER('&owner') , UPPER('&name') ,
 stype
 ,
> > >> TOTAL_BLOCKS , TOTAL_BYTES ,
 UNUSED_BLOCKS , UNUSED_BYTES ,
> > >> LAST_USED_EXTENT_FILE_ID ,
 LAST_USED_EXTENT_BLOCK_ID ,
> > >> LAST_USED_BLOCK );
> > >> DBMS_OUTPUT.PUT_LINE(' Object:' || Upper('&owner') || '.' ||
 UPPER('&name') );
> > >> DBMS_OUTPUT.PUT_LINE(' TOTAL Blocks '|| TOTAL_BLOCKS);
> > >> DBMS_OUTPUT.PUT_LINE(' TOTAL Bytes '|| TOTAL_BYTES);
> > >> DBMS_OUTPUT.PUT_LINE(' UNUSED Blocks '|| UNUSED_BLOCKS);
> > >> DBMS_OUTPUT.PUT_LINE(' UNUSED Bytes '|| UNUSED_BYTES);
> > >> DBMS_OUTPUT.PUT_LINE(' Last used extent_file_id ' ||
 LAST_USED_EXTENT_FILE_ID);
> > >> DBMS_OUTPUT.PUT_LINE(' Last used extent_block_id ' ||
 LAST_USED_EXTENT_BLOCK_ID);
> > >> DBMS_OUTPUT.PUT_LINE(' Last used block with last extent (HWM)
 '||
 LAST_USED_BLOCK);
> > >> END;
> > >> /
> > >
> > >_______________________________________________
> > >Submitted via WebNewsReader of http://www.interbulletin.com
> >
> >
> >
> > A select count(<whatever) will go to the high water mark. In your case
> > you have a big discrepancy between the last non-empty block and the
> > HWM. It will visit *all* of those 2140 blocks, without exception.
> >
> > You could try to do the following
> > - (This is a tric, and assumes the Cost Based Optimizer, and the
> > compatible parameter on 7.3.3).
> > select /*+INDEX_FFS(foo)*/ count(1)
> > from foo
> > where <primary key column> > 0 or > chr(0)
> > - Optimize the table by either exp/imp or copy it to a temporary one,
> > truncate the table and get it back.
> > IMO: you should really truncate that table using a pl/sql job, when it
> > is empty
> >
> > Also, get away from 7.3.4. It has been desupported, and you can't
> > remain in database pre-historic times 4ever.
> >
> > Hth,
> >
> > Sybrand Bakker, Oracle DBA
>
>
Received on Tue Mar 06 2001 - 00:44:40 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US