I’ve been told that using NOT EXISTS in (Oracle) SQL is a bad idea, and that a way to overcome this problem is to collect the non-matching rows with an OUTER JOIN. So I decided to check if it is true.

In order to start, here is my test case:

create table t1(id number,
   constraint t1_pk primary key(id));

  create table t2(id number);

begin
  for i in 1..100 loop
    insert into t1 values(i);
  end loop;
  commit;
end;

begin
  for i in 1..100000 loop
    insert into t2
      values(mod(i,97));
  end loop;
  commit;
end;
/

create index t2_idx on t2(id);

exec dbms_stats.gather_table_stats(USER,'T1');

exec dbms_stats.gather_table_stats(USER,'T2');

First, I checked what I’d been told, i.e. that the OUTER JOIN is more efficient than the NOT EXISTS. In order to do that, I wrote a simple SELECT and displayed the plan for both syntaxes (my database is 11.1.0.6 on Linux 32-bits). As I assumed, it’s not the case. In fact, both orders took the same plan. (more…)

Disclaimer: In most countries, looking at user passwords is illegal. Never try what is written below on a system that somebody other than you can access.

Oracle Grid Control documentation warns against leaving the emkey in the Grid Control repository. It says here:

After the emkey has been copied, you must remove it from the Management Repository as it is not considered secure. If it is not removed, data such as database passwords, server passwords and other sensitive information can be easily decrypted.

You may wonder: how easily?

A Bit of Background

When you deal with management tools, you want to collect metrics and run various tasks on different targets. Unless you evolve in an very advanced security infrastructure such as a PKI or another “real” Single Sign-On solution, it’s likely that you’ll need a username/password to connect to a remote server, a remote database, or a remote application server. Because Grid Control enables you to automate a great number of tasks, it has to be able to connect without prompting the users for credentials. In order to do that, it has to know the real passwords because it will itself authenticate on the targets. Because there no magic in there, it has to store these informations in its repository!

As a consequence, if the password you type to connect to the Grid Control is stored in a HASH form only, the one you store in Grid Control to run a task, collect data, or simply avoid typing it when you drill down to a target, has to be reversible. But I guess it’s the same for all management solution.

Let’s Be More Specific

Oracle Management Service 10.2 uses several ways to protect these sensitive data, including Virtual Private Database and Password Encryption.

• To overcome the first one, you have to be able to connect to the database as the SYS user.
• To overcome the second one, you have to know the encrypted password form, the key, and the associated algorithm.

Obviously, the key used to cipher the password is the emkey. It is located in $OMS_HOME/sysman/config/emkey.ora by default, and it can be generated/configured with "emctl xxx emkey". So the next question is, “Where are stored the ciphered passwords?”.

(more…)

I’m a Linux fan, and when it comes to specific problems, I’m afraid not all operating systems are equally armed. Enabling a specific user to listen on a port below 1024 is one of these problems that was solved for years with various approaches:

• The Windows approach: we just don’t care!
• The Solaris 10 approach: we have an advanced privilege (net_privaddr)
• The Linux approach*: you’ll find a way to make it work anyway (man setuid)

So you may think, obviously you can access the GridControl 10.2 agent on Linux with HTTPS only, on port 443! And obviously you can — but:

1. The agent cannot listen on port 443 on Linux because it uses the OC4J HTTP listener and cannot run under port 1024. I didn’t try to install the agent as root and you may know something I don’t.
2. You cannot, with 10.2, set up an HTTP front-end, like Apache, for your agent. That’s because the agent sends its listening port to the Oracle Management Server (OMS) so that the OMS can interact with it.
3. Here is a section of EM 10.2.0.4 documentation, you may want to consider: “The final step in that configuration process is to restrict uploads from the Management Agents to secure channels only. Before completing that step, configure your firewalls to allow both HTTP and HTTPS traffic between the Management Agent and Management Repository and test to be sure that you can log in to Enterprise Manager . . . ”
4. There is another interesting section of the documentation. It explains that the OMS directly accesses the target database with the Oracle*Net Protocol.

Regarding the last point, you can open the firewall to Oracle*Net traffic or have a look at Connection Manager (CMAN) to create a kind of Oracle*Net DMZ access to your databases. I’m wondering if SSH tunnels are reliable enough to handle this? I wouldn’t use it.

I know I least one other way, but I need to keep some material for future posts.

Part 1: How to Make the OMS to Agent Traffic via port 443

Can you use only port 443 between the OMS and the agent on Linux? The answer is obviously yes, and here is at least one approach: port address translation. (more…)

If you are or have ever been a SQL developer, it’s very likely you’ve been asked to return the rows from two joined tables, including all the rows from both tables that do not have a corresponding row in the other table. Oracle 9i introduced the FULL OUTER JOIN syntax to better address this scenario. Now it looks as if 11g has introduced a new algorithm to handle that.

So how can you get a look at this? (more…)

It seems Oracle 11g introduces a difference between count(*) and count(1). The way this happens is just the opposite of what I was thinking would happen. NB: I ran my test using “11.1.0.6 32bits” on Ubuntu Linux 7.04 (Feisty) which is not officially supported¹, and which has already lead me to some unexpected behaviors. If this difference with count() is really the 11g way and not buggy behavior related to the Ubuntu install, I’m glad to have found it.

Here’s what you can do to observe (or confirm or dispute) this.

(more…)

After weeks of not having the time I wanted for this, it’s finally done! Today, I installed RAC on Oracle Enterprise Linux 5 (OEL5), and I can tell you that there’s nothing exceptional about the process.

The only trouble I encountered had nothing directly to do with the installation. In order for the device rights to be assigned when RHEL5 or OEL5 start up, you have to create a file in /etc/udev/rules.d, but I have already spoken about that in my last post on the subject of raw devices.

Besides that, it’s all quite simple once 10g’s prerequisites are met. After an hour and a half, it was all wrapped up, at least for two nodes. I didn’t see anything revolutionary differences, but nonetheless there are some points worth mentioning.

(more…)

A lot of things have already been said about the Real Application Testing Option — from the price to the most interesting technical details — by Ken Jacobs, Arup Nanda, and even by me. Why add something? Because while Database Replay gets most people’s attention, Real Application Testing offers another interesting feature called SQL Performance Analyzer (SQLPA). So what is it?

The DBMS_SQLPA package enables you to register and compare the statistics of several SQL query executions stored in an SQL Tuning Set (STS). With SQL Performance Analyzer, you can compare the executions of queries before and after you make some changes to your database. As you might guess, I’m going to illustrate this new feature in Oracle 11g with a simple example.
(more…)

I’m just wondering… be careful it is part of Oracle Tuning Pack 11g !

Anyway, before you answer this question, lets illustrate with an example how the “non-automatic”, “no tuning Pack” SQL Plan Management works. This 11g new feature completes (replaces ?) the outlines by providing a new plan stability capability to Oracle database 11g Enterprise Edition.

If you want the truth behind this example, you should refer before anything else to the 11g documentation :

• Oracle 11g Performance Guide - 15.Using SQL Plan Management
• Oracle 11g PL/SQL Packages and Types Reference : DBMS_SPM
• Oracle 11g Reference :
  • optimizer_use_sql_plan_baselines
  • optimizer_capture_sql_plan_baselines (Tuning Pack)
  • DBA_SQL_MANAGEMENT_CONFIG
  • DBA_SQL_PLAN_BASELINES

(more…)

“Real Time SQL Monitoring” is a 11g Tuning Pack new feature. You can easily access it when the Tuning Pack is set. If a query is a “long” query, if it uses more than 5 seconds of CPU of I/O Wait or if it’s a parallel query (from the documentation), the plan execution statistics are kept by the engine and you can follow the query execution in Real Time. Note that it differs from V$SESSION_LONGOPS which enables you to follow one step of a query or other operations. Anyway, It’s very impressive even if it doesn’t look to be fully functional (Is it me ?). It’s just a shame that I won’t be able to access it for all the databases although we’ve already asked for.

I don’t want to paraphrase the documentation but just build a test case to illustrate this feature. For more information about Real Time SQL Monitoring see :

• Oracle 11.1 - Performance Guide : Real Time SQL Monitoring
  
• Oracle 11.1 - PL/SQL Package and Type Reference DBMS_SQLTUNE
  
• Oracle 11.1 - Reference V$SQL_MONITOR et V$SQL_PLAN_MONITOR

As always I guess Enterprise Manager provides a fantastic GUI for this feature. This post is for you if you don’t always use it…
(more…)

While Alex G. almost missed the documentation… I rushed into my favorite book, not the new Feature Guide, but Oracle Database Licensing Information - 11g Release 1. Guess what ? We now have 4 new opportunities to do business with Oracle :

• Oracle Active Data Guard
• Oracle Advanced Compression
• Oracle Real Application Testing
• Oracle Total Recall

I still miss the price list. As if you could look at a new Release without the price ;)