_sqlexec_progression_cost

From: Hemant K Chitale <hkchital_at_singnet.com.sg>
Date: Fri, 02 Aug 2002 07:33:30 -0800
Message-ID: <F001.004A9DF7.20020802073330@fatcity.com>

_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.

Hemant K Chitale
Now using Eudora Email. Try it !

My home page is : http://hkchital.tripod.com

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Hemant K Chitale
  INET: hkchital_at_singnet.com.sg

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri Aug 02 2002 - 10:33:30 CDT