Async direct path read : PART2 (playing with db_file_multiblock_read_count and _direct_io_slots)

In the previous part of this series i showed how we can set the minimum number of I/O slots (_direct_io_slots) used by Async direct path read and how this can improve the throughput.In this second part i will play with different setting for the maximum I/O size “db_file_multiblock_read_count” and minimum number of outstanding I/O “_direct_io_slots” values and check there impact on throughput and CPU consumption.

For the test i have created a big enough table (to force a direct pah read you can read Frits Hoogland Direct path and buffered reads) on an ASM disk group using a Large AU to avoid splitting the I/O if it’s bigger than the AU size.

This example show the use of a diskgroup having an AU of 1MB with db_file_multiblock_read_count set bigger than 1MB.We can see that the I/O of 1433600B is split on 2 I/O respectively of 385024 bytes and 1MB.The I/O will be split again at I/O block layer of 512KBytes blocks each.

Sadly 😦 i was not able to have a good enough test result (our testing server is overused these days) so here is the script that you can use to get the measurement and draw a chart (it’s a slightly modified version of Christian Antognini script).

DECLARE
   L_TIME              NUMBER;
   L_STARTING_TIME     NUMBER;
   L_ENDING_TIME       NUMBER;
   L_BLOCKS            NUMBER;
   L_STARTING_BLOCKS   NUMBER;
   L_ENDING_BLOCKS     NUMBER;
   L_CPU               NUMBER;
   L_ENDING_CPU        NUMBER;
   L_STARTING_CPU      NUMBER;
   L_COUNT             NUMBER;
BEGIN
   DBMS_OUTPUT.put_line (‘slots,mbrc,throu,cpu’);

   — Test different value for _direct_io_slots maximum is 32
   FOR j IN 2 .. 15
   LOOP
      EXECUTE IMMEDIATE ‘alter session set “_direct_io_slots” = ‘ || j;

      FOR i IN 6 .. 12
      LOOP
         EXECUTE IMMEDIATE
               ‘ALTER SESSION SET db_file_multiblock_read_count = ‘ || POWER (2, i);

         SELECT SUM (DECODE (name, ‘physical reads’, VALUE)),
                SUM (DECODE (name, ‘CPU used by this session’, VALUE))
           INTO l_starting_blocks, l_starting_cpu
           FROM v$mystat ms JOIN v$statname USING (statistic#)
          WHERE name IN (‘physical reads’, ‘CPU used by this session’);

l_starting_time := DBMS_UTILITY.get_time ();

SELECT COUNT (*) INTO l_count FROM compte;

l_ending_time := DBMS_UTILITY.get_time ();

         SELECT SUM (DECODE (name, ‘physical reads’, VALUE)),
                SUM (DECODE (name, ‘CPU used by this session’, VALUE))
           INTO l_ending_blocks, l_ending_cpu
           FROM v$mystat ms JOIN v$statname USING (statistic#)
          WHERE name IN (‘physical reads’, ‘CPU used by this session’);

         l_time := ROUND ( (l_ending_time – l_starting_time) / 100, 1);
         l_blocks := l_ending_blocks – l_starting_blocks;
         l_cpu := l_ending_cpu – l_starting_cpu;

         DBMS_OUTPUT.put_line (
              j
            || ‘, ‘
            || POWER (2, i)
            || ‘ ,’
            || ROUND ( (l_blocks * 8 / 1024) / l_time)
            || ‘ ,’
            || TO_CHAR (l_cpu));
      END LOOP;
   END LOOP;
END;
/