From hamcdc@yahoo.co.uk Thu, 10 May 2001 01:59:07 -0700 From: =?iso-8859-1?q?Connor=20McDonald?= Date: Thu, 10 May 2001 01:59:07 -0700 Subject: RE: Optimizer and block size changes = trouble... Message-ID: MIME-Version: 1.0 Content-Type: text/plain (The stats are a little off because I ran it through a few times, but version count remains at 1) mcdonac@EDEV> create table t as select * from dual; Table created. mcdonac@EDEV> analyze table t compute statistics; Table analyzed. mcdonac@EDEV> select count(*) from t; COUNT(*) ---------- 1 mcdonac@EDEV> select parse_calls, version_count 2 from v$sqlarea 3 where sql_text= 'select count(*) from t'; no rows selected mcdonac@EDEV> show parameters optimizer_index NAME TYPE VALUE ------------------------------------ ------- ------------------------------ optimizer_index_caching integer 0 optimizer_index_cost_adj integer 100 mcdonac@EDEV> alter session set optimizer_index_caching = 1; Session altered. mcdonac@EDEV> select count(*) from t; COUNT(*) ---------- 1 mcdonac@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@EDEV> alter session set optimizer_index_cost_adj = 99; Session altered. mcdonac@EDEV> select count(*) from t; COUNT(*) ---------- 1 mcdonac@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@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@EDEV> show parameters shared_pool NAME TYPE VALUE ------------------------------------ ------- ------------------------------ shared_pool_reserved_size string 2621440 shared_pool_size string 52428800 --- Steve Adams 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@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). ===== 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@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@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).