Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Strange behaviour

Re: Strange behaviour

From: Daniel Morgan <dmorgan_at_exesolutions.com>
Date: Tue, 18 Jun 2002 20:53:53 GMT
Message-ID: <3D0F9DD1.C07A8879@exesolutions.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US