Re: In what circumstances might optimizer not choose the lowest cost?

From: Stefan Koehler <contact_at_soocs.de>
Date: Wed, 21 Jan 2015 10:31:37 +0100 (CET)
Message-ID: <848715195.4607.1421832697887.JavaMail.open-xchange_at_app04.ox.hosteurope.de>

Hi Kim,
that application behavior sounds strange indeed as no two PARSE calls are required with deferred optimization. We also need to differ the common used expression "parsing" for both activities "parsing" and "optimizing".

Parsing = Oracle checks if the SQL statement is a valid one (syntactic analysis). Afterwards it checks and figures out things like object types, columns in object types, constraints, triggers, indexes, privileges, etc. (semantic analysis). Both together is called parsing.

Optimizing = After the statement is parsed, the optimizer starts its work by gathering stats and doing its arithmetic.

As you can see what is commonly named as "parsing a SQL" is a two step approach in reality. So usually your statement is parsed, then the binds are assigned and then it is optimized.

Tom Kyte also described this deferred optimization on his website: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:104931400346587790#5203655900346507293

.. prepareStatement (this does the parse UP TO but not including optimization) 
.. bind 
.. bind 
.. bind 
.. execute (this will optimize if necessary and then run the sql) 
.. close 

So your app is broken in general (with OCI 10), if your root cause is based on non existence binds by optimization (no bind peeking), but as far as i understand you have a valid work around for your critical SQL :-))

Best Regards
Stefan Koehler

Freelance Oracle performance consultant and researcher Homepage: http://www.soocs.de
Twitter: _at_OracleSK  

> Kim Berg Hansen <kibeha_at_gmail.com> hat am 20. Januar 2015 um 19:30 geschrieben:
>
> Stefan,
>
> I am not 100% sure what actually happens.
>
> This old ERP system is called XAL, the kernel is written in C using OCI libraries (originally written with OCI version 7, now recompiled with OCI
> version 10, and any newer will not be produced.) The kernel is running on HP-UX.
>
> The ERP system has it's own legacy language, which the kernel transforms to SQL. Those cursors behave reasonably well and the kernel also reuses
> these cursors with new content in bind variables rather than close/open/close/open.
>
> But the cursors I need to use when I have to perform joins is a kind of "prepared statement". I build a string in this legacy language and call a
> "sqlexecute" function. I do not use bind variables explicitly, but a literal value in the string that is put inside special comments ( like
> /*XAL_STR(*/'literal'/*)*/ ) will be pulled out of the string by the kernel at runtime and placed in bind variables.
>
> When tracing, it appears that this "sqlexecute" function parses the statement TWICE before executing. I think it does a parse first (with empty
> bind variables) in order to "describe" columns and retrieve datatypes of the columns of the query. And then it re-parses before executing with
> contents in bind variables. So my theory is that the first parse makes the hard parse (defeating bind variable peeking) and the second is simply
> soft parsing (and therefore doesn't peek at the actual bind variable content.)
>
> What OCI calls it actually performs I cannot see and I cannot influence it. But bear in mind that the OCI calls used are calls that worked in
> version 7. It may have been recompiled (or re-linked) for OCI libraries version 10, but I think it highly unlikely that they actually changed the
> OCI calls used back then.
>
> But going too far out that line of exploration will get a bit far away from the original question :-)
>
> Regards
> Kim Berg Hansen
>
> http://dspsd.blogspot.com
> kibeha_at_gmail.com <mailto:kibeha_at_gmail.com>
> _at_kibeha

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

Received on Wed Jan 21 2015 - 10:31:37 CET