RE: Seeking advice on potential 10g upgrade
- From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
- To: "Niall Litchfield" <niall.litchfield@xxxxxxxxx>
- Date: Tue, 29 Aug 2006 10:17:52 -0700
Good article, thanks Niall. I found the last line interesting:
"(d) assume that you're going to have to revisit and retest any hinted
SQL on the next upgrade."
My advice would be that you're going to have to revisit and retest *all*
SQL (hinted or not) on an upgrade anyway. Anyone that goes through an
Oracle upgrade without a full application functionality test (at least
of all business-critical processes) prior to go-live is asking for
serious trouble. I still would not agree that hints alone make SQL more
prone to trouble during an upgrade. For example, I recently completed
two upgrades of Baan on Oracle - one from Oracle 7.3 to Oracle 9.2 and
another from Oracle 8.0.6 to 10.2.0.2. In Baan - EVERY single statement
is hinted with the FIRST_ROWS hint, and most statements also have INDEX
and USE_NL hints as well. Out of thousands of statements, we only had
trouble with about 5 - and none of those problems were related to the
hints - they were due to problems with bind variable peeking.
That said, I'm not trying to advocate over-hinting - I fully agree with
Jonathan Lewis' advice from the referenced article:
"Hints can be very useful to solve urgent problems - but my general
advice is (a) don't use them as a first resort, (b) check whether the
real problem is in the statistics (c) if you really need to hint your
SQL, you probably need an average of at least one hint per table to lock
in the execution path you expect "
Regards,
Brandon
> -----Original Message-----
> From: Niall Litchfield [mailto:niall.litchfield@xxxxxxxxx]
> Sent: Tuesday, August 29, 2006 1:53 AM
>
> I'd take a look at
> http://www.jlcomp.demon.co.uk/hinted_sql.html for some
> further discussion of whether the system is likely to be more
> stable or run into issues in the presence of many hinted sql
> statements.
Privileged/Confidential Information may be contained in this message or
attachments hereto. Please advise immediately if you or your employer do not
consent to Internet email for messages of this kind. Opinions, conclusions and
other information in this message that do not relate to the official business
of this company shall be understood as neither given nor endorsed by it.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
