Statistics-related problems in Ora92.

Hi,

can someone comment on the case described below?

We have a Oracle 8.1.7 database. We want to migrate it to an another (unx) machine + Oracle 92 database.

What we do is the following: We take a backup from the source database and restore it on a Oracle 8.1.7 database on the new machine. Then we upgrade the new database on the new machine from 8.1.7 to 9.2

When we turned on our application we experienced a lot of problems which seems to relate with statistics. For example the following update never finisched (on the old machine the update took less then 5 min.):

update tbl1
set field = ( select fld from tbl2 where fdl2 = tlb1.fld2 );

Then, when this querie ran in session1, I dropped and created new statistics on table tbl2 in an other session. When the 'compute statistics' finnished in session 2, the same moment the update querie finished in the ohter session. This is not a co-incedance, because we never have seen a update statement, running under the same conditions, finishing (and in the beginning we were very patience) and we tried it serveral times with the same result again and again as descibed above.

An other problem we encountered is that we could not analyze a partition from a table. analyze table tb partition ( par_name ) resulted in
'ORA-02149: Specified partition does not exist'. This occurred inspite the partition was mentioned in the data-dictionary. We also were able to select or update data from the partition. When we dropped and created new statistics, the problem was fixed.

Have someone experienced the same problems or does someone know the cause of these problems?

Thanx in advance.

Greetings,

Wybrand. Received on Mon Aug 25 2003 - 08:52:14 CDT