From steve.adams@ixora.com.au Wed, 09 May 2001 14:34:52 -0700 From: "Steve Adams" Date: Wed, 09 May 2001 14:34:52 -0700 Subject: RE: Optimizer and block size changes = trouble... Message-ID: MIME-Version: 1.0 Content-Type: text/plain 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@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@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).