Steve,
I got the same results as Waleed on 8.1.6.0.0 on
Win2K, 8.1.6.3 32-bit on Solaris, and 8.1.6.1 64-bit
on Solaris.
Might you be running into bug 1210242 (fixed in
8.1.6.2) or one of the similar bugs in 8i where
cursors aren't shared when timed_statistics are
enabled?
The workaround for many of them is to set
_SQL_EXEC_PROGRESSION_COST=0.
- Anita
- Steve Adams <steve.adams_at_ixora.com.au> wrote:
> Hi Waleed,
>
> I ran the test below under 8.1.6.0 on NT using
> SQL*Plus and I would have
> expected the same results under 8.1.6.3 on Solaris.
> Do you possibly have a small shared pool with very
> quick reuse?
>
> @ Regards,
> @ Steve Adams
> @ http://www.ixora.com.au/
> @ http://www.christianity.net.au/
>
>
> -----Original Message-----
> Sent: Thursday, 10 May 2001 10:01
> To: Multiple recipients of list ORACLE-L
>
>
>
> Hi Steve,
>
> I tested it on Oracle 8.1.6.3 (Solaris 2.6).
>
> Parse_calls gets incremented every time the sql gets
> executed but the
> version_count continues to be 1.
>
> Regards,
>
> Waleed
> -----Original Message-----
> Sent: Wednesday, May 09, 2001 6:15 PM
> To: Multiple recipients of list ORACLE-L
>
>
> 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).
Do You Yahoo!?
Yahoo! Auctions - buy the things you want at great prices
http://auctions.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: A. Bardeen
INET: abardeen1_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 Thu May 10 2001 - 06:07:27 CDT
