direct path read - On 11g: table bigger than KEEP pool

I posted "What happened, when segment bigger than KEEP pool?". In this case, I hoped to see "direct path read" waited event on 11g. How? KEEP BUFFER POOL Does Not Work for Large Objects on 11g [ID 1081553.1]

While doing full table scan, Buffer Cache KEEP pool is not being used to read the blocks of a (KEEP) table whose size is >10% of DB_KEEP_CACHE_SIZE.

I tested on 11gR2.

SQL> show parameter compatible

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.2.0.0.0

SQL> select bytes/1024/1024 MB from dba_segments where segment_name='TB_DATA' and owner='DEMO';

MB
----------
52

SQL> alter system set db_keep_cache_size=100M;

System altered.

Case 1: Table size 50M and db_keep_cache_size 100M (table smaller than KEEP pool)

SQL> show parameter db_keep_cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_keep_cache_size big integer 100M

SQL> alter system flush buffer_cache;

System altered.

SQL> alter table demo.tb_data storage(buffer_pool keep);

Table altered.

SQL> select count(*) from demo.tb_data;

COUNT(*)
----------
453516

Execution Plan
----------------------------------------------------------
Plan hash value: 143091466
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1790 (1)| 00:00:22 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TB_DATA | 418K| 1790 (1)| 00:00:22 |
----------------------------------------------------------------------
Statistics
----------------------------------------------------------
48 recursive calls
0 db block gets
6574 consistent gets
6257 physical reads
404 redo size
528 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',
4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',
7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN') subcache,
bh.object_name,bh.blocks
from x$kcbwds ds,x$kcbwbpd pd,(select /*+ use_hash(x) */ set_ds,
o.name object_name,count(*) BLOCKS
from obj$ o, x$bh x where o.dataobj# = x.obj
and x.state !=0 and o.owner# !=0
group by set_ds,o.name) bh
where ds.set_id >= pd.bp_lo_sid
and ds.set_id <= pd.bp_hi_sid
and pd.bp_size != 0
and ds.addr=bh.set_ds
/

SUBCACHE OBJECT_NAME BLOCKS
------------ ------------------------------ ----------
KEEP TB_DATA 3240
KEEP TB_DATA 3235

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

Session altered.

SQL> select count(*) from demo.tb_data;

COUNT(*)
----------
453516

Execution Plan
----------------------------------------------------------
Plan hash value: 143091466
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1790 (1)| 00:00:22 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TB_DATA | 418K| 1790 (1)| 00:00:22 |
----------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6483 consistent gets
0 physical reads
0 redo size
528 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

In TKPROF report:

select count(*)
from
demo.tb_data

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 0.08 0.08 0 6488 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.08 0.08 0 6488 0 1

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 3 0.00 0.00
SQL*Net message from client 3 29.44 30.93
Disk file operations I/O 1 0.00 0.00

OK ... not seen "direct path read" waited event.

Case 2: Table bigger than KEEP pool !!! what should I see in 11gR2?

SQL> alter system set db_keep_cache_size=8M;

System altered.

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from demo.tb_data;

COUNT(*)
----------
453516

Execution Plan
----------------------------------------------------------
Plan hash value: 143091466
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1790 (1)| 00:00:22 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TB_DATA | 418K| 1790 (1)| 00:00:22 |
----------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6483 consistent gets
6480 physical reads
0 redo size
528 bytes sent via SQL*Net to client
524 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 decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',
4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',
7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN') subcache,
bh.object_name,bh.blocks
from x$kcbwds ds,x$kcbwbpd pd,(select /*+ use_hash(x) */ set_ds,
o.name object_name,count(*) BLOCKS
from obj$ o, x$bh x where o.dataobj# = x.obj
and x.state !=0 and o.owner# !=0
group by set_ds,o.name) bh
where ds.set_id >= pd.bp_lo_sid
and ds.set_id <= pd.bp_hi_sid
and pd.bp_size != 0
and ds.addr=bh.set_ds
/

SUBCACHE OBJECT_NAME BLOCKS
------------ ------------------------------ ----------
KEEP TB_DATA 116
KEEP TB_DATA 114

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

Session altered.

SQL> select count(*) from demo.tb_data;

COUNT(*)
----------
453516

Execution Plan
----------------------------------------------------------
Plan hash value: 143091466
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1790 (1)| 00:00:22 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TB_DATA | 418K| 1790 (1)| 00:00:22 |
----------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6478 consistent gets
6474 physical reads
0 redo size
528 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

In TKPROF report:

select count(*)
from
demo.tb_data

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 0.12 2.11 6474 6483 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.13 2.11 6474 6483 0 1

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 3 0.00 0.00
SQL*Net message from client 3 21.15 24.18
Disk file operations I/O 3 0.07 0.07
direct path read 109 0.03 1.91

When I queried with table size bigger than KEEP pool. I saw "direct path read ", but about "KEEP BUFFER POOL Does Not Work for Large Objects on 11g [ID 1081553.1] " not sure... I will test more!!! I used 11.2 (no test on 11.1 yet).

Thank You Joel Garry!!!
Buffer Cache KEEP pool is not being used to read the blocks of a (KEEP) table whose size is >10% of DB_KEEP_CACHE_SIZE.
Bugs fixed (8897574) in the 11.2.0.2 Patch Set

On 11.2.0.1 ... I found 'direct path read', when object size bigger than 30% of KEEP pool:
WAIT #1: nam='kfk: async disk IO' ela= 7 count=1 intr=0 timeout=4294967295 obj#=73424 tim=1314684133052298
WAIT #1: nam='direct path read' ela= 28618 file number=4 first dba=6786 block cnt=89 obj#=73424 tim=131468413308099

Related Post:
What happened, when segment bigger than KEEP pool?