Tuesday, August 19, 2014

More new Checks in DBMS_FEATURE_USAGE

After yesterdays post I decided to just run a quick diff against and catfsusrg.sql

This returned about 2000 lines, so I filtered it by new procedures

this results in

 CREATE OR REPLACE PROCEDURE dbms_feature_ba_owner
CREATE OR REPLACE PROCEDURE dbms_feature_adaptive_plans
CREATE OR REPLACE PROCEDURE dbms_feature_auto_reopt
create or replace procedure DBMS_FEATURE_JSON
create or replace procedure DBMS_FEATURE_IOT(
create or replace procedure DBMS_FEATURE_IMC(
create or replace procedure DBMS_FEATURE_ADV_TABCMP(
create or replace procedure DBMS_FEATURE_DATABASE_ODM
create or replace procedure DBMS_FEATURE_IMA(

A quick look at a couple of these
zmap=Zone Maps ( ? )

dbms_ba_owner looks like something to do with RMAN as there is much stuff in recover.bsq to back it.    Hmmmm....

Monday, August 18, 2014

dbms_feature_usage in 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

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

 * 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 ' ||
( 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.