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

Monitor and Analyze your Oracle Database (11g, 12c)

These are the very useful scripts, that I often use it. this can be very handy.
[ For RAC, you may have to tweak it a bit, using gv$ views for some scripts]

1. Monitor Database, what top SQL's are currently running in your DB instance. 

(Get sqlid, sql text, serial, elapsed time, plan_hash_value etc )  This can be a good start  :)

set lines 200 pages 200 

col USERNAME format a15
col SQL_TEXT format a30
select sesion.sid,
       sesion.SERIAL#,
       sesion.username,
       optimizer_mode,
       plan_hash_value,
       address,
       cpu_time,
       elapsed_time/1000/1000 elapsed_Second,
           sqlarea.SQL_ID,
       substr(sql_text,1,30) sql_text
  from v$sqlarea sqlarea, v$session sesion
 where sesion.sql_hash_value = sqlarea.hash_value
   and sesion.sql_address    = sqlarea.address
   and sesion.username is not null
   and rownum <= 20
   order by elapsed_time desc
   ;

Sample Output :

2.  Show SQL Execution Plan for any SQL  ID

Ok,  Now I know what sql's are running currently, I am interested in checking the execution plan of it. You can get the execution plan from cursor. If not available, then you can get it from AWR.

Cursor :
select * from table(dbms_xplan.display_cursor('&sql_id',0));

AWR:

select plan_table_output from table (dbms_xplan.display_awr('&sqlid'));

From AWR it gives all previous execution plans, which are used for a that SQL ID 
Suppose, I need to get a execution plan for sql_id with with specific plan_hash_value only, then I would do.

SQL_ID & Plan_Hash_Value:
select plan_table_output from table (dbms_xplan.display_awr('&sql_id','&PlanHash')); 

Get bind values for SQL query along with execution plan

select * from table(dbms_xplan.display_cursor('&sqlid',NULL,'peeked_binds'))

3.  SQL ID execution history, previous execution and elapsed time, what execution plan it used. 

Now I am also interested to see, all previous history when this SQL was executed, how may times executed and what execution plan it used that time.  Now this is very interesting and very useful. 

set lines 200 pages 200
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
col sql_profile format a30
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode (nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode (nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,sql_profile
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id','XXXXXXXXX')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
/

Output :


If you see above output, it will give you, Snapshot ID,  Node, execution time, Plan details (if it used a different execution plan at different time), number of execution, avg execution time, AVG_LIO (Logical IO or Buffer Gets), also whether it is using any SQL profile that you have created.  Pretty cool script !!

4. Find Top wait events in last 5 minutes.

set lines 200 pages 200
col WAIT_CLASS format a30
select * from (Select distinct a.event, a.total_waits, a.time_waited, a.average_wait, c.wait_class
  From v$system_event a, v$event_name b, v$system_wait_class c, v$active_session_history d
  Where a.event_id=b.event_id
  And a.event_id=d.event_id
  And b.wait_class#=c.wait_class#
  And c.wait_class in ('CPU','CPU Wait','Application','Concurrency','Cluster','Other','Network','Configuration','Commit','User I/O','System I/O')
  And d.sample_time > sysdate - ( 5 / ( 24 * 60 ) )
  order by average_wait desc)
  where rownum <= 15;
-- Waits during last 5 Minutes
SELECT event Event_LAST_5Min,
       COUNT(*)
FROM   v$active_session_history
WHERE  session_state = 'WAITING'
       AND sample_time > sysdate - ( 5 / ( 24 * 60 ) )
GROUP  BY event
ORDER  BY COUNT(*) DESC;

5.  Find SQL ID or SQL Query waiting on wait event

select event, sql_id, count(*),
avg(time_waited) avg_time_waited
from v$active_session_history
where event like nvl('&event','%more data from%')
group by event, sql_id
order by event, 3
/ 

6.  Find database size 

col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/ 

7. Find all Tablespace Size and usage 

SELECT /* + RULE */  df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024) "Size (MB)",
       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
       fs.bytes / (1024 * 1024),
       SUM(df.bytes_free) / (1024 * 1024),
       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs,
       (SELECT tablespace_name,bytes_free,bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
 ORDER BY 4 DESC;
 

8.  Find all indexes for a table, with details

break on index_name on uniqueness skip 1
column column_expression format a20
col COLUMN_NAME format a40
col INDEX_NAME format a30
select i.index_name, i.uniqueness, c.column_name, f.column_expression,i.status,i.visibility,i.last_analyzed
from all_ind_columns c, all_indexes i, all_IND_EXPRESSIONS f
where i.table_owner = '&OWNER'
and i.table_name = '&TABLE_NAME'
and i.index_name = c.index_name
and i.owner = c.indeX_owner
and c.index_owner = f.index_owner(+)
and c.index_name = f.index_name(+)
and c.table_owner = f.table_owner(+)
and c.table_name = f.table_name(+)
and c.column_position = f.column_position(+)
order by i.index_name, c.column_position
/

9.  Fetch Table definition

select DBMS_METADATA.GET_DDL('TABLE','&TABLE_name') from DUAL;

10.  Fetch Tablespace definition

select
     dbms_metadata.get_ddl('TABLESPACE',&tablespace_name)
from
     dba_tablespaces
;



I end here, with these 10 most useful scripts, I will share some more scripts in my next blogs.