Oracle Scratchpad

December 30, 2011


Filed under: Bugs,Execution plans,Function based indexes,Indexing,Oracle — Jonathan Lewis @ 5:47 pm BST Dec 30,2011

Here’s a funny little optimizer bug – though one that seems to have been fixed by at least It showed up earlier on today in a thread on the OTN database forum. We’ll start (in with a little table and two indexes – one normal, the other descending.

December 19, 2011

Correlation oddity

Filed under: Bugs,Indexing,Oracle,trace files,Troubleshooting — Jonathan Lewis @ 6:26 pm BST Dec 19,2011

This one’s so odd I nearly posted it as a “Quiz Night” – but decided that it would be friendlier simply to demonstrate it. Here’s a simple script to create a couple of identical tables. It’s using my standard environment but, apart from fiddling with optimizer settings, I doubt if there’s any reason why you need to worry too much about getting the environment exactly right.

September 12, 2011

System Stats

Filed under: Bugs,CBO,Oracle,Statistics,System Stats,Upgrades — Jonathan Lewis @ 5:40 pm BST Sep 12,2011

A quick collation – and warning – for 11.2

Bottom line – be careful about what you do with system stats on 11.2

Footnote: the MOS link is a search string  producing a list of references. I set it up like that because one of the articles referencing the bug is called “Things to consider before upgrade to″ and it’s worth reading.

Addendum: one of the people on the two-day course I’ve just run in Berlin sent me a link for a quick note on how to set your own values for the system stats if you hit this bug. It’s actually quite a reasonable thing to do whether or not you hit the bug given the way that gathering the stats can produce unsuitable figures anyway:  setting system stats. (I’ve also added their company blog to the links on the right, they have a number interesting items and post fairly regularly.)

August 3, 2011


Filed under: Bugs,Oracle,Troubleshooting — Jonathan Lewis @ 5:37 pm BST Aug 3,2011

How do you trouble-shoot a problem ? It’s not an easy question to answer when posed in this generic fashion; but perhaps it’s possible to help people trouble-shoot by doing some examples in front of them. (This is why I’ve got so many statspack/AWR examples – just reading a collection of different problems helps you to get into the right mental habit.)

So here’s a problem someone sent me yesterday. Since it only took a few seconds to read, and included a complete build for a test case, with results, and since it clearly displayed an Oracle bug, I took a look at it. (I’ve trimmed the test a little bit, there were a few more queries leading up to the error):


June 30, 2011

Virtual bug

Filed under: Bugs,Function based indexes,Indexing,Oracle,Parallel Execution,Statistics,Troubleshooting — Jonathan Lewis @ 5:37 pm BST Jun 30,2011

I’ve said in the past that one of the best new features, in my view, in 11g was the appearance of proper virtual columns; and I’ve also been very keen on the new “approximate NDV” that makes it viable to collect stats with the “auto_sample_size”.

Who’d have guessed that if you put them both together, then ran a parallel stats collection it would break :(

The bug number Karen quotes (10013177.8) doesn’t (appear to) mention extended stats – but since virtual columns, function-based indexes, and extended stats share a number of implementation details I’d guess that they might be affected as well.

June 7, 2011

Audit Excess

Filed under: audit,Bugs,Infrastructure,Oracle,trace files,Troubleshooting — Jonathan Lewis @ 6:18 pm BST Jun 7,2011

So you’ve decided you want to audit a particular table in your database, and think that Oracle’s built in audit command will do what you want. You discover two options that seem to be relevant:

audit all on t1 by access;
audit all on t1 by session;

To check the audit state of anything in your schema you can then run a simple query – with a few SQL*Plus formatting commands – to see something like the following:


June 3, 2011

Merge – argh!

Filed under: Bugs,Oracle — Jonathan Lewis @ 6:21 pm BST Jun 3,2011

I wonder if I ought to create an “Argh” category on my blog for articles about some of the nastier, and sometimes unbelievable, bugs in the Oracle code. Here’s one (that’s just about forgivable in terms of failing to test) published by Sokrates a couple of months ago.

If you’re using the merge command without including insert/delete options then Oracle will ignore check constraints. (What’s the most important requirement of a database – many people would say that it’s the correctness of the data.)

May 27, 2011

Audit Ouch!

Filed under: audit,Bugs,Infrastructure,Oracle,redo — Jonathan Lewis @ 5:37 pm BST May 27,2011

A few days ago I was rehearsing a presentation about how to investigate how Oracle works, and came across something surprising. Here’s a simple bit of code:

May 25, 2011


Filed under: audit,Bugs,Infrastructure,Oracle,Troubleshooting — Jonathan Lewis @ 6:41 pm BST May 25,2011

Here’s one of those funny little details that can cause confusion:

SQL> select * from user_audit_object;

no rows selected
SQL> audit select on indjoin by session whenever successful;

Audit succeeded.

SQL> select
  2     count(*)
  3  from
  4     indjoin         ij
  5  where
  6     id between 100 and 200
  7  and        val between 50 and 150
  8  ;


1 row selected.

SQL> select * from user_audit_object where obj_name = 'INDJOIN';

no rows selected


March 30, 2011

ASSM wreck

Filed under: ASSM,Bugs,Index Rebuilds,Indexing,Oracle,Troubleshooting — Jonathan Lewis @ 5:25 pm BST Mar 30,2011

Yesterday I introduced a little framework I use to avoid the traps inherent in writing PL/SQL loops when modelling a session that does lots of simple calls to the database. I decided to publish the framework because I had recently come across an example where a series of SQL statements gives a very different result from a single PL/SQL block.



December 15, 2010

Join Surprise

Filed under: Bugs,CBO,Execution plans,Oracle,Troubleshooting — Jonathan Lewis @ 8:54 pm BST Dec 15,2010

Imagine I have a simple SQL statement with a “where clause” that looks like this:

	t2.id1(+) = t1.id1
and	t2.id2(+) = t1.id2

Would you expect it to run several times faster (25 minutes instead of a few hours) when the only change you made was to swap the order of the join predicates to:

	t2.id2(+) = t1.id2
and	t2.id1(+) = t1.id1


November 30, 2010


Filed under: Bugs,Infrastructure,Oracle — Jonathan Lewis @ 5:33 pm BST Nov 30,2010

That’s “Virtual Private Database”, “Fine Grained Access Control”, or “Row-level Security” – three different names for the same feature. (Four if you include the separately licensed Oracle Label Security (OLS) which is a product built on top of RLS).

I’ve just seen a nice presentation from John Batchelor of Sopra hitting the key points of intent, implementation and threat from this (free) product and wondered how many sites used it. So I’ve put up a little poll about usage, with a follow-up for those who do use it about the impact on their system.

Collection Costs

Filed under: Bugs,Execution plans,Hints,Oracle — Jonathan Lewis @ 7:22 am BST Nov 30,2010

Here’s an extract from an execution plan I found on a client site recently. I’ve collapsed lines 5 to 42 into a single line representing the rowsource produced by a fairly messy execution plan, leaving just the last three stages of execution on view. Each of three operations joins the same collection variable (using the table() operator) to the row source – once through a hash join, then twice more (joining to two other columns) through nested loop outer joins:

The resulting estimates of row counts and costs are quite entertaining and, fortunately, not very accurate:

November 19, 2010

Quiz Night

Filed under: Bugs,Execution plans,Oracle,Performance,Troubleshooting — Jonathan Lewis @ 6:00 pm BST Nov 19,2010

Apart from the fact that the “Rows” figure for the FILTER operation at line 6 is blank, what’s the obvious error in this extract from an execution plan:

September 12, 2010

Surprising Error

Filed under: Bugs,Oracle — Jonathan Lewis @ 6:06 pm BST Sep 12,2010

Here’s an unexpected error that appeared recently while I was doing a test  on a database running  (The fact that I got an error didn’t surprise me, it was Oracle’s choice of error for the mistake I’d made.)

Cut-n-Paste from a SQL*Plus session:

SQL> alter system set db_cache_size = 256m scope = memory;
alter system set db_cache_size = 256m scope = memory
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00001: unique constraint (.) violated

If you’re wondering, I was trying to reduce the db_cache_size from 384MB and the value had been set by the startup parameter file – so the ORA-02097 is probably trying to tell me that I can’t reduce the dynamic value below the initial minimum. (I didn’t check this, I just bounced the database with a change of parameter file, but it’s probably a reasonable guess.)

« Previous PageNext Page »

The Rubric Theme Blog at


Get every new post delivered to your Inbox.

Join 3,453 other followers