SOUG-Tagung 09/2

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

March 18, 2009, Baden-Daettwil
http://www.soug.ch

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

select xmlserialize(document xmlcol) from agenda;

 

12:00

Empfang und Registrierung


SOUG Sekretariat

12:30

Stehlunch
im Foyer Seminarraum
Schottky

13:00

21.
Ordentliche Generalversammlung der SOUG
,
Seminarraum Schottky

Ehrung des SOUG Speaker of the Year 2008, Seminarraum
Schottky

 

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

Track 2:
Oracle
Applications
Moderation Michele
Savino, FI-Solutions AG

14:00



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


Oracle
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
Modellierung

Beat Brunschwiler,
Oracle

14:45



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


Oracle
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

Jean-Claude
Haupfleisch, Oracle

15:30 Pause
(ca. 30′)

16:00



SQL Plan Baseline 



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

Chris Antognini,
Trivadis



Siebel CRM Referenz Projekt



Beispiel einer Siebel CRM Implementierung bei einem Schweizer
Versicherer.

Lukas
Ehrensperger, ec4u

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

date or timestamp literals

Yesterday I commented on a post on the French developer forums http://www.developpez.net about using DATE and TIMESTAMP literals.

A date literal is DATE '2000-01-01'. It is not possible to have a date literal at 6am, like DATE '2000-01-01 06:00:00', the syntax simply does not exist. You can either use TO_DATE function, or if you are really hooked on litterals, maybe DATE '2000-01-01'+INTERVAL '+0 06:00:00' DAY TO SECOND.

One suggestion was to use TIMESTAMP, because TIMESTAMP literals do have time in it.

However, comparing a date with a timestamp will result in an implicit conversion to timestamp, and if the date colum is indexed, the index will not be used.

SQL> create table lsc_t(x number, d date)
Table created.
SQL> create index lsc_i on lsc_t(d)
Index created.
SQL> insert into lsc_t(d) values (to_date('2000-01-01 06:00:00',
  'YYYY-MM-DD HH24:MI:SS'))
1 row created.
SQL> select * from lsc_t where d>=timestamp '2000-01-01 06:00:00'
         X D
---------- -------------------
           2000-01-01 06:00:00
Execution Plan
----------------------------------------------------------
   0       SELECT STATEMENT Optimizer Mode=CHOOSE
   1    0    TABLE ACCESS FULL TGPOWNER.LSC_T
SQL> select * from lsc_t where d>=to_date('2000-01-01 06:00:00',
  'YYYY-MM-DD HH24:MI:SS')
         X D
---------- -------------------
           2000-01-01 06:00:00

Execution Plan
----------------------------------------------------------
   0       SELECT STATEMENT Optimizer Mode=CHOOSE
   1    0    TABLE ACCESS BY INDEX ROWID TGPOWNER.LSC_T
   2    1      INDEX RANGE SCAN TGPOWNER.LSC_I

As shown above, the index is not used when comparing to timestamp.

Why does Oracle convert the date to timestamp ? Because timestamp may contain nanoseconds.

SQL> select * from lsc_t where 
  d>=timestamp '2000-01-01 06:00:00.000000001';

no rows selected

SQL> select * from lsc_t where 
  cast(d as timestamp)>=timestamp '2000-01-01 06:00:00.000000001';

no rows selected

SQL> select * from lsc_t where 
  d>=cast(timestamp '2000-01-01 06:00:00.000000001' as date);

         X D
---------- -------------------
           2000-01-01 06:00:00

Converting to date would deliver wrong result.

Tuning query over database link

I just learnt from a colleague a very useful hint for remote databases. Over a db link, Oracle does not have the necessary statistics to take the right decision.

Here is the test case :
DB01


create database link lsc_link_2 using 'DB02';
create table lsc_small_1 as 
  select rownum x from dual connect by level<100;
alter table lsc_small_1 add primary key(x);
create table lsc_big_1 as 
  select rownum x from dual connect by level<1000000;
alter table lsc_big_1 add primary key(x);

DB02


create table lsc_small_2 as 
  select rownum x from dual connect by level<100;
alter table lsc_small_2 add primary key(x);
create table lsc_big_2 as 
  select rownum x from dual connect by level<1000000;
alter table lsc_big_2 add primary key(x);

and here are the two test queries (run on DB01)


select count(*) from lsc_big_1 b1 
  join lsc_small_2@lsc_link_2 s2 using (x);
  COUNT(*)
----------
        99
Elapsed: 00:00:00.10

select count(*) from lsc_big_2@lsc_link_2 b2 
  join lsc_small_1 s1 using (x);
  COUNT(*)
----------
        99
Elapsed: 00:00:10.31

As shown above, first one is 100x faster. Anything to tune? For sure!

The trick is to execute the join remotely when the remote table is much larger than the local one!


select /*+ DRIVING_SITE(b2) */ count(*) 
  from lsc_big_2@lsc_link_2 b2 
  join lsc_small_1 s1 using (x);
  COUNT(*)
----------
        99
Elapsed: 00:00:00.06

Ref: the DRIVING_SITE hint