tracksqltime.sql

This PL/SQL block identifies recently active SQL queries from a specific application server and provides details on their performance and execution plans. 

Here is a breakdown of what the script does:

• Filters Activity: It scans the Active Session History (gv$active_session_history) to find distinct SQL IDs triggered by machines containing “APP02” in their name within the last hour.
• Retrieves SQL Text: For each identified SQL ID, it attempts to fetch the actual SQL statement from the library cache (gv$sql). If the text is no longer in memory, it labels it as “NO SQL TEXT.”
• Analyzes Execution Plans: It looks for all unique Plan Hash Values associated with that SQL ID, searching both real-time memory (gv$sql_plan) and historical records (dba_hist_sql_plan).
• Calculates Performance: For every execution plan found, it calculates the Average Elapsed Time (in seconds) by checking current statistics and historical snapshots (dba_hist_sqlstat).
• Displays Results: It prints a formatted report to the console showing the SQL ID, the machine name, the SQL text, and a list of all known execution plans with their respective average timings. 

set lines 120 pages 200;
SET SERVEROUTPUT ON;

DECLARE
  header  varchar2(300);
  tmp_sqltext  varchar2(1000);
  CURSOR c1 IS
    SELECT distinct  SQL_ID, machine, program
    FROM gv$active_session_history
    WHERE upper(machine) LIKE upper('%XAAPP02%')
      AND sample_time > SYSDATE - 1/24
      AND sql_id IS NOT NULL ;
BEGIN
--  header := lpad('SQL_ID', 15) || lpad('PHV', 15) || lpad('Avg ET', 14) || lpad('Machine', 30);
--  DBMS_OUTPUT.PUT_LINE(header);
  FOR cur1 IN c1 LOOP
    begin
    select sql_text into tmp_sqltext from gv$sql where sql_id = cur1.sql_id and rownum = 1;
    exception
       when no_data_found then
            tmp_sqltext := 'NO SQL TEXT in V$SQL';
    end;
    dbms_output.put_line(lpad('------------------', 25));
    DBMS_OUTPUT.PUT_LINE(lpad(cur1.sql_id,15) || lpad(cur1.machine, 30) || ' ' || chr(10) || tmp_sqltext);
    begin
    -- Nested loop to handle 1-to-many relationship between SQL_ID and Plans
    FOR plan_cur IN (
        WITH
        p AS (
            SELECT plan_hash_value FROM gv$sql_plan
             WHERE sql_id = cur1.sql_id AND other_xml IS NOT NULL
             UNION
            SELECT plan_hash_value FROM dba_hist_sql_plan
             WHERE sql_id = cur1.sql_id AND other_xml IS NOT NULL
        ),
        m AS (
            SELECT plan_hash_value,
                   SUM(elapsed_time)/SUM(executions) avg_et_secs
              FROM gv$sql
             WHERE sql_id = cur1.sql_id AND executions > 0
             GROUP BY plan_hash_value
        ),
        a AS (
            SELECT plan_hash_value,
                   SUM(elapsed_time_total)/SUM(executions_total) avg_et_secs
              FROM dba_hist_sqlstat
             WHERE sql_id = cur1.sql_id AND executions_total > 0
             GROUP BY plan_hash_value
        )
        SELECT p.plan_hash_value,
               ROUND(NVL(m.avg_et_secs, a.avg_et_secs)/1e6, 3) AS avg_et_secs
          FROM p
          LEFT JOIN m ON p.plan_hash_value = m.plan_hash_value
          LEFT JOIN a ON p.plan_hash_value = a.plan_hash_value
    ) LOOP
      -- This prints for every plan found for the current SQL_ID
      DBMS_OUTPUT.PUT_LINE( lpad(plan_cur.plan_hash_value,15) || ' ' || lpad(plan_cur.avg_et_secs,10));
    END LOOP;
    exception
        when no_data_found  then
             DBMS_OUTPUT.PUT_LINE( 'NO PLAN INFO AVAILABLE');

    end;
  END LOOP;
END;
/