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: Ramon E. Estevez <com.banilejas_at_codetel.net.do>
Date: Thu, 13 Feb 2003 11:40:46 -0800
Message-ID: <F001.0054C8F7.20030213114046@fatcity.com> 





Tks Herman, will make that change.



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


Chitale


Sent: Thursday, February 13, 2003 10:15 AM
To: Multiple recipients of list ORACLE-L





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



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.





    
  
  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 



809-565-3121 

 



Hemant K Chitale 



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






Hemant K Chitale


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






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: 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 Thu Feb 13 2003 - 13:40:46 CST












Original text of this message













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