DB 12c In-Memory - Find what SQL queries (SQL ID's) are really using In-Memory.

I was recently working with Database 12c In-Memory feature and interested to find all the SQL queries, SQL ID which have used In-Memory scans.

I wrote a small PL/SQL script which can quickly give a list of all SQL_ID and Plan_Hash_Value and also the Object Names which used In-Memory Full Scan.

You can try this below script. You just have to enter (past time,within awr retention period) begin time & end time in (24 Hours format ): DD/MM/YY HH:MI  and schema name, which you are interested.

------------ copy from here ------------

set pages 2000 lines 200
SET FEEDBACK OFF
set serveroutput on format wrapped

BEGIN
  dbms_output.put_line('Input Time Format(24 Hours): DD/MM/YY HH:MI');
 END;
/

DECLARE
output number;

BEGIN
   FOR v IN (SELECT /*+ parallel(d,4) */ distinct
    sql_id, plan_hash_value as plan_value
   FROM
 dba_hist_sql_plan d
   WHERE
   object_owner='&SchemaName' and
   to_char(TIMESTAMP,'DD/MM/YY HH24:MI') between '&Begin_Time' and '&End_Time'
   )
   LOOP
  output := 0;
  FOR c_val IN (
    WITH plan_values AS (
      select P.PLAN_TABLE_OUTPUT as plan_tab from (select * from table(dbms_xplan.display_awr(v.sql_id, v.plan_value))) P where P.PLAN_TABLE_OUTPUT like '%TABLE ACCESS INMEMORY FULL%'
    )
    SELECT
      S.*
      FROM
      plan_values S
  )
  LOOP
  DBMS_OUTPUT.PUT_LINE(c_val.plan_tab);
  IF c_val.plan_tab like '%INMEMORY%'
  then
  output := 1;
  END IF;

  END LOOP;
  IF output = 1
  THEN
        dbms_output.new_line;
        DBMS_OUTPUT.PUT_LINE('SQLID : '||v.sql_id||'    Plan_Hash_Value : '||v.plan_value);
        dbms_output.new_line;
  END IF;
   END LOOP;
END;
/

---------------------------------------------------------------------------------------------------------------------

Tags : 
db 12c in memory find sql ids
db 12c in-memory find sql queries
db 12c in-memory list all sql
oracle database 12c monitoring