Oracle Block size Vs OS block size

[grjohnson]

Originally posted by slimdave
Hmmm.

Confused.

Retested... on a quieter DB

SQL> Alter session set events '10046 trace name context forever, level 12';

Session altered.

SQL> select count(*) from test
where snapshot_id_c = 'test';

Elapsed: 00:00:31.40

WAIT #1: nam='db file scattered read' ela= 50510 p1=9 p2=379403 p3=128
WAIT #1: nam='db file scattered read' ela= 50602 p1=9 p2=379531 p3=128
WAIT #1: nam='db file scattered read' ela= 37283 p1=9 p2=379659 p3=128
WAIT #1: nam='db file scattered read' ela= 37624 p1=9 p2=379787 p3=126
WAIT #1: nam='db file scattered read' ela= 56332 p1=10 p2=418315 p3=128
WAIT #1: nam='db file scattered read' ela= 30759 p1=10 p2=418443 p3=128
WAIT #1: nam='db file scattered read' ela= 37364 p1=10 p2=418571 p3=128
WAIT #1: nam='db file scattered read' ela= 30100 p1=10 p2=418699 p3=126
WAIT #1: nam='db file scattered read' ela= 68661 p1=9 p2=379915 p3=128
WAIT #1: nam='db file scattered read' ela= 41933 p1=9 p2=380043 p3=128
WAIT #1: nam='db file scattered read' ela= 42122 p1=9 p2=380171 p3=128
WAIT #1: nam='db file scattered read' ela= 39265 p1=9 p2=380299 p3=126
WAIT #1: nam='db file scattered read' ela= 56861 p1=10 p2=418827 p3=128
WAIT #1: nam='db file scattered read' ela= 31159 p1=10 p2=418955 p3=128
WAIT #1: nam='db file scattered read' ela= 36711 p1=10 p2=419083 p3=128
WAIT #1: nam='db file scattered read' ela= 32579 p1=10 p2=419211 p3=126
.
.
.

STAT #1 id=2 cnt=0 pid=1 pos=1 obj=30464 op='TABLE ACCESS FULL OBJ#(30464) (cr=82547 r=82527 w=0 time=30658401)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 5.34 30.65 82527 82547 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 5.34 30.65 82527 82547 0 1

Misses in library cache during parse: 1
Optimizer goal: FIRST_ROWS
Parsing user id: 58 (FRED)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=82547 r=82527 w=0 time=30658418 us)
0 TABLE ACCESS FULL OBJ#(30464) (cr=82547 r=82527 w=0 time=30658401 us)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: FIRST_ROWS
1 SORT (AGGREGATE)
0 TABLE ACCESS (FULL) OF 'TEST'


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file scattered read 650 0.07 28.21
SQL*Net message from client 2 52.87 52.87

SQL> exit

SQL> Alter session set events '10046 trace name context forever, level 12';

Session altered.


SQL> select count(*) from test
where snapshot_id_c = 'test';

Elapsed: 00:00:26.72

WAIT #1: nam='db file scattered read' ela= 34521 p1=12 p2=2949 p3=32
WAIT #1: nam='db file scattered read' ela= 31547 p1=12 p2=2981 p3=32
WAIT #1: nam='db file scattered read' ela= 22113 p1=12 p2=3013 p3=32
WAIT #1: nam='db file scattered read' ela= 23875 p1=12 p2=3045 p3=32
WAIT #1: nam='db file scattered read' ela= 52675 p1=11 p2=2566 p3=32
WAIT #1: nam='db file scattered read' ela= 38911 p1=11 p2=2598 p3=32
WAIT #1: nam='db file scattered read' ela= 35585 p1=11 p2=2630 p3=32
WAIT #1: nam='db file scattered read' ela= 36678 p1=11 p2=2662 p3=31
WAIT #1: nam='db file scattered read' ela= 37421 p1=11 p2=2693 p3=32
WAIT #1: nam='db file scattered read' ela= 36737 p1=11 p2=2725 p3=32
WAIT #1: nam='db file scattered read' ela= 40435 p1=11 p2=2757 p3=32
WAIT #1: nam='db file scattered read' ela= 33564 p1=11 p2=2789 p3=32
WAIT #1: nam='db file scattered read' ela= 47267 p1=11 p2=2822 p3=32
WAIT #1: nam='db file scattered read' ela= 39201 p1=11 p2=2854 p3=32
WAIT #1: nam='db file scattered read' ela= 34944 p1=11 p2=2886 p3=32
.
.
.
STAT #1 id=2 cnt=0 pid=1 pos=1 obj=30464 op='TABLE ACCESS FULL OBJ#(30464) (cr=20097 r=20078 w=0 time=26080640)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 4.05 26.08 20078 20097 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 4.05 26.08 20078 20097 0 1

Misses in library cache during parse: 1
Optimizer goal: FIRST_ROWS
Parsing user id: 58 (FRED)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=20097 r=20078 w=0 time=26080652 us)
0 TABLE ACCESS FULL OBJ#(30464) (cr=20097 r=20078 w=0 time=26080640 us)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: FIRST_ROWS
1 SORT (AGGREGATE)
0 TABLE ACCESS (FULL) OF 'TEST


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file scattered read 631 0.06 24.22
SQL*Net message from client 2 47.37 47.37

32K block size performed slightly quicker. But DMBC still as expected.

Incidently, for default Buffer_cache we can look at v$bh table for objects in the cache, how can we see what's in the non default cache?