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 - 03:54:50 CDT
