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_repopulation.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 ***************************************************************************

@connect.sql

SET ECHO ON TERMOUT ON FEEDBACK OFF SERVEROUTPUT OFF LINESIZE 128 PAGESIZE 128 TIMING OFF

REM
REM Setup test environment
REM

DROP TABLE t PURGE;



PAUSE

BEGIN
  dbms_inmemory.populate(schema_name => user, table_name => 'T');
  dbms_lock.sleep(10);
END;
/

PAUSE

SELECT populate_status, bytes_not_populated
FROM v$im_user_segments
WHERE segment_name = 'T';

PAUSE

REM
REM Data in IMCS is up-to-date --> query is fast
REM

SELECT /*+ gather_plan_statistics */ sum(id) FROM t WHERE n = 42;

PAUSE

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

PAUSE

REM
REM Switch off automatic repopulation and update data
REM 1% of data in IMCS is not up-to-date --> query is slow
REM

ALTER SYSTEM SET "_inmemory_repopulate_disable" = TRUE SCOPE=MEMORY;
ALTER SYSTEM SET "_inmemory_trickle_repopulate" = FALSE SCOPE=MEMORY;

UPDATE t SET n = n + 1 WHERE mod(id,100) = 0;

COMMIT;

ALTER SYSTEM FLUSH BUFFER_CACHE;

@connect.sql

PAUSE

SELECT /*+ gather_plan_statistics */ sum(id) FROM t WHERE n = 42;

PAUSE

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

PAUSE

SELECT event, total_waits 
FROM v$session_event 
WHERE sid = sys_context('userenv','sid')
AND wait_class = 'User I/O';

PAUSE

REM
REM Switch on automatic repopulation --> query is fast again
REM

ALTER SYSTEM SET "_inmemory_repopulate_disable" = FALSE SCOPE=MEMORY;
ALTER SYSTEM SET "_inmemory_trickle_repopulate" = TRUE SCOPE=MEMORY;

REM Default: 120 seconds
ALTER SYSTEM SET "_inmemory_imco_cycle" = 5 scope=memory;

execute dbms_lock.sleep(10)

PAUSE

SELECT /*+ gather_plan_statistics */ sum(id) FROM t WHERE n = 42;

PAUSE

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