I expected OracleXE 11g announcement at OOW 2010. It didn’t happen. Naturally, I start questioning if OracleXE is perhaps a dead end. Hopefully not, according to this good news. I think XE 11g will be released in the first half of 2011. Another good news, according to the same source, is that Oracle will raise DB size limit from 4GB to 10GB. Perhaps 10GB is not much in your part of the World, but I live and work in a small country where almost every official register (Business Register, Register of Territorial Units, Tax register, Population Census etc.) can individually be stored in database with 10GB limit. That doesn’t mean XE should be used casually as a production database for such tiny databases (registers). On the other hand, public sector should strive to save the money when it can — in most cases XE is perfectly suited to support dissemination of such data to public and/or for exchange in data hubs between register owners.

A week passed by since I installed 10.2.0.5 + Patch 2 on a bunch of production servers. Time for a short confession.
Was it trouble free? Of course not. :)

One particular problem hit us pretty hard. At the time of this writing the case is not yet fully resolved nor documented with a repeatable test cases so I can’t give you any proof in the shape of code, I’m afraid you’ll have to believe on my word. :-)

While troubleshooting performance problem of some reports that run well on 10.2.0.3 (elapsed time measured in tens of seconds), but get crazy execution plans after patchset installation (elapsed time measured in hours, if not days!) .

The pattern that I spotted was:

• in all cases complex views are involved in the queries (outer joins, nested selects, unions…you named it),
• roughly half of the views were written using ANSI join syntax, the other half used old-style join syntax,
• performance problem was reported only on queries that selected data from the views having ANSI join syntax, old-style views performed well
• if the problematic view with ANSI join was rewritten to old-style, it performed well

Workarounds that we found:

• rewrite views containing ANSI joins to use old style join syntax,
• hint the views with /*+ NO_QUERY_TRANSFORMATION */ hint,
• disable CBO query transformations at the session level with logon trigger such as:

CREATE OR REPLACE TRIGGER disable_cbo_qt
 AFTER LOGON ON DATABASE
 WHEN (USER IN('APP1','APP2'))
 BEGIN
   -- turn CBO query transformation from LINEAR to OFF
   execute immediate 'alter session set "_optimizer_cost_based_transformation"=off';
 END;
 /

We’ll very likely replace ANSI joins in the views with old-style joins on the long run, at the moment hint was placed in the views. I don’t like the idea to completely turn off query transformation at the session level.

In theory there should be no difference from the performance point of the view between old-style joins vs. ANSI joins. In theory. If you’re using ANSI joins (and there is no reason to be discouraged of using them after reading this post, really!), then at least remember that sometimes is worthwhile trying to refactor poorly performing query having ANSI join with equivalent one written in old-style syntax. I hope Oracle will eliminate such “surprises” in the future.

Today, I got link to the site that allow us to unwrap PL/SQL code with online service (thanks Jože!). If you’re not familiar with PL/SQL unwrapping I would recommend Pete Finnigan famous BlackHat convention 2006 presentation, where he showed the principles to unwrap PL/SQL code. At first, I hesitated to publish link to the site, but when I saw that Google is returning link as a second hit I changed my mind:).

I already blogged about Oracle decision to release SQL Developer Data Modeler as a free product. I would certainly never shell out 3000$ (original Oracle price) for the license of Data Modeler, because the product is simply not worth that much, especially compared to 479$ Data Modeler from Quest, a product that is imho at least as good as Oracle Data Modeler, if not better. Now that Oracle SQL Developer Data Modeler is a free product it certainly deserves to be evaluated. I’ll probably use Oracle Data Modeler in my next pet home project and if I found something worth writing about I’ll publish it here in the comments section. For now I just want a working copy to sit on my desktop, waiting for some free time on my part.

Downloading and installing Data Modeler on my Ubuntu Lucid Lynx was a breeze, because I already downloaded and installed JDK 6 for SQL Developer.

Steps for installing SQL Developer Data Modeler:

$ cd /home/alesk/Downloads
$ sudo unzip datamodeler-2.0.0-584-no-jre.zip -d /oracle
$ sudo chown -R oracle:dba /oracle/datamodeler/
$ cd /oracle/datamodeler
$ sh ./datamodeler.sh
-- the first time you run the script it'll ask you to
-- provide path to jdk installation directory, in my case:
/oracle/jdk1.6.0_21

That’s it. The only thing that remains to be done is to create new desktop launcher. SQL Developer Data Modeler starts surprisingly fast, considerably faster than SQL Developer. Screenshot example:

One of the first thing I checked was for which databases we can generate DDL, the list is much shorter compared to Quest Data Modeler, but for the mainly Oracle shop it’s more than adequate:

This is MY Sticky Note about installation of patchset 10.2.0.5 on Windows x64 (single instance, no database vault), followed by Patch 2 installation. Make sure you read README.html that comes with Patchset, this sticky note is MY note and as such is not a substitute for README.html.

-- Make a backup of database, Oracle Software (ORACLE_HOME) and Oracle inventory
-- Install Patchset 10.2.0.5 binaries.

sql> connect / as sysdba
sql> startup upgrade
sql> spool utlu102i.log
sql> @?\rdbms\admin\utlu102i.sql
sql> spool off

I got two warnings:

**********************************************************************
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
WARNING: --> "shared_pool_size" needs to be increased to at least 201326592
WARNING: --> "java_pool_size" needs to be increased to at least 67108864

sql> shutdown immedite

< fix the problem >

sql> startup upgrade
sql> spool patchset_10205.log
sql> @?\rdbms\admin\catupgrd.sql
sql> spool off

sql> shutdown immediate
sql> startup

sql> @?\rdbms\admin\utlrp.sql

-- check status of components:

set lines 100
column comp_name format a40
column version format a20
column status format a20
SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;

-- RMAN catalog upgrade (if the RMAN catalog is not yet at >= 10.2.0.5)

rman catalog rman/rman@tns
rman> upgrade catalog;

Because of the bug introduced with patchset 10.2.0.5 I postponed deployment of patchset 10.2.0.5 until Oracle released a fix in Patch 1 in mid September 2010, then I saw that Patch 2 (which is also a security bundle CPU October 2010) is planned for mid October 2010 and decided to wait for Patch 2 rather than installing Patch 1.

--
-- MY sticky note for installing Patch 2 on top of 10.2.0.5
-- Again, reading README.HTML is MANDATORY!
-- Download patch# 10155844 (p10155844_10205_MSWIN-x86-64.zip) and 
-- Opatch 10.2.0.5, patch# 6880880 (p6880880_102000_MSWIN-x86-64.zip).
-- Follow instructions in readme for Opatch installation.
--  Patch 2 archive unzip in some temporary directory 
-- (my location of choice was D:\ORACLE\10205_Patch2)
1) Backup database
2) Backup Oracle inventory (C:\Program Files\Oracle\...)
3) shutdown immediate
4) net stop OracleServiceSID
5) net stop OracleORA10TNSListener
6) net stop "Distributed Transaction Coordinator"
7) cd D:\Oracle\10205_Patch2\10155844
8) set PATH=D:\ORACLE\ORA10\Opatch;%PATH%
9) opatch lsinventory
10) opatch apply
11) check log file (D:\ORACLE\ORA10\cfgtoollogs\opatch)
-- post installation steps
12) net start OracleServiceSID
13) cd D:\ORACLE\ORA10\BUNDLE\Patch2
14) sqlplus /nolog
15) connect / as sysdba
16) startup     
17) @catcpu.sql
18) quit
19) check log file (D:\ORACLE\ORA10\cfgtoollogs\catBundle)
20) you can check REGISTRY$HISTORY for Patch 2 installation note
21) optioanl: if catcpu.sql reports invalid objects run utlprp.sql
22) net start OracleORA10TNSListener
23) net start "Distributed Transaction Coordinator"

Everything went smooth during 5 test and one production installment, more are on the way during this week. I’m happy to report that trace files reporting “WARNING:Could not lower the asynch I/O limit” are gone.