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

From: Steve Wales (AddOns) <"Steve>
Date: Tue, 26 Nov 2019 16:38:33 +0000
Message-ID: <CY4PR20MB141478EC0C9749C2B7A1524BF1450_at_CY4PR20MB1414.namprd20.prod.outlook.com>



Good morning everyone and thanks for your assorted insights overnight while I slept

For anyone bombarded with my out of office message, oops, sorry, turned that off. So much for Thanksgiving week PTO 😊

To the points raised so far:

Stefan:

Jonathan:

  • Thank you, I will try that modified look at the execution plan as well
  • Cursor sharing is set to EXACT in this database.

Neil:

  • I have no particular objections to a custom fix if I have to. My management might, but if a way can be found to correct this by doing something like that, I’m all over it.

Andy S:

  • I am going to have to go over your answer in more detail later. Joining 100 to 170 doesn’t really cut down rows. For this DB 100 has about 82000 rows, 170 has about 260,000. 100 PK is just stock code. I row per unique part in the company global stock catalog. 170 is per business unit, with the PK being BU / stock code – each BU records which parts of the global catalog they are using. Another strike against this query since it isn’t using the BU in the join.

Chris:

  • I will come back and look at your message in more detail later, and will definitely file that whole message and attachments away for later reference in case we ever do go EE – but you can’t even run the Diagnostics and Tuning pack with Standard. Tried to buy just that pack years ago, but Oracle said “Sorry, EE only”

Andy K:

  • You could be the hero of the hour. My main concern at the moment is to get rid of the performance issue in Prod. The part search by colloquial function is a fairly core part of the warehousing aspect of the application and having the thing not working is causing some grief in the assorted warehouses. The vendor has modified this piece of code in a newer release of the application and an upgrade is scheduled for early next year, but there is no backfit available of that change in the current version. I set the optimizer_features_enable parameter (which was 18.1.0) back to 12.1.0.2 in Development and the query dropped back down from 50+ minutes to 5 seconds. It’s not 0.7 seconds but it will do to get this functionality restored!

Andy W:

  • The query you provided returned no rows. So that may not be the issue ?

OK so I think that’s everyone responded to. I can’t thank everyone enough for their efforts in responding.

This has been an ongoing issue for a while that has come and gone a few times (most recently on a 12.2 database and just this weekend to a different database that upgraded to 18c. I pointed out the problem in the query to the vendor a while ago that seems to have led to the change in their code. For now, if I can get around it with setting optimizer_features_enable back to 12.1.0.2, to fix this immediate issue, I may go down that path and then after the application upgrade next year, revert back to enabling 18.1.0 optimizer features and review again.

I have raised a case with Oracle Support on this issue as well, I’m going to mention the optimizer features bit and see if the support folks have any ideas.

Again, thanks to everyone for their time today.

Regards
Steve

Disclaimer

The information contained in this communication from the sender is confidential. It is intended solely for use by the recipient and others authorized to receive it. If you are not the recipient, you are hereby notified that any disclosure, copying, distribution or taking action in relation of the contents of this information is strictly prohibited and may be unlawful.

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 - 17:38:33 CET

Original text of this message