RE: Count(*) last 30 seconds

[Turner, Adrian A SITI-ITPSIE]

Title: Message

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-----
From: Broodbakker, Mario [mailto:[EMAIL PROTECTED]]
Sent: 12 February 2003 14:09
To: Multiple recipients of list ORACLE-L
Subject: RE: Count(*) last 30 seconds

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-----
From: Ramon E. Estevez [mailto:[EMAIL PROTECTED]]
Sent: woensdag 12 februari 2003 14:19
To: Multiple recipients of list ORACLE-L
Subject: RE: Count(*) last 30 seconds

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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] 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
[EMAIL PROTECTED]
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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).