_sqlexec_progression

From: Jerry C <usidba_at_yahoo.com>
Date: Tue, 29 May 2001 11:01:40 -0700
Message-ID: <F001.0031241F.20010529105132@fatcity.com>

Hi there,

Anybody have experience setting this parameter? It references a bug
(1210242) that apparently causes certain cursors not to be shared if timed
statistics is set to TRUE.

Thanks!

Jerry
- from "Main issues affecting the Shared Pool on Oracle 7 , Oracle8 and Oracle8i ", Doc ID 62143.1 ===

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

Doc ID: Note:68955.1
Note
Type: REFERENCE
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 08-MAR-1999
Last Revision Date: 16-MAR-2001

Parameter: _SQLEXEC_PROGRESSION_COST

  Please note this is a hidden Oracle parameter and begins with an underscore
  character. The information on this parameter is made available to customers
  here as it can be used to avoid some cursor sharing bugs in Oracle8i.   If you set this parameter then include a comment to explain why it is   being set.

Versions: This parameter can be set in Oracle 8.1.5 to 8.1.7 inclusive
Description

SQL execution progression monitoring cost threshold.

This parameter controls the optimizer cost used as the threshold for cutting off progression monitoring. Progression monitoring involves extra function calls and row sources, so we don't want to cause this overhead on short operations; however, since the cost can be inaccurate there is a sliding threshold. The value defaults to 1000, but if set to zero, progression monitoring will be turned off. If nonzero, then any statement with a costs less than the value will not be monitored.

The progression monitoring output can be seen in <View:V$SESSION_LONGOPS>.

Support Notes

Leaving this parameter at its default value can cause SQL statements not to be shared as detailed in [BUG:1210242]. Setting this parameter to 0 in the init.ora file can help avoid such problems.

Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jerry C
  INET: usidba_at_yahoo.com

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 Tue May 29 2001 - 13:01:40 CDT