hi,

i have two test scenarios where i'm trying to understand to actual concept of db block gets and consistent gets.

SQL> set autotrace on

SQL>select * from scott.emp;

i get 14 rows then

Execution Plan ----------------------------------------------------------    0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=14 Bytes=51           8)

   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes           =518)

Statistics ----------------------------------------------------------        1528  recursive calls           0  db block gets         289  consistent gets          44  physical reads           0  redo size        1585  bytes sent via SQL*Net to client         522  bytes received via SQL*Net from client           4  SQL*Net roundtrips to/from client          31  sorts (memory)           0  sorts (disk)          14  rows processed

on the above i do not see db block gets why?

when i run the same statement again this is what i get

Execution Plan ----------------------------------------------------------    0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=14 Bytes=51           8)

   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes           =518)

 

 

Statistics ----------------------------------------------------------           0  recursive calls           0  db block gets          10  consistent gets           0  physical reads           0  redo size        1585  bytes sent via SQL*Net to client         522  bytes received via SQL*Net from client           4  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)          14  rows processed no db lock gets again why?

basically i want to understand the difference between db block gets and consisten gets

also i run the same statement from different session and i get below result

Execution Plan ----------------------------------------------------------    0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=14 Bytes=51           8)

   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes           =518)

Statistics ----------------------------------------------------------           0  recursive calls           0  db block gets           0  consistent gets           0  physical reads           0  redo size           0  bytes sent via SQL*Net to client           0  bytes received via SQL*Net from client           0  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)          14  rows processed

here i do not see anything, why?

please help me understand the what's happening here?

any help would be greatly appreciated

thanks in advance

 



Software/Hardware used:
Oracle10g R1