Re: Strange behaviour

Hi,

   Just to post some more information about my problem : after the INSERT ... SELECT ... was committed, the "select count(1)" was executed, then the indexes were created, and then this new table was analyzed with a sample of 20 percent. The "delayed block cleanout" mechanism explained me why the first "select count(1) from SMALL_TABLE" was slow. But why some other SQL´s, which accesses some of the indexes of the table, were also slow when they were first executed?? At this time, the data blocks headers were already "cleaned out".

Thanks in advanced,
Carlos

"Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> wrote in message news:<aepgsk$ifj$1_at_babylon.agtel.net>...
> This is due to 'deferred block cleanout'. If your transaction affected
> more than 10% of buffer cache, you are guaranteed to hit it. This
> is a trick Oracle uses to make commit as short as possible. If the
> transaction was large enough, Oracle simply does not clean out
> every block it affected upon commit. Instead, this is done on first
> query touching these blocks. Your select count(*) touched every
> block of affected table, causing full block cleanout to happen, and
> this involves generating a lot of redo and flushing blocks to disk.
> If you had analyzed the table after inserting into it and before doing
> any queries against it, analyze would've performed block cleanout
> for you and at the same time would've gathered statistics CBO needs
> to query your table most effectively. So, as a rule of thumb (if there
> can be any rules), analyze your tables right after you perform massive
> updates or inserts into them. This will both avoid the phenomenon
> you witnessed and keep statistics current.
>
> --
> Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
> Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
> All opinions are mine and do not necessarily go in line with those of my employer.
>
>
> "Carlos Alberto" <calberto2312_at_hotmail.com> wrote in message
> news: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
Received on Wed Jun 19 2002 - 12:04:51 CDT