Oracle-L: RE: Count(*) last 30 seconds

From: Ramon E. Estevez <com.banilejas_at_codetel.net.do>
Date: Wed, 12 Feb 2003 09:54:22 -0800
Message-ID: <F001.0054ADDA.20030212095422@fatcity.com>

Hermant and Chitale,

DB_FILE_MULTIBLOCK_READ_COUNT=32
DB_CACHE_SIZE   big integer 16777216
DB_BLOCK_BUFFERS = 0

Tablespace is LMT with a uniform size of 128 MB, DB not in archive mode is for a DW system.

The time for the first run and the re-run last the same.

To my understanding the table has only one extent. This query runs in about 7 seconds. In my production DB runs inmediately that is in NT also but 8.1.7.

SELECT TABLESPACE_NAME, EXTENT_ID, BYTES/1048576, BLOCKS FROM DBA_EXTENTS
WHERE
SEGMENT_NAME = 'DM_VENTAS' TABLESPACE_NAME EXTENT_ID BYTES/1048576 BLOCKS

------------------------------ ---------- ------------- ----------
DTMVENTAS                               0           128      16384

TKS -----Original Message-----
Chitale
Sent: Wednesday, February 12, 2003 8:59 AM To: Multiple recipients of list ORACLE-L

That's approx 100 records per blocks.
What is the value of DB_FILE_MULTIBLOCK_READ_COUNT ? Also, what is the elapsed time for the query if you re-run the query immediately ?
[the first run fetched everything in physical reads, the second run should still
find some or most blocks in the SGA, unless the DB_CACHE_SIZE or DB_BLOCK_BUFFERS
is very small].
Hemant
At 05:18 AM 12-02-03 -0800, you wrote:

Hermant, Sergey

The table has 13 columns, the PK is formed for the first 11.

There is no deletion nor update, just inserts in the table. I had truncated the tables sometimes testing the procedure that load the rows.

This is the result with an auto trace.

COUNT(*)

1466196

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=896 Card=1) 1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'DM_VENTAS' (Cost=896 Card=1466196)

Statistics

          0  recursive calls
          0  db block gets
      14677  consistent gets
      14644  physical reads
          0  redo size
        386  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

-----Original Message-----

Chitale

Sent: Tuesday, February 11, 2003 10:24 PM

To: Multiple recipients of list ORACLE-L

You are doing Full-Table-Scans.

What's the average row length ? How many columns does the table have ?
How many "consistent gets" does the count(*) cause ? [ie, how many blocks does it actually have to read ?]
Are all these Physical Reads ? Is the DB_CACHE_SIZE large enough to hold most of the

blocks ? What is the query-run-time if you re-run the query immediately again ?

Hemant

At 08:19 AM 11-02-03 -0800, you wrote:

Hi list,

I issue a select count(*) from mytable and last 30 seconds.

The table has 1,466,196 records and were loaded with a batch process, so they are in a countinous space.

I consider that time exagerated.

The TBS is LMT with a Uniform size of 128 MB.

The block size is 8MB, version 9.2.0.1.0 in Windows 2000.

Where should I start looking ???

TIA Ramon E. Estevez

com.banilejas_at_codetel.net.do

809-565-3121

Hemant K Chitale

My web site page is : http://hkchital.tripod.com <http://hkchital.tripod.com/>

Please see the official ORACLE-L FAQ: http://www.orafaq.net <http://www.orafaq.net/> -- Author: Hemant K Chitale INET: hkchital_at_singnet.com.sg Fat City Network Services -- 858-538-5051 http://www.fatcity.com <http://www.fatcity.com/> San Diego, California
Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

Hemant K Chitale
My web site page is : http://hkchital.tripod.com <http://hkchital.tripod.com/>

Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: hkchital_at_singnet.com.sg Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Ramon E. Estevez
INET: com.banilejas_at_codetel.net.do

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Feb 12 2003 - 11:54:22 CST