Posted by Its not a hard sell to convince anyone that a whopping huge great chunk of compressed column-based memory is going to assist with those analytic-style queries. The In-memory option seems a natural fit, especially for those people where the bottleneck in their infrastructure is the capacity to consume data from storage.
What perhaps a more interesting area of investigation is the claim that In-memory will also be of benefit in OLTP systems, where (to paraphrase many presenters at OpenWorld 2014), we wont be needing all those indexes on transactional tables.
So I thought I’d have an initial play around in that space, and see what happens.
We’ll start with a simple test bed – a transactional table “T”, being a child of parent table “PAR”, with a few indexes representing those “additional” OLTP indexes.
SQL*Plus: Release 12.1.0.2.0 Production on Sat Mar 28 11:58:03 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
SQL> drop table PAR cascade constraints purge;
Table dropped.
SQL> create table PAR ( p int primary key);
Table created.
SQL> insert into PAR
2 select rownum-1 from dual
3 connect by level
SQL> commit;
Commit complete.
SQL>
SQL> exec dbms_stats.gather_table_stats('','PAR');
PL/SQL procedure successfully completed.
SQL>
SQL> drop table T purge;
Table dropped.
SQL>
SQL> create table T (
2 pk int,
3 dist50 int,
4 dist1000 int,
5 dist100000 int,
6 stuff char(30),
7 numeric_stuff number(10,2)
8 );
Table created.
SQL>
SQL> drop sequence seq;
Sequence dropped.
SQL> create sequence seq cache 1000;
Sequence created.
SQL>
SQL> insert /*+ APPEND */ into T
2 select
3 seq.nextval,
4 trunc(dbms_random.value(1,50)),
5 trunc(dbms_random.value(1,1000)),
6 trunc(dbms_random.value(1,100000)),
7 'x',
8 dbms_random.value(1,1000)
9 from
10 ( select 1 from dual connect by level <= 1000 ),
11 ( select 1 from dual connect by level
SQL> commit;
Commit complete.
SQL>
SQL> exec dbms_stats.gather_table_stats('','T');
PL/SQL procedure successfully completed.
SQL>
SQL> alter table T add primary key ( pk );
Table altered.
SQL> create index IX1 on T ( dist50 );
Index created.
SQL> create index IX2 on T ( dist1000 );
Index created.
SQL> create index IX3 on T ( dist100000 );
Index created.
SQL>
SQL> alter table T add constraint FK foreign key ( dist50 )
2 references PAR ( p );
Table altered.
So at this point, we’ve got our table pre-populated with 1million rows, with some columns of varying levels of cardinality, each indexed to support adhoc query.
Now we’ll create our “application” that sits on top of this table. We have a routine for logging a transaction (this is the “OLTP” part of our application)
SQL> create or replace
2 procedure TRANS_LOGGER is
3 begin
4 insert into T values (
5 seq.nextval,
6 trunc(dbms_random.value(1,50)),
7 trunc(dbms_random.value(1,1000)),
8 trunc(dbms_random.value(1,100000)),
9 'x',
10 dbms_random.value(1,1000)
11 );
12 commit work write wait immediate;
13 end;
14 /
Procedure created.
We’re using the extended commit syntax, because when we run this routine in a PL/SQL loop, we dont want to ‘cheat’ by using the PL/SQL optimization that avoids waiting for commits to complete. Now we’ll add the other half of our application, the support for adhoc queries on those non-primary key columns.
SQL> create or replace
2 procedure adhoc_query is
3 x int;
4 res number;
5 begin
6 x := trunc(dbms_random.value(1,50));
7 select sum(numeric_stuff) into res
8 from t where dist50 = x;
9
10 x := trunc(dbms_random.value(1,1000));
11 select sum(numeric_stuff) into res
12 from t where dist1000 = x;
13
14 x := trunc(dbms_random.value(1,100000));
15 select sum(numeric_stuff) into res
16 from t where dist100000 = x;
17
18 end;
19 /
Procedure created.
So there we have it. Our “application” is ready to go 🙂 Now we need to make it “popular”, that is, have lots of people use it. To do this, I’ll create a routine which simulates a moderately busy application server thread. We’ll be firing off lots of these later to crank up the load.
SQL> create or replace
2 procedure app_server_thread is
3 think_time number := 0.05;
4 iterations int := 600 * ( 1 / think_time );
5 begin
6 for i in 1 .. iterations loop
7 dbms_application_info.set_client_info(i||' of '||iterations);
8 trans_logger;
9 dbms_lock.sleep(think_time);
10 end loop;
11 end ;
12 /
Procedure created.
So the “app_server_thread” procedure, will iterate a number of times, calling TRANS_LOGGER with a little bit of “think time”, and will roughly run for 10 minutes (the 600 seconds in the iterations expression above).
We’ll also have a another procedure doing a similar thing, but for adhoc query. It will have a little longer think time, representing that our app is mainly transactional focussed with intermittent query.
SQL> create or replace
2 procedure app_server_thread_query is
3 think_time number := 0.25;
4 iterations int := 600 * ( 1 / think_time );
5 begin
6 for i in 1 .. iterations loop
7 dbms_application_info.set_client_info(i||' of '||iterations);
8 adhoc_query;
9 dbms_lock.sleep(think_time);
10 end loop;
11 end ;
12 /
Procedure created.
We’re pretty much ready to go now. I’m running this on a Linux VM which has 12 “virtual” cores, and 128G of RAM hooked up to a Netapp filer for storage. Using some shell script, we can fire off concurrent session of our “app server” routines. I’m taking an AWR before and after so we can poke around in the results.
#!/bin/ksh
awr()
{
print "
connect / as sysdba
exec dbms_workload_repository.create_snapshot
exit" | sqlplus /nolog 1>awr.out 2>&1
}
TxnThread()
{
print "
connect xxx/yyy
set timing on
exec app_server_thread
exit" | sqlplus /nolog 1>txnthread.out.$1 2>&1
}
QueryThread()
{
print "
connect xxx/yyy
set timing on
exec app_server_thread_query
exit" | sqlplus /nolog 1>querythread.out.$1 2>&1
}
echo AWR
awr
echo TxnThreads
for i in 0 1 2 3 4 5 6 7 8 9
do
TxnThread $i &
done
echo QueryThreads
for i in 1 2 3 4 5
do
QueryThread $i &
done
wait
echo AWR
awr
echo Done
and wait 10 minutes for for it to finish.
The simplest analysis is how long did it take – we know that a good chunk of the time is sleep time, but we’re just going to compare this later with an in-memory test.
# grep Elapsed *out*
querythread.out.1:Elapsed: 00:12:09.42
querythread.out.2:Elapsed: 00:12:08.45
querythread.out.3:Elapsed: 00:12:08.20
querythread.out.4:Elapsed: 00:12:09.04
querythread.out.5:Elapsed: 00:12:08.74
txnthread.out.0:Elapsed: 00:10:20.91
txnthread.out.1:Elapsed: 00:10:20.92
txnthread.out.2:Elapsed: 00:10:21.01
txnthread.out.3:Elapsed: 00:10:21.11
txnthread.out.4:Elapsed: 00:10:20.90
txnthread.out.5:Elapsed: 00:10:21.00
txnthread.out.6:Elapsed: 00:10:21.06
txnthread.out.7:Elapsed: 00:10:21.10
txnthread.out.8:Elapsed: 00:10:20.86
txnthread.out.9:Elapsed: 00:10:20.95
So about 130 seconds (over the 10mins) for the query threads, and 21 seconds (over the 10mins) for the transactional threads. Now I’ve re-run the exact same setup script above, and added a few more steps:
SQL> alter table T inmemory priority critical;
Table altered.
SQL> alter index ix1 unusable;
Index altered.
SQL> alter index ix2 unusable;
Index altered.
SQL> alter index ix3 unusable;
Index altered.
SQL> select segment_name, bytes from v$im_segments;
SEGMENT_NAME BYTES
---------------------------------------- ----------
T 67108864
So now our table is loaded into the in-memory store, and the non-primary key indexes have been tossed out. Now we re-run our benchmark, and see what pops out
# grep Elapsed *out*
querythread.out.1:Elapsed: 00:10:53.10
querythread.out.2:Elapsed: 00:10:53.10
querythread.out.3:Elapsed: 00:10:53.10
querythread.out.4:Elapsed: 00:10:53.07
querythread.out.5:Elapsed: 00:10:53.09
txnthread.out.0:Elapsed: 00:10:20.55
txnthread.out.1:Elapsed: 00:10:21.02
txnthread.out.2:Elapsed: 00:10:20.82
txnthread.out.3:Elapsed: 00:10:20.77
txnthread.out.4:Elapsed: 00:10:20.92
txnthread.out.5:Elapsed: 00:10:20.82
txnthread.out.6:Elapsed: 00:10:20.86
txnthread.out.7:Elapsed: 00:10:20.91
txnthread.out.8:Elapsed: 00:10:20.75
txnthread.out.9:Elapsed: 00:10:20.75
Well thats certainly showing some promise. Our transaction times dont seem to have been adversely affected, and our query performance is better. We can also look at the AWR reports and see how in-memory has influenced things:
Without in-memory
With in-memory
One of the positive outcomes is that redo generation shrinks; less indexes to update means less redo. If you’ve got crappy storage and you’re suffering with redo write issues, then perhaps in-memory is an avenue worth exploring (although you’d want to be carefully considering license costs versus storage upgrade costs!)
Taking a look at the wait events is also interesting
Without in-memory
With in-memory
With in-memory enabled, buffer busy waits have dropped…but they have been “replaced” with a new event “IM buffer busy”. Always remember – Memory is memory – if multiple people want to manipulate it, then some sort of serialisation is going to occur. Similarly, note the appearance of the “latch free” event once in-memory comes into play. But (in this simple demo), the enabling of in-memory has not hindered the transactional characteristics of the app, and looks to have yielded some redo and query benefits.
This is by no means a definitive “yes, you are fine to drop those indexes” and similarly, not a definitive “you must keep those indexes” . Your own application will have its own particular idiosyncracies. But the results are promising – if you’ve got the license dollars for in-memory, you may as well look at exploiting that expenditure in every way possible.
Learning is not a spectator sport 
Interesting post, Connor. I was surprised you pointed out the deltas on busy buffer wait (.2% DB Time in both tests) and IM buffer busy instead of the 48% increase in log file sync which promoted that particular wait event up to 18.9% of DB Time in the IM case! Ouch. Considering the reduce redo payload in the IM case I can’t imagine these LFS are related at all to the log file parallel write that is included in LFS. Thoughts?
Another thing that looks odd–but not a lion’s share of DB Time–is the latch free wait events in the IM case. While only .3% of DB Time it surprises me that a workload burning only .7 seconds of DB CPU/second could suffer *any* latch sleeps…more less latch sleeps that drag on for an *average* of 32.7ms. This could be VM related though I suppose.
Thanks for dropping by Kevin. The main drama I’ve got on the VM I’m playing with is that its got several other databases on it, all of which in varying levels of use, and the VM itself competes with a swag of others for a common path to storage. So I didnt want to invest too heavily into any conclusions on what AWR might come back with, eg, “log file parallel write” jumps all over the place on these VM’s dependent on other activity, whether the moon is low in the sky, if I’m sitting north/south at my keyboard, and countless other factors….sigh. Oh to have a lab like yours 🙂
The next round of tests will be to take out all the sleep time and see how things cope until “flat out” conditions…but I’ll probably take advantage of an existing tool to test that 🙂
Hi Connor, very nice post.
By the way, did you check if your non-IM queries actually made use of the available indexes?
Randolf
Good point – I ran the test initially with the index hint, then removed it so that the case with the unusable’s did not full scan the untouched PK index. I’ll need to double check.
And of course….the gaping omission in the test …. Ididnt reset the seed for dbms_random between tests. So a fresh batch definitely needed!