RE: Seeking advice on potential 10g upgrade

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_at_gmail.com]
> 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.

--
http://www.freelists.org/webpage/oracle-l