SOUG-Tagung 09/2

I am impatient to speak in the swiss oracle user group next month

March 18, 2009, Baden-Daettwil

I will be presenting an XML session regarding selecting XML from the database.

select xmlserialize(document xmlcol) from agenda;



Empfang und Registrierung

SOUG Sekretariat


im Foyer Seminarraum


Ordentliche Generalversammlung der SOUG
Seminarraum Schottky

Ehrung des SOUG Speaker of the Year 2008, Seminarraum


Track 1:
Oracle DB und Entwicklung
Moderation Manfred
Drozd, In&Out AG

Track 2:
Moderation Michele
Savino, FI-Solutions AG


XML in the Oracle Database

Use SQL to retrieve and insert XML data in the database. Learn
about the SQL functions, the XMLTYPE and various techniques to
improve your performance when dealing with XML documents.

Laurent Schneider,
Credit Suisse

Applications Portfolio Strategie, Mehrwert und Eco System

Durch die
Akquisition von über 50 Firmen hat sich Oracle vom Technologie
Leader zum Partner für Business Applikationen entwickelt. Dank
seiner kompletten, offenen und integrierten Plattform, ermöglicht
Oracle eine Reduktion der Integrationskosten. Industriespezifische
Blueprints ermöglichen den Kunden eine effiziente Geschäftsprozess

Beat Brunschwiler,


Optimiertes Backup mit RMAN und der LiteSpeed Engine für Oracle

Der Vortrag
gibt zunächst einmal einen Überblick über die unterschiedlichen
Backupverfahren (RMAN, Hotbackup, Coldbackup) und die damit
verbundenen Vor- und Nachteile. Am Beispiel von RMAN mit LiteSpeed
Engine for Oracle wird dann erklärt, wie ein Backup optimiert
werden kann und welche Auswirkungen dies auf ein Recovery hat.

Johannes Ahrends,
Quest Software

Application Integrations-Architektur

Ein grosser
Teil des Implementierungsaufwandes wird in IT Projekten für die
Integration von Applikations-Silos investiert. Dadurch erschwert
sich die Anspassung von neuen Geschäfts-anforderungen. Die
Applikation Integration Plattform standardisiert die Integration,
vom Geschäftsprozess bis zum Daten-Layer. Innovation kann dadurch
schnell umgesetzt werden

Haupfleisch, Oracle

15:30 Pause
(ca. 30′)


SQL Plan Baseline 

Diese Technologie ersetzt ab Oracle 11g die früheren Stored

Chris Antognini,

Siebel CRM Referenz Projekt

Beispiel einer Siebel CRM Implementierung bei einem Schweizer

Ehrensperger, ec4u

16:45 Apéro und Informationsaustausch
– Wir danken dem Sponsor Quest

Obfuscation contest

I have been challenged to participated (my script : lsc2.sql) and now challenged to explain my query by Chen
Chen Shapira

Ok, I give a try.

To make the query unreadable and unformatable I used no space, no new line, and I started by q’<'>‘ to confuses sqlinform.

Ok, I added undocumented constructs like

connect by .1 < 1.
connect by .25 > rownum/100.

with 2 connect by, the first is ignored. The second is buggy in 10gR1 and returns 25 rows in 10gR1 (but 24 rows in 9i, 10gR2, 11g).

and group by () which means only one group. Note this clause is mandatory, you cannot remove it when using an aggregate function and a scalar subquery like in
select (select 1 from dual), count(*) from dual group by ();

The utl_raw and to_char converts a number to the string Oracle Community. I will keep terse on the formula.
The scalar subquery inside TAN returns 1. The regexp removes some characters out of the extract tag. _x0032_ is the alias of the column “2”. The v$reservedwords use UNIQUE to sort the rows (UNIQUE was sorting in 10gR1 for UNIQUE/DISTINCT) and the MAX(rownum) retrieve the 316th row (FROM) and 845th row (SELECT). Remember ROWNUM is evaluated BEFORE aggregation, therefore ROWNUM 845 exists! Note the “+” is an alias and not a string!

With the SELECT and FROM keyword I build a new query that I dynamically evaluates with dbms_xmlgen.getxml. Rest of the first part is fancy calculation.

The dburi is also dynamic.


Well, I could have obfuscated the text a bit more but I was short of time… This simply return the name of the package that have a procedure called GETKEY. Which is DBMS_OBFUSCATION_TOOLKIT_FFI. This is not very clean as the query would fail if you have a PROC.GETKEY in your schema. Well, I wanted to add this not very well know mechanism to query the database.

Last part is in dict, I select the pattern ‘[COTTON+XE]{4,}’ in dict and return the Mode (or modal score), which is CONTEXT.

A bit formatting and I get

Oracle Community_Obfuscation_Contest

Wake up SF, Oracle is coming !

I just arrived at San Francisco and meet my colleague Daniel and Lutz Hartmann. Thanks Dani for driving us to SF and thanks Lutz for the Indian food in the restaurant where we ended last year during Blogger 2006 Meetup :-) . By the way do not miss 2007 Meetup organized by Mark Rittman : oow 2007 blogger meetup

I took the cable car from Lutz hotel to the Fishermann’s Wharf. Here it is raining, but well, in Zurich it was snowing and the wind was so cold that they needed to unfreeze the aisles of the plane before take off!

In less than 8 hours the 11g exam cram is starting at Moscone South 104 ! My notebook clock is still set to Sun Nov 11, 10:10 am, I will try to sleep until 7am…

OOW schedule

I have start building my schedule. There are about 1722 sessions to chose from this year, so choice is difficult!

Due to jet-lag, I mostly cannot do all what I planed. And I also like to spent some time by the exhibitors and the boosts.

Ok, the one I will not miss :
Steven Feuerstein : Break Your Addiction to SQL!
Amit Ganesh : Oracle Database 11g: The Next-Generation Data Management Platform
Bryn Llewellyn : Doing SQL from PL/SQL: Best and Worst Practices
Thomas Kyte : The Top 10–No, 11–New Features of Oracle Database 11g
Lucas Jellema : The Great Oracle SQL Quiz

SIG SOUG: times ten

I have been to a SOUG last Thursday.

We first had a presentation from Thomas Koch about performance in Zurich Kantonalbank. As I have been working as a DBA for about two years in that bank, I already had my opinion about performance there 😕

The second presentation was about TimesTen. I must say I have never used Times Ten. So I was glad to hear Stefan Kolmar from Oracle presenting the product. Ok, here it is in a few lines.

In TimesTen, the whole database is in the memory. TimesTen is an Oracle Product and a Database, but it is not an Oracle Database. The objective must be to have a response time in microseconds and hundred of thousands of transactions per second. You have a log buffer, and you can decide to asynchronously dump the buffer to file.

Let me try to explain the example from Stefan :
You have a mobile phone company. Foreign call can be fairly expensive, so those transactions will be synchronously dumped to the disk. Local calls cost about 1 Euro in average. So if you dump the log to disk every ten transactions, in case of a failure an average of 5 Euros will not be billed. In this way you can select the transaction to have synchronous and the one to have asynchronous. It looks promising, but probably not for critical businesses like banking where you are required to guarantee zero data-loss.

There is an additional functionality in TimesTen which is called “cache for Oracle”. It is a layer between the client and the database. It does not offer the same functionality as Oracle. For example, you cannot do PL/SQL. But it may offer microsecond access.

I will document two examples :
1) read only
You have a flight reservation company. Flight reservation are very important, so they will be in the database (no data loss). Flight schedule are read-only for the client. They will be cached in TimesTen. So when accessing the timetables, it will be ultra-fast. When booking, it may take a few seconds.

2) on demand
You have a call center. When a customer phone, all data relative to the customer (history, name, contracts, contract details) are immediately loaded from the database in TimesTen. So when the Call Center employee asks for any info, they are immediately available

How much does it cost? Check on

For a tiny database up to 2Gb it is 6000$/processor for 3 years. More options, more money…

Fit for RAC

I will be speaking tomorrow in German and Wednesday in English in Credit Suisse Bank.

The workshop is about the differences between single instance and RAC

Some of them : architecture, availability, scalability, manageability, skills required, price. I will talk also about the operational aspects, upgrade, dictionary, parameter file.

OOW 2K6 day 1

I tried to watch the keynotes yesterday, but it is just to difficult to sit and not sleep for me… so I have seen only a few minutes of each one.

The PL/SQL 11g session was excellent, I wrote an overview in a separate post.

I started the afternoon with APC session, about the importance of testing and the power of the utPLSQL package. He presented that methodology and also the concept of testing. Fixing a bug at the development stage is fine, fixing it in production is very costly. I resume the method in those steps. First, checkout your package and your testing procedure. Second, modify your package. Never commit before testing. Improve your tests. Finally, commit your changes in both the source and the tests. APC was somehow unlucky with a laggy sql-developer and an unregistered textpad. Thanks Andrew for this presentation.

Than I went to Tom Kyte session about the worst things to do. Instead of telling what to do, Tom ironised on what we Should Always Do. He started with The Experts Are Always Right. Funny presentation, lots of laugh in the audience. I have meet Ken Jacobs during Tom Session. A good moment.

I have been to the exhibition hall, and I talked to the egenera team about RAC on BladeFrame and will go to the session S283094 on Wednesday.

not enough time to do all

I updated my calendar for Oracle Open World. By rechecking the location, I figured out I did book to much. Unfortunately, I will not be able to go to steven feuerstein session S281918 on monday, because it starts at the same time as tom session ends.

I had a difficult choice for tuesday :
– S281213 (now full) Developing a Successful Database Backup and Recovery Strategy: Customer Experiences with Jonathan Intner, oracle specialist at Novartis
– S283505 Battle-Tested Best Practices for Provisioning, Managing, and Monitoring Oracle Real Application Clusters Environments with Arup Nanda, Oracle Ace
– S283870 Flexframe for Oracle: Adaptive Infrastructure Solution for Oracle Database and Oracle Application Server 10g

I chose the third one, because I am managing a workshop with RAC on Egenera BladeFrame one week later in Zürich, so it will be very useful material.

I also managed to add three expert sessions at the otn lounge to my schedule :

Steven Feuerstein, Arup Nanda and Tom Kyte with APC

SOUG last week

The documents from last SOUG special interest group with Tom Kyte are now available for downloadable on the homepage (under history).

The day started with Sven Vetter, who talked about SLA management pack for Enterprise Manager Grid Control 10gR2. We saw also how to define a custom shell script and let OEM generate a graphic over time. He talked about “beacons”, a kind of interface to define application interaction with the grid.

Than came an interesting presentation about Performance Tuning from Patrick Schwanke. He talked about views like DBA_HIST_SQLTEXT and DBA_HIST_SQLBIND. I also learnt the virtual index trick : There is a parameter called _use_nosegment_indexes with allow you to generate EXPLAIN PLAN for a segment without segment. There is a magic keyword called NOSEGMENT. This mechanism is used internally by Oracle Tuning Pack, and by other tools like the ones from Quest.

SQL> create table t as select * from all_objects;

Table created.

SQL> create index i on t(object_id) NOSEGMENT;

Index created.

SQL> alter session set "_use_nosegment_indexes"=true;

Session altered.

SQL> select * from t where object_id=1;

Execution Plan
Plan hash value: 1984501315

| Id  | Operation                   | Name |
|   0 | SELECT STATEMENT            |      |
|*  2 |   INDEX RANGE SCAN          | I    |

Predicate Information (identified by operation id):

   2 - access("OBJECT_ID"=1)

SQL> alter session set "_use_nosegment_indexes"=false;

Session altered.

SQL> select * from t where object_id=1;

Execution Plan
Plan hash value: 2153619298

| Id  | Operation         | Name |
|   0 | SELECT STATEMENT  |      |
|*  1 |  TABLE ACCESS FULL| T    |

Predicate Information (identified by operation id):

   1 - filter("OBJECT_ID"=1)

   - dynamic sampling used for this statement

Later, Tim Polland talked about Texas Memory and the RAMdisks. It is a piece of hardware which use memory instead of disk to store the datas. The hardware has a kind of RAID mechanism for consistency, and it is not supposed to lose data (!). It is quite different from a typical disk cache, because it does not “cache” the datas, it just saves them in memory. Well, you are not supposed to use this to stores a terabyte of datas, but you could store only your most time-critical datas there, for example move a table to a different tablespace, and store that table on the RAMDISK, also the redo logs, and eventually the undo/system tablespaces too. The RAMDisk hardware is not specific for Oracle. Some “entry-level” ramdisk can offer something like 400 Megabytes/seconds data with nanoseconds access time. Well, it is the speed of memory, so it is also the price of memory. So do not expect to have this at home to play your favorite games !

In the afternoon, we started with Oracle Benchmarks with Manfred Drozd. Very interesting presentation, which shows that there is no “quick” benchmark, but a benchmark should consider I/O, PL/SQL performance, amount of disks, volume manager/ASM, cpu types/count, OS, architecture, Oracle Version, etc. The performance for select, for insert, for update, all those may differ from one system to another.

Real world example just followed with the presentation of a study case in the swiss post. The comparison was mainly between Sun Solaris on Sparc and Linux on x86_64. Well, there was no Better/Worst answer, you know, like is real world…

Last but not least, Tom Kyte, the “Tom” behind asktom, did talk about Instrumentation and the advantage -the need- of using debugging info, with DBMS_APPLICATION_INFO, with your own debug procedure, with Log4Plsql/Log4J packages. The tools we show were DBMS_MONITOR (10g) and trcsess tool (located in $ORACLE_HOME/bin), DBMS_TRACE and PL/SQL conditional compilation. DBMS_TRACE is usefull for detecting catched exception :

SQL> @?/rdbms/admin/dbmspbt
SQL> @?/rdbms/admin/prvtpbt.plb
SQL> @?/rdbms/admin/tracetab
SQL> exec dbms_trace.set_plsql_trace

PL/SQL procedure successfully completed.

SQL> begin dbms_output.put_line(1/0); 
  exception when others then null; 

PL/SQL procedure successfully completed.

  from plsql_trace_events
  where ERRORSTACK is not null;
---------- --------------------
ORA-01476: divisor is equal to zero

oow2k6 sessions

the sessions I registered with for oracle open world 2006 are the following :

Monday 10:45 : Bryn LLewellyn, PLSQL Manager, will talk about PLSQL future in the next release (11g) : session S281172

Monday 12:30 : Andrew Clarke, Oracle ACE, will talk about unit testing in PL/SQL : session 282112

Monday 15:15 : Thomas Kyte, Oracle ACE and asktom owner, will talk about sql worst practice : session 281206

Monday 16:15 : Steven Feuerstein, Oracle ACE and PL/SQL author, will talk about 10 Things You Should NEVER Do in PL/SQL : session 281918

Tuesday 13:15 : Sue Harper, SQL Developer Product Manager, and Kris Rice, Director of Database Tools Research, will talk about Advanced Database Development with Oracle SQL Developer : session 281142

Tuesday 16:30 : Wim Coekaerts, Oracle ACE and Linux Principal, will talk about Securing Linux for Oracle : session 281224

Thursday 09:30 : Werner Puschitz, Oracle ACE, will talk about Automated Oracle Real Application Clusters Deployment : Session 283479

I have booked a few more sessions, about Virtual Directory, Application Server and Grid Control.

I will be at the OTN lounge to play chess or othello at any time :-)


OpenWorld Connect Group Oracle Blogger

I created a new group in OpenWorld Connect. In San Francisco I would like to meat other people writing and reading blogs.

If you have not actived openworld connect yet, you can update your profile on, than join the Oracle Blogger group at Oracle Blogger

Oracle OpenWorld 2006

I just registered to Oracle OpenWorld in October in San Francisco… Well, I know it is a long long long way to the west, but I will survive again, but this time I will avoid Delta Airlines, they refused to serve me a glass of wine last year in the plane! I am going to take maybe Lufthansa.

As an ACE, I got the Oracle Develop for free this year too. I also took the gold pass, well, waiting hours for keynotes is just too much boring. Since it is the only thing I pay myself (ok wine in the plane too), I guess this 150$ investment is ok.

If available, I will try the Oracle Application Server OCP Beta exam 1Z1-312 in SF. Also, I will ask them why this OCM 10g upgrade is still not available.

oracle partner workshop : database 10g

Last month I had an application server seminar I blogged in SOA . Yesterday it was database 10g day.

First we had exactly the same talk about EMEA marketing, vision, partner and blabla for one hour. I did not expect to hear this comparison between Oracle Database and the C: prompt (origin) and fusion and mswin (ultimative achievement)… I almost told him than the C: prompt was not the first think to appear in MSDOS, but the A: prompt, well, if I will say it next time 😉

We heared about partitioning, parallel query, real application cluster, flashback database, undo management, in about 250 powerpoint slides and not a single line of sql in the whole presentation. Well, not willing to blame the teacher, I just felt losing my time listening to this superficial presentation of oracle database.

About the things I found interesting :
– Using the listener as http server. Especially interesting is to use lsnrctl star to start the listener (just joking).

  (HOST=chltlxlsc1) (PORT=8080)) (presentation=http)

$ lsnrctl star http
LSNRCTL for Linux: Version - Production 

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Starting .../bin/tnslsnr: please wait...

TNSLSNR for Linux: Version - Production
System parameter file is .../network/admin/listener.ora
Log messages written to .../network/log/http.log
( (PORT=8080))  
(presentation=http) (session=raw))

than I should be able with the package DBMS_EPG to administer the website

– An Excel plugin : well, if your marketing department is hooked on excel, there is a plugin which enable you some connectivity within your spreadsheet. You can download it on otn for free on BI Spreadsheet Addin. I did not try it yet, but it did look sexy.

– As last month, the pause has been enjoyable. Fine starters, great cat fish, delicious dessert