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

From: Hemant K Chitale <hkchital_at_singnet.com.sg>
Date: Thu, 13 Feb 2003 07:15:03 -0800
Message-ID: <F001.0054BD87.20030213071503@fatcity.com>

Let's see ...

Your DB_CACHE_SIZE is 16MB == 2048 blocks
The table is approx 14,677 blocks
The record-size is something like 800bytes
Each multiblock read call will read 32-blocks

Your DB_CACHE will be filled in less than 64 read-calls and will have
less than 1/7th of the table.
Your server will certainly be very busy doing physical reads for a
Full-Table-Scan of this table alone.

Now, the only thing I can suggest is to increase your DB_CACHE_SIZE significantly.
16MB is too low for any activity in Oracle.

Hemant

At 09:54 AM 12-02-03 -0800, you wrote:

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-----
From: root@fatcity.com [mailto:root@fatcity.com] On Behalf Of Hemant K Chitale
Sent: Wednesday, February 12, 2003 8:59 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Count(*) last 30 seconds

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-----
From: root@fatcity.com [mailto:root@fatcity.com] On Behalf Of Hemant K Chitale
Sent: Tuesday, February 11, 2003 10:24 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: Count(*) last 30 seconds

You are doing Full-Table-Scans.

1. What's the average row length ? How many columns does the table have ?
2. How many "consistent gets" does the count(*) cause ? [ie, how many blocks does it actually have to read ?]
3. 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@codetel.net.do
809-565-3121

Hemant K Chitale
My web site page is : http://hkchital.tripod.com
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: hkchital@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@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

-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: hkchital@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@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).