Pythian Group

In my previous blog entry, I explained why I would expect Result Cache not to scale well. Unfortunately, at the time that blog entry was written, I had no access to hardware with more than two cores. That left me in an everything-but-the-proof state. “Theory without practice is sterile.” ©Albert Einstein.

Since then, I got a chance to re-run my test cases on a quad-core CPU, moving one step forward.

I re-executed my test cases with one to four processes against the Buffer Cache and the Result Cache in order to capture the number of lookups per second. I raised number of iterations to 1M to make the results more stable though.

Here is what I got: Read the rest of this entry . . .

I do realize that for most of you, there may be nothing new about the dbms_sys_sql package – knowledge of it has been floating around for quite a while. I myself discovered this package a couple of years ago while playing around with HTMLDB’s (now APEX’s) internals. I’m posting this as a response to a vox populi demand: I often see people who could benefit from dbms_sys_sql, if they only knew about it.

Sometimes you need to execute SQL (or PL/SQL) as some other user. Say you need to remove a job residing in some other user’s schema. Or you need to create a database link in a different schema. You can’t do things like these without becoming the other user. If you don’t know the other user’s password then you basically have two choices: Read the rest of this entry . . .

In the previous article, I described my observations of RC Enqueue. Now it is time to take a look at the RC latches.

Latches, being serialization devices, are scalability inhibitors. Not that they inherently prevent you from scaling, quite the opposite is true. Serialization is a must if you expect your system to produce anything apart from GIGO (Garbage In Garbage Out). Concurrency is essentially made possible through serialization of shared resources. That being said, I would expect Result Cache to beat Oracle’s buffer cache on read-only workloads, since that is what RC was designed for. That is, Result Cache should perform faster and scale better.

Read the rest of this entry . . .

Way before 11G made its way to production, I joked that this new feature called “Query Result Cache” will just implicitly create a materialized view, protecting dependent objects with some new enqueue while doing so. Now we all can start the new release — at least on Linux x86 — so naturally, I was curious to see whether I was just throwing sparks without having a fire. Query Result Cache was the first new feature that I put to the test, only to discover that you can spend more than two minutes waiting on… guess what.

There is how it supposed to work:

SQL> create table t as select * from all_objects;

Table created.

SQL> set autot traceonly stat
SQL> select /*+ result_cache */ owner, count(*) from t group by owner;

18 rows selected.

Statistics
----------------------------------------------------------
         48  recursive calls
          0  db block gets
        986  consistent gets
        906  physical reads
          0  redo size
        780  bytes sent via SQL*Net to client
        403  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         18  rows processed

SQL> select /*+ result_cache */ owner, count(*) from t group by owner;

18 rows selected.

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        780  bytes sent via SQL*Net to client
        403  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         18  rows processed

Notice zero LIO count for a second execution. That’s pretty much the same effect you can observe while accessing fast dual. Since Result Cache is subject to read-only access, it should be backed up by lighter-weight serialization stuff than the traditional buffer cache. But more on that next time.

Okay, what about the fun part, you ask? The fun part is that it took me roughly 30 seconds to realize the presence of a new enqueue. Let’s go… Read the rest of this entry . . .

Just a small observation that resulted from a real-world case.

Recently, I was asked to take a look at a query processing part of a client’s application which had started to perform slowly after the application had a maintenance downtime. The idea behind the processing was quite simple. Requests were inserted into a table:

SQL> create table requests
  2  (
  3   subs_id  number,
  4   insert_time date,
  5   processed number
  6  );

Table created.

Those rows were dequeued by seven processes, each process working on its own set of data using the following SQL:

select *
	from requests
	where case processed when 0 then mod(subs_id, 7) else null end=:dequeue_process_number
	order by insert_time

That is, processes were dequeuing records with the processed flag set to zero (unprocessed), and data partitioning was done using a simple yet effective mod(subs_id, 7) function. The goal of that somewhat strange WHERE clause was to support a function-based index to allow for a quick dequeue:

SQL> create index fbi_requests_p
  2   on requests
  3   (case processed when 0 then mod(subs_id, 7) end);

Index created.

Since NULL keys are not stored inside a B*Tree index, that technique allows me to store only unprocessed records in the index and divide them between dequeue processes effectively.

We can now proceed with a test-case to demonstrate how dequeue part worked: Read the rest of this entry . . .

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? Read the rest of this entry . . .

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.
Read the rest of this entry . . .

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? Read the rest of this entry . . .

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?
Read the rest of this entry . . .