This may or may not do with caching. First time you execute
a query, accessed blocks from both index and table may
had to be read from disk. Subsequent queries found them
in cache avoiding physical i/o. Also, when you created your
indexes, index blocks could need to be cleaned out in the
same fashion (and indexes on big tables are also big). And
since you analyzed table on 20% sample, majority of the
blocks were not touched and thus were not cleaned out by
analyze. If your data is more or less evenly distributed,
estimation over 20% sample may be adequate, but I would
recommend full compute statistics anyway - you spend more
time computing stats, but then you spend less time querying
(or maybe not :).
--
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.0206190904.29f20231_at_posting.google.com...
> 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:53:11 CDT
