Home > Oracle > Serial direct read for small tables in 11.2.0.2

Serial direct read for small tables in 11.2.0.2

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.

About these ads
  1. May 21, 2012 at 8:10 pm

    Great bit of information.
    Thanks.

  2. Dmitry Kremer
    June 1, 2012 at 8:44 am

    fixed on 11.2.0.3, I guess

    >select * from v$version where rownum = 1;

    BANNER
    ———————————————————————————————————
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

    1 row selected.

    > select unique buffer_pool from dba_tables;

    BUFFER_POOL
    ———————
    DEFAULT

    1 row selected.

    • June 1, 2012 at 9:12 am

      Hi Dmitry,
      yes, the bug 12530276 is fixed in
      11.2.0.3 (Server Patch Set)
      11.2.0.2 Bundle Patch 16 for Exadata Database

  3. October 19, 2012 at 2:59 pm

    Looks like something is still broken. I see direct path reads on 8 blocks table happening on INSERT VALUES regularly. It’s 11.2.0.3.2 and _serial_direct_reads=never or none doesn’t help, as well as session restart. Crazy.

    • October 19, 2012 at 3:23 pm

      Ouch. My bad. Didn’t notice no cache LOB is there. So hopefully nothing’s broken. Cool.

  4. Bari Khan
    January 8, 2013 at 9:18 am

    Hi All….
    I have an issue with a select query. It behaves differently in Oracle 11.2.0.3.0, it works fine in 11.2.0.2.0.

    SELECT SET_ID, SET_NAME FROM ADB WHERE ROWNUM < 10 ORDER BY SET_ID DESC.

    I have total of 100 records. This query works fine in 0.2 version returning top 10 records where as in 0.3 it returns bottom 10 records ordering it in descending order.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 82 other followers

%d bloggers like this: