Pythian Group

It’s probably worth some explanation to understand where I want to drive you: when you run an update in Oracle, the changes are made at the point that is consistent during the whole execution of the update. This allows you to run a command like the one below, even if ID is the primary key of T:

update t
  set id=(case id
             when 1 then 2
             when 2 then 1
             end)
  where id in (1,2);

If that was not the case, you would have to DEFER the constraint validation to the commit time every time you ran a command like the one above, or you would get an error like:

ORA-00001: unique constraint T_PK violated

To read more about how updates work in Oracle, look at Tom Kyte’s Write Consistency Part I, Part II, Seeing a Restart, and Part III, Why is a Restart Important to Us. But that’s a tangent to the subject of this post. What I’ll show here is that you can run a DELETE and an INSERT as parts of the same statement.

Read the rest of this entry . . .

The Oracle gateway for ODBC provides an almost seamless data integration between Oracle and other RDBMS. I won’t argue about its performance, limits, or relevance. It serves a few purposes; set it up and you’ll be able, for example, to create database links between Oracle and MySQL. After all, wouldn’t it be nice if you could run some of the following SQL statements?

• select o.col1, m.col1 from oracle_tab
o, mysql_tab@mysql m where o.col1=m.col1;
• insert into oracle_tab (select * from mysql_tab@mysql);

This post is intended to share, the same way Karun did it for SQL Server last year, some tips related to the setup of the Oracle Gateway for ODBC with MySQL Connector ODBC on Linux.

Prerequisites

I’ve installed all the configuration on my laptop to test it. It’s running Ubuntu Intrepid Ibex 32bits, but I won’t dig into the challenge of installing the MySQL Connector ODBC 5.1 on it. All I’ll tell you is that, if I understand correctly, the version of iodbc that comes with Intrepid doesn’t support MySQL Connector ODBC 5.1 too well, and the messages that it returns are not quite explicit. To be frank, what I did is put that monkey on Augusto’s back. He sorted out everything in a few minutes. I guess I have to thank Augusto twice, just for this post! I’ve also followed his “Installing Oracle 11gR1 on Ubuntu 8.10 Intrepid Ibex” post to install the Oracle part.

But let’s talk about the prerequisites! You need to have installed and configured the following components:

Read the rest of this entry . . .

You can choose a DBID when you rename your Oracle database. This is probably a bad, unsupported, and useless idea. I assume this hidden feature can help you to mess up all your backups. So my advice would be: “don’t use it.”

I performed this test with Oracle 11.1.0.7 on Linux x86. It consists in using dbms_backup_restore instead of nid to rename the database. You’ll find below the few steps require to get to it.

Read the rest of this entry . . .

Today I’ve been trying to reproduce in an 11g database one of the problems I faced with 10g—one on those problems Riyaj described in his Multi-Column Correlation and Extended Stats in Oracle 11g post. And the fun part is that I wasn’t able to reproduce it. Yet just setting optimizer_features_enable='10.2.0.4' made it show up again. It was as though Oracle can detect Multi-Column Correlation without Extended Statistics. How is this possible?

You don’t need any complicated schemas to check this out for yourself; just one table as below:

create table x (
         a number,
         b number,
         c number);

begin
  for i in 1..1000 loop
    for j in 1..10 loop
      insert into x values (j,j,j);
    end loop;
  end loop;
end;
/

commit;

exec dbms_stats.gather_table_stats(-
                 user,-
                 'X');

Execute a query with a WHERE clause containing two correlated columns:

explain plan for select c from x where a=1 and b=1;

select * from table(dbms_xplan.display);

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |   100 |   900 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| X	 |   100 |   900 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

The optimizer estimates the number of rows returned as 100, which is equivalent to considering that each one of the conditions in the WHERE clause returns 1000 rows, and that those columns are not correlated. (In that case that’s obviously wrong, because a equals b). So, no change with 10g? Read the rest of this entry . . .

This 9^th post will describe how to remove a node from a 11.1 RAC cluster in silent mode. It will differ from the associated documentation in that it will allow you to remove the node even if it is not available anymore. This procedure has only a few differences from the 10.2 one in the 6^th post. For a complete series agenda, see below:

1. Installation of 10.2 And 11.1 Databases
2. Patches of 10.2 And 11.1 databases
3. Cloning Software and databases
4. Install a 10.2 RAC Database
5. Add a Node to a 10.2 RAC database
6. Remove a Node from a 10.2 RAC database
7. Install a 11.1 RAC Database
8. Add a Node to a 11.1 RAC database
9. Remove a Node from a 11.1 RAC database (this post!)
10. A ton of other stuff you should know

In what follows, we’ll remove rac-server5 from the configuration. We assume that rac-server5 is not accessible anymore. However, to cover the case when the node is still available, I’ll mark specific sections with a (*), meaning that the step should be done only if you still have access to that node; it can just be ignored otherwise.

Read the rest of this entry . . .

There has been a lot of buzz about the Oracle Exadata Storage Server these past few days. Did you know you can actually estimate the impact of it on some of your queries with SQL Performance Analyzer (SQLPA)? Here is the story.

First you need to install an 11.1.0.7 database! Then you must load your data and capture your queries in an SQL Tuning Set. Below is a very simple and short example:

Read the rest of this entry . . .

The process of adding a node to a 11.1 RAC is very similar to the 10.2 process described in Part 5 of this series. For this reason, this post will just focus on what has changed between the 2 versions. Here is the complete series up to now:

1. Installation of 10.2 And 11.1 Databases
2. Patches of 10.2 And 11.1 databases
3. Cloning Software and databases
4. Install a 10.2 RAC Database
5. Add a Node to a 10.2 RAC database
6. Remove a Node from a 10.2 RAC database
7. Install a 11.1 RAC Database
8. Add a Node to a 11.1 RAC database (this post!)
9. Remove a Node from a 11.1 RAC database
10. A ton of other stuff you should know

Before you start

Make sure you’ve kept a copy of the voting disk and that you have a backup of the OCR. Check that the locations for all the components to be installed, i.e.: Inventory, Clusterware, ASM, database software, OCR, Voting Disks, and data files, are writable. Confirm that all the prerequisites are met for the node and for the whole cluster with the node to be added.

Refer to the Oracle Clusterware Administration and Deployment Guide
11g Release 1 (11.1) – 4 Adding and Deleting Oracle Clusterware Homes
and the Oracle Real Application Clusters Administration and Deployment Guide
11g Release 1 (11.1) – 9 Adding and Deleting Oracle RAC from Nodes on Linux and UNIX Systems for the complete reference of how to perform these steps.

Read the rest of this entry . . .

This seventh post digs into some of the silent installation commands of an 11.1 RAC. For a complete series agenda up to now, see below:

1. Installation of 10.2 And 11.1 Databases
2. Patches of 10.2 And 11.1 databases
3. Cloning Software and databases
4. Install a 10.2 RAC Database
5. Add a Node to a 10.2 RAC database
6. Remove a Node from a 10.2 RAC database
7. Install a 11.1 RAC Database (this post!)
8. Add a Node to a 11.1 RAC database
9. Remove a Node from a 11.1 RAC database
10. A ton of other stuff you should know

As for the Installation of a 10.2 RAC Database, this post shows how to (1) install the 11.1 clusterware, (2) install the 11.1 database, and (3) create a RAC database. It doesn’t explore any Patch Set upgrade since 11.1.0.7 is not out for now. Another interesting question, however, is how to upgrade the 10.2 clusterware to 11.1, since it has to be done in place.

So let’s get into it.

Read the rest of this entry . . .

This post is for those who think Consistent Gets is the only thing that matters. It’s not. That’s why Statspack and AWR provide not only the top queries sorted by Consistent Gets but also Sorted by IO, CPU, Cluster Waits, and so on. I won’t argue. Check for yourself.

I’ve run the queries that follow on top of 10.2.0.3 on Linux X86_64.

Sample Table

Create and Fill up a table to run your queries. You’ll find the script you need below:

create table X1(a number,b number);

begin
   for i in 1..1000000 loop
      insert into X1 values (i,mod(i,100000));
   end loop;
end;
/

commit;

exec dbms_stats.gather_table_stats(user, 'X1');

Case 1: 4164 Consistent Gets for 0.14 seconds

First, let’s assume that a few Consistent Gets means good performance. Look at the following query:

Read the rest of this entry . . .

This sixth post describes how to remove a node from a 10.2 RAC cluster in silent mode. It differs from the associated documentation in that it will show how to remove a node, even if it has been made unavailable for any reason, including an error by a DBA or a SA.

Here is the complete series agenda:

1. Installation of 10.2 And 11.1 Databases
2. Patches of 10.2 And 11.1 databases
3. Cloning Software and databases
4. Install a 10.2 RAC Database
5. Add a Node to a 10.2 RAC database
6. Remove a Node from a 10.2 RAC database (this post!)
7. Install a 11.1 RAC Database
8. Add a Node to a 11.1 RAC database
9. Remove a Node from a 11.1 RAC database
10. A ton of other stuff you should know

Now for the substance of this part.

Read the rest of this entry . . .