Monday, August 18, 2014

dbms_feature_usage in 12.1.0.2 and the in-memory option.

With all the kerfuffle about in-memory reporting usage recently I thought I'd write a post about it.

DBMS_FEATURE_USAGE is a set of packages and procedures produced by Oracle to check ( the clue is in the title ) about how whether particular features are in use, and usually, if a license fee is due.

It does this by running a set of checks and recording the output.

Over a couple of blog posts I will see if we can figure out how it works.

We will take a topical example, that of the In-Memory Option.

So : How does Oracle figure out one is using In-Memory ?

in $ORACLE_HOME/rdbms/admin are several scripts.  The one we are interested in is
catfsusrg.sql

In this long piece of SQL ( 13000+ lines )  are procedures to check if anything from ASM to Exadata is in use.

So how does In-Memory Column Option get checked ?

Step forward

/****************************************************************
 * DBMS_FEATURE_IMC
 * The procedure to detect In-Memory Column Store usage
 ****************************************************************/

What does this do ?
It takes
   'select count(*) from dba_tables where ' ||
       'inmemory_compression is not null',
    'select count(*) from dba_tab_partitions where ' ||
       'inmemory_compression is not null'
   'select count(*) from dba_tab_subpartitions where ' ||
       'inmemory_compression is not null'
    'select count(*) from gv$im_segments_detail where ' ||
       'segtype=0'
( apparently to exclude in-memory journals ) 

Adds up the answer from the above 4 counts;  if the number is > 0 then the feature is in use. 










2 comments:

Paul Bullen said...

And as we now know that SQL is wrong; you cannot use In-Memory until the INMEMORY_SIZE parameter is set... and that SQL doesn't consider the setting of that parameter.

Chris Slattery at Version 1 said...

Yep .... darn it stealing my thunder for part 2 :;)