rem Analyze SQL library cache performance.  There are two approaches:
rem
rem   1. See how many statement executions were marked as having the SQL 
rem      missing from the library cache
rem   2. Count how many library cache addresses a statement has
rem
rem To determine which statements have been swapped in and out of the library 
rem cache, query the SQL Segment event table (v_192216243_f_5_e_7_8_1) using
rem the statement identifier.

select sql_text_hash_end "SQL ID", count(*) "# Misses"
    from v_192216243_f_5_e_9_8_1
    where missed_end = 1
    group by sql_text_hash_end
    having count(*) > 1;

select distinct (et.sql_text_hash_end) "SQL ID", 
       count(distinct et.lib_cache_addr_end) "Cache Addrs"
       from v_192216243_f_5_e_9_8_1 et, v_192216243_f_5_e_9_8_1 ev,
            epc_collection c
       where et.sql_text_hash_end = ev.sql_text_hash_end and
             c.collection_name = '&&cllctn' and
             c.collection_id = et.collection_number and
             c.collection_id = ev.collection_number
       group by et.sql_text_hash_end 
       having count(distinct ev.lib_cache_addr_end) > 1;

select count(distinct sql_text_hash_end) "# of SQL Stmts"
       from v_192216243_f_5_e_9_8_1;

-- select sql_text from v_192216243_f_5_e_7_8_1 
-- where sql_text_hash = <SQL ID>;
