Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Count(*) last 30 seconds

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.

  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_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: 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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US