Oracle Scratchpad

May 29, 2007

Autoallocate and PX

Filed under: Infrastructure,Oracle,Parallel Execution,Performance,Troubleshooting — Jonathan Lewis @ 9:22 pm GMT May 29,2007

Here’s a quirky little set of results from trace files. First a 10391 at level 64 – this shows the way in which an object was broken into granules for a parallel tablescan. I’ve just listed the first few granules: (more…)

May 28, 2007

Version Dependency

Filed under: CBO,Execution plans,Troubleshooting — Jonathan Lewis @ 9:48 pm GMT May 28,2007

Here’s a very simple query, hinted to follow a particular execution plan.

	/*+ ordered use_nl(t1) index(t1) */ 
	t2.n1, t1.n2 
	t2.n2 = 45 
and	t2.n1 = t1.n1 

And here’s the execution plan – from autotrace (with CPU costing disabled) (more…)

Oracle and I/O

Filed under: Infrastructure,Performance — Jonathan Lewis @ 6:58 am GMT May 28,2007

Thanks to a recent link on Doug Burns’ website, I’ve been introduced to an article on Bob Sneed’s website that everyone ought to read – especially the bit about testing properly (The Proof of the Pudding)

May 21, 2007


Filed under: Troubleshooting — Jonathan Lewis @ 9:49 pm GMT May 21,2007

How do you discover the source of a problem ? It’s not an easy thing to teach, but here’s a recent example someone sent me that may give you a clue. This is a question that came to me by email: (more…)

May 20, 2007

System Stats strategy

Filed under: CBO,Infrastructure,Statistics,System Stats — Jonathan Lewis @ 9:11 pm GMT May 20,2007

A few days ago I received an email about system statistics. I decided it was worth replying to, provided I wrote my response up as a series of questions and answers on the blog. (I don’t tend to respond to individual questions –  it’s not an effective use of my time – so there has to be a good reason for replying).


May 17, 2007

Data Cluster

Filed under: Indexing,Infrastructure,Statistics,Troubleshooting — Jonathan Lewis @ 9:53 pm GMT May 17,2007

I received an email today containing the following question:

For packaged applications, like the Oracle EBS, some indexes tend to develop a high Clustering Factor over time, like the one shown below:

            BTREE    LEAF    DISTINCT  CLUSTERING       INDEX     TABLE       TABLE 
----------  -----  ------  ----------  ----------  ----------  --------  ---------- 
XXX_PK          3  778150  77,842,100  17,163,350  77,842,100   865,805  77,043,200           

We have some queries that perform range scans on this index and run slower because they have to visit too many blocks from the table. How should one deal with this type of index?

This question raises a couple of important points that are worth reviewing.


May 16, 2007


Filed under: Troubleshooting — Jonathan Lewis @ 10:07 am GMT May 16,2007

One of the (free) aids to trouble-shooting that appeared in 10g was the view v$session_wait_history, a view that lists the last 10 waits for the currently live sessions. For example: (more…)

May 15, 2007

Slow Code

Filed under: Troubleshooting — Jonathan Lewis @ 10:38 am GMT May 15,2007

No, this isn’t going to be about dealing with slow code – it’s about how you can slow your code down when you need to. This may seem a little strange, but for testing purposes you might (for example) want to create a long-running query which didn’t actually do very much work – all you need is a small query with my ‘slow down’ function built in. (more…)

May 11, 2007

LOB sizing

Filed under: Infrastructure,LOBs,Oracle,Performance,Tuning — Jonathan Lewis @ 7:29 pm GMT May 11,2007

Some time ago, I was asked to take a quick look at an application that had to handle a lot of LOBs. The LOB-specific part of the application was actually quite simple – contracts were stored as LOBs – but only for occasional visual reference; all the “structured” information from the contract was extracted and stored in relational tables. Some time after a contract had expired, the LOB could be deleted to reclaim space (in theory).  Historically, the client had purged a load of LOBs from time to time, but didn’t have a deliberate house-keeping task to do the job on a regular basis.


May 8, 2007

More of a hash

Filed under: Partitioning,Performance,Tuning — Jonathan Lewis @ 8:46 pm GMT May 8,2007

No matter how simple a topic you pick, a few minutes thought invariably allow you to conjure up some new anomalies that could appear in the right (or possibly wrong) circumstances.

Yesterday I made a few comments about hash partitioning and performance. Today I suddenly realised that global indexes on hash-partitioned tables could exhibit an unfortunate behaviour pattern that would make them pretty useless – unless hacked or hinted. Consider the following table: (more…)

May 7, 2007

Hash Partitions

Filed under: Infrastructure,Partitioning,Tuning — Jonathan Lewis @ 9:06 pm GMT May 7,2007

A question recently appeared on an article I wrote about partitioning a few months ago:

We are planning to create 8 HASH partitions. Looking only at PERFORMANCE would be there be any improvements, if we go for 16 or 32 partitions (maintainance and availability is not a problem in our case). There are only 2 indexes on our 350M table – one is LOCAL, another non-partitioned index.


May 1, 2007

Optimizer Environment

Filed under: Troubleshooting — Jonathan Lewis @ 8:28 pm GMT May 1,2007

From time to time you may notice that a single SQL statement has several different child cursors visible in v$sql. One reason for this happening is that different sessions my be running with different optimizer environments, for example your optimizer_mode may be all_rows while mine is first_rows_1. If your optimizer environment is different from my optimizer environment, then we cannot share cursors.


The Rubric Theme. Blog at


Get every new post delivered to your Inbox.

Join 5,701 other followers