Re: why db block gets = 0?

From: Niy <niy38_at_hotmail.com>
Date: 9 Oct 2004 11:13:06 -0700
Message-ID: <55dd405a.0410091013.22373f11@posting.google.com>

rolympia_at_hotmail.com (Romeo Olympia) wrote in message news:<42fc55dc.0410082000.74219238_at_posting.google.com>...
> Seems like the usual confusion over DB BLOCK GETS and CONSISTENT GETS.
> Lots of AskTom resources for that, one of which is:
>
> http://asktom.oracle.com/pls/ask/f?p=4950:8:3827657808746015643::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:865586003021,
>
> Cheers!
>

Thanks for pointing, Tom has lots of good discussion, but I'm still confused, look at this:

SQL> create table t (id number);

Table created.

SQL> insert into t values(1);

1 row created.

Execution Plan

---

0 INSERT STATEMENT Optimizer=CHOOSE

Statistics

---

2 recursive calls
7 db block gets ---------- why need 7 db block gets? 2 consistent gets
0 physical reads
592 redo size
621 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client

2 sorts (memory) ------------------------why need sort here?

0 sorts (disk)
1 rows processed

SQL> select * from t;

ID

---

1

Execution Plan

---

0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'T' Statistics

---

0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
373 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

In another window:
SQL> alter system flush shared_pool;

System altered.

back to original window:

SQL> / ID

---

1

Execution Plan

---

0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'T' Statistics

---

181 recursive calls
0 db block gets
23 consistent gets ----------- why there are so many consistent gets after flushed shared_pool?
0 physical reads
0 redo size
373 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client

2 sorts (memory)   ------------------- sort again ?

0 sorts (disk)
1 rows processed Received on Sat Oct 09 2004 - 13:13:06 CDT