Alexander Anokhin

Today I have fixed an issue related with serial direct path reads.

There is 11.2.0.2 database

SQL> select * from v$version where rownum = 1;
 
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
 
SQL> SELECT a.ksppinm  "Parameter",
  2         b.ksppstvl "Session Value",
  3         c.ksppstvl "Instance Value"
  4    FROM x$ksppi a, x$ksppcv b, x$ksppsv c
  5   WHERE a.indx = b.indx
  6     AND a.indx = c.indx
  7     AND a.ksppinm = '_serial_direct_read';
 
Parameter               Session Value           Instance Value
----------------------- ----------------------- -----------------------
_serial_direct_read     auto                    auto

There is a very small table, let’s call it dualcopy.

SQL> exec dbms_stats.gather_table_stats(user, 'dualcopy', estimate_percent => 100);
 
PL/SQL procedure successfully completed
 
SQL> select object_type,
  2         num_rows,
  3         blocks,
  4         empty_blocks
  5    from all_tab_statistics
  6   where table_name = 'DUALCOPY';
 
OBJECT_TYPE    NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------ ---------- ---------- ------------
TABLE                 1          1            0

Let’s try to do select from the table with enabled 10046 event:

SQL> alter session set events '10046 trace name context forever, level 8';
 
Session altered

SQL> select * from dualcopy;
 
DUMMY
-----
X

Below is an excerpt from the trace file

PARSING IN CURSOR #18446744071497556472 len=24 dep=0 uid=2500 oct=3 lid=2500 tim=3988723224325 hv=250290826 ad='3e911e388' sqlid='g32a9w87fq8na'
select * from dualcopy
END OF STMT
PARSE #18446744071497556472:c=20000,e=26430,p=0,cr=27,cu=0,mis=1,r=0,dep=0,og=2,plh=769194902,tim=3988723224324
EXEC #18446744071497556472:c=0,e=21,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,plh=769194902,tim=3988723224417
WAIT #18446744071497556472: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=3988723224541
WAIT #18446744071497556472: nam='SQL*Net message from client' ela= 66047 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=3988723290684
WAIT #18446744071497556472: nam='direct path read' ela= 7852 file number=705 first dba=23954 block cnt=1 obj#=2229858 tim=3988723301159
WAIT #18446744071497556472: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=2229858 tim=3988723301360
FETCH #18446744071497556472:c=10000,e=10521,p=1,cr=2,cu=0,mis=0,r=1,dep=0,og=2,plh=769194902,tim=3988723301437
STAT #18446744071497556472 id=1 cnt=1 pid=0 pos=1 obj=2229858 op='TABLE ACCESS FULL DUALCOPY (cr=2 pr=1 pw=0 time=10419 us cost=2 size=2 card=1)'

What?! serial direct path read for the table in one block?! What’s going on?

The table can be small, but if your application accesses it many times it can lead to significant amount of physical I/O.

Serial direct read mode (_serial_direct_read) is a property of a child cursor. Similar behavior is possible if the same query was parsed earlier in a session with ALWAYS or TRUE mode (_serial_direct_read=always or _serial_direct_read=true). If we pay attention on the parse call, it was a hard parsing (mis=1) and new child cursor. Thus, this is not the reason in our case.

The reason is that the table has been configured for using of KEEP pool, but database has not.

SQL> select table_name, buffer_pool
  2    from all_tables
  3   where table_name = 'DUALCOPY';
 
TABLE_NAME                     BUFFER_POOL
------------------------------ -----------
DUALCOPY                       KEEP

SQL> show parameter db_keep_cache_size
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_keep_cache_size                   big integer 0

This is the bug 12530276. Oracle 11.2.0.2 treats all ‘keep objects’ as large objects when ‘keep pool’ is not configured.

Allocation of KEEP pool fixes the problem.