In my previous post, I described the most common cause for unstable plans due to bind peeking — histograms. It is now time to move forward and take a look at another case, namely range-based predicates. Strictly speaking, the cases I’m going to describe can appear without range-based predicates as well, you just need to remember that a range-based operation doesn’t necessarily imply a range-based predicate.

How Can Range-Based Predicates Cause an Unstable Plan?

Quite easy, take the following example:

SQL> create table t as
  2  	select level n, rpad('x', 200, 'x') v
  3  		from dual
  4  		connect by level <= 100000;

Table created

SQL> create index i_t_n on t (n);

Index created

SQL> exec dbms_stats.gather_table_stats(user, 't');

PL/SQL procedure successfully completed

Now, I’ll query the table using two different conditions:

SQL> set autot traceonly explain
SQL> select * from t where n <= 100;

Execution Plan
-----------------------------------------------------
Plan hash value: 2912310446

----------------------------------------------------
| Id  | Operation                   | Name  | Rows |
----------------------------------------------------
|   0 | SELECT STATEMENT            |       |   95 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |   95 |
|*  2 |   INDEX RANGE SCAN          | I_T_N |   95 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("N"<=100)

SQL> select * from t where n <= 25000;

Execution Plan
------------------------------------------
Plan hash value: 1601196873

------------------------------------------
| Id  | Operation         | Name | Rows  |
------------------------------------------
|   0 | SELECT STATEMENT  |      | 24998 |
|*  1 |  TABLE ACCESS FULL| T    | 24998 |
------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("N"<=25000)

(Note that I’ve trimmed the output for the sake of readability.)

The first query was executed using INDEX RANGE SCAN, the second one using TABLE ACCESS FULL. This makes perfect sense, since the first query is going to return only a small fraction of data in a table, while the second one is going to fetch substantially more data. As you probably already guessed, if you substitute a literal value for a bind variable, your plan will depend on what value was passed during a hard parse: (more…)

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.

(more…)

I wrote this post because I feel there is a great need for it. The number of people struggling with unstable query plans due to bind peeking in Oracle 10G is enormous, to say the least. More than that, solutions like disabling bind variable peeking are driving us away from understanding the root cause of the problem and applying the right fix to it.

What are the causes of unstable plans due to bind variable peeking?

There are three things that might put you at risk of unstable plans due to bind variable peeking. Those are histograms, partitions, and range-based predicates. I’ll cover last two in upcoming blog posts.

Histograms

Let me share with you my déjà vu. When I see this:

SQL> select value
  2  	from v$parameter
  3  	where name='_optim_peek_user_binds';

VALUE
--------------------
FALSE

I immediately expect this:

SQL> select	sum(case when max_cnt > 2 then 1 else 0 end) histograms,
  2  		sum(case when max_cnt <= 2 then 1 else 0 end) no_histograms
  3  from (
  4  	select table_name, max(cnt) max_cnt
  5  		from (
  6  			select table_name, column_name, count(*) cnt
  7  				from dba_tab_histograms
  8  				group by table_name, column_name
  9  		) group by table_name
 10  );

HISTOGRAMS NO_HISTOGRAMS
---------- -------------
      1169          2494

The above is an example from a real-world OLTP system running with bind peeking disabled. It is no surprise to me. An exception, you say? Here’s another one . . . (more…)

This will be the final post in my series on Result Caches. In my previous article, I had already got almost everything. Almost — four CPUs (cores) were still not enough to saturate the single latch. As you’ve probably already guessed, today we are going with an eight-way test.

Please note that today’s numbers are different since I’m using an entirely different hardware platform. While the four-way tests were done on a 2.4GHz Core 2 Quad box, today’s eight-way tests were done using four dual core Itanium 2 CPUs running at 1.1GHz.

Let’s take a look at the results:

I made a nice-looking graph from this:

(more…)

Just a short blog entry about a funny error message I’ve got while trying to activate a physical standby database:

SQL> alter database recover managed standby database finish skip standby logfile;
alter database recover managed standby database finish skip standby logfile
*
ERROR at line 1:
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 1: '/oradata/stage/datafile/system_01.dbf'
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/oradata/stage/datafile/system_01.dbf'
ORA-01130: database file version 9.2.0.0.0 incompatible with ORACLE
version 9.2.0.0.0

Database file version 9.2.0.0.0 is incompatible with ORACLE version 9.2.0.0.0, is it?

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: (more…)

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: (more…)

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.

(more…)

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… (more…)

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: (more…)