Mittwoch, 30. November 2011

Page 4 nothing fancy...

Nothing really new will happen on Page 4, but you have to run this code as user MAINTENANCE_DB

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 ,
    data.total_time ,
    MAX(data.total_time) OVER (PARTITION BY data.runid)       AS max_total_time ,
    MAX(data.total_time) OVER (PARTITION BY data.unit_number) AS max_total_time_unit
  FROM plsql_profiler_units units
  JOIN plsql_profiler_runs runs
  ON units.runid = runs.runid
  JOIN
    (SELECT runid,
      unit_number,
      SUM(total_time) AS total_time
    FROM plsql_profiler_data
    GROUP BY runid,
      unit_number
    ) data
  ON units.runid        = data.runid
  AND units.unit_number = data.unit_number;
  
GRANT SELECT ON plsql_profiler_units_vw TO maintenance;  

CREATE SYNONYM maintenance.plsql_profiler_units_vw FOR maintenance_db.plsql_profiler_units_vw;

to get the needed stored objects in place.

Source

The result will look like this (There is no link to data yet)



Keine Kommentare:

Kommentar veröffentlichen