Yesterday, I read a blog post from Maria Colgan (https://sqlmaria.com/2017/02/21/oracle-storage-index/) who described why you will not always see the benefits of In memory Storage Indexes.
I won’t re-write Maria’s post but to sum up, Oracle doesn’t sort the data to build IMCUs, as a result min and max values of the storage indexes are not very selective. I’ve explained this in my SIMD related presentation (available here: https://www.slideshare.net/lolo115/ukoug15-simd-outside-and-inside-oracle-12c-12102, See. Slides 22 and 23).
To go further, we can find a bunch of views, oops a bunch of undocumented views, related to IM segments, Compression units and SMUs. (See doc bug in MOS: Bug 19361690 : SEVERAL V$ VIEWS FOR INMEMORY ARE NOT DOCUMENTED, Bug doesn’t seem to be fixed in 12.2)
If you have a look at these views, they contains a lot of very interesting things, and specially the min and max values for every IMCUs and columns inside.
In the below example, I took the same kind of example that Maria described.
First, I created two tables loaded in the IM store. Those tables are based on SH.SALES table, the first one is not sorted, the second is ordered by AMOUNT_SOLD :
SQL> create table s inmemory no memcompress priority critical as select * from sh.sales;
SQL> create table s2 inmemory no memcompress priority critical as select * from sh.sales order by amount_sold;
SQL> @IM_seg
Enter value for owner: LAURENT
Enter value for segment_name:
OWNER SEGMENT_NAME PARTITION_NAM TABLESPACE_NAME INMEMORY_SIZE BYTES BYTES_NOT_POPULATED POPULATE_ INMEMORY INMEMORY_COMPRESS
----------- --------------- ------------- --------------- ------------- ---------- ------------------- --------- -------- -----------------
LAURENT S USERS 28966912 37748736 0 COMPLETED CRITICAL NO MEMCOMPRESS
LAURENT S2 USERS 28966912 37748736 0 COMPLETED CRITICAL NO MEMCOMPRESS
-------------
sum 57933824
Then, I used a script of mine based on one of these undocumented stuff, the V$IM_COL_CU view that gives us min and max values for each column in the IMCUs.
SQL> @IM_IMCU_stats
Enter value for tab_owner: LAURENT
Enter value for tab_name: S
Enter value for column_name: AMOUNT_SOLD
IMCU_ADDR OBJD ONAME COLUMN_NAME DATA_TYPE DICTIONARY_ENTRIES MIN_VAL MAX_VAL
-------------------- ---------- -------------------- ------------------------------ ---------- ------------------ --------------- ---------------
00000000610FFF70 94467 S AMOUNT_SOLD NUMBER 0 7.14 1520.39
00000000613FFF70 94467 S AMOUNT_SOLD NUMBER 0 7.22 1533.38
00000000616FFF70 94467 S AMOUNT_SOLD NUMBER 0 6.83 1566.01
00000000619FFF70 94467 S AMOUNT_SOLD NUMBER 0 6.4 1598.63
0000000061CFFF70 94467 S AMOUNT_SOLD NUMBER 0 6.54 1738.43
0000000076000000 94467 S AMOUNT_SOLD NUMBER 0 6.54 1738.43
0000000076300000 94467 S AMOUNT_SOLD NUMBER 0 6.54 1782.72
0000000076600000 94467 S AMOUNT_SOLD NUMBER 0 7.13 1782.72
0000000076900000 94467 S AMOUNT_SOLD NUMBER 0 7.13 1753.2
9 rows selected.
-- -------------------------------------
SQL> @IM_IMCU_stats
Enter value for tab_owner: LAURENT
Enter value for tab_name: S2
Enter value for column_name: AMOUNT_SOLD
IMCU_ADDR OBJD ONAME COLUMN_NAME DATA_TYPE DICTIONARY_ENTRIES MIN_VAL MAX_VAL
-------------------- ---------- -------------------- ------------------------------ ---------- ------------------ --------------- ---------------
0000000071FFFFE8 94469 S2 AMOUNT_SOLD NUMBER 0 481 1782.72
00000000721FFFE8 94469 S2 AMOUNT_SOLD NUMBER 0 69.08 481
00000000724FFFE8 94469 S2 AMOUNT_SOLD NUMBER 0 51.43 69.08
00000000727FFFE8 94469 S2 AMOUNT_SOLD NUMBER 0 46.31 51.43
0000000072AFFFE8 94469 S2 AMOUNT_SOLD NUMBER 0 32.35 46.31
0000000072DFFFE8 94469 S2 AMOUNT_SOLD NUMBER 0 24.24 32.35
00000000730FFFE8 94469 S2 AMOUNT_SOLD NUMBER 0 17.79 24.24
00000000733FFFE8 94469 S2 AMOUNT_SOLD NUMBER 0 10.79 17.79
00000000736FFFE8 94469 S2 AMOUNT_SOLD NUMBER 0 6.4 10.79
9 rows selected.
We can clearly see that S2 table has been ordered by AMOUNT_SOLD, and if I count the number of lines with a value of AMOUNT_SOLD equal to 20, S table (unordered) will read all IMCUs (9), a query againt S2 will prune 8 of the 9 IMCUs.
SQL> @IM_sesstat
NAME VALUE
---------------------------------------------------------------- ----------
CPU used by this session 3
IM scan CUs no memcompress 0
IM scan CUs memcompress for dml 0
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 CUs columns accessed 0
IM scan CUs columns decompressed 0
IM scan CUs columns theoretical max 0
IM scan rows 0
IM scan rows valid 0
IM scan rows optimized 0
IM scan rows projected 0
IM scan CUs split pieces 0
IM scan CUs pruned 0
16 rows selected.
SQL> select /*+ INMEMORY */ count(*) from s where amount_sold=20;
COUNT(*)
----------
140
1 row selected.
SQL> @IM_sesstat
NAME VALUE
---------------------------------------------------------------- ----------
CPU used by this session 20
IM scan CUs no memcompress 9
IM scan CUs memcompress for dml 0
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 CUs columns accessed 9
IM scan CUs columns decompressed 0
IM scan CUs columns theoretical max 63
IM scan rows 918843
IM scan rows valid 918843
IM scan rows optimized 0
IM scan rows projected 140
IM scan CUs split pieces 9
IM scan CUs pruned 0
16 rows selected.
-- ---------------------------------------------
SQL> @IM_sesstat
NAME VALUE
---------------------------------------------------------------- ----------
CPU used by this session 1
IM scan CUs no memcompress 0
IM scan CUs memcompress for dml 0
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 CUs columns accessed 0
IM scan CUs columns decompressed 0
IM scan CUs columns theoretical max 0
IM scan rows 0
IM scan rows valid 0
IM scan rows optimized 0
IM scan rows projected 0
IM scan CUs split pieces 0
IM scan CUs pruned 0
16 rows selected.
SQL> select /*+ INMEMORY */ count(*) from s2 where amount_sold=20;
COUNT(*)
----------
140
SQL> @IM_sesstat
NAME VALUE
---------------------------------------------------------------- ----------
CPU used by this session 2
IM scan CUs no memcompress 9
IM scan CUs memcompress for dml 0
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 CUs columns accessed 1
IM scan CUs columns decompressed 0
IM scan CUs columns theoretical max 63
IM scan rows 918843
IM scan rows valid 103923
IM scan rows optimized 814920
IM scan rows projected 140
IM scan CUs split pieces 9
IM scan CUs pruned 8
16 rows selected.
All the scripts I used in the post, including the one used to see storage indexes, are available here:
That’s all for today !! 🙂
Like this:
Like Loading...
Related
Data ... as usual 