RE: Optimizer and block size changes = trouble...

From: Steve Adams <steve.adams_at_ixora.com.au>
Date: Wed, 09 May 2001 14:34:52 -0700
Message-ID: <F001.002FE802.20010509141524@fatcity.com>

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

Received on Wed May 09 2001 - 16:34:52 CDT