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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: Khedr, Waleed
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
