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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
