In-memory table I read it first on somebody's blog. Oracle 12.1.0.2's in-memory option for a table almost completely eliminates consistent gets. Here's my test. Create a table sufficiently large. Mine is 13 MB according to user_segments. Don't create index. set autot on select count(*) from test; Run the query multiple times. The first time I get about 1500 consistent gets and physical reads. From second time on, physical reads is 0 and consistent gets remains the same, as expected. Set inmemory_size=100M scope=spfile. (The minimum allowed by Oracle is 100M. Or you would get "ORA-64353: in-memory area size cannot be less than 100MB".) Bounce database. Alter table test inmemory. Run the query multiple times. From second or third time on, consistent gets drops to 3![note] More test shows that the reduction of consistent gets is not as complete as I thought. It may just be the way how Sqlplus "set autotrace on" reports the number. A better way to study this is to check the session statistics in v$sesstat. I'll use my "Delta sesstat" (see ./SQLsFreqUsed.txt) which gives me all stats whose values have changed over a period of time for a give session. 1. Find the session's sid. Say it's 123. 2. Open another session for monitoring and prepare: SQL> create table stat (seq int, stat number, val number); Table created. SQL> define sid=123 SQL> set verify off SQL> insert into stat select 1, statistic#, value from v$sesstat where sid = &&sid; 1178 rows created. 3. Back to the first session and run select count(*) from test. (Assume this SQL has been run before so physical reads are already 0). 4. Back to the monitoring session: SQL> insert into stat select 2, statistic#, value from v$sesstat where sid = &&sid; 1178 rows created. SQL> commit; Commit complete. SQL> select name, oldval, newval, diff 2 --,decode(oldval,0,'***',trunc(diff/oldval*100,2)) pct_chng 3 from ( 4 select stat, lag(val) over (partition by stat order by seq) oldval, 5 val newval, val - lag(val) over (partition by stat order by seq) diff 6 from stat 7 ) t, v$statname n where t.diff > 0 and t.stat = n.statistic# order by name; NAME OLDVAL NEWVAL DIFF -------------------------------------------- ---------- ---------- ---------- CCursor + sql area evicted 9 12 3 CPU used by this session 10 11 1 CPU used when call started 9 11 2 DB time 8 9 1 IM scan CUs columns accessed 0 18 18 IM scan CUs columns theoretical max 0 18 18 IM scan CUs memcompress for query low 0 1 1 IM scan CUs split pieces 0 1 1 IM scan bytes in-memory 0 3447385 3447385 <------ IM scan bytes uncompressed 0 10563164 10563164 <------ IM scan rows 0 91403 91403 IM scan rows projected 0 91403 91403 IM scan rows valid 0 91403 91403 Requests to/from client 13 15 2 SQL*Net roundtrips to/from client 14 16 2 buffer is not pinned count 572 661 89 buffer is pinned count 17 19 2 bytes received via SQL*Net from client 4210 4513 303 bytes sent via SQL*Net to client 4479 4839 360 calls to get snapshot scn: kcmgss 230 273 43 calls to kcmgcs 62 76 14 cluster key scan block gets 58 74 16 cluster key scans 49 65 16 consistent gets 832 976 144 <------ consistent gets examination 291 355 64 consistent gets examination (fastpath) 291 355 64 <------ consistent gets from cache 832 976 144 consistent gets pin 541 621 80 consistent gets pin (fastpath) 540 620 80 <------ cursor authentications 6 7 1 enqueue releases 20 24 4 enqueue requests 21 25 4 execute count 226 267 41 index fetch by key 74 95 21 index scans kdiixs1 136 155 19 logical read bytes from cache 6840320 8019968 1179648 <------ no work - consistent read gets 485 554 69 non-idle wait count 25 27 2 opened cursors cumulative 213 254 41 parse count (hard) 20 22 2 parse count (total) 124 133 9 parse time elapsed 9 10 1 recursive calls 1554 1740 186 session cursor cache count 47 49 2 session cursor cache hits 170 205 35 session logical reads 835 2516 1681 session logical reads - IM 0 1537 1537 session pga memory 1224808 1618024 393216 session uga memory 610184 806648 196464 sorts (memory) 77 96 19 sorts (rows) 486 550 64 sql area evicted 14 16 2 table fetch by rowid 146 162 16 table scan blocks gotten 134 150 16 table scan disk non-IMC rows gotten 10890 11538 648 table scan rows gotten 10890 102941 92051 table scans (IM) 0 1 1 table scans (short tables) 24 29 5 user calls 20 23 3 workarea executions - optimal 37 44 7 Note the "consistent gets*" statistics (the DIFF column). It shows that 144 of total "consistent gets" come from 64 "consistent gets examination (fastpath)" and 80 "consistent gets pin (fastpath)". It's not correct to say in-memory table scan eliminates consistent gets or cuts it down to almost 0 or to only 3. But 144 consistent gets is indeed much less than over 1500 for a regular, non-in-memory, table, i.e. before this same table was altered to inmemory. For comparison, the following shows the stat delta after altering the table to no inmemory again and making sure "set auto on" reports stable "consistent gets" and 0 "physical reads". NAME OLDVAL NEWVAL DIFF -------------------------------------------- ---------- ---------- ---------- CPU used by this session 16 17 1 CPU used when call started 16 17 1 DB time 19 20 1 Requests to/from client 37 39 2 SQL*Net roundtrips to/from client 38 40 2 bytes received via SQL*Net from client 10949 10960 11 bytes sent via SQL*Net to client 10447 10807 360 calls to get snapshot scn: kcmgss 350 351 1 calls to kcmgcs 143 149 6 consistent gets 4166 5708 1542 <------ consistent gets from cache 4166 5708 1542 consistent gets pin 3768 5310 1542 consistent gets pin (fastpath) 3766 5308 1542 <------ execute count 329 330 1 logical read bytes from cache 34807808 47439872 12632064 <------ no work - consistent read gets 3654 5191 1537 non-idle wait count 72 74 2 opened cursors cumulative 315 316 1 parse count (total) 186 187 1 session cursor cache hits 223 224 1 session logical reads 5786 7328 1542 table scan blocks gotten 3235 4772 1537 table scan disk non-IMC rows gotten 194407 285810 91403 table scan rows gotten 285816 377219 91403 table scans (short tables) 44 45 1 user calls 62 65 3 It looks like all consistent gets are from "consistent gets pin (fastpath)". It's also interesting to see that "logical read bytes from cache" is 12MB, much higher than in the in-memory case, which was 1MB. But in the in-memory case, "IM scan bytes uncompressed" is a little over 10MB. So the accounting has mostly shifted to another part rather than disappeared. Still, the "IM scan bytes in-memory" is only 1/3 of that, due to compression when the data are in the in-memory area of SGA. Actual usage of in-memory area: SQL> select pool, alloc_bytes, used_bytes from v$inmemory_area; POOL ALLOC_BYTES USED_BYTES --------------- ----------- ---------- 1MB POOL 82837504 4194304 64KB POOL 16777216 131072 so 4194304+131072=4325376 or 4.125 MB, matching SQL> select inmemory_size, bytes from v$im_segments where owner=... and segment_name=...; <-- can also be V$IM_USER_SEGMENTS; INMEMORY_SIZE BYTES ------------- ---------- 4325376 13631488 for the 13631488 byte table. Just shifting stat accounting of course is not performance improvement. So let's measure it with an OS- level tool. Here's a comparison with `perf stat' against the server process for my sqlplus session (measured after discarding a few runs of the query): Regular table: select count(*) from test: $ perf stat -p 7675 ^C Performance counter stats for process id '7675': 3.974637 task-clock # 0.001 CPUs utilized 2 context-switches # 0.503 K/sec 0 cpu-migrations # 0.000 K/sec 1 page-faults # 0.252 K/sec 4,679,042 cycles # 1.177 GHz [75.09%] 3,531,716 stalled-cycles-frontend # 75.48% frontend cycles idle [75.02%] 2,890,354 stalled-cycles-backend # 61.77% backend cycles idle [67.48%] 3,375,777 instructions # 0.72 insns per cycle # 1.05 stalled cycles per insn 817,691 branches # 205.727 M/sec 4,559 branch-misses # 0.56% of all branches [83.54%] 2.882105750 seconds time elapsed In-memory table: select count(*) from test: $ perf stat -p 7675 ^C Performance counter stats for process id '7675': 1.763323 task-clock # 0.001 CPUs utilized 2 context-switches # 0.001 M/sec 0 cpu-migrations # 0.000 K/sec 6 page-faults # 0.003 M/sec 2,013,467 cycles # 1.142 GHz [44.21%] 1,247,647 stalled-cycles-frontend # 61.97% frontend cycles idle [98.78%] 1,155,291 stalled-cycles-backend # 57.38% backend cycles idle 1,869,635 instructions # 0.93 insns per cycle # 0.67 stalled cycles per insn 477,465 branches # 270.776 M/sec 8,045 branch-misses # 1.68% of all branches [62.51%] 3.083091165 seconds time elapsed We see that most counters are more than halved. It's possible that in the in-memory case, a row count is not really a full table scan reading all table blocks, in spite of "TABLE ACCESS INMEMORY FULL" in the plan. It may be going through an optimized path in the column store. Also see http://www.itpub.net/thread-1889129-1-1.html 2014-09 for a test of typical OLTP query, where In-Memory does NOT have performance advantage over a regular non-inmemory table. DMLs were not tested. (http://www.databasejournal.com/features/oracle/increase-dss-performance-by-100x-and-oltp-by-2x-switch-to-oracle-12c.html says otherwise) * Views and tables related to In-Memory CDB_HIST_IM_SEG_STAT CDB_HIST_IM_SEG_STAT_OBJ DBA_HIST_IM_SEG_STAT DBA_HIST_IM_SEG_STAT_OBJ WRH$_IM_SEG_STAT WRH$_IM_SEG_STAT_BL WRH$_IM_SEG_STAT_OBJ V$IM_COLUMN_LEVEL: (base table x$kdzcolcl) V$IM_COL_CU: column compression unit (base table x$kdmimccol) V$IM_HEADER: timestamp can tell you when it's put in IM (base table x$kdmimchead) V$IM_SEGMENTS: (base table x$imcsegments) V$IM_SEGMENTS_DETAIL: (base table x$imcsegments) V$IM_SEG_EXT_MAP: (base table x$imcsegextmap) V$IM_SMU_CHUNK: (base table x$ktmtxnchunk) V$IM_SMU_HEAD: (base table x$ktmtxnhead) V$IM_TBS_EXT_MAP: (base table x$imctbsextmap) V$IM_USER_SEGMENTS: (base table x$imcsegments) V$INMEMORY_AREA: (base table x$ktsimau) V$KEY_VECTOR: "provides debugging information related to the data structures used by in-memory aggregation for current and recent queries using key vectors" (base table x$qesxl) X$KTSIMAPOOL: pool types X$KEWRIMSEGSTAT: ? * PL/SQL packages or procedures DBMS_INMEMORY: has procedures to en/dis-able fast start ("In-memory FastStart is only supported on Oracle-engineered systems" as ORA-64368 says), procedures to (re)populate in-memory area DBMS_FEATURE_IMA: in-memory aggregate DBMS_FEATURE_IMC: in-memory column store * Parameters Too many to list. All have the string pattern "inmemory". * Official document http://www.oracle.com/technetwork/database/in-memory/overview/twp-oracle-database-in-memory-2245633.html ____________________ [note] I suspect the 3 "consistent" gets are actually current mode gets. So I tried to use _trace_pin_time to see it according to Fuyuncat's article http://www.hellodba.com/reader.php?ID=39&lang=en In 12c, the parameter no longer takes a number as its value, but a boolean instead. So I set it to true and bounced the database. Unfortunately, after running my query, there's still nothing generated in the trace, or nothing specific to current mode gets when combined with events 10046, 10202, 10200. Reading in-memory area should be quite different than reading the traditional buffer cache. It's an area of memory where data are stored in column order. It's no surprise some trace events relevant to buffer cache are no longer applicable.