Mittwoch, 7. Dezember 2011

Digging into the source...

Ok last page of this cycle, nothing really spectacular! Done that before, will do that again.


The only thing I want to mention is that you have to adjust the output of your sourcecode to keep the indentation. I started with <pre> and <code> tags, but that had a bad influence on the hights of the record.
Finally this approach

...
,''
      ||regexp_replace(rtrim(text),'\s','&nbsp;')
      ||'' AS text
...

Source

fulfilled my needs. Next I will create a page with the final version. So you don't have to follow all posts to get something running.

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.

Add some source...

Now I want to add some source to my profiler data. It is easy to find the necessary information for stored code. So I first add the missing lines to plsql_profiler_data.

...
  MERGE INTO plsql_profiler_data d USING
  (
    SELECT
      runid
    , unit_number
    , line AS line#
    FROM
      plsql_profiler_units
    JOIN dba_source
    ON
      unit_owner  = owner
    AND unit_name = name
    AND unit_type = TYPE
    WHERE
      (
        runid, unit_number
      )
      IN
      (
        SELECT
          runid
        , unit_number
        FROM
          plsql_profiler_units
        WHERE
          runid = runid_in
      )
  )
  s ON
  (
    d.runid = s.runid AND d.unit_number = s.unit_number AND d.line# = s.line#
  )
WHEN NOT MATCHED THEN
  INSERT
    (
      d.runid
    ,d.unit_number
    ,d.line#
    ,d.total_occur
    ,d.total_time
    ,d.min_time
    ,d.max_time
    )
    VALUES
    (
      s.runid
    ,s.unit_number
    ,s.line#
    ,0
    ,0
    ,0
    ,0
    );
...

and then I create the necessary information in the related table plsql_profiler_data_source

...
  MERGE INTO plsql_profiler_data_source d USING
  (
    SELECT
      runid
    ,unit_number
    ,line AS line#
    ,text
    FROM
      plsql_profiler_units
    JOIN dba_source
    ON
      unit_owner  = owner
    AND unit_name = name
    AND unit_type = TYPE
    WHERE
      (
        runid, unit_number
      )
      IN
      (
        SELECT
          runid
        , unit_number
        FROM
          plsql_profiler_units
        WHERE
          runid = runid_in
      )
  )
  s ON
  (
    d.runid = s.runid AND d.unit_number = s.unit_number AND d.line# = s.line#
  )
WHEN NOT MATCHED THEN
  INSERT
    (
      d.runid
    ,d.unit_number
    ,d.line#
    ,d.text
    )
    VALUES
    (
      s.runid
    ,s.unit_number
    ,s.line#
    ,s.text
    );
...


Source

to be continued...

The basic package...

 First I restrict the profiler from being started mor then once. I define a package private variable to keep the actual runid an a variable to keep the number of start_profiler_calls.
  g_run_number BINARY_INTEGER;
  g_profiler_counter binary_integer := 0;

I only want to run profiler so I will prevent to have more then one instance. I could have paused and resumed the profiler as well, but I  don't like that. I will increase the number of profiler calls every time start_profiler is called and save the runid only if the number of calls is 1

...
  l_run_number BINARY_INTEGER;
BEGIN
  IF increase_profiler_counter = 1 THEN
    start_profiler(run_comment_in, l_run_number);
    set_run_number(l_run_number);
  END IF;
...

and decrease it every time stop_profiler is called. I only stop the profiler if the number of calls is 0

...
IF reduce_profiler_counter = 0 THEN
    dbms_profiler.stop_profiler;
...

Rolling up the runs data will aggregate the total_time values on unit and run level. Unfortunatly the provided dbms_profiler procedure will only work on unit level, so I have to rollup the data on runs level myself. And yes I use autonomous transactions.

...
  pragma autonomous_transaction;
BEGIN
  dbms_profiler.rollup_run(runid_in);
  UPDATE
    plsql_profiler_runs
  SET
    run_total_time =
    (
      SELECT
        SUM(total_time)
      FROM
        plsql_profiler_units
      WHERE
        runid = runid_in
    )
  WHERE
    runid = runid_in;
  COMMIT;
...

Source

We can now simplify the units view.

CREATE OR REPLACE FORCE VIEW plsql_profiler_units_vw( runid 
                                                     ,unit_number 
                                                     ,unit_type 
                                                     ,unit_name 
                                                     ,unit_timestamp 
                                                     ,total_time 
                                                     ,max_total_time 
                                                     ,max_total_time_unit)
AS
  SELECT units.runid ,
    units.unit_number ,
    unit_type ,
    unit_owner
    || '.'
    || unit_name AS unit_name ,
    TO_TIMESTAMP( TO_CHAR(
    CASE
      WHEN unit_type = 'ANONYMOUS BLOCK'
      THEN runs.run_date
      ELSE unit_timestamp
    END ,'DD.MM.YYYY HH24:MI:SS') ,'DD.MM.YYYY HH24:MI:SS') AS unit_timestamp ,
    total_time ,
    MAX(units.total_time) OVER (PARTITION BY units.runid)       AS max_total_time ,
    MAX(units.total_time) OVER (PARTITION BY units.unit_number) AS max_total_time_unit
  FROM plsql_profiler_units units
  JOIN plsql_profiler_runs runs
  ON units.runid = runs.runid;

Source

to be continued...