Montag, 5. Dezember 2011

Howto handle Anonymous Blocks...

Stolen with pride. Thank you Frank for the basic idea.
The source of anonymous blocks cannot be found in the dba_source views.
I have to extract the source from v$sqltext. But first I have to parse the callstack to find the corresponding child_address. It would be really nich if Oracle could deliver the callstck as collection of record!

...
  DECLARE
    l_unit_number plsql_profiler_units.unit_number%TYPE;
  BEGIN
    SELECT
      unit_number
    INTO
      l_unit_number
    FROM
      plsql_profiler_units_vw
    WHERE
      runid       = runid_in
    AND unit_type = 'ANONYMOUS BLOCK';
    DECLARE
      l_call_stack    VARCHAR2(4000);
      l_child_address VARCHAR2(16);
      l_sqltext CLOB;
    BEGIN
      l_call_stack := TRIM(dbms_utility.format_call_stack);
      l_call_stack := SUBSTR(l_call_stack, INSTR(l_call_stack, CHR(10), -2) + 1
      );
      l_child_address := LPAD(TRIM(SUBSTR(l_call_stack, 0, INSTR(l_call_stack,
      ' '))), 16, '0');
      FOR l_cur IN
      (
        SELECT
          sql_text
        FROM
          v$sqltext_with_newlines
        WHERE
          sql_id IN
          (
            SELECT
              sql_id
            FROM
              v$sql
            WHERE
              LPAD(RAWTOHEX(child_address), 16, '0') = l_child_address
          )
        ORDER BY
          piece
      )
      LOOP
        l_sqltext := l_sqltext || l_cur.sql_text;
      END LOOP;
      IF l_sqltext IS NOT NULL THEN
        DECLARE
          l_line VARCHAR2(4000);
          l_instr BINARY_INTEGER;
          l_line# BINARY_INTEGER := 0;
        BEGIN
          LOOP
            l_instr := INSTR(l_sqltext, CHR(10));
            CASE
            WHEN l_instr != 0 THEN
              l_line     := SUBSTR(l_sqltext, 0, l_instr - 1);
              l_sqltext  := SUBSTR(l_sqltext, l_instr    + 1);
            ELSE
              l_line := l_sqltext;
            END CASE;
            l_line# := l_line# + 1;
            INSERT
            INTO
              plsql_profiler_data_source
              (
                runid
              ,unit_number
              ,line#
              ,text
              )
              VALUES
              (
                runid_in
              ,l_unit_number
              ,l_line#
              ,l_line
              );
            EXIT
          WHEN l_instr = 0;
          END LOOP;
        END;
      END IF;
    END;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL;
  END;
...

Source

I create a final view to display the profiler data

CREATE OR REPLACE FORCE VIEW plsql_profiler_data_vw(
  runid
 ,unit_number
 ,line#
 ,total_occur
 ,total_time
 ,avg_time
 ,min_time
 ,max_time
 ,text)
AS
  SELECT dat.runid
        ,dat.unit_number
        ,dat.line#
        ,dat.total_occur
        ,dat.total_time
        ,ROUND(CASE WHEN dat.total_occur != 0 THEN dat.total_time / dat.total_occur ELSE dat.total_time END, 2)
           AS avg_time
        ,dat.min_time
        ,dat.max_time
        ,src.text
  FROM     plsql_profiler_data dat
         LEFT OUTER JOIN
           plsql_profiler_data_source src
         ON (dat.runid = src.runid AND dat.unit_number = src.unit_number AND dat.line# = src.line#);

Source

So our data looks like this now


The next task will be to present this information in APEX.

Keine Kommentare:

Kommentar veröffentlichen