Category: SQL

March 31st, 2020 by Mathias

Yes, I admit it. I am sometimes not at all as structured as I would like to think. Recently I had worked on a query that had taken me many hours to get just right. When I was done I celebrated with a coffee break (I’m a Swede after all) and after it I started working on other pressing issues.

A couple of weeks later that query is desperately needed as the same test needs be be done again and the setup has to be  done all over. Now where is that script? Surely I saved it to a file. Nope, it is nowhere to be found. But didn’t I send it to a colleague to celebrate making it work, I had thought of it as I knew it would be of interest? No, no trace of it in email either. This other person I tend to discuss things with over Slack, it must be there. No, Slack has no recollection of any such message.

What on earth? Where is it? That sinking feeling is starting to get hold of me, you git you never saved it and now you will have to start from scratch. I was violently against this, not only would it be boring and accepting defeat it would also be HARD. It took a lot of fiddling to make it work the first time, it would be faster now but did I mention it would be BORING?

Surely it is in the history for SQL Developer_ No, it must have been pruned. I guess I write way too many SQLs I guess.

Now, where else could it be? Wait since I ran it and I know which database I could maybe find it in V$SQL? Seriously? It was over two weeks since I ran it. But I just had to check, nope, of course not. It would have been too weird in a database that has a fair use every day.

But this got me thinking. Could this be yet one more use for ASH/AWR? It reports SQL after all. After some mining for where SQL is saved in it turns out dba_hist_sqltext has SQL AWR found of interest during a snapshot.

With that I could find my SQL with this as I knew the created table included the letter PNR, which is short for “personnummer” – the Swedish equivalent of SSN in the US.

select * from dba_hist_sqltext where sql_text like '%pnr%';

So there it was, all the glorious variants I had been testing. Victory was snatched from the jaws of defeat!

This is of course not a good practice for how to look up scripts as scripts that has little impact will not be saved and this will at some point be trimmed also. But if you have forgotten to save it may be worth knowing that as a last resort it could be in the AWR history.

NOTE: Access to this table requires EM Diagnostics Pack to be licensed. If you have not it flags it as a used feature and risks having to license it in the future.

Posted in SQL

March 22nd, 2020 by Mathias

This is a short update to the presumed bug I wrote about recently in this post.

After a few back and forth with Oracle support it turns out this is a well-known (to them) effect of using dbms_random in SQL.

The base for this seems to be a bug report in 10g that was declared “not a bug”. You can read the bug report on MOS. It is also covered in this doc that references the same bug.

It turns out that a function can be called many times during the evaluation. This can of course cause all kinds of havoc to the expected result of the SQL when the function produces a non deterministic result.

You can prove it to yourself by writing a wrapper for dbms_random.value.

As a user I still feel it seems it should not produce results like this, while I understand and appreciate that on a purely technical level it is an effect of how the query is executed. It is however deemed to be “working as designed”.

The linked document shows a test case to prove how it works. It is surprising that this is the result, but it is good to be aware of this and be careful with dbms_random when using it in SQL. The danger is that the effect may change with small changes in optimization or with new versions of the database.

You are probably better off to avoid it in SQL when there are alternatives. When there is not, make sure to put such SQL on the list of code to test with every patch and new version.

Posted in Bug, SQL

March 3rd, 2020 by Mathias

Sometimes the results of a SQL is not at all what you expected. Most of the time it is a simple error you’ve made. Very rarely is it an issue that after hours of looking at it still looks like SQL does something wrong, But it still happens.

This post is about one such situation that occurred las week. If you read the last blog post, you’ll recognize some of this. It turned up when I looked at transforming that SQL to create the many million rows I needed.

with ds as (
 select level rad
   from dual
connect by level < 5
)
, a as (
select trunc(dbms_random.value(1900, 2020)) bs
  from dual
union all
select 2025 from dual
)
, b as
(
select a.bs, substr(a.bs, ds.rad, 1) dgt
  from a, ds
)
select b.bs, b.dgt
  from b
 where b.dgt >= 0
 order by b.bs
;

When I execute this it should get me eight rows, Four with a number somewhere between 1900 and 2020 and four with 2025. Those four then also has each digit in those number broken out as the second value in the result table.

An example would be like this:

1989 9
1989 9
1989 8
1989 1
2025 2
2025 5
2025 2
2025 0

The first four are 1989 with the digits 1,9,8,9 broken out and the second is 2025 with the digits 2,0,2,5 broken out.

Now the problem is that as written the SQL will return results that should not be possible. An example of the result it returns is.

1931 1
1989 8
2004 0
2008 8
2025 2
2025 5
2025 2
2025 0

Note the first four rows, for some reason it turned them into four different years. It should not be possible.

What happens seems to be a query transformation gone wrong. It does things that it should not when the execution of a SQL is not deterministic (due to the use of dbms_random).

The thing that triggers it to transform seems the be the very last condition in the SQL “where b.dgt >= 0”. That condition should never be fals with the data generated in the SQL, it is there just to showcase the bug. If you remove it you will get the expected result.

Same thing if you change “union all” to “union”, that also makes it produce a correct result. Note that as the query is written, there should be no difference in the result between union all and union.

An argument against it having anything to do with query transformation is that the hint NO_QUERY_TRANSFORMATION has no impact on the behavior.

I may of course have overlooked something here, but I have reviewed this behavior with two colleagues I enjoy debating SQL with.

This blogpost is largely as a reminder to myself and to anyone who may be interested in seeing the odd behavior non deterministic SQL in pre-factoring clauses seem to cause in some odd situations.

 

 

Posted in Bug, SQL

February 25th, 2020 by Mathias

Have you encountered LUHNs algorithm? I can almost guarantee it even if you’ve never heard the name before. It is part of all of our lives every single day.

It is used to check that various numbers are correctly entered. From ID numbers for persons in Sweden, Grace, and Israel to credit card numbers and IMEI numbers and misc other things.

It is a very simple checksum function not intended to be cryptographically secure hash function. Due to the calculation used it is also referred to modulus 10.

The algorithm was invented by Hans Peter Luhn in 1954. It was meant to protect against most accidental errors, not against malicious attacks.

The algorithm

Each number in a sequence is alternating multiplied by 1 and 2 with the results added together. After that a modulus 10 is applied to the sum and the result is the check digit the function should return.

Let’s take an example, 374 is the input, we’re looking for the check digit that should be used. The calculation should be based on even number of digits in the input so we prepend with a zero, thus we use 0374 as our input. Now we’ll calculate 0*1, 3*2, 7*1, and 4*2 – multiply each digit in the input with 1 and 2 alternating.

0*1 = 0, 3*2 = 6, 7*1 = 7 and 4*2 = 8. Add them together and we have 0 + 6 + 7 + 8 = 21. Here is where we use modulo, 21 modulo 10 is 1. That however is not the check digit, the check digit is the reverse. That is, what do you need to add to this to make the result 10. In our case we arrived at one after the modulo operation so our check digit is 9.

There is a special case however. If a digit that is multiplied with two is between five and nine then the result will be two digits (5*2 = 10 and so on). That result is then added together resulting in all results being added together as single digits. This if we have 7 we get 7*2=14, 14 is now taken each digit and added so the result we get for the 7 is 5 (1+4). Instead of adding the digit like that, one can also subtract 9 from the result as it arrives at the same end result (14-9=5).

If you find my explanation above to rushed or hard to follow, take a look at wikipedia.

With that explanation out of the way, lets look at three ways to implement this. From slowest to fastest. If you only need this occasionally then it does not matter, they are all fast. I however faced a situation where I had to generate valid numbers that has correct check digits för a few hundred million rows.

PL/SQL

Let’s first look at a basic implementation in PL/SQL. I say basic because it is probably the most straight forward implementation of the pseudo code version of the algorithm. It is in my experience the most common way to implement it. When this feature is needed it tends to end up in a traditional function/procedure in PL/SQL.

This code is “borrowed” from Daniel Ekberg, just changing some variable names to make it easier to understand for the rest of us.

CREATE OR REPLACE FUNCTION get_value_w_chk (inv IN VARCHAR2)
  RETURN VARCHAR
AS
  in_val     VARCHAR2 (30);
  pos        int;
  total      int := 0;
  multiplier int := 0;
  result_pos int := 0;
  in_len     int;
  chk_digit int;
BEGIN
  in_len := LENGTH (inv);
  in_val := inv;
 
  FOR i IN 0 .. (in_len - 1)
  LOOP
    pos := in_len - i;
    multiplier := MOD (multiplier + 1, 2);
    result_pos := TO_NUMBER(SUBSTR(in_val, pos, 1), '0') 
                * (multiplier + 1);
 
    IF result_pos &gt; 9 THEN
     total := total + result_pos - 9;
    ELSE
     total := total + result_pos;
    END IF;
  END LOOP;
 
  chk_digit := MOD (1000 - total, 10);
 
  RETURN in_val || chk_digit;
END get_value_w_chk;
/

The code essentially loops through a string of digits and makes the calculation for each digit, adds it to a total and finishes up with a modulo operation to end up with the check digit, it then returns the parameter concatenated with the check-digit.

PL/SQL recursive function

The cool kids are writing recursive functions all day long or so they would have you believe. This particular algorithm lent it self to it so I wanted to test doing that. Also, next time one of the cool kids claims it cannot be done in legacy languages like PL/SQL I can just point them to this blog post (hello future reader).

CREATE OR REPLACE FUNCTION get_chk(p_i_val IN VARCHAR2) 
       RETURN VARCHAR2 AS
  v_inval VARCHAR2(50) := 
                CASE
                  WHEN MOD(LENGTH(p_i_val), 2) = 0 THEN p_i_val
                  ELSE           <wbr />                       '0' || p_i_val
                END;
 
  v_num NUMBER;
  v_result VARCHAR2(51) := p_i_val;
 
  FUNCTION recur_value( p_i_mult_with IN NUMBER
                      , p_i_val       IN VARCHAR2) RETURN NUMBER AS
    v_num_char NUMBER(2);
    v_num_tot  NUMBER(2);
    v_sum      NUMBER(2);
  BEGIN
    v_num_char := TO_NUMBER(SUBSTR(p_i_val,1,1)) * p_i_mult_with;
 
    IF v_num_char &gt; 9 THEN
      v_num_tot := v_num_char - 9;
    ELSE
      v_num_tot := v_num_char;
    END IF;
 
    IF LENGTH(p_i_val) &gt; 1 THEN
      v_sum := v_num_tot + recur_value( CASE 
                                          WHEN p_i_mult_with = 1 THEN 2 
                                          ELSE                        1 
                                        END
                                      , SUBSTR(p_i_val, 2));
    ELSE
      v_sum := v_num_tot;
    END IF;
 
    RETURN v_sum;
  END recur_value; 
 
BEGIN
  RETURN v_result 
      || (10 - MOD( recur_value( p_i_mult_with =&gt; 1 
                               , p_i_val       =&gt; v_inval)
                  , 10)); 
END get_chk;
/

Side note: I have now spent an inordinate amount of time on what looks like a broken numbering of the code above. No idea what happens, but I need to find a better code formatter.

The code takes the inparameter and calls the recursive function which works by peeling of one digit from the parameter and calling itself with the rest. This call continues until a chain of calls has been done and the innermost invocation only has a single digit as the inparameter, then it returns it’s calculation and it starts summing each returned value with the calculation for the peel off digit eventually getting the total for all calculations returned to the outer function which performs the same kind of modulus operation as we saw in the first code block.

Pure SQL

The previous versions are what you probably want to start with if your value is already in PL/SQL, but what if it starts in SQL or you generate data with SQL? Then calling a PL/SQL function is not optimal.

In my case I had a need to get a correct check digit for an invented value. When you are to do it with a few hundred million rows you do not want to call a function for every row.

This is what I came up with to calculate the check digit in SQL.

WITH dl AS
(
  SELECT 1 rad FROM dual
  UNION ALL
  SELECT 2 rad FROM dual
)
, a AS
(
  SELECT CASE
           WHEN MOD(LENGTH(:1), 2) = 0 THEN :1
           ELSE                          <wbr />   '0' || :1
         END arg
    FROM dual
)
--select * from a;
, b AS
(
SELECT LEVEL rn, TO_NUMBER(SUBSTR(arg, LEVEL, 1)) dgt
  FROM a
  CONNECT BY LEVEL &lt;= LENGTH(a.arg)
)
--select * from b;
, c AS
(
SELECT b.rn
     , b.dgt
     , CASE
         WHEN MOD(b.rn, 2) = 0 THEN dgt * 2
         ELSE                       dgt
       END calc
  FROM b
)
--select * from c;
SELECT 10 - MOD(SUM(CASE
                      WHEN dl.rad = 1 AND c.calc &lt;  10 THEN c.calc
                      WHEN dl.rad = 1 AND c.calc &gt;= 10 THEN TRUNC(c.calc / 10)
                      WHEN dl.rad = 2 AND c.calc &lt;  10 THEN NULL
                      WHEN dl.rad = 2 AND c.calc &gt;= 10 THEN c.calc - 10
                    END), 10) chk
  FROM c, dl
WHERE dl.rad = 1
    OR(dl.rad = 2
   AND c.calc &gt; 9);

What is going on in this SQL? It looks long but it really is broken up into small chunks. We have four WITH-selects before we get down to the real one.

The first prefactoring SQL – dl – is just getting us a “table” with two rows. It is used later to handle the situation of the multiplication of a digit being > 9.

Next is “a”, all we do here is to fix the input in cases where it is of an uneven length. In such cases we prepend the input with the digit zero. An alternative would have been to change so we start multiplying the digits with 2 instead of 1, it achieves the same result.

After that comes “b” where we split the string into one row per digit in the input. The digits are also converted to numbers as we’re going to use them in multiplication in the next step.

In the next step “c” we calculate the value for each digit, The first is multiplied by 1, the second by two, the third by one again and so on.

After that the “real” SQL is executed. It takes the rows with sums by digits and creates two of them using “dl”. For rows where dl.rad is 1 we take the first digit (if calc is 15, we take 1), for situations where c.calc is over 9 we use the dl.rad rows where it is 2 to get the second digit (if calc is 15, we take 5). All those numbers are the added together and the usual modulus operation is performed returning the check digit.

To simplify following and troubleshooting I have the “select * from” rows left in the SQL. Uncomment one and run the SQL to see the result up to that point. It is by far the best way to understand what happens in the SQL, even better if you follow my explanation here and uncomment one to see the result I try to describe.

If this helps you please leave a comment or even more so if you have one variant of this that is simpler or faster.

Posted in PL/SQL, SQL, Uncategorized

February 29th, 2016 by Mathias

Reports of this blogs death have been greatly exaggerated. It has been very quiet here though while I worked on getting the Swedish part of Miracle started. It is now rocking the Stockholm market so it’s time to get back into more geeky stuff.

Talking of which. I have encountered Liquibase for database versioning time after time and always come to the conclusion that it is not what a DBA want to use. I recently took it out for a spin to prove once and for all that it is not capable of what real DBAs need.

Well, lets just say that it did not end like I expected. The phrase, an old dog learns a new trick comes to mind. Once I got over the idea that it is the DDL I have to protect and realised that it is rather control over how changes occur I need. In fact I get all the control I need and a lot of problems with being able to back out changes and to adopt for different kinds of environments and/or database types are easily handled.

Do take it out for a spin, you’ll like it.

If you read Swedish, I wrote a document with a full demo of lots of the features. You find it here. It is a long document, partly because it has a long appendix with a changelog and partly because I step through each change (of 37) and explain them.

I also show how APEX applications can be installed with Liquibase. This is often said to not work, you have to do it by hand with APEX. Well, not only is it possible – it is easy.

I’d translate the demo and the document to English if that would be useful to many people. Let me know if this sounds like a document you’d like to see in English.

Posted in DBA, SQL, Versioning

March 15th, 2013 by Mathias

In this post I’ll finish up the CRUD implementation using records, procedures and views. This series of blog posts started with this post which was followed by this.

At this point we have a working report that links to a form. The report is based on a view and the form is based on a procedure. At this point the form is only loading the record in using a procedure that uses a record in its signature. In this post we’ll complete the functionality by using the same form for insert, update, and delete functionality.

Let’s start with adding a mode page item to the form. We will use this to know if the form is invoked in insert or in update/delete mode.

  • Right-click on “Form on Stored Procedure” on the page where your form is (page 4 in my example)
  • Select “Create Page Item”
  • Select Hidden
  • Give the page item a name, like P4_MODE.
  • Click “Next”
  • Click “Next” (again)
  • Select “Create Item”

You now have a new page item which is not displayed, but whose value you can reference on this page.

To make this example easy, we’ll start with just implementing update functionality. But first we’ll go to the report and change this link column to pass in a “U” for the mode page item when it is clicked.

In the page with the report, perform the following steps.

  • Right-click “List of Employees”
  • Click “Edit Report Attributes”
  • Scroll down to “Link Column”
  • For Item 2, enter the name and value we want the newly created page item to be set to. P4_MODE and U in my case.
  • Apply Changes

Now with that done, let’s head back to the page with the form. But before we make any changes there, we need to change the package to add a procedure for updating a row in the emp table.

Here is the code for the procedure we’ll use.

create or replace package tb_access as
  type t_emp_rec is record (empno emp.empno%TYPE
                           ,ename emp.ename%TYPE);

  procedure read_emp(p_in_empno     in emp.empno%type
                    ,p_out_emp_rec out t_emp_rec);

  procedure write_emp(p_in_empno    in emp.empno%type
                     ,p_in_emp_rec  in t_emp_rec);
end tb_access;
create or replace package body tb_access as
  procedure read_emp(p_in_empno in emp.empno%type
                    ,p_out_emp_rec out t_emp_rec) is
    begin
      select empno
            ,ename
        into p_out_emp_rec.empno
            ,p_out_emp_rec.ename
        from emp
       where empno = p_in_empno;
  end read_emp;
----------------------------------------------------
  procedure write_emp(p_in_empno   in emp.empno%type
                     ,p_in_emp_rec in t_emp_rec) is
    begin
      update emp
         set ename = p_in_emp_rec.ename
       where empno = p_in_empno;
  end write_emp;
end tb_access;

This code adds the procedure write_emp wich also takes empno and the record used for read_emp, only this time they are both in parameters as we’ll not return any data from the procedure that writes to the table. The actual code in the procedure is a very simple update that sets the name to the ename in the record for the row that has the empno passed in.

With this in place we’re ready to change the form to call this procedure upon submit. We do this by taking the code used for reading in the record (The process in page rendering) and put it into the “Run Stored Procedure” process we commented out in page processing.

The code we copy looks like this:

declare
  in_emp_rec tb_access.t_emp_rec;

begin
  tb_access.read_emp(p_in_empno    => :p4_empno
                    ,p_out_emp_rec => in_emp_rec);

  :p4_ename := in_emp_rec.ename;
end;

Replace the code in “Run Stored Procedure” with the above and change it to look like this:

declare
  in_emp_rec tb_access.t_emp_rec;

begin
  in_emp_rec.ename := :p4_ename;

  tb_access.write_emp(p_in_empno    => :p4_empno
                     ,p_out_emp_rec => in_emp_rec);
end;

In the “Run Stored Procedure” change the condition to “- Select condition type -” to not have a condition for when the process is executed. It is currently conditioned on running only when the save button is clicked, leave that condition on for now.

With that in place, it is time to test the functionality of the forms new functionality. Run the report, click on the link and update the name of an employee. Assuming you followed the above and got the changes made in the right places, the form will now update the name of the employee.

The next step in this will be to add insert functionality to the form. First off is to set up the package to support inserting an employee. As we’re now going to have both insert and update through the same procedure, we need to include the mode in the signature of the procedure.

Let’s first update the package specification so the write_emp looks like this:

  procedure write_emp(p_in_empno    in emp.empno%type
                     ,p_in_mode     in varchar2
                     ,p_in_emp_rec  in t_emp_rec);

The procedure in the package needs to be updated to look like this:

  procedure write_emp(p_in_empno   in emp.empno%type
                     ,p_in_mode    in varchar2
                     ,p_in_emp_rec in t_emp_rec) is
    begin
      case p_in_mode
        when 'U' then
          update emp
             set ename = p_in_emp_rec.ename
           where empno = p_in_empno;
        when 'I' then
          insert into emp
             (empno
             ,ename)
           values((select max(empno) + 1 from emp) 
                 ,p_in_emp_rec.ename);
      end case;
  end write_emp;

Before the form is functional again, we need to change the call in “Run Stored Procedure” to match the new signature of write_emp.

declare
  in_emp_rec tb_access.t_emp_rec;

begin
  in_emp_rec.ename := :p4_ename;

  tb_access.write_emp(p_in_empno    => :p4_empno
                     ,p_in_mode     => :p4_mode
                     ,p_in_emp_rec  => in_emp_rec);
end;

With that change in place the form now works and the mode we set on the link column is now passed to the procedure to make sure an update is performed and not an insert.

To introduce insert functionality, we want to add a new button on the page with the report that invokes the form in insert mode. To add the button follow these steps on the page with the report.

  • Right-click on “List of Employees”
  • Select “Create Region Button”
  • Set name and label to “Add”
  • Click Next
  • Set position to “Right of Interactive Report Search Bar”
  • Set Alignment to “Left”
  • Click Next.
  • Select action “Redirect to Page in this Application”
  • Set the page to your form-page (4 in my example)
  • Set Request to INSERT
  • Set “Clear Cache” to 4
  • Set “Set these items” to P4_MODE
  • Set “With these values” to I.
  • Click Next.
  • Click “Create Button”

If you test the page, you’ll see that it ends up immediately to the right of the search bar. Do not click on the button just yet.

Now go to the page with the form and open the process in rendering (Read Emp).

  • Set “Condition Type” to “Value of item / column in expression 1 = expression 2”.
  • Set Expression 1 to P4_MODE.
  • Set Expression 2 to U
  • Apply Changes

This ensures that we’re only reading a row from emp when updating data. When inserting, there is no data to be read and displayed.

You may also want to set the same condition on the page item for empno to hide it when inserting as there is no empno to display then.

Test the function now by running the report page and click on the “Add” button. It adds a row with the name you enter on the form. The rest of the columns will have null as their value. Adding more columns in the procedures and on the form is an exercise left to the reader.

With the function now supporting insert and update, it is time to add the delete function so we can get rid of some of the test employees we’ve created. This function will be implemented only on the form and by adding supporting code in the package. Thus, the delete will be performed by clicking on the link and then clicking on a delete button on the form.

To implement this we’ll update the implementation of the write_emp procedure once more.

  procedure write_emp(p_in_empno   in emp.empno%type
                     ,p_in_mode    in varchar2
                     ,p_in_emp_rec in t_emp_rec) is
    begin
      case p_in_mode
        when 'I' then
          insert into emp
             (empno
             ,ename)
           values((select max(empno) + 1 from emp) 
                 ,p_in_emp_rec.ename);
        when 'U' then
          update emp
             set ename = p_in_emp_rec.ename
           where empno = p_in_empno;
        when 'D' then
          delete from emp
           where empno = p_in_empno;
      end case;
  end write_emp;

With the package updated, our next step is to change the code in the “Run Stored Procedure” process on the form page.

declare
  in_emp_rec tb_access.t_emp_rec;
  v_mode     varchar2(1);
begin
  in_emp_rec.ename := :p4_ename;
  v_mode           := :p4_mode;

  if :REQUEST = 'DELETE' then
    v_mode := 'D';
  end if;

  tb_access.write_emp(p_in_empno    => :p4_empno
                     ,p_in_mode     => v_mode
                     ,p_in_emp_rec  => in_emp_rec);
end;

The code is now changed such that a click on the delete button (not implemented yet) will set the mode to D. This is done by checking the REQUEST which we will set to DELETE for the button we’ll create now. REQUEST is a standard attribute available on buttons and branches in APEX. By setting it to a unique value, we can check for different invocation methods in code. This allows us to deal with different scenarios with just one block of code.

To add the button, follow these steps.

  • On the form-page, right-click on “Form on Stored Procedure”.
  • Select “Create Region Button”
  • Set “Button Name” and “Label” to “Delete”.
  • Click Next
  • Set “Position” to “Top of Region”
  • Set Alignment to “Left”
  • Click Next
  • Click Next (again)
  • Click Create Button

The value of “Button Name” is used to set REQUEST when the page is submitted. With this in place our form should now support deleting rows also. Update the branch on the page to not have a condition on the button pressed. Take it out for a spin.

There is one thing left to do, hide the delete button when the form is invoked to add a row.

  • Right-click the delete-button under rendering.
  • Select “Edit”.
  • Scroll down to conditions.
  • Set “Condition Type” to “Value of item / column in expression 1 = expression 2”.
  • Set Expression 1 to P4_MODE.
  • Set Expression 2 to U
  • Apply Changes

Now you’ve got a CRUD solution based on an interactive report with a form that supports insert, Update, and Delete. The total amount of code required is very small and you still have isolated the APEX-application from changes to the table structure by using a view, even the record can have fields added to it without impacting the functionality of your application. Developing using the demonstrated methods here allows you to create these applications almost as fast as with making them table based, but you have much more control and can make a lot of changes without touching the application,

Posted in APEX, Oracle, PL/SQL, SQL

April 13th, 2011 by Mathias

Quite a while ago I wrote about how to seup the alert log as an external table. Since then 11g has been introduced and is now widely used. It of couse changes the location and makes the alert log an xml file.

While it is possible to select from it using xml functions like Laurent Schneider does here, it is still a bit cumbersome.

Tanel Poder (@TanelPoder) found a nicer way by using X$DBGALERTEXT which does a really nice job of parsing the xml-file into a lot of different columns. A friend at work, Daniel Ekberg, let me know about it a while back. I just never got around to looking into it until today.

It is very nice but there is one slight issue with it. It is X$ and thereby only available to SYSDBA. That can of course be solved with a view and proper grants and synonyms. However, views on X$ can cause some issues during upgrades so such views should probably be dropped before upgrading.

While not documented, this is a neat way to gain access to a lot of information from the xml.log (alert-log).

Posted in Alert Log, DBA, Oracle, SQL, XML

October 3rd, 2010 by Mathias

Yes, this post is a little out of order as it clearly didn't take place after the Thursday afternoon sessions. I missed it during the conference so I had to catch up on it later on the on demand site. I thought it was interesting enough to write up a few notes.

It was held by Tom Kyte and the subject was "What's new in database development". It'd be more correct to say Oracle development than database development as he covered just about any area that Oracle focuses on.

He started with APEX and spent a lot of time on it. The coverage APEX gets in general sessions and the number of really good APEX sessions speaks volumes about how strong APEX is becoming within the Oracle development technologies.

Tom shared a little background of how APEX was built by Mike Hitchwa when Mike set next to Tom and wrote the initial code for APEX. Anyone that has been impressed with the way bind variables are handled in APEX got their explanation for why. Tom has relentlessly been preaching about how to to it and the issues caused when not done well. So it all makes sense when you hear that it was Tom who wrote the initial code for bind variable handling in APEX.

Tom went on to say that they are seeing a lot of momentum behind APEX right now. The catch phrase he used to explain why it grows fast in popularity was "Easy architecture, easy to deploy, and easy to run".

Tom then spent some time talking about performance of APEX as a lot of people still thinks it is not able to handle enterprise level system demands.

The site apex.oracle.com that is used to run all the applications people create on Oracles hosted apex "test and demo" site has a surprisingly small computer running it. The specifications for it is:

  • Poweredge 1950
  • 2 Dual core Xeon 2.33 Ghz
  • 32 GB Ram
  • Cost : $4,300 (in 2007)
  • Runs 11G database

Not a very big computer to serve a site that provides support to anone who wants to try APEX.

Tom shared som of the numbers for what apex.oracle.com is used for.

  • They had 3.5 million views (pages loaded and processed) last week.
  • Distinct users accessing during the week: 3028
  • Total workspaces :9062
  • Applications: 32776

I'd think most "enterprise" systems would have less demand than that.

Some of the biggest things on apex.oracle.com are:

  • SQL Developer updates 800K hits /week
  • Pro MED (Prodution system) 770K hits / week
  • Asktom 250K-1000K hits / week
  • APEX Appbuilder 238K hits / week
  • JDeveloper updates 175K hits / week

Impressive to serve all of that from a small(ish) computer.

Tom then shared some key features from the 4.0 release.

Websheets

Designed for the enduser and designed for webbased content sharing. A solution that allows endusers to stop mailing exceldocuments around would be a good thing…

Dynamic Actions

Declarative client-side behavior. It is integrated in the framework. Uses JQuery.

This allows using common javascript function for handling advanced user interface improvements. From validations to drag and drop and more.

Team Development

Allows you to manage the application development process within APEX. This makes APEX able to be used by large development projects. It has built in end-user feedback. When a user has a problem they can tell you directly in the application and it feeds into the team development features.

Oracle APEX Listener

Built in Java.

It is a good alternative to mod_plsql and it is certified against WLS and Glassfish.

After that long piece on APEX, Tom went through som improvements for other technologies.

SQL Developer

The datamodeller is now free. It will now come with the product and will be supported with the database (just like SQL Developer itself).

It has real-time SQL monitoring. It will allow you to see what part of the execution plan Oracle is currently spending time in for a SQL. This exists in Enterprise Manager, but this gives the insight to many more as EM often is just used by the DBAs.

It can now find the SQL trace file and display the contents graphically in SQL Developer. This ought to make trace files much more accessible to developers.

It has support for AWR and ASH reports and has added developer specefic things that are not available in the other AWR/ASH aware tools.

A hierarchical profiler is now also included to allow you to see what functions calls which in a run of PL*SQL code.

SQL Developer now used PL/SCOPE data to show information about where variables are defined and used, This is something that has been added to the database and can alays be accessed with SQL, but it is more convenient to have it hooked into the development tool.

They have also added a DBA Navigator that allows DBAs to access most things they deal with in the database. Most objects (such as extents and tablespaces) can be seen and modified.

An interface for DBMS_SCHEDULER has been added. It is supposedly a much nicer way to configure jobs than to do it by have, which can be both tedious and complicated.

Another neat feature is that auto tracing has been improved to not only allow tracing of a statement, you can now trace two statements and see a comparison report of the tracing so you can compare them. Since this usually has been done with Toms "Runstats" package, I'd assume this feature has Toms fingers all over it.

ODP.Net

It is free and provides native access to the database. It has statement caching (on the client side) and will support TimesTen in 11.2.0.2.

I'm not a .Net person at all , but it sounds as if this provides a lot of benefits both in use and in performance. If you work with .Net, then you will want to take a look at all the things Oracle provides in this area.

Java

There is a Universal Connection Pool for all technologies using connection pools to use. This is supposed to reduce the overhead caused by having several connection pools.

For Secure Files there has apparently been a performance issue dealing with very large files, my understanding from Toms presentation is that it resulted in double buffering. Now there is Zero Copy which sounded as if it would remove all buffering and thereby improve the speed significantly.

Database Improvements

Tom focused on the optimizer and on developer related topics as this was a Develop keynote.

Tom showed how the optimizer could detect a three-way join that was not needed. It was a fairly complicated situation, but the optimizer can apparently completely skip accessing tables now if they are not needed for the end result.

PL*SQL

Edition based redefinition is available in a editions of 11G. Tom thinks this is the killer feature of 11G, and says that it may be the first time the killer feature is available in all editions and with no additional licensing requirements.

It allows live changing of code (PL and views). Live changing of data can already be done with online redefinition and online actions on indexes.

The process now is:

  • Create new edition
  • Test new edition of the code while users still use the old.
  • Two options for rolling out
    • Let new sessions use the new one and allow old to keep using the old edition.
    • Switch everyone over to the new when the system is "idle". Takes just seconds.

That was the end of Toms presentation. He covered a lot of technologies, even some that are not mentioned above (like Ruby). WHat I think is interesting is what wasn't mentioned at all.

The fist one that comes to mind is JDeveloper. It has not become very popular with Java purists and it is not mentioned at all. There are some rumors of it being de-emphasized by Oracle as how you work with it seems to be very far from how most Java projects want to organize their builds and deployments.

Next interesting omission is the technology mostly integrated with JDeveloper. No mention at all on the Oracle Develop for their key Java development framework. ADF did not get any love at all and that is not true just for this presentations, there were very few mentions of it in any of the presentations I attended. True, I did not seek out ADF sessions, but virtually every single presentation on non APEX technologies found a way to talk about APEX, the oposite was true for ADF. Even when you felt that someone was building up to a plug for ADF, they never went there.

The lack of love for ADF and JDeveloper may mean that they are not considered critical to Oracles success anymore. I assume ADF was used to build the fusion applications which would mean that the tool and product is probably not going away, but could it mean that they are primarily being focused for large product development and not something that is good for custom development projects?

Personally it feels as if ADF is now essentially becoming a tool used when integrating with Oracle (fusion) Apps or Oracle SOA Suite. I'm not sure a custom development project would end up using ADF. Of course there will be exceptions to that, but they will probably be fewer and fewer.

It could also be that JDeveloper and ADF starts becoming less and less of something for the customers and more and more of an internal tool at Oracle. I'm sure they have very specialized versions and techniques used in development to help the development of the fusion applications.

On the other hand, it could just be that this wasn't the time for releasing improvements to JDeveloper or ADF. I have trouble convincing myself of that though. The reason is that Oracle now owns Java and if there ever would have nee a reason to show JDeveloper and ADF in its full glory, now would have been the time. By not releasing improvements to what is considered problems with JDeveloper and ADF, they have probably given more momentum to the competing tools. They of course ha another tool in this space now, maybe NetBeans will be the future migration path of JDeveloper and when ADF finds a new home.

Posted in APEX, DBA, OOW, Oracle, Performance, SQL

September 29th, 2010 by Mathias

The day starts with a presentation b6 Tom Kyte about "What else can you do with system and session data".

Tom starts with reviewing the history of tuning an Oracle database.

The prehistoric era (v5) required writeing debug code as that was the only way to get any information about what the code did.

The dark ages followed (v6) and now Oracle introduces:

  • Counters/ratios
  • bstat/estat
  • SQL Trace
  • The first few (7?) v$-views are introduced

In the rainessance era (v7) introduced a few more things:

  • The wait event instrumentation was introduced
  • Move from counters to timers
  • Statspack

The modern era (v10) introduced the tools we prefer today

  • DB-Time tuning
  • Multiple scoping levels (AWR)
  • Always on, non-intrusive
  • Bult into the infrastructure – instrumentation, ASH, AWR, ADDM, EM

DBA_HIST_* views is where the historic data is located.

Tom contunued with discussing the metrics and the timemodel and how to join that to get data about them. However, all his presentations are available for everyone and you're better off reading them on his site than getting a summary of it here.

As usual, Tom places his presentations and other things under files on asktom.

The next presentation was Apex debugging and tracing with Doug Gault from Sumneva.

Debug mode is truned off on the app level by default. It neds to be enabled there before it can be used.

Debug data is now written to a table, and not dumped on the webpage as before. It is viewed by clicking view debug in the developer bar in the APEX interface.

Use conditional display to output things only in debugmode (using v('DEBUG') ).

Things that happens on a page after it has been rendered will not be captured. This is things like javascript running on this client.

APEX_DEBUG_MESSAGE is currently undocumented, but the code shows how to use it.

Debugging can be turned on/off on the page programmatically.

Logging from you own code allows you to see what parts of a package call it is that takes the time.

Doug then shows a few different ways to collect and report on the debugdata for a report.

I recommend looking at the presentation for ideas on how to use it and what can be done. Unfortunately this presentation does not seem to be available on the sumneva website at this point. If you are interested in it, send an email to them asking if they are willing to share it on their presenation page. You'd of course want to check their site first to make sure it isn't uploaded between the time I write this and the time you read it.

 

Posted in APEX, OOW, Oracle, Performance, SQL

January 10th, 2010 by Mathias

I've just uploaded my latest article. This time it is a look at the SQL keyword CASE. It's power is often not fully appreciated. It is much more flexible and powerful than you may expect from just taking a quick glance in the official documentation.

The article is located here.

Please post comments and questions here.

Posted in Oracle, SQL