Steve,
In terms of the difference between 8.1.6.0 and
8.1.6.3, I can't remember the version vs bug cross-ref
but is it possible you've got timed stats or tracing
turned on ? It was around 8.1.6.x-ish that Oracle got
this sorted out wasn't it ?
Cheers
Connor
- Steve Adams <steve.adams_at_ixora.com.au> wrote: > Hi
Nuno (and list),
>
> Changing 'optimizer_index_caching' and
> 'optimizer_index_cost_adj' does seem to
> inhibit cursor sharing under 8i. You may want to try
> the following test under
> 8.0 and see if it is any different.
>
> SQL> create table t as select * from dual;
>
> Table created.
>
> SQL> analyze table t compute statistics;
>
> Table analyzed.
>
> SQL> select count(*) from t;
>
> COUNT(*)
> ----------
> 1
>
> SQL> select parse_calls, version_count from
> v$sqlarea where sql_text = 'select
> count(*) from t ';
>
> PARSE_CALLS VERSION_COUNT
> ----------- -------------
> 1 1
>
> SQL> show parameters optimizer_index
>
> NAME TYPE VALUE
> ------------------------------------ -------
> ------------------------------
> optimizer_index_caching integer 0
> optimizer_index_cost_adj integer 100
>
> SQL> alter session set optimizer_index_caching = 1;
>
> Session altered.
>
> SQL> select count(*) from t;
>
> COUNT(*)
> ----------
> 1
>
> SQL> select parse_calls, version_count from
> v$sqlarea where sql_text = 'select
> count(*) from t ';
>
> PARSE_CALLS VERSION_COUNT
> ----------- -------------
> 2 2
>
> SQL> alter session set optimizer_index_cost_adj =
> 99;
>
> Session altered.
>
> SQL> select count(*) from t;
>
> COUNT(*)
> ----------
> 1
>
> SQL> select parse_calls, version_count from
> v$sqlarea where sql_text = 'select
> count(*) from t ';
>
> PARSE_CALLS VERSION_COUNT
> ----------- -------------
> 3 3
>
> SQL>
>
> @ Regards,
> @ Steve Adams
> @ http://www.ixora.com.au/
> @ http://www.christianity.net.au/
>
>
> -----Original Message-----
> Sent: Thursday, 10 May 2001 0:51
> To: Multiple recipients of list ORACLE-L
>
>
> [snip]
> Now, start
> playing with OPTIMIZER_INDEX_CACHING and
> OPTIMIZER_INDEX_COST_ADJ.
>
> Incidentally, these can be set at session level but
> to get them picked
> up you need to do a FLUSH SHARED_POOL. Which kinda
> defeats the
> purpose of making them dynamic in the first place,
> Mr. ORACLE? Or am
> I missing something obvious? I can imagine someone
> doing this at the
> beginning of each batch job and flushing the shared
> pool each time!
> Jeez, some database coders don't have a clue about
> the real world, do
> they?...
>
> [snip]
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Steve Adams
> INET: steve.adams_at_ixora.com.au
>
> 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).
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)
"Some days you're the pigeon, some days you're the statue"
Do You Yahoo!?
Get your free @yahoo.co.uk address at
http://mail.yahoo.co.uk
or your free @yahoo.ie address at
http://mail.yahoo.ie
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: hamcdc_at_yahoo.co.uk
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 Thu May 10 2001 - 04:15:30 CDT
