Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Strange behaviour
Carlos Alberto wrote:
> 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
Did you run DBMS_STATS on small table before doing this or do you expect the optmizer to just guess at what is in the table?
Daniel Morgan Received on Tue Jun 18 2002 - 15:53:53 CDT