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

Text File | 1997-01-21 | 27.9 KB | 603 lines

rem OTRCDTL.SQL
rem
rem Creates and populates a table with summary data for each SQL statement.
rem Statements are uniquely identified by the sql_text_hash field.  The table
rem contains data for the parses, executions and fetches performed for each
rem statement; the data spans user sessions, since multiple users can execute
rem the same statement.
rem
rem The procedure will prompt for the name of the detail table to create and
rem the name of the collection if the variables dtl_table_name and cllctn
rem have not been defined.  You should create a separate detail table for
rem each collection.
rem
rem You need to create the elapsed function by executing OTRCFUNC.SQL before
rem creating the detail table.

CREATE TABLE &&dtl_table_name
    (sql_text_hash  NUMBER,
     cmd_type       NUMBER,
     parse_count    NUMBER,
     parse_ucpu_min NUMBER,
     parse_ucpu_max NUMBER,
     parse_ucpu_avg NUMBER,
     parse_scpu_min NUMBER,
     parse_scpu_max NUMBER,
     parse_scpu_avg NUMBER,
     exec_count     NUMBER,
     exec_time_min  NUMBER,
     exec_time_max  NUMBER,
     exec_time_avg  NUMBER,
     exec_ucpu_min  NUMBER,
     exec_ucpu_max  NUMBER,
     exec_ucpu_avg  NUMBER,
     exec_scpu_min  NUMBER,
     exec_scpu_max  NUMBER,
     exec_scpu_avg  NUMBER,
     exec_block_gets_min   NUMBER,
     exec_block_gets_max   NUMBER,
     exec_block_gets_avg   NUMBER,
     exec_cons_gets_min    NUMBER,
     exec_cons_gets_max    NUMBER,
     exec_cons_gets_avg    NUMBER,
     exec_reads_min NUMBER,
     exec_reads_max NUMBER,
     exec_reads_avg NUMBER,
     exec_redo_count_min   NUMBER,
     exec_redo_count_max   NUMBER,
     exec_redo_count_avg   NUMBER,
     exec_redo_size_min    NUMBER,
     exec_redo_size_max    NUMBER,
     exec_redo_size_avg    NUMBER,
     exec_sort_mem_min     NUMBER,
     exec_sort_mem_max     NUMBER,
     exec_sort_mem_avg     NUMBER,
     exec_sort_disk_min    NUMBER,
     exec_sort_disk_max    NUMBER,
     exec_sort_disk_avg    NUMBER,
     exec_sort_rows_min    NUMBER,
     exec_sort_rows_max    NUMBER,
     exec_sort_rows_avg    NUMBER,
     exec_row_count NUMBER,
     fetch_count    NUMBER,
     fetch_time_min NUMBER,
     fetch_time_max NUMBER,
     fetch_time_avg NUMBER,
     fetch_ucpu_min NUMBER,
     fetch_ucpu_max NUMBER,
     fetch_ucpu_avg NUMBER,
     fetch_scpu_min NUMBER,
     fetch_scpu_max NUMBER,
     fetch_scpu_avg NUMBER,
     fetch_block_gets_min  NUMBER,
     fetch_block_gets_max  NUMBER,
     fetch_block_gets_avg  NUMBER,
     fetch_cons_gets_min   NUMBER,
     fetch_cons_gets_max   NUMBER,
     fetch_cons_gets_avg   NUMBER,
     fetch_reads_min NUMBER,
     fetch_reads_max NUMBER,
     fetch_reads_avg NUMBER,
     fetch_redo_count_min  NUMBER,
     fetch_redo_count_max  NUMBER,
     fetch_redo_count_avg  NUMBER,
     fetch_redo_size_min   NUMBER,
     fetch_redo_size_max   NUMBER,
     fetch_redo_size_avg   NUMBER,
     fetch_sort_mem_min    NUMBER,
     fetch_sort_mem_max    NUMBER,
     fetch_sort_mem_avg    NUMBER,
     fetch_sort_disk_min   NUMBER,
     fetch_sort_disk_max   NUMBER,
     fetch_sort_disk_avg   NUMBER,
     fetch_sort_rows_min   NUMBER,
     fetch_sort_rows_max   NUMBER,
     fetch_sort_rows_avg   NUMBER,
     fetch_row_count NUMBER);
COMMIT;

INSERT INTO &&dtl_table_name (sql_text_hash, cmd_type)
    SELECT distinct p.sql_text_hash_end, p.oracle_cmd_type_end
    FROM v_192216243_f_5_e_8_7_3 p, epc_collection c
    WHERE c.collection_name = '&&cllctn' and
          c.collection_id = p.collection_number;
COMMIT;

UPDATE &&dtl_table_name d
    SET parse_count =
        (SELECT count(*) FROM v_192216243_f_5_e_8_7_3 p, epc_collection c
         WHERE d.sql_text_hash = p.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = p.collection_number),
        parse_ucpu_min =
        (SELECT min(p.ucpu_end - p.ucpu_start)
         FROM v_192216243_f_5_e_8_7_3 p, epc_collection c
         WHERE d.sql_text_hash = p.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = p.collection_number),
        parse_ucpu_max =
        (SELECT max(p.ucpu_end - p.ucpu_start)
         FROM v_192216243_f_5_e_8_7_3 p, epc_collection c
         WHERE d.sql_text_hash = p.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = p.collection_number),
        parse_ucpu_avg =
        (SELECT avg(p.ucpu_end - p.ucpu_start)
         FROM v_192216243_f_5_e_8_7_3 p, epc_collection c
         WHERE d.sql_text_hash = p.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = p.collection_number),
        parse_scpu_min =
        (SELECT min(p.scpu_end - p.scpu_start)
         FROM v_192216243_f_5_e_8_7_3 p, epc_collection c
         WHERE d.sql_text_hash = p.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = p.collection_number),
        parse_scpu_max =
        (SELECT max(p.scpu_end - p.scpu_start)
         FROM v_192216243_f_5_e_8_7_3 p, epc_collection c
         WHERE d.sql_text_hash = p.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = p.collection_number),
        parse_scpu_avg =
        (SELECT avg(p.scpu_end - p.scpu_start)
         FROM v_192216243_f_5_e_8_7_3 p, epc_collection c
         WHERE d.sql_text_hash = p.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = p.collection_number);
COMMIT;

UPDATE &&dtl_table_name d
    SET exec_count =
        (SELECT count(*) FROM v_192216243_f_5_e_9_7_3 x, epc_collection c
         WHERE d.sql_text_hash = x.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = x.collection_number),
        exec_time_min =
        (SELECT min(elapsed('&&cllctn',
                            x.timestamp_start, x.timestamp_nano_start,
                            x.timestamp_end, x.timestamp_nano_end))
         FROM v_192216243_f_5_e_9_7_3 x, epc_collection c
         WHERE d.sql_text_hash = x.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = x.collection_number),
        exec_time_max =
        (SELECT max(elapsed('&&cllctn',
                            x.timestamp_start, x.timestamp_nano_start,
                            x.timestamp_end, x.timestamp_nano_end))
         FROM v_192216243_f_5_e_9_7_3 x, epc_collection c
         WHERE d.sql_text_hash = x.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = x.collection_number),
        exec_time_avg =
        (SELECT avg(elapsed('&&cllctn',
                            x.timestamp_start, x.timestamp_nano_start,
                            x.timestamp_end, x.timestamp_nano_end))
         FROM v_192216243_f_5_e_9_7_3 x, epc_collection c
         WHERE d.sql_text_hash = x.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = x.collection_number),
        exec_ucpu_min =
        (SELECT min(x.ucpu_end - x.ucpu_start)
         FROM v_192216243_f_5_e_9_7_3 x, epc_collection c
         WHERE d.sql_text_hash = x.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = x.collection_number),
        exec_ucpu_max =
        (SELECT max(x.ucpu_end - x.ucpu_start)
         FROM v_192216243_f_5_e_9_7_3 x, epc_collection c
         WHERE d.sql_text_hash = x.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = x.collection_number),
        exec_ucpu_avg =
        (SELECT avg(x.ucpu_end - x.ucpu_start)
         FROM v_192216243_f_5_e_9_7_3 x, epc_collection c
         WHERE d.sql_text_hash = x.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = x.collection_number),
        exec_scpu_min =
        (SELECT min(x.scpu_end - x.scpu_start)
         FROM v_192216243_f_5_e_9_7_3 x, epc_collection c
         WHERE d.sql_text_hash = x.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = x.collection_number),
        exec_scpu_max =
        (SELECT max(x.scpu_end - x.scpu_start)
         FROM v_192216243_f_5_e_9_7_3 x, epc_collection c
         WHERE d.sql_text_hash = x.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = x.collection_number),
        exec_scpu_avg =
        (SELECT avg(x.scpu_end - x.scpu_start)
         FROM v_192216243_f_5_e_9_7_3 x, epc_collection c
         WHERE d.sql_text_hash = x.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = x.collection_number);
COMMIT;

UPDATE &&dtl_table_name d
    SET exec_block_gets_min =
        (SELECT min(x.db_block_gets_end - x.db_block_gets_start)
         FROM v_192216243_f_5_e_9_7_3 x, epc_collection c
         WHERE d.sql_text_hash = x.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = x.collection_number),
        exec_block_gets_max =
        (SELECT max(x.db_block_gets_end - x.db_block_gets_start)
         FROM v_192216243_f_5_e_9_7_3 x, epc_collection c
         WHERE d.sql_text_hash = x.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = x.collection_number),
        exec_block_gets_avg =
        (SELECT avg(x.db_block_gets_end - x.db_block_gets_start)
         FROM v_192216243_f_5_e_9_7_3 x, epc_collection c
         WHERE d.sql_text_hash = x.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = x.collection_number),
        exec_cons_gets_min =
        (SELECT min(x.consistent_gets_end - x.consistent_gets_start)
         FROM v_192216243_f_5_e_9_7_3 x, epc_collection c
         WHERE d.sql_text_hash = x.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = x.collection_number),
        exec_cons_gets_max =
        (SELECT max(x.consistent_gets_end - x.consistent_gets_start)
         FROM v_192216243_f_5_e_9_7_3 x, epc_collection c
         WHERE d.sql_text_hash = x.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = x.collection_number),
        exec_cons_gets_avg =
        (SELECT avg(x.consistent_gets_end - x.consistent_gets_start)
         FROM v_192216243_f_5_e_9_7_3 x, epc_collection c
         WHERE d.sql_text_hash = x.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = x.collection_number),
        exec_reads_min =
        (SELECT min(x.physical_reads_end - x.physical_reads_start)
         FROM v_192216243_f_5_e_9_7_3 x, epc_collection c
         WHERE d.sql_text_hash = x.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = x.collection_number),
        exec_reads_max =
        (SELECT max(x.physical_reads_end - x.physical_reads_start)
         FROM v_192216243_f_5_e_9_7_3 x, epc_collection c
         WHERE d.sql_text_hash = x.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = x.collection_number),
        exec_reads_avg =
        (SELECT avg(x.physical_reads_end - x.physical_reads_start)
         FROM v_192216243_f_5_e_9_7_3 x, epc_collection c
         WHERE d.sql_text_hash = x.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = x.collection_number);
COMMIT;

UPDATE &&dtl_table_name d
    SET exec_redo_count_min =
        (SELECT min(x.redo_entries_end - x.redo_entries_start)
         FROM v_192216243_f_5_e_9_7_3 x, epc_collection c
         WHERE d.sql_text_hash = x.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = x.collection_number),
        exec_redo_count_max =
        (SELECT max(x.redo_entries_end - x.redo_entries_start)
         FROM v_192216243_f_5_e_9_7_3 x, epc_collection c
         WHERE d.sql_text_hash = x.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = x.collection_number),
        exec_redo_count_avg =
        (SELECT avg(x.redo_entries_end - x.redo_entries_start)
         FROM v_192216243_f_5_e_9_7_3 x, epc_collection c
         WHERE d.sql_text_hash = x.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = x.collection_number),
        exec_redo_size_min =
        (SELECT min(x.redo_size_end - x.redo_size_start)
         FROM v_192216243_f_5_e_9_7_3 x, epc_collection c
         WHERE d.sql_text_hash = x.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = x.collection_number),
        exec_redo_size_max =
        (SELECT max(x.redo_size_end - x.redo_size_start)
         FROM v_192216243_f_5_e_9_7_3 x, epc_collection c
         WHERE d.sql_text_hash = x.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = x.collection_number),
        exec_redo_size_avg =
        (SELECT avg(x.redo_size_end - x.redo_size_start)
         FROM v_192216243_f_5_e_9_7_3 x, epc_collection c
         WHERE d.sql_text_hash = x.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = x.collection_number),
        exec_row_count =
        (SELECT sum(x.row_count_end)
         FROM v_192216243_f_5_e_9_7_3 x, epc_collection c
         WHERE d.sql_text_hash = x.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = x.collection_number);
COMMIT;

UPDATE &&dtl_table_name d
    SET exec_sort_mem_min =
        (SELECT min(x.sort_memory_end - x.sort_memory_start)
         FROM v_192216243_f_5_e_9_7_3 x, epc_collection c
         WHERE d.sql_text_hash = x.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = x.collection_number),
        exec_sort_mem_max =
        (SELECT max(x.sort_memory_end - x.sort_memory_start)
         FROM v_192216243_f_5_e_9_7_3 x, epc_collection c
         WHERE d.sql_text_hash = x.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = x.collection_number),
        exec_sort_mem_avg =
        (SELECT avg(x.sort_memory_end - x.sort_memory_start)
         FROM v_192216243_f_5_e_9_7_3 x, epc_collection c
         WHERE d.sql_text_hash = x.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = x.collection_number),
        exec_sort_disk_min =
        (SELECT min(x.sort_disk_end - x.sort_disk_start)
         FROM v_192216243_f_5_e_9_7_3 x, epc_collection c
         WHERE d.sql_text_hash = x.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = x.collection_number),
        exec_sort_disk_max =
        (SELECT max(x.sort_disk_end - x.sort_disk_start)
         FROM v_192216243_f_5_e_9_7_3 x, epc_collection c
         WHERE d.sql_text_hash = x.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = x.collection_number),
        exec_sort_disk_avg =
        (SELECT avg(x.sort_disk_end - x.sort_disk_start)
         FROM v_192216243_f_5_e_9_7_3 x, epc_collection c
         WHERE d.sql_text_hash = x.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = x.collection_number),
        exec_sort_rows_min =
        (SELECT min(x.sort_rows_end - x.sort_rows_start)
         FROM v_192216243_f_5_e_9_7_3 x, epc_collection c
         WHERE d.sql_text_hash = x.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = x.collection_number),
        exec_sort_rows_max =
        (SELECT max(x.sort_rows_end - x.sort_rows_start)
         FROM v_192216243_f_5_e_9_7_3 x, epc_collection c
         WHERE d.sql_text_hash = x.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = x.collection_number),
        exec_sort_rows_avg =
        (SELECT avg(x.sort_rows_end - x.sort_rows_start)
         FROM v_192216243_f_5_e_9_7_3 x, epc_collection c
         WHERE d.sql_text_hash = x.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = x.collection_number);
COMMIT;

UPDATE &&dtl_table_name d
    SET fetch_count =
        (SELECT count(*) FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
         WHERE d.sql_text_hash = f.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = f.collection_number),
        fetch_time_min =
        (SELECT min(elapsed('&&cllctn',
                            f.timestamp_start, f.timestamp_nano_start,
                            f.timestamp_end, f.timestamp_nano_end))
         FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
         WHERE d.sql_text_hash = f.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = f.collection_number),
        fetch_time_max =
        (SELECT max(elapsed('&&cllctn',
                            f.timestamp_start, f.timestamp_nano_start,
                            f.timestamp_end, f.timestamp_nano_end))
         FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
         WHERE d.sql_text_hash = f.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = f.collection_number),
        fetch_time_avg =
        (SELECT avg(elapsed('&&cllctn',
                            f.timestamp_start, f.timestamp_nano_start,
                            f.timestamp_end, f.timestamp_nano_end))
         FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
         WHERE d.sql_text_hash = f.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = f.collection_number),
        fetch_ucpu_min =
        (SELECT min(f.ucpu_end - f.ucpu_start)
         FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
         WHERE d.sql_text_hash = f.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = f.collection_number),
        fetch_ucpu_max =
        (SELECT max(f.ucpu_end - f.ucpu_start)
         FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
         WHERE d.sql_text_hash = f.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = f.collection_number),
        fetch_ucpu_avg =
        (SELECT avg(f.ucpu_end - f.ucpu_start)
         FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
         WHERE d.sql_text_hash = f.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = f.collection_number),
        fetch_scpu_min =
        (SELECT min(f.scpu_end - f.scpu_start)
         FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
         WHERE d.sql_text_hash = f.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = f.collection_number),
        fetch_scpu_max =
        (SELECT max(f.scpu_end - f.scpu_start)
         FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
         WHERE d.sql_text_hash = f.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = f.collection_number),
        fetch_scpu_avg =
        (SELECT avg(f.scpu_end - f.scpu_start)
         FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
         WHERE d.sql_text_hash = f.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = f.collection_number);
COMMIT;

UPDATE &&dtl_table_name d
    SET fetch_block_gets_min =
        (SELECT min(f.db_block_gets_end - f.db_block_gets_start)
         FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
         WHERE d.sql_text_hash = f.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = f.collection_number),
        fetch_block_gets_max =
        (SELECT max(f.db_block_gets_end - f.db_block_gets_start)
         FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
         WHERE d.sql_text_hash = f.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = f.collection_number),
        fetch_block_gets_avg =
        (SELECT avg(f.db_block_gets_end - f.db_block_gets_start)
         FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
         WHERE d.sql_text_hash = f.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = f.collection_number),
        fetch_cons_gets_min =
        (SELECT min(f.consistent_gets_end - f.consistent_gets_start)
         FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
         WHERE d.sql_text_hash = f.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = f.collection_number),
        fetch_cons_gets_max =
        (SELECT max(f.consistent_gets_end - f.consistent_gets_start)
         FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
         WHERE d.sql_text_hash = f.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = f.collection_number),
        fetch_cons_gets_avg =
        (SELECT avg(f.consistent_gets_end - f.consistent_gets_start)
         FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
         WHERE d.sql_text_hash = f.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = f.collection_number),
        fetch_reads_min =
        (SELECT min(f.physical_reads_end - f.physical_reads_start)
         FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
         WHERE d.sql_text_hash = f.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = f.collection_number),
        fetch_reads_max =
        (SELECT max(f.physical_reads_end - f.physical_reads_start)
         FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
         WHERE d.sql_text_hash = f.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = f.collection_number),
        fetch_reads_avg =
        (SELECT avg(f.physical_reads_end - f.physical_reads_start)
         FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
         WHERE d.sql_text_hash = f.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = f.collection_number);
COMMIT;

UPDATE &&dtl_table_name d
    SET fetch_redo_count_min =
        (SELECT min(f.redo_entries_end - f.redo_entries_start)
         FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
         WHERE d.sql_text_hash = f.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = f.collection_number),
        fetch_redo_count_max =
        (SELECT max(f.redo_entries_end - f.redo_entries_start)
         FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
         WHERE d.sql_text_hash = f.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = f.collection_number),
        fetch_redo_count_avg =
        (SELECT avg(f.redo_entries_end - f.redo_entries_start)
         FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
         WHERE d.sql_text_hash = f.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = f.collection_number),
        fetch_redo_size_min =
        (SELECT min(f.redo_size_end - f.redo_size_start)
         FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
         WHERE d.sql_text_hash = f.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = f.collection_number),
        fetch_redo_size_max =
        (SELECT max(f.redo_size_end - f.redo_size_start)
         FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
         WHERE d.sql_text_hash = f.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = f.collection_number),
        fetch_redo_size_avg =
        (SELECT avg(f.redo_size_end - f.redo_size_start)
         FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
         WHERE d.sql_text_hash = f.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = f.collection_number),
        fetch_row_count =
        (SELECT sum(f.row_count_end)
         FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
         WHERE d.sql_text_hash = f.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = f.collection_number);
COMMIT;

UPDATE &&dtl_table_name d
    SET fetch_sort_mem_min =
        (SELECT min(f.sort_memory_end - f.sort_memory_start)
         FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
         WHERE d.sql_text_hash = f.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = f.collection_number),
        fetch_sort_mem_max =
        (SELECT max(f.sort_memory_end - f.sort_memory_start)
         FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
         WHERE d.sql_text_hash = f.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = f.collection_number),
        fetch_sort_mem_avg =
        (SELECT avg(f.sort_memory_end - f.sort_memory_start)
         FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
         WHERE d.sql_text_hash = f.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = f.collection_number),
        fetch_sort_disk_min =
        (SELECT min(f.sort_disk_end - f.sort_disk_start)
         FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
         WHERE d.sql_text_hash = f.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = f.collection_number),
        fetch_sort_disk_max =
        (SELECT max(f.sort_disk_end - f.sort_disk_start)
         FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
         WHERE d.sql_text_hash = f.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = f.collection_number),
        fetch_sort_disk_avg =
        (SELECT avg(f.sort_disk_end - f.sort_disk_start)
         FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
         WHERE d.sql_text_hash = f.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = f.collection_number),
        fetch_sort_rows_min =
        (SELECT min(f.sort_rows_end - f.sort_rows_start)
         FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
         WHERE d.sql_text_hash = f.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = f.collection_number),
        fetch_sort_rows_max =
        (SELECT max(f.sort_rows_end - f.sort_rows_start)
         FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
         WHERE d.sql_text_hash = f.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = f.collection_number),
        fetch_sort_rows_avg =
        (SELECT avg(f.sort_rows_end - f.sort_rows_start)
         FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
         WHERE d.sql_text_hash = f.sql_text_hash_end AND
               c.collection_name = '&&cllctn' AND
               c.collection_id = f.collection_number);
COMMIT;