Oracle Scratchpad

December 16, 2009

Adaptive Optimisation ?

Filed under: CBO,Execution plans,trace files — Jonathan Lewis @ 11:53 pm BST Dec 16,2009

Here’s an interesting post and test case from Gregory Guillou (WeDoStreams blog). It features an SQL statement that is re-optimised the second time you run it.

Since it’s running on your first thought is likely to be “SQL Plan Management”, or “Adaptive Cursor Sharing” – except the first feature wasn’t enabled, and the statement doesn’t include any bind variables.

Gregory emailed me about this one, and  it was the thing that finally persuaded me to tear down a laptop and install 64-bit OEL with 11.2 – and I ran his test case and got the same results. (If you build his sample schema, you’ll need about 1GB of free space).


September 7, 2009

Analytic Agony

Filed under: Infrastructure,Performance,sorting,trace files,Troubleshooting — Jonathan Lewis @ 5:30 pm BST Sep 7,2009

When I wrote Practical Oracle 8i, version 8.1.5 was the most recent version of Oracle but version 8.1.6  came out just before I finished writing – and the only thing in 8.1.6 I thought important enough to add to the book was a section on Analytic Functions because they were the best new coding feature in the product.

Since then I’ve always warned people to be a little careful about how they use analytic functions because of the amount of sorting they can introduce. My suggestion has always been to crunch “large” volumes of data down to “small” volumes of data before applying any analytic functions to add “intelligence” to the intermediate result.

August 17, 2009


Filed under: Indexing,Infrastructure,Oracle,trace files,Troubleshooting — Jonathan Lewis @ 5:31 pm BST Aug 17,2009

Here’s a mechanism for examining indexes in some detail if you think that something odd may be going on inside them. It’s a feature that I first decribed in Practical Oracle 8i, although the book doesn’t mention a problem with it that I subsequently discovered that means I always test it carefully on a small index before I use it on a large one.  Here’s a demonstration – cut from an SQL*Plus session on


July 24, 2009

IQ2 – Answers

Filed under: Index Explosion,Indexing,Infrastructure,trace files,Troubleshooting — Jonathan Lewis @ 9:04 pm BST Jul 24,2009

I was planning to supply the answers to Index Quiz 2 as a comment – but there’s a lot of block dumps involved, and it’s easier to do that in postings.

Question 1: I’ve created a table and index with initrans 4, then inserted one row into the table. How many ITL (interested transaction list – see glossary) entries will there be in the first block of the index when you dump it.

Answer 1: The table block will show 4 entries in the ITL, obeying your setting for initrans, but the index block will show only two entries – unless you’re using Oracle 8i or earlier (Basically indexes tend to ignore the setting for initrans  except when you rebuild an index, or create it on existing data.):

June 7, 2009

PGA leaks

Filed under: Infrastructure,trace files,Troubleshooting — Jonathan Lewis @ 7:53 pm BST Jun 7,2009

Here’s a simple script that I created a short time ago while investigating a memory problem on a client site. The purpose of writing the script was, as always, to strip the client’s code back to a bare minimum in an attempt to work out the root cause of a problem. (Warning: if you want to run this script, your Oracle shadow process will grab about 1GB of PGA RAM )


May 14, 2009

Consistent Gets

Filed under: Execution plans,Performance,trace files,Troubleshooting — Jonathan Lewis @ 8:13 am BST May 14,2009

Today’s little quiz – just for fun, but prompted by a few comments in this posting on OTN: (more…)

March 22, 2009

Block size – again

Filed under: Block Size,Infrastructure,Performance,trace files,Troubleshooting,undo — Jonathan Lewis @ 7:09 pm BST Mar 22,2009

Here’s a little oddity that I came across at a client site recently.

The client called me in because they were having problems with Oracle error “ORA-01555: snapshot too old” appearing in a particular task after a few thousand seconds (typically 5,000 to 6,000) even though they had set the undo_retention to 14,400 seconds and had a huge undo tablespace running with autoextend enabled on the data files.


January 4, 2009

Index rebuild – again

Filed under: Index Rebuilds,Indexing,Infrastructure,trace files — Jonathan Lewis @ 7:10 pm BST Jan 4,2009

Someone posted a very pertinent question about blocks splits and index rebuilds on the OTN Database forum yesterday covering a detail of the cost/benefit equation that I don’t think I’ve mentioned before.

Since I answered the question on the forum I’ve posted a link here to make it available to a wider audience. 

(This is a strategy I may adopt more frequently in the future – there’s a lot of useful material of mine all over the internet, and I really ought to make sure I don’t spend time repeating myself when  simple pointer would do).

[Updated May 2009: The forum item has since gone missing – so maybe linking to notes I’ve written in public groups is not so smart after all]. Fortunately I happen to have been saving copies of the thread as it progressed, and the little point that I wanted to highlight was just the following:

“When you create or rebuild an index Oracle does not honour the pctfree setting in the branch blocks. Since the branch blocks are effectively 100% packed, the very first leaf block split in each branch block (except the last one) will almost certainly result in a branch block split. This means a single row insert into an 8KB block could result in 16KB+ of redo for the leaf block plus 16KB+ for the branch block split.”

[Further reading on rebuilding indexes]

November 25, 2008


Filed under: CBO,Performance,sorting,trace files — Jonathan Lewis @ 11:29 pm BST Nov 25,2008

I see that Tom Kyte has found a nasty little bug waiting to trap a few unlucky people as they patch to, or upgrade to 11g.


November 5, 2008

PX Buffer

Filed under: Execution plans,Parallel Execution,Performance,trace files,Troubleshooting — Jonathan Lewis @ 7:11 pm BST Nov 5,2008

Here’s a surprising anomaly that showed up in a question on the OTN forums a little while ago. Consider a simple query that uses a hash join between two tables.

		parallel(t1 2)
		parallel(t2 2)
		pq_distribute(t2 hash hash)
from 	t1, t2
where	t2.n1 = t1.n1
and	t2.small_vc = t1.small_vc

When it runs serially the join completes in memory and the only I/O you see comes from the two tablescans. When the query runs parallel something causes a spill to the temporary tablespace.

July 13, 2008

Sorted Hash Clusters

Filed under: Execution plans,Infrastructure,Oracle,trace files,Tuning — Jonathan Lewis @ 9:25 pm BST Jul 13,2008

[Forward to Part 2]

Towards the end of April, I published a note about manual optimisation,  and mentioned in one of the comments (#11) that as part of the discussion of the (slightly suspect) mechanism I had introduced I would eventually get around to talking about sorted hash clusters. So I’ve finally managed to make a start.


February 25, 2008

IOTs and 10053

Filed under: CBO,Execution plans,Infrastructure,IOT,Oracle,trace files,Troubleshooting — Jonathan Lewis @ 9:03 pm BST Feb 25,2008

I’ve probably got a couple of comments about 10053 trace files lurking somewhere on this blog and on my website – and when I mention the 10053 I usually remember to say that it’s a last resort that I only use when I think there may be a bug that needs to be nailed.

So here’s a bit of a 10053 – which I only looked at because I thought it was going to show me a bug.

February 13, 2008

Statistics problem

Filed under: Oracle,Performance,Statistics,trace files,Troubleshooting — Jonathan Lewis @ 12:38 pm BST Feb 13,2008

The worst type of Oracle bug is the one that seems to appear randomly and can’t be reproduced on demand. (Such as when Oracle support says “please send us a reproducible test case”).

Here’s one such (probable) bug that showed up at a client site that was reporting performance problems with a query that, on random days, chose a bad execution plan. The client was running one of the earlier versions of 9.2, and using the following call to dbms_stats to collect fresh table stats for each table in turn every night.

September 3, 2007

Trace Files

Filed under: trace files,Troubleshooting — Jonathan Lewis @ 8:14 pm BST Sep 3,2007

If you ever thought you could understand what’s printed in a trace file, you might like to read this comment from a recent SR:

The trace file is reporting the steps that are needed to troubleshoot an issue. The comments tags and entries in the files may not be consistent in every scenario and their meaning can change between tasks and versions. As they are not designed or intended for direct interpretation by customers, no rationale or consistency is guaranteed.

Further, no official documentation is provided for the raw trace files. The only behaviour that is considered ‘expected’ behaviour is that which is contained in the documentation and manuals.

Of course, this does make life a little confusing – is a note that’s been published on Metalink (such as 39817.1 and 338137.1) not official after all ?

August 20, 2007

Intelligent Stats

Filed under: CBO,Partitioning,trace files,Troubleshooting — Jonathan Lewis @ 5:43 pm BST Aug 20,2007

Experiences like this one [Ed: Nov 2008 – the blog has become private since I wrote this note]are always worth reading about to remind yourself what you can do with the dbms_stats package when it’s really necessary.

And while I’m pointing to other URLs, here’s another one worth knowing about – event “Cursor: pin S wait on X”.  It’s not surprising to see this wait event occasionally in a busy 10g system, but if you’re losing a significant amount of time, it could be a bug.

« Previous PageNext Page »

The Rubric Theme. Blog at


Get every new post delivered to your Inbox.

Join 4,894 other followers