Re: Query with Bind variables hangs but hard coded literals works fine

From: Neil Chandler <neil_chandler_at_hotmail.com>
Date: Tue, 26 Nov 2019 10:05:07 +0000
Message-ID: <DB7PR10MB2090B81376266CAF0EC5D59E85450_at_DB7PR10MB2090.EURPRD10.PROD.OUTLOOK.COM>

"One last thing, since this is an ERP package, I can’t change the code / insert hints / build new indexes (well I suppose I could do that but the next release upgrade would blow it away anyway). "

In addition to the good points made by Stefan and Jonathan, changing instance-wide parameters may cause instabilities or problems elsewhere.

If the plan can be fixed by using hints, it would be better to fix that specific plan using a SQL Patch and injecting hints directly into the SQL. It may "blow up" after an upgrade, but it's 1 SQL blowing up every year or two with a known fix (although your SQL Patch should upgrade with you) rather than potentially introducing an instance-wide issue. Keyhole surgery is better than open surgery.

regards

Neil Chandler.

---

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Steve Wales (AddOns) <steve.wales_at_addonsinc.com> Sent: 26 November 2019 01:23
To: oracle-l_at_freelists.org <oracle-l_at_freelists.org> Subject: Query with Bind variables hangs but hard coded literals works fine

Sorry for the length of this up front. Trying to get as much relevant info into the initial post as people might need to point me where I’m getting the problem

I have an Oracle 18c (18.8 if it matters) Standard Edition 2 database on Linux 7..

This email has been scanned for viruses and malware, and may have been automatically archived.

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

Received on Tue Nov 26 2019 - 11:05:07 CET