(The stats are a little off because I ran it through a
few times, but version count remains at 1)
mcdonac_at_EDEV> create table t as select * from dual;
Table created.
mcdonac_at_EDEV> analyze table t compute statistics;
Table analyzed.
mcdonac_at_EDEV> select count(*) from t;
COUNT(*)
1
mcdonac_at_EDEV> select parse_calls, version_count
2 from v$sqlarea
3 where sql_text= 'select count(*) from t';
no rows selected
mcdonac_at_EDEV> show parameters optimizer_index
NAME TYPE VALUE
------------------------------------ -------
------------------------------
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
mcdonac_at_EDEV> alter session set
optimizer_index_caching = 1;
Session altered.
mcdonac_at_EDEV> select count(*) from t;
COUNT(*)
1
mcdonac_at_EDEV> select parse_calls, version_count
2 from v$sqlarea
3 where sql_text= 'select count(*) from t ';
PARSE_CALLS VERSION_COUNT
----------- -------------
11 1
mcdonac_at_EDEV> alter session set
optimizer_index_cost_adj = 99;
Session altered.
mcdonac_at_EDEV> select count(*) from t;
COUNT(*)
1
mcdonac_at_EDEV> select parse_calls, version_count
2 from v$sqlarea
3 where sql_text= 'select count(*) from t ';
PARSE_CALLS VERSION_COUNT
----------- -------------
12 1
mcdonac_at_EDEV> select * from v$version;
BANNER
Oracle8i Enterprise Edition Release 8.1.6.3.0 -
Production
PL/SQL Release 8.1.6.3.0 - Production
CORE 8.1.6.0.0 Production
TNS for Solaris: Version 8.1.6.3.0 - Production
NLSRTL Version 3.4.0.0.0 - Production
mcdonac_at_EDEV> show parameters shared_pool
NAME TYPE VALUE
------------------------------------ -------
------------------------------
shared_pool_reserved_size string 2621440
shared_pool_size string 52428800
- 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).
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 - 03:59:07 CDT
