home *** CD-ROM | disk | FTP | other *** search
/ Oracle Video Server 3.0.3.1 / OVS_3031_NT.iso / win32 / tracesvr / otrcrpt7.sql < prev    next >
Encoding:
Text File  |  1997-01-21  |  2.1 KB  |  52 lines
  1. rem Analyze number of fetches and number of rows per cursor execution
  2.  
  3. CREATE TABLE fetches_temp 
  4.   (session_index  NUMBER,
  5.    session_serial NUMBER,
  6.    cursor_number  NUMBER, 
  7.    sql_text_hash  NUMBER,
  8.    frequency      NUMBER,
  9.    rows_fetched   NUMBER);
  10.  
  11. INSERT INTO fetches_temp (session_index, session_serial, 
  12.                            cursor_number, sql_text_hash)
  13.     SELECT DISTINCT f.session_index_end, f.session_serial_end, 
  14.                     f.cursor_number_end, f.sql_text_hash_end 
  15.     FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
  16.     WHERE c.collection_name = '&&cllctn' and
  17.           c.collection_id = f.collection_number;
  18.  
  19. UPDATE fetches_temp t
  20.     SET frequency = (SELECT count(*)
  21.                      FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
  22.                      WHERE t.session_index = f.session_index_end 
  23.                        and t.session_serial = f.session_serial_end
  24.                        and t.cursor_number = f.cursor_number_end
  25.                        and c.collection_name = '&&cllctn' 
  26.                        and c.collection_id = f.collection_number),
  27.         rows_fetched = (SELECT sum(f.row_count_end)
  28.                         FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
  29.                         WHERE t.session_index = f.session_index_end 
  30.                           and t.session_serial = f.session_serial_end
  31.                           and t.cursor_number = f.cursor_number_end
  32.                           and c.collection_name = '&&cllctn' 
  33.                           and c.collection_id = f.collection_number);
  34.  
  35. SELECT min(frequency) "Min Fetches",
  36.        max(frequency) "Max Fetches",
  37.        avg(frequency) "Avg Fetches",
  38.        min(rows_fetched) "Min Rows",
  39.        max(rows_fetched) "Max Rows",
  40.        avg(rows_fetched) "Avg Rows",
  41.        count(*) "Cursors" 
  42.        FROM fetches_temp;
  43.  
  44. SELECT s.sql_text "Stmts fetched single row"
  45. FROM v_192216243_f_5_e_7_7_3 s, fetches_temp t, collection c
  46. WHERE t.rows_fetched = 1
  47.   and t.sql_text_hash = s.sql_text_hash
  48.   and c.collection_name = '&&cllctn'
  49.   and c.collection_id = s.collection_number;
  50.  
  51. drop table fetches_temp;
  52.