In the following SQL*Plus output…

SQL> select sum(tran_sum), count(*)
  2    from trans;

no rows selected

…something is goofed up. You should not have a no rows selected with this SQL.

When you see something like that for the first time, you can generally draw from four conclusions:

1. Someone edited the output
2. SQL*Plus bug
3. Oracle bug
4. Combination of the above

Since it was me who saw this, number one was an easy but irrelevant answer.

What about an SQL*Plus bug? OK, run this using some other ad hoc tool. Same result. Time to take a more precise look at the problem. What is trans?

SQL> select object_type from user_objects where object_name='TRANS';

OBJECT_TYPE
-------------------
TABLE

SQL> desc TRANS;
 Name          Null?    Type
 ------------- -------- -------
 TRAN_ID       NOT NULL NUMBER
 TRAN_SUM      NOT NULL NUMBER

Table trans has two not null columns (of the number datatype). Couldn’t be simpler. Oracle can’t have a bug in such a simple case, so what’s the deal?

Could it be because what we see is not what we see? (more…)

Before you proceed with reading this post, I strongly encourage you to read Tom Kyte’s trilogy about write consistency, since I’ll do only a brief introduction to the subject.

The way Oracle ensures UPDATE write consistency is through a mechanism called restart.

The restart takes place when UPDATE detects that the current version of the column data used in the WHERE clause no longer matches the consistent version. That is, the data were changed after the UPDATE was started.

Let’s take a look at an example before we proceed with the main topic of this blog post.
(more…)

To between or not to between? Sometimes I wonder how “simple” things can end up being not where you would expect them to be.

Let’s take the following question, for example. Is there any difference between using:
where column between n and m and where column>=n and column<=m?

Looks like a simple one, eh? Oracle’s documentation is dead clear on this:

[Between] means “greater than or equal to low value and less than or equal to high value.”

They are the same from a semantic point of view. But SQL is a declarative language. In other words, you wouldn’t expect same execution plan with two semantically identical statements, would you? But we can actually observe that between can be transparently transformed into “greater than or equal to low value and less than or equal to high value” by the optimizer itself:

SQL> select *
  2   from dual
  3   where dummy between ‘A’ and ‘Z’;
–– plan omitted for the sake of clarity
Predicate Information (identified by operation id):
––––––––––––––––––––––––––––––––––––––––––

   1 - filter(”DUMMY”>=’A’ AND “DUMMY”<=’Z')

So it should be safe to assume that both statements are interchangeable with each other, since they all lead to the same filter or access predicate. Right? (more…)

Oracle 10g introduced the ability to run shared server sessions within the workarea_size_policy=auto parameter. However, there is one caveat I would like to point out.

I noticed this after one of our databases was upgraded from the 9iR2 release. In a nutshell , the number of shared servers jumped significantly. Before the upgrade, we were able run the system with only 8-10 shared servers, but ended up running as many as 50-60 just to handle the same workload in 10gR2.

After a quick look at the system, I discovered a lot of inactive sessions still holding the shared server. That is…

SQL> select status, server
2   from v$session
3   where sid=147;

STATUS   SERVER
-------- ---------
INACTIVE SHARED

…and the shared server status was WAIT(RECEIVE):

SQL> select status
  2   from v$shared_server
  3   where paddr=(select paddr from v$session where sid=147);

STATUS
----------------
WAIT(RECEIVE)

…and we had a lot of those. But what was the cause?
(more…)