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

Hi Steve, Waleed

on 8.1.6.0 Solaris I've got the same results as Waleed has. Shared pool is huge, about 250M and user activity is low at the moment (users haven't woken up yet :)

Is it NT-specific?

Regards,
Ed

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Shevtsov, Eduard
  INET: EShevtsov_at_flagship.ru

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