DIRECT READ ENHANCEMENTS IN 11G

 
In this post, I will discuss how direct read behaviour can be influenced in 11g by means of some undocumented parameters. But before coming to that, a few words about direct reads.
Whenever a server process is looking for some data blocks and does not find them in buffer cache, it will read them from disk into memory. Blocks can be read into :
- Buffer cache (buffered read) in which case
  . Latches are obtained in the buffer cache
  . Some earlier blocks in buffer cache may need to be aged out to make space for new blocks
 . those blocks can be accessed by other server processes also
- PGA of the server process (direct read) in which case
  . Blocks are read into PGA of the server process
  . those blocks cannot be accessed by other server processes
  . No latches need to be obtained and hence operation is faster
  . no blocks in buffer cache may need to be aged out to make space for new blocks
Hence, direct reads may be used when
  . the data blocks being read will not be needed by any other process
  . we don’t want a large no. of blocks in buffer cache to be aged out
   The mechanism for direct path reads has been modified in 11g as compared to 10g.
IN 10g
 – all the parallel queries resulted in direct reads irrespective of the size of the data as parallel queries are normally used in data warehouse where amount of data read is large.
 –  all the serial queries were executed using buffered reads assuming that amount of data read is not large.
Implications:
1. Parallel queries fetching even small amount of data bypassed the buffer cache so that if any other server process needed to access the same data,  he had to access the disk.
2. A serial query fetching massive amount of data could age out a lot of data from buffer cache .
In 11g we can modify this behaviour using some undocumented parameters.
1. we can set a threshold on the no. of blocks read (_small_table_threshold) so that even parallel queries fetching data less than the threshold will perform bufered I/O so that the fetched data in buffer cache can be accessed by other server processes also.
2. For serial queries we can enable direct I/O (_serial_direct_read) for all full table scans irrespective of the amount of the data read so that Full Table Scans (FTS’s)  read the data into PGA and data in buffer cache is not aged out.
                  PRACTICAL IMPLEMENTATION
OVERVIEW:
—————
SCENARIO – I
- Set
 _serial_direct_read = FALSE (Default) i.e. disable direct I/O for serial queries and
 _small_table_threshold = 10 (Default value  = 2% of the buffer cache)
so that
    . parallel queries reading less than 10 blocks of data will perform buffered I/O
    . parallel queries reading more than 10 blocks of data will perform direct I/O
    . serial queries always perform buffered I/O whether FTS or otherwise
———————-
SCENARIO – II
- Set _serial_direct_read = TRUE  i.e. enable direct I/O for serial queries
so that
    . serial FTS perform direct I/O
    . serial queries not performing FTS perform buffered I/O
——————————————————————————————
———————–  SCENARIO – I  ————————-
– Use following query to find initial values of
   . _serial_direct_read
   . _small_table_threshold
– Enter name of the parameter when prompted
SET linesize 235
col Parameter FOR a50
col SESSION FOR a28
col Instance FOR a55
col S FOR a1
col I FOR a1
col D FOR a1
col Description FOR a90
SELECT
  a.ksppinm  “Parameter”,
  decode(p.isses_modifiable,’FALSE’,NULL,NULL,NULL,b.ksppstvl) “Session”,
  c.ksppstvl “Instance”,
  decode(p.isses_modifiable,’FALSE’,’F’,’TRUE’,’T’) “S”,
  decode(p.issys_modifiable,’FALSE’,’F’,’TRUE’,’T’,’IMMEDIATE’,’I’,’DEFERRED’,’D’) “I”,
  decode(p.isdefault,’FALSE’,’F’,’TRUE’,’T’) “D”,
  a.ksppdesc “Description”
FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
WHERE a.indx = b.indx AND a.indx = c.indx
  AND p.name(+) = a.ksppinm
  AND UPPER(a.ksppinm) LIKE UPPER(‘%&1%’)
ORDER BY a.ksppinm;
Enter value for 1: small_table
old  12:   AND UPPER(a.ksppinm) LIKE UPPER(‘%&1%’)
new  12:   AND UPPER(a.ksppinm) LIKE UPPER(‘%small_table%’)
Parameter                     Session         Instance  S I D            Description
————-                                                      ———–  ———   ——-            ——-
_small_table_threshold                                    59               lower threshold level of table size for direct reads
SQL> /
old  12:   AND UPPER(a.ksppinm) LIKE UPPER(‘%&1%’)
new  12:   AND UPPER(a.ksppinm) LIKE UPPER(‘%_serial_direct%’)
Parameter                    Session         Instance                   S I D              Description
———————  ———-      ——————-   ————— ——————
_serial_direct_read                          FALSE                                            enable direct read in serial
-– SET PARAMETERS
  – SET _serial_direct_read = FALSE(DEFAULT) i.e. disable direct I/O for serial queries and
  – Set _small_table_threshold = 10
SQL>CONN / AS SYSDBA
         ALTER SYSTEM SET “_serial_direct_read” = FALSE SCOPE=SPFILE;
         ALTER SYSTEM SET “_small_table_threshold” = 10 SCOPE=SPFILE;
         STARTUP FORCE;
– Use following query to verify that
   . _serial_direct_read = FALSE
   . _small_table_threshold = 10
SET linesize 235
col Parameter FOR a50
col SESSION FOR a28
col Instance FOR a55
col S FOR a1
col I FOR a1
col D FOR a1
col Description FOR a90
SELECT
  a.ksppinm  “Parameter”,
  decode(p.isses_modifiable,’FALSE’,NULL,NULL,NULL,b.ksppstvl) “Session”,
  c.ksppstvl “Instance”,
  decode(p.isses_modifiable,’FALSE’,’F’,’TRUE’,’T’) “S”,
  decode(p.issys_modifiable,’FALSE’,’F’,’TRUE’,’T’,’IMMEDIATE’,’I’,’DEFERRED’,’D’) “I”,
  decode(p.isdefault,’FALSE’,’F’,’TRUE’,’T’) “D”,
  a.ksppdesc “Description”
FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
WHERE a.indx = b.indx AND a.indx = c.indx
  AND p.name(+) = a.ksppinm
  AND UPPER(a.ksppinm) LIKE UPPER(‘%&1%’)
ORDER BY a.ksppinm;
Enter value for 1: small_table
old  12:   AND UPPER(a.ksppinm) LIKE UPPER(‘%&1%’)
new  12:   AND UPPER(a.ksppinm) LIKE UPPER(‘%small_table%’)
Parameter                     Session         Instance  S I D          Description
————-                 ———–  ———   ——-       —————
_small_table_threshold     10               10          59             lower threshold level of table size for direct reads
SQL> /
old  12:   AND UPPER(a.ksppinm) LIKE UPPER(‘%&1%’)
new  12:   AND UPPER(a.ksppinm) LIKE UPPER(‘%_serial_direct%’)
Parameter                    Session         Instance                   S I D              Description
———————  ———-      ——————-   ————— ——————
_serial_direct_read         TRUE          FALSE                                            enable direct read in serial
– CREATE TEST TABLE TEST of large size
SQL>conn / as sysdba
    @?/sqlplus/admin/plustrce
    grant plustrace to hr;
    grant select on sh.sales to hr;
    grant execute on dbms_stats to hr;
    grant execute on dbms_monitor to hr;
    conn hr/hr
HR>drop table test purge;
   create table test as select * from sh.sales;
   exec dbms_stats.gather_table_stats(USER, ‘TEST’);
Check that no. of blocks in test   > 10
                               employees       < 10
HR> select table_name, blocks from user_tables
         where table_name in (‘TEST’, ‘EMPLOYEES’);
TABLE_NAME                         BLOCKS
—————————— ———-
EMPLOYEES                               5
TEST                                            4513
– SET CLIENT-ID FOR THE SESSION (to read the trace files)
HR>exec dbms_session.set_identifier (‘PARALLEL’);
– ENABLE TRACING FOR THE SESSION TO TRACE WAIT EVENTS AND DIRECT READS
HR>alter session set events ‘10046 trace name context forever, level 8′;
       alter session set events ‘10949 trace name context forever, level 1′;
– ISSUE PARALLEL QUERY ON BOTH THE TABLES TEST(>10 BLOCKS) AND EMPLOYEES (
   SET AUTOTRACE ON TO VERIFY THAT QUERY IS EXECUTED IN PARALLEL
HR>set autotrace traceonly
   select /*+ parallel(2) */ * from employees;
   select /*+ parallel(2) */ * from test;
–ISSUE SERIAL FTS  ON BOTH THE TABLES
        SERIAL non FTS (INDEXED READ) on EMPLOYEES
HR>select * from employees;
   select * from test;
   select * from employees where employee_id=100;
–CHECK THE WAIT EVENTS FOR ABOVE STATEMENTS IN THE TRACE FILE
$cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace
 trcsess output=parallel.out clientid=’PARALLEL’ *.trc
 tkprof parallel.out parallel.txt sys=no
 vi parallel.txt
 – PARALLEL  QUERY ON EMPLOYEES DOES NOT RESULT IN DIRECT READS AS BLOCKS < _SMALL_TABLE_THRESHOLD(10)
select /*+ parallel(2) */  * from  employees
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  —————————————-   Waited  ———-  ————
  PX Deq: Execution Msg                   1        0.01          0.01
  Disk file operations I/O                     2        0.00          0.00
  db file sequential read                       6        0.04          0.04
  os thread startup                               2        0.06          0.12
  PX Deq: Join ACK                          2        0.00          0.01
  PX Deq: Parse Reply                       2        0.01          0.01
  SQL*Net message to client              9        0.00          0.00
  PX Deq: Execute Reply                    8        0.04          0.05
  SQL*Net message from client           9        0.00          0.00
  PX Deq: Signal ACK RSG               1        0.00          0.00
  PX Deq: Signal ACK EXT               4        0.01          0.01
  PX qref latch                                    1        0.00          0.00
  PX Deq: Slave Session Stats             3        0.00          0.00
  enq: PS – contention                          1        0.00          0.00
***************************************************************
- PARALLEL QUERY ON test RESULTs IN DIRECT READS AS BLOCKS > _SMALL_TABLE_THRESHOLD(10)
select /*+ parallel(2) */  * from  test
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  —————————————-   Waited  ———-  ————
  PX Deq: Join ACK                          2         0.04          0.05
  PX Deq: Parse Reply                       2         0.00          0.01
  SQL*Net message to client              61258 0.00          0.21
  PX Deq: Execute Reply                    1021   0.01          1.36
  SQL*Net message from client          61258 0.78         27.58
  Disk file operations I/O                     1        0.00          0.00
  direct path read                                 1143  0.00          0.07
  PX Deq Credit: send blkd                 1104  0.92         38.77
  PX qref latch                                     1115 1.36         56.25
  PX Deq Credit: need buffer               15      0.15          0.49
  PX Deq: Execution Msg                     26     0.10          0.73
  asynch descriptor resize                      36     0.00          0.00
********************************************************************************
- ALL THE SERIAL QUERIES RESULT IN BUFFERED READS (WAIT EVENT ON DIRECT READS IS NOT THERE)
    AS (_SERIAL_DIRECT_READ=FALSE)
select *
from
 employees
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  —————————————-   Waited  ———-  ————
  SQL*Net message to client                9        0.00          0.00
  db file sequential read                         1        0.00          0.00
  db file scattered read                          1        0.00          0.00
  SQL*Net message from client            9        0.02          0.02
********************************************************************************
select * from  test
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  —————————————-   Waited  ———-  ————
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                   1        0.14          0.14
********************************************************************************
select * from  employees where employee_id=100
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 sequential read                         2        0.03          0.03
  SQL*Net message from client            2        0.00          0.00
********************************************************************************
—————————————————————————————————–
———————–  SCENARIO – II  ————————-
- SET _serial_direct_read = TRUE i.e. enable direct I/O for serial queries so that
    . serial FTS perform direct I/O
    . serial queries not performing FTS perform buffered I/O
— SET PARAMETERS
  – SET _serial_direct_read = true i.e. Enable direct I/O for serial queries
SQL>CONN / AS SYSDBA
    ALTER SYSTEM SET “_serial_direct_read” = TRUE SCOPE=SPFILE;
    STARTUP FORCE;
Use following query to verify that
   . _serial_direct_read = TRUE
   . _small_table_threshold = 10
SET linesize 235
col Parameter FOR a50
col SESSION FOR a28
col Instance FOR a55
col S FOR a1
col I FOR a1
col D FOR a1
col Description FOR a90
SELECT
  a.ksppinm  “Parameter”,
  decode(p.isses_modifiable,’FALSE’,NULL,NULL,NULL,b.ksppstvl) “Session”,
  c.ksppstvl “Instance”,
  decode(p.isses_modifiable,’FALSE’,’F’,’TRUE’,’T’) “S”,
  decode(p.issys_modifiable,’FALSE’,’F’,’TRUE’,’T’,’IMMEDIATE’,’I’,’DEFERRED’,’D’) “I”,
  decode(p.isdefault,’FALSE’,’F’,’TRUE’,’T’) “D”,
  a.ksppdesc “Description”
FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
WHERE a.indx = b.indx AND a.indx = c.indx
  AND p.name(+) = a.ksppinm
  AND UPPER(a.ksppinm) LIKE UPPER(‘%&1%’)
ORDER BY a.ksppinm;
– SET CLIENT-ID FOR THE SESSION
sql>CONN hr/hr
HR>exec dbms_session.set_identifier (‘SERIAL’);
-- ENABLE TRACING FOR THE SESSION TO TRACE WAIT EVENTS(10046) AND DIRECT READS(10949)
HR>alter session set events ‘10046 trace name context forever, level 8′;
       alter session set events ‘10949 trace name context forever, level 1′;
--ISSUE SERIAL FTS  ON BOTH THE TABLES
        SERIAL non FTS on EMPLOYEES
HR>select * from employees;
        select * from test;
        select * from employees where employee_id=100;
--CHECK THE WAIT EVENTS FOR ABOVE STATEMENTS IN THE TRACE FILE
$cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace
 trcsess output=parallel.out clientid=’SERIAL’ *.trc
 tkprof parallel.out parallel.txt sys=no
 vi parallel.txt
- FTS ON EMPLOYEES/TEST rESULTS IN DIRECT READS (WAIT EVENT ON DIRECT READS IS THERE)AS (_SERIAL_DIRECT_READ=truE)
select * from employees
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  —————————————-   Waited  ———-  ————
  SQL*Net message to client               9        0.00          0.00
  Disk file operations I/O                      1        0.00          0.00
  db file sequential read                         1        0.00          0.00
  direct path read                                  2        0.00          0.00
  SQL*Net message from client            9        0.00          0.00
********************************************************************************
select * from test;
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  —————————————-   Waited  ———-  ————
  SQL*Net message to client             61258   0.00          0.17
  Disk file operations I/O                   1           0.00          0.00
  asynch descriptor resize                  2           0.00          0.00
  direct path read                              1129     0.00          0.07
  SQL*Net message from client        61258   0.14         17.70
********************************************************************************
 - non FTS on EMPLOYES  DOES NOT RESULT IN DIRECT READS (WAIT EVENT ON DIRECT READS IS NOT THERE)
select * from  employees where employee_id=101
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 sequential read                       2        0.00          0.00
  SQL*Net message from client          2        0.00          0.00
********************************************************************************
—- CLEANUP —-
SYS>alter system reset _serial_direct_read  scope=spfile;
         alter system reset _small_table_threshold  scope=spfile;
         drop table hr.test purge;
References:
—————————————————————————————————–
Related links :
                                            ——————–

3 thoughts on “DIRECT READ ENHANCEMENTS IN 11G

  1. Hi Maam,

    Thanks for this post now i know how to trace parallel query session.

    Can you please help me in understanding

    1)which one is faster direct path read or serial read

    2)Sometime we see direct path read/write temp that means session is reading or writing to temp as sort area in PGA is small to accommodate

    So my question is suppose i have to sort 5mb of data but my pga sort area size if 2mb now it has to divide in set in 2-2-1 mb now how Oracle works here whether it read all 5mb data from datafile to tempfiles first and then take 2 mb of data sort it and send to client and then take other 2 mb.

    Thanks
    Jamsher

Your comments and suggestions are welcome!