IM (in memory) partial scan.

I decided to check whether it possible to do IM scan partially.
(I’ve heard recently a contradictory information someone believe
that it’s either full IM scan of the overall segment or full “old” style buffer cache scan and somewhere I read about extent level alignment.

Here is details of my test case.

select name, display_value from v$paramete name like ‘inmemory_size’;

NAME DISPLAY_VALUE
inmemory_size 304M

select segment_name, populate_status, trunc(bytes_not_populated/1024/1024,2) bytes_not_populated,bytes/1024/1024 bytes,inmemory_size/1024/1024 inmemory_size from v$im_segments

My in-memory size is about 300MB.

SEGMENT_NAME POPULATE_STATUS BYTES_NOT_POPULATED BYTES INMEMORY_SIZE
IDXTEST COMPLETED 0 120 1,125
IDXTEST2 COMPLETED 0 12 10,125
IDXTEST3 COMPLETED 14,04 57 38,1875
IDXTEST4 COMPLETED 0 35 29,125

So I have IDXTEST3 table partially populated in-memory.
14.04 MB has not been populated in-memory.

I switched on IM tracing as:

ALTER SESSION SET TRACEFILE_IDENTIFIER=”IM_SCAN_PART”;
alter session set events ‘trace[RDBMS.IM.*]’;

and did the SELECT itself

SELECT /*+ dynamic_sampling(t 0) FULL(t) INMEMORY(t) */ count(filler) from IDXTEST3 t WHERE id >:id2

and here is stats from the execution above

7188 session logical reads
0 physical reads
0 IM scan CUs rollback
0 IM scan CUs no rollback
0 IM scan CUs undo records applied
0 IM scan CUs cleanout
0 IM scan CUs no cleanout
0 IM scan journal cleanout
0 IM scan journal no cleanout
0 IM scan journal
0 IM scan rows journal total
0 IM scan found invalid smu
0 IM repopulate (scan) CUs requested
0 IM repopulate (scan) CUs resubmitted
0 IM repopulate (scan) CUs
0 IM scan CUs no memcompress
0 IM scan CUs memcompress for dml
2 IM scan CUs memcompress for query low
0 IM scan CUs memcompress for query high
0 IM scan CUs memcompress for capacity low
0 IM scan CUs memcompress for capacity high
0 IM scan segments disk
37858147 IM scan bytes in-memory
37833895 IM scan bytes uncompressed
4 IM scan CUs columns accessed
0 IM scan CUs columns decompressed
6 IM scan CUs columns theoretical max
37626 IM scan rows
37626 IM scan rows valid
0 IM scan rows range excluded
0 IM scan rows discontinuous
0 IM scan rows excluded
0 IM scan rows optimized
37626 IM scan rows projected
0 IM scan rows cache
0 IM scan blocks cache
0 IM scan fetches journal
0 IM scan rows journal
2 IM scan CUs split pieces
2 IM scan CUs predicates received
2 IM scan CUs predicates applied
2 IM scan CUs predicates optimized
2 IM scan CUs optimized read
0 IM scan CUs pruned
2 IM scan segments minmax eligible
0 IM scan CUs column not in memory
0 IM scan CUs invalid
0 IM scan invalid all blocks
15 IM scan CUs invalid or missing revert to on disk extent
0 IM scan CUs failed to reget pin
0 IM scan CUs invalid (all rows are invalid)

So you see 7188 session logical reads which is mostly because of “old style” buffer cache scan. We also see 37626 IM scan rows (and similar) that mean we did IM scans as well. And finally a very interesting statistic: 15 IM scan CUs invalid or missing revert to on disk extent.

Let’s have a look in the trace file.
for “normal” IM scan we see an entry:

kdmsGetIMCU(): In arguments: nblks 5: start_addr 37785203, end_addr 37785207
kdmsGetIMCU(): Mem addr: 685fffe8: mem len: 26214400: Start dba: 0x2408e73 37785203: len: 5 smu: 19cff10f0 td: 0x7f2fcff98808
kdmsGetIMCU(): range: 0 lrid dba: 0x0 slot: 0 hrid dba: 0x0 slot: 0
kdmsGetIMCU(): found imcu 0x685fffe8 37785203 37785207 1

but also we see for extents that missed in IM column store (by the way we see exactly 15 such entries)


kdmsGetIMCU(): In arguments: nblks 34: start_addr 37849310, end_addr 37849343
kdmsGetIMCU(): Mem addr: 0: mem len: 0: Start dba: 0x0 0: len: 1 smu: 0 td: 0x7f2fcff98808
kdmsGetIMCU(): range: 0 lrid dba: 0x0 slot: 0 hrid dba: 0x0 slot: 0
kdmsGetIMCU(): on disk (nil) !ktmpjInTxn: 1 range: 0 sample: 0

So my guess as we check IM column store for the next extent not found it in memory and switch over to the “old fashion” buffer cache scan.

Advertisements

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