| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re[2]: Count(*) last 30 seconds
What could possibly happen is that the table experienced many
deletions followed by bulk inserts, and the value for high water mark
is way off the actual size of the data.
Once bulk insert via direct path load occurs, Oracle does not reuse blocks in free lists, but allocates new ones instead, advancing the value of high water mark (HWM).
After deletion the value of HWM doesn't get reset, and new batch process that loads data advances it again. After several times of inserting/deleting records, the number of blocks Oracle has to read while doing full scan is much more than necessary.
It's easy to find out if that's the case. Try
create table b as select * from a;
where "a" is the "slow" table.
After that try
select count(*) from b;
and see how long does it take. If it happened quickly, the table "a" needs to be reorganized.
Tuesday, February 11, 2003, 7:23:41 PM, you wrote:
HKC> Hemant HKC> At 08:19 AM 11-02-03 -0800, you wrote: HKC> Hi HKC> list, HKC> HKC> I issue a select count(*) from mytable and HKC> last 30 seconds. HKC> HKC> The table has 1,466,196 records and were HKC> loaded with a batch process, so they are in a countinous HKC> space. HKC> HKC> I consider that time exagerated. HKC> HKC> The TBS is LMT with a Uniform size of 128 HKC> MB. HKC> HKC> The block size is 8MB, version 9.2.0.1.0 in HKC> Windows 2000. HKC> HKC> Where should I start looking ??? HKC> HKC> TIA HKC> HKC> Ramon E. Estevez HKC> com.banilejas_at_codetel.net.do HKC> 809-565-3121 HKC>
HKC> Hemant K Chitale
HKC> My web site page is : http://hkchital.tripod.com
HKC> --
HKC> Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Best regards,
Sergey mailto:sergten_at_cox.net
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Sergey Ten
INET: sergten_at_cox.net
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 Tue Feb 11 2003 - 22:28:41 CST
 |
 |