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_fts_no_filter.sql
REM Author......: Christian Antognini
REM Date........: September 2015
REM Description.: This script compares the execution of a simple query doing a
REM               FTS accessing different data structures (data on disk with 
REM               and without offloading, data in the buffer cache, data in the
REM               IMCS).
REM Notes.......: The query used in this script does not have a WHERE clause.
REM               An example with a WHERE clause is available in the script
REM               imcs_fts_filter.sql.
REM Parameters..: -
REM Requirements: refer to imcs_prepare_schema.sql
REM
REM Changes:
REM DD.MM.YYYY Author Description
REM ---------------------------------------------------------------------------
REM 
REM ***************************************************************************

COLUMN segment_name FORMAT A12
COLUMN object_name FORMAT A12
COLUMN gb FORMAT 9,990.9
COLUMN mb FORMAT 999,990.999
COLUMN im_mb FORMAT 999,990.999
COLUMN not_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;

PAUSE

SELECT segment_name, bytes/1E9 AS gb
FROM user_segments 
WHERE segment_name IN ('SALES', 'SALES_CACHE');

PAUSE

SELECT /*+ full(s) parallel */ count(*)
FROM sales_cache s;

PAUSE

SELECT o.object_name, count(*)*8192/1E9 AS gb
FROM user_objects o JOIN v$bh b ON o.data_object_id = b.objd
WHERE b.status != 'free'
AND o.object_name IN ('SALES', 'SALES_CACHE')
GROUP BY o.object_name;

PAUSE

SELECT inst_id, segment_name,
       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
ORDER BY inst_id;

PAUSE

ALTER SESSION SET inmemory_query = DISABLE;
ALTER SESSION SET cell_offload_processing = FALSE;

PAUSE

SELECT count(amount_sold)
FROM sales;

PAUSE

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

PAUSE

SELECT count(amount_sold)
FROM sales_cache;

PAUSE

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

PAUSE

ALTER SESSION SET inmemory_query = DISABLE;
ALTER SESSION SET cell_offload_processing = TRUE;

PAUSE

SELECT count(amount_sold)
FROM sales;

PAUSE

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

PAUSE

ALTER SESSION SET inmemory_query = ENABLE;

PAUSE

SELECT count(amount_sold)
FROM sales;

PAUSE

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

