From Dan.Hubler@Metavante.com Mon, 26 Mar 2001 11:10:05 -0800 From: Dan.Hubler@Metavante.com Date: Mon, 26 Mar 2001 11:10:05 -0800 Subject: RE: Migration from 7.3.4 to 8.1.6 Message-ID: MIME-Version: 1.0 Content-Type: text/plain After engaging Oracle WWS concerning this issue we have learned that there have been a significant number of complaints regarding performance when upgrading to 8.1.6.x. It appears that re-running your ANALYZE is not enough. Dropping statistics and then running ANALYZE might be required. ---------------------- Forwarded by Dan Hubler/DS/MICORP on 03/26/2001 11:41 AM --------------------------- Dan Hubler 03/23/2001 01:46 PM To: "Ruiz, Mary A (CAP, CDI)" @ MICORPEX cc: "'Dan.Hubler@Metavante.com'" Optimizer mode was CHOOSE in both releases. Statistics are estimated at 10 percent. Used command-line migration tool. Thanks for replying. "Ruiz, Mary A (CAP, CDI)" on 03/23/2001 12:37:15 PM To: "'Dan.Hubler@Metavante.com'" cc: Dan: Thanks for your answer. What is your optimizer method in the 8.1.6 db vs. what it was in 7.3.4 ? (I think Oracle 8 defaults to Choose) Do you have statistics computed or estimated on the table ? What method did you use to migrate ? export / import, migration assistant , or command-line migration tool ? I would also look at indexes on the table, Maybe the query is using an index when it should not or vice-versa. Thanks in advance, Mary -----Original Message----- Sent: Friday, March 23, 2001 11:11 AM To: Multiple recipients of list ORACLE-L The thing that is killing us is a single query. This query does a group by. It also references some user-defined functions in the column-list of the SELECT statement. These user-defined functions are actually SELECTS from 2 other small tables. It is not behaving the same way it did in version 7.3.4. The table that the query runs against is pretty big (700,000 rows across 4 gig). The 2 tables referenced by the user-defined functions are very small (10-20 rows). "Ruiz, Mary A (CAP, CDI)" on 03/22/2001 04:18:30 PM To: "'Dan.Hubler@Metavante.com'" cc: "'ORACLE-L@fatcity.com'" Dan: What kinds of problems ? I am beginning a migration of a hybrid OLTP / OLAP database from 7.3.4 to 8.1.5 on Solaris 2.6 We are planning to take 6 weeks to "clean up" the database and fix the chained /migrated rows before we migrate (mainly on the big tables). We will spend a month testing on a migrated copy before migrating production. Our apps are custom Powerbuilder apps. I decided to go to 8.1.5 because our other databases are 8.1.5, and just live with the fact I cannot import a table from a multiple-table export. (that bugs me to no end) I am most interested in responses to this post. Maybe some truly compelling reasons to go to 8.1.6 , although I heard about hanging listener problems with that version which scared me. thanks in advance, Mary Ruiz / Atlanta -----Original Message----- Sent: Thursday, March 22, 2001 3:46 PM To: Multiple recipients of list ORACLE-L We just migrated the first of our environments from 7.3.4 to 8.1.6 ( Solaris 2.6 ). We have run into a couple of problems that only seem to appear when processing large tables or large amounts of data. This is kind of what we expected because we did not have the resources to do full-blown, production size testing. Anybody have any information on things that go wrong with this type of migration? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Dan.Hubler@Metavante.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Dan.Hubler@Metavante.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Dan.Hubler@Metavante.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).