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:chao_ping_at_vip.163.com]
> 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:chao_ping_at_163.com
> 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: chao_ping_at_vip.163.com
>
> 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: 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).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Sony kristanto
INET: Sony_at_polyfincanggih.com
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: 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 Wed Jan 22 2003 - 04:34:00 CST
