Re: Statistics-related problems in Ora92.

On 25 Aug 2003 06:52:14 -0700, meer051_at_zonnet.nl (wybrand) wrote:

>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.

I guess if you would have been using the dbms_stats package (which you should always do, as ANALYZE is for backwards compatibility only) you would have exported the stats to a stats table and imported them in the live dictionary after upgrade.
For the rest the only clue you provide in your post is: 'It doesn't work', so it is impossible to provide any specific answer.

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Mon Aug 25 2003 - 12:12:12 CDT