Check your setting parameter for db_block_gets and compare between oracle9.2 and oracle817. Is it equivalent setting parameter ?
> -----Original Message----- > From: chao_ping [SMTP:[EMAIL PROTECTED]] > Sent: Wednesday, January 22, 2003 1:54 PM > To: Multiple recipients of list ORACLE-L > Subject: same sql: different db block gets in different oracle > version > > Hi, dba friends: > > Look at the following test result, the first one is from oracle9.2, > and the second is from the oracle 817. The table is same, and as you see, > the execution path is the same. > But there is difference in statistics: db_block_gets, in oracle92, > it is 0, and in oracle817, it is not. > And i noticed that in oracle8i, all sql that does only query with > execution path full scan of table/index,there will always be > db_block_gets, while in 9i, select won't make db_block_gets. > As tom said, db_block_gets is increased when data is accessed for > update, how does this query generate this statistics? > > > ORA92> select count(*) from abc; > > COUNT(*) > ---------- > 10000 > > Execution Plan > ---------------------------------------------------------- > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1) > 1 0 SORT (AGGREGATE) > 2 1 TABLE ACCESS (FULL) OF 'ABC' (Cost=6 Card=10000) > > Statistics > ---------------------------------------------------------- > 0 recursive calls > 0 db block gets > 27 consistent gets > 0 physical reads > 0 redo size > 379 bytes sent via SQL*Net to client > 503 bytes received via SQL*Net from client > 2 SQL*Net roundtrips to/from client > 0 sorts (memory) > 0 sorts (disk) > 1 rows processed > > ORA817> select count(*) from abc; > > COUNT(*) > ---------- > 10000 > > Elapsed: 00:00:00.26 > > Execution Plan > ---------------------------------------------------------- > 0 SELECT STATEMENT Optimizer=CHOOSE > 1 0 SORT (AGGREGATE) > 2 1 TABLE ACCESS (FULL) OF 'ABC' > > Statistics > ---------------------------------------------------------- > 0 recursive calls > 12 db block gets > 28 consistent gets > 24 physical reads > 0 redo size > 367 bytes sent via SQL*Net to client > 425 bytes received via SQL*Net from client > 2 SQL*Net roundtrips to/from client > 0 sorts (memory) > 0 sorts (disk) > 1 rows processed > > > Regards > zhu chao > msn:[EMAIL PROTECTED] > www.happyit.net > www.cnoug.org(China Oracle User Group) > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: chao_ping > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > 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.net -- Author: Sony kristanto INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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).
