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

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 - 11:17:27 CST