SET ECHO OFF
REM ***************************************************************************
REM ***************************** T R I V A D I S *****************************
REM ******************** Oracle Database 12c New Features *********************
REM ************************ http://www.trivadis.com **************************
REM ***************************************************************************
REM
REM Privileges..: DBA
REM File name...: imcs_duplicate.sql
REM Author......: Christian Antognini
REM Date........: September 2015
REM Description.: This script shows the impact of modifying data that is
REM               present in the IMCS.
REM Notes.......: -
REM Parameters..: -
REM Requirements: Oracle Database 12.1.0.2 with the In-Memory option configured
REM
REM Changes:
REM DD.MM.YYYY Author Description
REM ---------------------------------------------------------------------------
REM 
REM ***************************************************************************

COLUMN segment_name FORMAT A12
COLUMN mb FORMAT 999,990.999
COLUMN im_mb FORMAT 999,990.999
COLUMN non_pop_mb FORMAT 999,990.999

@connect.sql

SET ECHO ON TERMOUT ON LINESIZE 128 PAGESIZE 128 SERVEROUTPUT OFF

ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;

ALTER SESSION SET parallel_degree_policy = MANUAL;
ALTER SESSION SET statistics_level = ALL;
ALTER SESSION SET inmemory_query = ENABLE;

PAUSE

REM
REM Test 1 - Only one instance must be up
REM

PAUSE

SELECT count(*) FROM gv$instance;

PAUSE

ALTER TABLE sales NO INMEMORY;
ALTER TABLE sales INMEMORY;
execute dbms_inmemory.populate(schema_name => user, table_name => 'SALES');

PAUSE

SELECT inst_id, segment_name, populate_status, inmemory_distribute, inmemory_duplicate,
       sum(bytes)/1E6 AS mb,
       sum(inmemory_size)/1E6 AS im_mb,
       sum(bytes_not_populated)/1E6 AS not_pop_mb
FROM gv$im_user_segments
WHERE segment_name = 'SALES'
GROUP BY inst_id, segment_name, populate_status, inmemory_distribute, inmemory_duplicate;

PAUSE

SELECT count(*) FROM sales;

PAUSE

SELECT * FROM table(dbms_xplan.display_cursor(format=>'iostats last -rows'));

PAUSE

REM
REM Test 2 - Two instances must be up, duplicate
REM

PAUSE

SELECT count(*) FROM gv$instance;

PAUSE

ALTER TABLE sales NO INMEMORY;
ALTER TABLE sales INMEMORY DUPLICATE;
execute dbms_inmemory.populate(schema_name => user, table_name => 'SALES');

PAUSE

SELECT inst_id, segment_name, populate_status, inmemory_distribute, inmemory_duplicate,
       sum(bytes)/1E6 AS mb,
       sum(inmemory_size)/1E6 AS im_mb,
       sum(bytes_not_populated)/1E6 AS not_pop_mb
FROM gv$im_user_segments
WHERE segment_name = 'SALES'
GROUP BY inst_id, segment_name, populate_status, inmemory_distribute, inmemory_duplicate;

PAUSE

SELECT count(*) FROM sales;

PAUSE

SELECT * FROM table(dbms_xplan.display_cursor(format=>'iostats last -rows'));

PAUSE

REM
REM Test 3 - Two instances must be up, no duplicate
REM

PAUSE

SELECT count(*) FROM gv$instance;

PAUSE

ALTER TABLE sales NO INMEMORY;
ALTER TABLE sales INMEMORY NO DUPLICATE;
execute dbms_inmemory.populate(schema_name => user, table_name => 'SALES');

PAUSE

SELECT inst_id, segment_name, populate_status, inmemory_distribute, inmemory_duplicate,
       sum(bytes)/1E6 AS mb,
       sum(inmemory_size)/1E6 AS im_mb,
       sum(bytes_not_populated)/1E6 AS not_pop_mb
FROM gv$im_user_segments
WHERE segment_name = 'SALES'
GROUP BY inst_id, segment_name, populate_status, inmemory_distribute, inmemory_duplicate;

PAUSE

SELECT count(*) FROM sales;

PAUSE

SELECT * FROM table(dbms_xplan.display_cursor(format=>'iostats last -rows'));

PAUSE

REM
REM Test 4 - Two instances must be up, PX
REM

PAUSE

SELECT count(*) FROM gv$instance;

PAUSE

REM commented because there is no change compared to the previous test
REM ALTER TABLE sales NO INMEMORY;
REM ALTER TABLE sales INMEMORY NO DUPLICATE;
REM execute dbms_inmemory.populate(schema_name => user, table_name => 'SALES');

PAUSE

SELECT inst_id, segment_name, populate_status, inmemory_distribute, inmemory_duplicate,
       sum(bytes)/1E6 AS mb,
       sum(inmemory_size)/1E6 AS im_mb,
       sum(bytes_not_populated)/1E6 AS not_pop_mb
FROM gv$im_user_segments
WHERE segment_name = 'SALES'
GROUP BY inst_id, segment_name, populate_status, inmemory_distribute, inmemory_duplicate;

PAUSE

SELECT /*+ parallel */ count(*) FROM sales;

PAUSE

SELECT * FROM table(dbms_xplan.display_cursor(format=>'iostats last -rows'));

