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

From: Stefan Koehler <contact_at_soocs.de>
Date: Tue, 20 Jan 2015 18:55:25 +0100 (CET)
Message-ID: <294217487.205399.1421776525831.open-xchange_at_app04.ox.hosteurope.de>

Hi Kim,
just out of curiosity. What do you mean with "this old old ERP application environment parses SQL before populating bind variables"? Do you differ between parsing and optimization or do you merge and name both activities as parsing in general?

The cursor life cycle is usually something like that: open cursor - parse cursor - (define output variable) - (bind input variables) - execute cursor - etc.

As you can see the binds are populated after "parsing" - e.g. demo examples with DBMS_SQL: http://docs.oracle.com/database/121/ARPLS/d_sql.htm#ARPLS68199

.. or does your app use such an old client that does not support deferred optimization?

Thanks.

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 15:20 geschrieben:
>
> Unpeeked bind might be something to look into. Unfortunately this old old ERP application environment parses SQL before populating bind variables,
> so I haven't much help from bind variable peeking. Although that doc is an old bug that should be fixed in version 10.2 (and we're running
> 11.2.0.3.0), I can't rule out that this may be involved.
>
> It does appear that the unpeeked bind variable is involved here. I did a trial with bind variables in SQL*Plus that I've set before the parsing/call
> of the queries - that worked nicely no matter if my check constraint is in place or not.
> As unfortunately I cannot help that my production environment isn't really using bind variable peeking

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

Received on Tue Jan 20 2015 - 18:55:25 CET