In my last blog post on IN-MEMORY when investigating the amount of work done on behalf of IN-MEMORY population/[trickle] re-population i have done some observation concerning auto I/O slot resize.So here is more detail in a very short post(Sadly no time for a deeper investigation 😦 ).
In this test we will be comparing direct path read done on a big table : one by the in-memory slave process in the context of in-memory population vs direct path read issued by user action reading the table.
TEST CASE :
- Test database 12.1.0.2.6 (Linux)
- BIG_TABLE size 2GB
- Using event 10365 to turn on debug information for adaptive direct reads
- Using systemtap script to check for I/O slot usage and re-sizing
Reading a table using direct path read :
select count(*) from big_table2
Extract of 10365 trace event :
Extract of systemtap script:
Observation :
Adaptive direct path read is using only the default two I/O slot at a time for reading the hole table.There is no resize at this time.(for more info on how to control I/O slot resize check this )
INMEMORY population using direct path read (w0nn process):
Extract of 10365 trace event :
Extract of systemtap script:
Observation :
The output of the 10365 trace event seem wrong ! Although it report that the number of I/O slot used by the session is 2 the systemtap script show that more slot are used. Also this time more I/O slot are used at the beginning compared to the previous case witch used the default value of 2 I/O slot to begin with.
That’s it 😀
Hatem Mahmoud's blog 