There seems to be a bug in DBD::Oracle’s execute_array when working with 11g.

If you tell DBD::Oracle to autocommit, it seems that in 11g this commit will not take place when an error occurs during the processing of one of the tuples that you passed into execute_array. So given this table:

CREATE TABLE test_array (
	    row_1 INTEGER NOT NULL,
	    row_2 INTEGER NOT NULL,
	    row_3 INTEGER NOT NULL)

the following code will result in no records being added to the db.

# create a database handle
my $dbh = DBI->connect('dbi:Oracle:','xxx@xxx','xx',{
	RaiseError			=> 1,
	PrintError			=> 0,
	ShowErrorStatement		=> 1,
	AutoCommit			=> 1

}) || die "Unable to establish connection with Oracle server: $DBI::errstr";

my $rv;
my @var1         = (1,1,1,1,1,1,1,1,1,1);
my @var2         = (2,2,2,2,'s',2,2,2,2,2);
my @var3         = (3,3,3,3,3,3,3,3,3,3);

my $tuple_status = [];
my $dumped ;
my $rows = [];

$sth->execute_array(
        {ArrayTupleStatus => $tuple_status},
          \@var1,
          \@var2,
          \@var2,
   );

The workaround is to set AutoCommit => 0 and then use $dbh-->commit() after the statement.

So far, the jury is out whether this is a bug only in 11g, but as this code works correctly in 9 and 10 (it adds the 9 rows), it seems likely. I have noted the bug here: http://forums.oracle.com/forums/thread.jspa?messageID=2663832#2663832.

As some of you probably already noticed, there was a thread on AskTom discussing the scalability tests I did back in 2007. You are welcome to read the entire thread, but in a nutshell, Tom Kyte claimed that my tests did not reflect how one would use the result cache in the real world.

What is “real world?”

Of course, the important question is whether I tested a feature in a way it was never designed to be used, or whether someone is just trying to make an excuse for poor scalability results by defining “real world” in a way that makes my tests inappropriate.

A new feature

What do you do, then, you first see a new feature? You read about it in the documentation, and then you test it in order to compare what you have read with what you have in reality.

What the documentation tells us

Open the Performance Tuning Guide and go to 7.3.1.4 Result Cache Concepts:

When these queries and functions are executed repeatedly, the results are retrieved directly from the cache memory. This results in a faster response time. The cached results stored become invalid when data in the dependent database objects is modified. The use of the result cache is a database-wide decision.

All it says is that you have to have repeatedly-executed functions and queries to get faster response time. It says nothing about what kind of queries or functions. It also suggests that the result cache should be used database-wide or shouldn’t be used at all (which is perfectly sound according to Jonathan Lewis’s Rules for Hinting).

Now skip up to 7.3.2.7 Use of Result Cache:

OLTP applications can benefit significantly from the use of the result cache. The benefits highly depend on the application. Consider the use of the PL/SQL function result cache and the SQL query result cache when evaluating whether your application can benefit from the result cache.

It clearly says that result cache is perfectly appropriate for OLTP applications. They leave a backdoor with the words, “depend on the application” but, yet again, they say nothing about what kind of OLTP applications.

(more…)

After our last post about installing Oracle 11g on Ubuntu 7.10 (November, 6^th), and considering Ubuntu 8.04 LTS was released on April 21^st, I spent some time reviewing and putting together this new HOWTO for the installation.

Please note: I’ve used the x86 server version of Ubuntu 8.04, but the same steps should work without any problems for the Desktop version. Also notice that this whole procedure can easily take over six hours to complete, so don’t complain I didn’t warn you!

So, let’s get started, shall we?

Step One

Get the Ubuntu Linux 8.04 Hardy Heron (x86, 32-bit) image here, burn it, and install on any box you like. The only remark on the installation is that you should ask the installer to install an OpenSSH server at the end of the installation, since we’ll perform all the steps on this procedure remotely.

I’m not sure about the minimum requirements for the server, as, the last time I checked, running Oracle on Ubuntu is not officially supported by Oracle. In case you’re wondering, however, I’m using an x86 Pentium-like machine with 512M of RAM.

Step Two

Download Oracle 11g for Linux (x86, 32-bit).

(more…)

After setting up dg4odbc to connect to a SQL Server database (read ‘Tips on Using dg4odbc on 64-bit Linux’), I spent last week trying to get refreshes of materialized views to work in 11g, using the same DDL as in 8i. I was very frustrated to see the refreshes break in 11g.

The DDL gives no obvious clues. The remote table:

desc MYTABLE@SQLSERVER
Name                           Null?    Type
------------------------------ -------- ----------------------------
MY_ID                          NOT NULL NUMBER(5)
MY_DESC                        NOT NULL VARCHAR2(40)

SQL> create materialized view MV_MYTABLE
2 as select *
3 FROM   MYTABLE@SQLSERVER
4 WHERE  MY_ID IS NOT NULL
5 AND    MY_DESC IS NOT NULL;

Materialized view created.

SQL> begin
2  DBMS_SNAPSHOT.REFRESH( 'MV_MYTABLE','C');
3  end;
4  /
begin
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01400: cannot insert NULL into (%s)
ORA-02063: preceding line from SQLSERVER
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2537
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2743
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2712
ORA-06512: at line 2

The reason, it turns out, is the ANSI-compliance of dg4odbc. The remote table has a row where the column MY_DESC is storing a '' (a zero-length string).

SQL Server treats the zero-length string as different from NULL. Oracle 11g treats zero-length strings as the same as NULL. (more…)

I’ve recently moved to Ubuntu Linux, and this post describes my attempt to play around with Oracle ASM on Ubuntu. For this demonstration, I used Oracle 11.1.0.6 on Gutsy Gibbon. I hope it will be useful to somebody out there.

Important Notice: What I describe below is among the worst thing you can ever do with ASM. You can use it to play around but never use it with anything other than test data. If you lose something because of me, you’ll be the only one to blame !

Question #1: How do you simulate a disk from a file?

(See here for more details.)

If you have a free partition or disk to be used as an ASM disk, just skip this step. If you don’t, you can create a file with the dd command and create a device that actually loops to the file with the losetup command.

Let’s assume you’ve created a directory named /asmdisks (and you have write access to it). Run the command below to create a file named disk1 that is 3GB in size:

$ dd if=/dev/zero of=/asmdisks/disk1 bs=1024k count=3072
3072+0 records in
3072+0 records out
3221225472 bytes (3.2 GB) copied, 80.9113 seconds, 39.8 MB/s

Once you’ve created the file, map it to a device named loopN in /dev. You can list the used loop devices with the losetup -a command.

Once you’ve made sure the one you plan to used is free, e.g. /dev/loop1, you can map the device to the file with the following commands (you have to be root) :

# losetup /dev/loop1 /asmdisks/disk1
# losetup -a
/dev/loop1: [0802]:7438407 (/asmdisks/disk1)

Question #2: How do you create an interface to the disk that is usable by ASM?

(more…)

I have been using this SQL*Plus command for a while now:

SQL> show spparameter service_names

SID      NAME          TYPE        VALUE
-------- ------------- ----------- ---------
*        service_names string      REDX

And guess what ?

• The right syntax is actually show spparameters (with a “s” at the end of it).
• It’s an 11g New Feature!

It doesn’t work with 10g:

SQL> show spparameters service_names

SP2-0735: unknown SHOW option beginning "spparamete..."
SP2-0735: unknown SHOW option beginning "service_na..."

I thought MySQL was trying to become as big as Oracle but it could be that Oracle is trying to become as cool as MySQL — it has had something like this for a long time! Now Oracle should add SHOW TABLES too.

Anyway, this is really 11g’s most useful feature so far, though.

While perusing the Oracle 11g Data Pump documents recently, I noticed a new parameter that was introduced in Oracle 10g, but I had missed it there. The parameter is TABLE_EXISTS_ACTION, and it applies only to the Data Pump Import.

Basically, this feature allows you to decide how to handle importing data if a table already exists. The options are SKIP (defaut), APPEND, TRUNCATE, and REPLACE. Do these parameters look familiar? Possibly reminiscent of the SQL*Loader parameters (INTO TABLE x INSERT/REPLACE/TRUNCATE/APPEND)? They are very similar and they work the same way, with the exception of SKIP.

Here’s an explanation of each option.

• SKIP: The default value for TABLE_EXISTS_ACTION. If the table exists, then SKIP will cause the table (and any related data) to not be loaded. The table will be skipped.
• TRUNCATE: This will truncate the data in the table and load the data. If the table already exists, no metadata will be applied.
• APPEND: This will append the data to the end of the table. Again, if the table already exists, no metadata will be applied.
• REPLACE: This will drop the existing object, recreate the object with the information from the dumpfile, and then load the data.

Let’s create a simple test case to demonstrate. (more…)

This will be the final post in my series on Result Caches. In my previous article, I had already got almost everything. Almost — four CPUs (cores) were still not enough to saturate the single latch. As you’ve probably already guessed, today we are going with an eight-way test.

Please note that today’s numbers are different since I’m using an entirely different hardware platform. While the four-way tests were done on a 2.4GHz Core 2 Quad box, today’s eight-way tests were done using four dual core Itanium 2 CPUs running at 1.1GHz.

Let’s take a look at the results:

I made a nice-looking graph from this:

(more…)

This was the presentation I gave at Open World 2007. It went pretty well, judging by how full the room was. I estimate more than 300 people attended.

Although the title may sound a bit like a sales pitch, the content is more substantial. It covers the features that are ready to use out-of-the-box, things that will make your life easier from day one, without any kind of “implementation” procedure. One-line changes do not count as implementation.

I was please to recognize a few of Pythian’s clients in the crowd. They came to see me after the session to say how much they liked it. Judging by how lively the audience was, especially in the wake of the big conference party, I would say most people enjoyed it.

I saw a few people take pictures of my slides, and had some ask me for the slides. As promised, here they are: 11g: The Perfection of a Masterpiece.

I will next be going to the UK Oracle User Group Conference in December giving my presentation on memory. I look forward to seeing you there.

I have recently stumbled upon V$SESSION_CONNECT_INFO view and discovered that it provides interesting information about client-side software and settings.

Using this view in Oracle 11g you can simplify collecting some statistics about database clients. Here is what can be extracted:

• Version of client libraries
• Type of OCI library used (standard OCI, different instant clients and etc.) but no JDBC support it seems
• Client characterset (new in 11g – doesn’t work for pre-11.1 clients and for JDBC thin)
• Authentication type (username+password, OS based, proxy and etc.)

How many times have you been in the situation when you need to know certain attributes of your clients to evaluate impact of a coming change of a bug you just hit? Unless the environment is very simplistic or just recently setup, there is usually no easy way to discover every client driver used to connect. This view can workaround absence of standard policies and documentation, procedure violations and you can confidently determine which client versions are where. You can even setup monitoring and pro-actively generate an alert when violations are detected which would be my preferred way.

If you decide to use this feature, you might want to create an AFTER LOGON trigger – sampling of could be not enough to catch short-living sessions.

I haven’t used this view myself and I’m writing this on the plane as I’ve just come across it in the documentation and though that this rather unknown feature would be useful on the blog.

It’d be nice to have that info available in audit views but I couldn’t find it in DBA_AUDIT_SESSION and DBA_AUDIT_TRAIL. DBA_AUDIT_TRAIL has just column COMMENT_TEXT where we can see authentication type for session records.

Oh… which plane am I on? That deserves a separate blog post. Stay tuned!

PS: Oh… I’ve seen a ghost of Dave Ensor now on the plane… It’s probably not such a good idea to read Oracle manuals on the plane - it was just a man with very similar face profile. Yeah, I better switch to a movie then!

PPS: Actually, it took me few days to post this entry. Shame on me but better sooner then later… Oh… I mean better later than never!