Re: Strange behaviour

Carlos Alberto wrote:
>
> Hi,
>
> Just to say that the statistics were generated, therefore the
> execution plans for the big table and the small table are identical.
> Jonathan, if this phenomenon is occuring, is there a workaround?
>
> Thanks in advanced,
> Carlos
>
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<1024433883.20070.0.nnrp-12.9e984b29_at_news.demon.co.uk>...
> > 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

If direct load insert is an option then this may assist. Otherwise, delayed block cleanout is something you 'want' since the alternative would be to clean every block as its modified...very slow.

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."