Asynch direct path read : PART1 (Controlling auto I/O slots resize with _direct_io_slots to improve throughput)

Since version 11.2.0.2 ORACLE is able to adjust the number of I/O slots when using Asynch direct path read to improve bandwidth utilisation.Frits Hoogland explained this new feature in about-multiblock-reads .

Let’s begin by observing this I/O auto slots resizing in action :

• TEST DATABASE : 12.1.0.2 with ASM
• OS : OEL 6 KERNEL 3.8
• db_file_multiblock_read_count = 128

I begin by creating a large enough table “T64” (Copy of dba_objects)  to force direct path read :

SQL> select blocks from dba_segments where segment_name = ‘T64’;
BLOCKS
———-
1187840 = 9280 Mo

Then i turn on debug information for adaptive direct read and run a test query:

alter session set events ‘10365 trace name context forever, level 1’

SQL>  select count(*) from  t64;

  COUNT(*)
———-
  42323968

Elapsed: 00:00:24.82

Extract from trace file :

*** 2015-10-08 14:43:59.344
kcblsinc:Timing time 3003598, wait time 2198042, ratio 73 st 408912110 cur 411915710
kcblsinc: Timing curidx 4490 session idx 4490
kcblsinc: Timestamp 13494780 ms
kcblsinc: Current idx 4490
kcblsinc: Slave idx 4490
kcblsinc: Number slots 2
kcblsinc: Number of slots per session 2
kcblsinc: Previous throughput 290176 state 1
kcblsinc: adaptive direct read mode 1, adaptive direct write mode 0
kcblsinc: Adding extra slos 1

So the default number of slots is 2 and as Frits Hoogland explained :

”

• The database foreground measures direct path IO effectiveness
• It measures time, wait time and throughput
• The oracle process has the ability to add more asynchronous IO slots

”

So let’s check how the I/O slots are added :

[root@svltest stp]# more trace | egrep “\*|Adding”
*** 2015-10-08 14:43:53.136
*** 2015-10-08 14:43:56.340
*** 2015-10-08 14:43:59.344
kcblsinc: Adding extra slos 1
*** 2015-10-08 14:44:02.349
*** 2015-10-08 14:44:05.366
kcblsinc: Adding extra slos 1
*** 2015-10-08 14:44:08.377
*** 2015-10-08 14:44:11.380
kcblsinc: Adding extra slos 2
*** 2015-10-08 14:44:14.399
*** 2015-10-08 14:44:17.414

Observation :

The  oracle process will check different Metrics (Previous throughput , wait time ratio ) (generally every 3 Second ) and resize the number of slots (adding 1 slot at a time ,then 2 slots at a time,etc ) . The Maximum number of slots used by this session is 6 and the maximum throughput is 464789KB reached when using all the 6 slots after more than 15 second of execution.

So it may take some times to reach the maximum throughput.The question is how to control this ?

The hidden parameter “_direct_io_slots” define the minimum number of slots to use for async direct path operation (number of slots for direct path I/O ).

alter session set “_direct_io_slots”=6;

SQL> select count(*) from  t64;

  COUNT(*)
———-
  42323968

Elapsed: 00:00:20.23

*** 2015-10-08 15:15:57.922
kcbldrsini: Timestamp 15409730 ms
kcbldrsini: Current idx 5128
kcbldrsini: Initializing kcbldrps
kcbldrsini: Slave idx 5129
kcbldrsini: Number slots 6
kcbldrsini: Number of slots per session 6
kcblsinc:Timing time 25533519, wait time 1228904, ratio 4 st 2304962622 cur 2330496142
kcblsinc: Timing curidx 5128 session idx 5121

So now the minimum number of solts used is 6 and resized to 13 at the end of the query.

Let’s now test with the maximum number of slots supported which is 32.

alter session set “_direct_io_slots”=32;

SQL>  select count(*) from  t64;

  COUNT(*)
———-
  42323968

Elapsed: 00:00:18.67

The response time is now 18.67 second compared to the previous 24.82 second with the default _direct_io_slots parameter.The maximum throughput reached is 527560KB.

Let’s see what happen at the system level (using a slightly modified version of the scripts developed by Luca Canali)

So us we expected we see 32 outstanding asynchronous IO request “io_submit” of 1MB max size which is then split into 512KB ( block device maximum I/O size )

That’s it  (don’t play with this in production) 😀