Upgrade horror stories/victories

Upgrade horror stories/victories

Post by Andre Alle » Sun, 31 Dec 1899 09:00:00



I am looking for the opinions of anyone who has had the following
experiences

Upgrading from Oracle 7.3.4.2 --> 8i
Migrating from Oracle on Windows NT --> Some UNIX Flavor

My company has high transaction IVR system that is attached to an Oracle
RDBMS (7.3.4) which is running on a DEC AlphaServer 2100a with Windows NT
4.0 SP6.

7.3 --> 8i:  Ive heard that Exp/Imp is the safest way to go, but you've got
to have plenty of time, (which I do not have) and disk(Which is easy). The
MIG80/Oracle Migration Assistant utility is fast, but can be known to crash
and burn, which leaves you to restore the backups that you should have made
prior to the upgrade.

NT --> UNIX:  Is the migration really worth it?  The current database on NT
does not perform badly right now. Does going to a UNIX flavor (Solaris or
DEC UNIX preferably) offer significantly greater performance enhancements?
The reason we are evaluating this is because of Compaq/Digital's recent
announcement of no longer supporting the Windows flavors on the Alpha
platform.  With this news, I personally do not feel the Intel stuff is up to
*for an NT/Oracle combo. (but I could be wrong).

Any experienced opinions would be greatly appreciated.

Andre Allen
--
  .:'`':..:'`':.. Andre T. Allen ..:'`':..:'`':.   |  Disclaimer:  |

|  Systems Admin/Oracle 7 DBA/MS-SQL Server DBA  | | expressed are |
+------------------------------------------------+ | my own.       |

 
 
 

Upgrade horror stories/victories

Post by Jonathan Lewi » Sun, 31 Dec 1899 09:00:00


I haven't had any problems yet with migration ,
although the ones I have done have all been
Unix boxes, and I've always done the migration
by hand (using migprep and mig).

Total time required:
    Backup of database time + ca. 2 hours.

Fortunately I haven't had to migrate a system
with replication going.  It looks quite hairy
if you have to upgrade different nodes at
different times.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk


>I am looking for the opinions of anyone who has had the following
>experiences

>Upgrading from Oracle 7.3.4.2 --> 8i
>Migrating from Oracle on Windows NT --> Some UNIX Flavor

>My company has high transaction IVR system that is attached to an Oracle
>RDBMS (7.3.4) which is running on a DEC AlphaServer 2100a with Windows NT
>4.0 SP6.

>7.3 --> 8i:  Ive heard that Exp/Imp is the safest way to go, but you've got
>to have plenty of time, (which I do not have) and disk(Which is easy). The
>MIG80/Oracle Migration Assistant utility is fast, but can be known to crash
>and burn, which leaves you to restore the backups that you should have made
>prior to the upgrade.


 
 
 

Upgrade horror stories/victories

Post by r.gold » Sun, 31 Dec 1899 09:00:00


We've upgraded several 4g databases without difficulty.
In every case we
a) exported the databases
b) uninstalled O734, or re-installed NT
c) installed O815
d) manually created database/tablespaces/users/etc/import

This aproach I felt was not only safer, but had the added advantage that our
tables and indexes went back to single segments again.

Our only issue was with the O815i Optimizer. It makes different choices than
the O734 version, and so our 'optimized' select statements needed to be
tweaked again.

One of my favourite examples of this is
    select * from MyTable where MyPrinaryKey < 100;

In O734 this ran instantly using an index.
In O815 this causes a full table scan.

We started thinking about it, and decided that O734 was making the mistake.
The optimizer doesn't know if this column is all positive numbers or not.
It's possible that all of them are negative, and hence a full table scan
would be faster. Of course the optimizer could bother to check the root
blocks of the indexes to get the min and max values -- but as I recall the
rule is that the optimizer shouldn't have to read from disk to make up its
mind. (But who really knows how the Optimzer works?)

So we ran some command, (sorry I can't remember the name), and it created a
histogram on the index (one bucket) which obviously held the min and max
values for the data, and suddenly O815 started using the index again.

Regards
Robb

 
 
 

Upgrade horror stories/victories

Post by Jonathan Lewi » Sun, 31 Dec 1899 09:00:00


This sounds like you did an import which did NOT
do an ANALYZE of the table.

When a table is analyzed, each column
has a min and max value visible in user_tab_columns,
and also a pair of rows in user_tab_histograms (faked
in, even if you haven't generated a histogram).

Also, you can't generate a one-bucket histogram,
so I suspect the command you ran was simply:
    analyze table XXX estimate/compute statistics.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk


Quote:

>One of my favourite examples of this is
>    select * from MyTable where MyPrinaryKey < 100;

>In O734 this ran instantly using an index.
>In O815 this causes a full table scan.

>We started thinking about it, and decided that O734 was making the mistake.
>The optimizer doesn't know if this column is all positive numbers or not.
>It's possible that all of them are negative, and hence a full table scan
>would be faster.

>So we ran some command, (sorry I can't remember the name), and it created a
>histogram on the index (one bucket) which obviously held the min and max
>values for the data, and suddenly O815 started using the index again.

 
 
 

Upgrade horror stories/victories

Post by r.gold » Sun, 31 Dec 1899 09:00:00


Dear Johnathan Lewis

We DID analyze the tables.

We always import followed by a number of clean up things (analyze estimate
statistics 20000 rows, alter procedure .. compile, etc).

I remember asking them to create a one bucket histogram, and they did
whatever the minimum was. It might not have been 'one' bucket. And it was
the extra histogram command that made the difference, according to my staff.

My office is up to O815, but most of my clients are still at O734 so I don't
have the command memorized yet. Also, as of this week, we have a button on
our application that cleans up the database after an import.

I note your <and also a pair of rows in user_tab_histograms (faked
in, even if you haven't generated a histogram).> which is surprising,
because although I don't know if those rows are in user_tab_histograms, I do
know that just analyzing didn't help this query. There is a possibility that
our testing was done against O805. I think it was against O815, but I
wouldn't bet a paycheck on it.

When analyze estimate statistics didn't work, we did a full analyze (which
took almost a day). And that didn't work either.

We have to do analyze on our database with every import, because one of our
20 million row tables has two duplicate indexes -- one of which has about
400,000 rows per value, and the other has about 10 rows per value and
without an analyze the optimizer always picks the wrong one. But with an
anazlye the optimizer always gets it right.

Best regards
Robb


> This sounds like you did an import which did NOT
> do an ANALYZE of the table.

> When a table is analyzed, each column
> has a min and max value visible in user_tab_columns,
> and also a pair of rows in user_tab_histograms (faked
> in, even if you haven't generated a histogram).

> Also, you can't generate a one-bucket histogram,
> so I suspect the command you ran was simply:
>     analyze table XXX estimate/compute statistics.

> --

> Jonathan Lewis
> Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk



> >One of my favourite examples of this is
> >    select * from MyTable where MyPrinaryKey < 100;

> >In O734 this ran instantly using an index.
> >In O815 this causes a full table scan.

> >We started thinking about it, and decided that O734 was making the
mistake.
> >The optimizer doesn't know if this column is all positive numbers or not.
> >It's possible that all of them are negative, and hence a full table scan
> >would be faster.

> >So we ran some command, (sorry I can't remember the name), and it created
a
> >histogram on the index (one bucket) which obviously held the min and max
> >values for the data, and suddenly O815 started using the index again.

 
 
 

Upgrade horror stories/victories

Post by Jonathan Lewi » Sun, 31 Dec 1899 09:00:00


Could it be that the 'one bucket histogram'
was actually 'a histogram on one column' ?

The
    <pair of rows in user_tab_histograms (faked
    in, even if you haven't generated a histogram).>
is simply a different representation of the
low_value / high_value which is also displayed
in user_tab_columns.

You are quite right, of course, there are always
likely to be a few special cases where the
averaging effect of a simple analyze is never
good enough - even at 100% - and Oracle needs
a histogram to get a good result.  The default
bucket count is 75 buckets though, so over-casual
use of histograms can actually lead to a drop in
performance of low-volume queries due to excessive
parse costs.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk


Quote:>Dear Johnathan Lewis

>We DID analyze the tables.

>We always import followed by a number of clean up things (analyze estimate
>statistics 20000 rows, alter procedure .. compile, etc).

>I remember asking them to create a one bucket histogram, and they did
>whatever the minimum was. It might not have been 'one' bucket. And it was
>the extra histogram command that made the difference, according to my
staff.

>I note your <and also a pair of rows in user_tab_histograms (faked
>in, even if you haven't generated a histogram).> which is surprising,
>because although I don't know if those rows are in user_tab_histograms, I
do
>know that just analyzing didn't help this query. There is a possibility
that
>our testing was done against O805. I think it was against O815, but I
>wouldn't bet a paycheck on it.

>When analyze estimate statistics didn't work, we did a full analyze (which
>took almost a day). And that didn't work either.

 
 
 

Upgrade horror stories/victories

Post by Connor McDonal » Sun, 31 Dec 1899 09:00:00



> Could it be that the 'one bucket histogram'
> was actually 'a histogram on one column' ?

> The
>     <pair of rows in user_tab_histograms (faked
>     in, even if you haven't generated a histogram).>
> is simply a different representation of the
> low_value / high_value which is also displayed
> in user_tab_columns.

> You are quite right, of course, there are always
> likely to be a few special cases where the
> averaging effect of a simple analyze is never
> good enough - even at 100% - and Oracle needs
> a histogram to get a good result.  The default
> bucket count is 75 buckets though, so over-casual
> use of histograms can actually lead to a drop in
> performance of low-volume queries due to excessive
> parse costs.

> --

> Jonathan Lewis
> Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk



> >Dear Johnathan Lewis

> >We DID analyze the tables.

> >We always import followed by a number of clean up things (analyze estimate
> >statistics 20000 rows, alter procedure .. compile, etc).

> >I remember asking them to create a one bucket histogram, and they did
> >whatever the minimum was. It might not have been 'one' bucket. And it was
> >the extra histogram command that made the difference, according to my
> staff.

> >I note your <and also a pair of rows in user_tab_histograms (faked
> >in, even if you haven't generated a histogram).> which is surprising,
> >because although I don't know if those rows are in user_tab_histograms, I
> do
> >know that just analyzing didn't help this query. There is a possibility
> that
> >our testing was done against O805. I think it was against O815, but I
> >wouldn't bet a paycheck on it.

> >When analyze estimate statistics didn't work, we did a full analyze (which
> >took almost a day). And that didn't work either.

I recall a nice script on Oracle metalink which predicted (reasonably)
the usefulness of a histogram by

- adding the histogram
- checking the distribution to see if it skewed
- removing the histogram if not.

The author I think was "... Lin"

Cheers
Connor
--
===========================================
Connor McDonald
"These views mine, no-one elses etc etc"

"Some days you're the pigeon, and some days you're the statue."