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).
Received on Wed Feb 12 2003 - 07:58:58 CST
