_sqlexec_progression_cost in 8.1.7

From: Hemant K Chitale <hkchital_nospam_at_singnet_nospam.com.sg>
Date: Fri, 02 Aug 2002 22:34:06 +0800
Message-ID: <3D4A985E.B1083DE1@singnet_nospam.com.sg>

_sqlexec_progression_cost=0 is a required setting for Oracle Applications 11i,
running the Cost-Based optimizer.

Would it make sense to use it with the Rule-based optimizer (e.g. in Oracle Applications 11.0) ?
I don't think that the parameter applies only with the Cost-based optimizer but
also with the Rule-based optimizer. The performance impact of the default
value (1000) for this parameter in 8i relates to using timed_statistics=TRUE
and SQL_TRACE=TRUE. I do have timed_statistics set and the developers still have a number of programs where sql_trace=TRUE (or when an analyst runs a report from the Applications, he enables tracing on the report).

So, should I set it to 0, in Oracle applications 11.0, Rule-Based on 8.1.7.2 ?

Per note 62143.1 on "Understanding and Tuning the Shared Pool in Oracle7, Oracle8 and Oracle8i" :

"SQLEXEC_PROGRESSION_COST parameter (8.1.5 onwards) This is a hidden parameter which was introduced in Oracle 8.1.5. The parameter is included here as the
default setting has caused some problems with SQL sharability. Setting this parameter to 0 can avoid these
issues which result in multiple versions statements in the shared pool. Eg: Add the following to the init.ora file

        # _SQLEXEC_PROGRESSION_COST is set to ZERO to avoid SQL sharing issues

        # See Note:62143.1 for details
        _sqlexec_progression_cost=0

Note that a side effect of setting this to '0' is that the V$SESSION_LONGOPS view is not populated by long running queries.
See <Note:68955.1> for more details of this parameter. "

Also, note 68955.1 recommends setting it to 0 to resolve "some cursor sharing bugs
in Oracle8i".

I don't query V$SESSION_LONGOPS so it doesn't matter to me if this view doesn't get
updated. Received on Fri Aug 02 2002 - 09:34:06 CDT