Re: Strange behaviour

The phenomenon is due to "delayed block cleanout". There is an article about it somewhere on my website, and it is mentioned in the book.

For further details, the easiest option is to visit Steve Adams' website and use his search engine to find references to "block cleanout".

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
        UK            June / July
        Australia      July / August
http://www.jlcomp.demon.co.uk/seminar.html

Carlos Alberto wrote in message
<72954535.0206181248.c72c06e_at_posting.google.com>...


>Hi all,

>

>   I´m experiencing a strange behaviour with Oracle 8.0.6.0.0 : I have

>a 100 million records table, and based on a condition I loaded 47

>million of these records into another table, by INSERT .... SELECT

>.... command. The big table has 21GB, the new one has 12GB. The

>structure of them are the same, the only difference is the tablespace

>where each one resides. The strange is when I run the following script

>:

>

>set timing on;

>

>select count(1) from BIG_TABLE;

>

>--> 20 minutes

>

>select count(1) from SMALL_TABLE;

>

>--> 1 hour and 10 minutes!!!

>

>   How can this happen? When I run again :

>

>select count(1) from SMALL_TABLE;

>

>--> 6 minutes!!!

>

>   The result now is normal. This behaviour also happens when I run

>some other SQL, which access some index of the table. The hardware is

>Sun E10K with EMC Storage. Regarding EMC, the disks, controllers and

>cache are fine. Can anyone have some hint??

>

>Thanks in advanced,

>Carlos