Samstag, 3. Dezember 2011

A new table is born...

I could have updated the profiler data directly (plsql_profiler_data) using one of the unused columns. Anyhow, I decided to create my structure and relate it to the profiler data. I will create new records in the table  plsql_profiler_data if needed.

CREATE TABLE plsql_profiler_data_source
(
  runid         NUMBER NOT NULL
 ,unit_number   NUMBER NOT NULL
 ,line#         NUMBER NOT NULL
 ,text          VARCHAR2(4000 BYTE)
);

ALTER TABLE plsql_profiler_data_source ADD (
  PRIMARY KEY
  (runid, unit_number, line#)
  USING INDEX);

Source

I have to adjust the instead of trigger on plsql_profiler_runs_vw to avoid errors.

CREATE OR REPLACE TRIGGER plsql_profiler_runs_vw_io_trg INSTEAD OF
  DELETE OR
  update on plsql_profiler_runs_vw referencing new as new old as old for each row declare begin if deleting then
  DELETE FROM plsql_profiler_data_source WHERE runid = :old.runid;
  DELETE FROM plsql_profiler_data WHERE runid = :old.runid;
  DELETE FROM plsql_profiler_units WHERE runid = :old.runid;
  DELETE FROM plsql_profiler_runs WHERE runid = :old.runid;
ELSIF UPDATING THEN
  UPDATE plsql_profiler_runs
  SET run_comment = :new.run_comment
  WHERE runid     = :new.runid;
END IF;
END plsql_profiler_runs_vw_io_trg;
/

Source

to be continued...

Enhancing the profiler data...

The next step would be to create a page based on the profiler data. But looking on the retrieved data shows that it is not very usefull in the moment.


From my point of view it is not enough to know that line# 4 in unit 2 has been executed 101 times in a total time of 4412 nano seconds. I need to know the source code to find out that the code should be only executed once and that the excution should not need so much time at all.

In general there are two possible ways to combine source code and profiler data. I could fetch the data on the fly when needed, or retrieve it during the profiler run and store it in the database for later usage. The drawback of the first approach is, that you simply display invalid information for historical data whenever the code changed. So I will definatly go for the scond approach. I will place the needed logic in the MAINTENANCE_DB schema and prevent the direct usage of the dbms_profiler package.

I revoke some privileges from the user HR (run as user MAINTENANCE_DB)

REVOKE ALL ON sys.dbms_profiler FROM hr;
REVOKE ALL ON plsql_profiler_data FROM hr;
REVOKE ALL ON plsql_profiler_units FROM hr;
REVOKE ALL ON plsql_profiler_data FROM hr;
REVOKE SELECT ON plsql_profiler_runnumber FROM hr;


to prevent direct access to the dbms_profiler package. Please drop the private synonyms as well.

I create a new package in the MAINTENANCE_DB schema to be able to start profiling again as user HR.

create or replace
PACKAGE profiler
AS
  --wrap dbms_profiler.start_profiler
PROCEDURE start_profiler(
    run_comment_in IN VARCHAR2 := TO_CHAR(
      sysdate,
      'DD.MM.YYYY HH24:MI:SS'));
  --wrap dbms_profiler.stop_profiler
PROCEDURE stop_profiler;
END profiler;
/

CREATE OR REPLACE
PACKAGE BODY PROFILER
AS
PROCEDURE start_profiler(
    run_comment_in IN VARCHAR2 := TO_CHAR(
      sysdate,
      'DD.MM.YYYY HH24:MI:SS'))
AS
BEGIN
  dbms_profiler.start_profiler( run_comment_in);
END start_profiler;
PROCEDURE stop_profiler
AS
BEGIN
  dbms_profiler.stop_profiler;
END stop_profiler;
END PROFILER;

GRANT EXECUTE ON profiler to hr;

CREATE OR REPLACE SYNONYM hr.profiler FOR profiler;

Source

I test the changes as user HR


begin
  profiler.start_profiler;
  test;
  profiler.stop_profiler;
end;
/

and unfortunatly it works.

to be continued...

Freitag, 2. Dezember 2011

There was this strange error...

when I tried to delete all runs at once using the header checkbox.

Thanks to google I discovered that I had to remove the name from my checkbox column header.

<input type="Checkbox" id="runidAll" value="All" />

an it still works!

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)



Making the Gauge sortable...

I had a quick look during the development and discovered that the gauge was not sorted properly. In fact it sorting turned upside down. So I added a value to my tag deifintion on Page 3 and 4 in order to fix it.

Example from

...,'<div style="width:100px...</code></pre>

to

...,'<div value='||total_time||' style="width:100px...</code></pre>

Dienstag, 29. November 2011

Toggle the damn Button with Dynamic Actions...

I was just done with the javaScript stuff when I ran into a blog showing how to trigger a Dynamic Action the jQuery style.So I decided to switch from the good old pure javaScript to Dynamic Actions.

I soon (after some hours, jQuery experience even less then Novice) found out that the jQuery Selector could be like this:

input[id^=runid] I think this will mean something like "fire on the input element with id starting with runid"

So I change my query a little bit...

SELECT apex_item.checkbox2(1
                          ,runid
                          ,p_item_id => 'runid'||runid) as 
       chk
      ,...

and the column header as well!

<input type="Checkbox" name="runidAll" id="runidAll" value="All"/>

And believe me or not I find the wanted tags when I check the result with firebug.

I remove everything from the Page/JavaScript/Function an Global Variable Declaration section except:

var htmldb_delete_message='"DELETE_CONFIRM_MSG"';

and I keep the line:


$('#P3_DELETE_BUTTON').hide();

in the Page/JavaScript/Execute when Page Loads section. I could hide the button initialy by running the false action on startup, but I don't want to hide the button once for every displayed record.

Next I create an advanced Dynamic Action.

  • Name => Checkbox checked
  • Event => Change
  • Selection Type => jQuery Selector
  • jQuery Selector => input[id^=runid]
  • Condition => JavaScript expression
  • Value => this.triggeringElement.checked

The Action fires when a Checkbox is changed and returns true if the checkbox is checked.
I create a True Action connected to the Dynamic Action

if (this.triggeringElement.value=="All") $f_CheckFirstColumn(this.triggeringElement); 
$('#P3_DELETE_BUTTON').show();

and a False Action as well

if (this.triggeringElement.value=="All") {
  $f_CheckFirstColumn(this.triggeringElement);
}else{
  $("#runidAll")[0].checked = false;
}

var checkboxes = document.getElementsByName(this.triggeringElement.name);
var boxes = checkboxes.length;
for (i = 0; i < boxes; i++) {
  if (checkboxes[i].checked) return;
}
$('#P3_DELETE_BUTTON').hide();

Both Actions execute JavaScript Code and the Fire On Page Load flag is set to no!

This solution works as designed and looks a little leaner then the pure JavaScript Solution.

Source (forget about Page 4 for now)

Extend the basic setup....


There was already some need to place logic into the database (view, instead of trigger). To get a better grip on the governance I decided to split my application into two database schemas. The already existing apex (MAINTENANCE in my case) schema will keep the representation layer. I will create a second schema MAINTENANCE_DB to manage the data and the logic. That may look a little bit oversized for this little application, but I'm also trying to find a good structure to build a "real" application with APEX. The new setup will not allow the Developer to create a GUI directly on the table. It will also prevent the frontend from deleting UNITS or DATA directly.

Run as SYS:

-- USER maintenance_db SQL
CREATE USER maintenance_db IDENTIFIED BY apex
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";

-- ROLES
GRANT "RESOURCE" TO maintenance_db ;
GRANT "CONNECT" TO maintenance_db ;


-- SYSTEM PRIVILEGES
GRANT CREATE ANY SYNONYM TO maintenance_db ;
GRANT SELECT ANY DICTIONARY TO maintenance_db ;
GRANT CREATE TRIGGER TO "MAINTENANCE_DB" ;
GRANT CREATE TABLE TO "MAINTENANCE_DB" ;
GRANT CREATE VIEW TO "MAINTENANCE_DB" ;

--Other Privileges
GRANT EXECUTE ON sys.dbms_profiler TO maintenance_db WITH GRANT OPTION;

-- QUOTAS
ALTER USER maintenance_db QUOTA UNLIMITED ON USERS;
--Remove Privileges form user MAINTENANCE

-- SYSTEM PRIVILEGES
REVOKE CREATE ANY SYNONYM FROM "MAINTENANCE";
REVOKE SELECT ANY DICTIONARY FROM "MAINTENANCE";

--Other Privileges
REVOKE EXECUTE ON sys.dbms_profiler FROM "MAINTENANCE";


Source

Remove the database objects from the APEX Workspace Schema
Run as APEXUSER (MAINTENANCE):

--remove database objects
drop table plsql_profiler_data;
drop table plsql_profiler_units;
drop table plsql_profiler_runs;
drop sequence PLSQL_PROFILER_RUNNUMBER;
DROP VIEW PLSQL_PROFILER_RUNS_VW;

Source

Run the script proftab.sql as user MAINTENANCE_DB to create the profiler tables and sequence.

Create the view and instead of trigger as user MAINTENANCE_DB.

CREATE OR REPLACE FORCE VIEW plsql_profiler_runs_vw
AS
  (SELECT r.runid ,
    r.related_run ,
    r.run_owner ,
    TO_TIMESTAMP(TO_CHAR(r.run_date, 'DD.MM.YYYY HH24:MI:SS'), 'DD.MM.YYYY HH24:MI:SS') AS run_date ,
    r.run_comment ,
    r.run_total_time ,
    r.run_comment1,
    (SELECT MAX(run_total_time) FROM plsql_profiler_runs
    ) AS max_run_total_time
  FROM plsql_profiler_runs r
  )

Source

CREATE OR REPLACE TRIGGER plsql_profiler_runs_vw_io_trg INSTEAD OF
  DELETE OR
  UPDATE ON plsql_profiler_runs_vw REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE BEGIN IF DELETING THEN
  DELETE FROM plsql_profiler_data WHERE runid = :old.runid;
  DELETE FROM plsql_profiler_units WHERE runid = :old.runid;
  DELETE FROM plsql_profiler_runs WHERE runid = :old.runid;
ELSIF UPDATING THEN
  UPDATE plsql_profiler_runs
  SET run_comment = :new.run_comment
  WHERE runid     = :new.runid;
END IF;
END plsql_profiler_runs_vw_io_trg;
/

Source

Grant the necessary privileges to the APEX schema and to HR.

Run as MAINTENANCE_DB.

--Grant Privileges to user HR
GRANT INSERT,UPDATE,SELECT ON plsql_profiler_runs TO hr;
GRANT INSERT,UPDATE,SELECT ON plsql_profiler_units TO hr;
GRANT INSERT,UPDATE,SELECT ON plsql_profiler_data TO hr;
GRANT SELECT ON plsql_profiler_runnumber TO hr;

GRANT EXECUTE ON sys.dbms_profiler TO hr;

--Adjust Synonyms
CREATE OR REPLACE SYNONYM hr.plsql_profiler_runnumber FOR maintenance_db.plsql_profiler_runnumber;
CREATE OR REPLACE SYNONYM hr.plsql_profiler_runs FOR maintenance_db.plsql_profiler_runs;
CREATE OR REPLACE SYNONYM hr.plsql_profiler_units FOR maintenance_db.plsql_profiler_units;
CREATE OR REPLACE SYNONYM hr.plsql_profiler_data FOR maintenance_db.plsql_profiler_data;

--Grant privileges to APEX User
GRANT SELECT,INSERT,UPDATE,DELETE ON plsql_profiler_runs_vw TO maintenance;

--Create synonym
CREATE OR REPLACE SYNONYM maintenance.plsql_profiler_runs_vw FOR maintenance_db.plsql_profiler_runs_vw;

Source

You should be able to use the application (except Page 4) without any changes.
It should be also still possible for the schema HR to execute the dbms_profiler package.