Multiple Blocksizes...in 9i DB

[grjohnson]

You know when you're trying to do something to increase performance on a db and you test something and the result that's returned is not what you're expect? Great, that's what the following is... care to have a gander?

I've a DB with two different Block Sizes. 8K and 32K.

db_cache_size = 512M
db_32K_cache_size = 128M

I put a large table into the 32K, considering its a reporting table and therefore minimal block contention. I create a copy in the 8K tablespace, analyzed and computed statistics. and then ran the following a couple of times... this is the results after two runs...

SQL> select count(*) from 32K_TABLE;

COUNT(*)
----------
181298

Elapsed: 00:00:01.88

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=267 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF '32K_TABLE' (Cost=2 67 Card=181298

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

SQL> select count(*) from 8K_TABLE;
COUNT(*)

181298

Elapsed: 00:00:00.44

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=464 Card=1) 1 0 SORT AGGREGATE) 2 1 TABLE ACCESS (FULL) OF '8K_TABLE' (Cost= 464 Card=181298)

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

a). First, the speed of the queries surprised me, cause I expect the 32K to return quicker, considering, there'd be less blocks to read. result) Wrong.

b). Full tablescans need to perform (DB physical reads with each FTS)
result). Clearly wrong as the secound count(*) performed 7 physical reads, compared to the first's 1922 Physical reads. And why isn't the DB performing a physical read on a FTS... a tables buffer for a FTS should be read into LRU end of the buffer cache. Oracle treat a physical read as any read on a block not in the Buffercache.

What they??? Anyone elee like to comment?

[slimdave]

a). First, the speed of the queries surprised me, cause I expect the 32K to return quicker, considering, there'd be less blocks to read. result) Wrong.

The physical reads are obviously a factor -- improved efficiency on a 32K block size probably comes from improvements in PIO performance, not the LIO's

b). Full tablescans need to perform (DB physical reads with each FTS)
result). Clearly wrong as the secound count(*) performed 7 physical reads, compared to the first's 1922 Physical reads. And why isn't the DB performing a physical read on a FTS... a tables buffer for a FTS should be read into LRU end of the buffer cache. Oracle treat a physical read as any read on a block not in the Buffercache.

FTS will use a physical IO if it needs to -- it's true that FTS puts the blocks to the LRU end of the cache (although you can modify this behaviour with the CACHE hint) but that's not relevant here.

Long story short, your performance comparison is not valid. The PIO's on the 32K space are skewing the result. Try the comparison again after a db shutdown-startup to see a comparison of read speeds between the two.

[grjohnson]

Hmm, intersting....

Created the objects, analyzed. Shutdown startup. with DB caches as follows

db_cache_size = 128M
db_32K_cache_size = 128M

Ran both queries twice a, b, a, b and the results of the second run were as follows. (I also, stopped and restarted and ran) b, a, b, a and found no difference.

SQL> select count(*) from TEST_8K;

COUNT(*)
----------
322910

Elapsed: 00:00:00.37

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2580 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'TEST_8K' (Cost=2580 Card=322910)

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

SQL> select count(*) from TEST_32K;

COUNT(*)
----------
322910

Elapsed: 00:00:00.21

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1548 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'TEST_32K' (Cost=1548
Card=322910)

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

select o.object_name, count(*) from v$bh b, dba_objects o
where b.objd = o.object_id
group by o.object_name

I found 11641 buffer "blocks/granuales" refering to object TEST_8K in the BUFFER_CACHE

What's the other cache view for the 32K buffer cache? Had a look at

select * from dba_objects where object_name like 'V$_%'
order by 2;

Didn't recognise anything though...

[slimdave]

You've still got that disparity on the PIO's -- what were the results on the first execution? The point here is to compare disk reads times between the two TS sizes.

[grjohnson]

SQL> select count(*) from TEST_16K;

COUNT(*)
----------
322910

Elapsed: 00:00:00.69

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1548 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'TEST_16K' (Cost=1548
Card=322910)
Statistics
----------------------------------------------------------
266 recursive calls
0 db block gets
4087 consistent gets
4055 physical reads
72 redo size
493 bytes sent via SQL*Net to client
655 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select count(*) from TEST_8K;

COUNT(*)
----------
322910

Elapsed: 00:00:00.76

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2580 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF TEST_8K' (Cost
=2580 Card=322910)
Statistics
----------------------------------------------------------
266 recursive calls
0 db block gets
16869 consistent gets
16832 physical reads
0 redo size
493 bytes sent via SQL*Net to client
655 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed