What a news! Oracle Buys Sun !

Written by Paweł Barut
What a news: Oracle Buys Sun !! I'm not the first one that blogs about it, but anyway let me express my comments. What does it mean for IT:
1. Oracle software will be installed on almost every PC; Sun Java might be renamed to Oracle Java. It will give wider recognition for Oracle trademark also to people that are not so much engaged with IT Industry.
2. Oracle becomes hardware producer. Well, you might know already HP-Oracle Database Machine (Exadata), but this was joint-venture. Now Oracle will produce hardware and software that runs on it. If everything goes right next "Database Machine" will be fully "Oracle".
3. Sun Solaris is #1 platform for running Oracle, and the second one is Linux. This could mean that rate for Linux implementation in enterprises will be lower, as many companies would choose OS and DB from one vendor. I know that Oracle is also offering Linux, but Oracle sales will be promoting Solaris, as this deals will be better for Oracle.
4. Oracle now owns 2, ehh, 3 databases: Oracle, Berkley XML DB and now MySQL. I could mean that MySQL will get even bettor, or rather it will mean that Oracle will offer MySQL customer to migrate to Oracle. So far Oracle does not have good record for supporting free editions of Databases. We will see...
5. With Oracle-Sun merge, Oracle will be shipping now full stack: hardware,OS, Database, Midlleware, ERM, CRM and many others. Oracle is becoming giant now...

Read also what others have to say:

• Mark Rittman - Oracle Buys Sun - What Does It Mean for Oracle BI&DW?
• Pythian - Oracle Buys Sun
  
• Marco Gralike - Oracle to buy Sun, Ehh MySQL, Ehh Glashfish…
• Tanel Poder - Oracle buys Sun - hooray!
  
• Tim Hall - Oracle Buy Sun Microsystems…
  
• Kevin Closson - Everyone Else is Doing It!
  
• Jaffar - Oracle to acquire SUN?

Keep reading,
Paweł

Caution: FOR loop and CONTINUE in Oracle 11g

Written by Paweł Barut
Friend of mine showed me interesting issue of using CONTINUE Statement. CONTINUE is an new statement in Oracle 11; it allow to skip processing of current iteration of look, and go to begging of next iteration. So here is working sample:

SQL> set serveroutput on
SQL> begin
  2    dbms_output.put_line('=== START');
  3    for i in 1..15 loop
  4      dbms_output.put_line('before:'||i);
  5      if mod(i, 5) = 0 then
  6        dbms_output.put_line('CONTINUE');
  7        continue;
  8      end if;
  9      dbms_output.put_line('after:'||i);
 10    end loop;
 11    dbms_output.put_line('=== STOP');
 12  end;
 13  /
=== START
before:1
after:1
before:2
after:2
before:3
after:3
before:4
after:4
before:5
CONTINUE
before:6
after:6
before:7
after:7
before:8
after:8
before:9
after:9
before:10
CONTINUE
before:11
after:11
before:12
after:12
before:13
after:13
before:14
after:14
before:15
CONTINUE
=== STOP

But when we use CONTINUE in loop, that is based on implicit cursor, it gives wrong results:

SQL> begin
  2    dbms_output.put_line('=== START');
  3    for r in (select level num from dual connect by level <= 115) loop
  4      dbms_output.put_line('before:'||r.num);
  5      if mod(r.num, 5) = 0 then
  6        dbms_output.put_line('CONTINUE');
  7        continue;
  8      end if;
  9      dbms_output.put_line('after:'||r.num);
 10    end loop;
 11    dbms_output.put_line('=== STOP');
 12  end;
 13  /
=== START
before:1
after:1
before:2
after:2
before:3
after:3
before:4
after:4
before:5
CONTINUE
before:101
after:101
before:102
after:102
before:103
after:103
before:104
after:104
before:105
CONTINUE
=== STOP

In that case CONTINUE statement goes to iteration no 101 instead of going to iteration 6, as expected!!
To make the long story short, I've experiment with few setting and found that setting PLSQL_OPTIMIZE_LEVEL to 1 this block runs as expected:

SQL> ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 1;
SQL> begin
  2    dbms_output.put_line('=== START');
  3     for r in (select level num from dual connect by level <= 115) loop
  4       dbms_output.put_line('before:'||r.num);
  5       if mod(r.num, 5) = 0 then
  6         dbms_output.put_line('CONTINUE');
  7         continue;
  8       end if;
  9       dbms_output.put_line('after:'||r.num);
 10     end loop;
 11     dbms_output.put_line('=== STOP');
 12  end;
 13  /
=== START
before:1
after:1
before:2
after:2
before:3
after:3
before:4
after:4
before:5
CONTINUE
before:6
after:6
[... many lines cutted out from output...]
after:99
before:100
CONTINUE
before:101
after:101
before:102
after:102
before:103
after:103
before:104
after:104
before:105
CONTINUE
before:106
[... many lines cutted out from output...]
after:114
before:115
CONTINUE
=== STOP

For me it looks as bug in PL/SQL Optimizer on level 2. In that case PL/SQL does bulk collect for 100 rows when FOR LOOP with cursor is used. Seems that CONTINUE in that case causes to fetch next portion of records instead of taking next record from buffer. I've reported bug to Oracle Support, but until it will get fixed, be aware of possible wrong results.
This was tested on Oracle 11.1.0.6 and 11.1.0.7 Enterprise Edition.

Keep reading,
Paweł

--
Related Articles on Paweł Barut blog:

• Comments in SQL*Plus
• Dynamically set name of spool file in SQL*Plus
• Table that cannot be deleted

• ORA-03297 on empty datafile
• Flashback Table and Materialized View – not working together

Book Review: Processing XML documents with Oracle JDeveloper 11g

Written by Paweł Barut
This time I would like to recommend a book Processing XML documents with Oracle JDeveloper 11g published by PacktPub.com. As I have some experience with XML processing inside Oracle DB (XMLDB) I was lacking the knowledge of XML manipulation in Java, especially using Oracle JDeveloper. This book is rather not for beginners - you should have basic knowledge of what is XML, at least theoretically. From the book you can learn how to manage XML Schemas in JDeveloper, then write XML documents based on those schema and finally validate them in JDeveloper or write Java code to validate it automatically. Chapters that I value much are those describing how to generate PDF documents. There are two methods described - one using Apache FOP API , and Oracle XML Publisher. As I was not generating PDF's this way so far, I found those 2 chapters very interesting. There is also chapter on generatin Excel by transformation of XML using Apache HSSF API .

Book is very practical. If you want to start with subjects described above then this book is for you. Of course it does not mean that you do not have to study documentation. However, it will be easier for you to make first steps. Beside describing Oracle JDeveloper, author also shows how to install and use Oracle Berkley XML DB to store, query and update XML Documents - using command line and XML DB API.

I especially recommend eBook version. All URLs in eBook version are active, so linking to web pages are very simple. Also coping sample code is easy. This book contains many practical and usefull tips on XML processing and tools as well. So if those XML aspects are in your interest, then it's definitelly good to invest in this book.

Keep reading,
Paweł

--
Related Articles on Paweł Barut blog:

• ORA-00904: "XMLROOT": invalid identifier
• Useful links on Oracle XML DB
• Solving ORA-22905: cannot access rows from a non-nested table item
• Oracle XMLType: exctractvalue vs. extract
• ORA-30625 and XMLType

Multiplying texts and null effect

Written by Paweł Barut
This time short notice on side effect of implicit type conversion ans short expression evaluation. Let me show this on example - try this statement:

SQL> select 'a'*'b' from dual;
select 'a'*'b' from dual
       *
ERROR at line 1:
ORA-01722: invalid number

As expected we get error. It is due to fact that multiplexing operator (*) is expecting numbers on both sides, an implicit conversion takes place. However this statement:

SQL> select ''*'b' from dual;

    ''*'B'
----------

does not give error. How is it possible? In oracle empty string is in fact an null. null multiplied by any number gives null, so due to optimization oracle decided to evaluate this expression to null.
As you know also 0 (zero) multiplied by any number gives zero. So let's test it:

SQL> select '0'*'b' from dual;
select '0'*'b' from dual
           *
ERROR at line 1:
ORA-01722: invalid number

In that case Oracle did not optimized evaluation, and decided to make full calculation. I do not know the reason for different behaviour but is repeatable (at least on instances I've access to).
My recommendation: always make sure that you provide data in proper data-type. Make explicit data conversion using to_number, to_date or to_char functions.

Keep reading,
Paweł

--
Related Articles on Paweł Barut blog:

• UTL_MATCH - String Similarity in Oracle
• Rolling Back the DDL
• DB Link to Oracle 11g
• How to shrink Oracle Context Index
• Using Named Parameters within SQL

OOW2008 day 5 – It’s A Wrap

Written by Paweł Barut
Before I start to summarize my Day 5 at Oracle OpenWorld, I would like to add few words to day 4 (Wednesday).

Managing Very, Very Large XML Documents with Oracle XML Database
It was very good session. It one of those where practical experience was shared. Presenters showed step by step how to load very large XML files to DB:

• Setting up XML schema
• Schema annotation technique and few directives
• Direct Insert Store for XML
• Differences with loading XML into XML DB in 10.2g and 11g

The Appreciation Event.
It was very nice concert on Treasure Island. I’ve especially liked Seal. Beside that there was lot of good food and drinks.

Day 5

I will start with session Oracle’s New Database Accelerator: Query Processing Revolutionized. As I’ve expected it was related to announcement made yesterday. My yesterday’s description was not perfect. Now I will try to fix this. First of all we have 2 new machines. But one them is included in the second one.
Oracle Exodata Storage Server - it is hardware from HP: 2 Intel quad-core processors, 12 disks (300GB, 15RPM or 750 GB, 10 RPM) with disk controller optimized for best bandwidth and 2 InfiniBand connectors to connect to external equipment. The code for this product is HP DL180G5 (at least that was on one of slides). This computer is sold with preinstalled Oracle Enterprise Linux 5.1. The main role of this machine is to store database files. It cannot be used to store normal files.
The second hardware is HP-Oracle Exodata DB Machine - This one is rack box equipped with 14 Oracle Exodata Storage Servers and 8 DB Servers each with 8 Intel processors. On those DB Server runs Oracle Enterprise Linux 5.1 and Oracle RDBMS 11g (11.1.0.7). Even more – 6 such DB Machines can be connected into cluster.
Where is the revolution? In the way Oracle DB communicates with storage. There is new protocol iDB that allows to push query predicated down to storage. With this, number of data transferred from Storage to DB Server is minimized. This feature is called Smart Scan. It can be leveraged only when full table (or partition) scans occurs. And it still keeps all read consistency.
And here is technical spec from Oracle.

And short on my other sessions: Oracle Database Performance on Flash Drives
Very interesting session showing results of different approaches for using Flash drives. As an conclusion there was presented formula, when Flash drives can help with performance, and when it is better to stay with fast rotating drives, and when even with low cost but high capacity drives. As an side note when we consider power usage, Flash drives can be even more economic then traditional rotating drives.

Oracle ACE Director: Birds-of-a-Feather Tips and Techniques
Session lead by Oracle ACE’s: Lewis Cunningan, Arup Nunda, Edie Awad, Mark Rittman, Tim Hall, Hans Forbich and Bradley Brown. The session was Q&A style. ACE’s were answering questions based on own experience, and that is sometimes different that Oracle’s official recommendation.

Real-World XML DB Examples from Oracle Support
This was rather chaotic session, and did not gave me useful information- IHMO waste of time.

And the last session by Tom Kyte Reorganizing Objects
Tom have done great job showing different myths about when DBA’s should reorganize tables and indexes. It was really Great speech. Might be there was too much material for an 1h session, and everything was shown little bit in an hurry.

The day has ended with small party It’s A WRAP
While writing this I’m sitting in hotel and watching Fireworks over the San Francisco Port. Tomorrow I’m leaving San Francisco for 18h trip back to home.

Cheers,
Paweł
--
Related Articles on Paweł Barut blog:

• Oracle OpenWorld 2008
• Preparing for Oracle OpenWorld 2008
• Arriving San Francisco – OOW2008 day 1.
• OOW2008 day 2 – Oracle Beehive
• OOW2008 day 3
• OOW2008 day 4 – HP-Oracle Exadata Server Announcement = Extreme Performance
• OOW2008 day 5 – It’s A Wrap

OOW2008 day 4 – HP-Oracle Exadata Server Announcement = Extreme Performance

Written by Paweł Barut
Today is very busy day for me. It’s just after Larry Ellison keynote on which first Oracle Hardware was presented. I’m a little big skeptic if Oracle is realy going for hardware business. It is rather that Oracle had great idea how to solve problems with really big databases and growing demand for storage, and joined forces with HP to create new quality in data processing. So, how it looks: in one box we have 2 intel 4 core processors and 12 disks as storage – it is called The Exadata Programmable Storage Server. This machine is not storage, and is not pure DB Server as well. It can process queries (so it is DB), and it stores Data (so it is Storage). But it needs separate DB Server to work at full performance. How it works: DB Servers receives request for data. It then retries data from Exadata Storage Server, but data get initially filtered, so number of data transferred from Storage Server to DB Server is reduced. It allows much better overall performance.
At least this is my understanding. I will go also to DemoGround to get direct look at this machine, and more detailed specification.

Today I’ve also participated in few sessions.
Soup-to-Nuts RAD Development Using Oracle SQL Developer and Oracle Application Express It was quick show how to create simple application using APEX and SQL Developer. It focused on modeling capabilities of SQL Dev, and integration between APEX and SQL Dev. It was shown, how to view APEX objects in SQL Dev, and how to leverage this integration. There was also presentation of new functionality in APEX to migrate Oracle Forms to APEX.

Agile Database Testing Techniques (IOUG) This was very interesting session giving practical inside on how to organize unit tests in DB, how to validate that upgrade scripts run successfully, and how to prepare DB environment for Daily Builds. Presenter shared his real world experience and this was the biggest value of this session.

SQL Tuning Roundtable with the Experts This one was rather boring, as topics and answers were almost exactly the same as on session “Inside Oracle Database 11g Optimizer: Removing the Mystery” that I was participating yesterday.

Now I’m sitting in OCP Louge, and in few minutes I’m going for last session Managing Very, Very Large XML Documents with Oracle XML Database and then for The Appreciation Event.

Cheers,
Paweł

BTW. This is my post # 100.
--
Related Articles on Paweł Barut blog:

• Oracle OpenWorld 2008
• Preparing for Oracle OpenWorld 2008
• Arriving San Francisco – OOW2008 day 1.
• OOW2008 day 2 – Oracle Beehive
• OOW2008 day 3
• OOW2008 day 4 – HP-Oracle Exadata Server Announcement = Extreme Performance
• OOW2008 day 5 – It’s A Wrap

OOW2008 day 3

Written by Paweł Barut
This day was quite good for me. I’ve finally overcome problems with jet-lag.

I’ve started day on session “Inside Oracle Database 11g Optimizer: Removing the Mystery”. It was very good session explaining how new features in optimizer works. It provided inside on Plan Profiles, improved bind variable picking, improved performance for statistics collection and new statistics for correlated columns.

My next session was not exactly what I was expecting. “Global-Scale Web 2.0 Data Platforms: Sharding with Oracle Database”. I was expecting technical inside, but it mostly was marketing speech on “why Oracle is better then MySQL”. Break between sessions I’ve used to ride on Cable Car:


I’ve ended day on session “Top 10 Things You Wanted to know about ASM”. As I were not using ASM yet, it was good overview of features, and best practices for using ASM. Now, I’m going to rest to be prepared for next day, and big keynote by Larry Ellison.

Cheers,
Paweł
--
Related Articles on Paweł Barut blog:

• Oracle OpenWorld 2008
• Preparing for Oracle OpenWorld 2008
• Arriving San Francisco – OOW2008 day 1.
• OOW2008 day 2 – Oracle Beehive
• OOW2008 day 3
• OOW2008 day 4 – HP-Oracle Exadata Server Announcement = Extreme Performance
• OOW2008 day 5 – It’s A Wrap