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

From: Stefan Koehler <contact_at_soocs.de>
Date: Tue, 26 Nov 2019 10:26:03 +0100 (CET)
Message-ID: <283772456.91217.1574760363869_at_ox.hosteurope.de>


Hello Steve,
OK, there are several important things based on your provided information.

> (Estimated plans generated just by doing good old “explain plan for”)

This is not a valid approach in your case due to two main reasons: 1) Bind variables are not peeked with "EXPLAIN PLAN FOR" and all variables are treated as VARCHAR2 2) Runtime execution plan changes (e.g. Adaptive Plans) are not considered

... so it is very important that you grab the real execution plan from the SQL execution.

> If I take the query, replace the bind variables from the query with string values and run it through SQLPLUS, it runs in about 0.7 seconds and returns the expected values. If I instead leave the bind variables in the query, and in SQLPLUS define variables and then assign values via exec :v1 := ‘SOMEVALUE’, then it sits and spins 57 MINUTES before returning the exact same result set.

Seems like that there is some difference in real execution plan as well (not sure if it is the same that you see with "EXPLAIN PLAN FOR") - however let's assume that it is the same then you can see different kind of transformations going on (e.g. complex view merging with).

> This was not a problem with 12.1.0.2 SE2. I’m hoping that there’s an init parameter or something that was new in the 12.2 engine from 12.1 that might be causing the problem.

So if we assume that you did not gather new stats or anything else and that the change is related to some (new) CBO transformation - then it would be pretty easy to figure it out with Pathfinder (https://github.com/mauropagano/pathfinder/) - even without any deep knowledge about the CBO. Just run your SQL statement in/with pathfinder and crosscheck the output afterwards.

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website: http://www.soocs.de
Twitter: _at_OracleSK

> "Steve Wales (AddOns)" <steve.wales_at_addonsinc.com> hat am 26. November 2019 um 02:23 geschrieben:
>
>
> 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..
>
>
> There’s a query from the ERP system that’s hanging the online screen and getting http timeouts because it’s not completing in a timely fashion.
>
>
> I pulled the text of the query from v$sqltext and the bind variables from v$sql_bind_capture.
>
>
> The query is searching a parts catalog for colloquial names for parts.
>
>
> If I take the query, replace the bind variables from the query with string values and run it through SQLPLUS, it runs in about 0.7 seconds and returns the expected values.
>
>
> If I instead leave the bind variables in the query, and in SQLPLUS define variables and then assign values via exec :v1 := ‘SOMEVALUE’, then it sits and spins 57 MINUTES before returning the exact same result set.
>
>
> The execution plans change slightly but significantly as well.
>
>
> The tables:
>
>
> MSF100 is a stock catalog table.  Contains all stock items that the business catalogs across all properties
>
>
> MSF170 is a table that busts up the global catalog among assorted business units
>
>
> MSF120 contains the colloquial names.  If I search on “ACTUATOR” it can return me all the stock codes that have “ACTUATOR” as a colloquial name.
>
>
> I assume rownum <= 20 is specified because that’s how many rows fit on a screen at a time.
>
>
> select * from( select * from MSF100 CATALOG where (CATALOG.stock_code in (select MSF170Rec.stock_code from MSF170 MSF170Rec, MSF100 MSF100Rec
> where (MSF100Rec.stock_code = MSF170Rec.stock_code))) and ( exists (select 1 from MSF120 MSF120Rec where (MSF120Rec.colloq_code = CATALOG.stock_code
> and MSF120Rec.colloq_name like '%ACTUATOR%'  and MSF120Rec.colloq_type = 'S' )) or  exists (select 1 from MSF120 MSF120Rec
> where (MSF120Rec.colloq_code = CATALOG.template_id and MSF120Rec.colloq_name like '%ACTUATOR%'  and MSF120Rec.colloq_type = 'I' )))
> and (CATALOG.stock_status <> 'X' ) order by CATALOG.stock_code ) where ROWNUM <= 20;
>
> When using the static values (and I apologize in advance for what proportional fonts do the formatting below)
>
> (Estimated plans generated just by doing good old “explain plan for”)
>
> This was not a problem with 12.1.0.2 SE2.
>
>
> 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).   Any solution really has to be about a config / parameter change (but if an index would work, I suppose I’d be willing to try it as a work around for the time being).   
>
>
> Thanks in advance for any pointers.  I’m hoping that there’s an init parameter or something that was new in the 12.2 engine from 12.1 that might be causing the problem.
>
>
> Steve
>
>
> HTML Editor - Full Version
>
>
> 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 - 10:26:03 CET

Original text of this message