| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> How come my SELECT COUNT(1) takes so long...
.. 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;
> /
 |
 |