rem Analyze number of fetches and number of rows per cursor execution

CREATE TABLE fetches_temp 
  (session_index  NUMBER,
   session_serial NUMBER,
   cursor_number  NUMBER, 
   sql_text_hash  NUMBER,
   frequency      NUMBER,
   rows_fetched   NUMBER);

INSERT INTO fetches_temp (session_index, session_serial, 
                           cursor_number, sql_text_hash)
    SELECT DISTINCT f.session_index_end, f.session_serial_end, 
                    f.cursor_number_end, f.sql_text_hash_end 
    FROM v_192216243_f_5_e_10_8_1 f, epc_collection c
    WHERE c.collection_name = '&&cllctn' and
          c.collection_id = f.collection_number;

UPDATE fetches_temp t
    SET frequency = (SELECT count(*)
                     FROM v_192216243_f_5_e_10_8_1 f, epc_collection c
                     WHERE t.session_index = f.session_index_end 
                       and t.session_serial = f.session_serial_end
                       and t.cursor_number = f.cursor_number_end
                       and c.collection_name = '&&cllctn' 
                       and c.collection_id = f.collection_number),
        rows_fetched = (SELECT sum(f.row_count_end)
                        FROM v_192216243_f_5_e_10_8_1 f, epc_collection c
                        WHERE t.session_index = f.session_index_end 
                          and t.session_serial = f.session_serial_end
                          and t.cursor_number = f.cursor_number_end
                          and c.collection_name = '&&cllctn' 
                          and c.collection_id = f.collection_number);

SELECT min(frequency) "Min Fetches",
       max(frequency) "Max Fetches",
       avg(frequency) "Avg Fetches",
       min(rows_fetched) "Min Rows",
       max(rows_fetched) "Max Rows",
       avg(rows_fetched) "Avg Rows",
       count(*) "Cursors" 
       FROM fetches_temp;

SELECT s.sql_text "Stmts fetched single row"
FROM v_192216243_f_5_e_7_8_1 s, fetches_temp t, epc_collection c
WHERE t.rows_fetched = 1
  and t.sql_text_hash = s.sql_text_hash
  and c.collection_name = '&&cllctn'
  and c.collection_id = s.collection_number;

drop table fetches_temp;
