The beauty of Java stored procedures

Since 8i you can write stored procedures also in Java. But what is the advantage of writing a procedure in Java? Well the first advantage is that you’re now able to write complex solutions and use them in PL/SQL. You can write your own package for file I/O, email reading/sending, OS watching, an API for ZIP-files and many more. You’ve all the possibilities from Java now available in PL/SQL! And another advantage: It runs in the Oracle JVM which means it have access to the huge SGA and can do DB operations with better performance than outside it! And it’s easy to write it. For example a little Java stored procedure which shows you the properties of the Oracle JVM:

The first step is to write the java source which looks like this:

create or replace and compile java source named “JavaProps” as

And now continue with normal Java code:

public class props
{
public static void GetProps( )
{
System.getProperties().list(System.out);
}
}
/

Well this now creates the Java source inside the database. Now we create the stored procedure over it to use it inside PL/SQL:

create or replace procedure JavaProps
as language java name ‘props.GetProps()’;
/

And that it was! Now run the procedure in an anonymous PL/SQL block:

set serveroutput on size 10000;
begin
dbms_java.set_output (10000);
JavaProps;
end;
/

So you see, writing Java stored procedures isn’t difficult and allows you to do now powerful operations inside of PL/SQL!

Today Oracle 11g and tomorrow… the whole world

Finally the time has come. I’m currently installing Oracle 11g on a test-box (thanks to my Admin for the box!). I’m very excited about the new features Oracle promise. Unfortunately the memory of the box isn’t huge enough to enable Automatic Memory Management but I’ll try this feature in the future! So the next few weeks I’ll run miscellaneous test on it to see what improvement Oracle 11g brings. Well, lets see…. Oh, and of course, you’ll find the tests in this blog!

BULK COLLECT & FORALL vs. CURSOR & FOR LOOP

After more and more reads about BULK COLLECT and FORALL and their performance improvements I decided to have a closer look on it by myself to see how powerful they really are. So I built a little test-case which inserts all entries from the all_object view into another table. The inserts happens on three different ways:
First way is a simple cursor over the view and a insert in a loop with FETCH into local variables. This way also shows how slow the opening of the cursor itself is.
The second way is a simple FOR – IN LOOP with the insert of the cursor variables.
And, of course, the third way is the way with bulking the rows and inserting them with FORALL so lets see.

So the other table looks like this (three columns are enough for this tests)

SQL> create table temp (owner varchar2(30), name varchar2(30), type varchar2(19));

Table created.

And the three different procedures looks like this

CREATE OR REPLACE PROCEDURE CURSOR_FOR_OPEN_QUERY
 IS
 l_sOwner VARCHAR2(30);
 l_sName VARCHAR2(30);
 l_sType VARCHAR2(19);
 CURSOR cur IS SELECT owner, object_name name, object_type type FROM all_objects;
 BEGIN
 dbms_output.put_line('Before CURSOR OPEN: ' || systimestamp);
 OPEN cur;
 dbms_output.put_line('Before LOOP: ' || systimestamp);
 LOOP
 FETCH cur INTO l_sOwner, l_sName, l_sType;
 IF cur%NOTFOUND THEN
 EXIT;
 END IF;
 INSERT INTO temp values (l_sOwner, l_sName, l_sType);
 END LOOP;
 CLOSE cur;
 dbms_output.put_line('After CURSOR CLOSE: ' || systimestamp);
 COMMIT;
 END;
 /

CREATE OR REPLACE PROCEDURE CURSOR_FOR_QUERY
 IS
 BEGIN
 dbms_output.put_line('Before CURSOR: ' || systimestamp);
 FOR cur IN (SELECT owner, object_name name, object_type type FROM all_objects) LOOP
 INSERT INTO temp values (cur.owner, cur.name, cur.type);
 END LOOP;
 dbms_output.put_line('After CURSOR: ' || systimestamp);
 COMMIT;
 END;
 /

CREATE OR REPLACE PROCEDURE BULK_COLLECT_QUERY
 IS
 TYPE sOwner IS TABLE OF VARCHAR2(30);
 TYPE sName IS TABLE OF VARCHAR2(30);
 TYPE sType IS TABLE OF VARCHAR2(19);
 l_sOwner sOwner;
 l_sName sName;
 l_sType sType;
 BEGIN
 dbms_output.put_line('Before Bulk Collect: ' || systimestamp);
 SELECT owner, object_name, object_type
 BULK COLLECT INTO l_sOwner, l_sName, l_sType
 FROM all_objects;
 dbms_output.put_line('After Bulk Collect: ' || systimestamp);
 --
 FORALL indx IN l_sName.FIRST..l_sName.LAST
 INSERT INTO temp values (l_sOwner(indx), l_sName(indx), l_sType(indx));
 --
 dbms_output.put_line('After FORALL: ' || systimestamp);
 COMMIT;
 END;
 /

Ok, then I bounced the database to get no buffers, caching, etc. on it.

So the first execute

SQL> exec cursor_for_open_query
Before CURSOR OPEN: 27-SEP-07 10.56.30.699401000 AM +02:00
Before LOOP: 27-SEP-07 10.56.30.922366000 AM +02:00
After CURSOR CLOSE: 27-SEP-07 10.57.07.699791000 AM +02:00

Only look at the seconds it took 37 seconds and nearly nothing for opening the cursor! But how much rows were inserted?

SQL> select count(*) from temp;

COUNT(*)
----------
49424

Truncate the table (truncate to free the extends!) and bounce the database again and now the second run

SQL> exec cursor_for_query
Before CURSOR: 27-SEP-07 10.59.47.848249000 AM +02:00
After CURSOR: 27-SEP-07 11.00.09.072525000 AM +02:00

The whole loop took 22 seconds, well this looks already better. Well, also all rows inserted?

SQL> select count(*) from temp;

COUNT(*)
----------
49424

But now (after truncate and bouncing) the bulk collect run

SQL> exec bulk_collect_query
Before Bulk Collect: 27-SEP-07 11.01.33.553224000 AM +02:00
After Bulk Collect: 27-SEP-07 11.01.41.874054000 AM +02:00
After FORALL: 27-SEP-07 11.01.42.065753000 AM +02:00

Look at this, for bulking all the lines into the collection took just 8 seconds (for 49 424 rows) and the inserts just 1 second! Unbelievable, together we did everything in 9 seconds where the other ways took over 20 seconds!

Well now lets try to first execute the bulk load then truncate the table again but not bouncing the database so that the buffers and caches a still filled

SQL> exec bulk_collect_query
Before Bulk Collect: 27-SEP-07 11.02.31.257498000 AM +02:00
After Bulk Collect: 27-SEP-07 11.02.41.614205000 AM +02:00
After FORALL: 27-SEP-07 11.02.41.818092000 AM +02:00

PL/SQL procedure successfully completed.

SQL> select count(*) from temp;

COUNT(*)
----------
49423

SQL> truncate table temp;

Table truncated.

SQL> exec cursor_for_query
Before CURSOR: 27-SEP-07 11.04.04.960254000 AM +02:00
After CURSOR: 27-SEP-07 11.04.25.749038000 AM +02:00

Ok so now we need 10 seconds for the run with the bulk but we sill need 21 seconds for the cursor! So not really a improvement with the cache and so on. Ok final test on a big system with over 268 thousand rows

Before Bulk Collect: 27-SEP-07 11.24.17.034732000 AM +02:00
After Bulk Collect: 27-SEP-07 11.24.25.111020000 AM +02:00
After FORALL: 27-SEP-07 11.24.26.129826000 AM +02:00
PL/SQL procedure successfully completed.

COUNT(*)
----------
267985

Table truncated.

Before CURSOR: 27-SEP-07 11.24.29.629354000 AM +02:00
After CURSOR: 27-SEP-07 11.25.02.244549000 AM +02:00
PL/SQL procedure successfully completed.

COUNT(*)
----------
268056

And again, bulking took 8 seconds and the inserts just 1 second! But the run with the cursor took 33 seconds!

So this was just a short test but it definitely shows that BULK COLLECT and FORALL are much faster than cursors within the FOR loop! Only disadvantage of FORALL as you maybe already guess if you looked at the code: You can just perform one DML statement, there is no “FORALL END” clause! But anyway also bulking is a very high-performance functionality of Oracle! So if you have to run throw data collections then use BULK COLLECT!

Oracle Administration course

Today I started with an Oracle Administration course and just finished the “Database Architecture” part. Well, not really new stuff for me. They told me think like: What’s the SGA, PGA (they didn’t say anything about the UGA), background, server and user processes, what’s the diffrence between database, instance and server and a little overview of which work the processes perform. All in all a course which go to less in detail of the components for me. All the stuff they told me I already know and with know I mean: I know it in much more detail! But to be fair, I’m sure that it’s not a bad course for people who starting to be a DBA. And that was just the first session of the course, so we”ll see if the others are more interesting for me!

JDBCs setMaxRows and the SELECT … FOR UPDATE clause

Today following question came up: Would the JDBC driver of Oracle regonize it, when you set the setMaxRows and perform a SELECT using the FOR UPDATE clause. Example: You’ve a table with 40 rows. Now you call setMaxRows(20) so that you only would get the first 20 rows and perform the select without any where clause (I know, not really a pretty solution: Instead of using rownum, you would select all rows and just stop fetching at the 21th row, but however). Would you now lock all 40 rows or just 20. Well, the answer is (I’ve expected it): You get a row lock on the entry table, not only the first 20 lines. With rownum in the where clause it is now problem. You just lock the selected 20 rows and the other 20 are available for other transactions. So developers: Use the rownum pseudo column instead of the setMaxRows function specially if you perform a select… for update!

But the nicest thing on this little test: I wrote my first Java code since 4 years! 🙂

Possibility of object-oriented programming in PL/SQL

Oh damn, I just get through a presentation of Steven Feuerstein and found out, that PL/SQL supports object-oriented programming. I’m sure, now you think: Why does this stupid guy mean “oh damn…”. Because this “feature” is available since 8i and got strong on 9i. And now they already released 11g! So I didn’t know this possibility several years ago! Well but now I know it and I find it pretty cool, because PL/SQL gets more and more powerful (there are also supported functions for file I/O, HTTP, XML and many many more). Also external or java stored procedures were a big step forward. I just can recur me: Pretty cool!

How to spend a Friday night

What do you usually do on a Friday night? Go out with your friends, finish some work, or just watch TV and then go to bed? Well, today I spent my Friday night in the office (like real DBA’s). Why? Well, our unix admin change the configuration of the servers and of course the DBA also has to check afterwards, if everything is right with the databases. And it was a good decision to came. Actually all database were down except of one. And it was a good chance to patch one of them. Well, now it’s 12:09 AM (on a Saturday morning), the unix admin is still working, but I’ll now drive home and go to bed.

New Oracle 11g features

Oracle 11g is already available and I just finished reading the “New features guide” of Oracle 11g. There are some really nice features in it: Flashback Transactions, CONTINUE-Clause in PL/SQL, Data Revovery Advisor, SQL Test Case Builder, Automatic Health Monitor, SQL Performance Analyzer just to name some few. I’m really excited if all this features work as Oracle promises, but I’ll finally see, when I’ve installed the database on my test system.

Why I like Oracle much more than MySql

I’ve to get a little MySql database running on a local test system and import a “dump” file of a database to verify, if the file is valid. Sounds easy, but first you have to find out, how you get the dump file into the database. For that you must know, that MySql doesn’t write a “normal” dump file as default, but a simple SQL-file including all the statements to get the status from the database back. Ok, also not very hard. Then you import the file, but not with @ like in Sql*Plus. The correct syntax is ‘source <sourcefile>;’. While executing this you get pissed on with “value bigger than max_allowed_packet”. You spend 1 hour to get yourself throw the documentation, found 4 ways to set this damn variable but – just one way works. Ok, you throw away the database, recreate it, set the variable and get sure that it is set and before you reexecute the file you would like to spool the result. But, MySql doesn’t know “spool” like Sql*Plus. No it doesn’t know anything like that as I saw on spending some minutes on the documentation and Google. The only thing you have is a .mysql_history file, but it also didn’t protocol the echo from the database, no just the commands you typed in. Ok, well no spooling, you have the shell open and will some critical error.  So restart the import from the file and see: Same max_allowed_packet error again though the variable is set to the highest value. I’m really pissed on MySql at the moment. Sure I know, if I would know it better, it would not be a problem, but my Dad has his birthday today and it’s already 6:30 PM. I don’t want to spent more time on it in the office!

What’s new in Oracle 11g database

Just listen to a pod cast of Tom Kyte explaining what’s new in 11g database. Sounds intresting, but unfortunately it’s just the first part of it and the second one isn’t published till yet! But you should check it out!