Pythian Group

When creating an index, Oracle versions 10g and above automatically compute optimizer statistics. And even before that, 9i had a COMPUTE STATISTICS clause to accomplish the same thing. Not only does it save the time and effort of running DBMS_STATS, but it also saves the disk I/O involved in such an operation, since all rows are available in the course of index creation.

Now why can’t this happen on a CREATE TABLE AS SELECT command? Most if not all of the statistics gathering steps (high/low value, number of distinct values, and even possibly histograms) can be gathered over the course of a simple table read, which is happening anyway.

Oracle 12.1 maybe?

When news of the April 2010 PSU for Oracle 11.2 came out, I was excited to see it, since it marked the first non-one-off patch release for the 11.2 database software. I happened to have an 11gR2 test system running on 11gR2 ASM via standalone Grid Infrastructure. I applied PSU 9352237 to the DBMS home and fired it up, only to see the folly of my ways when any ASM file operations like disk resizing (or auto-extending) failed with ORA-1653. This was due to the DBMS component now having a higher version number than the ASM component, which ASM does not allow. The Grid Infrastucture PSU would need to be applied to bring the ASM component up to snuff, but that patch (9343627) was, at that time, only “announced” with no ETA. Alas, the patch was rolled back and we continued testing without it.

Then this week I check again and saw that PSU 9343627 was released and gave it a whirl. I was a little confused when the README seemed to contain a lot of instructions that always assumed it to be on a clustered, RAC install. My setup was a single-instance Grid Infrastructure installation just to provide ASM. I soon met problem upon problem when going through first this setup step: Read the rest of this entry . . .

The process for applying a patch on top of the CRS, or now called, the Grid Infrastructure, has changed from what we used to do on 11gR1 and prior releases.

The patch I had recently applied was in order to resolve the Oracle bug “11.2.0.1 ONS CORE DUMP or High Resource Usage [ID 988795.1]“.

Database name: TEST
Instance Names: TEST1, TEST2
Grid Infrastructure Home: /u02/app/11.2.0/grid/bin (non-share home)
Grid Infrastructure Home Owner: oracle

Due to the fact that the patch doesn’t require full downtime and could be applied on a rolling basis, the plan below is to be executed on each node at time. Read the rest of this entry . . .

I have successfully compiled and installed DBD::Oracle on Windows 2008 Server 64bit operating system today.

I used the latest version of DBD::Oracle 1.24, version 11.2.0.1.0 for 64bit Windows of Oracle’s
Instant Client Package – Basic along with the Instant Client Package – SQL*Plus and finally the Instant Client Package – SDK.

To get it to make and compile correctly I had to download Microsoft’s Visual Studio Ultimate

which should contain all the files you need. It is rather portly at 2+gb so you might want to grab lunch while you are downloading it.

After all the above downloading DBD::Oracle installed right out of the box.

All one has to do is select ‘Start Menu->All Programs->Microsoft Visual Studio 2010->Visual Studio Tools->Visual Studio x64 Win64 Command Prompt (2010)’
which will open a good old ‘dos’ window.

At this point CD to the directory where you downloaded DBD::Oracle

     c:\DBD-Oracle

then set your ‘ORACLE_HOME to the Instant Client directory

     c:\DBD-Oracle set ORACLE_HOME=c:\IC_11

you should also set your NLS like this

     c:\DBD-Oracle set NLS_LANG=.WE8ISO8859P15

Once the above setting are done do a

     c:\DBD-Oracle perl Makefile.PL

and then a

     c:\DBD-Oracle nmake install

Which will produce a whole of warnings (these you can ignore, as they do not seem to effect DBD::Oracle at all) and near the end it should output something like this;

     Generating code
     Finished generating code
     if exist blib\arch\auto\DBD\Oracle\Oracle.dll.manifest mt -nologo -manifest blib\arch\auto\DBD\Oracle\Oracle.dll.manifest -outputresource:blib\arch\auto
\DBD\Oracle\Oracle.dll;2
     if exist blib\arch\auto\DBD\Oracle\Oracle.dll.manifest del blib\arch\auto\DBD\Oracle\Oracle.dll.manifest
     C:\Perl64\bin\perl.exe -MExtUtils::Command -e "chmod" -- 755 blib\arch\auto\DBD\Oracle\Oracle.dll
     C:\Perl64\bin\perl.exe -MExtUtils::Command -e "cp" -- Oracle.bs blib\arch\auto\DBD\Oracle\Oracle.bs
     C:\Perl64\bin\perl.exe -MExtUtils::Command -e "chmod" -- 644 blib\arch\auto\DBD\Oracle\Oracle.bs
     C:\Perl64\bin\perl.exe "-Iblib\arch" "-Iblib\lib" ora_explain.PL ora_explain
Extracted ora_explain from ora_explain.PL with variable substitutions.
     C:\Perl64\bin\perl.exe -MExtUtils::Command -e "cp" -- ora_explain blib\script\ora_explain
        pl2bat.bat blib\script\ora_explain

At this point you are all done.

Well almost.

It is important that you test your code before you install but you will have to set a few things up first to get it to fully test correctly.

You will need a TNSNAMES.ORA file that points to a valid DB in the Instant Client Directory

Next you will need to set the ORACLE_USER_ID to a valid user

     c:\DBD-Oracle set ORACLE_USER_ID=system/system@XE

You will have to set up TNS_ADMIN to point to the Instant Client Directory

     c:\DBD-Oracle set TNS_ADMIN=c:\IC_11

Most importantly you will have to add the Instant Client directory to your path like this

     c:\DBD-Oracle path = c:\IC_11;%path%

If you do not do this step you will run into the dreaded

Can’t load ‘C:/Perl/lib/auto/DBD/Oracle/Oracle.dll’ for module DBD::Oracle: load_file:%1 is not a valid Win32 application at C:/Perl/lib/DynaLoader.pm line 202.

Error later on after the compile when you try to use DBD::Oracle.

What is actually going on is that Perl cannot find oci.dll (or one of the other .dlls it needs to run) the

C:/Perl/lib/auto/DBD/Oracle/Oracle.dll’ and the DynaLoader error

is just a false trail as perl is very limited in what it Windows errors it can report on. For more complet info on this sort of error check out this page;

Oracle Troubleshooter HOWTO

by Alexander Foken. It is rather dated but the facts of why perl did not find a dll are still valid.

now you can do this

     c:\DBD-Oracle nmake test

and all the tests should run and it will report.

Finally simply do a

     c:\DBD-Oracle nmake install

and you are all set.

That is about it.

At this point you might want to add the Instant Client directory permanently to your path so you will not run into the Dynaloader error again.

As well you do not need to keep Visual Studio around to use DBD::Oracle so you can uninstall that as well.

Stats overflow in Oracle is certainly something you should keep an eye out for, however, sometimes an overflow comes too early (and too unexpectedly).

Recently I’ve been puzzled with the performance difference observed between 11GR1 and 11GR2. As part of the investigation I’ve decided to compare session stats from both 11GR1 and 11GR2 and found that there is a big difference for session cursor cache hits. But the number of session cursor cache hits I’ve been observing in 11GR2 just didn’t made any sense while being absolutely fine in 11GR1. I’ve been running through a relatively low number of iterations and every time it kept coming back with some nonsensical number.

An overflow was certainly a possibility, though in 2010, it’s not like someone should be counting every bit especially if they didn’t in 11GR1! Anyway it was worth a shot so I’ve decided to check it out…

SQL> select statistic# from v$statname where name = 'session cursor cache hits';

STATISTIC#
----------
       498

SQL> create table z_t as select 1 n from dual;

Table created

SQL> set serveroutput on
SQL> declare
  2  	l_n1	number:=0;
  3  	l_n2	number:=0;
  4  begin
  5  	loop
  6  		for cur in (select n from z_t)
  7  		loop
  8  			null;
  9  		end loop;
 10
 11  		select value into l_n2 from v$mystat where statistic#=498;
 12
 13  		exit when l_n2 < l_n1;
 14
 15  		l_n1 := l_n2;
 16  	end loop;
 17
 18  	dbms_output.put_line(l_n1);
 19  	dbms_output.put_line(l_n2);
 20  end;
 21  /

65535
1

PL/SQL procedure successfully completed

Oh well… apparently session cursor cache hits is now backed up by 16-bit unsigned integer… uncool.

I don’t know whether any other stat experienced such a “downgrade” but for my particular case having it as 16-bit unsigned integer produced quite a bizarre results on the newest version of Oracle database where the previous one was behaving just fine.

Tests were done on Oracle 11.2.0.1 under Linux x64.

I’ve hit a bug in Oracle 11.2.0.1 when working with deferrable constraints which I think is worth sharing as it may have profound consequences under certain scenarios.

Let’s start by creating a simple table with a deferrable primary key:

SQL> create table def_bug(n number primary key deferrable initially deferred);

Table created

SQL> insert into def_bug values (1);

1 row inserted

SQL> insert into def_bug values (2);

1 row inserted

SQL> commit;

Commit complete

You can confirm that the primary key constraint is working fine by trying to insert a duplicate value:

SQL> insert into def_bug values (1);

1 row inserted

SQL> commit;

commit

ORA-02091: transaction rolled back
ORA-00001: unique constraint (SRC.SYS_C004070) violated

So far so good. Open a second session and execute the following update:

SQL> update def_bug set n=3 where n=2;

1 row updated

Do not commit yet and execute in your first session:

SQL> update def_bug set n=3 where n<=2;

The above update will block due to our second session holding a lock on the row where n=2. Now commit your second session…

SQL> update def_bug set n=3 where n=2;

1 row updated

SQL> commit;

Commit complete

…and then commit your first session:

SQL> update def_bug set n=3 where n<=2;

1 row updated

SQL> commit;

Commit complete

Take a look at the data now:

SQL> select * from def_bug;

         N
----------
         3
         3

Ouch! This was certainly unexpected. You can confirm that the primary key is still working by trying to insert a duplicate value again:

SQL> insert into def_bug values (3);

1 row inserted

SQL> commit;

commit

ORA-02091: transaction rolled back
ORA-00001: unique constraint (SRC.SYS_C004070) violated

It certainly looks like the update statement did not take into account deferrable constraint declared on the table during restart caused by the write consistency mechanism.

Recently, I tested a switchover on Oracle 11g SE1.

As you know, Oracle Database Standard Edition One—as well as Standard Edition—does not have the Data Guard feature. Therefore, I had to do everything manually.

The whole process took less than 15 minutes. This includes less than five minutes of full downtime to restart the database in READ-ONLY mode, and less than 10 minutes of READ-ONLY downtime.

Of course, it depends on the size of Redo logs and the network speed to move Redo logs from the primary server to standby.

Here is what I had. The primary database and one physical standby database:

• OS – SUSE Linux ES10 (SP2) x86_64
• Oracle – Release 11.1.0.7.0 64bit SE1

First of all, I switched the standby database to the primary role.

Read the rest of this entry . . .

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.

Read the rest of this entry . . .

Note: Installing Oracle 11gR1 on Ubuntu 8.10 Intrepid Ibex is now published.

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).

Read the rest of this entry . . .