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

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 Mon Mar 05 2001 - 18:31:07 CST