Oracle Video Server 3.0.3.1/OVS_3031_NT.iso/win32/tracesvr/otrcrpt6.sql

Text File | 1997-01-21 | 1.3 KB | 33 lines

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_7_3) using
rem the statement identifier.

select sql_text_hash_end "SQL ID", count(*) "# Misses"
    from v_192216243_f_5_e_9_7_3
    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_7_3 et, v_192216243_f_5_e_9_7_3 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_7_3;

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