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

From: Turner, Adrian A SITI-ITPSIE <Adrian.A.Turner_at_is.shell.com>
Date: Wed, 12 Feb 2003 06:44:01 -0800
Message-ID: <F001.0054A939.20030212064401@fatcity.com>

Ramon,

Our Win2k boxes get between 1000-2000 gets a second off a SAN.

Are you using compressed folders to store your datafiles?

Whats Multi_block_read_count set to? Set MBRC to 32 (32x8K=256K). Make your extent sizes are divisable by 256K to reduce gets

Regards
Adrian

-----Original Message-----
Sent: 12 February 2003 14:09
To: Multiple recipients of list ORACLE-L

That's not so bad: 14644 physical reads in 30 seconds..that's about 500 I/O sec. Depending on your disk layout that's pretty optimal, I think.

Mario

-----Original Message-----
Sent: woensdag 12 februari 2003 14:19
To: Multiple recipients of list ORACLE-L

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 <mailto: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 -- 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: Turner, Adrian A SITI-ITPSIE
INET: Adrian.A.Turner_at_is.shell.com

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 - 08:44:01 CST